Database System

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.

See Also

• SQL Module - Lua API reference
• Store - Key-value store backed by a db.sql.* database
• Queue - SQL-backed queue handler