Storyie

Drizzle ORM migrations on Supabase: lessons from 56 files in production

Storyie Engineering Team
••8 min read

How the Storyie team manages Drizzle ORM migrations on a live Supabase PostgreSQL database — two-stage GitHub Actions deployment, DDL vs DML split, schema layout, RLS co-location, and why we decided rollbacks aren't worth the complexity.

Drizzle ORM migrations on Supabase: lessons from 56 files in production

Storyie runs on Drizzle ORM with Supabase PostgreSQL. After roughly a year of development, our migration count sits at 56 files. The "how to write a Drizzle schema and run drizzle-kit generate" part is well-documented. What is harder to find is how to actually apply migrations to a live production database, how to handle data migrations alongside schema changes, and how to think about rollbacks. This post is our attempt to fill that gap.

TL;DR

  • Two migration types: auto-generated for DDL, custom (--custom) for DML. Never mix them.
  • Two-stage deployment via GitHub Actions: develop branch applies to the dev Supabase project, main applies to production. A production migration failure has not happened once.
  • 29 tables across 29 schema files, each table in its own file, flat layout. Domain-based directory grouping sounded good and didn't work.
  • RLS policies co-located with table definitions using .enableRLS() — prevents policies from lagging behind schema changes.
  • No rollback migrations. Develop-first validation plus Supabase PITR handles the failure cases.

| Topic | What we do |
| --- | --- |
| DDL changes | drizzle-kit generate |
| Data migrations | generate --custom, hand-written SQL |
| Production deployment | GitHub Actions, develop → main, two stages |
| Schema layout | Per-table files, flat directory, re-exported from one entry point |
| RLS management | .enableRLS() + pgPolicy in schema definitions |
| Rollback strategy | None — rely on develop validation + Supabase PITR |
| Custom SQL safety | Write idempotent SQL (ON CONFLICT DO NOTHING, etc.) |

Architecture overview

The database package sits at packages/database in the monorepo:

packages/database/
├── drizzle.config.ts        # Drizzle Kit config
├── src/
│   ├── schema.ts            # re-export entry point
│   └── schemas/             # one file per table (29 files)
└── supabase/
    └── migrations/          # generated SQL files (56 files)

Our drizzle.config.ts is minimal:

// drizzle.config.ts
import { defineConfig } from "drizzle-kit";

export default defineConfig({
  schemaFilter: ["public"],
  schema: "./src/schema.ts",
  out: "./supabase/migrations",
  dialect: "postgresql",
  dbCredentials: {
    url: process.env.DATABASE_URL!,
  },
  entities: {
    roles: {
      provider: "supabase",
    },
  },
});

The entities.roles.provider: "supabase" setting is not optional. Supabase uses a non-standard role hierarchy (authenticated, anon, service_role), and without this flag, drizzle-kit generate can produce SQL that conflicts with those roles or produces malformed RLS policy statements.

Two types of migrations

Drizzle Kit has two generation modes, and using the wrong one for the job is a recurring source of problems:

{
  "scripts": {
    "generate": "drizzle-kit generate",
    "generate:custom": "drizzle-kit generate --custom"
  }
}

Auto-generated (DDL)

Use this for any structural change: adding a table, adding a column, modifying a type, creating an index, adjusting a foreign key. Drizzle Kit diffs the schema against the previous state and generates the SQL:

pnpm --filter database generate

The output is a sequentially numbered file with a random name suffix: 0042_cool_character_name.sql. We always review the generated SQL before committing — not because Drizzle Kit makes mistakes often, but because the SQL makes the change concrete and catches cases where the diff produced something unexpected (like a silent column recreation instead of an in-place alteration).

Custom migrations (DML)

Use this for anything that moves or transforms data. The command creates an empty SQL file:

pnpm --filter database generate:custom --name=seed_reserved_slugs

We have used custom migrations for initial seed data, back-filling columns after a schema change, copying rows during a plan restructure, and updating stored Stripe price IDs after a pricing change:

-- 0013_seed_reserved_slugs.sql
INSERT INTO "reserved_slugs" ("slug", "reason") VALUES
  ('admin', 'System route'),
  ('api', 'System route'),
  ('settings', 'System route')
  -- ... several hundred rows
;
-- 0048_copy_plan_features_to_new_pro.sql
INSERT INTO "plan_features" (
  "plan_id", "feature_key", "limit_value", ...
)
SELECT
  'new_pro_plan_id', "feature_key", "limit_value", ...
FROM "plan_features"
WHERE "plan_id" = 'old_pro_plan_id';
-- 0051_update_pro_plan_pricing_v3.sql
UPDATE "subscription_plans"
SET "stripe_price_id_monthly" = 'price_xxx',
    "stripe_price_id_yearly" = 'price_yyy'
WHERE ...;

The rule we follow: DDL in auto-generated files, DML in custom files. When they land in the same migration and something fails halfway through, it becomes genuinely difficult to determine what already applied. Splitting them keeps failures local and recoverable.

Production deployment: GitHub Actions, two stages

Migrations apply automatically when SQL files land in packages/database/supabase/migrations/:

# .github/workflows/supabase-migration.yml (key parts)
on:
  push:
    branches: [develop, main]
    paths:
      - "packages/database/supabase/migrations/**"

jobs:
  migrate:
    steps:
      - name: Determine Supabase project
        run: |
          if [[ "$TARGET_BRANCH" == "develop" ]]; then
            echo "project_id=dev_project_id" >> $GITHUB_OUTPUT
          elif [[ "$TARGET_BRANCH" == "main" ]]; then
            echo "project_id=prod_project_id" >> $GITHUB_OUTPUT
          fi

      - name: Run migrations
        run: |
          npx supabase link --project-ref $PROJECT_ID
          npx supabase db push

The flow:

  1. Edit a schema file locally, run drizzle-kit generate to produce the SQL file.
  2. Review the SQL — even for auto-generated files.
  3. Push to develop → GitHub Actions applies the migration to the dev Supabase project.
  4. Test against the development environment.
  5. Merge to main → GitHub Actions applies the same migration to production.

We also run basic SQL validation on PRs that include migration files — checking that files are non-empty, that statements end with semicolons. It is a low bar, but it has caught the "accidentally committed an empty migration" scenario more than once.

Why the two-stage setup matters

We run two Supabase projects — one for development, one for production. Supabase's free plan allows two projects, so there is no cost. The practical benefit is that production has never received a migration that had not already run successfully on the development database. In a year of operation, we have not had a production migration failure. The delay between "written and reviewed" and "live on production" is typically hours, which is a trade-off we consider obvious.

Schema layout

We have 29 tables. Keeping them in a single file would be unmanageable, so each table lives in its own file:

src/schemas/
├── diaries.ts
├── tags.ts
├── document-tags.ts
├── subscriptions.ts
├── subscription-plans.ts
├── plan-features.ts
├── tenants.ts
├── tenant-members.ts
├── tenant-feature-usage.ts
└── ...

schema.ts is a single re-export:

// schema.ts
export * from "./schemas";

Drizzle Kit's schema config option points to this file and picks up all tables automatically.

We initially tried grouping tables into domain directories (auth/, billing/, content/). It sounded clean until we ran into foreign key relationships that crossed domain boundaries — which is most of them. A billing table with a foreign key into a user table means you either have cross-directory imports or you move things, neither of which improved on the flat layout. We reverted to flat and have not looked back.

Gotchas and how we handled them

1. RLS policies belong in the schema

Drizzle ORM supports .enableRLS() on table definitions and lets you declare policies alongside the table. With the supabase provider configured, drizzle-kit generate emits the correct RLS SQL:

// schemas/notes.ts
import { pgTable, text, uuid } from "drizzle-orm/pg-core";
import { authenticatedRole } from "drizzle-orm/supabase";

export const notes = pgTable("notes", {
  id: uuid("id").primaryKey().defaultRandom(),
  userId: uuid("user_id").notNull(),
  content: text("content"),
}).enableRLS();

We used to write ALTER TABLE ... ENABLE ROW LEVEL SECURITY and CREATE POLICY by hand in custom migrations. The failure mode was predictable: a new table would ship without RLS because the policy migration was a separate PR that did not land at the same time. Moving policy definitions into the schema file means the RLS SQL generates alongside the table DDL, in the same migration file. That failure class has not recurred since.

2. Custom migrations should be idempotent

supabase db push is idempotent for successfully completed migrations — it tracks applied files in a history table. But if a migration that contains DML fails partway through, some rows may already be written. PostgreSQL can roll back DDL inside a transaction; it cannot un-write committed rows. Writing custom migrations to be idempotent makes re-running after a failure safe:

-- NG: fails on second run with a unique constraint violation
INSERT INTO "reserved_slugs" ("slug", "reason")
VALUES ('admin', 'System route');

-- OK: safe to run multiple times
INSERT INTO "reserved_slugs" ("slug", "reason")
VALUES ('admin', 'System route')
ON CONFLICT ("slug") DO NOTHING;

We apply the same principle to UPDATE migrations: use WHERE conditions that limit the update to rows that actually need changing, so re-running produces the same result.

3. Destructive changes go slowly

Column renames, table splits, and similar breaking changes do not happen in a single migration. We follow an expand-and-contract pattern:

Step 1: Add the new column (nullable)
Step 2: Deploy app code that writes to both old and new columns
Step 3: Back-fill existing rows via custom migration
Step 4: Deploy app code that reads only from the new column
Step 5: Drop the old column

The instinct in a small codebase is "we have few users, we can do this in one shot." The practical counter-argument is that a migration failure during a one-shot change is harder to recover from than any individual step in the staged approach. Steps are small, reversible, and independently testable. The whole sequence typically spans a week.

Rollback strategy: we do not have one

We have no rollback migrations. This is a deliberate choice.

DDL rollbacks are deceptively complex. Reversing a DROP COLUMN is not a SQL operation — it is a data recovery operation. For that case, Supabase's point-in-time recovery (PITR) is more reliable than a rollback migration that was written speculatively and has never been tested under pressure.

For DML migrations, writing idempotent forward migrations means that re-running after a fix is usually the cleanest path. The failure mode of a custom migration is almost always a data condition we did not account for; the fix is to address that condition and re-run, not to undo.

The two-stage deployment catches most issues before production. For destructive operations — DROP TABLE, DROP COLUMN — we extend the develop soak period. Those changes might sit on develop for a week or more before we're confident enough to send them to main.

What 56 migrations look like in practice

Breaking them down roughly by type:

  • Table additions (~20): new feature work, each adding one or more tables
  • Column additions and modifications (~15): requirements evolving after initial design
  • Index additions (~5): performance tuning based on observed query patterns
  • Seed data and data migrations (~10): custom migrations, all of the DML variety
  • RLS policies (~6): mostly from the period before we moved policy definitions into schemas; new tables generate RLS SQL automatically now

The first ten or so migrations reflected a schema that was still finding its shape — a lot of add-then-revise. Past the twenty-migration mark, the schema stabilized, and the rate of migrations dropped noticeably. That curve is probably typical: early iterations are cheap when there is little data, and the schema naturally converges as the product solidifies.

Takeaways

  • Split DDL and DML across separate migration files. Mixing them makes failure recovery much harder.
  • Run two Supabase projects — development and production — and apply migrations to development first. The cost is zero on the free plan; the benefit is that production is always seeing a migration that has already succeeded at least once.
  • Co-locate RLS policy definitions with table definitions in schema files. The drift between "table exists" and "table has RLS" is a real bug class that this pattern eliminates.
  • Write custom migrations to be idempotent. ON CONFLICT DO NOTHING, WHERE NOT EXISTS, conditional updates — all of these make re-running safe and simplify the mental model around partial failures.
  • Accept that Drizzle ORM's migration tooling is less batteries-included than Rails or Django. The trade-off is that every generated file is plain SQL you can read, understand, and customize. That transparency is valuable when something goes wrong.

Related Posts

Try Storyie

Storyie is the diary app this infrastructure supports. Write your first entry at storyie.com or grab the iOS app.