Parameters, Transactions, and Versioned Migrations

By the end of this lesson you can pass every value into a query as a bound parameter, group related writes so they succeed or fail together, and change a table's shape without losing the rows already in it.

Three things separate a database you can trust from one you cannot.

The first is how values get into queries. Building SQL by joining strings means any text that reaches the query can change what the query is, not just what it matches. That is the vulnerability behind a large share of real data breaches, and the fix is one character.

The second is what happens when a multi-step write fails halfway. Without a transaction, the first two statements are permanent and the third is not, leaving a state your program never intended and cannot recognise.

The third is change. Every schema is wrong eventually. A stored dataset needs a way to gain a column without losing its rows, and without depending on anyone remembering to run the right statement by hand.

Parameters. Put a ? where the value belongs and pass the values separately:

connection.execute(
    "SELECT notice_id FROM notices WHERE agency_code = ? AND amount >= ?",
    ("GSA", 100000),
)

The database receives the statement and the values along separate paths. A value can never be read as SQL, because it is not on the path SQL arrives by. That is why parameter binding is a guarantee rather than an improvement over careful escaping.

Two practical details. Positional parameters need a sequence of values; a one-item tuple is written (value,), including the trailing comma. And parameters bind values, never table or column names; if something in your program chooses a column name at run time, that value must be checked against an allowlist you wrote, because there is no placeholder for it.

Transactions. A transaction groups statements so they all take effect or none do. In Python's sqlite3, connection.commit() makes the current transaction permanent and connection.rollback() discards it. Using the connection as a context manager commits an open transaction on success and rolls it back if an exception escapes. Entering with connection: does not itself begin a transaction. With the connection settings used here, an INSERT starts one implicitly; start one explicitly with BEGIN before a migration whose first statement changes the schema:

with connection:
    connection.execute("INSERT INTO notices VALUES (?, ?)", ("A-9", "GSA"))
    connection.execute("INSERT INTO shortlist VALUES (?)", ("A-9",))

If the second statement raises, the first is undone. Without that, a notice exists with no shortlist entry, and nothing in the data says why.

Migrations. A migration is one numbered file containing the statements that move a schema from one version to the next: 0001_init.sql, 0002_add_set_aside.sql. The rules that make them work:

• Numbered and applied in order, once each. Record which have run, in the database itself.
• Forward-only. To undo something, write a new migration; editing an applied one means two databases with the same version number and different shapes.
• Safe on an empty database and on a populated one. Both cases get tested.
• Additive where possible. Adding a nullable column is cheap and cannot lose data; dropping or renaming one needs a plan for the code that still reads it.

ALTER TABLE notices ADD COLUMN set_aside TEXT is the common case. Existing rows get NULL for the new column, which is exactly the honest value: those notices were stored before the field was recorded, and pretending otherwise would invent data.

This is not theoretical for this project. The learning platform you are reading runs on Cloudflare D1, which is SQLite-compatible, and its schema is managed by exactly this pattern: numbered files in a migrations/ directory, applied in order, tested against both an empty and a populated database.

import sqlite3

connection = sqlite3.connect(":memory:")
connection.executescript(
    """
    CREATE TABLE schema_version (version INTEGER NOT NULL);
    INSERT INTO schema_version VALUES (1);
    CREATE TABLE notices (notice_id TEXT PRIMARY KEY, agency_code TEXT NOT NULL);
    INSERT INTO notices VALUES ('A-1', 'GSA');
    """
)

# Migration 0002: record the set-aside category.
with connection:
    connection.execute("BEGIN")
    connection.execute("ALTER TABLE notices ADD COLUMN set_aside TEXT")
    connection.execute("UPDATE schema_version SET version = 2")

connection.execute(
    "INSERT INTO notices (notice_id, agency_code, set_aside) VALUES (?, ?, ?)",
    ("A-2", "DLA", "Total Small Business"),
)
connection.commit()

print(connection.execute("SELECT version FROM schema_version").fetchone()[0])
print(connection.execute("SELECT notice_id, set_aside FROM notices ORDER BY notice_id").fetchall())
print(connection.execute("SELECT COUNT(*) FROM notices WHERE set_aside IS NULL").fetchone()[0])

The migration explicitly begins a transaction before ALTER TABLE, so the schema change and version bump are one unit committed on normal exit from the with block. Without BEGIN, merely entering this block would not start a transaction for the schema change with these connection settings. A crash between them would otherwise leave a database whose recorded version disagrees with its actual shape, which is the worst state a migration system can be in.

The pre-existing row keeps its data and gains a NULL for the new column. After the migration commits, a separate insert supplies the new row through bound parameters and is committed explicitly. Both commits affect this in-memory database; closing its connection still discards the whole database.

The last query counts rows whose set-aside is NULL. In this fixture that is the pre-existing row, but a later insert can also supply or default to NULL; the count measures missing values, not row age.

Predict the three printed lines.

2
[('A-1', None), ('A-2', 'Total Small Business')]
1

Change the ALTER TABLE line to ALTER TABLE notices ADD COLUMN set_aside TEXT NOT NULL, and predict what happens.

sqlite3.OperationalError: Cannot add a NOT NULL column with default value NULL

An assistant was asked for a search by agency code. It produced this and said the input is escaped.

def find_by_agency(connection, agency_code):
    sql = "SELECT notice_id FROM notices WHERE agency_code = '" + agency_code + "'"
    return connection.execute(sql).fetchall()


print(find_by_agency(connection, "GSA"))
print(find_by_agency(connection, "GSA' OR '1'='1"))

SELECT notice_id FROM notices WHERE agency_code = 'GSA' OR '1'='1'

def find_by_agency(connection, agency_code):
    return connection.execute(
        "SELECT notice_id FROM notices WHERE agency_code = ?", (agency_code,)
    ).fetchall()

The query below is built by concatenation and is handed an input designed to end the statement early. Rewrite it with parameter binding, then confirm both that the input matches nothing and that the table is intact.

Loading this exercise…

Optional: write migration 0003 that adds a nullable posted_date column and updates the schema version. Start BEGIN inside a with connection: block before ALTER TABLE, on a connection with no pending transaction, and confirm the pre-existing rows survive with NULL in the new column. Then deliberately put a failing statement after ALTER TABLE inside the same transaction, run it on a fresh copy of the version-2 database, and confirm both that the new column is absent and that the schema version did not advance. Proving to yourself that a failed migration leaves no half-applied state is worth far more than reading that it should.

Sign in to track your progress on this exercise.

1. Why is parameter binding a guarantee rather than a careful habit?
2. What cannot be passed as a parameter, and what should you do instead?
3. What does a with connection: block do when a statement inside it raises?
4. Why must a migration never be edited after it has been applied?

Sign in to track your progress on this exercise.

Every value binds as a parameter, names are allowlisted rather than interpolated, related writes belong in one transaction, and migrations are numbered, forward-only, and tested on both empty and populated databases. The assistant now has durable storage it can be trusted with. Module 10 turns the whole thing into a command you can run twice without fear.