Database System

SQL database connection pooling and configuration. Supports PostgreSQL, MySQL, and SQLite.

Entry Kinds

Kind	Description
`db.sql.postgres`	PostgreSQL database
`db.sql.mysql`	MySQL database
`db.sql.sqlite`	SQLite database

Configuration

Standard Databases (PostgreSQL, MySQL)

# 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: "dbpass"
    pool:
      max_open: 25
      max_idle: 5
      max_lifetime: "1h"
    options:
      sslmode: "disable"
    lifecycle:
      auto_start: true

SQLite

  - name: cache_db
    kind: db.sql.sqlite
    file: "/var/data/cache.db"  # Use :memory: for in-memory
    pool:
      max_lifetime: "1h"
    lifecycle:
      auto_start: true

SQLite always runs with a single connection (max_open and max_idle are forced to 1) and WAL journal mode. Only max_lifetime from pool is applied.

Connection Fields

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	Description
`file`	string	Database file path or `:memory:`
`pool`	object	Only `max_lifetime` is applied (connections are fixed at 1)
`options`	map	Accepted but ignored
`lifecycle`	object	Lifecycle configuration

Environment Variable Fields

Use _env suffix to load values from environment variables or env.variable entries:

Field	Description
`host_env`	Host from environment variable
`port_env`	Port from environment variable
`database_env`	Database name from environment
`username_env`	Username from environment
`password_env`	Password from environment

- name: prod_db
  kind: db.sql.postgres
  host_env: "DB_HOST"
  port_env: "DB_PORT"
  database_env: "DB_NAME"
  username_env: "DB_USER"
  password_env: "app.secrets:db_password"  # Reference env.variable entry

Avoid hardcoding passwords in configuration. Use environment variables or env.variable entries for credentials. See Environment for secure secret management.

Connection Pool

Configure connection pooling behavior. Pool settings map to Go's database/sql connection pool.

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

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 ...]

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"}

options:
  sslmode: "require"      # disable, require, verify-ca, verify-full
  connect_timeout: "10"   # Connection timeout in seconds
  application_name: "myapp"

MySQL {id="options-mysql"}

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.

Examples

PostgreSQL with SSL

- name: secure_postgres
  kind: db.sql.postgres
  host: "db.example.com"
  port: 5432
  database: "production"
  username: "app_user"
  password: "${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

- name: mysql_replica
  kind: db.sql.mysql
  host: "replica.db.example.com"
  port: 3306
  database: "app"
  username: "readonly"
  password_env: "REPLICA_PASSWORD"
  pool:
    max_open: 20
    max_idle: 5
    max_lifetime: "30m"
  options:
    charset: "utf8mb4"
    parseTime: "true"
    readTimeout: "30s"

SQLite In-Memory

- name: test_db
  kind: db.sql.sqlite
  file: ":memory:"

Multiple Database Setup

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: "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: "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, enabling dynamic database configuration based on application state or external configuration.

Lua API

See SQL Module for database operations API.