Caspian DB

Native Python tooling for Caspian's Prisma schema, generated client, reviewed SQL migrations, and app-owned Python seed. Distribution: caspian-db; import: caspian_db; command: caspian-db. Python 3.14 or newer. No Node/npm subprocess.

Install

From PyPI, with the driver extra for your provider (postgresql, mysql, or sqlite):

uv add "caspian-db[postgresql]"
# or: pip install "caspian-db[postgresql]"

Then, from your Caspian application:

uv run caspian-db validate
uv run caspian-db generate
uv run caspian-db generate --check

By default the schema is read from prisma/ and the client is generated into src/lib/prisma. Use --schema and --output to choose other locations:

uv run caspian-db generate --schema path/to/schema.prisma --output path/to/client

uv run python -m caspian_db provides the same interface. Commands use the current project directory; pass explicit paths when running elsewhere. uv build produces an installable wheel and source distribution.

Schema and client

--schema accepts a .prisma file or a directory containing multiple .prisma files. Default: prisma. Generation reads source directly; no generated metadata manifest is needed. --metadata remains an explicit legacy compatibility input.

Supported client providers: PostgreSQL, MySQL, and SQLite. The strict schema reader supports models/enums, mappings, optional/list fields, defaults, updatedAt, named relations and inverse relations, compound primary/unique keys, and native-type annotations. It rejects unsupported declarations and attributes instead of silently dropping models. Supported scalar types: String, Int, Boolean, DateTime, Float, BigInt, Decimal, Json. Multi-schema, views, composite types, ignore/fulltext, expression/sorted compound keys, and Bytes/Unsupported types are not implemented. Mapped names currently accept letters/digits/underscores/spaces/periods/hyphens. This is a Caspian schema reader, not the complete Prisma language validator.

The schema holds models, enums, and a datasource provider. Prisma (TypeScript) configuration that the Python client does not read still parses, so a copied schema loads, but validate and generate print a warning naming file:line and the fix for each occurrence: a datasource url, directUrl, or shadowDatabaseUrl (the connection comes from DATABASE_URL in .env) and any generator block (the client comes from caspian-db generate). Remove what a warning names; warnings do not change the exit status.

Generated files remain models.py, db.py, and __init__.py. App imports such as from src.lib.prisma import prisma and existing delegate/query APIs are preserved. validate checks schema and generated syntax; generate --check checks the client against source. Neither compares a live database's structure to the schema. Native type, index, and foreign-key action annotations describe database structure; the client generator does not apply them. Express those changes in migration SQL.

upsert keeps Prisma's meaning: create applies to a new row and update to an existing one. On the native ON CONFLICT / ON DUPLICATE KEY path, a column whose update value is exactly its create value (same type and value) is set from the proposed row (EXCLUDED.col, excluded.col, VALUES(col)) instead of binding the same parameter twice, so a large value is sent once. The stored row is the same; a BEFORE INSERT trigger that rewrites such a column would be visible through it.

All outputs compile before publication. A process lock serializes writers; staged per-file replacements roll back on write errors. Authored files and file symlinks are refused. Unchanged output keeps timestamps. Publication is not a crash-atomic directory swap: stop Python before generation and regenerate after an interrupted publication. No watchers, automatic reloads, seeds, or migrations run implicitly.

Reviewed SQL migrations

uv run caspian-db migrate diff                      # print the SQL for schema changes; writes nothing
uv run caspian-db migrate create --name add_profile # write it as a migration
uv run caspian-db migrate status                    # pending migrations, unmigrated schema changes
uv run caspian-db migrate deploy

Every migration folder keeps schema.json, the structure the schema described when the migration was created. create diffs the current schema against the latest snapshot and writes migration.sql with Prisma's SQL conventions (T_pkey, T_col_key, T_col_idx, T_col_fkey, RESTRICT/SET NULL + CASCADE), plus the new snapshot. It reads no database. Review the SQL before deploying:

• Steps that lose data or can fail on existing rows (dropped tables and columns, a required column without a default, a type change, a column becoming required) are preceded by -- WARNING: lines and printed by the command.
• PostgreSQL alters columns, keys, indexes, and enum values in place. Removing an enum value is a -- TODO: block.
• SQLite adds tables, columns (including a new optional foreign key), and indexes in place. A change SQLite cannot make with ALTER TABLE (a column's type, nullability, or default, a primary key, a foreign key on an existing column) is a -- TODO: block with the new table definition to rebuild by hand.

A history created before snapshots existed has none; create then stops. When the schema matches the deployed database, uv run caspian-db migrate snapshot records it beside the latest migration, and later create runs diff from there. create --empty writes a migration to fill in by hand (with its snapshot).

status uses a read-only transaction, reports pending SQL, and says whether the schema has changes no migration captures yet. deploy is an explicit database write operation. Review its target and SQL first. Commands accept --schema and --migrations. DATABASE_URL loads from the current project's .env unless already set in the environment; credentials are not printed.

Deployment supports PostgreSQL and SQLite. Provider mismatch, changed/missing applied migrations, non-prefix history, and unresolved failures stop deployment. Existing _prisma_migrations records and original SQL file checksums are reused; there is no automatic baseline or replay. Local migration_lock.toml is preserved. PostgreSQL uses a transaction advisory lock; SQLite uses BEGIN IMMEDIATE. Pending SQL and ledger entries commit together as one batch; a failure rolls back the batch. Transaction controls and nontransactional operations such as CONCURRENTLY are refused. SQL is trusted, reviewed project source, not uploaded/untrusted input.

Failed existing Prisma migrations need deliberate recovery; this package does not provide an automatic resolve command. On a development database, migrate reset rebuilds from zero; elsewhere recovery uses a reviewed follow-up migration or explicit DBA intervention. Recovery uses a reviewed follow-up migration or explicit DBA intervention. It does not generate down migrations, perform schema drift detection, db push/introspection, or provide Prisma Studio. MySQL client generation remains supported; MySQL migration deployment is not implemented because its DDL transaction semantics need a separate runner.

Development loop: migrate dev and migrate reset

The equivalents of npx prisma migrate dev and npx prisma migrate reset:

uv run caspian-db migrate dev --name add_profile   # schema change -> migration -> apply -> client
uv run caspian-db migrate dev                      # no schema change: apply pending, regenerate
uv run caspian-db migrate dev --name x --create-only   # write the SQL only, apply on a later run
uv run caspian-db migrate reset                    # wipe and rebuild from zero (asks first)
uv run caspian-db migrate reset --force --skip-seed

migrate dev:

1. Creates a migration when the schema changed since the latest snapshot (--name, or a prompt in an interactive terminal; non-interactive runs require --name).
2. If that migration carries -- WARNING: lines (possible data loss), it is written but not applied unless you confirm or pass --accept-data-loss. Review or edit it, then run migrate dev again.
3. Creates the database named in DATABASE_URL when it does not exist (PostgreSQL: CREATE DATABASE through the postgres maintenance database; SQLite: the file).
4. Applies every pending migration, regenerates the client (--output, default src/lib/prisma; --skip-generate to skip), and runs the seed only when the database was just created (--skip-seed, --seed-file).

It does not detect drift between the database and the migration history; use migrate status, and migrate reset when a development database has drifted.

migrate reset drops the ?schema= schema (PostgreSQL, default public) with DROP SCHEMA ... CASCADE and recreates it, or deletes the SQLite file; creates the database if missing; re-applies every migration; and runs prisma/seed.py unless --skip-seed. It prompts with the target database (no credentials) unless --force, requires --force in non-interactive runs, and refuses outright when APP_ENV is set to anything other than dev, development, local, staging, test, or testing. All data in that schema is destroyed; stop the app server first (a locked SQLite file is reported).

Python seed

uv run caspian-db seed
uv run caspian-db seed --demo-user

seed loads prisma/seed.py (override with --file) and awaits its run(demo_user=...) function. The consuming app owns the seed policy. Never execute seeds implicitly. The migrated Caspian app seed creates missing Admin/User roles in a transaction, keeps existing records unchanged, and never deletes users or resets counters. The optional demo user requires CASPIAN_SEED_DEMO_PASSWORD, hashes it, and does not replace an existing user's credentials. The only destructive command is the development-guarded migrate reset, which reruns this seed afterwards.

Dependencies and verification

Generation/schema reading uses the standard library. Migration SQL classification uses sqlparse; environment loading uses python-dotenv. Generated clients need the provider driver; extras: caspian-db[postgresql], [mysql], [sqlite]. The app already installs its drivers. For exact ID default formats, install cuid2, nanoid, or python-ulid; the preserved client otherwise falls back to UUID.

uv sync --group dev
uv run python -m pytest tests -q
uv run ruff check src tests
uv build

Tests include independent legacy-output AST parity for three client providers, native multi-file parsing, no-Node execution, preservation on failure, concurrency, and SQLite migration history/rollback plus generated-client CRUD. The consuming app has an opt-in PostgreSQL test using only session-local temporary tables to verify deployment, history, rollback, additive seeding, and client operations. MySQL execution is still unverified. Read PORTING.md and AGENTS.md before edits.