sqlite3.open
Signature
function sqlite3.open(path, opts)Description
Open the SQLite database at path and return a Database object. Use ":memory:" for a temporary in-memory database. By default the database is opened read-write and the file is created if it does not exist.
The optional opts map currently recognizes a single key, readonly. When it is true, the database is opened read-only and is not created. Any other keys are ignored.
Parameters
path- The path of the database file, or
":memory:"for an in-memory database. opts- (Optional) A map of options. Currently only
readonlyis recognized; whentrue, the database is opened read-only.
Returns
A Database object.
Errors
- Raises an error if
pathis not a string. - Raises an error with SQLite’s error message if the database cannot be opened, such as a missing file in read-only mode or a path that cannot be created.
Example
import sqlite3
// Read-write database, created on demand
const db = sqlite3.open("app.db")
db.close()
// Temporary database that lives in memory
const memory = sqlite3.open(":memory:")
memory.close()
// Existing database, opened read-only
const reader = sqlite3.open("existing.db", { readonly = true })
reader.close()Database
Signature
class DatabaseDescription
Represents an open SQLite database, returned by sqlite3.open. All operations are methods of the object, as in db.query(...).
Parameters for run, query, row, value and the Statement methods can be given as a list for positional placeholders or as a map for named placeholders. The supported placeholder forms are SQLite’s ?, :name, @name and $name. For named placeholders, the map keys omit the placeholder prefix: use name for :name, @name or $name. Positional values are matched to placeholders in order. Missing named values and omitted positional values are left unbound, which SQLite treats as NULL, and extra positional values are ignored.
Values read from the database are converted as follows: NULL becomes nil; INTEGER and REAL become numbers (very large 64-bit integers may not be exactly representable); TEXT and BLOB become strings, preserving embedded NUL bytes. When binding, nil, booleans, numbers and strings are supported. Booleans bind as the integers 0 and 1, numbers bind as floating-point values, and strings bind as TEXT. There is no way to bind a BLOB.
The database is closed automatically when the object is collected. Call close to release it at a specific point.
Returns
A Database object.
Errors
- Calling any method on a database that has been closed raises an error stating that the database is closed.
Example
import sqlite3
const db = sqlite3.open(":memory:")
db.exec("CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT, age INTEGER)")
// Positional parameters
db.run("INSERT INTO users(name, age) VALUES (?, ?)", ["Ada", 37])
// Named parameters
db.run("INSERT INTO users(name, age) VALUES (:name, :age)", { name = "Grace", age = 85 })
for const user in db.query("SELECT name, age FROM users ORDER BY name")
{
print(user["name"], user["age"])
}
db.close()Database.exec
Signature
function Database.exec(sql)Description
run or query when parameters or results are needed.Parameters
sql- A string with one or more SQL statements.
Returns
Nothing.
Errors
- Raises an error if the database is closed.
- Raises an error if
sqlis not a string. - Raises an error with SQLite’s error message if a statement fails. Statements executed before the failing one are not undone.
Example
import sqlite3
const db = sqlite3.open(":memory:")
db.exec(```
CREATE TABLE categories (id INTEGER PRIMARY KEY, name TEXT NOT NULL UNIQUE);
INSERT INTO categories(name) VALUES ('films'), ('series'), ('anime');```)
print(db.value("SELECT count(*) FROM categories")) // 3
Database.run
Signature
function Database.run(sql, params)Description
sql, consuming any rows it returns. Only the first statement of the string is executed; use exec for scripts with several statements. Intended for statements such as INSERT, UPDATE and DELETE.Parameters
sql- A string with the SQL statement to execute.
params- (Optional) A list of positional values or a map of named values to bind to the placeholders.
Returns
A map with two keys: changes, the number of rows modified by the most recent statement, and last_id, the row ID of the most recently inserted row.
Errors
- Raises an error if the database is closed.
- Raises an error if
sqlis not a string. - Raises an error if
paramsis neither a list nor a map. - Raises an error if a parameter has an unsupported type, such as a list, map or function.
- Raises an error with SQLite’s error message if the statement cannot be prepared or fails during execution, for example because of a syntax error or a violated constraint.
Example
import sqlite3
const db = sqlite3.open(":memory:")
db.exec("CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT)")
const result = db.run("INSERT INTO users(name) VALUES (?)", ["Ada"])
print(result["changes"]) // 1
print(result["last_id"]) // 1
db.run("UPDATE users SET name = :name WHERE id = :id", { name = "Ada Lovelace", id = 1 })Database.query
Signature
function Database.query(sql, params)Description
sql and collect every resulting row. Each row is a map from column name to value. Use column aliases in the query to control the keys.Parameters
sql- A string with the SQL query to execute.
params- (Optional) A list of positional values or a map of named values to bind to the placeholders.
Returns
A list of maps, one per row, which is empty if the query returns no rows.
Errors
- Raises an error if the database is closed.
- Raises an error if
sqlis not a string. - Raises an error if
paramsis neither a list nor a map. - Raises an error if a parameter has an unsupported type, such as a list, map or function.
- Raises an error with SQLite’s error message if the statement cannot be prepared or fails during execution.
Example
import sqlite3
const db = sqlite3.open(":memory:")
db.exec(```
CREATE TABLE users (name TEXT, age INTEGER);
INSERT INTO users VALUES ('Ada', 37), ('Grace', 85), ('Tim', 12);```)
const adults = db.query("SELECT name, age FROM users WHERE age >= ? ORDER BY age", [18])
for const user in adults
{
print(user["name"], user["age"])
}Database.row
Signature
function Database.row(sql, params)Description
sql and return only its first row as a map from column name to value. Remaining rows are ignored.Parameters
sql- A string with the SQL query to execute.
params- (Optional) A list of positional values or a map of named values to bind to the placeholders.
Returns
A map with the first row, or nil if the query returns no rows.
Errors
- Raises an error if the database is closed.
- Raises an error if
sqlis not a string. - Raises an error if
paramsis neither a list nor a map. - Raises an error if a parameter has an unsupported type, such as a list, map or function.
- Raises an error with SQLite’s error message if the statement cannot be prepared or fails during execution.
Example
import sqlite3
const db = sqlite3.open(":memory:")
db.exec(```
CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT, age INTEGER);
INSERT INTO users(name, age) VALUES ('Ada', 37);```)
const user = db.row("SELECT * FROM users WHERE name = :name", { name = "Ada" })
if user != nil
{
print(user["id"], user["age"])
}
print(db.row("SELECT * FROM users WHERE name = ?", ["Nobody"])) // nil
Database.value
Signature
function Database.value(sql, params)Description
sql and return the value of the first column of the first row. Useful for scalar queries such as count(*) or an ID lookup.Parameters
sql- A string with the SQL query to execute.
params- (Optional) A list of positional values or a map of named values to bind to the placeholders.
Returns
The value of the first column of the first row, converted to a Teascript value, or nil if the query returns no rows or the value is NULL.
Errors
- Raises an error if the database is closed.
- Raises an error if
sqlis not a string. - Raises an error if
paramsis neither a list nor a map. - Raises an error if a parameter has an unsupported type, such as a list, map or function.
- Raises an error with SQLite’s error message if the statement cannot be prepared or fails during execution.
Example
import sqlite3
const db = sqlite3.open(":memory:")
db.exec(```
CREATE TABLE categories (id INTEGER PRIMARY KEY, name TEXT);
INSERT INTO categories(name) VALUES ('films'), ('anime');```)
print(db.value("SELECT count(*) FROM categories")) // 2
print(db.value("SELECT id FROM categories WHERE name = ?", ["anime"])) // 2
print(db.value("SELECT id FROM categories WHERE name = ?", ["none"])) // nil
Database.prepare
Signature
function Database.prepare(sql)Description
sql into a reusable Statement. Preparing a statement once and running it many times with different parameters avoids parsing the SQL again. Only the first statement of the string is prepared.Parameters
sql- A string with the SQL statement to prepare.
Returns
A Statement object.
Errors
- Raises an error if the database is closed.
- Raises an error if
sqlis not a string. - Raises an error with SQLite’s error message if the statement cannot be prepared, such as a syntax error or a reference to a table that does not exist.
Example
import sqlite3
const db = sqlite3.open(":memory:")
db.exec("CREATE TABLE logs (message TEXT)")
const insert = db.prepare("INSERT INTO logs(message) VALUES (?)")
insert.run(["started"])
insert.run(["finished"])
insert.finalize()
print(db.value("SELECT count(*) FROM logs")) // 2
Database.transaction
Signature
function Database.transaction(fn)Description
fn with no arguments, and commit when it returns. If fn raises an error, the transaction is rolled back and the error is raised again. Transactions cannot be nested.Parameters
fn- A function called with no arguments that performs the database operations of the transaction.
Returns
Nothing.
Errors
- Raises an error if the database is closed.
- Raises an error if
fnis not a function. - Raises an error with SQLite’s error message if the transaction cannot begin, for example because one is already active.
- Re-raises any error raised by
fn, after rolling back the transaction. - Raises an error with SQLite’s error message if the commit fails, after rolling back the transaction.
Example
import sqlite3
const db = sqlite3.open(":memory:")
db.exec("CREATE TABLE accounts (name TEXT)")
db.transaction(function() {
db.run("INSERT INTO accounts(name) VALUES (?)", ["checking"])
db.run("INSERT INTO accounts(name) VALUES (?)", ["savings"])
})
print(db.value("SELECT count(*) FROM accounts")) // 2
Database.begin
Signature
function Database.begin()Description
commit or rollback is called. Prefer transaction when the work fits in a function, as it ends the transaction automatically.Returns
Nothing.
Errors
- Raises an error if the database is closed.
- Raises an error with SQLite’s error message if the transaction cannot begin, for example because one is already active.
Example
import sqlite3
const db = sqlite3.open(":memory:")
db.exec("CREATE TABLE items (name TEXT)")
db.begin()
db.run("INSERT INTO items(name) VALUES (?)", ["first"])
db.commit()Database.commit
Signature
function Database.commit()Description
begin, making its changes permanent.Returns
Nothing.
Errors
- Raises an error if the database is closed.
- Raises an error with SQLite’s error message if there is no active transaction or the commit fails.
Example
import sqlite3
const db = sqlite3.open(":memory:")
db.exec("CREATE TABLE items (name TEXT)")
db.begin()
db.run("INSERT INTO items(name) VALUES (?)", ["kept"])
db.commit()
print(db.value("SELECT count(*) FROM items")) // 1
Database.rollback
Signature
function Database.rollback()Description
begin, discarding its changes.Returns
Nothing.
Errors
- Raises an error if the database is closed.
- Raises an error with SQLite’s error message if there is no active transaction or the rollback fails.
Example
import sqlite3
const db = sqlite3.open(":memory:")
db.exec("CREATE TABLE items (name TEXT)")
db.begin()
db.run("INSERT INTO items(name) VALUES (?)", ["discarded"])
db.rollback()
print(db.value("SELECT count(*) FROM items")) // 0
Database.close
Signature
function Database.close()Description
close on a database that is already closed does nothing. Any other operation on a closed database raises an error. All prepared statements should be finalized before closing.Returns
Nothing.
Errors
- Raises an error with SQLite’s error message if the database cannot be closed, for example because of statements that have not been finalized. The database remains open in that case.
Example
import sqlite3
const db = sqlite3.open("app.db")
// ... use the database ...
db.close()
db.close() // safe, does nothing
Statement
Signature
class StatementDescription
Represents a compiled SQL statement, returned by Database.prepare. Each execution first resets the statement and clears its previous bindings, and then binds the parameters that were passed, so the same statement can be run any number of times with different values. Parameters follow the same rules as for Database methods.
The statement is finalized automatically when the object is collected. Call finalize to release it at a specific point, in particular before closing the database.
Returns
A Statement object.
Errors
- Calling any method other than
finalizeon a statement that has been finalized raises an error stating that the statement is finalized.
Example
import sqlite3
const db = sqlite3.open(":memory:")
db.exec("CREATE TABLE users (name TEXT, age INTEGER)")
const insert = db.prepare("INSERT INTO users(name, age) VALUES (:name, :age)")
insert.run({ name = "Ada", age = 37 })
insert.run({ name = "Grace", age = 85 })
insert.finalize()
const select = db.prepare("SELECT name FROM users WHERE age > ?")
print(select.query([50]).len)
select.finalize()Statement.run
Signature
function Statement.run(params)Description
params, and execute it, consuming any rows it returns.Parameters
params- (Optional) A list of positional values or a map of named values to bind to the placeholders.
Returns
A map with two keys: changes, the number of rows modified by the most recent statement, and last_id, the row ID of the most recently inserted row.
Errors
- Raises an error if the statement has been finalized.
- Raises an error if
paramsis neither a list nor a map. - Raises an error if a parameter has an unsupported type, such as a list, map or function.
- Raises an error with SQLite’s error message if execution fails, for example because of a violated constraint.
Example
import sqlite3
const db = sqlite3.open(":memory:")
db.exec("CREATE TABLE logs (message TEXT)")
const insert = db.prepare("INSERT INTO logs(message) VALUES (?)")
const first = insert.run(["started"])
print(first["last_id"]) // 1
const second = insert.run(["finished"])
print(second["last_id"]) // 2
insert.finalize()Statement.query
Signature
function Statement.query(params)Description
params, execute it, and collect every resulting row. Each row is a map from column name to value.Parameters
params- (Optional) A list of positional values or a map of named values to bind to the placeholders.
Returns
A list of maps, one per row, which is empty if the statement returns no rows.
Errors
- Raises an error if the statement has been finalized.
- Raises an error if
paramsis neither a list nor a map. - Raises an error if a parameter has an unsupported type, such as a list, map or function.
- Raises an error with SQLite’s error message if execution fails.
Example
import sqlite3
const db = sqlite3.open(":memory:")
db.exec(```
CREATE TABLE titles (name TEXT, year INTEGER);
INSERT INTO titles VALUES ('Arrival', 2016), ('Severance', 2022), ('Spirited Away', 2001);```)
const since = db.prepare("SELECT name FROM titles WHERE year >= ? ORDER BY year")
for const title in since.query([2010])
{
print(title["name"])
}
since.finalize()Statement.finalize
Signature
function Statement.finalize()Description
finalize on a statement that is already finalized does nothing. Any other use of a finalized statement raises an error.Returns
Nothing.
Errors
- Does not raise errors when called again on a finalized statement.
Example
import sqlite3
const db = sqlite3.open(":memory:")
db.exec("CREATE TABLE logs (message TEXT)")
const insert = db.prepare("INSERT INTO logs(message) VALUES (?)")
insert.run(["started"])
insert.finalize()
insert.finalize() // safe, does nothing
db.close()