Ordering backend project · Chapter 2 of 12
Migrations and seed
By the end of this chapter you can turn chapter 1's models into a migration that Alembic applies to any database exactly once, fill a database with DabbaDrop's kitchens, zones and launch-week slots in one transaction that you can run again and again, and say why a production database is changed by migration files, never by the app guessing at startup. 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
- Python
- 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 Python classes. Postgres has not seen them: start chapter 1's app on an empty database and "Is the database seeded?" answers {"ok":true,"database":"up","counts":null}. DabbaDrop needs those tables in at least four databases: Kunal's laptop, yours, the throwaway databases the Checks make, and the production database that will take launch week's orders. 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 orders already in it.
Typing CREATE TABLE into psql on each machine does not do that: someone forgets one, someone runs it twice, and nobody can say which database has which change. And an empty database is no use to Bhavna, who wants to try the admin routes with Andheri Kitchen's real 12 dishes, or to anyone testing the 12:30 Goregaon slot that overflowed on 19 October. DabbaDrop needs its data written down once, in code, and loaded the same way every time: 3 people, 3 kitchens, 30 dishes, 6 zones and the 90 lunch slots of Monday 2 to Friday 6 November.
The lesson
Tour of the codebase
New since chapter 1 (your three files are marked):
alembic.ini Alembic's settings: where the migrations live
alembic/env.py connects, and compares with Base.metadata
alembic/versions/0001_dabba_schema.py generated: the 10 tables
app/lib/time.py MUMBAI, mumbai_day, clock
app/db/seed_data.py people, kitchens and dishes, zones, windows, Advait's order
app/db/setup.py migrate, then seed if nobody is in the database
app/__main__.py changed: python -m app runs setup() first
app/lib/delivery_windows.py yours: a day's slots (Task 1)
app/db/migrate.py yours: apply the migrations (Task 2)
app/db/seed.py yours: fill the tables (Task 3)
Chapter 1's three model files are now given: Kunal's finished tables, the same ones you wrote.
From models to a migration: Alembic
Alembic is SQLAlchemy's migration tool. alembic/env.py imports every model so that Base.metadata knows all ten tables, then connects to DATABASE_URL. Kunal ran alembic revision --autogenerate once on an empty database: Alembic compared Base.metadata with what it found (nothing) and wrote 0001_dabba_schema.py, a file with an upgrade() of op.create_table(...) calls, one per table, with the same columns, uniques and checks you wrote in chapter 1, and a downgrade() that drops them. alembic history lists the chain:
$ alembic history
<base> -> 0001 (head), dabba schema
Each file names its own revision and the down_revision before it, so the files form a list in order. head is the newest.
A change is a new file
On a throwaway copy of the project, with its database already at 0001, we gave User one more column, phone: Mapped[str | None] = mapped_column(String), and asked Alembic again:
$ alembic revision --autogenerate -m "add phone"
INFO [alembic.runtime.migration] Context impl PostgresqlImpl.
INFO [alembic.runtime.migration] Will assume transactional DDL.
INFO [alembic.autogenerate.compare.tables] Detected added column 'users.phone'
Generating /workspace/chapters/w-wa-02x/alembic/versions/3cf4d749b60e_add_phone.py ... done
The new file says down_revision = '0001', and its upgrade() is one line: op.add_column('users', sa.Column('phone', sa.String(), nullable=True)). Not a new CREATE TABLE users: the difference. To read the SQL it will send, ask for it instead of running it:
$ alembic upgrade 0001:head --sql
BEGIN;
-- Running upgrade 0001 -> 3cf4d749b60e
ALTER TABLE users ADD COLUMN phone VARCHAR;
UPDATE alembic_version SET version_num='3cf4d749b60e' WHERE alembic_version.version_num = '0001';
COMMIT;
A database that already holds a thousand 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. Autogenerate is a draft, so you read it before you commit it: it cannot tell a renamed column from one dropped and one added.
Applying migrations, each once
How does Alembic know what a database has had? It keeps a one-row table, alembic_version, holding the newest revision applied. Upgrading reads it, walks the chain from there to head, runs each upgrade() and moves the row on, all inside one transaction (that is the "transactional DDL" line: Postgres can undo a CREATE TABLE). After Task 2, on a fresh database:
$ python -m app.db.setup
INFO [alembic.runtime.migration] Context impl PostgresqlImpl.
INFO [alembic.runtime.migration] Will assume transactional DDL.
INFO [alembic.runtime.migration] Running upgrade -> 0001, dabba schema
Migrations: 1 applied
Seeded: 3 users, 3 kitchens, 30 dishes, 6 zones, 90 slots, 1 subscriptions, 1 orders
$ python -m app.db.setup
INFO [alembic.runtime.migration] Context impl PostgresqlImpl.
INFO [alembic.runtime.migration] Will assume transactional DDL.
Migrations: up to date
$ psql -c "select * from alembic_version"
version_num
-------------
0001
(1 row)
The second run found 0001 there and applied nothing. \dt lists the ten tables and alembic_version, all owned by app.
Counting what you applied
command.upgrade(cfg, "head") does the work but returns nothing, and setup.py wants to print how many it applied. So you ask before you upgrade. MigrationContext.configure(conn).get_current_revision() reads alembic_version on a connection (None when the table is not there), and ScriptDirectory.from_config(cfg) reads the files, so it can list the revisions between head and the current one. On the throwaway copy, which has two files:
w_wa_z current: None
to apply: ['3cf4d749b60e', '0001']
w_wa current: 0001
to apply: ['3cf4d749b60e']
Newest first, and only what is missing.
An engine you dispose
run_migrations runs from setup.py before every python -m app, and from the Check, so it must clean up after itself. create_engine(url) does not connect yet: it makes a pool. The first engine.connect() opens a real connection to Postgres, and closing it in the with block only hands it back to the pool, which keeps it open for next time. Here is a pool after one connect, with a second engine watching pg_stat_activity:
Pool size: 5 Connections in pool: 1 Current Overflow: -4 Current Checked out connections: 0
other connections open: 1
after dispose: 0
engine.dispose() closes them. Put it in a finally, so a migration that fails closes them too. The Check counts the database's connections after your function; leave one behind and it says:
after run_migrations 1 connection(s) to the database are still open; the task wants the engine disposed when it is done
Until Task 2 is written, python -m app stops at the starter:
$ python -m app
...
raise NotImplementedError("Task 2: run_migrations is not written yet")
NotImplementedError: Task 2: run_migrations is not written yet
Mumbai moments
The seed's slots come from windows of Mumbai time, "12:00-12:30", on each day of launch week. The given parse_window turns a window into two time objects and refuses anything else:
print(parse_window("19:00-19:30"))
parse_window("dinner")
(datetime.time(19, 0), datetime.time(19, 30))
Traceback (most recent call last):
...
ValueError: 'dinner' is not a window like 12:00-12:30
A date and a time become one datetime with datetime.combine. Leave out the time zone and you get a naive datetime: 19:00, but 19:00 where? Python cannot compare it with a real moment, and Postgres would read it in whatever zone its connection happens to use. A Friday dinner time, both ways:
friday = date(2026, 11, 6)
naive = datetime.combine(friday, time(19, 0))
aware = datetime.combine(friday, time(19, 0), tzinfo=MUMBAI)
print(naive.isoformat(), "|", aware.isoformat())
print(aware.astimezone(UTC).isoformat())
print(naive < aware)
2026-11-06T19:00:00 | 2026-11-06T19:00:00+05:30
2026-11-06T13:30:00+00:00
Traceback (most recent call last):
...
TypeError: can't compare offset-naive and offset-aware datetimes
The second one is aware: combine takes tzinfo=MUMBAI as a keyword, and the moment is 13:30 UTC everywhere. timestamptz stores that instant. The Check compares in UTC, and for a naive answer it says:
slots_for_day returned 2026-11-02T12:00:00, a datetime with no time zone; the task wants Mumbai's (MUMBAI, +05:30)
Two windows overlap when each one starts before the other one ends: 12:00 to 12:45 and 12:30 to 13:00 do (12:00 is before 13:00, and 12:30 is before 12:45); 12:00 to 12:30 and 12:30 to 13:00 only touch (12:30 is not before 12:30). A day planned with overlapping windows would give the riders two slots for the same half hour, so the function refuses it before the seed writes anything.
A seed you can run again
seed_data.py holds DabbaDrop's data as plain Python: three people (Bhavna the admin, Tara and Advait, with bcrypt hashes of their lab passwords), three kitchens each with its "dishes", six zones with the kitchen that cooks for them and their "capacity", WEEK_STARTS, WEEK_DAYS and WINDOWS, Advait's subscription and his one paid order. The database gives the ids, in the order rows arrive, which is why the data's order matters: Tara is user 2, and slot 17 is Goregaon (zone 2) on Monday, the second window.
A seed must give the same rows every time: empty everything, fill it again. Postgres's TRUNCATE empties tables in one statement; CASCADE lets it empty tables that others point at; RESTART IDENTITY sets each table's id counter back to 1. The counters live outside the rows, so without it the second run's kitchens are 4 to 6, while the data's zones still say kitchen 1. The Check found exactly that in a seed that left RESTART IDENTITY out:
seed(db) a second time raised an error: Postgres said: insert or update on table "zones" violates foreign key constraint "zones_kitchen_id_fkey"
After a seed with Task 3 written, the ids are always these:
id | zone | starts_at | ends_at | capacity
----+----------+---------------------------+---------------------------+----------
16 | Goregaon | 2026-11-02 12:00:00+05:30 | 2026-11-02 12:30:00+05:30 | 40
17 | Goregaon | 2026-11-02 12:30:00+05:30 | 2026-11-02 13:00:00+05:30 | 40
18 | Goregaon | 2026-11-02 13:00:00+05:30 | 2026-11-02 13:30:00+05:30 | 40
(3 rows)
psql shows +05:30 because the lab's server runs on Mumbai time.
Transactions in a session
A seed is hundreds of statements. If the 200th fails, the database should not be half seeded. A transaction makes many statements one: either all of them happen, or none.
A SQLAlchemy Session is the unit of work. db.add(obj) only remembers the object; nothing is sent. The first statement the session sends opens a transaction (BEGIN (implicit) in the log). db.flush() sends what was added so far as INSERT ... RETURNING id, inside that transaction, and puts the new id on the object. db.commit() flushes and ends it with COMMIT; db.rollback() ends it with ROLLBACK, and Postgres undoes every statement since BEGIN. With the engine's log turned on, on a made-up zone:
after add: None
...
BEGIN (implicit)
INSERT INTO zones (name, kitchen_id) VALUES (%(name)s::VARCHAR, %(kitchen_id)s::INTEGER) RETURNING zones.id
[generated in 0.00008s] {'name': 'Powai', 'kitchen_id': 1}
after flush: 11
ROLLBACK
Id 11, though there are six zones: earlier tries had taken 7 to 10. A counter never goes back on rollback, which is one more reason the seed restarts identity.
So when do you commit? Two zones added together, the second with a name that is taken, once with a commit after each and once with one commit at the end:
(psycopg.errors.UniqueViolation) duplicate key value violates unique constraint "zones_name_key"
commit after each: Powai rows: 1
(psycopg.errors.UniqueViolation) duplicate key value violates unique constraint "zones_name_key"
one transaction: Powai rows: 0
The error reaches Python as SQLAlchemy's IntegrityError, with psycopg's UniqueViolation inside it. With a commit after each, Powai stayed behind, half of a job. With one commit at the end, the rollback undid it. The seed has the same trap: commit after the first kitchen and a later failure leaves the database truncated and nearly empty. The Check slips a broken order into the data to see:
a seed that failed half way left {"users": 3, "slots": 0}; the task wants everything undone, the old rows back: one transaction, rolled back
python -m app, from now on
python -m app runs setup() first: your run_migrations, then your seed when the database has no people, then uvicorn. The next starts print Migrations: up to date and leave your data alone. "Is the database seeded?" now answers:
{"ok":true,"database":"up","counts":{"users":3,"kitchens":3,"dishes":30,"zones":6,"slots":90,"orders":1}}
"Log in (not built yet)" still answers 404, {"detail":"Not Found"}: that is chapter 3.
Your turn
- Task 1, delivery_windows.py: each window as a pair of aware Mumbai datetimes, in the order given, refusing overlaps. The trap:
combinewithouttzinfo. - Task 2, migrate.py: the current revision, the pending list, then
command.upgrade, and the engine disposed infinally. Kunal will ask: why do teams commit migration files and run them on deploy, instead of letting the app call Base.metadata.create_all() when it starts? - Task 3, seed.py: one transaction on the session you are given: truncate with restart identity, then people, kitchens and dishes, zones and slots, the subscription, Advait's order. The trap: flush for ids, commit once.
Try each with python -m app.db.setup (for a fresh database, stop the app, then dropdb dabba && createdb -O app dabba), look with psql, start the app with python -m 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 Tara and slot 17.
What you build: 3 tasks
Each task has tests. You write the code in the lab's editor and press Check.
- Task 1
delivery_windows.py: a day's delivery slots
Write slots_for_day(day, windows) in app/lib/delivery_windows.py. windows are strings like "12:00-12:30"; for each one, in the order given, return the pair (starts_at, ends_at) as datetimes on that day in Mumbai time (tzinfo=MUMBAI, offset +05:30), all in a list. slots_for_day(date(2026, 11, 2), ["12:00-12:30"]) is [(2026-11-02 12:00+05:30, 2026-11-02 12:30+05:30)].
Use the given parse_window for each window: it raises ValueError for text that is not a window and for a window that ends before it starts. Two windows that overlap (or the same window twice) are a ValueError too; windows that only touch, 12:00-12:30 and 12:30-13:00, are fine. No windows gives [].
The Check calls it with two windows, with windows out of order, with overlapping ones, with "noon" and with "13:00-12:30", and compares the moments in UTC: 12:00 in Mumbai is 06:30 UTC.
- Task 2 · interview question
Interview: migrate.py, apply the migrations
Write run_migrations(url=None) in app/db/migrate.py. It brings the database at url (settings.database_url when url is None) up to the newest Alembic migration and returns how many migrations it applied, as an int: 1 on an empty database (there is one migration, 0001), 0 when the database is up to date.
Use the given alembic_config(url) for Alembic's Config and command.upgrade(cfg, "head") to apply. To count, before upgrading, read the database's current revision with MigrationContext on a connection of an engine you create (None when the database is empty), and list the revisions still to apply with ScriptDirectory.from_config(cfg).iterate_revisions("head", current). Dispose of the engine when you are done, even when something fails.
The Check calls it on an empty database and wants every table, alembic_version holding 0001, the number 1 and no connection left open; then twice more, with the url and with none, wanting 0 each time and still no connection open.
Kunal will ask: why do teams commit migration files and run them on deploy, instead of letting the app call Base.metadata.create_all() when it starts?
- Task 3
seed.py: fill the database with DabbaDrop's data
Write seed(db) in app/db/seed.py. In one transaction: TRUNCATE every table in the given TABLES with RESTART IDENTITY CASCADE; add a User for each of data.PEOPLE; each Kitchen of data.KITCHENS and then a Dish for each of its "dishes" (with the kitchen's id); each Zone of data.ZONES and then its slots: for each of data.WEEK_DAYS days from data.WEEK_STARTS, a Slot for every (starts_at, ends_at) of slots_for_day(day, data.WINDOWS), with the zone's "capacity"; a Subscription for each of data.SUBSCRIPTIONS; each Order of data.ORDERS (without its "items" and "payment" keys), then an OrderItem for each item and its Payment. Commit once at the end; if anything fails, roll back and raise.
Return the counts {"users": 3, "kitchens": 3, "dishes": 30, "zones": 6, "slots": 90, "subscriptions": 1, "orders": 1}.
The Check seeds a migrated database and wants the counts, the rows seen from another connection, Tara with id 2 and slot 17 as Goregaon, Mon 2 Nov, 12:30 Mumbai time; seeds again and wants the same ids; then puts a broken order in the data and wants seed to raise and leave the old rows as they were. Then GET /health must count 90 slots.
What you will learn
- Alembic's autogenerate: compares your models with a database and writes the Python that gets from one to the other.
- Migrations: numbered files, applied in order, each once; Postgres remembers the newest in alembic_version.
- Alembic from code: command.upgrade, the current revision, the revisions still to apply, and an engine you dispose.
- Aware datetimes: a Mumbai moment that Postgres stores as the right instant.
- A seed: DabbaDrop's data, loaded from Python, the same rows and ids every run.
- Transactions in a SQLAlchemy session: add, flush, commit once, roll back on any error.
More in The data
Build "Migrations and seed" yourself
DabbaDrop delivers tiffins across Mumbai. You build its ordering 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.