SQLite add column with Schema Diff Studio
A notes app schema is loaded before and after a feature adds five columns. Four of them are allowed in place: a NOT NULL flag with a default, a foreign key with a NULL default, a CHECK column and a virtual generated column. The fifth, created_at with DEFAULT CURRENT_TIMESTAMP, is something SQLite refuses to add, so that table gets the documented rebuild. Everything runs in SQLite in your tab.
- Get the current schema with sqlite3 app.db .schema and paste it into Before, or open the .sqlite / .db file directly (only the schema is read, never the rows).
- Write the tables as they should be into After, with the new columns inline.
- Press Compare. Columns SQLite can add in place appear as "Add column"; a table that needs more is listed as "Rebuild table" with the reason, for example "add column created_at (non-constant default)".
- Open Migration: the folders rebuild (create folders__new, copy rows, drop, rename) and the four ADD COLUMN lines run in one transaction, with foreign keys switched off around it. Check Verified and copy it (the .sql download is Pro).
What to know
SQLite's ALTER TABLE ADD COLUMN has fixed rules, listed in its documentation. The new column may not be PRIMARY KEY or UNIQUE. Its default may not be CURRENT_TIME, CURRENT_DATE, CURRENT_TIMESTAMP or an expression in parentheses. If it is NOT NULL it needs a non-NULL default. With foreign key enforcement on, a REFERENCES column must default to NULL. And it may not be a STORED generated column, though a VIRTUAL one is fine. Break one of these and SQLite answers with errors such as "Cannot add a column with non-constant default".
The rule about CURRENT_TIMESTAMP surprises people, because the same default is fine in CREATE TABLE. ADD COLUMN works by changing only the stored CREATE statement, never touching existing rows, and old rows then read the default on the fly; a timestamp that changes with every read cannot work that way. The fixes are to rebuild the table (what this tool writes), or to add the column as nullable and fill it with UPDATE folders SET created_at = CURRENT_TIMESTAMP, then set the value from your application on insert.
Because ADD COLUMN only rewrites the schema text, it is instant at any table size. A rebuild copies every row, so it takes time and disk space proportional to the table, and it needs the 12-step procedure from the SQLite docs: foreign keys off, a new table, copy, drop, rename, recreate indexes, triggers and views, run PRAGMA foreign_key_check, then switch foreign keys back on. The generated script does that, and PRAGMA foreign_keys is set outside the transaction because inside one it has no effect.
Since SQLite 3.37, CHECK constraints on an added column are tested against existing rows, so ADD COLUMN color TEXT CHECK (...) fails if a default would violate the check. Columns are always appended at the end; SQLite has no ADD COLUMN ... AFTER. If column order matters to you (it should not to SQL that names its columns), only a rebuild can change it.
Updated · Cosmovex
Questions
Why does SQLite say "Cannot add a column with non-constant default"?
ADD COLUMN cannot use CURRENT_TIMESTAMP, CURRENT_DATE, CURRENT_TIME or a parenthesised expression as the default, because existing rows are never rewritten. Rebuild the table with the new column, which this tool generates, or add the column without the default and backfill it with an UPDATE.
How do I add a NOT NULL column to an existing SQLite table?
Give it a non-NULL default in the same statement: ALTER TABLE notes ADD COLUMN pinned INTEGER NOT NULL DEFAULT 0. Without a default SQLite rejects it, and the only alternative is a table rebuild.
Can I add a foreign key column with ALTER TABLE in SQLite?
Yes, as long as the new column defaults to NULL: ALTER TABLE notes ADD COLUMN folder_id INTEGER REFERENCES folders (id). Adding a foreign key to a column that already exists is not possible with ALTER TABLE and needs a rebuild.
Can I add several columns in one ALTER TABLE statement?
No. SQLite accepts one ADD COLUMN per statement, so the migration has one ALTER TABLE line per new column. They run in one transaction, so either all are added or none.
Can I choose where the new column goes?
No. SQLite always adds the column at the end of the table. Changing the column order needs a table rebuild, and code that names its columns in SELECT and INSERT does not depend on the order.
The free plan covers everything on this page. Schema Diff Studio Pro ($29, paid once) is described on the Schema Diff Studio page.
