SQLite databases

How it works

Vary has built-in SQLite support. You define data types whose fields become SQL columns, connect to a database file (or an in-memory database), and run typed queries. The compiler validates column names and types at compile time and generates JVM bytecode that calls SQLite through JDBC.

There is no ORM and no configuration. SQLite is bundled with the runtime, so there is nothing extra to install.

Connecting

Sql.connect(path) opens a file-backed database. Sql.memory() opens an in-memory database that lives for the duration of the connection.

let db = Sql.connect("app.db")

# or in-memory for tests / ephemeral data
let mem = Sql.memory()

connect() creates parent directories if they don't exist and resolves relative paths to absolute. When you're done, call db.close().

Defining tables

Data types serve as SQL table schemas. The compiler derives the column names and types from the data type's fields, so a single declaration defines both the value type and the table schema that query expressions check against at compile time.

data User {
    id: Int
    name: Str
    score: Float
    active: Bool
}

There are four column types:

Creating tables

The setup expression creates a SQL table from a data type declaration:

setup db { create User }

This generates CREATE TABLE IF NOT EXISTS User (id INTEGER, name TEXT, score REAL, active INTEGER) from the data User definition. Column types are derived automatically from the data type's fields.

For DDL that goes beyond what the DSL covers, use the raw setup() method:

db.setup("CREATE INDEX idx_user_name ON User (name)")

Inserting data

The insert expression adds rows with compile-time column validation:

insert db {
    into User
    values {
        id = 1
        name = "Alice"
        score = 95.5
        active = True
    }
}

The compiler checks that every column name exists in the data type and that the value types match. The values are bound as parameters (not interpolated), so injection is not possible.

For bulk or dynamic inserts, use the raw execute() method:

let affected = db.execute("INSERT INTO User VALUES (?, ?, ?, ?)", [3, "Carol", 91.2, 1])

Both return the number of affected rows as an Int.

Querying data

The query expression runs a typed SELECT and returns a List of rows:

let rows = query db {
    from User as u
    select {
        id = u.id
        name = u.name
        score = u.score
    }
}

for row in rows {
    print(row.name + ": " + str(row.score))
}

The compiler checks that User is a data type with SQL-compatible fields, that u.id, u.name, and u.score are valid columns, and that row.name is accessed as a Str (not an Int). A type mismatch is a compile error, not a runtime surprise.

Filtering with where

Add a where clause to filter rows. The expression uses Vary syntax, not SQL:

let active = query db {
    from User as u
    where u.active == True
    select {
        name = u.name
        score = u.score
    }
}

Column references in the where clause are validated against the table schema. Values are bound as parameters in the generated SQL.

Single-row queries

query_one returns a single row or None instead of a list:

let user = query_one db {
    from User as u
    where u.id == target_id
    select {
        id = u.id
        name = u.name
    }
}

if user is not None {
    print(user.name)
}

The return type is nullable (Row?), so the compiler requires a null check before accessing fields.

Deleting data

The delete expression removes rows matching a condition and returns the number of affected rows:

let removed = delete db {
    from User
    where User.id == target_id
}

A where clause is required. The table name can be used directly as the column qualifier (no alias needed).

Counting rows

The count expression returns the number of rows in a table:

let n = count db { from User }

This generates SELECT COUNT(*) FROM User.

File management

Static methods on Sql handle files on disk:

The path() method on a connection returns the resolved absolute path, or ":memory:" for in-memory databases.

import process

let path = process.tempdir() + "/myapp/data.db"
let db = Sql.connect(path)
print(db.path())    # absolute path to data.db

db.setup("CREATE TABLE kv (key TEXT, val TEXT)")
db.execute("INSERT INTO kv VALUES (?, ?)", ["lang", "vary"])
db.close()

# data persists on disk
print(Sql.exists(path))  # True

# reopen later
let db2 = Sql.connect(path)
let rows = db2.execute_query("SELECT val FROM kv WHERE key = ?", ["lang"])
db2.close()

# clean up
Sql.remove(path)
print(Sql.exists(path))  # False

Connection methods

Testing with SQLite

In-memory databases work well for tests: they are fast and need no cleanup.

data Product {
    id: Int
    name: Str
    price: Float
}

test "insert and query products" {
    let db = Sql.memory()
    setup db { create Product }
    insert db {
        into Product
        values { id = 1  name = "Widget"  price = 9.99 }
    }
    insert db {
        into Product
        values { id = 2  name = "Gadget"  price = 24.50 }
    }

    let rows = query db {
        from Product as p
        select {
            name = p.name
            price = p.price
        }
    }

    observe len(rows) == 2
    observe rows[0].name == "Widget"
    observe rows[1].price == 24.50
    db.close()
}

For tests that need data to persist across connections, use file databases with process.tempdir() and clean up with Sql.remove():

import process

test "data survives reconnect" {
    let path = process.tempdir() + "/test_persist.db"
    let db1 = Sql.connect(path)
    db1.setup("CREATE TABLE t (v INTEGER)")
    db1.setup("INSERT INTO t VALUES (42)")
    db1.close()

    let db2 = Sql.connect(path)
    let rows = db2.execute_query("SELECT v FROM t", [])
    observe rows[0].get("v") == 42
    db2.close()
    Sql.remove(path)
}