05 — Relational Databases — Tables, Migrations, and the N+1 Trap

"SQL queries against tables" was how I summarized relational databases, and the summary made me feel done. The split that changed that: data lives in tables governed by a schema, the schema evolves through versioned migrations, and the single failure mode that bites every backend is the N+1 query. [1] Once those three were distinct, "I know SQL" stopped feeling like enough.

The frame that helped is that a relational database is a contract, not just a store. The schema declares what tables exist, what columns they have, what types those columns are, and how rows in one table refer to rows in another (foreign keys). The query engine enforces that contract. Everything else — PostgreSQL, MySQL, SQLite, Oracle — is a different implementation of the same relational idea, differing in features, scale, and licensing, not in the core model.

Tables, rows, and the relational contract

A relational database organizes data into tables (relations) — grids of rows and columns, where each row is one entity and each column is a typed field [1]. The "relational" part is the explicit links between tables via keys: a posts table has a user_id column that references the id column of users. The database can enforce that link (foreign key constraints) so a post can never point at a non-existent user.

The contract the schema provides is what makes relational databases safe for complex data:

• Typed columns — users.email is text, users.created_at is a timestamp. Wrong types are rejected.
• Constraints — NOT NULL, UNIQUE, CHECK. The database refuses data that violates them.
• Foreign keys — relationships must reference real rows.
• Transactions — groups of operations succeed or fail atomically (more on this in the databases post).

This contract is why relational databases dominate anything with structured, consistency-critical data — finance, e-commerce, user accounts. The schema is a guarantee the application code can rely on.

The major engines: same model, different trade-offs

The roadmap lists several engines, and they cluster by trade-off rather than by feature gaps:

• PostgreSQL is the advanced open-source one — robust, extensible, standards-compliant, with complex query support, custom types, full-text search, and strong concurrency [3]. It's the default recommendation when there's no specific reason to pick something else. If I'm starting a new project, Postgres is usually the answer.
• MySQL / MariaDB — the speed-and-simplicity open-source option, the M in LAMP, running a huge fraction of the web [4][5]. MySQL is Oracle-maintained; MariaDB is the community fork by the original team, drop-in compatible. MySQL earned its dominance through speed and ease of use; modern Postgres has largely caught up and surpassed it on features.
• SQLite — the embedded one. Serverless, file-based, no separate process; the whole database is one file [6]. SQLite is what runs mobile apps, desktop apps, small sites, and tests. It's not for concurrent-write workloads at scale, but for anything single-writer it's genuinely excellent.
• MS SQL Server and Oracle — the enterprise ones [7]. Powerful, feature-rich, expensive, common in large enterprises and legacy environments. If the job is at a bank or a giant corp, one of these is probably already there.

The thing to internalize is that the SQL they speak is mostly portable. SELECT, INSERT, UPDATE, JOIN, and GROUP BY work the same across all of them; the differences are in extensions, performance characteristics, and operational tooling. Learn standard SQL once and the engines become dialects.

Migrations: evolving the schema in versioned steps

A schema isn't static — it changes as the application grows. A new feature needs a new column; a refactoring renames a table; an index gets added for performance. Database migrations are the controlled, repeatable way to make those changes [2].

A migration is a script with two halves: an up that applies a change (create table, add column) and a down that reverses it. Migrations are versioned and applied in order, so every environment — dev, staging, production — can be brought to the same schema state by running the same sequence:

-- migration 003: add email_verified column to users
ALTER TABLE users ADD COLUMN email_verified BOOLEAN NOT NULL DEFAULT FALSE;

The point of migrations is that schema changes become code: reviewable, testable, and reproducible. Without them, schema drift between environments is a near-guaranteed production incident. With them, a new developer runs migrate up and has the exact database the application expects.

A rule I learned the hard way: migrations must be additive and backward-compatible. Adding a column with a default is safe; dropping or renaming a column that running code still references is not. The discipline is to deploy the new code that tolerates the schema change, then the change, then the code that depends on it — across two or more releases, never in one.

The N+1 problem: the failure mode that finds every backend

This is the trap I want flagged because it finds everyone. The N+1 problem happens when code fetches a list of items and then, for each item, makes a separate query to fetch its related data — producing 1 query for the list plus N queries for the items, instead of one join [8].

The classic shape: I want to render 50 posts with each post's author's name. The wrong way fetches the 50 posts, then loops and fetches each author one at a time:

// N+1 in the wild
const posts = await db.query("SELECT * FROM posts LIMIT 50"); // 1 query
for (const post of posts) {
  post.author = await db.query("SELECT * FROM users WHERE id = $1", [post.user_id]); // 50 queries
}

51 round trips to the database. With an ORM's lazy-loading — an ORM (object-relational mapper) is a layer that turns database tables into objects in your code — this happens invisibly: accessing post.author triggers a query per post, and the developer never sees the loop that's hammering the database.

The fix is almost always a join or a batch fetch — one query that pulls the posts and their authors together:

// one query, not 51
const posts = await db.query(`
  SELECT posts.*, users.name AS author_name
  FROM posts
  JOIN users ON users.id = posts.user_id
  LIMIT 50
`);

One round trip. The N+1 is the single most common performance bug in backend code that uses an ORM, and the reason I always check the generated SQL when a page is slow.

JSON APIs and gRPC: how data leaves the database

Two related nodes round out the picture: how relational data gets to clients. JSON APIs are the dominant wire format — relational rows are serialized to JSON objects and sent over HTTP, the universal lingua franca between frontend and backend. gRPC is the high-performance alternative — a binary, strongly-typed RPC framework using Protocol Buffers, where client and server can be in different languages and the contract is the proto definition [9][10]. gRPC is common for internal service-to-service communication where every millisecond counts; JSON-over-HTTP is the default for public APIs where human-readability and ubiquity matter more.

How I use this

Three habits capture the practical takeaway:

• Postgres as the default. Unless there's a specific reason (embedded → SQLite, enterprise mandate → Oracle/SQL Server, legacy → MySQL), I start with Postgres. It's rarely the wrong choice.
• Migrations are code. I commit them, review them, and make them additive. Schema drift is a production-killer I don't risk.
• Hunt N+1s proactively. When using an ORM, I watch the query log on any endpoint that touches related data. A slow page that gets faster when I add a join is the signature.

The relational model isn't going anywhere, and the reason is the contract: a typed, constrained, transactional store that application code can trust. Everything above — APIs, caching, scaling — is built to compensate for the fact that the database is the source of truth and therefore the slowest, most carefully guarded layer.

References

[1] IBM, "Relational Databases," 2024. [Online]. Available: https://www.ibm.com/cloud/learn/relational-databases

[2] "Schema migration," Wikipedia. [Online]. Available: https://en.wikipedia.org/wiki/Schema_migration

[3] PostgreSQL Global Development Group, "PostgreSQL." [Online]. Available: https://www.postgresql.org/

[4] Oracle, "MySQL Documentation." [Online]. Available: https://dev.mysql.com/doc/

[5] "MariaDB vs MySQL," guru99. [Online]. Available: https://www.guru99.com/mariadb-vs-mysql.html

[6] "SQLite." [Online]. Available: https://www.sqlite.org/index.html

[7] Microsoft, "SQL Server tutorials." [Online]. Available: https://docs.microsoft.com/en-us/sql/sql-server/tutorials-for-sql-server-2016

[8] "What is the N+1 Problem," PlanetScale. [Online]. Available: https://planetscale.com/blog/what-is-n-1-query-problem-and-how-to-solve-it

[9] "Introducing JSON," json.org. [Online]. Available: https://www.json.org/json-en.html

[10] gRPC Authors, "gRPC." [Online]. Available: https://grpc.io/