Standard library · Database

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:

  • QuoteLiteral only 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 own c.Quote, which escapes backslashes as well.
  • MigrateUp compares 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, ALTER and DROP on 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 with c.Exec instead.

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.

db-guide-store.salam
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
end
Output#1 buy milk #3 it's Reza's birthday notes by Omid: 1 all notes: 4

Every 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.

db-guide-transfer.salam
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()
end
Outputtrue moved 30 | ali=70 sara=80 refused, only 70 left | ali=70 sara=80 moved 80 | ali=150 sara=0

Inside 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.

db-guide-startup.salam
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()
end
Outputschema 0 -> 2 (2 applied) true schema 2 -> 2 (0 applied) pending: 1 applied: 1 now at 3 Ali

Never 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.

db-guide-engines.salam
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
end

Without 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.

JobUse
One statement, did it work?db.Run
A single valueQueryInt, QueryInt64, QueryFloat, QueryText
Many rowsc.Query, then Next and Close
Several statements that belong togetherc.Begin, Commit, Rollback, or db.InTx
Schema changesNewMigrator, AddMigration, MigrateUp
Several connections for threadsNewPool, Acquire, Release

Types

Migration

struct Migration
One migration: one numbered change to the database schema.
NameTypeDescription
versionint = 0The version number; migrations are applied in that order.
namestr = ""The name.
upstr = ""The SQL that applies it; separate several statements with ;.
downstr = ""The SQL that undoes it.
db-Migrate.salam
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)
end
Output3 0 3 2 2 true false 1 3 0 true 2 2 0 0 0 1 app_versions 2 SELECT ';'

Migrator

struct Migrator
A set of migrations.
NameTypeDescription
itemsVectorThe migrations.
tablestr = ""The name of the version table.

The example for this item is shown with the ones above: db-Migrate.salam

Rows

interface Rows
The interface of the rows of a query; its methods are next, text, int, close and the like.
db-Connection.salam
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
end
Outputsqlite true true 2 1 true 3 name 1 O'Brien 7.5 1 false 3 2 Maryam 9 2 false 3 true 0 1 2 9 Maryam 1 true false no such table: nowhere 2 false 4 'it''s' sqlite mysql postgres 0 1 2 3 4

Tx

interface Tx
The transaction interface; its methods are exec, query, commit and rollback.

The example for this item is shown with the ones above: db-Connection.salam

Connection

interface Connection
The interface shared by every engine. Code written against it runs unchanged on SQLite, MySQL and PostgreSQL; get one from the Connect of whichever engine.

The example for this item is shown with the ones above: db-Connection.salam

Pool

struct Pool
A pool of connections, for programs that need several at once.
NameTypeDescription
connsVectorThe connections.
takenVectorWhich connections are taken.
limitint = 0The greatest number of connections.
db-Pool.salam
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)
end
Outputtrue true false 2 2 0 true false true 0 2 42 2 0

Constants and variables

MIGRATION_TABLE

const MIGRATION_TABLE: str = "schema_migrations"
The default name of the table the applied versions are recorded in.

The example for this item is shown with the ones above: db-Migrate.salam

SQLITE

const SQLITE: str = "sqlite"
The name of the SQLite engine.

The example for this item is shown with the ones above: db-Connection.salam

MYSQL

const MYSQL: str = "mysql"
The name of the MySQL engine.

The example for this item is shown with the ones above: db-Connection.salam

POSTGRES

const POSTGRES: str = "postgres"
The name of the PostgreSQL engine.

The example for this item is shown with the ones above: db-Connection.salam

ELASTICSEARCH

const ELASTICSEARCH: str = "elasticsearch"
The name of the Elasticsearch engine.

The example for this item is shown with the ones above: db-Connection.salam

OPENSEARCH

const OPENSEARCH: str = "opensearch"
The name of the OpenSearch engine.

The example for this item is shown with the ones above: db-Connection.salam

CLICKHOUSE

const CLICKHOUSE: str = "clickhouse"
The name of the ClickHouse engine.

The example for this item is shown with the ones above: db-Connection.salam

TYPE_NULL

const TYPE_NULL: i32 = 0
Column type: null.

The example for this item is shown with the ones above: db-Connection.salam

TYPE_INT

const TYPE_INT: i32 = 1
Column type: integer.

The example for this item is shown with the ones above: db-Connection.salam

TYPE_FLOAT

const TYPE_FLOAT: i32 = 2
Column type: float.

The example for this item is shown with the ones above: db-Connection.salam

TYPE_TEXT

const TYPE_TEXT: i32 = 3
Column type: text.

The example for this item is shown with the ones above: db-Connection.salam

TYPE_BLOB

const TYPE_BLOB: i32 = 4
Column type: binary.

The example for this item is shown with the ones above: db-Connection.salam

NO_LEASE

const NO_LEASE: i32 = -1
The number Acquire returns when no connection is free.

The example for this item is shown with the ones above: db-Pool.salam

Functions

NewMigrator

func NewMigrator(): Migrator
A migrator with the default table.

The example for this item is shown with the ones above: db-Migrate.salam

NewMigratorIn

func NewMigratorIn(table: str): Migrator
A migrator with a version table of another name.

The example for this item is shown with the ones above: db-Migrate.salam

FreeMigrator

func FreeMigrator(m &: Migrator)
Frees the memory.

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)
Adds a migration with a number, a name and the up and down SQL; the order you add them in does not matter.

The example for this item is shown with the ones above: db-Migrate.salam

MigrationCount

func MigrationCount(m: Migrator): int
The number of migrations.

The example for this item is shown with the ones above: db-Migrate.salam

SplitStatements

func SplitStatements(sql: str): Vector<str>
Splits SQL text into statements at each ;. 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): int
Makes the version table when it is not there.

The example for this item is shown with the ones above: db-Migrate.salam

SchemaVersion

func SchemaVersion(m: Migrator, c: dyn Connection): int
The highest applied version; zero when there is none.

The example for this item is shown with the ones above: db-Migrate.salam

IsApplied

func IsApplied(m: Migrator, c: dyn Connection, version: int): bool
This version has been applied.

The example for this item is shown with the ones above: db-Migrate.salam

PendingCount

func PendingCount(m: Migrator, c: dyn Connection): int
The number of migrations not applied yet.

The example for this item is shown with the ones above: db-Migrate.salam

MigrateUp

func MigrateUp(m: Migrator, c: dyn Connection): int
Applies, in order, every migration whose version is above the current schema version, each in its own transaction, and returns how many ran. It stops at the first failure.

The example for this item is shown with the ones above: db-Migrate.salam

Rollback

func Rollback(m: Migrator, c: dyn Connection): bool
Undoes the last migration.

The example for this item is shown with the ones above: db-Connection.salam

MigrateTo

func MigrateTo(m: Migrator, c: dyn Connection, target: int): int
Goes up or down until the version reaches the given number and returns how many steps; zero means undo them all.

The example for this item is shown with the ones above: db-Migrate.salam

QueryInt

func QueryInt(c: dyn Connection, sql: str): int
The first column of the first row as an integer; for COUNT 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): i64
The same, 64 bit.

The example for this item is shown with the ones above: db-Connection.salam

QueryFloat

func QueryFloat(c: dyn Connection, sql: str): f64
The same, as a float.

The example for this item is shown with the ones above: db-Connection.salam

QueryText

func QueryText(c: dyn Connection, sql: str): str
The same, as text.

The example for this item is shown with the ones above: db-Connection.salam

CountRows

func CountRows(c: dyn Connection, sql: str): int
The number of rows in the result of a query.

The example for this item is shown with the ones above: db-Connection.salam

Run

func Run(c: dyn Connection, sql: str): bool
Runs one statement; true when it succeeded.

The example for this item is shown with the ones above: db-Connection.salam

RunAll

func RunAll(c: dyn Connection, stmts: Vector<str>): int
Runs the statements in order and returns how many succeeded.

The example for this item is shown with the ones above: db-Connection.salam

InTx

func InTx(c: dyn Connection, stmts: Vector<str>): bool
Runs every statement in one transaction; when one fails, none of them is applied.

The example for this item is shown with the ones above: db-Connection.salam

QuoteLiteral

func QuoteLiteral(s: str): str
Returns the string as an SQL value: wrapped in single quotes, with each ' 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): Pool
An empty pool with a greatest number of connections.

The example for this item is shown with the ones above: db-Pool.salam

Add

func Add(p &: Pool, c: dyn Connection): bool
Adds a connection to the pool; false when the pool is full.

The example for this item is shown with the ones above: db-Connection.salam

Acquire

func Acquire(p &: Pool): int
Takes a free connection and returns the lease number.

The example for this item is shown with the ones above: db-Pool.salam

Leased

func Leased(lease: int): bool
This lease number is valid.

The example for this item is shown with the ones above: db-Pool.salam

Conn

func Conn(p: Pool, lease: int): dyn Connection
The connection of a lease.

The example for this item is shown with the ones above: db-Pool.salam

Release

func Release(p &: Pool, lease: int)
Returns the connection to the pool.

The example for this item is shown with the ones above: db-Pool.salam

Size

func Size(p: Pool): int
The number of connections.

The example for this item is shown with the ones above: db-Pool.salam

Idle

func Idle(p: Pool): int
The number of free connections.

The example for this item is shown with the ones above: db-Pool.salam

InUse

func InUse(p: Pool): int
The number of connections taken.

The example for this item is shown with the ones above: db-Pool.salam

DropDead

func DropDead(p &: Pool): int
Closes and removes the free connections that no longer answer.

The example for this item is shown with the ones above: db-Pool.salam

CloseAll

func CloseAll(p &: Pool)
Closes every connection.

The example for this item is shown with the ones above: db-Pool.salam

Free

func Free(p &: Pool)
Frees the memory of the pool.

The example for this item is shown with the ones above: db-Migrate.salam