Opening a database connection.

A database connection is opened by calling the openDatabase procedure with the path to the database file as an argument. If the file doesn't exist, it will be created. An in-memory database can be created by using the special path ":memory:" as an argument. Once the database connection is no longer needed close must be called to prevent memory leaks.

let db = openDatabase("path/to/file.db")
# ... (do something with `db`)
db.close()

Executing SQL

The exec procedure can be used to execute a single SQL statement. The execScript procedure is used to execute several statements, but it doesn't support parameter substitution.

db.execScript("""
    CREATE TABLE Person(
        name TEXT,
        age INTEGER
    );
    
    CREATE TABLE Log(
        message TEXT
    );
""")

db.exec("""
    INSERT INTO Person(name, age)
    VALUES(?, ?);
""", "John Doe", 37)

Reading data

Four different procedures for reading data are available:

• all: procedure returning all result rows
• iterate: iterator yielding each result row one by one
• one: procedure returning the first result row, or none if no result row exists
• value: procedure returning the first column of the first result row, or none if no result row exists

Note that the procedures one and value returns the result wrapped in an Option. See the standard library options module for documentation on how to deal with Option values. For convenience the tiny_sqlite module exports the options.get, options.isSome, and options.isNone procedures so the options module doesn't need to be explicitly imported for typical usage.

for row in db.iterate("SELECT name, age FROM Person"):
    # The 'row' variable is of type ResultRow.
    # The column values can be accesed by both index and column name:
    echo row[0].strVal      # Prints the name
    echo row["name"].strVal # Prints the name
    echo row[1].intVal      # Prints the age
    # Above we're using the raw DbValue's directly. Instead, we can unpack the
    # DbValue using the fromDbValue procedure:
    echo fromDbValue(row[0], string) # Prints the name
    echo fromDbValue(row[1], int)    # Prints the age
    # Alternatively, the entire row can be unpacked at once:
    let (name, age) = row.unpack((string, int))
    # Unpacking the value is preferable as it makes it possible to handle
    # bools, enums, distinct types, nullable types and more. For example, nullable
    # types are handled using Option[T]:
    echo fromDbValue(row[0], Option[string]) # Will work even if the db value is NULL

# Example of reading a single value. In this case, 'value' will be of type `Option[DbValue]`.
let value = db.one("SELECT age FROM Person WHERE name = ?", "John Doe")
if value.isSome:
    echo fromDbValue(value.get, int) # Prints age of John Doe

Inserting data in bulk

The exec procedure works fine for inserting single rows, but it gets awkward when inserting many rows. For this purpose the execMany procedure can be used instead. It executes the same SQL repeatedly, but with different parameters each time.

let parameters = @[toDbValues("Person 1", 17), toDbValues("Person 2", 55)]
# Will insert two rows
db.execMany("""
    INSERT INTO Person(name, age)
    VALUES(?, ?);
""", parameters)

Transactions

The procedures that can execute multiple SQL statements (execScript and execMany) are wrapped in a transaction by tiny_sqlite. Transactions can also be controlled manually by using one of these two options:

• Option 1: using the transaction template

db.transaction:
    # Anything inside here is executed inside a transaction which
    # will be rolled back in case of an error
    db.exec("DELETE FROM Person")
    db.exec("""INSERT INTO Person(name, age) VALUES("Jane Doe", 35)""")

• Option 2: using the exec procedure manually

db.exec("BEGIN")
try:
    db.exec("DELETE FROM Person")
    db.exec("""INSERT INTO Person(name, age) VALUES("Jane Doe", 35)""")
    db.exec("COMMIT")
except:
    db.exec("ROLLBACK")

Prepared statements

All the procedures for executing SQL described above create and execute prepared statements internally. In addition to those procedures, tiny_sqlite also offers an API for preparing SQL statements explicitly. Prepared statements are created with the stmt procedure, and the same procedures for executing SQL that are available directly on the connection object are also available for the prepared statement:

let stmt = db.stmt("INSERT INTO Person(name, age) VALUES (?, ?)")
stmt.exec("John Doe", 21)
# Once the statement is no longer needed it must be finalized
# to prevent memory leaks.
stmt.finalize()

There are performance benefits of reusing prepared statements, since the preparation only needs to be done once. However, tiny_sqlite keeps an internal cache of prepared statements, so it's typically not necesarry to manage prepared statements manually. If you prefer if tiny_sqlite doesn't perform this caching, you can disable it by setting the cacheSize parameter when opening the database:

let db = openDatabase(":memory:", cacheSize = 0)

Supported types

For a type to be supported when using unpacking and parameter substitution the procedures toDbValue and fromDbValue must be implemented for the type. Below is table describing which types are supported by default and to which SQLite type they are mapped to:

Nim type	SQLite type
Ordinal	INTEGER
SomeFloat	REAL
string	TEXT
seq[byte]	BLOB
Option[T]	NULL if value is none(T), otherwise the type that T would use

This can be extended by implementing toDdValue and fromDbValue for other types on your own. Below is an example how support for times.Time can be added:

import times

proc toDbValue(t: Time): DbValue =
    DbValue(kind: sqliteInteger, intVal: toUnix(t))

proc fromDbValue(value: DbValue, T: typedesc[Time]): Time =
    fromUnix(value.intval)