Adding a NOT NULL column to a table that already has rows

You add a required field to a table:

export const projects = pgTable("projects", {
  id: uuid("id").primaryKey().defaultRandom(),
  name: text("name").notNull(),
  slug: text("slug").notNull(),        // new
  ...timestamps,
});

bun run db:generate writes exactly what you asked for:

ALTER TABLE "projects" ADD COLUMN "slug" text NOT NULL;

It applies cleanly on your empty local database and fails in production:

ERROR: column "slug" of relation "projects" contains null values (SQLSTATE 23502)

Postgres has to put something in that column for the rows that already exist, and you did not say what.

The tempting fix, and why it is a trap

ALTER TABLE "projects" ADD COLUMN "slug" text NOT NULL DEFAULT '';

This works (modern Postgres adds a constant default without rewriting the table) but you have traded a loud failure for a quiet one. Every existing row now has an empty slug, your unique index cannot be created, and the bug surfaces weeks later as "why do half our projects have no URL".

A default is right when the default is genuinely the correct value for old rows (status = 'active', retry_count = 0). It is wrong when the value has to be computed or looked up.

The shape that works: three migrations, two deploys

Migration 1: add it nullable

Change the schema to nullable first:

slug: text("slug"),

bun run db:generate

ALTER TABLE "projects" ADD COLUMN "slug" text;

This is instant on any table size: adding a nullable column with no default is a catalog-only change in Postgres, no rewrite, no long lock.

Deploy this together with code that writes the new column on every insert and update, but does not yet require it on read. Both the old and the new code can run against this schema, which is what makes the deploy safe.

Migration 2: backfill, in batches

Do not write UPDATE projects SET slug = ... with no bound. On a large table that is one transaction holding row locks on everything, generating a write-ahead log entry per row, blocking the app for as long as it takes.

Batch it. A standalone script, run once, is usually clearer than a migration file because it can be resumed:

// scripts/backfill-project-slug.ts
import { and, isNull, sql as raw } from "drizzle-orm";
import { db } from "@/db";
import { projects } from "@/db/schema";

const BATCH = 1_000;

async function main(): Promise<void> {
  let updated = 0;
  for (;;) {
    // ctid-free, index-friendly: take a bounded set of ids that still need work.
    const rows = await db
      .update(projects)
      .set({
        slug: raw`lower(regexp_replace(${projects.name}, '[^a-zA-Z0-9]+', '-', 'g'))`,
      })
      .where(
        and(
          isNull(projects.slug),
          raw`${projects.id} in (select id from projects where slug is null limit ${BATCH})`,
        ),
      )
      .returning({ id: projects.id });

    if (rows.length === 0) break;
    updated += rows.length;
    console.log(`backfilled ${updated}`);
    // Give the database room to breathe and autovacuum room to keep up.
    await new Promise((resolve) => setTimeout(resolve, 100));
  }
  console.log(`done: ${updated} rows`);
}

main().catch((error: unknown) => {
  console.error(error);
  process.exitCode = 1;
});

Three properties that make this safe: each batch is its own transaction, the WHERE clause means re-running it is harmless, and killing it halfway loses nothing.

If the value collides (slugs must be unique) resolve collisions in the script, deterministically, before you get to migration 3.

Verify before moving on:

select count(*) from projects where slug is null;

Migration 3: enforce

Only when that count is zero. Make the schema non-null again:

slug: text("slug").notNull(),

bun run db:generate

ALTER TABLE "projects" ALTER COLUMN "slug" SET NOT NULL;

SET NOT NULL scans the table to verify. On a very large table, avoid the scan by adding a NOT VALID check constraint first, validating it without a heavy lock, then setting NOT NULL: Postgres 12+ will use the validated constraint and skip the scan:

ALTER TABLE projects ADD CONSTRAINT projects_slug_not_null
  CHECK (slug IS NOT NULL) NOT VALID;--> statement-breakpoint
ALTER TABLE projects VALIDATE CONSTRAINT projects_slug_not_null;--> statement-breakpoint
ALTER TABLE projects ALTER COLUMN slug SET NOT NULL;--> statement-breakpoint
ALTER TABLE projects DROP CONSTRAINT projects_slug_not_null;

Hand-edit that into the generated file before its first apply, keeping the --> statement-breakpoint markers Drizzle uses to split statements.

Adding the unique index

Same discipline. A plain CREATE UNIQUE INDEX takes a write lock for the whole build:

CREATE UNIQUE INDEX CONCURRENTLY "projects_slug_key" ON "projects" ("slug");

CONCURRENTLY cannot run inside a transaction block, so this migration must run on its own, on a direct (non-pooled) connection. If it fails it leaves an invalid index behind: check with

select indexrelid::regclass from pg_index where not indisvalid;

and DROP INDEX before retrying.

Rehearse against real data

The reason this whole page exists is that an empty local database cannot fail the way production does. Before you deploy, apply the sequence to a copy with real row counts: a Neon branch or a Supabase branch, both of which are copy-on-write and take seconds. You will learn the backfill duration and find the duplicate values, on a database you can throw away.

Checklist

• Column added nullable in its own migration; deploy writes it before requiring it.
• Backfill is batched, resumable and idempotent, and was run to completion.
• select count(*) where <col> is null returns 0 before the NOT NULL migration.
• Unique indexes built CONCURRENTLY, on a direct connection, in their own migration.
• The whole sequence rehearsed on a branch with production-shaped data.