Skip to content

Database helpers

Everything here lives in fastpluggy.core.database. FastPluggy owns one engine and session factory for the whole app; plugins should use these helpers rather than building their own engine, or they end up with a second connection pool nobody is managing.

Getting a session

Helper Use it for
get_db() FastAPI dependency — db: Session = Depends(get_db). The normal choice in routes.
session_scope() Context manager for background/task code: commits on success, rolls back on error, always closes.
ensure_engine() The engine itself. Needed for DDL and for anything that must run outside a session's transaction.

Creating tables

create_table_if_not_exist(model) issues CREATE TABLE … IF NOT EXISTS and adds any declared indexes the table is missing.

Declared indexes missing from an already-existing table are added through ensure_index (below), not a blocking CREATE INDEX — so a plugin loading against a live table can't hang startup waiting on a lock.

Tables are created, not migrated

It does not ALTER an existing table, so adding a column to a model does not change the live table. FastPluggy ships no migration framework — see Plugins → Models.

Creating indexes — use ensure_index, never a bare CREATE INDEX

from fastpluggy.core.database import ensure_index

ensure_index("idx_users_email", "users", "(email)")
ensure_index("idx_docs_vec", "docs", "USING hnsw (vector vector_cosine_ops)")
ensure_index("idx_users_email_u", "users", "(email)", unique=True)

definition is everything after ON <table>, so the same helper covers B-tree, GIN, HNSW and anything else. It returns True/False and never raises.

A no-op CREATE INDEX IF NOT EXISTS can still hang your app forever

It looks free when the index already exists — it isn't. It still takes a ShareLock on the table, and anything holding a conflicting lock blocks it indefinitely. An autovacuum is the usual culprit.

This is not theoretical. On 2026-07-29 a plugin ran a no-op CREATE INDEX IF NOT EXISTS in on_load_complete; it queued behind a 9-hour pgvector autovacuum, the app never finished starting (so it served 502 while the container looked healthy), 31 backends stacked up behind it, and the autovacuum ignored both pg_cancel_backend and pg_terminate_backend. The only way out was restarting a PostgreSQL instance shared by 24 databases.

ensure_index applies three defences on PostgreSQL, in order:

  1. Existence check first — if the index is already there and valid it emits no DDL at all, so it cannot queue behind anything. This is the part that prevents the outage.
  2. CONCURRENTLY for real builds, on its own AUTOCOMMIT connection (it cannot run inside a transaction). Takes only ShareUpdateExclusiveLock, so readers and writers keep working.
  3. lock_timeout (default 5s) so a genuinely contended build fails fast and loudly instead of hanging the process.

It also detects and drops the INVALID index a failed CONCURRENTLY build leaves behind, which would otherwise block every future rebuild under that name.

On SQLite (and any non-PostgreSQL dialect) it falls back to a plain CREATE INDEX IF NOT EXISTS — there is no lock-convoy problem there, and CONCURRENTLY is a syntax error.

Rules of thumb

  • Never create indexes in on_load_complete. Startup is exactly when you can least afford an unbounded lock wait, and it repeats per replica and per restart. Put index creation in a migration step, or behind ensure_index, which makes the common path free.
  • Pass bind= when you already hold a Session or Engine; otherwise it uses the app engine.
  • Don't verify an index by inspecting your ORM metadata — ask the database (pg_index.indisvalid), which is what ensure_index does.