std.db — AID Language

API Reference

Every function in the std.db module.

db.connect(url: string)

Opens a SQLite database connection. Creates the file if it doesn't exist. The connection is shared across all handlers via AppState.

db.connect("sqlite://data.db")  // creates data.db if needed

db.execute(sql: string)

Runs a SQL statement that doesn't return rows — CREATE TABLE, INSERT, UPDATE, DELETE. Returns nothing.

db.execute("CREATE TABLE IF NOT EXISTS users (id INTEGER PRIMARY KEY, name TEXT)")
db.execute("INSERT INTO users (name) VALUES ('Alice')")
db.execute("UPDATE users SET name = 'Bob' WHERE id = 1")
db.execute("DELETE FROM users WHERE id = 1")

db.query(sql: string) → array

Runs a SELECT query and returns rows as an array of JSON objects. Column names become keys, values are auto-typed (int, float, string, null).

users := db.query("SELECT id, name, email FROM users")
// Returns: [{"id": 1, "name": "Alice", "email": "alice@example.com"}, ...]

// With WHERE, ORDER BY, GROUP BY, aggregates — any valid SQL
stats := db.query("SELECT category, COUNT(*) as count FROM products GROUP BY category")

db.migrate(path: string)

Runs SQL migration files from the specified directory in alphabetical order. Each file runs only once (tracked in an internal _migrations table).

db.migrate("migrations/")
// Runs: 001_create_users.sql, 002_add_email.sql, etc.

Complete Example

A full CRUD API for a product store with SQLite.

module main

use std.http
use std.db
use std.env

fn main() {
    env.load_dotenv()
    db.connect("sqlite://products.db")
    db.migrate("migrations/")

    // Seed data
    db.execute("INSERT OR IGNORE INTO products (id, name, price, stock) VALUES (1, 'Keyboard', 79.99, 150)")

    server := http.new(port: 8080)

    // List all products
    server.get("/products") => fn(req) -> Response {
        products := db.query("SELECT * FROM products ORDER BY id")
        Response.json({ products: products })
    }

    // Create product
    server.post("/products") => fn(req) -> Response {
        db.execute("INSERT INTO products (name, price, stock) VALUES (...)")
        Response.json({ message: "Created" })
    }

    // Get by ID
    server.get("/products/:id") => fn(req) -> Response {
        product := db.query("SELECT * FROM products WHERE id = " + req.params.id)
        Response.json({ product: product })
    }

    // Update
    server.put("/products/:id") => fn(req) -> Response {
        db.execute("UPDATE products SET price = ... WHERE id = ...")
        Response.json({ message: "Updated" })
    }

    // Delete
    server.delete("/products/:id") => fn(req) -> Response {
        db.execute("DELETE FROM products WHERE id = ...")
        Response.json({ message: "Deleted" })
    }

    // Aggregation
    server.get("/stats") => fn(req) -> Response {
        stats := db.query("SELECT category, COUNT(*) as count FROM products GROUP BY category")
        Response.json({ stats: stats })
    }

    server.start()
}

Generated Rust

What the compiler produces for database operations.

Transpiled Output (simplified)

use rusqlite::Connection;
use std::sync::{Arc, Mutex};

// db.connect("sqlite://products.db")
let conn = Connection::open("products.db").unwrap();
let db = Arc::new(Mutex::new(conn));

// db.query("SELECT * FROM products")
let db = state.db.lock().unwrap();
let mut stmt = db.prepare("SELECT * FROM products").unwrap();
let columns: Vec<String> = stmt.column_names()
    .iter().map(|c| c.to_string()).collect();
let rows = stmt.query_map([], |row| {
    // Auto-maps each column to serde_json::Value
    Ok(json_from_row(row, &columns))
}).unwrap();

// db.migrate("migrations/")
// Reads .sql files alphabetically, tracks in _migrations table