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 #

zolo▶ Playground
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:

zolo▶ Playground
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:

zolo▶ Playground
// 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:

zolo▶ Playground
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:

zolo▶ Playground
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:

zolo▶ Playground
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:

zolo▶ Playground
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:

zolo▶ Playground
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:

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

Complete Example #

zolo▶ Playground
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, &#39;Widget&#39;, 9.99)")
db.execute("INSERT INTO products (id, name, price) VALUES (2, &#39;Gadget&#39;, 19.99)")
db.execute("INSERT INTO products (id, name, price) VALUES (3, &#39;Doohickey&#39;, 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 = &#39;Widget&#39;")
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}")
}
Edit on GitHub
enespt-br