Nakodo has 25 tables and 318 columns. Getting there took 32 migrations, generated by drizzle-kit from a TypeScript schema file, and the drizzle/ folder they live in is the second largest directory in the repository.
Here is the ratio that started this post:
32 .sql files 35,819 bytes
33 .json files 2,495,839 bytes
Seventy times more generated metadata than SQL. None of the JSON runs against the database. All of it is in git, on purpose, and deleting it would break the next migration. This is a post about what that JSON is and what it buys.
What the SQL looks like
All 32 files together are 36 KB and 225 statements. The smallest is 51 bytes:
ALTER TABLE "campaigns" ADD COLUMN "logo_url" text;
The largest is the first one, 8.4 KB, which creates nine tables. The median is one or two ALTER TABLE lines. The 193 gaps between them carry a --> statement-breakpoint comment, which is drizzle's marker for "run these one at a time" and is on for every entry in the journal.
Twenty-nine of the 32 have machine-picked names like 0025_daily_tarot and 0014_wooden_ultragirl. Three have names I passed by hand, and all three are from moments when I knew the migration would be read again later: two that undo each other, and one that adds a column whose meaning is not obvious from the column name.
What the JSON is
Two kinds of file. One _journal.json, 4.7 KB, which is the ordered list:
{
"idx": 0,
"version": "7",
"when": 1790858704014,
"tag": "0000_kind_rick_jones",
"breakpoints": true
}
Thirty-two entries, the first on 1 October 2026 and the last on 10 October, which is a rate I would not recommend to anybody but is what building the first version of a product looks like.
Then 32 snapshots, one per migration, each a complete description of the schema as it was after that migration. Not a diff. The whole thing: every table, every column with its type and default and nullability, every index with its columns and method, every foreign key with its delete rule.
That is why they grow:
| Snapshot | Tables | Columns | Indexes | FKs | Bytes |
|---|---|---|---|---|---|
| 0000 | 9 | 118 | 9 | 8 | 31,212 |
| 0006 | 22 | 260 | 36 | 25 | 79,125 |
| 0013 | 23 | 282 | 38 | 22 | 82,534 |
| 0021 | 24 | 293 | 45 | 22 | 88,094 |
| 0031 | 25 | 318 | 55 | 24 | 98,489 |
A migration that adds one column rewrites a 98 KB file to change four lines of it. Every one of those rewrites is a new blob in git.
Why the whole schema, and not a diff
Because drizzle-kit generate never looks at your database. It compares the schema you wrote in TypeScript against the newest snapshot, and the difference between those two is the migration. The database is only involved at drizzle-kit migrate, and by then the SQL is already written.
That design has one large benefit and one sharp edge.
The benefit is that generating a migration needs no connection, no credentials and no running Postgres. It works on a plane. It also means two developers generate identical SQL from identical source, because the input is two files in the repository rather than whatever state somebody's local database drifted into.
The sharp edge is that the snapshot, not the database and not the SQL, is the thing drizzle believes. If I hand-edit a generated .sql file (to add a USING clause to a type change, say, which is a normal reason to want to), the snapshot still describes the outcome drizzle intended, so the next generate produces a diff from a state the database is not in. Nothing warns you. The rule I follow, which the README states as flatly as I can put it, is that a migration file is generated output and is not edited after it is written.
The id chain, and the thing it catches
Every snapshot carries its own id and the prevId of the one before it:
0000_snapshot.json id 037cdf98 prev 00000000
0001_snapshot.json id 74345596 prev 037cdf98
0002_snapshot.json id 1b92e79c prev 74345596
I walked all 32 and the chain is intact, with the all-zeros id as the root. This is not decoration. It is what makes a parallel generate detectable: if two branches both generate from snapshot 30, both new snapshots claim the same prevId, and merging them produces two files that say they came after the same parent. You still have to resolve that by hand, and the resolution is nearly always to delete one migration, regenerate it on top of the other, and renumber. But you find out at merge time rather than at deploy time.
Two snapshots that are the same file
I hashed every snapshot with its id and prevId removed. Thirty of the 32 hashes are unique. Two are identical: 0009 and 0011.
That is the pair of hand-named migrations. One added two columns and reshaped an index to support an idea about paging through search results. The idea did not survive, and the next migration dropped both columns and put the index back. The SQL files are not inverses in appearance, because dropping a column and recreating an index are different statements from adding one, but the resulting schema is identical to the byte.
I like that this is checkable. If you want to know whether a change was really reverted, or whether something subtle survived it, you do not have to read two SQL files and reason about them. You can hash the two snapshots. That is the one capability all this JSON gives you that the SQL does not.
There are two dips in the table above as well, where the table count goes down rather than up, at 0007 and 0022. Both are deletions of features that existed for days, and the second one also takes 11 columns out of tables that stayed. A schema history that only grows is a schema history that is not being edited.
The git measurement that made me stop and look
$ git log --numstat --format="" -- drizzle | awk '{a+=$1;d+=$2} END {print a, d}'
96218 0
$ git log --numstat --format="" -- src | awk '{a+=$1;d+=$2} END {print a, d}'
91718 18169
Across the whole history, the migrations folder has added more lines than the entire application source, and has never deleted one. Twenty-four commits touched it. The deletions are zero because every file in it is append-only by construction: a snapshot, once written, is the permanent record of what the schema was at that point, and the only way its content changes is by adding a new file next to it.
That is 45 per cent of every line this repository has ever gained, and about one per cent of its interest.
Migrations are applied by hand, and that is also a decision
There is no drizzle-kit migrate in the build and no migration step in the deploy. The README says it in one line: migrations are run by hand. The deploy and the schema change are two separate acts performed by a person in an order they choose.
That sounds lazy, and the first few weeks it was. It has turned into a constraint I now rely on, because it forces every schema change to be survivable in both directions for a while:
- a new column is added nullable or with a default, and deployed in the migration-first order, so the old code that does not know about it keeps working;
- a column that is going away stops being read in one deploy and is dropped in a later migration, never in the same one;
- anything that cannot be done in that order gets a maintenance window, which this app has a switch for, and which I have used once.
A migrate step in CI would remove the ordering mistake I could make and replace it with a deploy that half-applies a schema change while requests are in flight. For an app with one person deploying it, I would rather have the ordering mistake, because I can see it coming.
What I would change
Nothing about committing the snapshots, which is not optional: delete them and the next generate produces a migration that recreates the entire database.
What I would change is my own expectations when reading the history. The .sql files are the thing I thought I would reread, and I essentially never do. The files I actually open are the snapshots, and I open them to answer archaeology questions: when did this column become not-null, which index did this table have before the current one, was that revert complete. For that job a 98 KB JSON file per step is a fair price, and git log -p drizzle/meta/ is the real changelog of the database.
The product all this schema is holding up is described, without any of the above, on how it works, and the deadlines that several of these columns exist to enforce are written into the privacy notice. The 76 pages in the sitemap read none of these tables at all, which is why they are the only part of the app a migration cannot break.
Top comments (0)