skip to content

Table Metadata & Reflection

MetaData collects Table and Column objects with their constraints, emits CREATE statements and reflects live schemas. Interviewers ask why constraint names need a naming convention.

on this pageshow

explore

questions

5

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.

open as a page

Why do teams set SQLAlchemy's MetaData(naming_convention=...), and what names do its ix, uq, ck, fk and pk templates produce?

level: middleimportance: should knowfreq 40%

basics

~20 s

MetaData(naming_convention=...) gives every unnamed constraint and index a predictable name when its Table is built, so later DDL can refer to it. Without it SQLAlchemy names only indexes; other unnamed constraints get whatever name the database assigns.

open as a page

In SQLAlchemy, how do Table(..., autoload_with=engine), MetaData.reflect() and inspect(engine) differ when you read an existing database's schema?

level: middleimportance: should knowfreq 35%

basics

~10 s

Table(name, metadata, autoload_with=engine) builds one Table from the live database, MetaData.reflect() does that for many tables at once, and inspect(engine) returns an Inspector whose methods return plain lists and dicts, not Table objects.

open as a page

On PostgreSQL, SQLAlchemy's metadata.drop_all() raises CircularDependencyError for accounts and invoices tables whose foreign keys reference each other, though create_all() worked. Why, and how do you fix it?

level: seniorimportance: should knowfreq 20%

basics

~20 s

create_all() breaks the cycle with ALTER TABLE ... ADD FOREIGN KEY, which needs no name. drop_all() must break it with ALTER TABLE ... DROP CONSTRAINT, which does. Name the cycle's foreign keys, ideally through an fk naming convention.

open as a page

In SQLAlchemy, how do you decide between declaring a legacy billing database's tables as Table objects and reflecting them at startup, when another team owns that database?

level: principalimportance: nice to knowfreq 15%

basics

~20 s

Declare the tables your code writes or depends on closely: declared Table objects are reviewable and work offline. Reflect wide, read-only schemas another team changes, accepting startup catalog queries, lost Python-side detail and drift found only at query time.

open as a page