Booking backend project · Chapter 2 of 12

Migrations and seed

By the end of this chapter you can turn a Drizzle schema into SQL migration files, apply them to any database exactly once, fill a database with Parda's data in one transaction that you can run again and again, and say why a production database is changed by migrations, never by hand. Build it in your browser: a real editor and terminal, AI that explains, and tests that tell you when it works.

  • Part: The data
  • 3 tasks
  • TypeScript
  • Free chapter

Free with a Google or GitHub sign-in, like the first 2 chapters of every lab.

Why you need this

Chapter 1's tables are TypeScript. Postgres has not seen them: send "Is the database seeded?" against chapter 1's code and /health still says "counts":null. Parda needs those tables in at least four databases: Nikhil's laptop, yours, the test databases the Checks make, and the production database that will sell Diwali week's tickets. Each one must end up with exactly the same tables, and when the schema changes in December (a phone number for members, say), each one must change the same way, without losing the bookings already in it.

Typing CREATE TABLE into psql on each machine does not do that: someone forgets one, or runs it twice, and nobody can say which database has which change. And an empty database is not much use to Asha, who wants to try the admin pages with Kala Talkies' real 412 seats, or to Rukhsana, whose React site needs showtimes to show. Parda needs its data written down once, in code, and loaded the same way every time.

The lesson

Tour of the codebase

New since chapter 1 (your three files are marked in the file tree):

drizzle/0000_parda_schema.sql   the SQL that creates the 10 tables (generated)
drizzle/meta/                   drizzle-kit's snapshot and journal
src/db/seed-data.ts             Parda's people, 4 venues, 24 shows, 23 showtimes
src/db/setup.ts                 runs before npm run dev: migrate, then seed if empty
src/lib/seat-layout.ts          yours: every seat of a layout (Task 1)
src/db/migrate.ts               yours: apply the migrations (Task 2)
src/db/seed.ts                  yours: fill the tables (Task 3)

package.json has new scripts: db:generate, db:migrate, db:seed, and dev now runs src/db/setup.ts before it starts the app. Chapter 1's three schema files are now given: they are Nikhil's finished tables, the same ones you wrote.

From a schema to SQL: drizzle-kit generate

drizzle-kit reads the schema (drizzle.config.ts points it at src/db/schema/index.ts) and writes the SQL that creates it. Nikhil ran it once already, which is where drizzle/ came from. Run it yourself:

$ npm run db:generate
...
10 tables
users 6 columns 0 indexes 0 fks
screens 3 columns 0 indexes 1 fks
seats 5 columns 0 indexes 1 fks
...
No schema changes, nothing to migrate 😴

Nothing to do: drizzle-kit compared the schema with the snapshot it saved last time (drizzle/meta/0000_snapshot.json) and found them equal. It never looks at a database to decide; it compares two descriptions. The journal lists the migrations in order:

{
  "version": "7",
  "dialect": "postgresql",
  "entries": [
    {
      "idx": 0,
      "version": "7",
      "when": 1792805137912,
      "tag": "0000_parda_schema",
      "breakpoints": true
    }
  ]
}

And 0000_parda_schema.sql is chapter 1's CREATE TYPE, CREATE TABLE and ALTER TABLE ... ADD CONSTRAINT statements, the same SQL npm run schema:sql printed, ten tables of it.

A change is a new file

On a throwaway copy of the project, we gave users one more column, phone: text('phone'), and generated again:

$ npx drizzle-kit generate --name add_phone
...
[✓] Your SQL migration file ➜ drizzle/0001_add_phone.sql 🚀
$ cat drizzle/0001_add_phone.sql
ALTER TABLE "users" ADD COLUMN "phone" text;

Not a new CREATE TABLE users: the difference, one ALTER TABLE. A database that already has 600 members keeps them and gains an empty phone column. That is the whole idea of a migration: the history of the schema as a list of small, ordered steps, kept in git with the code that needs them.

Applying migrations, each once

A migration file is applied by Drizzle's migrator: give it a Drizzle object and the folder, and it runs every file the database has not had yet, in the journal's order. How does it know? It keeps a table of its own, drizzle.__drizzle_migrations, with one row per migration applied. After npm run db:migrate with Task 2 written:

$ npm run db:migrate
> tsx src/db/migrate.ts

Migrations applied: the database has 1
$ npm run --silent db:migrate
Migrations applied: the database has 1
$ psql -c "select id, hash, created_at from drizzle.__drizzle_migrations"
 id |                               hash                               |  created_at
----+------------------------------------------------------------------+---------------
  1 | 09d49260e06cedeba256f177cda5f07e25a86f22294ad60bff98aa6cdfc3eb71 | 1792805137912
(1 row)

The second run found 0000_parda_schema in that table and applied nothing. On the throwaway copy with 0001_add_phone, the same command applied only the new file: Migrations applied: the database has 2.

psql's \dt lists what the migration made:

           List of relations
 Schema |     Name      | Type  | Owner
--------+---------------+-------+-------
 public | booking_seats | table | app
 public | bookings      | table | app
 public | holds         | table | app
 public | payments      | table | app
 public | screens       | table | app
 public | seats         | table | app
 public | shows         | table | app
 public | showtimes     | table | app
 public | users         | table | app
 public | venues        | table | app
(10 rows)
A script that ends

runMigrations runs from npm run db:migrate, from setup.ts before the dev server, and from the Check. It makes a pool of its own from the url it is given, with one connection (migrations run one after another, there is nothing to share), and closes it at the end. Node keeps running while a pool holds an open connection: forget pool.end() and npm run db:migrate prints its line and then sits there. Close it in a finally, so a migration that fails closes it too. The Check counts the database's connections before and after your function to make sure.

Until Task 2 is written, the script stops at the starter:

$ npm run db:migrate

> parda-api@1.0.0 db:migrate
> tsx src/db/migrate.ts

/workspace/chapters/02-migrations-and-seed/src/db/migrate.ts:41
  throw new Error('Task 2: runMigrations is not written yet');
        ^

Error: Task 2: runMigrations is not written yet

A seed you can run again

seed-data.ts holds Parda's data as plain TypeScript: three people (Asha the admin, Neha and Pranav, with bcrypt hashes of their lab passwords), four venues with their real layouts, all 24 shows and the 23 showtimes of Friday 6 and Saturday 7 November at those venues. Each item has a ref, its id in Parda's catalogue (V01, S01), because the database gives its own ids.

A seed must give the same rows every time: delete everything, fill it again. Postgres's truncate empties tables in one statement; cascade lets it empty tables that others point at; and restart identity sets every serial counter back to 1. Without it, the second run's seats start where the first run's ended. A seed with restart identity left out, run on a seeded database:

 min  | max
------+------
 1033 | 2064
(1 row)

 id
----
(0 rows)

The seats are 1033 to 2064, and there is no showtime 9: every saved request that says "seat 79" or "showtime 9" now points at nothing. The Check catches that one:

after a second seed there is no showtime 9; the task wants Ghar Wapsi Express at Kala Talkies there (restart the id counters, so the ids start from 1 on every run)

After a seed with Task 3 written, showtime 9 and seat 79 are always these:

 id |       title        |    venue     |       starts_at        |        ends_at         |                    prices
----+--------------------+--------------+------------------------+------------------------+-----------------------------------------------
  9 | Ghar Wapsi Express | Kala Talkies | 2026-11-06 16:00:00+00 | 2026-11-06 18:37:00+00 | {"Front": 120, "Middle": 180, "Balcony": 240}
(1 row)

 id | row | number | class
----+-----+--------+--------
 78 | E   |      6 | Middle
 79 | E   |      7 | Middle
 80 | E   |      8 | Middle
(3 rows)

16:00 UTC is 9:30 pm in Pune; psql shows timestamptz in the server's time zone. The end is the start plus the show's 142 minutes plus 15 for the cleaners: 18:37 UTC, 12:07 am in Pune.

Transactions: all or nothing

A seed is many statements. If the 600th fails (a typo in a layout, a name that clashes), what should the database look like? Not half seeded. A transaction makes several statements one: either all of them happen, or none. Here is the idea on a smaller job, not the seed: two venues added together, the second with a name that is taken.

error: Failed query: insert into "venues" ("id", "name", "area", "kind", "created_at") values (default, $1, $2, $3, default)
cause: duplicate key value violates unique constraint "venues_name_unique"
without a transaction: Natya Ghar rows: 1
error: Failed query: insert into "venues" ("id", "name", "area", "kind", "created_at") values (default, $1, $2, $3, default)
cause: duplicate key value violates unique constraint "venues_name_unique"
with a transaction: Natya Ghar rows: 0

Without a transaction, Natya Ghar stayed behind, a venue nobody meant to add on its own. With db.transaction(async (tx) => { ... }), Drizzle sent BEGIN, ran the callback's queries on one connection, and when the callback threw, sent ROLLBACK: Postgres undid Natya Ghar too. When the callback returns, Drizzle sends COMMIT, and the callback's return value is the transaction's.

Two traps. Every query must go through tx, the object the callback gets: a query sent with the app's db runs on another connection, outside the transaction, and is not undone. And notice the error's shape: Drizzle wraps Postgres's error in its own ("Failed query: ..."), and Postgres's message and code are on its cause. Later chapters read the code from there.

npm run dev, from now on

dev runs src/db/setup.ts first: your runMigrations, then your seed when the database has no people. The first start:

$ npm run dev

> parda-api@1.0.0 dev
> tsx src/db/setup.ts && tsx watch src/server.ts

Dev database ready (1 migrations), seeded: {
  users: 3,
  venues: 4,
  screens: 4,
  seats: 1032,
  shows: 24,
  showtimes: 23
}

The next starts leave your data alone: Dev database ready (1 migrations, 3 people). To start the data over, npm run db:seed. "Is the database seeded?" now answers:

{"ok":true,"database":"up","counts":{"users":3,"venues":4,"seats":1032,"shows":24,"showtimes":23}}

"Log in (not built yet)" still answers 404, Cannot POST /auth/login: that is chapter 3.

Your turn

  • Task 1, seat-layout.ts: every seat of a layout, numbered from 1 in each row. The trap: seats is how many, so a row of 18 gives 1 to 18.
  • Task 2, migrate.ts: the migrator on a pool of its own, the count from drizzle.__drizzle_migrations, and the pool closed in finally. Nikhil will ask: why does Parda apply migration files, in order, instead of letting drizzle-kit push the schema straight into the production database?
  • Task 3, seed.ts: one transaction on the database you are given: truncate with restart identity, then people, venues with screens and seats, shows and showtimes. The trap: every query through tx.

Try each with the scripts (npm run db:migrate, npm run db:seed), look with psql, start the app and send "Is the database seeded?", then press Check. It makes an empty database of its own, migrates it with your function, seeds it twice and looks at showtime 9 and seat 79.

What you build: 3 tasks

Each task has tests. You write the code in the lab's editor and press Check.

  1. Task 1

    seat-layout.ts: every seat of a layout

    Write seatsForLayout(layout) in src/lib/seat-layout.ts. layout is a list of rows, each { row, seats, class }: the row's letter, how many seats it has, and its seat class. Return every seat as { row, number, class }, in the layout's order (front row first), numbered from 1 in each row. For A with 2 Front seats and B with 1 Middle seat that is A1 Front, A2 Front, B1 Middle. An empty layout gives an empty list.

    Throw an Error (any message) when a row is in the layout twice, and when a row's seats is not a whole number of at least 1 (0, -2, 1.5). The Check also gives it Kala Talkies' layout from seed-data.ts: 412 seats, and the 79th is E7, Middle.

  2. Task 2 · interview question

    Interview: migrate.ts, apply the migrations

    Write runMigrations(url) in src/db/migrate.ts. Make a pg Pool of its own for url with at most 1 connection, run Drizzle's migrator (migrate, already imported) on it with migrationsFolder set to the given MIGRATIONS_FOLDER, then count the rows of the table drizzle.__drizzle_migrations and return that count as a number. Close the pool when you are done, even when a step throws.

    The Check runs it on an empty database of its own: afterwards the 10 tables exist and it returns 1. Run a second time it applies nothing and returns 1 again, and the table still has one row. When it has returned, no connection it opened may still be open.

    Nikhil will ask: why does Parda apply migration files, in order, instead of letting drizzle-kit push the schema straight into the production database?

  3. Task 3

    seed.ts: fill the database with Parda's data

    Write seed(database) in src/db/seed.ts. Inside one transaction on the database it is given (database.transaction), first empty all ten tables and restart their id counters with one SQL statement (truncate the tables, restart identity, cascade). Then insert, from seed-data.ts: the people (seedUsers, as they are); each venue (seedVenues), each of its screens, and each screen's seats from seatsForLayout of the screen's layout; the shows (seedShows, without ref); and the showtimes (seedShowtimes), whose show and venue are refs (S01, V01) you turn into the ids the inserts gave back. startsAt is new Date of the given text; endsAt is startsAt plus the show's minutes plus config.turnaroundMinutes (15).

    Return the counts { users: 3, venues: 4, screens: 4, seats: 1032, shows: 24, showtimes: 23 }. Run twice it must give the same rows with the same ids: showtime 9 is Ghar Wapsi Express at Kala Talkies, starting 2026-11-06T16:00:00.000Z and ending 2026-11-06T18:37:00.000Z, and seat 79 is E7 of screen 1. A hold made between two seeds is gone after the second. The Check passes its own database and watches that seed uses its transaction; then GET /health shows 1032 seats and 23 showtimes.

What you will learn

  • drizzle-kit generate: compares your schema with the last snapshot and writes the SQL that gets from one to the other.
  • Migrations: numbered SQL files, applied in order, each once; Postgres remembers which ones it has.
  • Drizzle's migrator: applies the files from code, with a pool of its own that it closes.
  • A seed: Parda's data, loaded from TypeScript, the same rows and ids every run.
  • Transactions: several statements that happen all together or not at all.

More in The data

Build "Migrations and seed" yourself

Parda sells tickets for Pune's own cinemas and stages. You build its booking API, one module a chapter, and take the whole codebase home. A real editor and terminal in your browser, AI that explains, and a Check that says when it works.

Free with a Google or GitHub sign-in, like the first 2 chapters of every lab.