SQLAlchemy models and database objects

Use one project base for tables, views, materialized views, and functions:

from sqlalchemy import MetaData, String, select
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column

from codegen_database import (
    CodegenDatabaseBase,
    FunctionOptions,
    ViewOptions,
)

class Base(CodegenDatabaseBase, DeclarativeBase):
    metadata = MetaData()

class User(Base):
    __tablename__ = "users"
    id: Mapped[int] = mapped_column(primary_key=True)
    email: Mapped[str] = mapped_column(String)

class UserEmails(Base):
    __tablename__ = "user_emails"
    __table_args__ = {"schema": "public"}
    __query__ = select(User.id, User.email)

class UserEmailSummary(Base):
    __tablename__ = "user_email_summary"
    __table_args__ = {"schema": "reporting"}
    __query__ = select(User.id)
    __options__ = ViewOptions(materialized=True)

class Answer(Base):
    __funcname__ = "answer"
    __table_args__ = {"schema": "public"}
    __definition__ = "SELECT 42"
    __options__ = FunctionOptions(returns="integer")

Table models

Ordinary table subclasses use SQLAlchemy’s declarative mapping unchanged. Mapped inference, mapped_column, relationships, mixins, inheritance, declared_attr, __table_args__, and __mapper_args__ retain their normal behavior.

Views

A class with its own __query__ is registered as a PostgreSQL view and mapped to a detached proxy table. It can be queried with select(UserEmails) without making Alembic treat the proxy as a table.

Selected primary-key columns are preserved. Otherwise the mapper uses id or the first selected column. Set __mapper_args__ = {"primary_key": ["column_name"]} when required.

Functions

A class with its own __funcname__ registers a PostgreSQL function on the shared metadata and is not ORM-mapped. __definition__ may be SQL or a callable returning SQL.

The imperative view and function constructors remain available; see Cookbook.