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

> The one-line migration fails on any populated database. Split it into add-nullable, backfill in batches, and enforce: three migrations across two deploys.

You add a required field to a table:

```ts
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:

```sql
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

```sql
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:

```ts
slug: text("slug"),
```

```bash
bun run db:generate
```

```sql
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:

```ts
// 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:

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

### Migration 3: enforce

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

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

```bash
bun run db:generate
```

```sql
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:

```sql
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:

```sql
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

```sql
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.

---

Agentic Boilerplate: A Next.js repo your agent already knows. Free during launch, then $99 once.

- Site map for agents: https://agenticboilerplate.com/llms.txt
- Public API: https://agenticboilerplate.com/openapi.json
- Contact: agenticstudio@gmail.com