Ory Kratos supports multiple SQL databases for production deployments. Choose the database that best fits your infrastructure requirements.
Supported databases
PostgreSQL Recommended for most production deployments. Supports all Kratos features.
MySQL Alternative SQL database with full feature support.
CockroachDB Distributed SQL database for high availability and global deployments.
SQLite Development and testing only. Not recommended for production.
Database connection strings (DSN) Configure the database connection using the DSN environment variable or in the configuration file.
PostgreSQL
MySQL
CockroachDB
SQLite
DSN = "postgres://user:password@localhost:5432/kratos?sslmode=require&max_conns=20&max_idle_conns=4"
DSN parameters:
sslmode - SSL mode (require, verify-ca, verify-full, disable)max_conns - Maximum number of connections (default: unlimited)max_idle_conns - Maximum idle connections (default: 2)conn_max_lifetime - Connection maximum lifetime (e.g., 1h) DSN = "mysql://user:password@tcp(localhost:3306)/kratos?max_conns=20&max_idle_conns=4&parseTime=true"
DSN parameters:
parseTime=true - Required for proper timestamp handlingmax_conns - Maximum number of connectionsmax_idle_conns - Maximum idle connectionsconn_max_lifetime - Connection maximum lifetime DSN = "cockroach://user:password@localhost:26257/kratos?sslmode=verify-full&sslrootcert=certs/ca.crt&sslcert=certs/client.crt&sslkey=certs/client.key"
DSN parameters:
sslmode - Always use verify-full for productionsslrootcert - Path to CA certificatesslcert - Path to client certificatesslkey - Path to client key DSN = "sqlite:///var/lib/sqlite/db.sqlite?_fk=true&mode=rwc"
DSN parameters:
_fk=true - Enable foreign key constraints (required)mode=rwc - Read, write, create mode SQLite is only suitable for development and testing. Use PostgreSQL, MySQL, or CockroachDB for production.
PostgreSQL setup
Install PostgreSQL
Install PostgreSQL 12 or later: sudo apt update sudo apt install postgresql postgresql-contrib
Create database and user
CREATE USER kratos WITH PASSWORD 'your-secure-password' ; CREATE DATABASE kratos ; GRANT ALL PRIVILEGES ON DATABASE kratos TO kratos;
Configure Kratos
Set the DSN in your configuration: dsn : postgres://kratos:your-secure-password@localhost:5432/kratos?sslmode=require
Run migrations
kratos migrate sql -e --yes
MySQL setup
Install MySQL
Install MySQL 8.0 or later: sudo apt update sudo apt install mysql-server
Create database and user
CREATE USER ' kratos '@ '%' IDENTIFIED BY 'your-secure-password' ; CREATE DATABASE kratos CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; GRANT ALL PRIVILEGES ON kratos. * TO 'kratos' @ '%' ; FLUSH PRIVILEGES;
Configure Kratos
dsn : mysql://kratos:your-secure-password@tcp(localhost:3306)/kratos?parseTime=true
Run migrations
kratos migrate sql -e --yes
CockroachDB setup
Install CockroachDB
curl https://binaries.cockroachdb.com/cockroach-v23.1.0.linux-amd64.tgz | tar -xz sudo cp cockroach-v23.1.0.linux-amd64/cockroach /usr/local/bin/ cockroach start-single-node --insecure --listen-addr=localhost:26257
Create database and user
CREATE USER kratos ; CREATE DATABASE kratos ; GRANT ALL ON DATABASE kratos TO kratos;
Configure Kratos
dsn : cockroach://kratos@localhost:26257/kratos?sslmode=disable
Use proper SSL certificates in production. Never use sslmode=disable in production.
Run migrations
kratos migrate sql -e --yes
Connection pool configuration Optimize database connections for your workload:
dsn : postgres://user:pass@host/db?max_conns=20&max_idle_conns=4&conn_max_lifetime=1h
Maximum number of open connections. Set based on your database server capacity and expected load. Recommended values:
Small deployment: 10-20
Medium deployment: 20-50
Large deployment: 50-100
Maximum number of idle connections. Generally 20-25% of max_conns.
Maximum lifetime of a connection. Helps prevent stale connections. Recommended: 1h to 4h.
Database migrations Kratos uses automatic schema migrations. See the migration guide for details.
Optimize PostgreSQL for Kratos: -- In postgresql.conf shared_buffers = 256MB effective_cache_size = 1GB maintenance_work_mem = 64MB checkpoint_completion_target = 0 . 9 wal_buffers = 16MB default_statistics_target = 100 random_page_cost = 1 . 1 effective_io_concurrency = 200 work_mem = 4MB min_wal_size = 1GB max_wal_size = 4GB
Optimize MySQL for Kratos: [mysqld] innodb_buffer_pool_size = 1G innodb_log_file_size = 256M innodb_flush_log_at_trx_commit = 2 innodb_flush_method = O_DIRECT max_connections = 500
Backup and recovery Always implement regular database backups for production deployments.
PostgreSQL
MySQL
CockroachDB
# Backup pg_dump -U kratos -h localhost kratos > kratos_backup.sql # Restore psql -U kratos -h localhost kratos < kratos_backup.sql
# Backup mysqldump -u kratos -p kratos > kratos_backup.sql # Restore mysql -u kratos -p kratos < kratos_backup.sql
# Backup to cloud storage cockroach sql --insecure -e "BACKUP DATABASE kratos TO 's3://bucket/backup?AWS_ACCESS_KEY_ID=x&AWS_SECRET_ACCESS_KEY=y'" # Restore cockroach sql --insecure -e "RESTORE DATABASE kratos FROM 's3://bucket/backup?AWS_ACCESS_KEY_ID=x&AWS_SECRET_ACCESS_KEY=y'"
Troubleshooting
Check that:
Database is running
Firewall allows connections
Database is listening on the correct interface
Credentials are correct
For production, always use SSL: # PostgreSQL with SSL DSN = "postgres://user:pass@host/db?sslmode=require" # MySQL with SSL DSN = "mysql://user:pass@tcp(host)/db?tls=true"
Increase max_connections in your database configuration or reduce max_conns in the Kratos DSN.
Next steps
Run migrations Learn about database migration management
Configuration Complete configuration reference