Standard library · Database

db/sqlite

An SQLite database in one file or in memory.

import db.sqlite

SQLite is a database that fits in one file and needs no server; for desktop programs, tools and a great many websites it is enough. The program links against the system's sqlite3 library, so that library has to be installed where it runs (it is on almost every Linux and macOS system already).

There are two ways to work: the functions of this package, such as Query and Next, which reach everything SQLite offers; or the database and row methods and Conn, which is the shared interface of db. Send user values through Prepare and the Bind... functions, not by pasting them into the SQL text.

This page starts with a tutorial and a few guides for common jobs; the full reference of every function and type comes after them.

Tutorial: a lending library in twelve steps

The steps below build the database of a small lending library: books, members and loans. Each step is a complete program, and the output under it is what it printed with SQLite 3.46. Run the steps in order in one folder, because each one opens the same library.db file and uses what the earlier steps left in it. To start over, delete library.db and the library.db-wal and library.db-shm files next to it.

Nothing needs to be started first. If a program fails to link with a message about sqlite3, install the library: sudo apt install libsqlite3-dev on Debian and Ubuntu, sudo dnf install sqlite-devel on Fedora, or brew install sqlite on macOS.

1. Open the database and set options

Open opens a file, creating it when it is not there. :memory: (or an empty path) gives a private database in memory that disappears when it is closed, which is handy for tests and caches. SQLite has no connection string: options are PRAGMA statements you run after opening. Three are worth setting in almost every program. journal_mode = WAL lets readers keep reading while a writer works, and it is stored in the file, so setting it once is enough. foreign_keys = ON makes SQLite enforce REFERENCES; it is off by default and belongs to the connection, so every program that opens the file sets it again. busy_timeout makes a connection wait that many milliseconds for a lock instead of failing at once.

db-sqlite-open.salam

import db.sqlite
import str

func main:
    if not sqlite.Available():
        println "SQLite is not installed"
        ret 1
    end
    println "SQLite 3:", str.StartsWith(sqlite.Version(), "3.")

    d := sqlite.Open("library.db")
    defer sqlite.Close(d)
    if not sqlite.Ok(d) or sqlite.Exec(d, "SELECT 1") != 0:
        println "could not open:", sqlite.Error(d)
        ret 1
    end

    println "journal:", sqlite.QueryText(d, "PRAGMA journal_mode = WAL")
    sqlite.Exec(d, "PRAGMA foreign_keys = ON")
    println "foreign keys:", sqlite.QueryInt(d, "PRAGMA foreign_keys")
    println "busy timeout:", sqlite.QueryInt(d, "PRAGMA busy_timeout = 5000")

    bad := sqlite.Open("/no/such/folder/library.db")
    println "bad path, handle:", sqlite.Ok(bad), "| first statement:", sqlite.Exec(bad, "SELECT 1"), sqlite.Error(bad)
    sqlite.Close(bad)
end

OutputSQLite 3: true journal: wal foreign keys: 1 busy timeout: 5000 bad path, handle: true | first statement: -1 unable to open database file

Look at the last line. SQLite hands back a handle even when the file cannot be opened, so Ok is true and the problem only shows when the first statement fails. That is why this step runs SELECT 1 before trusting the database.

2. Create the tables

Exec runs one or more statements separated by semicolons and returns 0 on success or -1 on failure, with the reason in Error. A triple quoted string keeps a long schema readable. IF NOT EXISTS makes the step safe to run again, and PRAGMA user_version is a free integer in the file header that is handy for a schema version (step 11 uses a migration table instead).

db-sqlite-schema.salam

import db.sqlite

func main:
    d := sqlite.Open("library.db")
    defer sqlite.Close(d)

    schema := """
    CREATE TABLE IF NOT EXISTS books (
        id INTEGER PRIMARY KEY,
        title TEXT NOT NULL,
        author TEXT NOT NULL,
        year INTEGER,
        copies INTEGER NOT NULL DEFAULT 1 CHECK (copies >= 0),
        rating REAL,
        note TEXT
    );
    CREATE TABLE IF NOT EXISTS members (
        id INTEGER PRIMARY KEY,
        name TEXT NOT NULL UNIQUE,
        email TEXT
    );
    CREATE TABLE IF NOT EXISTS loans (
        id INTEGER PRIMARY KEY,
        book INTEGER NOT NULL REFERENCES books (id),
        member INTEGER NOT NULL REFERENCES members (id),
        due TEXT NOT NULL,
        returned TEXT
    );
    CREATE INDEX IF NOT EXISTS books_author ON books (author);
    PRAGMA user_version = 1;
    """
    if sqlite.Exec(d, schema) != 0:
        println "schema failed:", sqlite.Error(d)
        ret 1
    end

    rows := sqlite.Query(d, "SELECT type, name FROM sqlite_master ORDER BY type, name")
    until sqlite.Next(rows):
        println sqlite.Text(rows, 0), sqlite.Text(rows, 1)
    end
    sqlite.Finish(rows)
    println "schema version:", sqlite.QueryInt(d, "PRAGMA user_version")
end

Outputindex books_author index sqlite_autoindex_members_1 table books table loans table members schema version: 1

SQLite types are loose: a column declared INTEGER will store text if you give it text. Constraints such as NOT NULL, UNIQUE and CHECK are what actually keep the data clean. An INTEGER PRIMARY KEY column is the row id, filled in for you on insert.

3. Insert rows

Prepare compiles a statement with ? places once; the Bind... functions fill the places (numbered from 1), Next runs it, and Reset readies it for the next row. Preparing once and binding many times is both faster and safer than building a new SQL string for each row. LastInsertId gives the id SQLite chose and Changes how many rows the last statement touched.

db-sqlite-insert.salam

import db.sqlite

struct Book:
    pub title: str = ""
    pub author: str = ""
    pub year: int = 0
    pub copies: int = 1
    pub rating: f64 = 0.0
end

func main:
    d := sqlite.Open("library.db")
    defer sqlite.Close(d)

    mut books := Vector {} as Vector<Book>
    defer books.free()
    books.push(Book { title = "Dune", author = "Frank Herbert", year = 1965, copies = 2, rating = 4.5 })
    books.push(Book { title = "Emma", author = "Jane Austen", year = 1815, rating = 4.1 })
    books.push(Book { title = "Persuasion", author = "Jane Austen", year = 1817, rating = 4.3 })
    books.push(Book { title = "Kindred", author = "Octavia Butler", year = 1979, copies = 3, rating = 4.6 })
    books.push(Book { title = "Neuromancer", author = "William Gibson", year = 1984, rating = 3.9 })

    st := sqlite.Prepare(d, "INSERT INTO books (title, author, year, copies, rating) VALUES (?, ?, ?, ?, ?)")
    defer sqlite.Finish(st)
    each b in books:
        sqlite.Reset(st)
        sqlite.BindText(st, 1, b.title)
        sqlite.BindText(st, 2, b.author)
        sqlite.BindInt(st, 3, b.year as i64)
        sqlite.BindInt(st, 4, b.copies as i64)
        sqlite.BindFloat(st, 5, b.rating)
        sqlite.Next(st)
        println "book", sqlite.LastInsertId(d), b.title
    end

    sqlite.Exec(d, "INSERT INTO members (name, email) VALUES ('Sara', 'sara@example.com'), ('Reza', NULL), ('Mina', 'mina@example.com')")
    println "members added:", sqlite.Changes(d)
    println "books:", sqlite.QueryInt(d, "SELECT COUNT(*) FROM books"), "copies:", sqlite.QueryInt(d, "SELECT SUM(copies) FROM books")
end

Outputbook 1 Dune book 2 Emma book 3 Persuasion book 4 Kindred book 5 Neuromancer members added: 3 books: 5 copies: 8

BindInt takes an i64, so an int field is cast with as i64. The statement is freed by Finish; the defer makes sure that happens however the function ends.

4. Read rows into your own structs

A query is a statement too: Next steps to the next row and returns false when there are none left, and Text, Int, Int64 and Float read the columns of the current row by position, starting at 0. Put the reading of one row in a small function and every query that selects the same columns can share it.

db-sqlite-structs.salam

import db.sqlite

struct Book:
    pub id: i64 = 0
    pub title: str = ""
    pub author: str = ""
    pub year: int = 0
    pub copies: int = 0
    pub rating: f64 = 0.0
end

func scanBook(rows: sqlite.Rows): Book:
    ret Book {
        id = sqlite.Int64(rows, 0),
        title = sqlite.Text(rows, 1),
        author = sqlite.Text(rows, 2),
        year = sqlite.Int(rows, 3),
        copies = sqlite.Int(rows, 4),
        rating = sqlite.Float(rows, 5)
    }
end

func booksBy(d: sqlite.Database, author: str): Vector<Book>:
    mut out := Vector {} as Vector<Book>
    st := sqlite.Prepare(d, "SELECT id, title, author, year, copies, rating FROM books WHERE author = ? ORDER BY year")
    defer sqlite.Finish(st)
    sqlite.BindText(st, 1, author)
    until sqlite.Next(st):
        out.push(scanBook(st))
    end
    ret out
end

func bookById(d: sqlite.Database, id: i64, found &: bool): Book:
    st := sqlite.Prepare(d, "SELECT id, title, author, year, copies, rating FROM books WHERE id = ?")
    defer sqlite.Finish(st)
    sqlite.BindInt(st, 1, id)
    found = sqlite.Next(st)
    if not found:
        ret Book {}
    end
    ret scanBook(st)
end

func main:
    d := sqlite.Open("library.db")
    defer sqlite.Close(d)

    austen := booksBy(d, "Jane Austen")
    defer austen.free()
    each b in austen:
        println b.id, b.title, b.year, b.rating
    end

    mut found := false
    k := bookById(d, 4, found)
    println found, k.title, "by", k.author + ",", k.copies, "copies"
    bookById(d, 99, found)
    println "book 99 found:", found
end

Output2 Emma 1815 4.1 3 Persuasion 1817 4.3 true Kindred by Octavia Butler, 3 copies book 99 found: false

Ids are i64; read them with Int64. Int is 32 bits and wraps on large values. When the lookup may find nothing, report that separately (here through the found parameter) instead of returning an empty struct that looks like a real book.

5. Parameters and SQL injection

A bound value is never parsed as SQL, so it cannot change what the statement does. Text pasted into the SQL string can. This step searches with what a user typed, then shows the same text pasted in: the attack string matches every book, and an ordinary name with an apostrophe breaks the statement.

db-sqlite-params.salam

import db.sqlite

func search(d: sqlite.Database, typed: str):
    st := sqlite.Prepare(d, "SELECT title FROM books WHERE title LIKE '%' || ? || '%' ORDER BY title")
    defer sqlite.Finish(st)
    sqlite.BindText(st, 1, typed)
    mut hits := 0
    until sqlite.Next(st):
        hits = hits + 1
    end
    println "search", d.Quote(typed) + ":", hits, "hits"
end

func main:
    d := sqlite.Open("library.db")
    defer sqlite.Close(d)

    search(d, "er")
    search(d, "x' OR '1'='1")

    pasted := "SELECT COUNT(*) FROM books WHERE title = 'x' OR '1'='1'"
    println "pasted into the SQL:", sqlite.QueryInt(d, pasted), "books"

    st := sqlite.Prepare(d, "INSERT INTO members (name, email) VALUES (?, ?)")
    sqlite.BindText(st, 1, "Liam O'Brien")
    sqlite.BindText(st, 2, "liam@example.com")
    sqlite.Next(st)
    sqlite.Finish(st)
    println "added member", sqlite.LastInsertId(d)

    name := "Liam O'Brien"
    if sqlite.Exec(d, "UPDATE members SET email = NULL WHERE name = '" + name + "'") != 0:
        println "pasted name:", sqlite.Error(d)
    end
    println "quoted:", sqlite.QueryInt(d, "SELECT COUNT(*) FROM members WHERE name = " + d.Quote(name))
end

Outputsearch 'er': 2 hits search 'x'' OR ''1''=''1': 0 hits pasted into the SQL: 5 books added member 4 pasted name: near "Brien": syntax error quoted: 1

Use a parameter for every value that comes from outside the program. The Quote method of the database (from the shared db interface) wraps text in quotes and doubles the quotes inside it; keep it for the rare place where a value cannot be a parameter, such as SQL you generate for another tool. Table and column names cannot be parameters at all, so pick them from a fixed list in your code.

6. NULL values

A missing value in SQL is NULL, which is not the same as an empty string or zero. BindNull stores one, and IsNull tells you a column holds one. Text returns "" for NULL and Int and Float return 0, so check IsNull first whenever empty and missing mean different things, or let SQL fill in a default with COALESCE.

db-sqlite-null.salam

import db.sqlite

func bindOptional(st: sqlite.Rows, idx: int, val: str):
    if val == "":
        sqlite.BindNull(st, idx)
    else:
        sqlite.BindText(st, idx, val)
    end
end

func setNote(d: sqlite.Database, id: i64, note: str):
    st := sqlite.Prepare(d, "UPDATE books SET note = ? WHERE id = ?")
    defer sqlite.Finish(st)
    bindOptional(st, 1, note)
    sqlite.BindInt(st, 2, id)
    sqlite.Next(st)
end

func main:
    d := sqlite.Open("library.db")
    defer sqlite.Close(d)

    setNote(d, 1, "signed by the author")
    setNote(d, 4, "")

    rows := sqlite.Query(d, "SELECT title, note FROM books WHERE id IN (1, 4)")
    until sqlite.Next(rows):
        if sqlite.IsNull(rows, 1):
            println sqlite.Text(rows, 0) + ": no note, Text gives \"" + sqlite.Text(rows, 1) + "\""
        else:
            println sqlite.Text(rows, 0) + ":", sqlite.Text(rows, 1)
        end
    end
    sqlite.Finish(rows)

    emails := sqlite.Query(d, "SELECT name, COALESCE(email, '(no email)') FROM members ORDER BY id")
    until sqlite.Next(emails):
        println sqlite.Text(emails, 0), sqlite.Text(emails, 1)
    end
    sqlite.Finish(emails)
    println "without email:", sqlite.QueryInt(d, "SELECT COUNT(*) FROM members WHERE email IS NULL")
end

OutputDune: signed by the author Kindred: no note, Text gives "" Sara sara@example.com Reza (no email) Mina mina@example.com Liam O'Brien liam@example.com without email: 1

7. Transactions

Lending a book is two writes: one copy fewer on the shelf, and a new row in loans. They must happen together or not at all. Begin starts a transaction, Commit makes every change in it permanent at once, and Rollback throws them all away. Each helper reports whether exactly one row changed, so a book with no copies left or an unknown member undoes the whole loan.

db-sqlite-tx.salam

import db.sqlite

func step(d: sqlite.Database, sql: str, a: i64, b: i64, due: str): bool:
    st := sqlite.Prepare(d, sql)
    sqlite.BindInt(st, 1, a)
    if b > 0:
        sqlite.BindInt(st, 2, b)
        sqlite.BindText(st, 3, due)
    end
    sqlite.Next(st)
    ret sqlite.Finish(st) == 0 and sqlite.Changes(d) == 1
end

func lend(d: sqlite.Database, book: i64, member: i64, today: str): bool:
    if sqlite.Begin(d) != 0:
        ret false
    end
    if not step(d, "UPDATE books SET copies = copies - 1 WHERE id = ? AND copies > 0", book, 0, ""):
        sqlite.Rollback(d)
        ret false
    end
    if not step(d, "INSERT INTO loans (book, member, due) VALUES (?, ?, date(?, '+14 days'))", book, member, today):
        sqlite.Rollback(d)
        ret false
    end
    ret sqlite.Commit(d) == 0
end

func main:
    d := sqlite.Open("library.db")
    defer sqlite.Close(d)
    sqlite.Exec(d, "PRAGMA foreign_keys = ON")
    today := "2026-10-17"

    println "Sara borrows Emma:", lend(d, 2, 1, today)
    println "Reza borrows Emma:", lend(d, 2, 2, today)
    println "Mina borrows Dune:", lend(d, 1, 3, today)
    println "Mina borrows Kindred:", lend(d, 4, 3, "2026-10-20")
    println "member 42 borrows Dune:", lend(d, 1, 42, today)

    println "copies of Dune:", sqlite.QueryInt(d, "SELECT copies FROM books WHERE id = 1")
    println "loans:", sqlite.QueryInt(d, "SELECT COUNT(*) FROM loans")
    println "Sara's book is due", sqlite.QueryText(d, "SELECT due FROM loans WHERE member = 1")
end

OutputSara borrows Emma: true Reza borrows Emma: false Mina borrows Dune: true Mina borrows Kindred: true member 42 borrows Dune: false copies of Dune: 1 loans: 3 Sara's book is due 2026-10-31

The due date is computed by SQLite's own date() function. Transactions are also the way to make bulk inserts fast: SQLite writes to disk once per transaction, so a thousand inserts inside one Begin and Commit take a fraction of the time of a thousand separate ones.

8. Update and delete

UPDATE and DELETE go through Exec or a prepared statement like any other SQL. Changes says how many rows the last statement touched, which is how the program learns that an UPDATE ... WHERE matched nothing. TotalChanges counts every row changed since the database was opened.

db-sqlite-update.salam

import db.sqlite

func giveBack(d: sqlite.Database, loan: i64, day: str): bool:
    sqlite.Begin(d)
    st := sqlite.Prepare(d, "UPDATE loans SET returned = ? WHERE id = ? AND returned IS NULL")
    sqlite.BindText(st, 1, day)
    sqlite.BindInt(st, 2, loan)
    sqlite.Next(st)
    sqlite.Finish(st)
    if sqlite.Changes(d) != 1:
        sqlite.Rollback(d)
        ret false
    end
    back := sqlite.Prepare(d, "UPDATE books SET copies = copies + 1 WHERE id = (SELECT book FROM loans WHERE id = ?)")
    sqlite.BindInt(back, 1, loan)
    sqlite.Next(back)
    sqlite.Finish(back)
    ret sqlite.Commit(d) == 0
end

func main:
    d := sqlite.Open("library.db")
    defer sqlite.Close(d)
    before := sqlite.TotalChanges(d)

    println "loan 1 returned:", giveBack(d, 1, "2026-10-25")
    println "loan 1 again:", giveBack(d, 1, "2026-10-26")
    println "copies of Emma:", sqlite.QueryInt(d, "SELECT copies FROM books WHERE id = 2")

    sqlite.Exec(d, "UPDATE books SET rating = rating + 0.1 WHERE author = 'Jane Austen'")
    println "re-rated:", sqlite.Changes(d)

    sqlite.Exec(d, "DELETE FROM members WHERE id NOT IN (SELECT member FROM loans)")
    println "members without loans removed:", sqlite.Changes(d)
    println "rows changed by this program:", sqlite.TotalChanges(d) - before
end

Outputloan 1 returned: true loan 1 again: false copies of Emma: 1 re-rated: 2 members without loans removed: 2 rows changed by this program: 6

Returning a loan twice fails the second time because of returned IS NULL in the WHERE clause: the condition does the checking, and Changes reports it.

9. Errors

Every function reports failure in its return value and keeps the reason in Error. A mistake in the SQL itself shows at once: Prepare returns a statement for which StmtOk is false. A broken constraint shows when the statement runs: Next returns false (just as it does at the end of the rows), and Finish or Reset returns -1. Check Finish to tell the two apart.

db-sqlite-errors.salam

import db.sqlite

func insert(d: sqlite.Database, sql: str, a: str, b: i64): bool:
    st := sqlite.Prepare(d, sql)
    if not sqlite.StmtOk(st):
        println "  prepare failed:", sqlite.Error(d)
        ret false
    end
    sqlite.BindText(st, 1, a)
    sqlite.BindInt(st, 2, b)
    done := not sqlite.Next(st)
    if sqlite.Finish(st) != 0:
        println "  failed:", sqlite.Error(d)
        ret false
    end
    ret done
end

func main:
    d := sqlite.Open("library.db")
    defer sqlite.Close(d)
    sqlite.Exec(d, "PRAGMA foreign_keys = ON")

    println "a second Sara:"
    insert(d, "INSERT INTO members (name, id) VALUES (?, ?)", "Sara", 50)
    println "a loan for book 99:"
    insert(d, "INSERT INTO loans (due, book, member) VALUES (?, ?, 1)", "2026-11-01", 99)
    println "negative copies:"
    insert(d, "INSERT INTO books (title, copies, author) VALUES (?, ?, 'Nobody')", "Ghost", -1)
    println "a typo in the SQL:"
    insert(d, "INSERT INTO bokks (title, year) VALUES (?, ?)", "Ghost", 2026)

    if sqlite.Exec(d, "DELETE FROM books WHERE id = 2") != 0:
        println "delete Emma:", sqlite.Error(d)
    end
    println "Emma is still there:", sqlite.QueryText(d, "SELECT title FROM books WHERE id = 2")
end

Outputa second Sara: failed: UNIQUE constraint failed: members.name a loan for book 99: failed: FOREIGN KEY constraint failed negative copies: failed: CHECK constraint failed: copies >= 0 a typo in the SQL: prepare failed: no such table: bokks delete Emma: FOREIGN KEY constraint failed Emma is still there: Emma

Read Error right after the call that failed: the next statement replaces the message. SQLite's messages are in English and name the constraint, which is enough to turn into a message for the user (UNIQUE constraint failed: members.name means the name is taken).

10. Reports with joins and grouping

Most of the work in a report is SQL. JOIN brings the related rows together, GROUP BY with COUNT, SUM and AVG summarizes them, and SQLite's JSON functions can build a JSON answer for a web API in one query, read back with QueryText.

db-sqlite-report.salam

import db.sqlite

func main:
    d := sqlite.Open("library.db")
    defer sqlite.Close(d)

    out := sqlite.Query(d, """
        SELECT m.name, b.title, l.due
        FROM loans l
        JOIN books b ON b.id = l.book
        JOIN members m ON m.id = l.member
        WHERE l.returned IS NULL
        ORDER BY l.due, m.name""")
    println "out on loan:"
    until sqlite.Next(out):
        println " ", sqlite.Text(out, 0), "has", sqlite.Text(out, 1), "until", sqlite.Text(out, 2)
    end
    sqlite.Finish(out)

    late := sqlite.Prepare(d, "SELECT COUNT(*) FROM loans WHERE returned IS NULL AND due < ?")
    sqlite.BindText(late, 1, "2026-11-01")
    sqlite.Next(late)
    println "late on 2026-11-01:", sqlite.Int(late, 0)
    sqlite.Finish(late)

    authors := sqlite.Query(d, """
        SELECT author, COUNT(*), SUM(copies), ROUND(AVG(rating), 2)
        FROM books GROUP BY author ORDER BY AVG(rating) DESC""")
    until sqlite.Next(authors):
        println sqlite.Text(authors, 0), "| books", sqlite.Int(authors, 1), "| copies", sqlite.Int(authors, 2), "| rating", sqlite.Float(authors, 3)
    end
    sqlite.Finish(authors)

    println sqlite.QueryText(d, "SELECT json_group_array(json_object('id', id, 'title', title)) FROM (SELECT id, title FROM books WHERE copies > 0 ORDER BY id LIMIT 2)")
end

Outputout on loan: Mina has Dune until 2026-10-31 Mina has Kindred until 2026-11-03 late on 2026-11-01: 1 Octavia Butler | books 1 | copies 2 | rating 4.6 Frank Herbert | books 1 | copies 1 | rating 4.5 Jane Austen | books 2 | copies 2 | rating 4.3 William Gibson | books 1 | copies 1 | rating 3.9 [{"id":1,"title":"Dune"},{"id":2,"title":"Emma"}]

Dates stored as YYYY-MM-DD text sort and compare correctly as plain strings, which is why due < ? finds the late loans.

11. The shared interface and migrations

Conn returns the database as a dyn db.Connection, the interface that the MySQL and PostgreSQL packages also implement. Code written against it, such as the helpers and the migrator in db, works with any of them. A migrator keeps numbered schema changes in order, records which ones ran in a schema_migrations table, and runs each one in its own transaction. db.InTx runs a list of statements as one transaction and rolls back on the first failure.

db-sqlite-migrate.salam

import db
import db.sqlite

func printCopies(c: dyn db.Connection):
    rows := c.Query("SELECT title, copies, shelf FROM books ORDER BY id LIMIT 3")
    until rows.Next():
        println rows.Text(0), rows.Int(1), rows.Text(2)
    end
    rows.Close()
end

func main:
    d := sqlite.Open("library.db")
    defer sqlite.Close(d)
    c := sqlite.Conn(d)
    println "engine:", c.Kind()

    mut m := db.NewMigrator()
    defer db.FreeMigrator(m)
    db.AddMigration(m, 1, "shelves", "ALTER TABLE books ADD COLUMN shelf TEXT NOT NULL DEFAULT 'A'", "ALTER TABLE books DROP COLUMN shelf")
    db.AddMigration(m, 2, "sci-fi shelf", "UPDATE books SET shelf = 'S' WHERE id IN (1, 4, 5)", "UPDATE books SET shelf = 'A'")

    println "pending:", db.PendingCount(m, c)
    println "applied:", db.MigrateUp(m, c)
    println "schema version:", db.SchemaVersion(m, c), "pending:", db.PendingCount(m, c)

    mut fix := Vector {} as Vector<str>
    defer fix.free()
    fix.push("UPDATE books SET copies = copies + 1 WHERE id = 5")
    fix.push("UPDATE books SET copies = -1 WHERE id = 4")
    println "both or neither:", db.InTx(c, fix)
    println "Neuromancer copies:", db.QueryInt(c, "SELECT copies FROM books WHERE id = 5")
    printCopies(c)
end

Outputengine: sqlite pending: 2 applied: 2 schema version: 2 pending: 0 both or neither: false Neuromancer copies: 1 Dune 1 S Emma 1 A Persuasion 1 A

The second statement of the fix breaks the CHECK constraint, so the first one is rolled back too and Neuromancer keeps its single copy. Run this step again and the migrator does nothing, because both versions are recorded.

12. Full text search and a backup

Two features that make SQLite more than a plain table store. An FTS5 virtual table indexes words for fast search with prefixes (dun*), column filters (author:butler), OR, ranking and highlighting; the search text is just another bound parameter. VACUUM INTO writes a clean, consistent copy of the open database to a new file, safe to run while the program is using it.

db-sqlite-fts.salam

import db.sqlite
import os

func main:
    d := sqlite.Open("library.db")
    defer sqlite.Close(d)

    sqlite.Exec(d, """
    DROP TABLE IF EXISTS book_search;
    CREATE VIRTUAL TABLE book_search USING fts5(title, author, note);
    INSERT INTO book_search (rowid, title, author, note)
        SELECT id, title, author, COALESCE(note, '') FROM books;
    """)

    st := sqlite.Prepare(d, "SELECT rowid, highlight(book_search, 0, '[', ']') FROM book_search WHERE book_search MATCH ? ORDER BY rank")
    defer sqlite.Finish(st)
    each q in ["austen", "dun*", "signed", "author:butler OR neuro*"]:
        sqlite.Reset(st)
        sqlite.BindText(st, 1, q)
        mut line := q + ":"
        until sqlite.Next(st):
            line = line + " " + sqlite.Text(st, 0) + "=" + sqlite.Text(st, 1)
        end
        println line
    end

    os.Remove("backup.db")
    if sqlite.Exec(d, "VACUUM INTO 'backup.db'") != 0:
        println "backup failed:", sqlite.Error(d)
        ret 1
    end
    b := sqlite.Open("backup.db")
    defer sqlite.Close(b)
    println "backup has", sqlite.QueryInt(b, "SELECT COUNT(*) FROM books"), "books and", sqlite.QueryInt(b, "SELECT COUNT(*) FROM loans"), "loans"
end

Outputausten: 2=Emma 3=Persuasion dun*: 1=[Dune] signed: 1=Dune author:butler OR neuro*: 4=Kindred 5=[Neuromancer] backup has 5 books and 3 loans

The search table here is filled once from books. In a real program keep it in step with triggers on books, or rebuild it after changes. FTS5 is part of the sqlite3 library that most systems ship.

QueryInt and QueryText return the first column of the first row in one call, for counts and single values. They return 0 or "" when there is no row, and they take no parameters, so use them only with SQL you wrote yourself.
One Database value is one connection. Do not share it between threads that write at the same time; give each thread its own Open of the same file, as the guide below shows. db also has a small Pool that holds any Connection values.
INSERT ... RETURNING and ON CONFLICT ... DO UPDATE (upsert) work through Prepare: step once and read the returned columns.
ColumnCount, ColumnName and ColumnType describe a result you did not write yourself, for a table viewer or an export.
Binary data (BLOBs) has no bind function in this package; store it hex or base64 encoded as text.

Guide: counters with upsert

A view counter, a rate limiter or a stock count needs "add one, or create the row with one". ON CONFLICT (key) DO UPDATE does it in one statement with no race between a read and a write, and RETURNING hands back the new value, read with Next and Int like a query.

db-sqlite-upsert.salam

import db.sqlite

func hit(d: sqlite.Database, page: str): int:
    st := sqlite.Prepare(d, "INSERT INTO views (page, n) VALUES (?, 1) ON CONFLICT (page) DO UPDATE SET n = n + 1 RETURNING n")
    defer sqlite.Finish(st)
    sqlite.BindText(st, 1, page)
    if not sqlite.Next(st):
        ret 0
    end
    ret sqlite.Int(st, 0)
end

func main:
    d := sqlite.Open(":memory:")
    defer sqlite.Close(d)
    sqlite.Exec(d, "CREATE TABLE views (page TEXT PRIMARY KEY, n INTEGER NOT NULL)")

    each p in ["/", "/docs", "/", "/std/db-sqlite", "/", "/docs"]:
        println p, "view", hit(d, p)
    end

    rows := sqlite.Query(d, "SELECT page, n FROM views ORDER BY n DESC, page")
    until sqlite.Next(rows):
        println sqlite.Int(rows, 1), sqlite.Text(rows, 0)
    end
    sqlite.Finish(rows)
end

Output/ view 1 /docs view 1 / view 2 /std/db-sqlite view 1 / view 3 /docs view 2 3 / 2 /docs 1 /std/db-sqlite

Guide: print any query as a table

When the columns are not known in advance, as in an admin page, an export or a debugging helper, ask the result about itself. ColumnCount and ColumnName give the header, and the ColumnType method returns one of the shared constants of db (TYPE_NULL, TYPE_INT, TYPE_FLOAT, TYPE_TEXT, TYPE_BLOB) for each value of the current row. A column can hold a different type in each row, so ask per row, not once.

db-sqlite-dump.salam

import db
import db.sqlite

func cell(rows: sqlite.Rows, i: int): str:
    switch rows.ColumnType(i):
        db.TYPE_NULL: ret "NULL" end
        db.TYPE_TEXT: ret "'" + rows.Text(i) + "'" end
    end
    ret rows.Text(i)
end

func dump(d: sqlite.Database, sql: str):
    rows := sqlite.Query(d, sql)
    defer sqlite.Finish(rows)
    n := rows.ColumnCount()
    mut head := ""
    repeat n in i:
        if i > 0: head = head + " | " end
        head = head + rows.ColumnName(i)
    end
    println head
    until rows.Next():
        mut line := ""
        repeat n in i:
            if i > 0: line = line + " | " end
            line = line + cell(rows, i)
        end
        println line
    end
end

func main:
    d := sqlite.Open(":memory:")
    defer sqlite.Close(d)
    sqlite.Exec(d, "CREATE TABLE items (id INTEGER PRIMARY KEY, name TEXT, price REAL, tag TEXT)")
    sqlite.Exec(d, "INSERT INTO items (name, price, tag) VALUES ('pen', 1.5, 'office'), ('lamp', 24, NULL), ('mug', NULL, 'kitchen')")

    dump(d, "SELECT * FROM items")
    println ""
    dump(d, "SELECT tag, COUNT(*) AS items, SUM(price) AS total FROM items GROUP BY tag ORDER BY tag")
end

Outputid | name | price | tag 1 | 'pen' | 1.5 | 'office' 2 | 'lamp' | 24.0 | NULL 3 | 'mug' | NULL | 'kitchen' tag | items | total NULL | 1 | 24.0 'kitchen' | 1 | NULL 'office' | 1 | 1.5

The package function sqlite.ColumnType returns SQLite's own codes instead, where 5 means NULL; the method shown here maps that to db.TYPE_NULL, which is 0.

Guide: several connections to one file

Two programs, or two threads with their own connections, can open the same file. In WAL mode readers see the last committed state and are never blocked by a writer, while only one writer at a time can hold the write lock. A second writer gets database is locked at once, or after waiting busy_timeout milliseconds if that is set.

db-sqlite-wal.salam

import db.sqlite
import os

func main:
    path := "shop.db"
    writer := sqlite.Open(path)
    sqlite.QueryText(writer, "PRAGMA journal_mode = WAL")
    sqlite.Exec(writer, "CREATE TABLE stock (item TEXT PRIMARY KEY, qty INTEGER)")
    sqlite.Exec(writer, "INSERT INTO stock VALUES ('pen', 10)")

    reader := sqlite.Open(path)
    sqlite.Exec(reader, "PRAGMA busy_timeout = 2000")

    sqlite.Begin(writer)
    sqlite.Exec(writer, "UPDATE stock SET qty = 7 WHERE item = 'pen'")
    println "reader during the write:", sqlite.QueryInt(reader, "SELECT qty FROM stock")
    println "writer sees its own change:", sqlite.QueryInt(writer, "SELECT qty FROM stock")

    second := sqlite.Open(path)
    sqlite.Begin(second)
    println "a second writer:", sqlite.Exec(second, "UPDATE stock SET qty = 0"), sqlite.Error(second)
    sqlite.Rollback(second)
    sqlite.Close(second)

    sqlite.Commit(writer)
    println "reader after commit:", sqlite.QueryInt(reader, "SELECT qty FROM stock")

    sqlite.Close(reader)
    sqlite.Close(writer)
    os.Remove(path)
end

Outputreader during the write: 10 writer sees its own change: 7 a second writer: -1 database is locked reader after commit: 7

Keep write transactions short, set busy_timeout on every connection, and retry a write that still fails with database is locked. For a busy web server with many writers at once, a server database such as PostgreSQL is the better fit.

Guide: JSON columns

SQLite stores JSON as text and has functions to check and read it in SQL, so loosely shaped data such as event logs or settings can live next to normal columns. CHECK (json_valid(body)) rejects broken JSON at insert time, ->> reads one field, and json_group_object builds an object from rows. The rejected insert also shows Reset returning -1 when the last run of the statement failed.

db-sqlite-json.salam

import db.sqlite

func main:
    d := sqlite.Open(":memory:")
    defer sqlite.Close(d)
    sqlite.Exec(d, "CREATE TABLE events (id INTEGER PRIMARY KEY, kind TEXT, body TEXT CHECK (json_valid(body)))")

    st := sqlite.Prepare(d, "INSERT INTO events (kind, body) VALUES (?, ?)")
    defer sqlite.Finish(st)
    kinds := ["signup", "order", "order", "broken"]
    bodies := ["{\"user\":\"sara\",\"plan\":\"free\"}", "{\"user\":\"sara\",\"items\":[\"pen\",\"mug\"],\"total\":12.5}", "{\"user\":\"reza\",\"items\":[\"lamp\"],\"total\":24}", "{not json"]
    repeat 4 in i:
        sqlite.Reset(st)
        sqlite.BindText(st, 1, kinds[i])
        sqlite.BindText(st, 2, bodies[i])
        sqlite.Next(st)
        if sqlite.Reset(st) != 0:
            println "rejected", kinds[i] + ":", sqlite.Error(d)
        end
    end

    rows := sqlite.Query(d, "SELECT body ->> '$.user', json_array_length(body, '$.items'), body ->> '$.total' FROM events WHERE kind = 'order' ORDER BY id")
    until sqlite.Next(rows):
        println sqlite.Text(rows, 0), "| items", sqlite.Int(rows, 1), "| total", sqlite.Float(rows, 2)
    end
    sqlite.Finish(rows)

    println sqlite.QueryText(d, "SELECT json_group_object(u, n) FROM (SELECT body ->> '$.user' AS u, COUNT(*) AS n FROM events GROUP BY u ORDER BY u)")
end

Outputrejected broken: CHECK constraint failed: json_valid(body) sara | items 2 | total 12.5 reza | items 1 | total 24 {"reza":1,"sara":2}

Guide: which function for which job

Job	Use
Create tables, run a fixed script	`Exec` (one or more statements)
Any SQL with a value from outside	`Prepare`, `Bind...`, `Next`, `Finish`
The same statement for many rows	`Prepare` once, then `Reset` and bind again for each row, inside `Begin` and `Commit`
Read a result	`until Next(rows):` with `Text`, `Int`, `Int64`, `Float`, `IsNull`
One number or one string, fixed SQL	`QueryInt`, `QueryText`
The id of a new row, rows changed	`LastInsertId`, `Changes`, `TotalChanges`
All or nothing	`Begin`, `Commit`, `Rollback`, or `db.InTx` through `Conn`
Code that should also run on MySQL or PostgreSQL	`Conn` and the db helpers
Find out what went wrong	`StmtOk`, the return of `Finish` or `Reset`, then `Error`

Types

`Database`

struct Database

One open SQLite database.

Name	Type	Description
`handle`	`void*`	The SQLite pointer.

db-sqlite-Methods.salam

import db
import db.sqlite

func main:
    d := sqlite.Open(":memory:")
    println d.Kind(), d.Ok(), d.Ping()
    _ := d.Exec("CREATE TABLE notes (id INTEGER PRIMARY KEY, body TEXT, stars REAL)")
    _ := d.Exec("INSERT INTO notes (body, stars) VALUES (" + d.Quote("the 'first' note") + ", 4.5)")
    println d.LastInsertId(), d.Changes()

    tx := d.Begin()
    _ := tx.Exec("INSERT INTO notes (body) VALUES ('second')")
    txrows := tx.Query("SELECT COUNT(*) FROM notes")
    _ := txrows.Next()
    println txrows.Int(0)
    _ := txrows.Close()
    println tx.Ok(), tx.Commit()

    rows := d.Query("SELECT id, body, stars FROM notes ORDER BY id")
    println rows.Ok(), rows.ColumnCount(), rows.ColumnName(2)
    until rows.Next():
        mut stars := "-"
        if not rows.IsNull(2):
            stars = "" + rows.Float(2)
        end
        println rows.Int64(0), rows.Text(1), stars, rows.ColumnType(2)
    end
    _ := rows.Close()

    tx2 := d.Begin()
    _ := tx2.Exec("DELETE FROM notes")
    _ := tx2.Rollback()
    c := sqlite.Conn(d)
    println db.QueryInt(c, "SELECT COUNT(*) FROM notes")
    println d.Exec("DROP TABLE nope") != 0, d.Error()
    println d.Close()
end

Outputsqlite true true 1 1 2 true 0 true 3 stars 1 the 'first' note 4.5 2 2 second - 0 2 true no such table: nope 0

`Kind`

func Kind(): str

The name of the database engine.

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

`Ok`

func Ok(): bool

There is a handle. SQLite returns one even when the file could not be opened, so run a first statement before trusting it.

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

`Ping`

func Ping(): bool

True while the handle is open; SQLite has no server to reach, so this does not touch the file.

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

`Exec`

func Exec(sql: str): int

Runs one or more statements separated by semicolons; zero means success.

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

`Query`

func Query(sql: str): dyn db.Rows

Prepares a query and returns its rows; Ok is false when the SQL has an error.

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

`Begin`

func Begin(): dyn db.Tx

Starts a transaction.

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

`LastInsertId`

func LastInsertId(): i64

The id of the last inserted row.

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

`Changes`

func Changes(): i64

The number of rows the last statement changed.

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

`Error`

func Error(): str

The last error message.

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

`Quote`

func Quote(s: str): str

Returns the string quoted and escaped, safe for SQL.

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

`Close`

func Close(): int

Closes it; zero means success.

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

`Rows`

struct Rows

One prepared statement or query result.

Name	Type	Description
`handle`	`void*`	The statement pointer.

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

`Ok`

func Ok(): bool

It is healthy.

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

`Next`

func Next(): bool

Moves to the next row; false when no row is left.

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

`ColumnCount`

func ColumnCount(): int

The number of columns.

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

`ColumnName`

func ColumnName(col: int): str

The column name.

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

`ColumnType`

func ColumnType(col: int): int

The type of the column value in the current row, one of the TYPE_... constants in db; zero for null.

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

`IsNull`

func IsNull(col: int): bool

The column value is null.

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

`Text`

func Text(col: int): str

The column value as text.

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

`Int`

func Int(col: int): int

The column value as an integer.

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

`Int64`

func Int64(col: int): i64

The column value as a 64 bit integer.

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

`Float`

func Float(col: int): f64

The column value as a float.

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

`Close`

func Close(): int

Closes and frees it.

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

`Txn`

struct Txn

One transaction.

Name	Type	Description
`handle`	`void*`	The database pointer.

db-sqlite-Basics.salam

import db.sqlite

func main:
    println sqlite.Available(), sqlite.Version().len() > 0
    d := sqlite.Open(":memory:")
    println sqlite.Ok(d)
    _ := sqlite.Exec(d, "CREATE TABLE books (id INTEGER PRIMARY KEY, title TEXT NOT NULL, pages INTEGER, price REAL, note TEXT)")
    _ := sqlite.Exec(d, "INSERT INTO books (title, pages, price) VALUES ('The Little Prince', 96, 4.5)")
    println sqlite.LastInsertId(d), sqlite.Changes(d)

    stmt := sqlite.Prepare(d, "INSERT INTO books (title, pages, price, note) VALUES (?, ?, ?, ?)")
    println sqlite.StmtOk(stmt)
    _ := sqlite.BindText(stmt, 1, "The Blind Owl")
    _ := sqlite.BindInt(stmt, 2, 120)
    _ := sqlite.BindFloat(stmt, 3, 6.25)
    _ := sqlite.BindNull(stmt, 4)
    _ := sqlite.Next(stmt)
    _ := sqlite.Reset(stmt)
    _ := sqlite.BindText(stmt, 1, "Savushun")
    _ := sqlite.BindInt(stmt, 2, 304)
    _ := sqlite.BindFloat(stmt, 3, 9)
    _ := sqlite.BindText(stmt, 4, "classic")
    _ := sqlite.Next(stmt)
    _ := sqlite.Finish(stmt)
    println sqlite.TotalChanges(d)

    rows := sqlite.Query(d, "SELECT id, title, pages, price, note FROM books ORDER BY id")
    println sqlite.ColumnCount(rows), sqlite.ColumnName(rows, 1)
    until sqlite.Next(rows):
        mut note := "-"
        if not sqlite.IsNull(rows, 4):
            note = sqlite.Text(rows, 4)
        end
        println sqlite.Int(rows, 0), sqlite.Text(rows, 1), sqlite.Int64(rows, 2), sqlite.Float(rows, 3), note
    end
    _ := sqlite.Finish(rows)

    kinds := sqlite.Query(d, "SELECT pages, title, price, note FROM books WHERE id = 2")
    _ := sqlite.Next(kinds)
    println sqlite.ColumnType(kinds, 0), sqlite.ColumnType(kinds, 1), sqlite.ColumnType(kinds, 2), sqlite.ColumnType(kinds, 3)
    _ := sqlite.Finish(kinds)

    println sqlite.QueryInt(d, "SELECT SUM(pages) FROM books"), sqlite.QueryText(d, "SELECT title FROM books ORDER BY price DESC LIMIT 1")

    _ := sqlite.Begin(d)
    _ := sqlite.Exec(d, "DELETE FROM books")
    _ := sqlite.Rollback(d)
    _ := sqlite.Begin(d)
    _ := sqlite.Exec(d, "UPDATE books SET price = price * 2")
    _ := sqlite.Commit(d)
    println sqlite.QueryInt(d, "SELECT COUNT(*) FROM books"), sqlite.QueryText(d, "SELECT price FROM books WHERE id = 1")

    println sqlite.Exec(d, "SELECT * FROM missing") != 0
    println sqlite.Error(d)
    _ := sqlite.Close(d)
end

Outputtrue true true 1 1 true 3 5 title 1 The Little Prince 96 4.5 - 2 The Blind Owl 120 6.25 - 3 Savushun 304 9 classic 1 3 2 5 520 Savushun 3 9.0 true no such table: missing