Skip to main content

No migration files: Drizzle push and one hand-written trigger

• 5 min read
postgres drizzle database full-text-search

A confession that gets me in arguments

RoleReady’s database is about 100 tables — lib/database/schema.ts is around 2,900 lines and exports 106 table definitions. It has no migrations directory. No timestamped SQL files. I sync the schema to Postgres with drizzle-kit push, the command most “best practices” posts tell you is for prototypes only.

I’ve shipped this to production for a year. Here’s the actual case for it, and the one place where I admit push can’t do the job and I write SQL by hand.

What push actually does

drizzle-kit push reads your schema definition, introspects the live database, computes the diff, and applies it directly. No intermediate artifact. The schema file is the source of truth, and the diff a reviewer sees in a pull request is the change to the database. Compare that to generated migrations, where the truth is split between the schema and a growing pile of SQL files, and the thing you review is a generated artifact you didn’t write.

For a single maintainer, the generated-migration workflow mostly buys you problems:

  • Two branches both add a column, both generate a migration, and now you’re hand-resolving a merge between two files that Drizzle wrote and you don’t fully own.
  • The migration file and the schema can disagree, and you find out in production.
  • You accumulate hundreds of files describing a history nobody will ever replay from zero, because you restore from backups, not by re-running 2019’s migrations.

With push, there’s one file, the PR diff is the migration, and there’s nothing to merge-conflict. The configuration is unremarkable:

// drizzle.config.ts
export default defineConfig({
  schema: './lib/database/schema.ts',
  dialect: 'postgresql',
  dbCredentials: { url: process.env.DATABASE_URL! },
});

The discipline that makes it safe

Push is a loaded gun precisely because it’s direct. The rule that keeps it from going off, written into the repo’s AGENTS.md, is that an agent — or me — edits the schema file and then stops:

Schema changes: edit lib/database/schema.ts only, then ask the maintainer to run bun x drizzle-kit push — never run push yourself, never use generate/migrate.

The reason is that a destructive diff — dropping a column, narrowing a type — has to be looked at by a human who asks “what data does this delete?” before it runs. push --force will happily execute a data-losing change. So the workflow has a deliberate stop: propose the schema, review the exact diff, then apply. In CI, integration tests get their own throwaway databases and push to both roleready and roleready_test before the suite runs, so the push path itself is exercised on every PR.

This works at my scale. I’m not going to pretend it scales to a team of twenty pushing to a database with strict zero-downtime requirements and an on-call rotation. At that point you want migrations you can review, stage, and roll back as discrete units. But “solo-to-small and pre-that-scale” is a long, real phase, and push is the right tool for the whole of it.

Where push gives up: full-text search

There’s exactly one thing in the schema that push cannot express, and it’s the job-bank search. Drizzle’s schema DSL describes tables and columns. It does not describe a tsvector column maintained by a trigger with per-column weights and a GIN index. So that lives in the one raw SQL file in the repo, applied once by hand.

The job bank lets users search thousands of curated postings, and a naive ILIKE '%query%' is both slow and dumb — it can’t rank a title match above a location match. Postgres full-text search does both, if you set it up right. The core is a generated tsvector that weights fields by importance:

-- drizzle/0005_job_bank_search_tsvector.sql
NEW.search_tsv :=
  setweight(to_tsvector('english', coalesce(NEW.normalized_title, '')), 'A') ||
  setweight(to_tsvector('english', coalesce(NEW.title_key,        '')), 'A') ||
  setweight(to_tsvector('english', coalesce(NEW.company_name,     '')), 'B') ||
  setweight(to_tsvector('english', coalesce(NEW.location,         '')), 'C');

The setweight calls are the whole point. A query matching the job title (weight A) ranks above one matching only the company (B), which ranks above a location-only match (C). A trigger keeps search_tsv current on every insert and update, a GIN index over it makes the lookup fast, and queries rank with ts_rank. You set it up once:

psql "$DATABASE_URL" -f drizzle/0005_job_bank_search_tsvector.sql

After that, it just maintains itself. No extra search service, no separate index to keep in sync — the ranking lives where the data lives, and a backup restores it for free.

The rule I’d actually give someone

Use push for the 99% of your schema that’s tables and columns, and keep the source of truth in one file you own. Drop to raw SQL only for the things the ORM genuinely can’t model — triggers, generated columns, functional indexes, weighted full-text search — and keep those in a tiny, deliberate set of files you apply by hand and understand completely. The mistake isn’t choosing push over migrations. The mistake is pretending an ORM can express everything Postgres can do, and contorting your schema to avoid the SQL that would’ve been five clear lines.