Skip to content

DB

One database interface for every consumer in the org. Build the connection once, hand it to everything, and every table operation goes through one client with one honest report back.

Install

  • common-db: the connection, the client, the schema builder, the archive leg.
  • common-db[pandas]: pandas frames in and out beside polars and arrow.

The connection

from common_db import DBConnectionConfig, PoolConfig
connection = DBConnectionConfig(host="db.internal", database="core", user="app", password=secret)
sidecar = connection.merge(database="monitoring", connect_timeout=5.0, statement_timeout_ms=5_000)
pool = PoolConfig(min_size=1, max_size=8)

DBConnectionConfig is typed by backend, PostgreSQL today. Every field defaults; merge returns a new validated config. Secrets never appear in a repr. DBConnectionConfig.from_env("POSTGRES") exists for the application to call from its settings layer; the package never reads the environment on its own.

The client

from common_db import DBClient, DBSchemaBuilder, TableRef, WriteConfig
db = DBClient(connection=connection, targets={"monitoring": sidecar}, pool=pool)
with db:
db.ensure(
DBSchemaBuilder(TableRef.parse("raw.bars"))
.col("symbol", "text", pk=True)
.col("day", "date", pk=True)
)
report = db.upsert(frame, "raw.bars")
report = db.reconcile(frame, "raw.bars", scope={"day": today})
dry = db.assess(frame, "raw.bars")
rows = db.read("raw.bars", scope={"symbol": "AAPL"})
with db.on("monitoring"):
db.append(events, "ops.stage_audit")

Every op is composed SQL with identifiers through psycopg.sql, memory-streaming COPY, one transaction per logical op, and a typed report: WriteResult, ReconcileReport, AssessmentReport, ReadResult. Failures return reports; nothing is silent. WriteConfig and ReadConfig carry the knobs: coercion posture, what happens to stale rows, batch sizes, output kind.

Async

Every op has an a-prefixed twin with the same signature, delegated off the event loop, so a job awaits a write the way it awaits a fetch. For a consumer’s own short transactions there is an async pool per target:

async with db:
await db.aupsert(frame, "raw.bars")
async with db.atx() as conn:
await conn.execute("SELECT pg_advisory_xact_lock(%s)", (key,))

aclose() closes both pools. close() closes the sync pools, and the async ones too when no event loop is running.

Tables and the census

from common_db import Base
class Bars(Base):
__tablename__ = "bars"
__table_args__ = {"schema": "raw"}
...

Base is the one declarative base every consumer’s tables register on, so a single metadata knows every table and ensure can read from it. The schema builder produces the same SQLAlchemy objects from code, from inference over a frame, or from a saved definition.

The archive leg

DatasetRef names a hive-partitioned parquet dataset under a local root or any S3-compatible store. archive writes a batch as stable snapshot objects; scan reads back lazily with the same scope language the PostgreSQL ops speak.