You've added a new column to your `users` table. Your staging environment works. You deploy to production and Postgres rejects the INSERT because your code expects a default value that doesn't exist. Your teammates have no record of what changed, when it changed, or how to roll it back. This is the migration problem: schema changes without versioning are chaos.
Most teams pick one tool and stick with it. The two heavyweight options are Supabase CLI (SQL-based, native to Postgres) and drizzle-kit (TypeScript schema generation). Aidxn rule: drizzle-kit if you're already writing Drizzle ORM queries in your code; Supabase CLI if you're using the Supabase client directly. Each solves the same problem — schema versioning, replay-ability, team sync — but from different angles.
Why Schema Versioning Matters
Without migrations, your database schema exists only in your head (or in a Figma diagram that went stale 3 months ago). When a new teammate joins and needs to set up a local database, they have to manually run 40 ALTER TABLE commands copied from Slack. When production breaks, you can't roll back the schema to yesterday's state — the ALTER is permanent. When you want to add a column on staging and test it before production, there's no versioned script to copy over.
Migrations solve this by storing every schema change as a version-controlled file. Each migration is idempotent (safe to run twice) and reversible (can be rolled back). The database knows which migrations have been applied, and deployment tools can enforce "migrate before deploy" as part of your CI/CD pipeline.
Supabase CLI Migrations: Native SQL, File-Per-Change
Supabase migrations are SQL files stored in your repo under `supabase/migrations/`. Each file is timestamped and executed once per environment. When you run `supabase db push`, the CLI reads files you haven't pushed yet and applies them.
-- supabase/migrations/20260613120000_add_user_email_verified.sql
alter table users add column email_verified boolean default false;
create index idx_users_email_verified on users(email_verified);
Run `supabase migration new add_user_email_verified` and it creates a timestamped SQL file. You edit it, commit it, and the CLI tracks which migrations have run on each environment (remote database, staging, local). To roll back, Supabase generates a reversal script, or you manually write one. For teams using Supabase client (not Drizzle), this is the standard path — SQL is portable and works on any Postgres, not just inside an ORM.
drizzle-kit: TypeScript Schema as Source of Truth
Drizzle migrations invert the flow: you write your schema in TypeScript, drizzle-kit generates SQL migrations from the diff, and the CLI applies them. Your schema definition IS your single source of truth.
// src/db/schema.ts
import { pgTable, text, boolean, timestamp, index } from 'drizzle-orm/pg-core';
export const users = pgTable('users', {
id: text('id').primaryKey(),
email: text('email').notNull().unique(),
emailVerified: boolean('email_verified').default(false),
createdAt: timestamp('created_at').defaultNow(),
}, (table) => ({
idxEmailVerified: index('idx_users_email_verified').on(table.emailVerified),
}));
Run `drizzle-kit generate` and it compares your TypeScript schema to the database, generates a SQL migration, and saves it to `drizzle/migrations/`. You review the SQL, commit it, and `drizzle-kit migrate` applies it. The advantage: your schema definition and code are always in sync — if you change the TypeScript definition without generating and applying a migration, drizzle-orm will warn you at runtime.
Real Workflow: Which One Are You Using?
Use Supabase CLI if: You query the Supabase client directly (RLS policies, realtime subscriptions). You want to write SQL by hand (stored procedures, custom indexes). You're not using Drizzle ORM. You prefer explicit control over each migration file.
Use drizzle-kit if: You use Drizzle ORM in your application code. You want your schema definition and queries in the same language (TypeScript). You want type safety — schema changes are validated before they hit the database. You're building a multi-tenant SaaS where dozens of schema changes land per sprint and you need high confidence they're all correct.
For Aidxn SaaS products, drizzle-kit is the default. For Rebuild Relief internal tools where we query Supabase client directly and want manual SQL control, we use Supabase CLI. Some teams use both — drizzle-kit for application tables, Supabase CLI for custom functions and views that drizzle-kit doesn't handle well.
Six FAQs
Can I switch from one to the other mid-project?
Yes. Supabase CLI just tracks applied migration filenames. If you've run 10 SQL migrations via Supabase CLI, you can generate a drizzle schema that matches your current database, run `drizzle-kit generate`, and it'll generate zero new migrations (because the schema is already up-to-date). Your next change uses drizzle-kit. No data loss, no downtime. The reverse (drizzle → Supabase) is harder — you'd need to write custom SQL for anything drizzle-kit didn't generate.
What if I forget to generate a migration in drizzle-kit?
Your TypeScript schema changes but the database stays old. When you deploy code that expects a new column and queries it, the query fails at runtime. Drizzle ORM doesn't prevent this — the schema definition in code and the actual database have diverged. Always run `drizzle-kit generate` before committing schema changes, and always review the generated SQL to catch mistakes.
Does Supabase CLI support rolling back migrations?
Not automatically. Supabase tracks which migrations have been applied (stored in `schema_migrations` table), but the CLI doesn't reverse them. You have to manually write a reversal migration. For example, if a migration added a column, the reversal migration drops it. For drizzle-kit, `drizzle-kit drop` reverses a local migration before it's pushed, but reversing a production migration requires a manual migration as well.
Can I use RLS policies with drizzle-kit migrations?
Sort of. Drizzle-kit generates SQL for tables, columns, and indexes. For RLS policies, you write custom SQL in a separate migration file or hand-edit the generated migration. The policy SQL isn't type-checked by drizzle-kit, so errors show up when the migration runs on the database. For complex RLS-heavy products, some teams prefer Supabase CLI + custom SQL for policies, and drizzle-kit for application tables.
How do I test a migration locally before pushing to production?
Both tools support local Postgres. Run `supabase start` to spin up a local Supabase stack (includes Postgres), apply your migrations, and test your app against it. The local database mirrors your remote schema. Once confident, `supabase db push` sends migrations to staging, then production. Same with drizzle-kit — run migrations against your local database, test, then `drizzle-kit migrate --database-url "prod-url"` pushes to production.
What if a migration fails halfway through on production?
Postgres transactions save you. Most migrations run in a single transaction — if any statement fails, the whole migration rolls back and the database state is unchanged. The migration record (in `schema_migrations` or drizzle's migration table) is NOT inserted, so you can fix the migration and run it again. Exception: some DDL statements (like `CREATE INDEX CONCURRENTLY`) can't be part of a transaction. For those, wrap migrations carefully and test on staging first.
The Bottom Line
Schema versioning isn't optional — it's how teams ship database changes safely. Supabase CLI gives you raw SQL control and works with any Postgres. Drizzle-kit keeps your schema definition and code in sync and catches errors at generation time. Pick one based on whether you're using Drizzle ORM; if you're not, Supabase CLI is simpler and more portable. Version every change, test on staging, and you'll never panic about production schema again. For deeper database resilience, see zero-downtime migration patterns and partner with Aidxn on your database architecture.