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.

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 create --name add_profile
# Edit and review the created prisma/migrations/<timestamp>_add_profile/migration.sql.
uv run caspian-db migrate status
uv run caspian-db migrate deploy

create writes a template only. It does not derive a schema diff, connect to the DB, or apply SQL. status uses a read-only transaction and reports pending SQL. 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/reset command. 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.

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. No built-in destructive reset is supplied.

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.