# 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:

```text
bt install sqlite
```

## Syntax

```bt
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

```bt
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

```bt
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

```bt
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

```bt
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

```bt
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

```bt
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

```bt
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

```bt
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

```bt
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

```bt
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

```bt
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

```bt
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

```bt
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

```text
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 supports `exec()`, not `one()` and `all()`.
- `workers()` is the MySQL migration compatible interface; SQLite currently does not write to the same connection concurrently.
- `all()` must set reasonable `max_rows` and `max_result_bytes` to prevent large results from entering the BT VM uncontrollably.
- `close()` / `SqliteQuery.close()` should be called explicitly; bindings are marked `lifecycle: "dispose"` and the old handle will be invalidated after the call is successful.
- `busy_timeout_ms` only handles SQLite lock waits, not SQL logical timeouts; the `call_timeout_ms` of shared workers is still responsible for the host ExtensionService.