drizzle-kit migrate Does Not Apply Hand-Written SQL

Conclusion

drizzle-kit migrate applies only the DDL that drizzle generated.

If SQL you wrote by hand — RLS policies, audit triggers, SECURITY DEFINER functions — sits in a different directory of the same repository, it is not applied.

No error, no warning. The migrate finishes as a success.

Symptom

• The migration succeeds
• Tables and columns exist exactly as expected
• The application starts and most screens work
• Only the RLS policies, triggers and functions are absent from production

Because the tables exist, nothing looks missing when you eyeball the schema. What is missing is not the shape of the tables but the controls attached to them.

And missing controls are not inconsistent with the result of applying DDL. So nothing stops.

Cause

A drizzle migration is self-contained in the files drizzle generated and their journal.

When the migration content is split across two directories, this happens:

drizzle/*.sql      … DDL generated by drizzle       → run by drizzle-kit migrate
migrations/*.sql   … hand-written RLS/triggers/fns  → not run

To drizzle, the second directory is just some unrelated files sitting in the repository. It never reads them, so there is no “not applied” report either. Not appearing in the output is exactly what keeps this hidden for a while.

There is more: when drizzle’s journal and its generated files drift apart, even the generated DDL may not run as intended. Those two reasons together are why drizzle-kit migrate alone cannot be the mechanism for applying to production.

Why it splits in two

What drizzle can generate stops at DDL derivable from the schema definition. RLS policy expressions, the body of an audit trigger and the contents of a SECURITY DEFINER function are not in the schema definition. They have to be written by hand, so they live somewhere else.

Splitting them is not the mistake. The mistake is the applying side only knowing about one of them.

How to check

A schema diff will not show it. Query the catalogs directly.

Policies:

SELECT schemaname, tablename, policyname
FROM pg_policies
WHERE schemaname = 'public';

Triggers:

SELECT c.relname AS table_name, t.tgname
FROM pg_trigger t
JOIN pg_class c ON c.oid = t.tgrelid
WHERE NOT t.tgisinternal;

Functions:

SELECT proname, prosecdef
FROM pg_proc
WHERE pronamespace = 'public'::regnamespace;

prosecdef true means SECURITY DEFINER.

If the rows you expect come back empty, that directory did not run. The presence of a table tells you nothing, so always look here.

The fix

Move the unit of applying from “the drizzle command” to “the apply order itself”.

1. Hold the apply order in a single source. Define which files run in what order — DDL, then RLS/triggers/functions — in one place
2. Run both directories in that order in production too. Let drizzle-kit migrate own the drizzle side, and run the hand-written side after it from the same definition
3. Make “applied” mean the catalog queries have been run. Put the three SELECTs above into the verification step

This is not an argument against calling drizzle-kit migrate. It is about the procedure carrying the assumption that it does not finish the job on its own.

Where to borrow the apply order from

The test setup may already have code that runs both directories in the right order — it has to, if the RLS policies and triggers are under test.

If a working apply order already exists there, that is the single source the production procedure should reference. Copying the order into a runbook means one of the two rots as soon as the other grows.

An assumption worth naming

This problem is not “the tests are too weak to catch it”. The tests are the ones correctly running both, which leaves only the test environment complete and only production with bare tables.

Adding tests will not detect it. What to look at is whether the apply path is the same in test and in production.

For another shape of “passes in test, differs only in production”, see SECURITY DEFINER functions failing only in production because of the executing role. That one is the same SQL run as a different role; this one is the SQL never running at all. Neither makes the database complain.