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

The goal is to create a repeatable, container‑based development environment for Ory Kratos that starts quickly while still giving confidence that database migrations and schema will behave identically in production.

Using the SQLite backend eliminates the need for a separate database service, giving near‑instant container start and allowing the database file to be version‑controlled for deterministic CI runs. However, SQLite does not support concurrent writes or certain column types (e.g., JSON) used by Kratos, and its journal mode changed in v0.11+, which can cause lock errors under parallel test execution. The SQL backend mirrors production schema and enables migration testing, but adds startup latency due to an extra service in Docker Compose.

Which backend offers the best trade‑off between container startup speed and schema fidelity for automated CI pipelines?

Does relying on SQLite in development risk undetected incompatibilities that only appear when deploying to PostgreSQL or MySQL?

Can the SQLite backend be safely used for parallel test execution given its journal mode changes in Ory Kratos v0.11+?