Core Concepts

Views

Turn JSON blobs into queryable SQL columns with auto-generated views and indexes.

Views are CentauroDB's bridge between flexible JSON storage and the SQL world. One call extracts each model field from the JSON blob into a queryable SQL column:

┌──────┬──────────┬──────────────────────────────────────────────────────┐
│  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     │
└──────┴──────────┴──────┴──────────┴───────────┘

Views are standard SQL — query them with coll.sql_select() (returns a Polars DataFrame), or from any external tool: DBeaver, Metabase, Jupyter, or raw SELECT * FROM.

Your first view

Let's say you have sensors stored in a collection and want to query them with SQL:

from centaurodb import Engine, Collection, CentauroModel

engine = Engine("monitoring.db")
coll = Collection(engine, "monitoring")

class Sensor(CentauroModel):
    __centauro_name__ = "Sensor"
    unit: str = ""
    location: str = ""
    threshold: float = 100.0

# Write some data
coll.write_objects([
    Sensor(unit="Celsius", location="pelion", threshold=80),
    Sensor(unit="Fahrenheit", location="olympus", threshold=200),
])

# Create a view
coll.create_view("dashboard", Sensor)
coll.create_index("unit")  # expression index for fast filtering

This generates:

CREATE VIEW monitoring_view_dashboard AS
SELECT id, name, write_time, edit_time,
       json_extract(meta, '$.unit') AS unit,
       json_extract(meta, '$.location') AS location,
       json_extract(meta, '$.threshold') AS threshold
FROM monitoring_objects
WHERE name IN ('Sensor')

Now query with plain SQL — sql_select returns a Polars DataFrame:

df = coll.sql_select("""
    SELECT location, threshold
    FROM monitoring_view_dashboard
    WHERE unit = 'Celsius' AND threshold > 50
""")
print(df)
# ┌──────────┬───────────┐
# │ location ┆ threshold │
# │ ---      ┆ ---       │
# │ str      ┆ f64       │
# ╞══════════╪═══════════╡
# │ pelion   ┆ 80.0      │
# └──────────┴───────────┘

The name you pass to create_view is combined with the collection name to form the SQL view: {collection}_view_{name}. The name should describe what the view is for — a use case, a consumer, or a subset — not the model type (the model is already a parameter).

# Good — describes the purpose
coll.create_view("dashboard", Sensor)        # research_view_dashboard
coll.create_view("lite", Sensor)             # research_view_lite
coll.create_view("export", Sensor, Alert)    # research_view_export

# Bad — repeats the model name
coll.create_view("sensor", Sensor)           # research_view_sensor — redundant
coll.create_view("all_data", Sensor)         # vague

Same naming rules as collections: [a-z][a-z0-9_]* — lowercase, starts with a letter.

Multiple views on the same collection are fine. Use them to serve different consumers (a dashboard view with a few columns, an export view with all columns, a monitoring view spanning multiple model types).

Indexing fields for performance

Views are virtual — they cannot be indexed directly. To speed up queries against a view, create indexes on the underlying objects table via create_index():

coll.create_index("unit")
coll.create_index("location")

This creates indexes like:

CREATE INDEX idx_monitoring_unit ON monitoring_objects (json_extract(meta, '$.unit'))
CREATE INDEX idx_monitoring_location ON monitoring_objects (json_extract(meta, '$.location'))

Indexes and views are independent. Indexes live on the objects table, not on the view. Dropping or refreshing a view does not affect indexes, and dropping an index does not affect views. An index speeds up both view queries and read_objects calls on the same field.

See the Performance Guide for index tuning strategies.

Restricting columns with `include_fields`

By default the view exposes every field on the model. On wide models, restrict the view to only the fields you need — this means fewer json_extract() calls per row:

coll.create_view(
    "lite",
    Sensor,
    include_fields=["unit", "location"],  # threshold is omitted
)

Pair with create_index() for maximum impact:

coll.create_view(
    "lite",
    Sensor,
    include_fields=["unit", "location"],
)
coll.create_index("unit")

Renamed fields in the subset still generate the correct COALESCE expression, so old JSON keys remain readable:

from centaurodb import renamed_from

class Sensor(CentauroModel):
    __centauro_name__ = "Sensor"
    temperature: float = renamed_from("temp", default=0.0)
    location: str = ""

# View includes only 'temperature'; old rows with {"temp":…} are still readable
coll.create_view("lite", Sensor, include_fields=["temperature"])

Multi-model views

Store different model types in the same collection and create a single view spanning all of them:

class Alert(CentauroModel):
    __centauro_name__ = "Alert"
    level: int = 0
    message: str = ""

# Same collection — Sensor and Alert share the monitoring_objects table
coll.write_object(Sensor(unit="C", location="pelion"))
coll.write_object(Alert(level=3, message="Hydra spotted near Lerna"))

coll.create_view("all", Sensor, Alert)

In the resulting view:

Sensor rows have unit and location populated, NULL for level and message
Alert rows have level and message populated, NULL for unit and location

This is useful for dashboards that display a unified log of different event types.

Refreshing a view

After changing your model (adding, removing, or renaming fields), refresh the view to update its columns:

coll.refresh_view("dashboard", Sensor)

When you omit include_fields, refresh_view introspects the existing view and reuses its current column configuration. It also translates renamed fields automatically — if a column was created under the old name and you've since used renamed_from(), the refreshed view maps to the new name.

To explicitly change the column set, pass a new list:

coll.refresh_view(
    "dashboard",
    Sensor,
    include_fields=["unit", "location", "temperature"],
)

You only need to refresh views when the model definition changes (fields added/removed/renamed). Normal data operations (writes, updates, deletes) are reflected in views automatically — they read live data from the underlying table.

Inspecting views

List all views in a collection and their columns:

coll.describe_views()
# {'monitoring_view_dashboard': ['unit', 'location', 'threshold']}

Overwriting a view

By default, create_view() raises ViewAlreadyExistsError if the view name is already taken. Pass overwrite=True to replace it:

coll.create_view("dashboard", Sensor, overwrite=True)

Standalone indexes

You can create and manage indexes independently of views. This is useful when you want to speed up read_objects queries without creating a SQL view.

`create_index`

Idempotent — safe to call on every application startup. CentauroDB automatically picks the correct index type:

Plain column index for built-in fields: id, name, write_time, edit_time
JSON expression index for all other names (fields stored in the meta JSON blob)

coll.create_index("unit")        # json_extract expression index
coll.create_index("write_time")  # plain column index

`drop_index`

Drops an index by field name. Silent if the index doesn't exist. The auto-managed name index cannot be dropped.

coll.drop_index("unit")          # drops idx_monitoring_unit
coll.drop_index("old_field")     # no-op if already gone

`list_indexes`

Returns all user-managed indexes on the collection. The internal name index is always excluded.

coll.list_indexes()
# ['unit', 'write_time']

coll.list_indexes(raw=True)
# ['idx_monitoring_unit', 'idx_monitoring_write_time']

All indexes share the same naming convention (idx_{collection}_{field}) and appear in list_indexes().

Edit this pageorReport an issue

Collections

Store, read, update, and query your models with Collection and TimeSeriesCollection.

Schema Evolution

Add, remove, and rename fields without migrations — just update your Python class and redeploy.