Drizzle schema file to first applied migration
2 hours ago
Drizzle ORM just wrote a SQL migration file with no database anywhere near it. The database URL variable is unset. drizzle-kit generate still exits zero. Point drizzle-kit migrate at a port with nothing listening and migrate exits one. Generate still finishes. Migrate cannot start without the database.
Ask
Ask about this presentation
Answers are generated from this presentation.
Chapters
Show transcriptHide transcript
generate runs without the database
Drizzle ORM just wrote a SQL migration file with no database anywhere near it. The database URL variable is unset. drizzle-kit generate still exits zero. Point drizzle-kit migrate at a port with nothing listening and migrate exits one. Generate still finishes. Migrate cannot start without the database.
Two lines install Drizzle
Install is two lines. bun add drizzle-orm at 0.45.2 and pg puts the query builder and the PostgreSQL driver in the project. bun add dash d drizzle-kit at 0.31.10 adds the command line tool as a development dependency, because drizzle-kit never runs in production. Eighteen packages, one and seven tenths of a second.
Stable line version contract
drizzle-orm 0.45.2 and drizzle-kit 0.31.10, on PostgreSQL 17.10. A version one is on the way, 1.0.0 release candidate 4, published in June and unmoved since. This is the stable line, and this is PostgreSQL. The SQLite and MySQL migrators behave differently.
Schema and config files
schema dot ts declares one table: a users table with four columns: id, name, a unique email, and a created-at stamp that defaults to now. drizzle dot config dot ts is four options: which database this is for, where the schema file lives, where to write the output, and the connection URL. A migration is a file of SQL that moves your database from the shape it has now to the shape your code expects, kept in order so it runs once and only once.
Generated SQL and constraint name
Run npx drizzle-kit generate. It prints one tables, then users, four columns, and writes a file called 0000 third elektra dot sql. Four TypeScript lines became four SQL columns, plus a table constraint named users email unique. The doc page's own example writes email as text unique, inline. 0.31.10 emits lowercase types and an explicit not null on every column. The docs skip this.
The generate docs under the hood
On the generate doc page, a toggle labelled how it works under the hood sits closed when the page loads. Open it and the page lists four steps. One: read your schema files and compose a JSON snapshot. Two: read the previous migration folders and compare the current snapshot to the most recent one. Three: generate SQL from the differences between those two JSON files. Four: save the migration SQL and the snapshot. The manual does say this. It just does not say it where you would see it.
generate diffs JSON files
Your database appears in zero of those four steps. Nothing in generate opens a connection. A JSON snapshot is a plain description of every table and every column your schema files declare, written to disk right next to the SQL, so that the next run has something to compare against. So the diff generate computes runs between two JSON files on your disk. Drizzle does have a command that compares against the live database instead. It is called push, and it is a later milestone.
The migrations folder is the record
Open drizzle slash meta and there are two files. Underscore journal dot json is an index listing every migration in order, with its name and the moment it was generated. One entry so far, 0000 third elektra. And 0000 snapshot dot json is the description of the database as of that migration, listing public dot users, your table in the default schema, with its four columns spelled out. That pair of files is what the next generate compares against. So the migrations folder goes into version control next to your code, and deleting the folder to start clean destroys the only record of what your database has ever been told.
Renames are ambiguous
Rename name to full name in the schema file and run generate again. The old snapshot has a name column. The new one has a full name column and no name column. That is what a rename looks like. It is also what dropping one column and adding another looks like, and neither JSON file ever saw the data. So the tool stops and asks: is full name in the users table created, or renamed from another column? Answer rename column and it writes one alter table rename line. Answer create column and it writes add column full name, then drop column name, and that drop takes every value in it. Create column is already selected, so a stray Enter loses the data.
migrate applies SQL online
drizzle-kit migrate reads the journal, finds the migrations that are not recorded as applied yet, runs their SQL, and writes a row for each one into a log table.
PostgreSQL confirms the table
Ask PostgreSQL directly. Backslash d t lists one table, users, in the public schema. That is PostgreSQL's own answer, not Drizzle's.
Insert and select the row
Eight lines of application code: connect, insert one row for Ada, select it back. bun src slash index dot ts prints the row. Id one, the name Ada, the email, and a created-at timestamp PostgreSQL filled in from the default. Generate is offline and produces files you commit. Migrate is online and applies files you already have.
A failing migration exits one
This is the part people get wrong. Run migrate against a migration whose SQL fails and you get a spinner, then exit code one, and no error text. You get the same silence when the database is simply unreachable. That is drizzle-orm issue 5601, thirty reactions. Closed upstream on the version one beta line in April, never backported, still what stable 0.31.10 does today. Compare the journal against the log table. The first tag missing from the log is the one that failed.
Rename prompts need a TTY
This is the part people get wrong. In a shell with no terminal attached, generate throws: interactive prompts require a TTY terminal. That is issue 5307, open since January, fourteen reactions, and generate still has no flag for answering the prompt ahead of time. Generate on a developer's machine and commit the files. Continuous integration runs migrate, never generate.
Migration log table lives in drizzle
This is the part people get wrong. Ask for the migration log the obvious way and PostgreSQL says the relation does not exist. Search information schema and there it is, in a schema called drizzle. A schema here is a namespace inside the same database, so the log table is not in public with your own tables. Query the log table and each row holds a sha256 fingerprint of one migration file's exact contents. That is issue 3417, open since 2023.
The runnable set
The folder that generate and migrate write is the record of every shape your database has been asked to take, and the database is downstream of it.
Who decides the database shape
drizzle-orm 0.45.2, drizzle-kit 0.31.10, PostgreSQL 17.10. If you deleted your migrations folder tomorrow, what could you actually reconstruct? Who decides what your database looks like, your code or your database?
