Cookbook
========

Practical recipes for common codegen_database use cases.

.. _cookbook-migrations-only:

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**

.. code-block:: python

   # 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 :doc:`setup` 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**

.. code-block:: bash

   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:

.. code-block:: bash

   # Upgrade from nothing to head (full schema)
   alembic upgrade head --sql > upgrade.sql

Generate SQL for a specific revision range:

.. code-block:: bash

   # 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**

.. code-block:: bash

   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.


.. _cookbook-models-in-app:

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 :class:`~codegen_database.declarative.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
:class:`sqlalchemy.schema.Table` object.

Shared models module
~~~~~~~~~~~~~~~~~~~~

Put your codegen_database definitions in a module that both Alembic and your
application import:

.. code-block:: python

   # myapp/models.py
   from sqlalchemy import Column, Integer, MetaData, String

   from codegen_database import CodegenDatabaseBase, construct_naming_conventions_dict

   metadata = MetaData(
       naming_convention=construct_naming_conventions_dict(),
   )


   class Base(CodegenDatabaseBase):
       metadata = metadata


   class Users(Base):
       __tablename__ = "users"
       __table_args__ = {"schema": "public"}

       name = Column(String, nullable=False)
       email = Column(String, nullable=False)
       age = Column(Integer)


   # Grab the generated table for use in queries
   users = metadata.tables["public.users"]

FastAPI example
~~~~~~~~~~~~~~~

.. code-block:: python

   # 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
~~~~~~~~~~~~~

.. code-block:: python

   # 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


.. _cookbook-indices-and-fks:

Indices and foreign keys
------------------------

codegen_database supports declarative index and foreign key definitions
using ``{column_name}`` markers — the same syntax used by
:class:`~codegen_database.check.CodegenDatabaseCheck`.

Adding indices
~~~~~~~~~~~~~~

Use :class:`~codegen_database.index.CodegenDatabaseIndex` inside ``__table_args__``.
The constructor mirrors ``sqlalchemy.Index``: name first, then
expressions, then keyword arguments passed through to the
underlying index.

.. code-block:: python

   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 :class:`~codegen_database.fk.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):

.. code-block:: python

   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):

.. code-block:: python

   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 :doc:`constraints_and_indices` for a walkthrough of the
generated SQL.