Cookbook

Declarative views and functions

Tables, views, materialized views, and functions can share one base:

from sqlalchemy import select
from codegen_database import FunctionOptions, ViewOptions

class ActiveUser(Base):
    __tablename__ = "active_users"
    __table_args__ = {"schema": "public"}
    __query__ = select(User.id).where(User.active)

class ActiveUserSummary(Base):
    __tablename__ = "active_user_summary"
    __table_args__ = {"schema": "reporting"}
    __query__ = select(...)
    __options__ = ViewOptions(materialized=True)

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

Declarative view classes are ORM-selectable:

query = select(ActiveUser)

Imperative views

from codegen_database import (
    CodegenDatabaseMaterializedView,
    CodegenDatabasePlainView,
)

active_users = CodegenDatabasePlainView(
    "active_users",
    "public",
    Base.metadata,
    select(User.id).where(User.active),
)
summary = CodegenDatabaseMaterializedView(
    "active_user_summary",
    "reporting",
    Base.metadata,
    select(...),
)

query = select(active_users.table)

Imperative functions

from codegen_database import CodegenDatabaseFunction

CodegenDatabaseFunction(
    "answer",
    "public",
    "SELECT 42",
    metadata=Base.metadata,
    returns="integer",
)

Row-level security

from codegen_database.ext.rls import RLSPolicy, register_rls_policy

register_rls_policy(
    Base.metadata,
    RLSPolicy(
        schema="private",
        table="documents",
        name="documents_for_current_user",
        using="owner_id = current_setting('app.user_id')::integer",
    ),
)