Cookbook¶
Practical recipes for common codegen_database use cases.
Migrations only¶
Use codegen_database purely as a migration generator — define your schema with codegen_database factories, produce Alembic migrations, and export them as raw SQL. No codegen_database code runs at application time.
Project layout:
myproject/
├── alembic.ini
├── models.py
└── migrations/
├── env.py
└── versions/
1. Define your schema
# models.py
from sqlalchemy import Column, MetaData, Numeric, String
from codegen_database import CodegenDatabaseBase, construct_naming_conventions_dict
metadata = MetaData(
naming_convention=construct_naming_conventions_dict(),
)
class Base(CodegenDatabaseBase):
metadata = metadata
class Products(Base):
__tablename__ = "products"
__table_args__ = {"schema": "inventory"}
name = Column(Text, nullable=False)
sku = Column(Text, nullable=False)
price = Column(Numeric(10, 2), nullable=False)
2. Wire up Alembic
Follow the standard Setting up a new project instructions: call
alembic_hook() early in env.py, call
configure_metadata() after loading your metadata, and pass
process_revision_directives and render_item to
context.configure().
3. Generate a migration
alembic revision --autogenerate -m "add products table"
Review the generated Python file in migrations/versions/.
4. Export as raw SQL
Alembic’s --sql flag renders migrations as plain SQL instead of
executing them against a database. This is useful when you hand
migrations off to a DBA, run them via CI, or apply them with psql
directly.
Generate the upgrade SQL for all pending migrations:
# Upgrade from nothing to head (full schema)
alembic upgrade head --sql > upgrade.sql
Generate SQL for a specific revision range:
# From one revision to another
alembic upgrade abc123:def456 --sql > upgrade.sql
# Downgrade SQL
alembic downgrade def456:abc123 --sql > downgrade.sql
The resulting .sql files are standalone — they have no dependency on
codegen_database or Python. You can commit them to your repo, review them in a
PR, or hand them to your ops team.
5. Apply with psql
psql -d mydb -f upgrade.sql
At this point codegen_database has done its job. Your application code never imports codegen_database — it only consumes the database schema that the migrations created.
Using codegen_database models in your application¶
codegen_database factories produce real SQLAlchemy tables registered on a shared
MetaData instance. This means you can query and insert data using
SQLAlchemy Core or ORM directly — in Flask, FastAPI, or any other
framework.
This recipe uses CodegenDatabaseBase to define
models declaratively. Each model class is ORM-mapped automatically at
definition time — access the generated table via
metadata.tables["<schema>.<tablename>"] to get a standard
sqlalchemy.schema.Table object.
FastAPI example¶
# app.py
from fastapi import FastAPI, HTTPException
from pydantic import BaseModel
from sqlalchemy import create_engine, select
from myapp.models import users
engine = create_engine("postgresql+psycopg://localhost/mydb")
app = FastAPI()
class UserCreate(BaseModel):
name: str
email: str
age: int | None = None
@app.get("/users")
def list_users():
with engine.connect() as conn:
rows = conn.execute(select(users)).mappings().all()
return [dict(r) for r in rows]
@app.get("/users/{user_id}")
def get_user(user_id: int):
with engine.connect() as conn:
row = conn.execute(
select(users).where(users.c.id == user_id)
).mappings().first()
if not row:
raise HTTPException(status_code=404)
return dict(row)
@app.post("/users", status_code=201)
def create_user(body: UserCreate):
with engine.begin() as conn:
result = conn.execute(
users.insert()
.values(**body.model_dump(exclude_none=True))
.returning(users)
)
return dict(result.mappings().first())
Flask example¶
# app.py
from flask import Flask, jsonify, request
from sqlalchemy import create_engine, select
from myapp.models import users
engine = create_engine("postgresql+psycopg://localhost/mydb")
app = Flask(__name__)
@app.get("/users")
def list_users():
with engine.connect() as conn:
rows = conn.execute(select(users)).mappings().all()
return jsonify([dict(r) for r in rows])
@app.post("/users")
def create_user():
data = request.get_json()
with engine.begin() as conn:
result = conn.execute(
users.insert().values(**data).returning(users)
)
return jsonify(dict(result.mappings().first())), 201
Indices and foreign keys¶
codegen_database supports declarative index and foreign key definitions
using {column_name} markers — the same syntax used by
CodegenDatabaseCheck.
Adding indices¶
Use CodegenDatabaseIndex inside __table_args__.
The constructor mirrors sqlalchemy.Index: name first, then
expressions, then keyword arguments passed through to the
underlying index.
from sqlalchemy import Column, Integer, String
from codegen_database.index import CodegenDatabaseIndex
class Products(Base):
__tablename__ = "products"
__table_args__ = (
# Simple index
CodegenDatabaseIndex("idx_products_sku", "{sku}"),
# Unique index
CodegenDatabaseIndex(
"uq_products_name", "{name}", unique=True
),
# Functional index with dialect kwargs
CodegenDatabaseIndex(
"idx_products_lower_name",
"lower({name})",
postgresql_using="btree",
),
# Multi-column index
CodegenDatabaseIndex(
"idx_products_name_price",
"{name}", "{price}",
),
{"schema": "inventory"},
)
name = Column(Text, nullable=False)
sku = Column(Text, nullable=False)
price = Column(Integer, nullable=False)
Adding foreign keys¶
Use CodegenDatabaseForeignKey inline on the Column
constructor — analogous to SQLAlchemy’s ForeignKey. The
reference string accepts two formats:
"dimension.column"— resolved via the dimension registry. codegen_database finds the correct physical table regardless of dimension type (simple vs append-only)."schema.table.column"— passed through to SQLAlchemy directly, bypassing resolution.
Dimension reference (resolved via registry):
from sqlalchemy import Column, Integer, String
from codegen_database.fk import CodegenDatabaseForeignKey
class Customers(Base):
__tablename__ = "customers"
__table_args__ = {"schema": "public"}
name = Column(String, nullable=False)
class Orders(Base):
__tablename__ = "orders"
__table_args__ = {"schema": "public"}
customer_id = Column(
Integer,
CodegenDatabaseForeignKey("customers.id", ondelete="CASCADE"),
nullable=False,
)
total = Column(Integer, nullable=False)
The "customers.id" reference is resolved to the physical table
via the dimension registry. If customers were an append-only
dimension, the FK would point to the root table automatically.
Raw three-part reference (bypass resolution):
Column(
"org_id", Integer,
CodegenDatabaseForeignKey("public.organizations.id"),
)
Use a three-part "schema.table.column" reference when targeting
tables outside codegen_database or when you want full control over the target.
See Constraints and indices for a walkthrough of the generated SQL.