TopolearnSchema changes in SQLite need a specific procedure; doing it naively loses data or breaks foreign keys.
SQLite's ALTER TABLE supports only a few operations:
RENAME TO new_name(rename the table),RENAME COLUMN old TO new,ADD COLUMN(with restrictions: noPRIMARY KEYorUNIQUE, and aNOT NULLcolumn needs a non-NULL default),DROP COLUMN(3.35+; fails if the column is indexed, part of a key, or used by a constraint, view or trigger).
Anything else — changing a type, adding a constraint, changing a default — needs a table rebuild. The documented safe procedure, in short:
1. PRAGMA foreign_keys = OFF (outside the transaction), then BEGIN. 2. CREATE TABLE new_t (...) with the desired definition. 3. INSERT INTO new_t SELECT ... FROM t. 4. DROP TABLE t; ALTER TABLE new_t RENAME TO t; 5. Recreate indexes, triggers and views on t. 6. PRAGMA foreign_key_check; then COMMIT and turn foreign keys back on.
Because SQLite DDL is transactional, a failure mid-way rolls back cleanly.
To track which migrations have run, store an integer in the file header with PRAGMA user_version = n and read it at startup: apply each migration whose number is higher, in a transaction, then bump the version. Libraries like Alembic do this for SQLAlchemy projects, with a "batch" mode that performs rebuilds for you.
Example
import sqlite3
MIGRATIONS = [
"CREATE TABLE user (id INTEGER PRIMARY KEY, name TEXT)",
"ALTER TABLE user ADD COLUMN email TEXT",
# v3: make email NOT NULL + UNIQUE -> needs a rebuild
'''CREATE TABLE user_new (id INTEGER PRIMARY KEY, name TEXT,
email TEXT NOT NULL UNIQUE);
INSERT INTO user_new SELECT id, name, coalesce(email, 'unknown' || id) FROM user;
DROP TABLE user;
ALTER TABLE user_new RENAME TO user;''',
]
def migrate(con):
version = con.execute("PRAGMA user_version").fetchone()[0]
for number, sql in enumerate(MIGRATIONS[version:], start=version + 1):
con.executescript(f"BEGIN; {sql}; PRAGMA user_version = {number}; COMMIT;")
return con.execute("PRAGMA user_version").fetchone()[0]
con = sqlite3.connect(":memory:")
print(migrate(con)) # 3
print(con.execute("SELECT sql FROM sqlite_schema WHERE name = 'user'").fetchone()[0])Remember
- ALTER TABLE can rename, add and drop columns, little else
- Other changes need the create-copy-drop-rename rebuild
- PRAGMA user_version is a simple, built-in migration counter
Watch out for
- Rebuilding a table with foreign keys still ON, which can cascade deletes into child tables when the old table is dropped.