SQL vs SQLite for Ory Kratos Dev: Speed vs Production Parity

Answer the Question First

For a repeatable, container‑based development environment that starts quickly while still giving confidence that migrations and schema will behave identically in production, the **recommended approach is to use PostgreSQL in CI and production, and optionally SQLite for local, single‑user prototyping**. SQLite is fast to start and can be version‑controlled, but it lacks several features that Ory Kratos relies on (JSONB, advanced indexes, strict transaction isolation). Running migrations against PostgreSQL in CI guarantees that schema changes survive the transition to production.

Why PostgreSQL is the safer choice for CI

• Ory Kratos officially supports PostgreSQL and MySQL; SQLite is not a supported production database.
• All core features—JSONB columns, foreign‑key enforcement, and concurrent write handling—are available in PostgreSQL.
• Running kratos migrate against PostgreSQL produces a fully‑fledged schema; SQLite will fail or create an incomplete schema.
• CI that mirrors production eliminates “works on my machine” surprises when you push to a PostgreSQL backend.

When SQLite can be used safely

• Local, single‑user development where you need a quick startup and are happy to run your own PostgreSQL locally for full parity.
• Testing non‑concurrent flows that do not exercise JSONB or complex transactions.
• If you add a --migrate-only step in CI that runs against PostgreSQL, you can keep SQLite for interactive debugging.

Risk of Undetected Incompatibilities

Using SQLite in development can mask subtle differences:

• SQLite’s JSON type is a text field; it does not enforce JSON structure or provide JSON operators.
• Concurrent writes are serialized; tests that succeed against SQLite may fail against PostgreSQL when many goroutines write simultaneously.
• Transaction isolation levels differ; SQLite uses READ UNCOMMITTED by default, whereas PostgreSQL offers READ COMMITTED and higher.

To avoid these issues, always run kratos migrate and a full integration test suite against PostgreSQL in CI.

Parallel Test Execution with SQLite

Ory Kratos v0.11+ changed SQLite’s journal mode to WAL for better concurrency, but WAL still has limits:

• Only one writer can hold the WAL lock at a time.
• Multiple readers are fine, but writer contention can cause lock errors during parallel test runs.
• If you must run tests in parallel against SQLite, set PRAGMA journal_mode=WAL; at startup and keep the test matrix to a single writer per process.

In practice, most CI environments run tests sequentially or with a limited number of workers; SQLite will usually be fine. For high‑concurrency test suites, switch to PostgreSQL.

Practical Steps for a CI‑Ready Setup

1. Docker Compose for CI – define a postgres service and a kratos service that connects to it. Example snippet:

   services:
     postgres:
       image: postgres:16
       environment:
         POSTGRES_USER: kratos
         POSTGRES_PASSWORD: kratos
         POSTGRES_DB: kratos
     kratos:
       image: oryd/kratos:latest
       depends_on:
         - postgres
       environment:
         DSN: postgres://kratos:kratos@postgres:5432/kratos?sslmode=disable
   

2. Run migrations in CI – add a job step:

   docker compose up -d postgres
   docker compose run kratos migrate up
   

3. Run tests against PostgreSQL – point your test framework to the same DSN used by the kratos service.
4. Optional local SQLite dev – create a kratos.yaml with:

   dsn: sqlite://./kratos.db?mode=rwc
   

   Run kratos migrate up once to bootstrap the schema. Use this only for quick iterations.

Ask for Missing Diagnostic Detail

To fine‑tune the recommendation, could you tell me how many concurrent test workers you plan to run in CI? If you’re targeting heavy parallelism, PostgreSQL is the safer default.