SQLite Basics: Add, Rename, and Remove Table Columns

A schema often needs to evolve after rows already exist. SQLite’s ALTER TABLE handles several common changes without asking you to export and reload every column yourself. Start with an isolated rehearsal and verify both the remaining data and the objects that depend on the changed column.

All three examples require SQLite 3.35.0 or later because the final example uses DROP COLUMN. RENAME COLUMN itself arrived in 3.25.0. Only the installed runtime below was executed. These disposable databases are practice data, not instructions to modify a production database without a backup.

Prepare a scratch directory

Tested on Linux with Python 3.12.14 and SQLite 3.53.1. You need Bash, Python 3 with SQLite support, a text editor, and mktemp, rm, and rmdir. No additional Python packages are needed. Keep the same terminal open and save files as UTF-8.

start_dir="$PWD"
work_dir="$(mktemp -d)" || exit 1
cd -- "$work_dir" || exit 1

The standalone SQLite CLI was unavailable on the test machine. Save this runner as run-sql.py. Each invocation uses a fresh in-memory database and prints pipe-separated rows, displaying missing values as NULL. The toy database disappears when the connection closes. Put each statement’s ending semicolon at the end of a line, as shown; do not put two complete statements on one line. This is a teaching runner, not a general SQL shell or CSV exporter.

from pathlib import Path
import sqlite3
import sys

connection = sqlite3.connect(":memory:", isolation_level=None)
try:
    statement = ""
    for line in Path(sys.argv[1]).read_text(encoding="utf-8").splitlines(True):
        statement += line
        if sqlite3.complete_statement(statement):
            cursor = connection.execute(statement)
            for row in cursor:
                print("|".join("NULL" if value is None else str(value) for value in row))
            statement = ""
    if statement.strip():
        raise ValueError("Unfinished SQL statement")
finally:
    connection.close()

1. Add a required field with a useful default

Save as add-status-column.sql:

CREATE TABLE jobs (id INTEGER PRIMARY KEY, title TEXT NOT NULL);
INSERT INTO jobs VALUES (1, 'Write guide');
ALTER TABLE jobs ADD COLUMN status TEXT NOT NULL DEFAULT 'queued';
INSERT INTO jobs (id, title) VALUES (2, 'Check links');
UPDATE jobs SET status = 'done' WHERE id = 1;
SELECT id, title, status FROM jobs ORDER BY id;

Run:

python3 run-sql.py add-status-column.sql

Output:

1|Write guide|done
2|Check links|queued

The old row gains a readable status, and the later INSERT can omit status because the column has a default. The update changes only the first job. Naming inserted columns also keeps application code clearer when a table gains another field.

For a populated table, adding NOT NULL requires an appropriate non-null default. Pick a value that is actually true for historical data; queued would be misleading if earlier jobs were already finished. A staged migration with a nullable field and deliberate backfill may fit that case better. A default alone also does not restrict future statuses to a known vocabulary.

2. Rename a column used by a view and index

Save as rename-label-column.sql:

CREATE TABLE tasks (id INTEGER PRIMARY KEY, label TEXT NOT NULL);
CREATE INDEX tasks_label ON tasks(label);
CREATE VIEW task_labels AS SELECT id, label FROM tasks;
INSERT INTO tasks VALUES (1, 'Review draft');
ALTER TABLE tasks RENAME COLUMN label TO title;
SELECT id, title FROM task_labels ORDER BY id;
PRAGMA index_info(tasks_label);

Run:

python3 run-sql.py rename-label-column.sql

Output:

1|Review draft
0|1|title

The view can now select title, and the index-info row identifies title as the indexed column. The index’s object name remains tasks_label: changing a column reference does not mean every human-chosen object name is renamed too. The view’s object name similarly stays task_labels.

SQLite updates dependent schema references for a supported column rename, but it cannot rewrite SQL strings inside your application or a saved report outside the database. Check those callers before deploying the new name. A rename that introduces ambiguity in a dependent view or trigger is rejected rather than guessed. This example uses simple, unambiguous references.

3. Remove an unused column while preserving the other data

Save as drop-debug-column.sql:

CREATE TABLE notes (
    id INTEGER PRIMARY KEY,
    body TEXT NOT NULL,
    debug_label TEXT
);
INSERT INTO notes VALUES
    (1, 'Keep this note', 'temporary'),
    (2, 'Keep this one too', NULL);
BEGIN;
ALTER TABLE notes DROP COLUMN debug_label;
COMMIT;
SELECT id, body FROM notes ORDER BY id;
PRAGMA table_info(notes);

Run:

python3 run-sql.py drop-debug-column.sql

Output:

1|Keep this note
2|Keep this one too
0|id|INTEGER|0|NULL|1
1|body|TEXT|1|NULL|0

The notes remain, and table_info lists only id and body. Its fields are column position, name, declared type, NOT NULL flag, default value, and primary-key position. The runner displays missing default expressions as NULL. The primary-key marker is separate from the reported NOT NULL flag in this ordinary table.

DROP COLUMN rewrites table content and refuses many dependency conflicts, such as an index or view still referring to the column. Our debug field has none. Inspect dependencies and take a tested backup before a real deletion; a committed drop removes values you may later discover were useful. For a rehearsal, rolling back before COMMIT can restore the schema and its data.

SQLite’s ALTER TABLE reference explains restrictions and dependencies. The 3.25.0 and 3.35.0 release notes establish the rename and drop requirements. Newer versions also support some additional alterations; use the documentation for the runtime you deploy rather than assuming every schema change has a direct command.

Clean up

In the same terminal, remove only these example files and the empty scratch directory:

rm -- run-sql.py add-status-column.sql rename-label-column.sql drop-debug-column.sql
cd -- "$start_dir" || exit 1
rmdir -- "$work_dir"
unset start_dir work_dir

Acknowledgements

This post was created with assistance from AI.