Standard library · Database

db/sqlite

An SQLite database in one file or in memory.

import db.sqlite

On this page

Types
Functions

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 sqlite3 library has to be on the system to run it.

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.

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

It is healthy.

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

`Ping`

func Ping(): bool

The connection is alive.

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

`Exec`

func Exec(sql: str): int

Runs one statement; 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

Runs a query and returns the rows.

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 and frees it.

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