DEV Community

Dreiev
Dreiev

Posted on

Building a game server tracker: what I learned exposing a public JSON API

#api #mysql #node #gaming

A few months ago I shipped L2 Calendar, a tracker for Lineage 2 private server openings. Players kept asking the same thing: "when's the next Interlude server?" and server owners had no good place to announce openings. The site solves that. Last week I opened up the data as a public JSON API, and I want to walk through the decisions that mattered — because two of them bit me harder than I expected.

The data model is deceptively simple

A server opening looks like this: name, website, chronicle (Interlude, High Five, Classic...), rate (x5, x1200, whatever the owner decided), opening date, and labels like "PvP" or "RP". That's it. Four tables in MySQL: servers, chronicles, labels, and a many-to-many server_labels_map.

The trap wasn't the schema. It was the dates.

mysql2's dateStrings setting, and why DATETIME still bites you

I had this in my connection config:

dateStrings: ['DATE']
Enter fullscreen mode Exit fullscreen mode

Sounds like "give me all dates as strings, please." It doesn't. It means: convert columns typed as DATE to strings. DATETIME columns still come back as JavaScript Date objects.

Why does that matter? My display code treated every date as a string:

const isUtc = value.endsWith('Z');
Enter fullscreen mode Exit fullscreen mode

One day a TypeError: value.endsWith is not a function started showing up in production for rows where opening_datetime_utc was populated. A DATETIME column came back as a Date object, .endsWith doesn't exist on it, crash.

The fix was boring but worth writing down:

const rawUtc = row.opening_datetime_utc;
const utc = rawUtc instanceof Date ? rawUtc.toISOString() : rawUtc;
Enter fullscreen mode Exit fullscreen mode

mysql2's type coercion is per-column-type, not per-config-intent. If you have mixed DATE and DATETIME columns feeding the same code path, normalize at the boundary or you'll debug this at 2am eventually.

The API itself is one route handler

I didn't build a separate service. The API is a route handler in the same Next.js app:

export async function GET(request: NextRequest) {
  const chronicle = searchParams.get('chronicle');
  // one query with a LEFT JOIN to chronicles, one for labels
  return NextResponse.json(servers, { headers: corsHeaders });
}
Enter fullscreen mode Exit fullscreen mode

Filter by chronicle slug, join labels in a second query, ship the JSON. There's a second OPTIONS handler for CORS preflight with a 24h Access-Control-Max-Age so browsers don't re-preflight on every request.

464 servers tracked right now, response is a few KB, no auth. If the site dies, the API dies — and I'm fine with that trade until there's a reason to split it.

Opening CORS on purpose

Default Next.js APIs are same-origin. For a public API that people should use from browsers and Discord bots, that's useless. Two lines on the GET handler:

const corsHeaders = {
  'Access-Control-Allow-Origin': '*',
  'Access-Control-Allow-Methods': 'GET, OPTIONS',
};
Enter fullscreen mode Exit fullscreen mode

Everything else in the app — forms, admin routes, auth — keeps the default policy. Opening CORS on one read-only route costs nothing security-wise because the data is already public on the homepage; it just removes the friction for anyone building a dashboard or a Discord bot on top of it.

What I'd tell anyone doing this with game data

Three things, in order of how much they hurt when ignored:

  1. Normalize dates at the data boundary. Game servers live and die by opening dates and timezones. Owners submit "opening at midnight my time" and your users are in twelve timezones. Store UTC, convert at display, and treat every date coming out of mysql2 as suspicious until you've checked its type.
  2. Cache the expensive query. The homepage hammers the same data the API serves. One in-memory cache with a TTL both can share — invalidate on admin edits — beats two separate query paths that drift.
  3. Give the data away. The tracker gets more useful when other people build on it. The Discord bot someone else writes drives traffic back here. Hoarding the JSON would've saved me nothing.

If you play Lineage 2 — or you just want to poke at the API — the endpoint is https://l2calendar.com/api/servers?lang=en, no key, CORS open. And if you run an L2 server, listings are free.

Questions about the mysql2 date handling or the CORS setup are welcome in the comments.

Top comments (0)