SQLite extension library
SQLite extension library
Function
examples/extensions/sqlite is BT official SQLite file database extension library example. It retains SQLite connections within workers via the shared WASM extension, supporting chained SQL construction, parameter binding, transactions, concurrent reads, busy timeout, result caps, and object deallocation.
This extension is not built into the standard library. Before use, you need to compile the extension into module.wasm, then package it into .bts and install it into the project extensions/ directory. The official extension library installation can be used directly:
bt install sqlite
Syntax
db = sqlite_open(path, options) row = db.query(sql).bind(value).one() rows = db.query(sql).bind(value).all() ret = db.query(sql).bind(value).exec() ret = db.query(sql).bind(prefix).binds(rows).batch(size).workers(count).exec() changed = db.transaction(statements) db.close()
sqlite_open
opens the SQLite database file and returns the Sqlite connection object.
Syntax
db = sqlite_open(path, options)
Parameters
| Parameters | Type | Required | Default value | Description |
|---|---|---|---|---|
path | String | Yes | None | SQLite database file path. bindings uses the path_write role, and the path must be within the project root directory. |
options | Object | Yes | {} | Connection configuration object. |
options Field
| Field | Type | Required | Default | Range | Description |
|---|---|---|---|---|---|
wal | Bool | No | false | true or false | Whether to enable SQLite WAL logging mode. true will execute PRAGMA journal_mode = WAL, and the read and write concurrency experience is usually better, but -wal and -shm auxiliary files will appear next to the database. The single-script temporary database does not need to be enabled; it is recommended to enable it for web services, desktop applications, and long-running projects. |
busy_timeout_ms | Int | No | 1000 | 0..=300000 | When the database is locked by other connections, the maximum number of milliseconds to wait before reporting an error. 1000 means waiting at most 1 second; 0 means no waiting. It only handles SQLite file lock waits, not SQL query execution timeouts. |
max_rows | Int | No | 1000 | 1..=100000 | all() The maximum number of rows allowed to be returned at a time. If exceeded, an error will be reported to prevent a large amount of data from being read into the BT VM at one time. When you only want to fetch one page of data, it is recommended to add LIMIT to SQL yourself. |
max_result_bytes | Int | No | 4194304 | 1..=16777216 | The estimated upper bound in bytes for the results returned by one() and all(). Default is about 4MB, maximum is 16MB. It is used to protect resident process memory and avoid returning too many large texts or large BLOBs at one time. |
Newbies can first pass {} and use the default value; when you know that the result set may be large, there will be concurrent reading and writing, or you want to limit memory usage, you can configure these fields as needed.
Return Value
| Type | Description |
|---|---|
Sqlite | SQLite connection object, type(db) returns Sqlite. |
Sqlite.query
Create a chained query object. query() only saves SQL and does not perform database operations.
Syntax
query = db.query(sql)
Parameters
| Parameter | Type | Required | Default value | Description |
|---|---|---|---|---|
sql | String | Yes | None | SQL to be executed, use ? as parameter placeholder. |
Return Value
| Type | Description |
|---|---|
SqliteQuery | Chain query object, type(query) returns SqliteQuery. |
SqliteQuery.bind
Append a common binding parameter. The binding value will correspond to the ? placeholder in SQL in the calling order.
Syntax
query = db.query(sql).bind(value)
Parameters
| Parameters | Type | Required | Default value | Description |
|---|---|---|---|---|
value | Any | Yes | None | Binding value. Supports null, Bool, Int, Float, String, Bytes; empty is not allowed. |
Return Value
| Type | Description |
|---|---|
SqliteQuery | Return the same query object to facilitate continued chain calls. |
SqliteQuery.binds
Add multiple lines of batch binding parameters, only used for exec().
Syntax
query = db.query(sql).bind(prefix).binds(rows)
Parameters
| Parameters | Type | Required | Default value | Description |
|---|---|---|---|---|
rows | Array | Yes | None | Two-dimensional array. Each row is a set of bound values; if the row element is not an array, it will be treated as a single-value row. |
Return Value
| Type | Description |
|---|---|
SqliteQuery | Return the same query object. |
SqliteQuery.batch
Set batch size statistics for batch exec(). It is mainly used to be consistent with the batch writing method of the MySQL standard library.
SQLite currently executes these bind rows serially in the same transaction; batch(size) will affect batch_count and batch_size in the returned object, making it easier to migrate code and observe batch size.
Syntax
query = db.query(sql).binds(rows).batch(size)
Parameters
| Parameters | Type | Required | Default | Description |
|---|---|---|---|---|
size | Int | Yes | 0 when not called | Batch size. Less than 0 is processed as 0; 0 means using all bound rows as a batch. |
Return Value
| Type | Description |
|---|---|
SqliteQuery | Return the same query object. |
SqliteQuery.workers
Set migration-compatible job count statistics. This method is to allow the code migrated from the MySQL standard library to retain similar writing methods.
SQLite cannot write concurrently to the same connection like the MySQL connection pool; the current implementation will still execute serially within a transaction. In other words, workers(4) will not allow the same SQLite connection to write 4 SQL statements concurrently.
Syntax
query = db.query(sql).binds(rows).workers(count)
Parameters
| Parameters | Type | Required | Default | Range | Description |
|---|---|---|---|---|---|
count | Int | Yes | 1 when not called | 1..=4096 | Number of migration compatible jobs. If it is smaller than 1, it will be processed as 1, and if it is larger than 4096, it will be processed as 4096. |
Return Value
| Type | Description |
|---|---|
SqliteQuery | Return the same query object. |
SqliteQuery.one
Execute the query and return the first row.
Syntax
row = db.query(sql).bind(value).one()
Parameters
has no parameters.
Return Value
| Type | Description |
|---|---|
| Object/empty | Return the object when the row is queried; return empty if there is no result. SQLite NULL returns BT null, BLOB returns BT Bytes. |
SqliteQuery.all
Execute a query and return multiple rows.
Syntax
rows = db.query(sql).bind(value).all()
Parameters
has no parameters.
Return Value
| Type | Description |
|---|---|
| Array | Returns an array of row objects, subject to max_rows and max_result_bytes limits. |
SqliteQuery.exec
Execute SQL that does not require returning a result set. Ordinary bind() is executed once; binds() will be executed serially according to the bound rows in the SQLite transaction.
Syntax
ret = db.query(sql).bind(value).exec() ret = db.query(sql).bind(prefix).binds(rows).batch(size).workers(count).exec()
Parameters
has no parameters.
Return Value
| Type | Description |
|---|---|
| Object | Returns the SQL execution statistics object. |
Execution result field
| Field | Type | Must exist | Description |
|---|---|---|---|
total | Int | Yes | The number of bound rows processed by this execution. When ordinary bind() is executed, it is 1; when binds() is executed in batches, it is the number of bound array rows; when binds() is executed in batches, it is 0. |
rows_affected | Int | Yes | The number of affected rows reported by SQLite. When executed in batches, the values are accumulated row by row. |
last_insert_id | Int | Yes | SQLite last_insert_rowid() for the current connection. |
batch_count | Int | Yes | Number of batches calculated as batch(). The current execution of SQLite is still completed within a transaction. |
batch_size | Int | Yes | The batch size configured for the current query object; 0 when batch() is not called. |
workers | Int | Yes | The number of jobs after the current query object is configured and normalized. SQLite currently does not write concurrently to the same connection. |
SqliteQuery.sql
Returns the debugging text of the current SQL.
Syntax
text = db.query(sql).bind(value).sql()
Parameters
has no parameters.
Return Value
| Type | Description |
|---|---|
| String | Returns the SQL preview text after rendering the binding value to the ? placeholder. This text is only used for debugging and does not participate in execution. |
Sqlite.transaction
Execute multiple write statements serially in the same SQLite transaction.
Syntax
changed = db.transaction(statements)
Parameters
| Parameters | Type | Required | Default value | Description |
|---|---|---|---|---|
statements | Array | Yes | None | Array of transaction statements. The element can be a SQL string or a { sql, binds } object. |
statements Object field
| Field | Type | Required | Default value | Description |
|---|---|---|---|---|
sql | String | Yes | None | SQL to be executed. |
binds | Array | No | [] | SQL parameter array. Supports null, Bool, Int, Float, String, Bytes; empty is not allowed. |
params | Array | No | [] | Old field alias, it is recommended that new code use binds. |
Return Value
| Type | Description |
|---|---|
| Int | The cumulative number of affected rows in the transaction. |
close
Release the query object or database connection object.
Syntax
query.close() db.close()
Parameters
has no parameters.
Return Value
| Type | Description |
|---|---|
| Bool | Successful release returns true; the old handle becomes invalid after release. |
Code Examples
fs('@/data').create_dir() db = sqlite_open('@/data/sqlite-demo.db', { wal: true, busy_timeout_ms: 1000, max_rows: 100, max_result_bytes: 1048576 }) // Output: Sqlite print type(db) db.query('CREATE TABLE IF NOT EXISTS users (id INTEGER PRIMARY KEY, name TEXT, payload BLOB, note TEXT)').exec() db.query('DELETE FROM users').exec() ret = db.query('INSERT INTO users (name, payload, note) VALUES (?, ?, ?)') .bind('Alice') .bind(bytes('4254', 'hex')) .bind(null) .exec() // Output: 1 print ret.rows_affected db.query('INSERT INTO users (name, payload, note) VALUES (?, ?, ?)') .binds([ ['Bob', bytes('0102', 'hex'), 'writer'], ['Carol', bytes('0304', 'hex'), 'writer'] ]) .batch(2) .workers(1) .exec() row = db.query('SELECT name, payload, note FROM users WHERE name = ?').bind('Alice').one() // Output: Alice print row.name // Output: true print is_null(row.note) missing = db.query('SELECT name FROM users WHERE name = ?').bind('Missing').one() // Output: true print is_empty(missing) rows = db.query('SELECT id, name FROM users ORDER BY id').all() // Output: 3 print rows.len() changed = db.transaction([ { sql: 'UPDATE users SET note = ? WHERE name = ?', binds: ['reader', 'Bob'] }, { sql: 'UPDATE users SET note = ? WHERE name = ?', binds: ['reader', 'Carol'] } ]) // Output: 2 print changed db.close()
Building bundled SQLite for
rustup target add wasm32-wasip1 cargo build --manifest-path examples/extensions/sqlite/Cargo.toml --target wasm32-wasip1 --release copy examples\extensions\sqlite\target\wasm32-wasip1\release\sqlite.wasm examples\extensions\sqlite\module.wasm cargo run -- ext build examples/extensions/sqlite -o examples/extensions/sqlite/sqlite.bts
rusqlite requires a C compiler under the WASI target. Windows environment needs to install LLVM clang or WASI SDK first, and make sure clang is in PATH.
Notes
- The main usage of the SQLite extension is consistent with the MySQL standard library: first query(sql), then bind() or binds(), and finally call one(), all() or exec().
- The number of extension method parameters is strictly verified by
bindings.json;bind()only binds one value at a time and is called multiple times continuously when multiple parameters are required. -
binds()only supportsexec(), notone()andall(). -
workers()is the MySQL migration compatible interface; SQLite currently does not write to the same connection concurrently. -
all()must set reasonablemax_rowsandmax_result_bytesto prevent large results from entering the BT VM uncontrollably. -
close()/SqliteQuery.close()should be called explicitly; bindings are markedlifecycle: "dispose"and the old handle will be invalidated after the call is successful. -
busy_timeout_msonly handles SQLite lock waits, not SQL logical timeouts; thecall_timeout_msof shared workers is still responsible for the host ExtensionService.