October 12, 20263 min read

Add a Non-NULL Column to an Existing SQLite Table

Give existing SQLite orders a new column, inspect Ptah’s generated SQL, and check preserved rows, foreign keys, replay, and rollback.

Simon HartSolutions Engineer

Ptah helps you plan, review, and apply database migrations. Try in your browser

A new column needs a value for the rows that already exist. In our SQLite order service, we add note as non-NULL text. The agreed value for an order without a note is the empty string. Non-NULL does not mean nonempty here.

Using Ptah 0.13.0, we generated and applied the change to a database with an existing queued order. We checked that row, checked foreign keys, replayed the files on an empty database, and exercised the down migration. The example files also retain SQLite’s refusal when the new column has no default.

Make the existing-row behavior part of the schema

Start with the four-table shop from the first-project walkthrough. Its first migration is already applied, and one customer and one order exist.

In schema.sql, add note text NOT NULL DEFAULT '' to orders. Ptah compares that file with the live SQLite database:

ptah migrations generate --db-url sqlite://shop.db --schema-file schema.sql --migrations-dir migrations --name order_note

The generated up file contains this SQL:

-- Migration generated from schema differences
-- Generated on: 2026-10-12T08:56:58+02:00
-- Direction: UP

ALTER TABLE "orders" ADD COLUMN "note" TEXT NOT NULL DEFAULT '';

The result is a direct ADD COLUMN. This case did not rebuild the table. The default encodes our decision for existing orders and future inserts that omit the new field. It does not invent meaningful notes for old orders.

SQLite documents a non-NULL default as a requirement when adding a column with a NOT NULL constraint. That engine rule is part of the design, even when a migration tool prepares the SQL. See SQLite’s ADD COLUMN rules.

Deploy and read the old order

ptah migrations hash --dir migrations
ptah migrations up --db-url sqlite://shop.db --migrations-dir migrations --verify-sum
ptah schema compare --db-url sqlite://shop.db --schema-file schema.sql --exit-code

The migration and comparison passed. A separate SQLite client then read the order and ran PRAGMA foreign_key_check:

[(1, 'queued', '')]
foreign_key_check: []
sqlite_version: 3.53.1

The existing row retained its queued status and acquired the empty note. The empty foreign-key result means that check found no violations in this fixture. The reported SQLite 3.53.1 is the version used by the Python client; Ptah uses its own embedded driver.

The check queries make the data assertion visible. A passing schema comparison alone would not prove that the old order survived or that its status stayed queued.

Replay and reverse this exact change

We applied both reviewed migrations to a separate empty replay.db and compared it with the same desired schema:

ptah migrations up --db-url sqlite://replay.db --migrations-dir migrations --verify-sum
ptah schema compare --db-url sqlite://replay.db --schema-file schema.sql --exit-code

Both commands exited 0. We then used the original disposable database to reverse only the note migration:

ptah migrations down --db-url sqlite://shop.db --migrations-dir migrations --verify-sum --target 1791539236 --confirm

The down file removes note. It is suitable for this synthetic exercise, where no note needs preserving. Reversing it after users have written notes would discard those values; review that consequence before choosing rollback.

With the column removed, we tried the alternative that omitted its default:

ALTER TABLE orders ADD COLUMN note text NOT NULL;

The SQLite client refused it with this exact error line:

sqlite3.OperationalError: Cannot add a NOT NULL column with default value NULL

Reapplying the reviewed migration with its default succeeded. The failure and successful reapply used the same existing order, rather than two empty databases that could hide the compatibility problem.

Let the engine shape the test

This example establishes the behavior of one column addition. A change that alters keys, removes a referenced column, or rebuilds a SQLite table needs different checks. Consult the SQLite guide and write a migration test for the rows and relations that your particular change must preserve.

To run the same order service on a server database, follow the PostgreSQL walkthrough.

Put Ptah to work

Example files

← All articles