Every table, and no guarantees
People ask which database and which ORM in the same breath, as though the two decisions carry similar weight. On a wealth platform they don't. One took about ten minutes and the other is still generating consequences.
Postgres, briefly
There wasn't much to decide. In a system where the central artifact is a ledger of financial transactions, you need numeric that behaves like decimal rather than a float that is approximately right. You need constraints the application cannot talk its way around. You want row-level security if tenant isolation is going to be enforced below the application. Partial and expression indexes turn out to matter enormously once real data arrives. And transactional DDL means a failed migration leaves you where you started rather than halfway.
The alternative in this domain isn't a different database. It's a worse version of this one, plus application code doing the parts you gave up.
So: Postgres, on node-postgres, and on to the decision that was actually interesting.
Drizzle, and what we bought
The schema is TypeScript. Migrations are generated from it, the instance is exposed through one global injection token, and every service injects it the same way.
The part that gets a reaction from other engineers is that there's no repository layer. No abstraction over the query builder, no interface between services and the database. Services write queries directly, against types inferred from the schema.
That's a hard coupling to one ORM, and we took it deliberately. What you get in exchange is that there's exactly one representation of a table in the codebase, and the types the compiler checks your queries against are generated from the same definition the migration came from. A repository layer would buy portability we will never use, at the cost of a second set of types that drift from the first, plus a translation layer everyone has to read through to answer what a query actually does.
I've worked on codebases with that layer. The abstraction is almost never exercised. Nobody swaps the database. What they do is maintain the interface, forever, in case somebody someday does.
The corollary is that when the ORM can't express something, there's nowhere for the workaround to hide. Which brings us to the bill.
What a code-first schema cannot say
The migration generator emits only what the schema DSL can describe, and the DSL is a subset of what Postgres can do. In our case, five categories fall outside it: trigger functions, the triggers that use them, most raw CHECK constraints, row-level security along with its policies, and partial or expression indexes — anything with a WHERE clause or a lower() in it.
So those live in a hand-maintained SQL file, applied by its own command, with a check mode that reports drift and writes nothing. That file is the home for anything new of those kinds, and it's the only mechanical proof that a given database actually has them.
Which sounds like bookkeeping until you look at what's in the list. Tenant isolation. The append-only guarantee on the attribution table. The write-once guarantee on the audit tables. The most important integrity properties in the system are precisely the ones the schema language can't describe.
The category that is genuinely dangerous
There's a worse case than "the generator can't emit this", and it took us a while to see it.
For eight unique indexes, the generator emits something. It produces the right name, over the right columns, and drops the part that makes the index correct — the WHERE clause, the lower(), the null-handling. The result isn't missing. It's stricter than intended, and it already exists under the right name, so a conditional create leaves it alone.
Three of those were not academic:
A unique index on tenant and email, without its WHERE email IS NOT NULL, means only one staff row per tenant may have a null email. Staff email is nullable by design, and the bootstrap admin deliberately has none. The second one you create fails.
A unique index on admin email, without lower(), makes uniqueness case-sensitive, so the same address registers twice in different cases. Without its predicate on the deleted flag, a soft-deleted admin squats on their address permanently.
An index over a linkage table, without WHERE validTo IS NULL, means a relationship that legitimately ended and was later re-established collides with its own closed row.
Every one of those is a plausible bug report months later, in a component nobody would think to connect to a migration.
Why this needs a guard when a missing table does not
A missing table is loud. The next query throws and somebody fixes it in five minutes.
Every object in that SQL file fails silently. A database without them has every table, every column and every foreign key — in our case a few hundred tables and several thousand columns. Seeding completes. The test suite passes, because most tests never touch a real database at all. Nothing anywhere reports a problem.
What's actually gone is that tenant-scoped queries return rows, just not only this tenant's rows. Attribution can be deleted. Audit history can be rewritten. The system behaves normally in every way you'd notice from the outside.
That asymmetry is the whole argument for the check running in CI and after every deploy. An integrity guarantee that fails loudly needs no ceremony. One that fails silently needs a mechanical prover, because human review will not catch its absence — there's nothing to see.
Related, and worth knowing if you use one of these tools: the generator does not read your database. It diffs the schema against a stored snapshot of what it last generated. Write a migration by hand and the snapshot goes stale, so the next generate cheerfully re-proposes everything that migration already did. We produced one forty-one-statement migration that would have dropped a table twice before anyone read it properly.
We also found two indexes present on the live database and in no migration at all. Somebody created them by hand, at some point, for some reason. Without the SQL file collecting them, they'd simply be absent from every freshly built environment, and nobody would know until behaviour diverged between two databases that were supposed to be identical.
The other tax: migration numbers
With several feature branches open at once, any of which may carry a schema change, sequential migration filenames collide constantly. Two people generate the same number in the same week and both are correct.
The rule we landed on is that a migration is provisional until it reaches the trunk and immutable forever after. On a branch its number is a working guess, renumbered freely by regenerating. Once merged, its number, filename and every byte of its SQL are frozen, because production walks that one chain forward and nothing else.
This isn't Drizzle's fault and you'd have it with any file-per-migration tool. It's worth planning for before it happens rather than during a release.
Would I choose it again
Yes, and I'd set up the SQL file and its check on day one rather than after discovering the gap.
The generalisable point isn't about Drizzle specifically. Any code-first ORM defines a schema language that is a subset of what your database can express, and that subset is chosen for what's common across databases. Your integrity guarantees are, almost by definition, the uncommon parts: the partial index that encodes a business rule, the trigger that makes a table append-only, the policy that makes isolation real.
So the gap between what your ORM can generate and what your database can enforce is not an edge case you'll hit occasionally. It's exactly where the important things live. Find out what falls in that gap early, put it somewhere version-controlled, and build the thing that proves it's applied — because the failure mode is a database that has every table and enforces nothing.