Database Module

The std/db module provides a driver-agnostic SQL database interface with built-in support for SQLite, PostgreSQL, MySQL, and MSSQL. All four drivers work in both interpreted (chuks run) and compiled (chuks build) modes — no packages to install.

Import

import { db, DbDriver } from "std/db"

Connecting

db.open(driver: DbDriver, dsn: string, config?: any): any

Open a database connection. Returns a connection handle. The driver parameter accepts a DbDriver enum value. The optional config map lets you control connection behaviour (e.g. { "autoSyncTableToDB": false }).

DbDriver enum values:

Enum Value	Database
DbDriver.Sqlite	SQLite (file path or ":memory:")
DbDriver.Sqlite3	SQLite (alias)
DbDriver.Postgres	PostgreSQL
DbDriver.PostgreSQL	PostgreSQL (alias)
DbDriver.MySQL	MySQL / MariaDB
DbDriver.MariaDB	MariaDB (alias)
DbDriver.MSSQL	Microsoft SQL Server
DbDriver.SQLServer	SQL Server (alias)

// SQLite — in-memory
var conn: any = db.open(DbDriver.Sqlite, ":memory:")

// SQLite — file-based
var conn: any = db.open(DbDriver.Sqlite, "./data.db")

// PostgreSQL
var conn: any = db.open(DbDriver.Postgres, "postgres://user:pass@localhost:5432/mydb?sslmode=disable")

// MySQL
var conn: any = db.open(DbDriver.MySQL, "user:pass@tcp(localhost:3306)/mydb")

// MSSQL
var conn: any = db.open(DbDriver.MSSQL, "sqlserver://user:pass@localhost:1433?database=mydb")

// With config options
var conn: any = db.open(DbDriver.Sqlite, ":memory:", { "autoSyncTableToDB": false })

db.close(conn: any): bool

Close a database connection. Returns true on success.

db.close(conn)

db.driver(conn: any): string

Returns the driver name for a connection handle. Useful when writing driver-agnostic code.

var conn: any = db.open(DbDriver.Postgres, "postgres://localhost/mydb")
println(db.driver(conn)) // postgres

db.getConfig(conn: any, key: string): any

Read a configuration value from a connection. Returns null if the key doesn’t exist.

var conn: any = db.open(DbDriver.Sqlite, ":memory:", { "autoSyncTableToDB": false })
println(db.getConfig(conn, "autoSyncTableToDB")) // false

Executing Statements

db.exec(conn: any, query: string, params?: []any): any

Execute a non-query SQL statement (INSERT, UPDATE, DELETE, CREATE, DROP, etc.). Returns a map with rowsAffected and lastInsertId.

Use ? placeholders for parameterized queries to prevent SQL injection.

// Create a table
db.exec(conn, "CREATE TABLE users (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, email TEXT, age INTEGER)")

// Insert with parameters
var result: any = db.exec(conn, "INSERT INTO users (name, email, age) VALUES (?, ?, ?)", ["Alice", "alice@example.com", 30])
println(result["lastInsertId"])  // 1
println(result["rowsAffected"]) // 1

// Update
var upd: any = db.exec(conn, "UPDATE users SET age = ? WHERE name = ?", [31, "Alice"])
println(upd["rowsAffected"]) // 1

// Delete
var del: any = db.exec(conn, "DELETE FROM users WHERE name = ?", ["Alice"])
println(del["rowsAffected"]) // 1

Querying Data

db.query(conn: any, query: string, params?: []any): []any

Execute a query and return all matching rows as an array of maps. Each row is a map with column names as keys. Returns an empty array if no rows match.

var rows: any = db.query(conn, "SELECT * FROM users ORDER BY id")
for (var i: int = 0; i < length(rows); i++) {
    println(rows[i]["name"])
}

// With parameters
var adults: any = db.query(conn, "SELECT * FROM users WHERE age >= ?", [18])
println(length(adults))

db.queryRow(conn: any, query: string, params?: []any): any

Execute a query and return a single row as a map, or null if no row matches.

var user: any = db.queryRow(conn, "SELECT * FROM users WHERE id = ?", [1])
if (user != null) {
    println(user["name"])
    println(user["email"])
}

// No match returns null
var nobody: any = db.queryRow(conn, "SELECT * FROM users WHERE id = ?", [999])
println(nobody) // null

Transactions

Transactions allow you to group multiple operations that either all succeed or all fail.

db.begin(conn: any): any

Begin a new transaction. Returns a transaction handle.

db.commit(tx: any): bool

Commit a transaction. Returns true on success.

db.rollback(tx: any): bool

Roll back a transaction. Returns true on success.

db.txExec(tx: any, query: string, params?: []any): any

Execute a non-query statement within a transaction. Same return value as db.exec().

db.txQuery(tx: any, query: string, params?: []any): []any

Execute a query within a transaction. Same return value as db.query().

var tx: any = db.begin(conn)

try {
    db.txExec(tx, "INSERT INTO users (name, email) VALUES (?, ?)", ["Alice", "alice@example.com"])
    db.txExec(tx, "INSERT INTO users (name, email) VALUES (?, ?)", ["Bob", "bob@example.com"])
    db.commit(tx)
    println("Transaction committed")
} catch (err) {
    db.rollback(tx)
    println("Transaction rolled back: " + err.message)
}

Rollback Example

var tx: any = db.begin(conn)
db.txExec(tx, "INSERT INTO users (name) VALUES (?)", ["Temporary"])
db.rollback(tx) // Undo the insert

// The row was never persisted
var rows: any = db.query(conn, "SELECT * FROM users WHERE name = ?", ["Temporary"])
println(length(rows)) // 0

Prepared Statements

Prepared statements compile a query once and execute it multiple times with different parameters — improving performance for repeated operations.

db.prepare(conn: any, query: string): any

Create a prepared statement. Returns a statement handle.

db.stmtExec(stmt: any, params?: []any): any

Execute a prepared statement for non-query operations. Same return value as db.exec().

db.stmtQuery(stmt: any, params?: []any): []any

Execute a prepared statement query. Same return value as db.query().

db.stmtClose(stmt: any): bool

Close a prepared statement to free resources. Returns true on success.

// Batch insert with a prepared statement
var stmt: any = db.prepare(conn, "INSERT INTO users (name, email, age) VALUES (?, ?, ?)")

db.stmtExec(stmt, ["Alice", "alice@example.com", 30])
db.stmtExec(stmt, ["Bob", "bob@example.com", 25])
db.stmtExec(stmt, ["Charlie", "charlie@example.com", 35])

db.stmtClose(stmt)

// Prepared query
var stmt: any = db.prepare(conn, "SELECT * FROM users WHERE age >= ?")

var adults: any = db.stmtQuery(stmt, [18])
println(length(adults))

var seniors: any = db.stmtQuery(stmt, [65])
println(length(seniors))

db.stmtClose(stmt)

NULL Handling

SQL NULL values are mapped to Chuks null. You can pass null as a parameter value and receive null back from queries.

// Insert with NULL
db.exec(conn, "INSERT INTO users (name, email) VALUES (?, ?)", ["Alice", null])

// Query returns null for NULL columns
var user: any = db.queryRow(conn, "SELECT * FROM users WHERE name = ?", ["Alice"])
println(user["email"]) // null

Schema Definition

db.define<T>(conn: any, tableName: string, callback: (schema: SchemaBuilder) => void): Schema

Define a table schema using a fluent callback API. Pass a connection handle so the schema knows which driver to target. Returns a Schema object that can generate DDL and sync to the database. See the Schema & Repository guide for full documentation.

You can also call db.define<T>(tableName, callback) without a connection — useful for DDL generation without a live database.

import { db, DbDriver } from "std/db"
import { Schema, SchemaBuilder } from "std/db/schema"
import { Repository } from "std/db/repository"

dataType User {
    id: int;
    name: string;
    email: string;
    age: int
}

var conn: any = db.open(DbDriver.Sqlite, ":memory:")

const UserSchema: Schema = db.define<User>(conn, "users", (schema: SchemaBuilder) => {
    schema.pk("id").auto()
    schema.string("name").notNull()
    schema.string("email").unique().notNull()
    schema.int("age").min(0).max(150)
})

class UserRepo extends Repository<User> {
    constructor() { super(UserSchema) }
}

Function Reference

Function	Description
db.open(driver, dsn, config?)	Open a database connection
db.close(conn)	Close a connection
db.driver(conn)	Get the driver name for a connection
db.getConfig(conn, key)	Read a config value from a connection
db.define<T>(conn?, table, callback)	Define a table schema — returns Schema (guide)
db.exec(conn, query, params?)	Execute non-query (INSERT, UPDATE, DELETE)
db.query(conn, query, params?)	Query all matching rows
db.queryRow(conn, query, params?)	Query a single row (or null)
db.begin(conn)	Begin a transaction
db.commit(tx)	Commit a transaction
db.rollback(tx)	Roll back a transaction
db.txExec(tx, query, params?)	Execute within a transaction
db.txQuery(tx, query, params?)	Query within a transaction
db.prepare(conn, query)	Create a prepared statement
db.stmtExec(stmt, params?)	Execute a prepared statement
db.stmtQuery(stmt, params?)	Query with a prepared statement
db.stmtClose(stmt)	Close a prepared statement

Complete Example

import { db, DbDriver } from "std/db"

// Connect
var conn: any = db.open(DbDriver.Sqlite, ":memory:")

// Schema
db.exec(conn, "CREATE TABLE products (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT NOT NULL, price REAL, stock INTEGER DEFAULT 0)")

// Insert products
var insert: any = db.prepare(conn, "INSERT INTO products (name, price, stock) VALUES (?, ?, ?)")
db.stmtExec(insert, ["Widget", 9.99, 100])
db.stmtExec(insert, ["Gadget", 24.99, 50])
db.stmtExec(insert, ["Doohickey", 4.99, 200])
db.stmtClose(insert)

// Query
var products: any = db.query(conn, "SELECT * FROM products WHERE price > ?", [5.0])
for (var i: int = 0; i < length(products); i++) {
    var p: any = products[i]
    println(p["name"] + " — $" + string(p["price"]) + " (" + string(p["stock"]) + " in stock)")
}

// Transaction: purchase
var tx: any = db.begin(conn)
db.txExec(tx, "UPDATE products SET stock = stock - ? WHERE name = ?", [2, "Widget"])
db.txExec(tx, "UPDATE products SET stock = stock - ? WHERE name = ?", [1, "Gadget"])
db.commit(tx)

// Verify
var widget: any = db.queryRow(conn, "SELECT stock FROM products WHERE name = ?", ["Widget"])
println("Widget stock: " + string(widget["stock"])) // 98

// Clean up
db.close(conn)