Does SQLite guarantee rollback safety when ALTER TABLE ADD COLUMN with NOT NULL DEFAULT fails mid-rewrite?

Short answer

Yes. Within an active transaction and with an intact rollback journal or WAL, ROLLBACK restores the schema exactly as it was before ALTER TABLE ... ADD COLUMN ... NOT NULL DEFAULT. No error condition is universally guaranteed to leave the schema unchanged, and the guarantee does not survive power loss, process crash, disk full, I/O error, out-of-memory conditions, or file corruption.

The premise needs one correction: SQLite does not rewrite existing rows when you add a column. It appends the column definition to sqlite_master and stores the default expression. Rows written before the change read the new column as the default value; NOT NULL is enforced only for new inserts and updates. Because no row-copying phase exists, there is no "mid-rewrite" point where a partially rewritten table can be left on disk.

Confirmed behavior

• DDL is transactional in SQLite. An uncommitted ALTER TABLE modifies only the pending schema state; ROLLBACK removes it.
• A committed ALTER TABLE cannot be rolled back.
• ADD COLUMN does not backfill or scan existing rows.
• Schema recovery after statement-level failure depends on an intact connection and journal or WAL.

What is not guaranteed

• No error code during the schema change is universally safe.
• Crash, power loss, I/O error, disk full, or out-of-memory can leave the database requiring recovery.
• Crash-recovery details vary by SQLite version and journal mode.

Verification steps

1. Open a scratch database and start a transaction.
2. Run ALTER TABLE t ADD COLUMN b INTEGER NOT NULL DEFAULT 0.
3. Force a statement-level error, such as adding the same column again.
4. Run ROLLBACK.
5. Run PRAGMA table_info(t) and PRAGMA integrity_check; confirm column b is absent and the check returns ok.

BEGIN;
CREATE TABLE t(a INTEGER);
INSERT INTO t VALUES (1);
ALTER TABLE t ADD COLUMN b INTEGER NOT NULL DEFAULT 0;
ALTER TABLE t ADD COLUMN b INTEGER NOT NULL DEFAULT 0; -- duplicate column error
ROLLBACK;
PRAGMA table_info(t);
PRAGMA integrity_check;

If you are investigating a real failure, provide your SQLite version and journal mode (PRAGMA journal_mode); those details change the crash-recovery answer but not the basic rollback rule above.