db
One interface shared by the database engines, schema migrations and a connection pool.
import db
Every database engine has its own package: db.sqlite, db.mysql, db.postgres, for key and value db.redis, for analytics db.clickhouse, and for search db.typesense, db.meilisearch, db.elasticsearch and, for search and analytics, db.opensearch; the last two also answer SQL queries through Connection. This package gives the Connection interface that the three SQL engines implement; write your code against it and changing engine is one line. To check the SQL itself before it ever reaches a server, db.sql parses it for each engine's dialect and resolves it against your schema.
Migrations move the database schema along with the code: every change has a number and MigrateUp applies the ones numbered above the current schema version. Never paste a value a user typed straight into SQL; send it through QuoteLiteral or as a prepared parameter.
Things that catch people out:
QuoteLiteralonly doubles single quotes, which is the SQL standard that SQLite and PostgreSQL follow. MySQL also treats a backslash as an escape by default, so on MySQL quote with the connection's ownc.Quote, which escapes backslashes as well.MigrateUpcompares version numbers, not a list of applied names. A migration numbered below the highest applied one, for example one merged from an older branch, is skipped without a word. Always give a new migration the next free number.- MySQL commits every
CREATE,ALTERandDROPon its own, so on MySQL a migration that fails halfway can leave the first statements applied. SQLite and PostgreSQL roll the whole migration back. - Migration SQL is split into statements at each
;outside single quotes. A PostgreSQL function body in$$ ... $$or a trigger with several statements is cut apart; run those withc.Execinstead.
Writing code against Connection
Give your data functions a dyn db.Connection parameter instead of a SQLite or PostgreSQL database. They then work with every SQL engine, and tests can pass an in-memory SQLite database while production passes PostgreSQL. db.Run runs one statement and returns true on success; c.Error() says what went wrong; c.LastInsertId() gives the id of the row just inserted.
import db
import db.sqlite
func setup(c: dyn db.Connection): bool:
ret db.Run(c, "CREATE TABLE notes (id INTEGER PRIMARY KEY, author TEXT NOT NULL, body TEXT NOT NULL)")
end
func add_note(c: dyn db.Connection, author: str, body: str): i64:
sql := "INSERT INTO notes (author, body) VALUES (" + db.QuoteLiteral(author) + ", " + db.QuoteLiteral(body) + ")"
if not db.Run(c, sql):
println "insert failed:", c.Error()
ret 0
end
ret c.LastInsertId()
end
func print_notes(c: dyn db.Connection, author: str):
rows := c.Query("SELECT id, body FROM notes WHERE author = " + db.QuoteLiteral(author) + " ORDER BY id")
until rows.Next():
println "#" + rows.Int(0), rows.Text(1)
end
_ := rows.Close()
end
func main:
c := sqlite.Conn(sqlite.Open(":memory:"))
if not setup(c):
println "setup failed:", c.Error()
ret 1
end
add_note(c, "Sara", "buy milk")
add_note(c, "Omid", "call the bank")
add_note(c, "Sara", "it's Reza's birthday")
add_note(c, "x' OR '1'='1", "trying to break in")
print_notes(c, "Sara")
println "notes by Omid:", db.QueryInt(c, "SELECT COUNT(*) FROM notes WHERE author = 'Omid'")
println "all notes:", db.QueryInt(c, "SELECT COUNT(*) FROM notes")
_ := c.Close()
ret 0
endEvery value that came from a user goes through QuoteLiteral, which wraps it in quotes and doubles any quote inside. That is why the apostrophe in Reza's is stored correctly, and why the author name x' OR '1'='1 is stored as an odd name instead of changing the meaning of the query. Never build SQL by gluing raw input into it.
A query returns dyn db.Rows. Next moves to the next row and returns false at the end, so until rows.Next(): visits every row; read columns by position with Int, Text, Float and IsNull, and always Close the rows. For a single number or string, QueryInt and QueryText do all of that in one call.
Transactions: all or nothing
When several statements must succeed or fail together, run them inside a transaction. c.Begin() returns a dyn db.Tx with its own Exec and Query; Commit makes the changes permanent and Rollback throws them all away. The transfer below takes money from one account, gives it to the other, then checks the result and rolls back if the first account went below zero.
import db
import db.sqlite
func transfer(c: dyn db.Connection, from_id: int, to_id: int, amount: int): str:
tx := c.Begin()
if not tx.Ok():
ret "could not start: " + c.Error()
end
_ := tx.Exec("UPDATE accounts SET balance = balance - " + amount + " WHERE id = " + from_id)
_ := tx.Exec("UPDATE accounts SET balance = balance + " + amount + " WHERE id = " + to_id)
rows := tx.Query("SELECT balance FROM accounts WHERE id = " + from_id)
mut left := 0
if rows.Next():
left = rows.Int(0)
end
_ := rows.Close()
if left < 0:
_ := tx.Rollback()
ret "refused, only " + (left + amount) + " left"
end
_ := tx.Commit()
ret "moved " + amount
end
func balances(c: dyn db.Connection): str:
ret db.QueryText(c, "SELECT group_concat(name || '=' || balance, ' ') FROM accounts")
end
func main:
c := sqlite.Conn(sqlite.Open(":memory:"))
mut setup := Vector {} as Vector<str>
defer setup.Free()
setup.Push("CREATE TABLE accounts (id INTEGER PRIMARY KEY, name TEXT, balance INTEGER)")
setup.Push("INSERT INTO accounts VALUES (1, 'ali', 100), (2, 'sara', 50)")
println db.InTx(c, setup)
println transfer(c, 1, 2, 30), "|", balances(c)
println transfer(c, 1, 2, 500), "|", balances(c)
println transfer(c, 2, 1, 80), "|", balances(c)
_ := c.Close()
endInside the transaction the queries already see the uncommitted changes, which is what makes the check possible. Other connections do not see them until Commit. When you have a fixed list of statements and no checks in between, db.InTx runs them in one transaction and returns false, with nothing applied, if any of them fails.
Migrations at start up
Call MigrateUp every time the program starts. It records each applied version in a table of its own, so the first run creates the schema, later runs do nothing, and a release that adds migration 3 applies only that one. Existing data stays where it is.
import db
import db.sqlite
func migrations(): db.Migrator:
mut m := db.NewMigrator()
db.AddMigration(m, 1, "create users", "CREATE TABLE users (id INTEGER PRIMARY KEY, email TEXT NOT NULL UNIQUE)", "DROP TABLE users")
db.AddMigration(m, 2, "add names", "ALTER TABLE users ADD COLUMN name TEXT DEFAULT ''", "ALTER TABLE users DROP COLUMN name")
ret m
end
func start(c: dyn db.Connection):
mut m := migrations()
before := db.SchemaVersion(m, c)
applied := db.MigrateUp(m, c)
println "schema", before, "->", db.SchemaVersion(m, c), "(" + applied + " applied)"
db.FreeMigrator(m)
end
func main:
c := sqlite.Conn(sqlite.Open(":memory:"))
start(c)
println db.Run(c, "INSERT INTO users (email, name) VALUES ('a@x.ir', 'Ali')")
start(c)
mut m := migrations()
db.AddMigration(m, 3, "add city", "ALTER TABLE users ADD COLUMN city TEXT", "ALTER TABLE users DROP COLUMN city")
println "pending:", db.PendingCount(m, c)
println "applied:", db.MigrateUp(m, c), "now at", db.SchemaVersion(m, c)
println db.QueryText(c, "SELECT name FROM users WHERE email = 'a@x.ir'")
db.FreeMigrator(m)
_ := c.Close()
endNever change a migration that has already run somewhere: add a new one instead. Each migration runs in its own transaction, so on SQLite and PostgreSQL a failing one leaves the schema at the previous version. Rollback undoes the last migration with its down SQL and MigrateTo moves to any version, which is useful in development but rarely in production.
Choosing the engine at run time
Because the rest of the program only sees dyn db.Connection, the engine can be picked from a setting. Here the APP_DB environment variable chooses PostgreSQL, MySQL or a SQLite file, and everything after connect is the same for all three.
import db
import db.sqlite
import db.postgres
import db.mysql
import env
func connect(): dyn db.Connection:
engine := env.GetOr("APP_DB", "sqlite")
if engine == "postgres":
ret postgres.Conn(postgres.Open("host=127.0.0.1 port=5432 user=app password=secret dbname=app"))
else engine == "mysql":
ret mysql.Conn(mysql.Open("127.0.0.1", "app", "secret", "app", 3306))
end
ret sqlite.Conn(sqlite.Open("app.db"))
end
func main:
c := connect()
if not c.Ok():
println "cannot connect to", c.Kind() + ":", c.Error()
ret 1
end
println "connected to", c.Kind()
_ := db.Run(c, "CREATE TABLE IF NOT EXISTS visits (page VARCHAR(100))")
_ := db.Run(c, "INSERT INTO visits (page) VALUES ('home')")
println "visits so far:", db.QueryInt(c, "SELECT COUNT(*) FROM visits")
_ := c.Close()
ret 0
endWithout APP_DB it uses SQLite and needs nothing else; each run adds a visit, so the count grows by one each time. To try PostgreSQL, start a server with Docker and run the program with APP_DB=postgres:
docker run -d -p 5432:5432 -e POSTGRES_USER=app -e POSTGRES_PASSWORD=secret -e POSTGRES_DB=app postgres:17
APP_DB=postgres salam run db-guide-engines.salam
Stick to SQL that all your engines understand, or keep the engine specific parts in one function. Placeholders and quoting differ between engines, which is one more reason to let QuoteLiteral or c.Quote do the quoting.
| Job | Use |
|---|---|
| One statement, did it work? | db.Run |
| A single value | QueryInt, QueryInt64, QueryFloat, QueryText |
| Many rows | c.Query, then Next and Close |
| Several statements that belong together | c.Begin, Commit, Rollback, or db.InTx |
| Schema changes | NewMigrator, AddMigration, MigrateUp |
| Several connections for threads | NewPool, Acquire, Release |
Types
Migration
struct Migration| Name | Type | Description |
|---|---|---|
version | int = 0 | The version number; migrations are applied in that order. |
name | str = "" | The name. |
up | str = "" | The SQL that applies it; separate several statements with ;. |
down | str = "" | The SQL that undoes it. |
import db
import db.sqlite
func main:
c := sqlite.Conn(sqlite.Open(":memory:"))
mut m := db.NewMigrator()
db.AddMigration(m, 1, "users", "CREATE TABLE users (id INTEGER PRIMARY KEY, email TEXT NOT NULL)", "DROP TABLE users")
db.AddMigration(m, 2, "posts", "CREATE TABLE posts (id INTEGER PRIMARY KEY, title TEXT); INSERT INTO posts (title) VALUES ('hello')", "DROP TABLE posts")
db.AddMigration(m, 3, "tags", "ALTER TABLE posts ADD COLUMN tag TEXT", "ALTER TABLE posts DROP COLUMN tag")
println db.MigrationCount(m), db.SchemaVersion(m, c), db.PendingCount(m, c)
println db.MigrateTo(m, c, 2)
println db.SchemaVersion(m, c), db.IsApplied(m, c, 2), db.IsApplied(m, c, 3)
println db.MigrateUp(m, c), db.SchemaVersion(m, c)
println db.MigrateUp(m, c)
println db.Rollback(m, c), db.SchemaVersion(m, c)
println db.MigrateTo(m, c, 0), db.SchemaVersion(m, c)
println db.QueryInt(c, "SELECT COUNT(*) FROM " + db.MIGRATION_TABLE)
db.FreeMigrator(m)
mut m2 := db.NewMigratorIn("app_versions")
db.AddMigration(m2, 1, "t", "CREATE TABLE t (x INTEGER)", "DROP TABLE t")
println db.EnsureTable(m2, c), db.MigrateUp(m2, c), m2.table
db.FreeMigrator(m2)
parts := db.SplitStatements("INSERT INTO t VALUES (1); SELECT ';' ; ")
println parts.Len(), parts.Get(1)
endMigrator
struct Migrator| Name | Type | Description |
|---|---|---|
items | Vector | The migrations. |
table | str = "" | The name of the version table. |
The example for this item is shown with the ones above: db-Migrate.salam
Rows
interface Rowsnext, text, int, close and the like.import db
import db.sqlite
func report(c: dyn db.Connection):
println c.Kind(), c.Ok(), c.Ping()
_ := c.Exec("CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT, score REAL)")
_ := c.Exec("INSERT INTO users (name, score) VALUES (" + c.Quote("O'Brien") + ", 7.5)")
_ := c.Exec("INSERT INTO users (name, score) VALUES ('Maryam', 9)")
println c.LastInsertId(), c.Changes()
rows := c.Query("SELECT id, name, score FROM users ORDER BY id")
println rows.Ok(), rows.ColumnCount(), rows.ColumnName(1)
until rows.Next():
println rows.Int(0), rows.Text(1), rows.Float(2), rows.Int64(0), rows.IsNull(2), rows.ColumnType(1)
end
_ := rows.Close()
tx := c.Begin()
println tx.Ok()
_ := tx.Exec("UPDATE users SET score = 0")
txrows := tx.Query("SELECT SUM(score) FROM users")
_ := txrows.Next()
println txrows.Float(0)
_ := txrows.Close()
_ := tx.Rollback()
tx2 := c.Begin()
_ := tx2.Exec("DELETE FROM users WHERE id = 1")
_ := tx2.Commit()
println db.QueryInt(c, "SELECT COUNT(*) FROM users"), db.QueryInt64(c, "SELECT MAX(id) FROM users")
println db.QueryFloat(c, "SELECT score FROM users"), db.QueryText(c, "SELECT name FROM users")
println db.CountRows(c, "SELECT * FROM users")
println db.Run(c, "INSERT INTO users (name) VALUES ('Ali')"), db.Run(c, "INSERT INTO nowhere VALUES (1)")
println c.Error()
mut stmts := Vector {} as Vector<str>
stmts.Push("INSERT INTO users (name) VALUES ('Sara')")
stmts.Push("INSERT INTO users (name) VALUES ('Reza')")
println db.RunAll(c, stmts)
stmts.Push("INSERT INTO nowhere VALUES (1)")
println db.InTx(c, stmts), db.QueryInt(c, "SELECT COUNT(*) FROM users")
println db.QuoteLiteral("it's")
_ := c.Close()
end
func main:
report(sqlite.Conn(sqlite.Open(":memory:")))
println db.SQLITE, db.MYSQL, db.POSTGRES
println db.TYPE_NULL, db.TYPE_INT, db.TYPE_FLOAT, db.TYPE_TEXT, db.TYPE_BLOB
endTx
interface Txexec, query, commit and rollback.The example for this item is shown with the ones above: db-Connection.salam
Connection
interface ConnectionConnect of whichever engine.The example for this item is shown with the ones above: db-Connection.salam
Pool
struct Pool| Name | Type | Description |
|---|---|---|
conns | Vector | The connections. |
taken | Vector | Which connections are taken. |
limit | int = 0 | The greatest number of connections. |
import db
import db.sqlite
func main:
mut pool := db.NewPool(2)
repeat 3:
println db.Add(pool, sqlite.Conn(sqlite.Open(":memory:")))
end
println db.Size(pool), db.Idle(pool), db.InUse(pool)
one := db.Acquire(pool)
two := db.Acquire(pool)
three := db.Acquire(pool)
println db.Leased(one), db.Leased(three), three == db.NO_LEASE
println db.Idle(pool), db.InUse(pool)
c := db.Conn(pool, one)
println db.QueryInt(c, "SELECT 6 * 7")
db.Release(pool, one)
db.Release(pool, two)
println db.Idle(pool), db.DropDead(pool)
db.CloseAll(pool)
db.Free(pool)
endConstants and variables
MIGRATION_TABLE
const MIGRATION_TABLE: str = "schema_migrations"The example for this item is shown with the ones above: db-Migrate.salam
SQLITE
const SQLITE: str = "sqlite"The example for this item is shown with the ones above: db-Connection.salam
MYSQL
const MYSQL: str = "mysql"The example for this item is shown with the ones above: db-Connection.salam
POSTGRES
const POSTGRES: str = "postgres"The example for this item is shown with the ones above: db-Connection.salam
ELASTICSEARCH
const ELASTICSEARCH: str = "elasticsearch"The example for this item is shown with the ones above: db-Connection.salam
OPENSEARCH
const OPENSEARCH: str = "opensearch"The example for this item is shown with the ones above: db-Connection.salam
CLICKHOUSE
const CLICKHOUSE: str = "clickhouse"The example for this item is shown with the ones above: db-Connection.salam
TYPE_NULL
const TYPE_NULL: i32 = 0The example for this item is shown with the ones above: db-Connection.salam
TYPE_INT
const TYPE_INT: i32 = 1The example for this item is shown with the ones above: db-Connection.salam
TYPE_FLOAT
const TYPE_FLOAT: i32 = 2The example for this item is shown with the ones above: db-Connection.salam
TYPE_TEXT
const TYPE_TEXT: i32 = 3The example for this item is shown with the ones above: db-Connection.salam
TYPE_BLOB
const TYPE_BLOB: i32 = 4The example for this item is shown with the ones above: db-Connection.salam
NO_LEASE
const NO_LEASE: i32 = -1Acquire returns when no connection is free.The example for this item is shown with the ones above: db-Pool.salam
Functions
NewMigrator
func NewMigrator(): MigratorThe example for this item is shown with the ones above: db-Migrate.salam
NewMigratorIn
func NewMigratorIn(table: str): MigratorThe example for this item is shown with the ones above: db-Migrate.salam
FreeMigrator
func FreeMigrator(m &: Migrator)The example for this item is shown with the ones above: db-Migrate.salam
AddMigration
func AddMigration(m &: Migrator, version: int, name: str, up: str, down: str)The example for this item is shown with the ones above: db-Migrate.salam
MigrationCount
func MigrationCount(m: Migrator): intThe example for this item is shown with the ones above: db-Migrate.salam
SplitStatements
func SplitStatements(sql: str): Vector<str>;. A ; inside a single quoted string is not a separator, but one inside a double quoted name, a comment or a $$ block is.The example for this item is shown with the ones above: db-Migrate.salam
EnsureTable
func EnsureTable(m: Migrator, c: dyn Connection): intThe example for this item is shown with the ones above: db-Migrate.salam
SchemaVersion
func SchemaVersion(m: Migrator, c: dyn Connection): intThe example for this item is shown with the ones above: db-Migrate.salam
IsApplied
func IsApplied(m: Migrator, c: dyn Connection, version: int): boolThe example for this item is shown with the ones above: db-Migrate.salam
PendingCount
func PendingCount(m: Migrator, c: dyn Connection): intThe example for this item is shown with the ones above: db-Migrate.salam
MigrateUp
func MigrateUp(m: Migrator, c: dyn Connection): intThe example for this item is shown with the ones above: db-Migrate.salam
Rollback
func Rollback(m: Migrator, c: dyn Connection): boolThe example for this item is shown with the ones above: db-Connection.salam
MigrateTo
func MigrateTo(m: Migrator, c: dyn Connection, target: int): intThe example for this item is shown with the ones above: db-Migrate.salam
QueryInt
func QueryInt(c: dyn Connection, sql: str): intCOUNT and the like.The example for this item is shown with the ones above: db-Connection.salam
QueryInt64
func QueryInt64(c: dyn Connection, sql: str): i64The example for this item is shown with the ones above: db-Connection.salam
QueryFloat
func QueryFloat(c: dyn Connection, sql: str): f64The example for this item is shown with the ones above: db-Connection.salam
QueryText
func QueryText(c: dyn Connection, sql: str): strThe example for this item is shown with the ones above: db-Connection.salam
CountRows
func CountRows(c: dyn Connection, sql: str): intThe example for this item is shown with the ones above: db-Connection.salam
Run
func Run(c: dyn Connection, sql: str): boolThe example for this item is shown with the ones above: db-Connection.salam
RunAll
func RunAll(c: dyn Connection, stmts: Vector<str>): intThe example for this item is shown with the ones above: db-Connection.salam
InTx
func InTx(c: dyn Connection, stmts: Vector<str>): boolThe example for this item is shown with the ones above: db-Connection.salam
QuoteLiteral
func QuoteLiteral(s: str): str' inside doubled. Backslashes are left alone, so on MySQL use the connection's Quote.The example for this item is shown with the ones above: db-Connection.salam
NewPool
func NewPool(limit: int): PoolThe example for this item is shown with the ones above: db-Pool.salam
Add
func Add(p &: Pool, c: dyn Connection): boolThe example for this item is shown with the ones above: db-Connection.salam
Acquire
func Acquire(p &: Pool): intThe example for this item is shown with the ones above: db-Pool.salam
Leased
func Leased(lease: int): boolThe example for this item is shown with the ones above: db-Pool.salam
Conn
func Conn(p: Pool, lease: int): dyn ConnectionThe example for this item is shown with the ones above: db-Pool.salam
Release
func Release(p &: Pool, lease: int)The example for this item is shown with the ones above: db-Pool.salam
Size
func Size(p: Pool): intThe example for this item is shown with the ones above: db-Pool.salam
Idle
func Idle(p: Pool): intThe example for this item is shown with the ones above: db-Pool.salam
InUse
func InUse(p: Pool): intThe example for this item is shown with the ones above: db-Pool.salam
DropDead
func DropDead(p &: Pool): intThe example for this item is shown with the ones above: db-Pool.salam
CloseAll
func CloseAll(p &: Pool)The example for this item is shown with the ones above: db-Pool.salam
Free
func Free(p &: Pool)The example for this item is shown with the ones above: db-Migrate.salam