Solved!
Problem:
I was tasked to convert a social sports web app's data needs from a MySQL database (to store their standings, schedules, results, player/team stats, etc) to MongoDB.
There were way more reads than writes from/to their database, so it seemed like it made sense. However, the existing MySQL database had hundreds of thousands of rows spanning 24 seasons and I needed a way to convert those rows into a format to use in MongoDB.
Solution:
I needed to write several different scripts for each table that needed to be converted to a MongoDB document. Some tables had many tens of thousands of rows of data and it was a typical relational database with a lot of foreign keys.
To follow is an example of how I converted (perhaps the most complicated table) their results table to a MongoDB document. What I needed to do was convert something like this:
(season_id, store_id, division_id, week_id, team_id, player_num, player_id, g1, g2, g3, g4, g5, g6, g7, g8, g9, g10) (24, 17, 3, 7, 272, 1, 3319, 250, 270, 260, 230, 240, 280, 320, 280, 270, 280)
Into something like this:
[
{
seasonId: 24,
seasonName: 'Winter',
year: 2019,
storeId: 12,
storeCity: 'Brunswick',
divisionId: 1,
divisionName: 'Monday',
weeks: [
{
weekId: 9,
date: '2019-03-11',
matches: [
{
startTime: '06:30PM',
alley: 1,
teams: [
{
teamId: 152,
teamName: 'Brewskees',
type: 'away',
players: [
{
playerId: 311,
playerName: 'Michaiah Rundell',
scores: [370, 180, 180, 130, 180, 200, 190, 190, 270, 530],
totalPoints: 2420,
},
{
playerId: 289,
playerName: 'Gun Chao',
scores: [310, 100, 160, 280, 190, 220, 360, 300, 150, 170],
totalPoints: 2240,
},
{
playerId: 337,
playerName: 'Tiffany Ruic',
scores: [540, 420, 450, 450, 440, 450, 250, 630, 460, 130],
totalPoints: 4220,
},
],
},
// home team object
],
},
// more matches
],
},
// more weeks
],
},
// more season/store/division objects
]I wrote a database model to get all the MySQL results data.
getAllResults: async () => {
try {
const queryString = 'SELECT s.season_id, s.season_name, s.year, st.store_id,
st.store_city, d.division_id, d.day_name, r.week_id, t.team_id, t.team_name,
p.player_id, p.full_name, r.player_num, DATE_FORMAT(sc.week_date, "%Y-%m-%d")
AS date, sc.start_time, sc.alley, IF(t.team_id=sc.away_team_id, "away",
"home") AS type, r.g1, r.g2, r.g3, r.g4, r.g5, r.g6, r.g7, r.g8, r.g9, r.g10
FROM seasons AS s INNER JOIN results AS r USING (`season_id`) INNER JOIN
stores AS st USING (`store_id`) INNER JOIN divisions AS d USING (`division_id`)
INNER JOIN teams AS t USING (`team_id`) INNER JOIN players AS p USING
(`player_id`) INNER JOIN schedule AS sc ON r.season_id=sc.season_id &&
r.store_id=sc.store_id && r.division_id=sc.division_id && r.week_id=sc.week_id
&& (r.team_id=sc.away_team_id || r.team_id=sc.home_team_id) ORDER BY s.season_id
ASC, st.store_id ASC, d.division_id ASC, r.week_id ASC, sc.start_time ASC,
sc.alley ASC, type ASC, r.player_num ASC;';
const queryParams = [];
const [result] = await pool.query(queryString, queryParams);
return [result, null];
} catch (error) {
return [null, error];
}
},Then a controller route to access it.
router.get('/mongo-convert', async (req, res, next) => {
try {
const [data, error] = await Result.getAllResults();
data ? res.json(ResultsFunctions.formatResults(data)) : next(error);
// data ? res.json(data) : next(error);
} catch (error) {
next(error);
}
});In the above route, it passes the row by row data returned from the results table into the imported formatResults() function... which grouped all the data the way I needed it. This is where all the magic happened!
function formatResults(unGroupedResults) {
let seasonStoreDivisionObj, weekObj, matchObj, teamsObj, playerObj,
seasonStoreDivisionIndex, weekIndex, matchIndex, teamIndex;
return unGroupedResults.reduce((acc, cur) => {
seasonStoreDivisionObj = {
seasonId: cur.season_id,
seasonName: cur.season_name,
year: cur.year,
storeId: cur.store_id,
storeCity: cur.store_city,
divisionId: cur.division_id,
divisionName: cur.day_name,
weeks: [],
};
weekObj = {
weekId: cur.week_id,
date: cur.date,
matches: [],
};
matchObj = {
startTime: cur.start_time,
alley: cur.alley,
teams: [],
};
teamsObj = {
teamId: cur.team_id,
teamName: cur.team_name,
type: cur.type,
players: [],
};
playerObj = {
playerId: cur.player_id,
playerName: cur.full_name,
scores: [cur.g1, cur.g2, cur.g3, cur.g4, cur.g5, cur.g6, cur.g7,
cur.g8, cur.g9, cur.g10],
totalPoints: cur.g1 + cur.g2 + cur.g3 + cur.g4 + cur.g5 + cur.g6 +
cur.g7 + cur.g8 + cur.g9 + cur.g10,
};
seasonStoreDivisionIndex = acc.findIndex(a => a.seasonId === cur.season_id
&& a.storeId === cur.store_id && a.divisionId === cur.division_id);
if (seasonStoreDivisionIndex === -1) {
teamsObj.players.push(playerObj);
matchObj.teams.push(teamsObj);
weekObj.matches.push(matchObj);
seasonStoreDivisionObj.weeks.push(weekObj);
acc.push(seasonStoreDivisionObj);
return acc;
}
weekIndex = acc[seasonStoreDivisionIndex].weeks.findIndex(w => w.weekId
=== cur.week_id);
if (weekIndex === -1) {
teamsObj.players.push(playerObj);
matchObj.teams.push(teamsObj);
weekObj.matches.push(matchObj);
acc[seasonStoreDivisionIndex].weeks.push(weekObj);
return acc;
}
matchIndex = acc[seasonStoreDivisionIndex].weeks[weekIndex].matches.findIndex(m =>
m.startTime === cur.start_time && m.alley === cur.alley);
if (matchIndex === -1) {
teamsObj.players.push(playerObj);
matchObj.teams.push(teamsObj);
acc[seasonStoreDivisionIndex].weeks[weekIndex].matches.push(matchObj);
return acc;
}
teamIndex = acc[seasonStoreDivisionIndex].weeks[weekIndex].matches[matchIndex]
.teams.findIndex(t => t.teamId === cur.team_id);
if (teamIndex === -1) {
teamsObj.players.push(playerObj);
acc[seasonStoreDivisionIndex].weeks[weekIndex].matches[matchIndex].teams
.push(teamsObj);
return acc;
}
acc[seasonStoreDivisionIndex].weeks[weekIndex].matches[matchIndex].teams[teamIndex]
.players.push(playerObj);
return acc;
}, []);
}
module.exports = {
formatResults,
};When I accessed the http://localhost:3001/api/results/mongo-convert route in Postman, within a couple of seconds the output (nearly a million lines worth) was perfectly formatted to import into MongoDB.
Problem:
In next.js, if you are using getInitialProps() in _app.js to get data for components, it can be tricky to fetch data from an internal api in particular.
- getInitialProps() can run on the server at times and in the client at other times.
- You must use absolute api paths, not relative paths (eg: ./api/users).
- You cannot use serverless functions that have a database connection (or any other server code running in them).
With all that in mind, it is very helpful to not have to worry about what the url of your api. After all:
- It might be in development on your localhost.
- It might be a deployed production version.
- If it is a production version deployed to Vercel, it would be available under multiple domains.
I was building an app that used an internal api which connected to a remote MongoDB database. So, I tried an npm package named next-absolute-url (which seemed to work great) to dynamically calculate the base url for my api calls in getIntialProps(). Even though it's a small package, who doesn't like using as few npm packages as possible? So, I rewrote the main function of that package to provide only the essentials I need.
// /lib/getBaseUrl.js
export default function baseUrl(req) {
let isWindowDefined = true;
if (typeof window === 'undefined') isWindowDefined = false;
let host;
if (req.headers.host) {
host = req.headers.host;
} else if (isWindowDefined) {
host = window.location.host;
} else {
host = 'localhost:3000';
}
let protocol = /^localhost(:\d+)?$/.test(host) ? 'http:' : 'https:';
if (req.headers['x-forwarded-host'] && typeof req.headers['x-forwarded-host'] === 'string') {
host = req.headers['x-forwarded-host'];
}
if (req.headers['x-forwarded-proto'] && typeof req.headers['x-forwarded-proto'] === 'string') {
protocol = req.headers['x-forwarded-proto'] + ':';
}
return protocol + '//' + host;
}I thought this would become my preferred method of using getInitialProps():
import getBaseUrl from '../lib/getBaseUrl';
// ...
MyApp.getInitialProps = async (context) => {
const baseUrl = getBaseUrl(context.ctx.req);
let settings = null;
let error = null;
const settingsResponse = await fetch(baseUrl + '/api/settings');
if (settingsResponse.ok) {
settings = await settingsResponse.json();
} else {
error = { message: 'An error occurred trying to fetch data!' };
}
return { settings, error };
};I soon discovered a flaw in my thinking about using the getBaseUrl() function when I tried to run a local build. During the local build process, it would try to connect via localhost:3000 and of course, fail. So, I tried starting the dev server and then running a local build... and it did succeed. However, once I tried to deploy the app to Vercel (where there is no way to start a dev server) of course the build process failed.
Solution:
It seems like the only way you can use getInitialProps() is if you have an external api as the source of your data. That led me to a better alternative for getting internal data to pass to components. Best of all, it eliminates the need for React context.
Fetching initial data and passing it down to components in the Layout component to the rescue. It includes using Vercel's npm package swr. You can pass the bits of data as props to each component that needs it.
In this Layout example, the data is being accessed from multiple endpoints... thus the need to alias the destructured data fetching returns from swr.
import useSWR from 'swr';
import PropTypes from 'prop-types';
import Header from './Header';
import Navbar from './Navbar';
import Footer from './Footer';
import Loading from './Loading';
const fetcher = (url) => fetch(url).then(res => res.json());
const Layout = ({ children }) => {
const { data: settings, error: error1 } = useSWR('/api/settings', fetcher);
const { data: otherData, error: error2 } = useSWR('/api/some-other-data', fetcher);
if (error1 || error2) return <h1>An error has occurred!</h1>;
if (!settings || !otherData) return <Loading />;
return (
<>
<Header someValue={settings.someValue} />
<Navbar someId={settings.someId} otherData={otherData} />
<main className="main-container">
{children}
</main>
<Footer contactEmail={settings.contactEmail} />
</>
);
};
Layout.propTypes = {
children: PropTypes.object,
};
export default Layout;Here is an example of how to use the data in components:
const Footer = ({ contactEmail }) => {
return (
<footer>
{contactEmail &&
<address>
<a href={'mailto:' + contactEmail}}>CONTACT US</a>
</address>
}
<p>© 2020 Some Website</p>
</footer>
);
};
export default Footer;