# "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)