Schema as Code

A schema that lives only as SQL someone ran by hand, once, against a server nobody quite remembers the state of, is a liability the moment a second environment needs the same tables. Writing schema creation as plain Python functions — one per table, all following the same shape — turns it into something that can be reviewed, versioned, and re-run with confidence.

One function, one table#

def create_table_events():    sql = """CREATE TABLE IF NOT EXISTS events (        event_id    BIGINT PRIMARY KEY,        occurred_at TIMESTAMP,        payload     TEXT    )"""    with conn.cursor() as cursor:        cursor.execute(sql)    conn.commit()

That is the whole pattern: build a CREATE TABLE IF NOT EXISTS string, execute it on a shared connection, commit. IF NOT EXISTS is what makes calling the function safe to repeat — a second run finds the table already there and does nothing, instead of raising an error.

Every other table in a real project follows this exact shape, changing only the name and the column list. That repetition is precisely the point: once the pattern is established, adding a table means writing one small, self-contained, independently testable function — not editing a growing SQL file where every statement depends on running in the right order by hand.

Rebuilding from a clean slate#

In a project still under active development, it is often easier to drop and recreate the whole database than to write migrations for every change. That requires connecting to a different database first — you cannot drop the one you are currently connected to:

admin_conn = psycopg.connect(dbname="postgres", ...)admin_conn.autocommit = Truewith admin_conn.cursor() as cursor:    cursor.execute("DROP DATABASE IF EXISTS app")    cursor.execute("CREATE DATABASE app")

autocommit = True matters: CREATE DATABASE and DROP DATABASE are statements Postgres refuses to run inside a multi-statement transaction block, so each one has to commit immediately on its own.

The guard that is easy to forget#

Not every table-creation function needs the same safety. A narrow, purely-additive table is sometimes written with a plain CREATE TABLE, without IF NOT EXISTS — which works only as long as the surrounding script always runs against a freshly recreated database. Call that function a second time against a database that already has the table, and it fails. The fix is one keyword; the trap is that nothing about the function's name tells you it is missing.

Common mistakes#

Treating the entry point as the place to add logic#

The function that calls every create_table_* should stay a flat list of calls. The moment it grows an if, a loop with side effects, or inline SQL of its own, the "each table is independent and testable" property is gone.

Forgetting IF NOT EXISTS outside a full-rebuild context#

Safe only when the database is always dropped and recreated first. Anywhere else, the missing guard turns a second run into a hard failure.

Building the schema by running SQL files by hand#

Without functions and a single entry point, "what schema does this environment actually have" has no reliable answer — it depends on which files someone remembered to run, and in what order.

Further reading#

• PostgreSQL: CREATE TABLE and CREATE/DROP DATABASE — the statements behind every function in this article.
• Infrastructure as code — the broader practice this pattern is a small instance of.
• Psycopg 3 documentation — cursor and transaction behavior in detail.

See also#