### SQL Initialization with wa-sqlite (SQL)

Source: https://github.com/rhashimoto/wa-sqlite/blob/master/demo/hello/index.html

This SQL snippet shows the initialization of a table named 't' if it does not already exist. It defines 'x' as the primary key, inserts or replaces two values ('foo' and 'bar'), and then selects all entries from the table. This is a basic setup for demonstrating database operations.

```sql
CREATE TABLE IF NOT EXISTS t(x PRIMARY KEY);
INSERT OR REPLACE INTO t VALUES ('foo'), ('bar');
SELECT * FROM t;
```

--------------------------------

### Execute SQL and Display Results with wa-sqlite (JavaScript)

Source: https://github.com/rhashimoto/wa-sqlite/blob/master/demo/hello/index.html

This snippet demonstrates how to load a wa-sqlite script, either in the main window or a Web Worker, using a MessageChannel for communication. It handles user input for SQL queries and dynamically builds HTML tables to display the query results or errors. Dependencies include the wa-sqlite library and DOM manipulation.

```javascript
const { port1, port2 } = new MessageChannel();
const searchParams = new URLSearchParams(window.location.search);
if (searchParams.has('worker')) {
    document.querySelector('h1').textContent = 'Running in a Worker';
    const worker = new Worker('./hello.js', { type: 'module' });
    worker.postMessage('messagePort', [port2])
} else {
    document.querySelector('h1').textContent = 'Running in the Window';
    await import('./hello.js')
    window.postMessage('messagePort', '*', [port2]);
}

document.getElementById('submit').addEventListener('click', () => {
    port1.postMessage(document.getElementById('input').value);
});

port1.addEventListener('message', (event) => {
    console.log(event.data);
    const output = document.getElementById('output');
    output.innerHTML = '';
    if (Array.isArray(event.data)) {
        for (const { columns, rows } of event.data) {
            output.appendChild(buildHTMLTable(columns, rows));
        }
    } else {
        output.textContent = 'Error: ' + event.data.error;
    }
});
port1.start();

function buildHTMLTable(columns, rows) {
    function tx(tag, data) {
        const tx = document.createElement(tag);
        tx.textContent = data.toString();
        return tx;
    }
    const table = document.createElement('table');
    const thead = document.createElement('thead');
    const tr = document.createElement('tr');
    columns.forEach(column => tr.appendChild(tx('th', column)));
    thead.appendChild(tr);
    table.appendChild(thead);
    const tbody = document.createElement('tbody');
    rows.forEach(row => {
        const tr = document.createElement('tr');
        row.forEach(cell => tr.appendChild(tx('td', cell)));
        tbody.appendChild(tr);
    });
    table.appendChild(tbody);
    return table;
}
```

--------------------------------

### Load wa-sqlite Script in Window or Worker (JavaScript)

Source: https://github.com/rhashimoto/wa-sqlite/blob/master/demo/hello/hello.html

This snippet demonstrates how to conditionally load the wa-sqlite script, either directly in the main window or within a web worker, based on a URL query parameter. It utilizes `MessageChannel` for communication between the main thread and the worker, posting messages to the worker and handling incoming messages.

```javascript
// Load script in the Window or in a Worker, depending on whether // the URL contains a "worker" query parameter. Use a MessageChannel // to communicate with the script.
const { port1, port2 } = new MessageChannel();
const searchParams = new URLSearchParams(window.location.search);

if (searchParams.has('worker')) {
  document.querySelector('h1').textContent = 'Running in a Worker';
  const worker = new Worker('./hello.js', { type: 'module' });
  worker.postMessage('messagePort', [port2]);
} else {
  document.querySelector('h1').textContent = 'Running in the Window';
  await import('./hello.js');
  window.postMessage('messagePort', '*', [port2]);
}
```

--------------------------------

### Load and Use wa-sqlite API in JavaScript

Source: https://github.com/rhashimoto/wa-sqlite/blob/master/README.md

Demonstrates how to import and initialize the wa-sqlite library in a JavaScript environment. It shows the process of opening a database, executing a simple SQL query, and closing the connection. This is a fundamental example for interacting with the SQLite database via the wa-sqlite API.

```javascript
import SQLiteESMFactory from 'wa-sqlite/dist/wa-sqlite.mjs';
  import * as SQLite from 'wa-sqlite';

  async function hello() {
    const module = await SQLiteESMFactory();
    const sqlite3 = SQLite.Factory(module);
    const db = await sqlite3.open_v2('myDB');
    await sqlite3.exec(db, `SELECT 'Hello, world!'`, (row, columns) => {
      console.log(row);
    });
    await sqlite3.close(db);
  }

  hello();
```

--------------------------------

### Execute SQL and Display Results in HTML Table (JavaScript)

Source: https://github.com/rhashimoto/wa-sqlite/blob/master/demo/hello/hello.html

This snippet demonstrates how to submit SQL queries via a MessageChannel, receive results, and dynamically build HTML tables to display the data. It handles both successful array results and error messages. It relies on the `buildHTMLTable` function for rendering.

```javascript
// Submit SQL over the MessageChannel.
const submitButton = document.getElementById('submit');
const inputElement = document.getElementById('input');

submitButton.addEventListener('click', () => {
  port1.postMessage(inputElement.value);
});

// Handle query results.
port1.addEventListener('message', (event) => {
  console.log(event.data);
  const outputElement = document.getElementById('output');
  outputElement.innerHTML = '';
  if (Array.isArray(event.data)) {
    for (const { columns, rows } of event.data) {
      outputElement.appendChild(buildHTMLTable(columns, rows));
    }
  } else {
    outputElement.textContent = 'Error: ' + event.data.error;
  }
});

port1.start();

function buildHTMLTable(columns, rows) {
  function tx(tag, data) {
    const tx = document.createElement(tag);
    tx.textContent = data.toString();
    return tx;
  }
  const table = document.createElement('table');
  const thead = document.createElement('thead');
  const trHead = document.createElement('tr');
  columns.forEach(column => trHead.appendChild(tx('th', column)));
  thead.appendChild(trHead);
  table.appendChild(thead);
  const tbody = document.createElement('tbody');
  rows.forEach(row => {
    const trBody = document.createElement('tr');
    row.forEach(cell => trBody.appendChild(tx('td', cell)));
    tbody.appendChild(trBody);
  });
  table.appendChild(tbody);
  return table;
}
```

--------------------------------

### High-Performance File System Access with OPFS and wa-sqlite in Web Workers

Source: https://context7.com/rhashimoto/wa-sqlite/llms.txt

This example demonstrates using wa-sqlite with the Origin Private File System (OPFS) within a Web Worker for high-performance file system operations. It utilizes OPFSCoopSyncVFS for cooperative synchronous access, enabling efficient bulk inserts and queries. The code is intended to run in a worker script (`worker.js`). Dependencies include `wa-sqlite/dist/wa-sqlite.mjs` and `wa-sqlite/src/examples/OPFSCoopSyncVFS.js`.

```javascript
// worker.js - Must run in a Web Worker
import SQLiteESMFactory from 'wa-sqlite/dist/wa-sqlite.mjs';
import * as SQLite from 'wa-sqlite';
import { OPFSCoopSyncVFS } from 'wa-sqlite/src/examples/OPFSCoopSyncVFS.js';

(async () => {
  const module = await SQLiteESMFactory();
  const sqlite3 = SQLite.Factory(module);

  // OPFS with cooperative synchronous access
  const vfs = await OPFSCoopSyncVFS.create('opfs-vfs', module);
  sqlite3.vfs_register(vfs, true);

  const db = await sqlite3.open_v2('opfs-database.db');

  try {
    // High-performance operations
    await sqlite3.exec(db, `
      CREATE TABLE IF NOT EXISTS metrics (timestamp INTEGER, value REAL);
      BEGIN TRANSACTION;
    `);

    // Bulk insert
    for (let i = 0; i < 1000; i++) {
      await sqlite3.exec(db,
        `INSERT INTO metrics VALUES (${Date.now()}, ${Math.random()})`);
    }

    await sqlite3.exec(db, 'COMMIT');

    // Query results
    const results = [];
    await sqlite3.exec(db, 'SELECT COUNT(*) as count FROM metrics',
      (row, columns) => {
        results.push(row[0]);
      });

    postMessage({ type: 'result', count: results[0] });
  } catch (error) {
    postMessage({ type: 'error', message: error.message });
  } finally {
    await sqlite3.close(db);
  }
})();

```

--------------------------------

### Bind Collection of Values to Statement (JavaScript)

Source: https://github.com/rhashimoto/wa-sqlite/blob/master/docs/interfaces/SQLiteAPI.html

Binds values from an array or object to a prepared statement with placeholder parameters. Supports both numbered and named parameters. Note that SQLite bindings are indexed starting from 1, while array indices start from 0.

```javascript
const sql = 'INSERT INTO tbl VALUES (?, ?, ?)';
for await (const stmt of sqlite3.statements(db, sql)) {
  sqlite3.bind_collection(stmt, [42, 'hello', null]);
  // ...
}

const sqlNamed = 'INSERT INTO tbl VALUES (@foo, @bar, @baz)';
for await (const stmt of sqlite3.statements(db, sqlNamed)) {
  sqlite3.bind_collection(stmt, {
    '@foo': 42,
    '@bar': 'hello',
    '@baz': null,
  });
  // ...
}
```

--------------------------------

### Bind Value to Prepared Statement (wa-sqlite)

Source: https://github.com/rhashimoto/wa-sqlite/blob/master/docs/interfaces/SQLiteAPI.html

A convenience function that automatically selects the correct bind function based on the provided value's type. It simplifies the process of binding various data types to prepared statement parameters. Binding indices start from 1. The function returns SQLITE_OK upon successful execution, or throws an error if an issue occurs.

```typescript
bind(stmt: number, i: number, value: SQLiteCompatibleType): number;
```

--------------------------------

### Create SQLiteAPI Instance (JavaScript)

Source: https://github.com/rhashimoto/wa-sqlite/blob/master/docs/interfaces/SQLiteAPI.html

This snippet demonstrates how to import an ES6 module factory, instantiate the SQLite Emscripten module, and then use the factory to build the SQLiteAPI instance. It handles both synchronous and asynchronous builds and shows how to open a database.

```javascript
import SQLiteESMFactory from 'wa-sqlite/dist/wa-sqlite.mjs';
import * as SQLite from 'wa-sqlite';

(async function() {
  const module = await SQLiteESMFactory();
  const sqlite3 = SQLite.Factory(module);
  const db = await sqlite3.open_v2('myDB');
  // ... use the database
})();
```

--------------------------------

### column_bytes

Source: https://github.com/rhashimoto/wa-sqlite/blob/master/docs/interfaces/SQLiteAPI.html

Gets the storage size in bytes for a text or blob column.

```APIDOC
## column_bytes

### Description
Get storage size for column text or blob.

### Method
`column_bytes(stmt, i)`

### Parameters
#### Path Parameters
- **stmt** (number) - Required - prepared statement pointer
- **i** (number) - Required - column index

#### Query Parameters
None

#### Request Body
None

### Request Example
```json
{
  "stmt": 1,
  "i": 0
}
```

### Response
#### Success Response (200)
- **return value** (number) - number of bytes in column text or blob

#### Response Example
```json
{
  "bytes": 1024
}
```
```

--------------------------------

### Get Autocommit API

Source: https://github.com/rhashimoto/wa-sqlite/blob/master/docs/interfaces/SQLiteAPI.html

Tests whether the database is in autocommit mode.

```APIDOC
## get_autocommit

### Description
Test for autocommit mode.

### Method
(Implicitly a function call, not a standard HTTP method)

### Endpoint
N/A (This is a library function)

### Parameters
#### Path Parameters
None

#### Query Parameters
None

#### Request Body
None

### Parameters (Function Arguments)
- **db** (number) - Required - database pointer

### Request Example
```javascript
// Example usage (conceptual, actual implementation depends on context)
// const isAutocommit = get_autocommit(dbPointer);
```

### Response
#### Success Response (Returns)
- **number** - Non-zero if autocommit mode is on, zero otherwise.

#### Response Example
```javascript
// const isAutocommit = get_autocommit(dbPointer);
// if (isAutocommit) {
//   console.log("Database is in autocommit mode.");
// } else {
//   console.log("Database is not in autocommit mode.");
// }
```

#### See
[https://sqlite.org/c3ref/get_autocommit.html](https://sqlite.org/c3ref/get_autocommit.html)
```

--------------------------------

### Get Parameter Count (JavaScript)

Source: https://github.com/rhashimoto/wa-sqlite/blob/master/docs/interfaces/SQLiteAPI.html

Retrieves the number of binding parameters in a prepared statement. This function is useful for validating the number of parameters before binding values.

```javascript
const count = sqlite3.bind_parameter_count(stmt);
```

--------------------------------

### Get Parameter Name (JavaScript)

Source: https://github.com/rhashimoto/wa-sqlite/blob/master/docs/interfaces/SQLiteAPI.html

Retrieves the name of a binding parameter at a specified index in a prepared statement. Parameter indices begin at 1. This function is helpful for inspecting named parameters.

```javascript
const name = sqlite3.bind_parameter_name(stmt, i);
```

--------------------------------

### Theme Setting from Local Storage

Source: https://github.com/rhashimoto/wa-sqlite/blob/master/docs/types/SQLiteCompatibleType.html

Demonstrates how to retrieve the theme setting ('os', 'light', or 'dark') from local storage, defaulting to 'os' if not found. This setting is applied to the document element's dataset.

```javascript
document.documentElement.dataset.theme = localStorage.getItem("tsd-theme") || "os";
```

--------------------------------

### Initialize SQLite, Open DB, and Execute Queries (JavaScript)

Source: https://context7.com/rhashimoto/wa-sqlite/llms.txt

Initializes the wa-sqlite module, opens a SQLite database, and executes SQL commands including table creation, data insertion, and data retrieval. It handles potential errors and ensures the database is closed. This is useful for basic database interactions in web applications.

```javascript
import SQLiteESMFactory from 'wa-sqlite/dist/wa-sqlite.mjs';
import * as SQLite from 'wa-sqlite';

async function example() {
  // Initialize the SQLite module
  const module = await SQLiteESMFactory();
  const sqlite3 = SQLite.Factory(module);

  // Open a database (creates if doesn't exist)
  const db = await sqlite3.open_v2('myDB');

  try {
    // Execute SQL with callback for results
    await sqlite3.exec(db, `
      CREATE TABLE IF NOT EXISTS users (id INTEGER PRIMARY KEY, name TEXT);
      INSERT INTO users (name) VALUES ('Alice'), ('Bob');
      SELECT * FROM users;
    `, (row, columns) => {
      console.log('Row:', row);
      console.log('Columns:', columns);
    });
  } catch (error) {
    console.error('Database error:', error.message, 'Code:', error.code);
  } finally {
    // Always close the database
    await sqlite3.close(db);
  }
}

example();
```

--------------------------------

### SQLite API Methods

Source: https://github.com/rhashimoto/wa-sqlite/blob/master/docs/interfaces/SQLiteAPI.html

This section details the various methods available for interacting with SQLite, including binding parameters, executing statements, and retrieving data.

```APIDOC
## bind

### Description

Bind value to prepared statement. This convenience function calls the appropriate `bind_*` function based on the type of `value`. Note that binding indices begin with 1.

### Method

`bind(stmt, i, value)`

### Parameters

#### Path Parameters

*   **stmt** (number) - Required - prepared statement pointer
*   **i** (number) - Required - binding index
*   **value** ([SQLiteCompatibleType](../types/SQLiteCompatibleType.html)) - Required

#### Query Parameters

None

#### Request Body

None

### Request Example

```json
{
  "stmt": 1,
  "i": 1,
  "value": "example_string"
}
```

### Response

#### Success Response (200)

*   **return value** (number) - `SQLITE_OK` (throws exception on error)

#### Response Example

```json
{
  "status": "SQLITE_OK"
}
```

## bind_blob

### Description

Bind blob to prepared statement parameter. Note that binding indices begin with 1.

### Method

`bind_blob(stmt, i, value)`

### Parameters

#### Path Parameters

*   **stmt** (number) - Required - prepared statement pointer
*   **i** (number) - Required - binding index
*   **value** (number[] | Uint8Array) - Required

#### Query Parameters

None

#### Request Body

None

### Request Example

```json
{
  "stmt": 1,
  "i": 1,
  "value": [1, 2, 3, 4]
}
```

### Response

#### Success Response (200)

*   **return value** (number) - `SQLITE_OK` (throws exception on error)

#### Response Example

```json
{
  "status": "SQLITE_OK"
}
```

### See

[https://www.sqlite.org/c3ref/bind_blob.html](https://www.sqlite.org/c3ref/bind_blob.html)
```

--------------------------------

### wa-sqlite API Reference

Source: https://github.com/rhashimoto/wa-sqlite/blob/master/docs/interfaces/SQLiteAPI.html

This section provides a comprehensive reference for all functions available in the SQLiteAPI interface.

```APIDOC
## wa-sqlite Core API

### Description
This API provides a low-level interface for interacting with SQLite.

### `SQLiteAPI` Interface

#### Binding Functions

- **`bind(stmt, i, value): number`**: Binds a value to a prepared statement parameter.
- **`bind_blob(stmt, i, value): number`**: Binds a blob value to a prepared statement parameter.
- **`bind_collection(stmt, bindings): number`**: Binds multiple values to a prepared statement.
- **`bind_double(stmt, i, value): number`**: Binds a double value to a prepared statement parameter.
- **`bind_int(stmt, i, value): number`**: Binds an integer value to a prepared statement parameter.
- **`bind_int64(stmt, i, value): number`**: Binds a 64-bit integer value to a prepared statement parameter.
- **`bind_null(stmt, i): number`**: Binds a null value to a prepared statement parameter.
- **`bind_parameter_count(stmt): number`**: Returns the number of SQL parameters in the prepared statement.
- **`bind_parameter_name(stmt, i): string`**: Returns the name of the i-th parameter in the prepared statement.
- **`bind_text(stmt, i, value): number`**: Binds a text value to a prepared statement parameter.

#### Statement and Database Operations

- **`changes(db): number`**: Returns the number of rows modified by the most recent database operation.
- **`clear_bindings(stmt): number`**: Clears all parameter bindings for a prepared statement.
- **`close(db): Promise<number>`**: Closes the SQLite database connection.
- **`column(stmt, i): SQLiteCompatibleType`**: Retrieves the value of the i-th column in the current row of the result set.
- **`column_blob(stmt, i): Uint8Array`**: Retrieves the value of the i-th column as a blob.
- **`column_bytes(stmt, i): number`**: Returns the number of bytes in the i-th column value.
- **`column_count(stmt): number`**: Returns the number of columns in the result set.
- **`column_double(stmt, i): number`**: Retrieves the value of the i-th column as a double.
- **`column_int(stmt, i): number`**: Retrieves the value of the i-th column as an integer.
- **`column_int64(stmt, i): bigint`**: Retrieves the value of the i-th column as a 64-bit integer.
- **`column_name(stmt, i): string`**: Returns the name of the i-th column.
- **`column_names(stmt): string[]`**: Returns an array of all column names.
- **`column_text(stmt, i): string`**: Retrieves the value of the i-th column as text.
- **`column_type(stmt, i): number`**: Returns the data type of the i-th column.
- **`create_function(db, zFunctionName, nArg, eTextRep, pApp, xFunc?, xStep?, xFinal?): number`**: Registers a new SQL function.
- **`data_count(stmt): number`**: Returns the number of rows returned by the most recent query.
- **`exec(db, zSQL, callback?): Promise<number>`**: Executes an SQL statement.
- **`finalize(stmt): Promise<number>`**: Destroys a prepared statement.
- **`get_autocommit(db): number`**: Returns the autocommit status of the database.
- **`libversion(): string`**: Returns the SQLite library version string.
- **`libversion_number(): number`**: Returns the SQLite library version number.
- **`limit(db, id, newVal): number`**: Gets or sets a runtime limit.
- **`open_v2(zFilename, iFlags?, zVfs?): Promise<number>`**: Opens a SQLite database file.
- **`progress_handler(db, nProgressOps, handler, userData): any`**: Sets a progress handler.
- **`reset(stmt): Promise<number>`**: Resets a prepared statement for reuse.
- **`result(context, value): void`**: Sets the result of a user-defined function.
- **`result_blob(context, value): void`**: Sets the result of a user-defined function as a blob.
- **`result_double(context, value): void`**: Sets the result of a user-defined function as a double.
- **`result_int(context, value): void`**: Sets the result of a user-defined function as an integer.
- **`result_int64(context, value): void`**: Sets the result of a user-defined function as a 64-bit integer.
- **`result_null(context): void`**: Sets the result of a user-defined function as null.
- **`result_text(context, value): void`**: Sets the result of a user-defined function as text.
- **`row(stmt): SQLiteCompatibleType[]`**: Retrieves the current row of the result set as an array.
- **`set_authorizer(db, authFunction, userData): number`**: Sets an authorizer function.
- **`sql(stmt): string`**: Returns the SQL text of the prepared statement.
- **`statements(db, sql, options?): AsyncIterable<number>`**: Returns an async iterable of statement handles.
- **`step(stmt): Promise<number>`**: Executes the next step of a prepared statement.
- **`update_hook(db, callback): void`**: Registers an update hook callback.

#### Value Access Functions

- **`value(pValue): SQLiteCompatibleType`**: Retrieves the value of a function argument.
- **`value_blob(pValue): Uint8Array`**: Retrieves the value of a function argument as a blob.
- **`value_bytes(pValue): number`**: Returns the number of bytes in a function argument value.
- **`value_double(pValue): number`**: Retrieves the value of a function argument as a double.
- **`value_int(pValue): number`**: Retrieves the value of a function argument as an integer.
- **`value_int64(pValue): bigint`**: Retrieves the value of a function argument as a 64-bit integer.
- **`value_text(pValue): string`**: Retrieves the value of a function argument as text.
- **`value_type(pValue): number`**: Returns the data type of a function argument.

### See Also

- [SQLite C/C++ API Function Reference](https://sqlite.org/c3ref/funclist.html)
```

--------------------------------

### Implement Custom VFS with Javascript in wa-sqlite

Source: https://context7.com/rhashimoto/wa-sqlite/llms.txt

Demonstrates how to create a custom in-memory virtual file system (VFS) by extending the FacadeVFS class. This allows for custom file handling logic, such as in-memory storage, and is useful for testing or specific application needs. It requires implementing methods like jOpen, jRead, and jWrite.

```javascript
import { FacadeVFS } from 'wa-sqlite/src/FacadeVFS.js';
import * as VFS from 'wa-sqlite/src/VFS.js';

class CustomMemoryVFS extends FacadeVFS {
  files = new Map();
  fileIdCounter = 1;

  constructor(name, module) {
    super(name, module);
  }

  async jOpen(filename, fileId, flags, pOutFlags) {
    const path = filename || `temp-${this.fileIdCounter++}`;

    // Create file if needed
    if (flags & VFS.SQLITE_OPEN_CREATE) {
      if (!this.files.has(path)) {
        this.files.set(path, {
          data: new Uint8Array(0),
          size: 0
        });
      }
    }

    if (!this.files.has(path)) {
      return VFS.SQLITE_CANTOPEN;
    }

    // Store file handle mapping
    this.files.get(path).fileId = fileId;
    pOutFlags.setInt32(0, flags, true);
    return VFS.SQLITE_OK;
  }

  async jRead(fileId, pData, iAmt, iOffset) {
    const file = Array.from(this.files.values())
      .find(f => f.fileId === fileId);

    if (!file) return VFS.SQLITE_IOERR_READ;

    const end = Math.min(iOffset + iAmt, file.size);
    const bytesRead = end - iOffset;

    if (bytesRead < iAmt) {
      // Short read - fill with zeros
      const buffer = this._module.HEAPU8.subarray(pData, pData + iAmt);
      buffer.fill(0);
      buffer.set(file.data.subarray(iOffset, end));
      return VFS.SQLITE_IOERR_SHORT_READ;
    }

    this._module.HEAPU8.set(
      file.data.subarray(iOffset, end),
      pData
    );
    return VFS.SQLITE_OK;
  }

  async jWrite(fileId, pData, iAmt, iOffset) {
    const file = Array.from(this.files.values())
      .find(f => f.fileId === fileId);

    if (!file) return VFS.SQLITE_IOERR_WRITE;

    const newSize = Math.max(file.size, iOffset + iAmt);
    if (newSize > file.data.length) {
      // Grow buffer
      const newData = new Uint8Array(newSize);
      newData.set(file.data);
      file.data = newData;
    }

    file.data.set(
      this._module.HEAPU8.subarray(pData, pData + iAmt),
      iOffset
    );
    file.size = newSize;
    return VFS.SQLITE_OK;
  }

  getFilename(fileId) {
    const entry = Array.from(this.files.entries())
      .find(([_, file]) => file.fileId === fileId);
    return entry ? entry[0] : 'unknown';
  }
}

// Usage
import SQLiteESMFactory from 'wa-sqlite/dist/wa-sqlite-async.mjs';
import * as SQLite from 'wa-sqlite';

async function useCustomVFS() {
  const module = await SQLiteESMFactory();
  const sqlite3 = SQLite.Factory(module);

  const vfs = new CustomMemoryVFS('custom-vfs', module);
  sqlite3.vfs_register(vfs, true);

  const db = await sqlite3.open_v2('custom.db');

  try {
    await sqlite3.exec(db, 'CREATE TABLE test (id INTEGER, value TEXT)');
    await sqlite3.exec(db, "INSERT INTO test VALUES (1, 'Custom VFS')");
    await sqlite3.exec(db, 'SELECT * FROM test', (row) => console.log(row));
  } finally {
    await sqlite3.close(db);
  }
}

useCustomVFS();

```

--------------------------------

### Simulate write hint with PRAGMA

Source: https://github.com/rhashimoto/wa-sqlite/blob/master/demo/write-hint/index.html

This snippet simulates passing a write transaction hint using the PRAGMA command in SQLite. It's used within a transaction to check and potentially insert data, ensuring synchronization.

```sql
PRAGMA write_hint; -- simulate fcntl write transaction hint
WITH cnt AS (
  SELECT COUNT() AS n FROM t
)
INSERT INTO t SELECT * FROM cnt WHERE n < 500 RETURNING *;
```

--------------------------------

### SQLiteVFS Interface

Source: https://github.com/rhashimoto/wa-sqlite/blob/master/docs/interfaces/SQLiteVFS.html

The SQLiteVFS interface defines the methods required for a custom virtual file system that can be registered with SQLiteAPI.vfs_register.

```APIDOC
## SQLiteVFS Interface

### Description

Objects with this interface can be passed to [SQLiteAPI.vfs_register](SQLiteAPI.html#vfs_register) to define a new filesystem. Examples include synchronous [MemoryVFS.js](https://github.com/rhashimoto/wa-sqlite/blob/master/src/examples/MemoryVFS.js), and asynchronous [MemoryAsyncVFS.js](https://github.com/rhashimoto/wa-sqlite/blob/master/src/examples/MemoryAsyncVFS.js) and [IndexedDbVFS.js](https://github.com/rhashimoto/wa-sqlite/blob/master/src/examples/IndexedDbVFS.js).

### See

*   [https://sqlite.org/vfs.html](https://sqlite.org/vfs.html)
*   [https://sqlite.org/c3ref/io_methods.html](https://sqlite.org/c3ref/io_methods.html)

### Properties

#### `Optional` mxPathName

- **mxPathName** (number) - Optional - Maximum length of a file path in UTF-8 bytes (default 64)

### Methods

#### xAccess

- **Description**: Checks if a file or directory meets certain attributes.
- **Method**: Not specified (interface method).
- **Endpoint**: Not applicable (interface method).
- **Parameters**:
  - **Path Parameters**: None
  - **Query Parameters**: None
  - **Request Body**: None
- **Request Example**: None
- **Response**:
  - **Success Response (0 or non-zero)**: Returns 0 on success, or an error code.
- **Response Example**: None

#### xCheckReservedLock

- **Description**: Checks if a reserved lock is in place.
- **Method**: Not specified (interface method).
- **Endpoint**: Not applicable (interface method).
- **Parameters**:
  - **Path Parameters**: None
  - **Query Parameters**: None
  - **Request Body**: None
- **Request Example**: None
- **Response**:
  - **Success Response (0 or non-zero)**: Returns 0 on success, or an error code.
- **Response Example**: None

#### xClose

- **Description**: Closes a file descriptor.
- **Method**: Not specified (interface method).
- **Endpoint**: Not applicable (interface method).
- **Parameters**:
  - **Path Parameters**: None
  - **Query Parameters**: None
  - **Request Body**: None
- **Request Example**: None
- **Response**:
  - **Success Response (0 or non-zero)**: Returns 0 on success, or an error code.
- **Response Example**: None

#### xDelete

- **Description**: Deletes a file or directory.
- **Method**: Not specified (interface method).
- **Endpoint**: Not applicable (interface method).
- **Parameters**:
  - **Path Parameters**: None
  - **Query Parameters**: None
  - **Request Body**: None
- **Request Example**: None
- **Response**:
  - **Success Response (0 or non-zero)**: Returns 0 on success, or an error code.
- **Response Example**: None

#### xDeviceCharacteristics

- **Description**: Returns the device characteristics.
- **Method**: Not specified (interface method).
- **Endpoint**: Not applicable (interface method).
- **Parameters**:
  - **Path Parameters**: None
  - **Query Parameters**: None
  - **Request Body**: None
- **Request Example**: None
- **Response**:
  - **Success Response (number)**: Returns the device characteristics.
- **Response Example**: None

#### xFileControl

- **Description**: Performs a file control operation.
- **Method**: Not specified (interface method).
- **Endpoint**: Not applicable (interface method).
- **Parameters**:
  - **Path Parameters**: None
  - **Query Parameters**: None
  - **Request Body**: None
- **Request Example**: None
- **Response**:
  - **Success Response (0 or non-zero)**: Returns 0 on success, or an error code.
- **Response Example**: None

#### xFileSize

- **Description**: Returns the size of the file.
- **Method**: Not specified (interface method).
- **Endpoint**: Not applicable (interface method).
- **Parameters**:
  - **Path Parameters**: None
  - **Query Parameters**: None
  - **Request Body**: None
- **Request Example**: None
- **Response**:
  - **Success Response (number)**: Returns the file size.
- **Response Example**: None

#### xLock

- **Description**: Acquires a lock on the file.
- **Method**: Not specified (interface method).
- **Endpoint**: Not applicable (interface method).
- **Parameters**:
  - **Path Parameters**: None
  - **Query Parameters**: None
  - **Request Body**: None
- **Request Example**: None
- **Response**:
  - **Success Response (0 or non-zero)**: Returns 0 on success, or an error code.
- **Response Example**: None

#### xOpen

- **Description**: Opens a file.
- **Method**: Not specified (interface method).
- **Endpoint**: Not applicable (interface method).
- **Parameters**:
  - **Path Parameters**: None
  - **Query Parameters**: None
  - **Request Body**: None
- **Request Example**: None
- **Response**:
  - **Success Response (0 or non-zero)**: Returns 0 on success, or an error code.
- **Response Example**: None

#### xRead

- **Description**: Reads data from the file.
- **Method**: Not specified (interface method).
- **Endpoint**: Not applicable (interface method).
- **Parameters**:
  - **Path Parameters**: None
  - **Query Parameters**: None
  - **Request Body**: None
- **Request Example**: None
- **Response**:
  - **Success Response (0 or non-zero)**: Returns 0 on success, or an error code.
- **Response Example**: None

#### xSync

- **Description**: Synchronizes the file to disk.
- **Method**: Not specified (interface method).
- **Endpoint**: Not applicable (interface method).
- **Parameters**:
  - **Path Parameters**: None
  - **Query Parameters**: None
  - **Request Body**: None
- **Request Example**: None
- **Response**:
  - **Success Response (0 or non-zero)**: Returns 0 on success, or an error code.
- **Response Example**: None

#### xTruncate

- **Description**: Truncates the file to a specified size.
- **Method**: Not specified (interface method).
- **Endpoint**: Not applicable (interface method).
- **Parameters**:
  - **Path Parameters**: None
  - **Query Parameters**: None
  - **Request Body**: None
- **Request Example**: None
- **Response**:
  - **Success Response (0 or non-zero)**: Returns 0 on success, or an error code.
- **Response Example**: None

#### xUnlock

- **Description**: Releases a lock on the file.
- **Method**: Not specified (interface method).
- **Endpoint**: Not applicable (interface method).
- **Parameters**:
  - **Path Parameters**: None
  - **Query Parameters**: None
  - **Request Body**: None
- **Request Example**: None
- **Response**:
  - **Success Response (0 or non-zero)**: Returns 0 on success, or an error code.
- **Response Example**: None

#### xWrite

- **Description**: Writes data to the file.
- **Method**: Not specified (interface method).
- **Endpoint**: Not applicable (interface method).
- **Parameters**:
  - **Path Parameters**: None
  - **Query Parameters**: None
  - **Request Body**: None
- **Request Example**: None
- **Response**:
  - **Success Response (0 or non-zero)**: Returns 0 on success, or an error code.
- **Response Example**: None
```

--------------------------------

### Database Connection Management

Source: https://github.com/rhashimoto/wa-sqlite/blob/master/docs/interfaces/SQLiteAPI.html

Provides functions for opening new database connections and managing them.

```APIDOC
## POST /open_v2

### Description
Opens a new database connection. This function returns a Promise-wrapped database pointer.

### Method
POST

### Endpoint
/open_v2

### Parameters
#### Request Body
- **zFilename** (string) - Required - The name of the database file.
- **iFlags** (number) - Optional - Flags for opening the database. Defaults to `SQLite.SQLITE_OPEN_CREATE | SQLite.SQLITE_OPEN_READWRITE` (0x6).
- **zVfs** (string) - Optional - The name of the VFS to use.

### Request Example
```json
{
  "zFilename": "my_database.db",
  "iFlags": 6,
  "zVfs": "my_vfs"
}
```

### Response
#### Success Response (200)
- **dbPointer** (number) - A Promise that resolves to the database pointer.

#### Response Example
```json
{
  "dbPointer": 12345
}
```
```

--------------------------------

### vfs_register

Source: https://github.com/rhashimoto/wa-sqlite/blob/master/docs/interfaces/SQLiteAPI.html

Registers a new Virtual File System (VFS) with SQLite.

```APIDOC
## vfs_register

### Description
Register a new Virtual File System.

### Method
Not applicable (function signature provided)

### Endpoint
Not applicable (function signature provided)

### Parameters
#### Path Parameters
None

#### Query Parameters
None

#### Request Body
None

### Request Example
```javascript
// Example usage (assuming vfs is a valid SQLiteVFS object)
// const result = vfs_register(vfs, true);
```

### Response
#### Success Response
- **result** (number) - `SQLITE_OK` on success.

#### Response Example
```json
{
  "result": 0 // Assuming 0 represents SQLITE_OK
}
```
```

--------------------------------

### Implement Access Control with Authorizer in JavaScript

Source: https://context7.com/rhashimoto/wa-sqlite/llms.txt

Shows how to use the authorizer callback in wa-sqlite to implement fine-grained access control for database operations. This requires the 'wa-sqlite' library. The authorizer function intercepts actions and can allow or deny them based on defined rules.

```javascript
import SQLiteESMFactory from 'wa-sqlite/dist/wa-sqlite-async.mjs';
import * as SQLite from 'wa-sqlite';

async function accessControl() {
  const module = await SQLiteESMFactory();
  const sqlite3 = SQLite.Factory(module);
  const db = await sqlite3.open_v2('myDB');

  try {
    await sqlite3.exec(db, `
      CREATE TABLE public_data (id INTEGER, value TEXT);
      CREATE TABLE private_data (id INTEGER, secret TEXT);
      INSERT INTO public_data VALUES (1, 'Public info');
      INSERT INTO private_data VALUES (1, 'Secret info');
    `);

    // Set authorizer to block access to private_data
    sqlite3.set_authorizer(db,
      (userData, actionCode, param1, param2, dbName, triggerOrView) => {
        // Block any access to private_data table
        if (param1 === 'private_data') {
          console.log(`Access denied to ${param1}`);
          return SQLite.SQLITE_DENY;
        }

        // Allow DELETE only on specific tables
        if (actionCode === SQLite.SQLITE_DELETE && param1 === 'public_data') {
          console.log(`DELETE blocked on ${param1}`);
          return SQLite.SQLITE_DENY;
        }

        return SQLite.SQLITE_OK;
      },
      null
    );

    // This succeeds
    await sqlite3.exec(db, 'SELECT * FROM public_data',
      (row) => console.log('Public:', row));

    // This fails with authorization error
    try {
      await sqlite3.exec(db, 'SELECT * FROM private_data',
        (row) => console.log('Private:', row));
    } catch (error) {
      console.error('Blocked:', error.message);
    }
  } finally {
    await sqlite3.close(db);
  }
}

accessControl();

```

--------------------------------

### Prepared Statements with Parameter Binding (JavaScript)

Source: https://context7.com/rhashimoto/wa-sqlite/llms.txt

Demonstrates how to use prepared statements with parameter binding in wa-sqlite for efficient and secure SQL query execution. It covers inserting data using both positional (?) and named (:name) parameters, and retrieving data with filtering. This is crucial for preventing SQL injection and optimizing query performance.

```javascript
import SQLiteESMFactory from 'wa-sqlite/dist/wa-sqlite.mjs';
import * as SQLite from 'wa-sqlite';

async function preparedStatements() {
  const module = await SQLiteESMFactory();
  const sqlite3 = SQLite.Factory(module);
  const db = await sqlite3.open_v2('myDB');

  try {
    // Create table
    await sqlite3.exec(db, 'CREATE TABLE products (id INTEGER PRIMARY KEY, name TEXT, price REAL)');

    // Insert with parameter binding using array
    for await (const stmt of sqlite3.statements(db, 'INSERT INTO products (name, price) VALUES (?, ?)')) {
      sqlite3.bind_collection(stmt, ['Laptop', 999.99]);
      await sqlite3.step(stmt);
      await sqlite3.reset(stmt);

      sqlite3.bind_collection(stmt, ['Mouse', 29.99]);
      await sqlite3.step(stmt);
    }

    // Query with named parameters
    for await (const stmt of sqlite3.statements(db, 'SELECT * FROM products WHERE price > :min_price')) {
      sqlite3.bind_collection(stmt, { ':min_price': 50 });

      while (await sqlite3.step(stmt) === SQLite.SQLITE_ROW) {
        const row = sqlite3.row(stmt);
        const columns = sqlite3.column_names(stmt);
        console.log(columns, row);
      }
    }
  } finally {
    await sqlite3.close(db);
  }
}

preparedStatements();
```

--------------------------------

### sql

Source: https://github.com/rhashimoto/wa-sqlite/blob/master/docs/interfaces/SQLiteAPI.html

Retrieves the SQL statement from a prepared statement pointer. Returns the SQL as a string.

```APIDOC
## sql

### Description
Retrieves the statement SQL from a prepared statement step.

### Method
Not applicable (function signature)

### Endpoint
Not applicable (function signature)

### Parameters
#### Path Parameters
None

#### Query Parameters
None

#### Request Body
None

### Parameters
*   **stmt** (number) - Required - prepared statement pointer

### Request Example
```json
{
  "stmt": 456
}
```

### Response
#### Success Response (200)
*   **SQL** (string) - The SQL string associated with the prepared statement.

#### Response Example
```json
{
  "sql": "SELECT * FROM my_table WHERE id = ?"
}
```
```

--------------------------------

### Working with Different Data Types in SQLite (JavaScript)

Source: https://context7.com/rhashimoto/wa-sqlite/llms.txt

Illustrates how to handle various data types including integers, floats, text, blobs (Uint8Array), bigints, and NULL values when interacting with SQLite using wa-sqlite. It shows binding different types to prepared statements and retrieving data with automatic type detection. This is essential for storing and manipulating diverse data formats.

```javascript
import SQLiteESMFactory from 'wa-sqlite/dist/wa-sqlite.mjs';
import * as SQLite from 'wa-sqlite';

async function dataTypes() {
  const module = await SQLiteESMFactory();
  const sqlite3 = SQLite.Factory(module);
  const db = await sqlite3.open_v2('myDB');

  try {
    await sqlite3.exec(db, `
      CREATE TABLE data (
        id INTEGER,
        amount REAL,
        description TEXT,
        binary_data BLOB,
        big_number INTEGER,
        nullable TEXT
      )
    `);

    for await (const stmt of sqlite3.statements(db,
      'INSERT INTO data VALUES (?, ?, ?, ?, ?, ?)')) {

      // Bind different data types
      sqlite3.bind(stmt, 1, 42);                              // integer
      sqlite3.bind(stmt, 2, 3.14159);                         // float
      sqlite3.bind(stmt, 3, 'Hello, SQLite!');               // text
      sqlite3.bind(stmt, 4, new Uint8Array([1, 2, 3, 4]));  // blob
      sqlite3.bind(stmt, 5, 9007199254740991n);              // bigint
      sqlite3.bind(stmt, 6, null);                            // NULL

      await sqlite3.step(stmt);
    }

    // Retrieve with automatic type detection
    await sqlite3.exec(db, 'SELECT * FROM data', (row, columns) => {
      row.forEach((value, i) => {
        const type = typeof value;
        console.log(`${columns[i]}: ${value} (${type})`);
      });
    });
  } finally {
    await sqlite3.close(db);
  }
}

dataTypes();
```

--------------------------------

### set_authorizer

Source: https://github.com/rhashimoto/wa-sqlite/blob/master/docs/interfaces/SQLiteAPI.html

Registers a callback function to authorize SQL statement actions. It takes a database pointer, an authorization function, and user data.

```APIDOC
## set_authorizer

### Description
Registers a callback function that is invoked to authorize certain SQL statement actions.

### Method
Not applicable (function signature)

### Endpoint
Not applicable (function signature)

### Parameters
#### Path Parameters
None

#### Query Parameters
None

#### Request Body
None

### Parameters
*   **db** (number) - Required - database pointer
*   **authFunction** ((userData, iActionCode, param3, param4, param5, param6) => number | Promise<number>) - Required - The callback function to authorize actions.
    *   **userData** (any) - Optional - User-defined data passed to the authorization function.
    *   **iActionCode** (number) - Required - The action code being authorized.
    *   **param3** (string) - Optional - First parameter for the action code.
    *   **param4** (string) - Optional - Second parameter for the action code.
    *   **param5** (string) - Optional - Third parameter for the action code.
    *   **param6** (string) - Optional - Fourth parameter for the action code.
    *   **Returns** (number | Promise<number>) - Returns 0 to deny, 1 to allow, or 2 to rollback.
*   **userData** (any) - Optional - User data to be passed to the authorization function.

### Request Example
```json
{
  "db": 789,
  "authFunction": "(userData, iActionCode, param3, param4, param5, param6) => {
    console.log(`Authorizing action: ${iActionCode}`);
    return 1; // Allow
  }",
  "userData": { "userId": "abc" }
}
```

### Response
#### Success Response (200)
*   **Return value** (number) - Returns 0 on success, or an error code.

#### Response Example
```json
{
  "returnValue": 0
}
```
```