db #

Description

db is a namespace that contains several useful modules for operating with databases (SQLite, MySQL, MSQL, etc.)

Common Driver Interface

The top-level db module exposes a small Driver interface for code that only needs common SQL operations:

import db

mut conn := db.open(db.DriverConfig{
    kind: .sqlite
    path: ':memory:'
})!
defer { conn.close() or {} }

rows := conn.exec('select 1 as n')!
println(rows[0].val(0))

SQLite support is available by default. The PostgreSQL, MySQL, and MSSQL adapters are compiled in only when their C client libraries are enabled:

• PostgreSQL: -d db_pg
• MySQL: -d db_mysql
• MSSQL/ODBC: -d db_mssql

For backend-specific features, continue using db.pg, db.mysql, db.sqlite, or db.mssql directly.

Shared connection pool

open_pooled creates a lazy pool over any built-in Driver. Each operation checks out one connection and releases it after the operation, including after a query error. SQLite's :memory: databases belong to individual connections; use a single connection or a file-backed database when statements must share their data.

import db

mut database := db.open_pooled(db.DriverConfig{
    kind: .sqlite
    path: ':memory:'
}, max_open_conns: 1, max_idle_conns: 1)
defer { database.close() or {} }

database.exec('create table users (id integer primary key, name text)')!
database.exec_param_many('insert into users (name) values (?)', ['alice'])!
println(database.exec_one('select name from users')!.val(0))

PoolConfig sets max_open_conns (zero means unlimited), max_idle_conns (default two), and conn_max_lifetime (zero disables expiration). The limits can also be changed on DB with set_max_open_conns, set_max_idle_conns, and set_conn_max_lifetime. stats() reports open, idle, in-use, and waiting connections. Negative count limits are normalized to zero.

Implement DriverFactory.connect() !&Driver to use a third-party driver with new_pool or new_db. The factory must support concurrent calls and return an independent physical connection each time. Pool.acquire() and DB.acquire() return fresh Conn handles; Conn.close() returns the physical connection. A released handle remains invalid even when another caller acquires the same physical connection. Copies of a Conn share the same checkout: releasing any copy invalidates every copy and returns the physical connection once. The pool calls Driver.reset() before reusing a released connection and discards connections whose reset fails. Reused connections are validated during acquisition, including direct handoffs to waiting callers; invalid and expired connections are discarded.

Reset behavior depends on the driver. Some built-in drivers implement reset() as a no-op; the pool does not guarantee a rollback or restore session settings. Finish manual transactions and perform any required session cleanup on the checked-out Conn before releasing it.

Closing a pool wakes waiting callers and closes idle connections. Checked-out connections remain usable until released, when they are closed. Acquisition errors from the factory are returned to the caller; construction itself does not connect. db.open() and existing backend-specific pools retain their current APIs.

Use a checked-out Conn to pin several statements to one session. The shared pool does not yet provide an ORM adapter or a pooled prepared-statement manager.

Shared transactions

DB.begin() returns a Tx that owns one connection until commit() or rollback(). Queries through the transaction always use that connection, while other database operations acquire their own connections and may wait for pool capacity. Always finish a transaction; defer a rollback so an early return or a query error does not keep a connection checked out.

mut tx := database.begin()!
defer { tx.rollback() or {} }

tx.exec_param_many('insert into users (name) values (?)', ['bob'])!
tx.savepoint('before_update')!
tx.exec_param_many('update users set name = ? where name = ?', ['bobby', 'bob'])!
tx.rollback_to('before_update')!
tx.release_savepoint('before_update')!
tx.commit()!

Tx exposes exec, exec_one, and exec_param_many, plus savepoint, rollback_to, and release_savepoint. Savepoint names must start with an ASCII letter or underscore and contain only ASCII letters, digits, or underscores. Built-in drivers quote these identifiers, so SQL keywords can be used as names. MSSQL limits names to 32 characters and does not support release_savepoint; it returns an error without finishing the transaction.

Transaction operations are serialized. A commit or rollback attempt finishes the transaction even if it fails; subsequent calls, including through a copied handle, return an error. A failed begin, commit, or rollback closes the physical connection to prevent reuse of an uncertain transaction state. Successful completion returns it through the pool's reset hook. Closing the database lets an existing transaction finish and closes its connection on release.

The default transaction commands use BEGIN, COMMIT, ROLLBACK, and ANSI SQL savepoints, with the database's default isolation level. A driver can implement the optional TransactionDriver.transaction(command TransactionCommand, name string) ! interface to use its native transaction API or SQL dialect. This keeps the existing Driver interface unchanged. The hook receives a validated, unquoted savepoint name, or an empty name for other commands; it must report database failures accurately. Built-in SQLite checks transaction result codes, MySQL uses backtick identifiers, and MSSQL uses its own transaction syntax.

Cross-driver consistency helpers

db.pg and db.mysql accept both user and username in their Config structs.

db.pg, db.mysql, and db.sqlite rows expose row.val(index) and row.values() for direct string access. In db.pg, SQL NULL remains available through row.val_opt(index).

db.pg, db.mysql, and db.sqlite also expose exec_param2(...) as a convenience wrapper around their parameterized query helpers.

fn new_db(factory DriverFactory, cfg PoolConfig) &DB

new_db constructs a lazily connected SQL handle using a third-party driver factory.

fn new_pool(factory DriverFactory, cfg PoolConfig) &Pool

new_pool creates a pool without opening a physical connection.

fn open(config DriverConfig) !&Driver

open creates a normalized database driver for the selected backend.

SQLite support is available by default. PostgreSQL, MySQL, and MSSQL are compiled in only when -d db_pg, -d db_mysql, or -d db_mssql is used, avoiding unconditional dependencies on their C client libraries.

fn open_pooled(config DriverConfig, cfg PoolConfig) &DB

open_pooled constructs a lazily connected pool for a built-in database driver. Connection errors are reported when a connection is first acquired.

fn DriverKind.from[W](input W) !DriverKind

from returns the DriverKind value of an integer or of a field name, or an error if there is none.

fn TransactionCommand.from[W](input W) !TransactionCommand

from returns the TransactionCommand value of an integer or of a field name, or an error if there is none.

interface Driver {
mut:
	exec(query string) ![]DriverRow
	exec_one(query string) !DriverRow
	exec_param_many(query string, params []string) ![]DriverRow
	validate() !bool
	reset() !
	close() !
}

Driver is the minimal common database connection interface exposed by the top-level db module. Backend-specific APIs remain available in their existing modules.

interface DriverFactory {
	connect() !&Driver
}

DriverFactory opens independent physical connections for a Pool.

interface TransactionDriver {
	Driver
mut:
	transaction(command TransactionCommand, name string) !
}

TransactionDriver optionally overrides transaction commands for a Driver's SQL dialect. Drivers without this interface use BEGIN, COMMIT, ROLLBACK, and SQL savepoint statements. name is a validated identifier for savepoint commands and empty for other commands.

enum DriverKind {
	sqlite
	mysql
	pg
	mssql
}

DriverKind selects the backend used by open.

enum TransactionCommand {
	begin
	commit
	rollback
	savepoint
	rollback_to
	release_savepoint
}

TransactionCommand identifies a transaction operation for an optional TransactionDriver.

struct Conn {
	pool       &Pool
	created_at time.Time
	state      &ConnState = &ConnState{}
}

Conn is a checked-out pool handle. Closing any copy invalidates all copies of the handle. Released handles cannot reach a reused driver.

fn (mut c Conn) exec(query string) ![]DriverRow

exec executes query on this checked-out physical connection.

fn (mut c Conn) exec_one(query string) !DriverRow

exec_one returns one row from this checked-out physical connection.

fn (mut c Conn) exec_param_many(query string, params []string) ![]DriverRow

exec_param_many executes query with parameters on this connection.

fn (mut c Conn) validate() !bool

validate checks whether this checked-out connection is usable.

fn (mut c Conn) reset() !

reset invokes the driver's backend-defined reset for this checked-out connection. Finish manual transactions and any required session cleanup before releasing it.

fn (mut c Conn) close() !

close returns the connection to its pool and invalidates every copy of this handle.

struct DB {
mut:
	pool &Pool
}

DB is a pool-backed SQL handle. Each operation borrows and releases one connection.

fn (mut database DB) acquire() !&Conn

acquire pins a connection for multiple statements or backend-specific SQL transactions.

fn (mut database DB) exec(query string) ![]DriverRow

exec executes query on an acquired connection and returns it to the pool.

fn (mut database DB) exec_one(query string) !DriverRow

exec_one returns one row and returns the acquired connection to the pool.

fn (mut database DB) exec_param_many(query string, params []string) ![]DriverRow

exec_param_many executes a parameterized query and releases the acquired connection.

fn (mut database DB) ping() !

ping verifies that a connection can be acquired and validated.

fn (mut database DB) close() !

close stops acquisition and closes idle connections; borrowed connections close on release.

fn (mut database DB) stats() PoolStats

stats returns a consistent snapshot of pool counters.

fn (mut database DB) set_max_open_conns(n int)

set_max_open_conns changes the physical connection limit; zero means unlimited.

fn (mut database DB) set_max_idle_conns(n int)

set_max_idle_conns changes the retained idle connection limit.

fn (mut database DB) set_conn_max_lifetime(d time.Duration)

set_conn_max_lifetime changes physical connection lifetime; zero disables expiration.

fn (mut database DB) begin() !&Tx

begin acquires an exclusive connection and starts a transaction using the driver's defaults. A failed start discards the connection because its transaction state may be uncertain.

struct DriverConfig {
pub:
	kind DriverKind

	path     string
	host     string
	port     int
	user     string
	username string
	password string
	dbname   string

	conn_str string
	dsn      string
	driver   string
	server   string
	uid      string
	pwd      string
	options  map[string]string

	ssl_mode   string
	ssl_key    string
	ssl_cert   string
	ssl_ca     string
	ssl_crl    string
	ssl_capath string
	ssl_cipher string
}

DriverConfig contains the common connection fields used by open.

SQLite uses path or, when empty, dbname. PostgreSQL/MySQL use the network/user fields. MSSQL also accepts ODBC fields such as conn_str, dsn, driver, server, uid, pwd, and options.

struct DriverRow {
pub mut:
	vals  []string
	names []string
}

DriverRow is the normalized row type returned by Driver implementations.

fn (row DriverRow) val(index int) string

val returns the value at index.

fn (row DriverRow) values() []string

values returns all row values.

fn (row DriverRow) get_string(col_name string) string

get_string returns the value for the given column name, or '' if it is missing.

struct Pool {
	factory DriverFactory
mut:
	mu           &sync.Mutex = sync.new_mutex()
	idle         []PoolSlot
	waiters      []chan PoolSlot
	open_count   int
	max_open     int
	max_idle     int
	max_lifetime time.Duration
	closed       bool
}

Pool manages independent Driver connections supplied by a DriverFactory.

fn (mut p Pool) acquire() !&Conn

acquire returns a fresh handle, waiting when the physical connection limit is reached. Reused connections are validated before they are handed to the caller.

fn (mut p Pool) release(conn &Conn)

release detaches the handle, resets its session, and returns or closes the connection. Releasing a handle again is harmless; handles belonging to another pool are ignored.

fn (mut p Pool) close()

close stops acquisition, wakes waiting callers, and closes idle connections. Checked-out connections close when released; close is idempotent.

fn (mut p Pool) stats() PoolStats

stats returns a consistent snapshot of the pool's counters.

fn (mut p Pool) set_max_open(n int)

set_max_open changes capacity; negative values mean unlimited.

fn (mut p Pool) set_max_idle(n int)

set_max_idle changes the idle connection limit; zero retains no idle connections.

fn (mut p Pool) set_max_lifetime(d time.Duration)

set_max_lifetime changes connection lifetime; nonpositive values disable expiration.

struct PoolConfig {
pub:
	max_open_conns    int
	max_idle_conns    int = 2
	conn_max_lifetime time.Duration
}

PoolConfig sets connection limits. Zero max_open_conns means unlimited.

struct PoolStats {
pub:
	max_open_connections int
	open_connections     int
	in_use               int
	idle                 int
	wait_count           int
}

PoolStats describes physical connections and callers waiting for capacity.

struct Tx {
	state &TxState = &TxState{}
}

Tx owns one pooled connection until commit or rollback, including when either fails. Its operations are serialized; using a finished transaction returns an error. Call rollback in a defer to ensure abandoned transactions release their connection.

fn (mut tx Tx) commit() !

commit commits and releases the pinned connection. A failure discards the connection. The transaction is finished even when the driver reports an error.

fn (mut tx Tx) rollback() !

rollback rolls back and releases the pinned connection. A failure discards the connection. The transaction is finished even when the driver reports an error.

fn (mut tx Tx) exec(query string) ![]DriverRow

exec executes query on the transaction's pinned connection.

fn (mut tx Tx) exec_one(query string) !DriverRow

exec_one returns one row from the transaction's pinned connection.

fn (mut tx Tx) exec_param_many(query string, params []string) ![]DriverRow

exec_param_many executes a parameterized query on the transaction's pinned connection.

fn (mut tx Tx) savepoint(name string) !

savepoint creates a savepoint named name, which must contain only identifier characters.

fn (mut tx Tx) rollback_to(name string) !

rollback_to rolls back to the named savepoint without finishing the transaction.

fn (mut tx Tx) release_savepoint(name string) !

release_savepoint removes a savepoint if supported by the driver's SQL dialect.