skip to content

In SQLAlchemy Core, how do MetaData, Table and Column fit together, and what does metadata.create_all() do with a table that already exists?

level: juniorimportance: must knowfreq 55%

answer

  1. a registry of schema objects
  2. tables keyed by name
  3. an existence check before CREATE
  4. no ALTER path at all

basics

~20 s

MetaData holds Table objects, each built from Column and constraint objects. metadata.create_all(engine) checks which tables already exist, creates only the missing ones in foreign-key order, and never alters an existing table, even when its Python definition changed.

solid answer

~40 s

`MetaData` is a registry: `Table("invoices", metadata, Column(...), ...)` registers the table in `metadata.tables`, and each `Column` carries a name, a type and flags such as `primary_key`, `nullable`, `unique`, `index` and a `ForeignKey("accounts.id")`. `metadata.create_all(engine)` first checks which tables exist (by default it checks every kind of object; SQLAlchemy 2.1 takes a `CheckFirst` flag or a plain bool), then emits `CREATE TABLE` only for the missing ones, sorted so `accounts` comes before `invoices`, followed by those tables' indexes. A table that already exists is skipped whole: a column or `Index` you added to it in Python is never emitted, because `create_all()` compares names, not definitions. Changing a live table is a migration tool's job. `drop_all()` is the mirror image, dropping in reverse dependency order.

code

python · 25 lines
python
from sqlalchemy import Column, ForeignKey, Integer, MetaData, Numeric, String, Table, create_engine, inspect

metadata = MetaData()

accounts = Table(
    "accounts",
    metadata,
    Column("id", Integer, primary_key=True),
    Column("email", String(255), nullable=False, unique=True),
)

invoices = Table(
    "invoices",
    metadata,
    Column("id", Integer, primary_key=True),
    Column("account_id", ForeignKey("accounts.id"), nullable=False, index=True),
    Column("total", Numeric(12, 2), nullable=False),
)

engine = create_engine("sqlite://")
metadata.create_all(engine)  # CREATE TABLE accounts, CREATE TABLE invoices, CREATE INDEX
metadata.create_all(engine)  # existence checks only: both tables exist, nothing is emitted

print([t.name for t in metadata.sorted_tables])  # ['accounts', 'invoices']
print(inspect(engine).get_table_names())  # ['accounts', 'invoices']

go deeper

for a junior

Recall the roles: MetaData collects tables, Table groups columns and constraints, Column describes one column. Then say plainly that create_all() only creates what is missing.

for a middle

Walk through create_all(): the existence check controlled by checkfirst, the foreign-key sort, CREATE TABLE then CREATE INDEX, and why an index added to an existing table never appears.

for a senior

Show you know where create_all() stops being safe: a changed table is silently skipped, so a service that relies on it at startup drifts from its own code without any error until a query fails.

for a principal

Frame create_all() as a bootstrap tool for empty databases and tests, and say where your team's schema changes go instead, so nobody mistakes a clean startup for an up-to-date schema.

## The three building blocks SQLAlchemy Core describes a database schema with plain Python objects, before any connection exists. - **`MetaData`** is the collection. Every `Table` constructed against it is registered in `metadata.tables`, a dictionary keyed by the table name, or by `schema.name` when the table has a `schema=` argument. `create_all()`, `drop_all()`, `sorted_tables` and `reflect()` all operate on one `MetaData`. - **`Table`** takes a name, the `MetaData` to register in, then `Column` objects and table-level schema items such as `UniqueConstraint`, `CheckConstraint`, `ForeignKeyConstraint` and `Index`. Asking for the same name again in the same `MetaData` hands back the existing object; passing new columns that way raises `InvalidRequestError` unless you add `extend_existing=True`. - **`Column`** carries a name, a type such as `Integer`, `String(255)` or `Numeric(12, 2)`, and flags: `primary_key`, `nullable` (`True` unless the column is in the primary key or has an `Identity`), `unique=True`, which adds a `UniqueConstraint`, and `index=True`, which adds an `Index`. - **`ForeignKey("accounts.id")`** names its target as a string, so tables may be declared in any order. When a `Column` holds a `ForeignKey` and no type, it takes the referenced column's type. ## A shared accounts-and-invoices package Two services can import one module that holds the schema as module-level objects: ```python metadata = MetaData() accounts = Table("accounts", metadata, Column("id", Integer, primary_key=True)) invoices = Table( "invoices", metadata, Column("id", Integer, primary_key=True), Column("account_id", ForeignKey("accounts.id"), nullable=False, index=True), ) ``` Importing that module runs no SQL. The objects are descriptions; both services then use `invoices.c.account_id` in `select()`, `insert()` and the rest of Core. ## What create_all() does, step by step 1. With an `Engine` it opens a connection and a transaction (`engine.begin()`); with a `Connection` it uses yours. 2. It checks which of the metadata's tables already exist. The `checkfirst` argument controls the check, and its default checks everything. SQLAlchemy 2.1 batches the table check into fewer round trips on dialects that implement it, PostgreSQL among them. 3. It sorts the missing tables by foreign-key dependency, so `accounts` is created before `invoices`. 4. For each missing table it emits `CREATE TABLE`, then `CREATE INDEX` for that table's indexes. 5. The transaction commits when the block ends. Run it again and step 2 finds both tables, so nothing is emitted. That makes it safe for a fresh database: a test run, a prototype, a local container. ## What create_all() never does - add a column you appended to an existing `Table`; - create an `Index` declared on a table that already exists, since indexes are emitted only for tables created in the same call; - change a type, a nullability or a server default; - drop a table or a column you deleted from the Python code. The existence check asks only whether a table of that name exists; it never compares definitions. Evolving a live schema with `ALTER TABLE` belongs to a migration tool, a separate subject. ## The checkfirst argument in 2.1 | value | what is checked before CREATE | |---|---| | `True` or `CheckFirst.ALL` (the `create_all()` and `drop_all()` default) | tables, views, indexes, sequences and server-side types | | `CheckFirst.TABLES` | tables only | | `False` or `CheckFirst.NONE` | nothing; an existing table makes the database raise | `CheckFirst` is a flag enumeration new in 2.1, so values combine with `|`. `Table.create(engine)` keeps a narrower default, `CheckFirst.TYPES`, so it does not look for the table at all and fails if it exists, unless you pass `checkfirst=True`. ## drop_all() and sorted_tables `metadata.sorted_tables` lists tables in creation order, referenced tables first; `reversed()` gives drop order. `drop_all()` drops in that reverse order and by default skips tables that are absent. Tables whose foreign keys reference each other break the sort, which is where constraint names start to matter. ## Where create_all() fits - **Test fixtures**: build a fresh schema at the start of a run and call `drop_all()` at the end, on a database that holds nothing else. - **Prototypes and local databases**: get from an empty database to a working schema with one call. - **Inside a larger transaction**: pass a `Connection` instead of an `Engine`, and the DDL joins the transaction you already opened, on backends whose DDL is transactional. The same call serves ORM code: declarative classes register their tables on a `MetaData` too, so `create_all()` behaves identically there. What it never replaces is a reviewed, versioned change to a database that already holds data.

  • What does invoices.create(engine) do in SQLAlchemy 2.1 if the invoices table already exists?
    `Table.create()` defaults to `checkfirst=CheckFirst.TYPES`, which checks only server-side types, so it emits `CREATE TABLE` without looking for the table and the database rejects it; SQLite raises `OperationalError` saying the table already exists. Pass `checkfirst=True` to make it skip an existing table the way `MetaData.create_all()` does.
  • You add a country column to accounts in the shared package and restart a service that calls create_all(). Why does a select of that column fail?
    `create_all()` sees that `accounts` exists and skips it, so no `ALTER TABLE ... ADD COLUMN` is ever emitted. The Python `Table` now names a column the database lacks, and the first statement that selects it fails with a database error about an unknown column. The column has to be added by a migration.
  • Can create_all() create only some of the tables in a MetaData?
    Yes: `metadata.create_all(engine, tables=[invoices])` considers only the listed `Table` objects and ignores the rest. It does not add the tables they reference, so on a backend that enforces foreign keys you list `accounts` too, or create it first.

  • create_all() adds new columns to tables that already exist
  • create_all() drops and recreates a table whose definition changed
  • Calling create_all() twice fails because the tables already exist
  • MetaData stores rows or caches query results
  • Declaring a Table opens a database connection at import time

Assumes SQLAlchemy 2.1; the CheckFirst enumeration and batched table-existence checks in create_all() are new in 2.1, and boolean checkfirst values still work.