Database

Zolo provides built-in database support through the std::Database and std::SQL modules. You can connect to SQLite, PostgreSQL, MSSQL, and Oracle databases using a unified API, and leverage automatic SQL transpilation across dialects.

Quick Start #

use std::Database

let db = Database.open("sqlite://:memory:")
defer db.close()

db.execute("CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT)")
db.execute("INSERT INTO users (id, name) VALUES (1, 'Alice')")

let rows = db.query("SELECT * FROM users")
for row in rows {
  print("{row.name}")
}

Opening a Connection #

Use Database.open() with a connection string to connect to a database. The connection string format is driver://connection_details:

use std::Database

// SQLite — in-memory database
let db = Database.open("sqlite://:memory:")

// SQLite — file-based
let db = Database.open("sqlite:///path/to/database.db")

// PostgreSQL
let db = Database.open("postgres://user:password@host:5432/dbname")

// MSSQL
let db = Database.open("mssql://user:password@host:1433/dbname")

// Oracle
let db = Database.open("oracle://user:password@host:1521/service")

Executing SQL #

Use db.execute() to run statements that modify data (CREATE, INSERT, UPDATE, DELETE). For UPDATE and DELETE, it returns the number of affected rows:

// Create a table
db.execute("CREATE TABLE products (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    price REAL
)")

// Insert rows
db.execute("INSERT INTO products (id, name, price) VALUES (1, 'Widget', 9.99)")
db.execute("INSERT INTO products (id, name, price) VALUES (2, 'Gadget', 19.99)")

// Update — returns affected row count
let affected = db.execute("UPDATE products SET price = 14.99 WHERE name = 'Widget'")
print("Updated: {affected} row(s)")

// Delete
db.execute("DELETE FROM products WHERE price < 5")

Querying Data #

Use db.query() to run SELECT statements. It returns an iterable collection of rows:

let rows = db.query("SELECT * FROM products ORDER BY price DESC LIMIT 2")
for row in rows {
  print("{row.name}: ${row.price}")
}

Row Access #

Query results use dot notation to access column values by name:

let rows = db.query("SELECT name, price FROM products ORDER BY name")
for row in rows {
  print("{row.name}: ${row.price}")
}

Each row object exposes columns as properties matching the column names in your query.

SQL Transpilation #

The std::SQL module provides cross-database SQL transpilation, converting standard SQL into dialect-specific syntax.

SQL.compile — Standalone Transpilation #

Use SQL.compile() to convert a SQL string for a specific driver:

use std::SQL

["postgres", "mssql", "oracle"].* |> .each(|driver| {
  let sql = SQL.compile("SELECT * FROM users LIMIT 10", driver)
  print("{driver}: {sql}")
})

This converts standard SQL constructs (like LIMIT) into the equivalent syntax for each target database (e.g., TOP for MSSQL, FETCH FIRST for Oracle).

SQL() — Auto-Transpile for Connected Database #

When used inside db.query(), the SQL() macro automatically transpiles the query for the currently connected database driver:

use std::Database
use std::SQL

let db = Database.open("postgres://user:pass@localhost:5432/mydb")
defer db.close()

// SQL() detects the connected driver and transpiles automatically
let rows = db.query(SQL("SELECT * FROM products ORDER BY price DESC LIMIT 2"))
for row in rows {
  print("{row.name}: ${row.price}")
}

This lets you write portable SQL that works across different database backends without manually specifying the driver.

Resource Management #

Always close database connections when done. Use defer to ensure the connection is closed even if an error occurs:

let db = Database.open("sqlite://:memory:")
defer db.close()

// ... use db ...
// db.close() is called automatically when the scope exits

Connection Health Check #

Use db.ping() to verify the connection is alive:

let ok = db.ping()
print("Ping: {ok}")  // true if connected

Complete Example #

use std::Database
use std::SQL

// SQL Transpilation (standalone)
["postgres", "mssql", "oracle"].* |> .each(|driver| {
  let sql = SQL.compile("SELECT * FROM users LIMIT 10", driver)
  print("{driver}: {sql}")
})

// Database OOP API with SQLite
let db = Database.open("sqlite://:memory:")
defer db.close()

// Create
db.execute("CREATE TABLE products (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    price REAL
)")

// Insert
db.execute("INSERT INTO products (id, name, price) VALUES (1, 'Widget', 9.99)")
db.execute("INSERT INTO products (id, name, price) VALUES (2, 'Gadget', 19.99)")
db.execute("INSERT INTO products (id, name, price) VALUES (3, 'Doohickey', 4.99)")

// Query with SQL() — auto-transpiled for the connected driver
let rows = db.query(SQL("SELECT * FROM products ORDER BY price DESC LIMIT 2"))
for row in rows {
  print("{row.name}: ${row.price}")
}

// Ping
let ok = db.ping()
print("Ping: {ok}")

// Update
let affected = db.execute("UPDATE products SET price = 14.99 WHERE name = 'Widget'")
print("Updated: {affected} row(s)")

// Delete
db.execute("DELETE FROM products WHERE price < 5")

// Verify
let remaining = db.query("SELECT name, price FROM products ORDER BY name")
print("Remaining products:")
for row in remaining {
  print("  {row.name}: ${row.price}")
}