Skip to content

common_db.builder.base

base.py — DBSchemaBuilder: the model constructor.

A BUILDER first (Sam’s ruling): you build the schema UP, fluently, with no source required — sources (frames, pydantic classes, dict specs) are optional construction shortcuts. Output is a real SQLAlchemy Table (+ KeySpec), savable as .py source, with idempotent bootstrap DDL emission (the ensure() every legacy monitoring module hand-rolled).

table = (
DBSchemaBuilder("raw.bars")
.col("symbol", "text", pk=True)
.col("date", "date", pk=True)
.col("close", "numeric", nullable=False)
.unique("symbol", "date", where="close IS NOT NULL")
.index("date")
.build()
)

The builder never touches a connection — it EMITS (Tables, DDL statements, reports); the client domain EXECUTES. That split keeps this domain pure and its tests DB-free.

Classes

DBSchemaBuilder

Fluent schema construction — build up, or start from a source, then refine; build() hands back the real SQLAlchemy Table.

__init__

def __init__(table: TableRef | str, *, default_schema: str = 'public') -> None

build

def build(metadata: sa.MetaData | None = None) -> sa.Table

The real Table. Received tables return AS-IS (zero transformation).

col

def col(name: str, type_: str | Any, *, pk: bool = False, nullable: bool = True, default: Any = None, comment: str | None = None) -> Self

One column — type by shorthand (‘numeric’) or a real sa type. Scalar defaults (0, True, dates) coerce to server-side literals.

comment

def comment(text: str) -> Self

ensure_ddl

def ensure_ddl() -> list[str]

The idempotent bootstrap set — schema, table, missing columns, catalog-guarded primary key, and indexes (what every legacy monitoring module hand-rolled, emitted once here; the client executes). Safe to run every process start; incompatible existing identity/data refuses.

index

def index(*cols: str, unique: bool = False, where: str | None = None, name: str | None = None) -> Self

index_ddl

def index_ddl() -> list[str]

JUST the indexes — the post-bulk-load index pass.

infer

@classmethod
def infer(data: Any, table: TableRef | str, *, keys: KeySpec | None = None, default_schema: str = 'public') -> DBSchemaBuilder

Start from a source’s schema — polars/pandas frame, arrow table/schema, a pydantic model class, or a {name: shorthand} dict — then refine fluently. Explicit keys become PK/uniques/indexes.

key_spec

def key_spec() -> KeySpec

The identity story — from the received table or the declarations.

primary

def primary(*cols: str) -> Self

receive

@classmethod
def receive(table: sa.Table) -> DBSchemaBuilder

An already-built Table (the census hands these over) — used AS-IS: build() returns the exact object; keys are read straight off it.

to_python

def to_python() -> str

The schema as real .py source — dynamic tables can graduate into a census file. Everything the table carries survives graduation: dialect types route through pg.*, uniques/defaults/comments all emit.

unique

def unique(*cols: str, where: str | None = None, name: str | None = None) -> Self

validate

def validate(data: Any) -> AssessmentReport

Schema-side compatibility of DATA against THIS schema — column coverage, dtype fits, key nullability. No connection, no dry-run counts (the client’s assess() adds those on top).