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.
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().
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 |
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)")
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.
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.
whereAdd 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.
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.
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).
The count expression returns the number of rows in a table:
let n = count db { from User }
This generates SELECT COUNT(*) FROM User.
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
| 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 |
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)
}