Using Databases

Luma provides database access through the db built-in module and pluggable driver modules.
The design separates the interface (what your application uses) from the driver (how the protocol works), so application code stays the same regardless of which database engine you connect to.

Luma includes two built-in database drivers, both written entirely in Luma with no external dependencies:

• MySQL – full read/write support for MySQL databases
• SQLite – read-only support for SQLite3 database files (see SQLite Driver)

Connecting

Import a driver and open a connection

mysql = import "mysql"
conn = db.open(mysql, "root:secret@localhost:3306/mydb")

sqlite = import "sqlite"
conn = db.open(sqlite, "/path/to/database.db")

db.open() takes two arguments:

1. A driver module — any module that implements the required driver functions (connect, query, exec, disconnect)
2. A DSN string — the connection details in the driver’s expected format

The returned conn is a managed connection object with methods for querying, executing, transactions, and closing.

DSN format (MySQL)

The built-in MySQL driver expects:

user:password@host:port/database

Examples:

"root:@localhost:3306/mydb"            // no password
"admin:secret@db.example.com:3306/app" // full credentials

DSN format (SQLite)

The SQLite driver expects a file path:

"/tmp/mydata.db"
"data/local.db"

Executing Queries

Basic query

rows = conn.query("SELECT name, age FROM users")

Returns a result set that you can iterate, count, or inspect.

Query with named parameters

rows = conn.query("SELECT name, age FROM users WHERE age > :min_age", {
    "min_age": 21
})

Named parameters use the :name syntax. Pass a map as the second argument — keys match parameter names, values are automatically formatted and escaped.

Named parameters prevent SQL injection. Strings are quoted and escaped, numbers are inserted bare, nil becomes NULL, and booleans become 1 or 0.

Executing Statements

Basic exec

conn.exec("CREATE TABLE users (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(100), age INT)")

conn.exec() runs a statement that doesn’t return rows. It returns the number of affected rows as an int.

Exec with named parameters

affected: int = conn.exec("INSERT INTO users (name, age) VALUES (:name, :age)", {
    "name": "Alice",
    "age": 30
})

Named parameters work the same way as in conn.query().

Last Insert ID

After an INSERT into a table with an auto-increment primary key, conn.lastInsertId() returns the ID of the newly inserted row as an int.

conn.exec("INSERT INTO users (name, age) VALUES (:name, :age)", {
    "name": "Alice",
    "age": 30
})

id: int = conn.lastInsertId()
print("New user ID: ${id}")

conn.lastInsertId() always returns the value from the most recent conn.exec() call. If the last statement wasn’t an INSERT or no statement has been executed, it returns 0.

conn.exec("INSERT INTO users (name) VALUES (:name)", { "name": "Alice" })
print(conn.lastInsertId())  // 1

conn.exec("INSERT INTO users (name) VALUES (:name)", { "name": "Bob" })
print(conn.lastInsertId())  // 2

conn.exec("UPDATE users SET name = 'Charlie' WHERE id = 1")
print(conn.lastInsertId())  // 0

Debugging SQL

After any conn.query() or conn.exec() call, conn.debug() returns the actual SQL string that was sent to the database — with all named parameters already substituted.

conn.query("SELECT name, age FROM users WHERE age > :min_age", {
    "min_age": 21
})

print(conn.debug())
// Output: SELECT name, age FROM users WHERE age > 21

This is invaluable for debugging. When a query doesn’t return what you expect, conn.debug() shows you exactly what the database received — no guessing about parameter binding.

conn.exec("INSERT INTO users (name, age) VALUES (:name, :age)", {
    "name": "Alice",
    "age": 30
})

print(conn.debug())
// Output: INSERT INTO users (name, age) VALUES ('Alice', 30)

conn.debug() always returns the SQL from the most recent query() or exec() call. If no statement has been executed yet, it returns an empty string.

Working with Results

Iterating rows

rows = conn.query("SELECT name, age FROM users")

rows.walk(row) -> {
    name: str = row.get("name")
    age: int = row.get("age")
    print("${name} is ${age} years old")
}

rows.walk() iterates over every row in the result set.

Iterating with an index

rows.walk(i, row) -> {
    name: str = row.get("name")
    print("[${i}] ${name}")
}

Pass two parameters to receive the zero-based index alongside each row.

Counting rows

count: int = rows.count()
print("Found ${count} rows")

Getting the first row

first = rows.first()
name: str = first.get("name")

Panics if the result set is empty.

Getting all rows as a list

all = rows.all()

Returns a list of row objects. Useful when you need random access or want to pass the data to another function.

Reading Row Values

row.get() retrieves a column value by name. The return type is determined by the declared type of the receiving variable:

name: str   = row.get("name")     // returns string
age: int    = row.get("age")      // converts to int
price: float = row.get("price")   // converts to float
active: bool = row.get("active")  // converts to bool

If no type is declared, row.get() returns a str.

If the column doesn’t exist, row.get() panics with a clear error message.

Transactions

Starting a transaction

conn.beginTransaction()

Executes BEGIN on the database. Panics if a transaction is already active.

Checking transaction state

if conn.inTransaction() {
    print("Inside a transaction")
}

Returns true if a transaction is active, false otherwise.

Committing

conn.commit()

Executes COMMIT. Panics if no transaction is active.

Rolling back

conn.rollback()

Executes ROLLBACK. Panics if no transaction is active.

Transaction example

conn.beginTransaction()

conn.exec("INSERT INTO orders (user_id, total) VALUES (:uid, :total)", {
    "uid": 42,
    "total": 99
})
conn.exec("UPDATE inventory SET stock = stock - 1 WHERE item_id = :id", {
    "id": 7
})

conn.commit()

If something goes wrong between beginTransaction() and commit(), use rollback() to discard the changes.

Closing

conn.close()

Closes the database connection. After closing, any method call on the connection will fail.

Error Handling

The db module uses Luma’s standard panic-based error model:

Complete Example

mysql = import "mysql"

conn = db.open(mysql, "root:@localhost:3306/myapp")

// Create a table
conn.exec("CREATE TABLE IF NOT EXISTS users (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(100), age INT)")

// Insert rows with named parameters
conn.exec("INSERT INTO users (name, age) VALUES (:name, :age)", {
    "name": "Alice",
    "age": 30
})
conn.exec("INSERT INTO users (name, age) VALUES (:name, :age)", {
    "name": "Bob",
    "age": 25
})

// Query with a filter
rows = conn.query("SELECT name, age FROM users WHERE age > :min", {
    "min": 20
})

print("Users older than 20:")
rows.walk(row) -> {
    name: str = row.get("name")
    age: int = row.get("age")
    print("  ${name} is ${age}")
}

// Transaction
conn.beginTransaction()
conn.exec("INSERT INTO users (name, age) VALUES (:name, :age)", {
    "name": "Charlie",
    "age": 35
})
conn.commit()

// Count all users
all = conn.query("SELECT * FROM users")
count: int = all.count()
print("Total users: ${count}")

// Clean up
conn.exec("DROP TABLE users")
conn.close()

Method Summary

Connection (conn)

Result Set (rows)

Row (row)

Design Notes

• db.open() creates a managed connection — the db module handles named parameter binding, result normalization, and transaction state tracking on top of the raw driver
• Named parameters use :name syntax, not ? positional placeholders — this makes queries more readable and less error-prone
• All column values arrive from the driver as strings — type conversion happens at row.get() time, controlled by the declared type of the receiving variable
• The driver module is passed as a value to db.open(), not registered globally — this keeps everything explicit and composable
• Transaction state is tracked on the connection, so calling beginTransaction() twice or commit() without a transaction gives an immediate, clear error instead of a confusing SQL error