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:

Vary type	SQLite type	Notes
Int	INTEGER	64-bit signed integer
Str	TEXT	UTF-8 string
Float	REAL	64-bit floating point
Bool	INTEGER	Stored as 0/1, returned as True/False

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)")

Managed schemas and migrations

For application persistence, prefer a database declaration over hand-written schema setup calls. A database groups table-backed data types, records the managed binding name, and generates a SqlSchema factory function.

data User {
    id: Str
    email: Str
    created_at: Str
} table {
    name users
    primary_key id
    unique email
    index created_at
}

database AppDatabase version 1 binding "DATABASE" {
    table User
}

def open_app_database() -> SqlConnection {
    return Sql.migrate_binding("DATABASE", AppDatabase_schema())
}

Table metadata can declare primary keys, composite primary keys, indexes, unique indexes, defaults, and foreign keys. The generated schema creates tables and table metadata-backed indexes without copying CREATE TABLE or CREATE INDEX strings into application code.

When a released database must evolve, add a typed migration block. Migration operations are checked against the declared table metadata.

database AppDatabase version 2 binding "DATABASE" {
    table User

    migration 2 "user_status" {
        add_column User.status default "active"
        create_index User(status, created_at)
    }
}

Use raw SQL migrations only as an explicit escape hatch for backend-specific DDL that the migration DSL cannot express.

Repositories

Repository declarations generate ordinary methods for mechanical persistence operations. They can augment a handwritten store class, so domain transitions stay handwritten while simple storage plumbing is generated.

class UserStore(db: SqlConnection) {
    def disabled_user_label(self, id: Str) -> Str {
        return "disabled:" + id
    }
}

repository UserStore for AppDatabase {
    create User as create_user
    exists User by email as user_exists
    count User as user_count
    find User.id by email as user_id_for_email default ""
    set User.status by id as set_user_status
    upsert_fields User by email as save_user_by_email
}

Generated repository methods have normal Vary signatures and compile-time checked parameter and return types. Imported table metadata can be used from a separate store module, which lets large applications keep schema metadata, query row shapes, and domain transitions in separate files.

Queries

Use query declarations for named application read queries. They generate callable functions and require the selected aliases to match the declared result type.

data UserSummary {
    id: Str
    email: Str
}

query UserSummary list_users {
    from User as u
    order {
        u.created_at desc
    }
    select {
        id = u.id
        email = u.email
    }
}

Inside ordinary functions, use query ... into Type or query_one ... into Type for the same typed projection checks.

def active_users(db: SqlConnection) -> List[UserSummary] {
    return query db into UserSummary {
        from User as u
        where u.active == True
        select {
            id = u.id
            email = u.email
        }
    }
}

For query shapes that are not yet covered by the SQL DSL, use typed raw SQL:

def search_users(db: SqlConnection, email_like: Str) -> List[UserSummary] {
    return sql db into UserSummary "SELECT id AS id, email AS email FROM users WHERE email LIKE ?" [email_like]
}

Prefer a raw-backed query when the query is part of the application persistence surface. This keeps the escape hatch named, typed, and reusable while still making the raw SQL explicit:

query UserSummary search_users(email_like: Str, limit: Int) {
    sql "SELECT id AS id, email AS email FROM users WHERE email LIKE ? LIMIT ?" [email_like, limit]
}

def page_users(db: SqlConnection, email_like: Str) -> List[UserSummary] {
    return search_users(db, email_like, 50)
}

Avoid untyped execute_query(...) plus row.get_* mapping in application code. If a query returns an application row shape, declare that shape and project into it directly.

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:

Method	Returns	Purpose
Sql.connect(path)	SqlConnection	Open (or create) a database file, auto-creating parent directories
Sql.exists(path)	Bool	Check if a database file exists (returns False for directories)
Sql.remove(path)	Bool	Delete a database file and its SQLite sidecar files (-journal, -wal, -shm)

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

Method	Returns	Purpose
setup(sql)	Int	Execute a DDL or DML statement without parameters
execute(sql, params)	Int	Execute parameterized DML, returns affected row count
execute_query(sql, params)	List[Row]	Execute parameterized SELECT, returns typed rows
path()	Str	Resolved absolute path, or ":memory:"
close()	None	Close the underlying database connection

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)
}