SQLite migrations in production are a minefield, and the most surprising thing is that the "ideal" strategy often involves not changing the schema at all, at least not immediately.
Let’s see how this plays out with a simple user table. Imagine we have a users table like this:
CREATE TABLE users (
id INTEGER PRIMARY KEY AUTOINCREMENT,
username TEXT NOT NULL UNIQUE,
email TEXT UNIQUE
);
And our application code expects to find username and email. Now, we need to add a signup_date column.
If we were to just run ALTER TABLE users ADD COLUMN signup_date DATETIME;, SQLite would happily create the new column, but what value would existing rows get? By default, it’s NULL. This is often fine.
import sqlite3
conn = sqlite3.connect(':memory:')
cursor = conn.cursor()
# Initial schema
cursor.execute("""
CREATE TABLE users (
id INTEGER PRIMARY KEY AUTOINCREMENT,
username TEXT NOT NULL UNIQUE,
email TEXT UNIQUE
);
""")
cursor.execute("INSERT INTO users (username, email) VALUES ('alice', 'alice@example.com')")
cursor.execute("INSERT INTO users (username, email) VALUES ('bob', 'bob@example.com')")
conn.commit()
print("--- Before Migration ---")
cursor.execute("PRAGMA table_info(users);")
print(cursor.fetchall())
cursor.execute("SELECT * FROM users;")
print(cursor.fetchall())
# --- Migration ---
print("\n--- Performing Migration ---")
cursor.execute("ALTER TABLE users ADD COLUMN signup_date DATETIME;")
conn.commit()
print("\n--- After Migration ---")
cursor.execute("PRAGMA table_info(users);")
print(cursor.fetchall())
cursor.execute("SELECT * FROM users;")
print(cursor.fetchall())
conn.close()
Output:
--- Before Migration ---
[(0, 'id', 'INTEGER', 1, None, 1), (1, 'username', 'TEXT', 1, None, 0), (2, 'email', 'TEXT', 0, None, 0)]
[(1, 'alice', 'alice@example.com'), (2, 'bob', 'bob@example.com')]
--- Performing Migration ---
--- After Migration ---
[(0, 'id', 'INTEGER', 1, None, 1), (1, 'username', 'TEXT', 1, None, 0), (2, 'email', 'TEXT', 0, None, 0), (3, 'signup_date', 'DATETIME', 0, None, 0)]
[(1, 'alice', 'alice@example.com', None), (2, 'bob', 'bob@example.com', None)]
Notice how signup_date is added and existing rows have NULL for it. This is the "no schema change" part of the strategy: the existing schema is preserved, and new data can be added with the new column.
The mental model here is that SQLite’s ALTER TABLE is quite limited. It supports ADD COLUMN, RENAME TABLE, and RENAME COLUMN (in newer versions). Complex changes like dropping columns, changing column types, or adding constraints often require a more involved, multi-step process: create a new table with the desired schema, copy data over, drop the old table, and rename the new one. This is risky in production.
The problem this solves is the inherent fragility of modifying a live database schema. Downtime is bad, and data corruption is worse. The "no schema change" strategy, where possible, minimizes the surface area for error. Instead of forcing a schema change that might break existing application logic or data integrity, you allow the schema to evolve by adding nullable columns. Your application code then needs to be updated to handle both the old schema (where the new column is NULL) and the new schema (where the new column has a value).
The exact levers you control are the ALTER TABLE ADD COLUMN statement and how your application code gracefully handles NULL values for newly introduced fields. Forcing NOT NULL on a new column without a default value will fail immediately for existing rows. If you need a non-null value, you must provide a DEFAULT clause, like ALTER TABLE users ADD COLUMN signup_date DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP;. This adds the column, ensures new rows have a timestamp, and populates existing rows with the current timestamp.
The core of this strategy is realizing that your application can often tolerate a slightly "older" schema for a period. You deploy application code that can read the new column (treating NULL as "not set") and write to the new column. Then, you can perform the schema migration. This phased approach minimizes the blast radius of a failed deployment.
The real trick is when you must remove a column or change its type. Then you’re forced into the create-copy-drop-rename dance, which is best orchestrated by a robust migration tool (like Alembic for Python, or built-in framework tools) that can manage the steps and rollbacks.
The next concept you’ll run into is how to handle backward compatibility when you need to remove a column or change a data type.