# "Database System" _Path: en/system/database_ > "SQL database connection pooling and configuration. Supports PostgreSQL, MySQL, and SQLite." ## Table of Contents - Database System ## Content # Database System Wippy provides SQL database entries for PostgreSQL, MySQL, and SQLite with configurable connection pools. This page is a configuration reference. Unless a fence includes `version`, `namespace`, and `entries`, treat it as a fragment to place inside an existing entry list. ## Entry Kinds | Kind | Description | |------|-------------| | `db.sql.postgres` | PostgreSQL database | | `db.sql.mysql` | MySQL database | | `db.sql.sqlite` | SQLite database | ### Standard Databases (PostgreSQL, MySQL) ```yaml # src/data/_index.yaml version: "1.0" namespace: app.data entries: - name: main_db kind: db.sql.postgres host: "localhost" port: 5432 database: "myapp" username: "dbuser" password: ${env:app.secrets:db_password} pool: max_open: 25 max_idle: 5 max_lifetime: "1h" options: sslmode: "disable" lifecycle: auto_start: true ``` ### SQLite ```yaml - name: cache_db kind: db.sql.sqlite file: "/var/data/cache.db" # Use :memory: for in-memory pool: max_open: 1 max_idle: 1 max_lifetime: "1h" lifecycle: auto_start: true ``` SQLite application operations are serialized through one physical connection. For file-backed and in-memory databases, max_open and max_idle are forced to 1; max_lifetime still applies. CDC snapshots use a separate read-only pool. File-backed databases use WAL journal mode. ### Standard Database Fields | Field | Type | Description | |-------|------|-------------| | `host` | string | Database host address | | `port` | int | Database port number | | `database` | string | Database name | | `username` | string | Database user | | `password` | string | Database password | | `pool` | object | Connection pool settings | | `options` | map | Database-specific options | | `lifecycle` | object | Lifecycle configuration | ### SQLite Fields | Field | Type | Default | Description | |-------|------|---------|-------------| | `file` | string | required | Database file path or `:memory:` | | `foreign_keys` | bool | false | Enforce SQLite foreign keys on every connection | | `pool` | object | - | Application pool settings; `max_open` and `max_idle` are forced to `1` | | `max_mutation_changes` | int | 100000 | Rows one transaction may hold in the committed-mutation observer | | `max_mutation_bytes` | int | 67108864 | Logical bytes one transaction may hold in the observer (64 MiB) | | `checkpoint` | object | See below | Background WAL checkpoint settings for managed writable files | | `options` | map | - | Accepted but ignored | | `lifecycle` | object | - | Lifecycle configuration | `max_mutation_changes` and `max_mutation_bytes` bound the in-memory committed-mutation observer that feeds a [`db.cdc.sqlite`](system/cdc.md) source. Zero on either field selects the default; negative values are rejected. The bounds are conservative rather than exact: SQLite delivers a complete row to the pre-update hook, so one row can materialize before the bound rejects the candidate. ### SQLite WAL Checkpoints From runtime v0.3.62a, managed writable SQLite files checkpoint WAL in a background worker. Their committing connections use `wal_autocheckpoint=0`, keeping checkpoint work off transaction commits. | Setting | Default | Behavior | |---------|---------|----------| | `checkpoint.interval` | `1s` | Interval between background PASSIVE checkpoints | | `checkpoint.max_wal_pages` | `16384` | WAL-page threshold for attempting a TRUNCATE checkpoint | ```yaml - name: cache_db kind: db.sql.sqlite file: data/cache.db checkpoint: interval: 500ms max_wal_pages: 8192 ``` PASSIVE backfill runs on a dedicated maintenance connection. From runtime v0.3.65a, a WAL above the configured threshold gets 50 ms writer turns to let backfill catch up under sustained writes. The worker returns the application connection between turns while the storage operation continues. After backfill finishes, it reserves the writer, checks the current frame counts, and runs TRUNCATE only when no frames remain to be copied. Reader lock waits use a separate 50 ms maintenance busy timeout. Active snapshots or new commits can defer truncation beyond the configured page threshold, which is a soft limit. Writer turns schedule access to the connection; foreground commit latency still depends on storage. Pool shutdown stops the worker and attempts a final TRUNCATE. From runtime v0.3.65a, writable attached WAL databases are discovered through the application connection and backfilled through temporary attachments on the maintenance connection. The application connection is returned before backfill starts. Read-only attachments are skipped, and files removed after detaching are left removed. In-memory and read-only main databases have no background worker and retain the ordinary 1000-page automatic checkpoint policy. Application transactions retain their existing synchronization settings and deadlines. ### Secret and Environment Values Pull connection values from the [environment registry](system/env.md) with `${env:NAME}` placeholders, resolved at decode time. `NAME` is a registered variable's public name or its entry ID (e.g. `app.secrets:db_password`); it is not a raw OS env var. ```yaml - name: prod_db kind: db.sql.postgres host: ${env:DB_HOST} port: ${env:DB_PORT|5432} database: ${env:DB_NAME} username: ${env:DB_USER} password: ${env:app.secrets:db_password} ``` Older configurations use a sibling <field>_env directive (host_env, port_env, database_env, username_env, password_env) that resolves the same way. This form is deprecated — migrate it to the ${env:NAME} placeholder shown above. Avoid hardcoding passwords in configuration. Use env.variable entries for credentials. See Environment for secret configuration. ## Connection Pool Configure connection pooling behavior. Pool settings map to Go's [database/sql connection pool](https://pkg.go.dev/database/sql#DB.SetMaxOpenConns). | Field | Type | Default | Description | |-------|------|---------|-------------| | `max_open` | int | 0 | Maximum open connections (0 = unlimited) | | `max_idle` | int | 0 | Maximum idle connections (0 = no idle connections retained) | | `max_lifetime` | duration | 1h | Maximum connection lifetime | ```yaml pool: max_open: 25 # Limit concurrent connections max_idle: 5 # Keep 5 connections ready max_lifetime: "30m" # Recycle connections every 30 minutes ``` Set max_idle less than or equal to max_open. Connections exceeding max_lifetime are closed and replaced, helping recover from stale connections. ## DSN Formats Each database type constructs a DSN from configuration. Any `options` are appended (sorted by key); none are included by default. ### PostgreSQL {id="dsn-postgresql"} ``` host='host' port=port user='username' password='password' dbname='database' [option='value' ...] ``` Every value except the port is single-quoted, and embedded `'` and `\` are backslash-escaped, so hosts, passwords and option values containing spaces or quotes are passed through intact. ### MySQL {id="dsn-mysql"} ``` username:password@tcp(host:port)/database[?option=value&...] ``` ### SQLite {id="dsn-sqlite"} ``` file:/path/to/database.db?mode=rwc :memory: ``` ## Database Options Common database-specific options: ### PostgreSQL {id="options-postgresql"} ```yaml options: sslmode: "require" # disable, require, verify-ca, verify-full connect_timeout: "10" # Connection timeout in seconds application_name: "myapp" ``` ### MySQL {id="options-mysql"} ```yaml options: charset: "utf8mb4" parseTime: "true" # Parse time values to time.Time loc: "Local" # Timezone ``` ### SQLite {id="options-sqlite"} SQLite does not apply the `options` map to its DSN. File databases always open with `mode=rwc`, and journal mode is always set to `WAL`. The `options` field is accepted but ignored. Set the top-level `foreign_keys: true` field to enable foreign-key enforcement for every pooled connection, including in-memory databases. It is disabled by default; setting it in `options` has no effect. ### PostgreSQL with SSL ```yaml - name: secure_postgres kind: db.sql.postgres host: "db.example.com" port: 5432 database: "production" username: "app_user" password: ${env:app.secrets:db_password} pool: max_open: 50 max_idle: 10 max_lifetime: "1h" options: sslmode: "verify-full" sslcert: "/certs/client.crt" sslkey: "/certs/client.key" sslrootcert: "/certs/ca.crt" lifecycle: auto_start: true ``` ### MySQL Read Replica ```yaml - name: mysql_replica kind: db.sql.mysql host: "replica.db.example.com" port: 3306 database: "app" username: "readonly" password: ${env:app.secrets:replica_password} pool: max_open: 20 max_idle: 5 max_lifetime: "30m" options: charset: "utf8mb4" parseTime: "true" readTimeout: "30s" ``` ### SQLite In-Memory ```yaml - name: test_db kind: db.sql.sqlite file: ":memory:" ``` ### Multiple Database Setup ```yaml entries: # Primary database - name: users_db kind: db.sql.postgres host: ${env:USERS_DB_HOST} port: 5432 database: "users" username: ${env:USERS_DB_USER} password: ${env:app.secrets:users_db_password} lifecycle: auto_start: true # Analytics database - name: analytics_db kind: db.sql.mysql host: ${env:ANALYTICS_DB_HOST} port: 3306 database: "analytics" username: ${env:ANALYTICS_DB_USER} password: ${env:app.secrets:analytics_db_password} lifecycle: auto_start: true # Local cache - name: cache kind: db.sql.sqlite file: "/var/cache/app.db" lifecycle: auto_start: true ``` ## Runtime Registration Databases can be registered at runtime using the [registry module](lua/core/registry.md). ## Lua API See [SQL Module](lua/storage/sql.md) for query, transaction, and connection operations. ## See Also - [SQL Module](lua/storage/sql.md) - Lua API reference - [Store](system/store.md) - Key-value store backed by a `db.sql.*` database - [Queue](system/queue.md) - SQL-backed queue handler - [Change Data Capture](system/cdc.md) - Streaming row-level changes from a `db.sql.sqlite` or Postgres database ## Navigation Previous: "Terminal" (system/terminal) Next: "Change Data Capture" (system/cdc)