Cookbook¶
Practical recipes for common pgcraft use cases.
Migrations only¶
Use pgcraft purely as a migration generator — define your schema with pgcraft factories, produce Alembic migrations, and export them as raw SQL. No pgcraft code runs at application time.
To add PostgREST API views, see PostgREST extension.
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 pgcraft.factory import PGCraftSimple
from pgcraft import pgcraft_build_naming_conventions
metadata = MetaData(
naming_convention=pgcraft_build_naming_conventions(),
)
PGCraftSimple(
tablename="products",
schemaname="inventory",
metadata=metadata,
schema_items=[
Column("name", String, nullable=False),
Column("sku", String(32), nullable=False),
Column("price", Numeric(10, 2), nullable=False),
],
)
2. Wire up Alembic
Follow the standard Setting up a new project instructions: call
pgcraft_alembic_hook() early in env.py, call
pgcraft_configure_metadata() after loading your metadata, and pass
pgcraft_process_revision_directives 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
pgcraft 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 pgcraft has done its job. Your application code never imports pgcraft — it only consumes the database schema that the migrations created.
Using pgcraft models in your application¶
pgcraft 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 the register() decorator
to define models. The decorator registers the table on metadata at
import time — access it via
metadata.tables["<schema>.<tablename>"] to get a standard
sqlalchemy.schema.Table object.
To add PostgREST API views, see PostgREST extension.
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
For PostgREST API views, computed columns, column filtering, and aggregate view joins, see PostgREST extension.
Indices and foreign keys¶
pgcraft supports declarative index and foreign key definitions
using {column_name} markers — the same syntax used by
PGCraftCheck.
Adding indices¶
Use PGCraftIndex in schema_items.
The constructor mirrors sqlalchemy.Index: name first, then
expressions, then keyword arguments passed through to the
underlying index.
from sqlalchemy import Column, Integer, MetaData, String
from pgcraft.factory import PGCraftSimple
from pgcraft.index import PGCraftIndex
from pgcraft import pgcraft_build_naming_conventions
metadata = MetaData(
naming_convention=pgcraft_build_naming_conventions(),
)
products = PGCraftSimple(
"products", "inventory", metadata,
schema_items=[
Column("name", String, nullable=False),
Column("sku", String(32), nullable=False),
Column("price", Integer, nullable=False),
# Simple index
PGCraftIndex("idx_products_sku", "{sku}"),
# Unique index
PGCraftIndex(
"uq_products_name", "{name}", unique=True
),
# Functional index with dialect kwargs
PGCraftIndex(
"idx_products_lower_name",
"lower({name})",
postgresql_using="btree",
),
# Multi-column index
PGCraftIndex(
"idx_products_name_price",
"{name}", "{price}",
),
],
)
Adding foreign keys¶
Use PGCraftFK in schema_items.
Exactly one of references or raw_references must be
provided:
references—"dimension.column"strings resolved via the dimension registry. pgcraft finds the correct physical table regardless of dimension type (simple vs append-only).raw_references—"schema.table.column"strings passed through to SQLAlchemy directly, bypassing resolution.
Resolved references (dimension registry):
from sqlalchemy import Column, Integer, MetaData, String
from pgcraft.factory import PGCraftSimple
from pgcraft.fk import PGCraftFK
from pgcraft import pgcraft_build_naming_conventions
metadata = MetaData(
naming_convention=pgcraft_build_naming_conventions(),
)
customers = PGCraftSimple(
"customers", "public", metadata,
schema_items=[
Column("name", String, nullable=False),
],
)
orders = PGCraftSimple(
"orders", "public", metadata,
schema_items=[
Column("customer_id", Integer, nullable=False),
Column("total", Integer, nullable=False),
PGCraftFK(
references={
"{customer_id}": "customers.id"
},
name="fk_orders_customer",
ondelete="CASCADE",
),
],
)
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 references (bypass resolution):
PGCraftFK(
raw_references={
"{org_id}": "public.organizations.id"
},
name="fk_orders_org",
)
Use raw_references when referencing tables outside pgcraft
or when you want full control over the target.
See Constraints and indices for a walkthrough of the generated SQL.