U
Umbral
This version is in beta. Some features may change before release.
Umbral

Adding NOT NULL columns to existing tables

How to safely add NOT NULL columns to populated tables without losing data, with backend-specific notes for SQLite and Postgres.

Adding a new NOT NULL column to a table that already has rows is the most common "your migration won't apply" scenario. umbral catches it earlier than most: makemigrations itself refuses to write the migration when it would brick a populated table. This page covers the safe paths umbral knows about and the backend differences you'll hit.

The constraint, in one paragraph

Both SQLite and Postgres refuse to ALTER TABLE x ADD COLUMN y NOT NULL against existing rows unless they're told what value to put in y for those rows. SQLite is stricter: it also refuses non-constant defaults like CURRENT_TIMESTAMP in ADD COLUMN. Postgres allows DEFAULT now() and backfills inline. The migration engine handles both transparently for the timestamp columns; for everything else you pick the backfill shape.

makemigrations refuses the unsafe shape

When a model gains a NOT NULL field that is not Option<T>, not #[umbral(default = "...")], and not #[umbral(auto_now)] / #[umbral(auto_now_add)] (and isn't the primary key), the autodetector stops with an UnsafeAlter error instead of generating an AddColumn op that would fail on every populated row:

Code
text 
umbral migrate: unsafe column change on `Order.region`: adding NOT NULL column
`region` without a default to existing table `order` would fail on every
populated row. Pick one: (a) make the field `Option<T>`, (b) add
`#[umbral(default = "...")]` so the migration backfills, or (c) add
`#[umbral(auto_now_add)]` for timestamp columns

The fix is to adjust the model to one of the three safe shapes below, then re-run makemigrations. The migration is never written until the shape is safe, so there's nothing to delete and nothing reaches the database in a broken state.

The three safe shapes

1. Option<T>: nullable, no backfill needed

Code
rust 
pub struct Customer {
    // ...
    pub deleted_at: Option<DateTime<Utc>>,
}

The column is NULL-permitting, existing rows get NULL, no migration drama. Use this when the new field genuinely doesn't apply to historical rows (soft-delete timestamps, optional metadata, etc.).

2. #[umbral(default = "...")]: constant default

Code
rust 
pub struct Order {
    // ...
    #[umbral(default = "0")]
    pub refund_count: i32,
    #[umbral(default = "false")]
    pub is_archived: bool,
}

Each default lands in the DDL verbatim (DEFAULT 0 for refund_count). Boolean defaults are translated to integer literals on SQLite, so false renders as DEFAULT 0, not DEFAULT 'false'. The DB backfills existing rows automatically. Works on both backends.

3. #[umbral(auto_now)] / #[umbral(auto_now_add)]: timestamp columns

Code
rust 
pub struct Customer {
    // ...
    #[umbral(auto_now_add)]
    pub created_at: DateTime<Utc>,
    #[umbral(auto_now)]
    pub updated_at: DateTime<Utc>,
}

Special-cased by the engine because timestamp defaults differ across backends:

  • Postgres: emits DEFAULT now() NOT NULL in the ALTER, backfilled inline by the DB.
  • SQLite: emits a two-statement migration:
    1. ALTER TABLE x ADD COLUMN y (nullable, no default, since SQLite refuses non-constant defaults)
    2. UPDATE x SET y = datetime('now') WHERE y IS NULL

After step 2 every existing row has a sensible value. The column ends up NULL-permitting at the SQLite layer; Rust still types it as DateTime<Utc> (not Option), and every INSERT through the ORM supplies a value via the macro-emitted auto_now arm. Direct-SQL writers could theoretically write NULL; see "Caveats" below.

Why the SQLite asymmetry?

SQLite's ALTER TABLE ADD COLUMN requires a constant default. CURRENT_TIMESTAMP is computed at evaluation time, not constant, so SQLite rejects it with "Cannot add a column with non-constant default". The full table-rebuild dance (CREATE new, COPY, DROP, RENAME) would let us keep DB-level NOT NULL, but it's heavy machinery for a guarantee Rust already provides at the type level. We picked the lighter compromise.

Tightening an existing nullable column to NOT NULL

The same backfill applies when you make an existing Option<T> field non-optional and give it a #[umbral(default = "...")] — some rows may already hold NULL. The migration engine backfills them as part of the same migration: on Postgres it emits UPDATE "t" SET "c" = <default> WHERE "c" IS NULL before ALTER COLUMN c SET NOT NULL; on SQLite the table-recreation dance copies the column as COALESCE(c, <default>). So the tighten applies cleanly against a populated table instead of aborting with column "c" contains null values / a failed rebuild.

Code
rust 
// Before: pub note: Option<String>
// After — existing NULL notes become '' at migrate time:
#[umbral(default = "")]
pub note: String,

(Without a default, makemigrations still refuses the tighten — there'd be no value to backfill the existing NULLs with.)

What NOT to do

  • Don't rm -f *.db to dodge a failing migration. The framework's CLAUDE.md spells this out: the failure IS the test, deleting your DB hides it and destroys real data.
  • Don't delete migration files in migrations/<plugin>/ to "regenerate cleanly." The history is part of the schema's audit trail; older deploys depend on it.
  • Don't ignore an UnsafeAlter error from makemigrations. It's telling you the column shape would fail on a populated table. Adjust the model to one of the three safe shapes above rather than reaching for a workaround.

Recovering when makemigrations errors

Because the autodetector refuses the unsafe shape, the common recovery is at makemigrations time, before anything touches the database:

  1. Read the error. It names the model and column and lists the three safe shapes.
  2. Adjust the model to one of those shapes (Option<T>, #[umbral(default = "...")], or an auto_now* timestamp).
  3. Re-run cargo run -- makemigrations to generate the now-safe operation, then cargo run -- migrate to apply it.

Nothing was written to disk and nothing reached the database, so there's no half-applied migration to clean up.

If an ALTER did fail at apply time
The NOT-NULL-without-default case is caught at `makemigrations` time. A *different* migration that was written and then failed mid-apply (e.g. a UNIQUE constraint that trips on existing duplicates) is transactional on both backends - your table is unchanged - and the file lives on disk but was never recorded in `umbral_migrations`. In that narrow case, fix the model, delete the never-applied last migration file, and re-run `makemigrations`. This is the one exception to CLAUDE.md's "never delete migration files" rule: it applies only to a file that was never recorded as applied.

Caveats

  • Direct-SQL writers can NULL out a "NOT NULL" auto_now column on SQLite when that column was added via ALTER to a populated table. The Rust ORM enforces non-null on every INSERT/UPDATE; nothing downstream of the ORM sees NULL in practice. If you have raw-SQL writers (analytics jobs, third-party tools), audit them or migrate the column via a table rebuild; the engine doesn't do that automatically.
  • Postgres users get the cleaner story: every auto_now column keeps DB-level NOT NULL whether added at CREATE or ALTER time. Prefer Postgres for production deploys (the framework already recommends this; see arch.md §9).
  • #[umbral(default = "...")] strings are passed through verbatim to the DDL as literal SQL. Use single quotes around string defaults yourself if needed (default = "'pending'"); booleans get auto-translated to 1/0 for SQLite.
Edit this page Report an issue
Previous
Recovering from migration drift
Next
Checking migrations for zero-downtime safety