Salam
Online editor

Standard library · Database

db

One interface shared by the database engines, schema migrations and a connection pool.

import db
On this page
  1. Types
  2. Constants and variables
  3. Functions

Every database engine has its own package: db/sqlite, db/mysql, db/postgres, for key and value db/redis, and for search db/meilisearch and db/elasticsearch, which also answers 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.

Migrations move the database schema along with the code: every change has a number and MigrateUp applies only the ones not applied yet. Never paste a value a user typed straight into SQL; send it through QuoteLiteral or as a prepared parameter.

Types

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

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

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

Constants and variables

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

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

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

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

Functions

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

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, quoted and with ' escaped.

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

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; a ; inside a string is not a separator.

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 every migration not applied yet, in order, and returns how many; each migration in its own transaction.

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