Useful SQL queries for Drizzle ORM

Note: When I tried these, I was at drizzle-orm@0.45.2. They seem to be working on a version 1 release which is still in beta. If you’re using the beta version, use with caution!

Get the table for all migrations

Drizzle creates a table in the database which stores all migrations. Get the table of all migrations.

SELECT * FROM drizzle.__drizzle_migrations;

Useful when you want to confirm if a migration ran.

Rollback a migration

At this point in time, Drizzle ORM doesn’t support reverting a migration. It’s a pretty manual process. Make sure to do backups before fiddling with the internals of Drizzle.

We revert/rollback a migration by deleting the relevant row from the drizzle_migrations table.

Run with EXPLAIN first to confirm the row for DELETE is what you want to delete. It should match the related Drizzle meta/ migration. (You have to remove the row and also the related file and information for the “rollback” to work.)

EXPLAIN DELETE FROM drizzle.__drizzle_migrations
	WHERE id = (
	SELECT id
	FROM drizzle.__drizzle_migrations
	ORDER BY created_at DESC
	LIMIT 1
);

Debugging when Drizzle goes out of sync with the database

This happens when I use a custom migration for e.g. renaming a table name (not supported by Drizzle right now). Drizzle doesn’t realize the migration was executed, and keeps trying to apply it again, preventing it from moving on.

These are the queries that worked for me. Use with caution, and backup if necessary.

1. Reset all drizzle migrations

Useful when you need a clean slate because of custom migrations corrupting Drizzle.

DELETE FROM drizzle.__drizzle_migrations;

2. Drop the drizzle schema from the database

DROP SCHEMA drizzle CASCADE;

Bonus: Saving queries on Supabase

This part is only relevant if you’re working with Supabase and Drizzle ORM.

You can save these snippets under SQL Editor in Supabase.

If you’re using Supabase for local development, a pleasant surprise is — they also appear locally under supabase/snippets/your_file_name.sql.

If you’re using the UI to create the files, it’s a good idea to name them without spaces for cross-platform support. Since it’s related to database, I like to name them in snake case: drizzle_migrations.