Introduction

CentauroDB is a lightweight database wrapper for Python. It stores Pydantic models as JSON blobs in SQLite or PostgreSQL and makes them queryable through auto-generated SQL views.

The core idea: your data is stored as flexible JSON (like a document database), but CentauroDB generates SQL views that extract each field into a queryable column using json_extract(). You get the flexibility of NoSQL with the query power of SQL.

A deliberate design line: every CentauroDB query targets a single collection by design — read_objects, count_objects, aggregate, and children_of each operate on one {name}_objects table per call. Cross-collection JOINs are out of scope intentionally, not deferred: CentauroDB is a document-store-shaped library on top of a relational engine, and we don't compete with relational ORMs on that axis. Users with cross-table-JOIN workloads should reach for SQLAlchemy or SQLModel.

┌──────┬──────────┬──────────────────────────────────────────────────────┐
│  id  │   name   │                    meta (JSON)                       │
├──────┼──────────┼──────────────────────────────────────────────────────┤
│  1   │  Sensor  │ {"unit":"C","location":"pelion","threshold":80}      │
│  2   │  Sensor  │ {"unit":"F","location":"olympus","threshold":200}    │
└──────┴──────────┴──────────────────────────────────────────────────────┘

        ↓  create_view("dashboard", Sensor)

┌──────┬──────────┬──────┬──────────┬───────────┐
│  id  │   name   │ unit │ location │ threshold │  ← plain SQL columns
├──────┼──────────┼──────┼──────────┼───────────┤
│  1   │  Sensor  │  C   │  pelion  │    80     │
│  2   │  Sensor  │  F   │  olympus │   200     │
└──────┴──────────┴──────┴──────────┴───────────┘

Why zero migrations?

With a traditional ORM, every schema change requires a migration: generate it, review it, run it in dev, run it in staging, run it in production. Miss a step and your deploy breaks.

CentauroDB sidesteps this entirely. Because data is stored as JSON, old rows don't need to match the current schema — Pydantic fills in defaults for missing fields and silently ignores removed ones. The result:

• Add a field — give it a default value. Old rows get that default on read. No migration.
• Remove a field — delete it from your class. Old JSON still has the key, Pydantic ignores it. No migration.
• Rename a field — use renamed_from(). Old JSON reads under the new name. No migration.

You change your Python class and redeploy. That's it.

Why SQL views?

JSON flexibility is powerful, but querying raw JSON is clunky. CentauroDB bridges this gap by generating standard SQL views that extract each field into a real column. Once a view exists, you can:

• Run SELECT * FROM monitoring_view_dashboard WHERE unit = 'Celsius' from any SQL tool
• Connect BI dashboards like Metabase or Grafana directly
• Join views with other tables in raw SQL
• Use GROUP BY, ORDER BY, aggregates — the full SQL language

Views are virtual — they read live data from the underlying table. No data duplication, no sync lag.

What else is built in?

Requirements

• Python 3.11+
• Runtime dependency: Pydantic v2+
• Optional: Polars (for .df property), psycopg (for PostgreSQL)