Technology handbook
Drizzle ORM
SQL-first schema, typed queries, relations, migrations and serverless drivers.
- Topics
- 24
- Sections
- 4
Schema definition
Table definitions
Describe a table and its columns in TypeScript with database-accurate types.
- A table is TypeScript that describes real SQL columns rather than an abstraction over them.
- The declaration is the single source for both the migration and the inferred types.
- A column whose declared type differs from the database is a runtime surprise, not a compile error.
Column types and modifiers
Set nullability, defaults and generated values explicitly per dialect.
- Nullability, defaults and generated values are stated per column and per dialect.
- A default set in TypeScript and a default set in SQL are not the same thing.
- Dialects disagree on types, so a column that ports cleanly is a deliberate choice.
Primary and composite keys
Declare single or multi-column keys the way the database enforces them.
- Keys are declared the way the database will actually enforce them.
- A composite key changes what a conflict target and a join must name.
- A surrogate key hides a natural uniqueness that still needs a constraint.
Indexes and constraints
Add unique constraints, checks and indexes alongside the table.
- Unique constraints, checks and indexes sit beside the table that owns them.
- A constraint is enforced by the database; a check in application code is not.
- An index the query planner cannot use costs writes and returns nothing.
Relations
Declare relations so the relational query API can traverse them.
- Relations are declared so the relational query API can traverse them.
- They describe shape for queries and do not themselves create a foreign key.
- A relation declared in one direction only is traversable in one direction only.
Schema organization
Split a growing schema across files and re-export one entry point.
- A growing schema is split across files and re-exported from one entry point.
- The single entry is what migrations and the client are both generated from.
- Circular imports between table files break inference before they break the build.
Querying
Select builder
Compose select, from, where and join the way the SQL clauses compose.
- select, from, where and join compose the way the SQL clauses compose.
- The result type is inferred from the columns actually selected.
- Selecting whole tables where columns would do moves rows you never read.
Operators
Build conditions with eq, and, or, inArray and friends instead of string concatenation.
- Conditions are built from eq, and, or and inArray rather than concatenated strings.
- Parameters are bound, which is what keeps input out of the statement text.
- inArray with an empty list is the edge case that quietly matches nothing.
Joins
Choose inner, left and full joins knowingly and read the resulting row shape.
- Inner, left and full joins are chosen knowingly, and each returns a different row shape.
- A left join makes every column on the right side nullable in the result type.
- A join that multiplies rows turns one aggregate into a wrong number.
Relational queries
Fetch nested related rows in one call with typed nesting.
- Nested related rows come back in one call with the nesting typed.
- This is a different tool from the select builder and returns a different shape.
- Nesting deeply fetches more than a page usually renders.
Aggregations and grouping
Group, count and aggregate with the builder's typed helpers.
- Grouping, counting and aggregating use the builder's typed helpers.
- Every non-aggregated selected column has to appear in the grouping.
- Counting a left-joined column counts matches, not parent rows.
Prepared statements
Reuse a compiled query with bound parameters on a hot path.
- A compiled query is reused with bound parameters on a hot path.
- Preparing moves the planning cost off every call.
- A prepared statement is tied to its connection, which matters when pooling.
Writes & migrations
Insert and upsert
Insert rows and resolve conflicts with an explicit update target.
- Rows are inserted and conflicts resolved against an explicit target.
- The conflict target must match a real unique constraint or nothing is caught.
- An upsert that updates every column overwrites fields it was never given.
Update and delete
Restrict writes with the same conditions the select builder uses.
- Writes are restricted by the same conditions the select builder uses.
- An update with no condition rewrites the whole table.
- Testing the condition as a select first is what makes the write safe.
Returning clauses
Read back generated values without a second query.
- Generated values are read back without issuing a second query.
- It returns the row as written, including defaults the database supplied.
- Not every dialect supports returning on every statement.
Transactions
Group dependent statements and roll back on failure.
- Dependent statements are grouped and rolled back together on failure.
- Every statement must use the transaction handle or it runs outside it.
- Work held open across a network call holds locks for that long.
drizzle-kit generate
Produce SQL migration files from schema changes.
- SQL migration files are produced from the difference in the schema.
- The generated SQL is reviewed, because a rename can read as a drop and an add.
- Generating without applying leaves the code ahead of the database.
Applying migrations
Run migrations at deploy time and keep the journal in version control.
- Migrations run at deploy time and the journal is kept in version control.
- The journal is what tells an environment which migrations it has already run.
- Two instances migrating at once need a lock or they race.
Runtimes & operation
Driver selection
Pick the driver that matches the database and the runtime.
- The driver is chosen to match both the database and the runtime it runs in.
- A driver that opens raw sockets cannot run where sockets are unavailable.
- Drivers differ in what they support, so the choice affects more than connection setup.
Serverless drivers
Use an HTTP or pooled driver where long-lived sockets are unavailable.
- An HTTP or pooled driver is used where long-lived sockets do not exist.
- Each invocation is short-lived, so connecting per request exhausts the database.
- An HTTP driver adds latency per statement, which makes chatty code expensive.
Connection management
Reuse a connection across invocations and avoid per-request pools.
- A connection is reused across invocations rather than created per request.
- A pool created inside a handler is a new pool on every call.
- Pool size multiplied by instance count is what the database actually sees.
Type inference helpers
Derive insert and select types from a table instead of restating them.
- Insert and select types are derived from the table instead of restated.
- Derived types change with the schema, so they cannot drift from it.
- Hand-written duplicates are what silently disagree after a column changes.
Raw SQL escape hatch
Use the sql template safely for expressions the builder lacks.
- The sql template covers expressions the builder does not express.
- Interpolated values are still bound as parameters rather than inlined.
- Raw SQL is not type-checked, so the result shape is asserted, not inferred.
Testing
Run migrations against a disposable database and assert on real SQL behaviour.
- Migrations are run against a disposable database and assertions made on real SQL behaviour.
- Only a real database enforces constraints, defaults and collation.
- A mocked query proves the code ran, not that the SQL was right.
Sources
- Drizzle ORM official documentation