SQLocal

https://sqlocal.dev/
Admin
SQLocal is a local-first database that runs SQLite3 in the browser, backed by the origin private...
Tokens:29,938
Snippets:310
Trust Score:6.2
Update:5 months ago
Show doc for...
Tokens:
Raw
### Install SQLocal and Kysely

Source: https://sqlocal.dev/kysely/setup

Install the necessary packages using your preferred package manager.

```sh
npm install sqlocal kysely
```

```sh
yarn add sqlocal kysely
```

```sh
pnpm install sqlocal kysely
```

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

### Install SQLocal Package

Source: https://sqlocal.dev/guide/setup

Install the SQLocal package using npm, yarn, or pnpm.

```sh
npm install sqlocal
```

```sh
yarn add sqlocal
```

```sh
pnpm install sqlocal
```

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

### Install Drizzle ORM and SQLocal

Source: https://sqlocal.dev/drizzle/setup

Install the necessary packages using npm, yarn, or pnpm.

```sh
npm install sqlocal drizzle-orm
```

```sh
yarn add sqlocal drizzle-orm
```

```sh
pnpm install sqlocal drizzle-orm
```

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

### Initialize SQLocal and Destructure getDatabaseInfo

Source: https://sqlocal.dev/api/getdatabaseinfo

Access or destructure `getDatabaseInfo` from the `SQLocal` client. This is the initial setup required before calling the method.

```javascript
import { SQLocal } from 'sqlocal';

const { getDatabaseInfo } = new SQLocal('database.sqlite3');
```

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

### Overwrite Database with Callback for Migrations

Source: https://sqlocal.dev/api/overwritedatabasefile

Provide a callback function to overwriteDatabaseFile to execute migrations or other setup tasks immediately after the database is overwritten and before other SQLocal clients reconnect.

```javascript
await overwriteDatabaseFile(databaseFile, async () => {
	// Run your migrations
});
```

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

### Call getDatabaseInfo to Retrieve Database Details

Source: https://sqlocal.dev/api/getdatabaseinfo

Call the `getDatabaseInfo` method without arguments to get a Promise resolving to an object containing database file information. This includes path, size, storage type, and persistence.

```javascript
const databaseInfo = await getDatabaseInfo();
```

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

### Get Database File with SQLocal

Source: https://sqlocal.dev/reference/drizzle/classes/SQLocalDrizzle

Retrieves the SQLite database file as a `File` object, which can be used for uploading to a server or downloading by the user.

```typescript
getDatabaseFile(): Promise<File>;
```

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

### Create a temporary SQL trigger

Source: https://sqlocal.dev/api/createcallbackfunction

Use SQL to create a temporary trigger that calls your custom JavaScript callback function. This example logs insertions into the 'groceries' table.

```sql
await sql`
  CREATE TEMP TRIGGER logGroceriesInsert AFTER INSERT ON groceries
  BEGIN
    SELECT logInsert('groceries', new.name);
  END
`;
```

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

### Import SQLocal and createWindowFunction

Source: https://sqlocal.dev/api/createwindowfunction

Import the SQLocal client and destructure the createWindowFunction method. Initialize the SQLocal client with your database file.

```javascript
import { SQLocal } from 'sqlocal';

const { createWindowFunction } = new SQLocal('database.sqlite3');
```

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

### Constructor

Source: https://sqlocal.dev/reference/index/classes/SQLocalProcessor

Initializes a new instance of SQLocalProcessor.

```APIDOC
## Constructor

### Description
Initializes a new instance of SQLocalProcessor.

### Parameters
#### Parameters
- **driver** (`SQLocalDriver`) - Description of the driver parameter.

### Returns
`SQLocalProcessor` - An instance of SQLocalProcessor.
```

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

### Initialize SQLocal Client

Source: https://sqlocal.dev/guide/setup

Import the SQLocal class and initialize your client by passing the database file name to the constructor. The file will be created if it doesn't exist.

```javascript
import { SQLocal } from 'sqlocal';

export const db = new SQLocal('database.sqlite3');
```

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

### Get Database Info with SQLocal

Source: https://sqlocal.dev/reference/drizzle/classes/SQLocalDrizzle

Retrieves information about the SQLite database file, such as its size and last modified date.

```typescript
getDatabaseInfo(): Promise<DatabaseInfo>;
```

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

### Initialize SQLocal and destructure destroy

Source: https://sqlocal.dev/api/destroy

Import `SQLocal` and initialize a new client, then destructure the `destroy` method for direct access.

```javascript
import { SQLocal } from 'sqlocal';

const { destroy } = new SQLocal('database.sqlite3');
```

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

### Sqlite3Method Type Alias Definition

Source: https://sqlocal.dev/reference/index/type-aliases/Sqlite3Method

Defines the set of valid methods for executing SQL statements. Use these string literals to specify the type of operation (get, all, run, values).

```typescript
type Sqlite3Method = "get" | "all" | "run" | "values";
```

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

### Instantiate SQLocalProcessor

Source: https://sqlocal.dev/reference/index/classes/SQLocalProcessor

Create a new instance of SQLocalProcessor by passing a driver object. This is the entry point for database interactions.

```typescript
new SQLocalProcessor(driver)
```

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

### Initialize SQLocal Client and Access Transaction

Source: https://sqlocal.dev/api/transaction

Import and initialize the SQLocal client, then destructure the `transaction` method for use.

```javascript
import { SQLocal } from 'sqlocal';

const { transaction } = new SQLocal('database.sqlite3');
```

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

### Initialize SQLocal with Reactive Queries Enabled

Source: https://sqlocal.dev/api/reactivequery

Import and initialize SQLocal with the 'reactive' option set to true to enable reactive queries. Access 'reactiveQuery' from the client instance.

```javascript
import { SQLocal } from 'sqlocal';

const { reactiveQuery } = new SQLocal({
	databasePath: 'database.sqlite3',
	reactive: true,
});
```

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

### Use aggregate function in HAVING clause

Source: https://sqlocal.dev/api/createaggregatefunction

Demonstrates using the previously defined 'mostCommon' aggregate function within a SQL query's `HAVING` clause to filter groups. This example finds days of the week where 'Cleaning' is the most common task category.

```sql
SELECT dayOfWeek
FROM tasks
GROUP BY dayOfWeek
HAVING mostCommon(category) = 'Cleaning'
```

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

### Initialize SQLocal with Options

Source: https://sqlocal.dev/guide/setup

Initialize the SQLocal client with additional options such as read-only mode, verbose logging, reactivity, and callbacks for initialization and connection events.

```javascript
export const db = new SQLocal({
	databasePath: 'database.sqlite3',
	readOnly: true,
	verbose: true,
	reactive: true,
	onInit: (sql) => {},
	onConnect: (reason) => {},
});
```

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

### init

Source: https://sqlocal.dev/reference/index/interfaces/SQLocalDriver

Initializes the SQLocal driver with the provided configuration. This method must be called before any other driver operations.

```APIDOC
## init

### Description
Initializes the SQLocal driver with the provided configuration.

### Method
`init(config: DriverConfig): Promise<void>`

### Parameters
#### Path Parameters
None

#### Query Parameters
None

#### Request Body
- **config** (DriverConfig) - Required - The configuration object for the driver.

### Request Example
```typescript
await driver.init({
  // config options
});
```

### Response
#### Success Response (200)
- **void** - Indicates successful initialization.

#### Response Example
```json
// No response body for successful initialization
```
```

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

### Initialize SQLocal Client

Source: https://sqlocal.dev/api/batch

Import and initialize the SQLocal client, destructuring the batch function for use.

```javascript
import { SQLocal } from 'sqlocal';

const { batch } = new SQLocal('database.sqlite3');
```

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

### Import SQLocal Client

Source: https://sqlocal.dev/api/deletedatabasefile

Import the SQLocal class and initialize a client instance. Destructure `deleteDatabaseFile` for direct use.

```javascript
import { SQLocal } from 'sqlocal';

const { deleteDatabaseFile } = new SQLocal('database.sqlite3');
```

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

### Initialize SQLocal Client

Source: https://sqlocal.dev/api/sql

Import and initialize the SQLocal client to access the `sql` tagged template literal.

```javascript
import { SQLocal } from 'sqlocal';

const { sql } = new SQLocal('database.sqlite3');
```

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

### Initialize SQLocal Client

Source: https://sqlocal.dev/api/getdatabasefile

Import and initialize the SQLocal client to access its methods. Destructure getDatabaseFile for direct use.

```javascript
import { SQLocal } from 'sqlocal';

const { getDatabaseFile } = new SQLocal('database.sqlite3');
```

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

### Initializing SQLocal with Reactive Queries

Source: https://sqlocal.dev/api/reactivequery

To use reactiveQuery, initialize the SQLocal client with the 'reactive' option set to true.

```APIDOC
## Initialize SQLocal with Reactive Queries

### Description
To enable the `reactiveQuery` feature, the `SQLocal` client must be initialized with the `reactive` option set to `true`.

### Code Example
```javascript
import { SQLocal } from 'sqlocal';

const { reactiveQuery } = new SQLocal({
	databasePath: 'database.sqlite3',
	reactive: true,
});
```
```

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

### import

Source: https://sqlocal.dev/reference/index/interfaces/SQLocalDriver

Imports a database from an ArrayBuffer, Uint8Array, or ReadableStream.

```APIDOC
## import

### Description
Imports a database from an ArrayBuffer, Uint8Array, or ReadableStream.

### Method
`import(database: ArrayBuffer | Uint8Array<ArrayBuffer> | ReadableStream<Uint8Array<ArrayBuffer>>): Promise<void>`

### Parameters
#### Path Parameters
None

#### Query Parameters
None

#### Request Body
- **database** (ArrayBuffer | Uint8Array<ArrayBuffer> | ReadableStream<Uint8Array<ArrayBuffer>>) - Required - The database content to import.

### Request Example
```typescript
const response = await fetch('/path/to/database.db');
const arrayBuffer = await response.arrayBuffer();
await driver.import(arrayBuffer);
```

### Response
#### Success Response (200)
- **void** - Indicates successful import.

#### Response Example
```json
// No response body for successful import
```
```

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

### Initialize SQLocalKysely Dialect

Source: https://sqlocal.dev/kysely/setup

Initialize SQLocalKysely and pass its dialect to a new Kysely instance. This sets up the connection to your SQLite database.

```typescript
import { SQLocalKysely } from 'sqlocal/kysely';
import { Kysely } from 'kysely';

const { dialect } = new SQLocalKysely('database.sqlite3');
export const db = new Kysely({ dialect });
```

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

### Basic SQLocal Usage

Source: https://sqlocal.dev/guide/introduction

Create a SQLocal client, execute SQL statements using tagged template literals, and insert/select data. Ensure the database file is named appropriately for storage in the origin private file system.

```javascript
import { SQLocal } from 'sqlocal';

// Create a client with a name for the SQLite file to save in
// the origin private file system
const { sql } = new SQLocal('database.sqlite3');

// Use the "sql" tagged template to execute a SQL statement
// against the SQLite database
await sql`CREATE TABLE groceries (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT)`;

// Execute a parameterized statement just by inserting
// parameters in the SQL string
const items = ['bread', 'milk', 'rice'];
for (let item of items) {
	await sql`INSERT INTO groceries (name) VALUES (${item})`;
}

// SELECT queries and queries with the RETURNING clause will
// return the matched records as an array of objects
const data = await sql`SELECT * FROM groceries`;
console.log(data);
```

```javascript
[
	{ id: 1, name: 'bread' },
	{ id: 2, name: 'milk' },
	{ id: 3, name: 'rice' },
];
```

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

### Download Database File to User

Source: https://sqlocal.dev/api/getdatabasefile

Create a downloadable link for the database file. This method generates a temporary URL and simulates a click event to initiate the download.

```javascript
const databaseFile = await getDatabaseFile();
const fileUrl = URL.createObjectURL(databaseFile);

const a = document.createElement('a');
a.href = fileUrl;
a.download = 'database.sqlite3';
a.click();
a.remove();

URL.revokeObjectURL(fileUrl);
```

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

### Nano Stores: SQL Integration

Source: https://sqlocal.dev/api/reactivequery

Use the `@nanostores/sql` package to integrate SQLocal with Nano Stores. This allows you to create reactive stores from SQL queries using `reactiveQuery`.

```typescript
import { openDb } from '@nanostores/sql';
import { sqlocalDriver } from '@nanostores/sql/sqlocal';

const db = openDb(sqlocalDriver('database.sqlite3'));
const $groceries = db.store`SELECT * FROM groceries`;
```

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

### Instantiate SQLocal with Configuration

Source: https://sqlocal.dev/reference/index/classes/SQLocal

Use this constructor to initialize SQLocal with a custom configuration object.

```typescript
new SQLocal(config)
```

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

### Import SQLocal and createCallbackFunction

Source: https://sqlocal.dev/api/createcallbackfunction

Import the `SQLocal` class and destructure `createCallbackFunction` when initializing the client. This sets up the necessary connection to your SQLite database.

```javascript
import { SQLocal } from 'sqlocal';

const { createCallbackFunction } = new SQLocal('database.sqlite3');
```

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

### SQLocalKysely Constructor

Source: https://sqlocal.dev/reference/kysely/classes/SQLocalKysely

Initializes a new instance of the SQLocalKysely class. It can be constructed with either a database path or a configuration object.

```APIDOC
## Constructor

```ts
new SQLocalKysely(databasePath): SQLocalKysely;
```

### Parameters

* `databasePath` (DatabasePath) - Description of the database path parameter.

### Returns

`SQLocalKysely`

## Constructor

```ts
new SQLocalKysely(config): SQLocalKysely;
```

### Parameters

* `config` (ClientConfig) - Description of the configuration object parameter.

### Returns

`SQLocalKysely`
```

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

### Instantiate SQLocal with Database Path

Source: https://sqlocal.dev/reference/index/classes/SQLocal

Use this constructor to initialize SQLocal by providing the path to the SQLite database file.

```typescript
new SQLocal(databasePath)
```

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

### Create Kysely Migrator Instance

Source: https://sqlocal.dev/kysely/migrations

Instantiate the Kysely Migrator, providing the database instance and a migration provider. The provider dynamically imports migrations.

```typescript
import { Migrator } from 'kysely';
import { db } from './client';

export const migrator = new Migrator({
	db,
	provider: {
		aSync getMigrations() {
			const { migrations } = await import('./migrations/');
			return migrations;
		},
	},
});
```

```typescript
import { SQLocalKysely } from 'sqlocal/kysely';
import { Kysely } from 'kysely';
import type { Database } from './schema';

const { dialect } = new SQLocalKysely('database.sqlite3');
export const db = new Kysely<Database>({ dialect });
```

```typescript
import type { Generated } from 'kysely';

export type Database = {
	groceries: GroceriesTable;
};

export type GroceriesTable = {
	id: Generated<number>;
	name: string;
	quantity: number;
};
```

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

### SQLocal Constructor

Source: https://sqlocal.dev/reference/index/classes/SQLocal

Initializes a new SQLocal instance. It can be constructed with either a database path or a configuration object.

```APIDOC
## Constructor

### `new SQLocal(databasePath: DatabasePath): SQLocal`

Initializes SQLocal with a database path.

### `new SQLocal(config: ClientConfig): SQLocal`

Initializes SQLocal with a client configuration object.
```

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

### Import createScalarFunction

Source: https://sqlocal.dev/api/createscalarfunction

Import the `createScalarFunction` method from the SQLocal client. This is the initial step before defining any custom SQL functions.

```javascript
import { SQLocal } from 'sqlocal';

const { createScalarFunction } = new SQLocal('database.sqlite3');
```

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

### Import overwriteDatabaseFile from SQLocal Client

Source: https://sqlocal.dev/api/overwritedatabasefile

Access or destructure overwriteDatabaseFile from the SQLocal client. Initialize the client with your database file name.

```javascript
import { SQLocal } from 'sqlocal';

const { overwriteDatabaseFile } = new SQLocal('database.sqlite3');
```

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

### Overwrite Database with Blob from Fetch

Source: https://sqlocal.dev/api/overwritedatabasefile

Download a database file from a server using fetch and overwrite the local database with the received Blob. Ensure the response is successfully fetched before proceeding.

```javascript
const response = await fetch('https://example.com/download?id=12345');
const databaseFile = await response.blob();
await overwriteDatabaseFile(databaseFile);
```

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

### Additional Migrator Methods

Source: https://sqlocal.dev/kysely/migrations

Demonstrates alternative methods for running migrations, including migrating up, rolling back, and migrating to a specific version.

```typescript
// run the next migration
await migrator.migrateUp();
// rollback the last migration
await migrator.migrateDown();
// migrate to the point of the migration passed by key
await migrator.migrateTo('2023-08-01');
```

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

### Initialize Drizzle ORM with SQLocal Driver

Source: https://sqlocal.dev/drizzle/setup

Initialize SQLocalDrizzle and pass its driver and batchDriver to the Drizzle instance.

```typescript
import { SQLocalDrizzle } from 'sqlocal/drizzle';
import { drizzle } from 'drizzle-orm/sqlite-proxy';

const { driver, batchDriver } = new SQLocalDrizzle('database.sqlite3');
export const db = drizzle(driver, batchDriver);
```

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

### Import SQLocal and createAggregateFunction

Source: https://sqlocal.dev/api/createaggregatefunction

Import the `SQLocal` class and destructure `createAggregateFunction` when initializing the client. This sets up the necessary components for defining custom SQL functions.

```javascript
import { SQLocal } from 'sqlocal';

const { createAggregateFunction } = new SQLocal('database.sqlite3');
```

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

### sql()

Source: https://sqlocal.dev/reference/index/classes/SQLocal

Executes SQL queries against the database and returns the results.

```APIDOC
## sql<Result>(queryTemplate: string | TemplateStringsArray, ...params: unknown[]): Promise<Result[]>

### Description
Execute SQL queries against the database.

### Type Parameters
- **Result** *extends* `Record<string, any>`

### Parameters
#### Path Parameters
- **queryTemplate** (string | TemplateStringsArray) - Required - The SQL query template.
- **params** (unknown[]) - Optional - Parameters for the query.

### Returns
`Promise<Result[]>`
```

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

### Using reactiveQuery with Query Builders (Kysely)

Source: https://sqlocal.dev/api/reactivequery

Integrate reactiveQuery with Kysely for type-safe queries.

```APIDOC
## Using reactiveQuery with Kysely

### Description
For Kysely users, construct your query, call the `.compile()` method on it, and then pass the result to `reactiveQuery`. This ensures type safety for the data received in the subscription callback.

### Method Signature
`reactiveQuery(kyselyCompiledQuery): SubscriptionObject`

### Parameters
#### `kyselyCompiledQuery`
- (Object): The result of calling `.compile()` on a Kysely query.

### Code Example
```javascript
// Assuming 'db' is your initialized Kysely instance
const subscription = reactiveQuery(
	db.selectFrom('groceries').select('name').compile()
).subscribe((data) => {
	// data is typed as { name: string; }[]
	console.log('Grocery List Updated:', data);
});

// To unsubscribe:
// subscription.unsubscribe();
```
```

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

### Delete Database File with Migration Callback

Source: https://sqlocal.dev/api/deletedatabasefile

Provide a callback function to `deleteDatabaseFile` to execute code, such as running migrations, after the database is deleted but before other clients can access the new database.

```javascript
await deleteDatabaseFile(async () => {
	// Run your migrations
});
```

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

### Managing Subscriptions

Source: https://sqlocal.dev/api/reactivequery

Learn how to subscribe to data changes and unsubscribe to clean up resources.

```APIDOC
## Managing Subscriptions

### Description
The `subscribe` method on the object returned by `reactiveQuery` allows you to register callbacks for data updates and errors. The `unsubscribe` method on the returned subscription object is used to stop listening for updates.

### Subscribe Method
#### Parameters
- `dataCallback` (Function): Called with the latest query results.
- `errorCallback` (Function, Optional): Called if an error occurs during query execution.

### Unsubscribe Method
#### Description
Call this method on the object returned by `subscribe` to stop receiving updates and release resources.

### Code Example
```javascript
const groceries = reactiveQuery((sql) => sql`SELECT name FROM groceries`);

const subscription = groceries.subscribe(
	(data) => {
		console.log('Grocery List Updated:', data);
	},
	(err) => {
		console.error('Query Error:', err);
	}
);

// To stop updates:
// subscription.unsubscribe();
```
```

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

### Upload Database File to Server

Source: https://sqlocal.dev/api/getdatabasefile

Retrieve the database file and upload it to a server endpoint using FormData and fetch. Ensure the server is configured to handle the file upload.

```javascript
const databaseFile = await getDatabaseFile();
const formData = new FormData();
formData.set('databaseFile', databaseFile);

await fetch('https://example.com/upload', {
	method: 'POST',
	body: formData,
});
```

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

### default()

Source: https://sqlocal.dev/reference/vite/functions/default

A Vite plugin that tweaks some Vite settings for building apps that use SQLocal.

```APIDOC
## default()

### Description
A Vite plugin that tweaks some Vite settings for building apps that use SQLocal.

### Signature
```ts
function default(config?): Plugin<UserConfig>;
```

### Parameters
#### Path Parameters
- **config** (`VitePluginConfig`) - Optional - Configuration object for the Vite plugin.

### Returns
`Plugin`<`UserConfig`>
```

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

### Run Latest Kysely Migrations

Source: https://sqlocal.dev/kysely/migrations

Execute the `migrateToLatest` method on the migrator instance to apply all pending migrations.

```typescript
import { migrator } from './database/migrator';

await migrator.migrateToLatest();
```

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

### batch()

Source: https://sqlocal.dev/reference/index/classes/SQLocal

Executes a batch of SQL queries atomically against the database. Results are returned as an array of arrays, where each inner array corresponds to the results of a single statement.

```APIDOC
## batch<Result extends Record<string, any>>()

### Description
Execute a batch of SQL queries against the database in an atomic way.

### Parameters
#### `passStatements`
- `(sql) => Statement[]` - A function that takes a SQL helper and returns an array of statements to execute.

### Returns
- `Promise<Result[][]>` - A promise that resolves to an array of arrays, where each inner array contains the results for a single statement.

### See
<https://sqlocal.dev/api/batch>
```

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

### Basic Transaction Usage

Source: https://sqlocal.dev/api/transaction

Execute a transaction with raw SQL queries. The callback receives a `tx` object with `sql` and `batch` methods. If any query fails or an error is thrown, the transaction is rolled back. If successful, it's committed and the return value of the callback is returned.

```APIDOC
## Transaction

### Description
Executes a callback function within a database transaction, ensuring atomicity and isolation.

### Method
`transaction(callback: (tx: TransactionContext) => Promise<T>) => Promise<T>`

### Parameters
#### Callback Function (`callback`)
- `tx` (TransactionContext) - An object providing methods to execute queries within the transaction.
  - `sql`: A tagged template literal for executing raw SQL queries.
  - `batch`: A function for executing multiple SQL queries.
  - `query`: A function to execute queries from ORMs like Drizzle or Kysely.

### Returns
- `Promise<T>`: A promise that resolves with the return value of the callback function if the transaction is successful, or rejects if an error occurs.

### Request Example
```javascript
const productName = 'rice';
const productPrice = 2.99;

const newProductId = await transaction(async (tx) => {
	const [product] = await tx.sql`
		INSERT INTO groceries (name) VALUES (${productName}) RETURNING *
	`;
	await tx.sql`
		INSERT INTO prices (groceryId, price) VALUES (${product.id}, ${productPrice})
	`;
	return product.id;
});
```
```

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

### createWindowFunction Usage

Source: https://sqlocal.dev/api/createwindowfunction

This method is used to create custom SQL functions that can operate as window aggregates. It takes a function name and an object containing step, inverse, value, and final callbacks.

```APIDOC
## createWindowFunction

### Description
Creates a SQL function that can be called from queries to perform calculations for rows using data from related rows. This function can be used as a window aggregate function with an `OVER` clause or as a regular aggregate function if no `OVER` clause is used.

### Method Signature
`createWindowFunction(name: string, callbacks: { step: Function, inverse?: Function, value?: Function, final: Function })`

### Parameters
#### `name` (string)
- Required - The name of the custom SQL function to create.

#### `callbacks` (object)
- Required - An object containing the callbacks that define the function's behavior.
  - **`step`** (Function) - Invoked to add a row to the current window. Receives row arguments.
  - **`inverse`** (Function) - Optional, only used for window aggregate functions. Invoked to remove the oldest aggregated result from the window. Receives row arguments.
  - **`value`** (Function) - Optional, only used for window aggregate functions. Invoked to return the current value of the aggregate without freeing resources.
  - **`final`** (Function) - Required - Invoked to return the current value of the aggregate and free any allocated resources.

### Request Example (JavaScript)
```javascript
let values = [];

const calcRange = (isFinal) => {
	if (values.length === 0) return null;
	const min = values[0];
	const max = values[values.length - 1];
	if (isFinal) values = [];
	return max - min;
};

await createWindowFunction('range', {
	step: (value) => {
		if (typeof value !== 'number') return;
		const idx = values.findIndex((v) => v > value);
		if (idx === -1) {
			values.push(value);
		} else {
			values.splice(idx, 0, value);
		}
	},
	inverse: (value) => {
		if (typeof value !== 'number') return;
		const idx = values.indexOf(value);
		if (idx !== -1) values.splice(idx, 1);
	},
	value: () => calcRange(false),
	final: () => calcRange(true),
});
```

### Usage Example (SQL Window Aggregate)
```javascript
await sql`
  SELECT
    value,
    range(value) OVER (
      ORDER BY sampleIndex
      ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
    ) AS rangeOver3
  FROM tracking
`;
```

### Usage Example (SQL Regular Aggregate)
```javascript
await sql`SELECT range(value) AS range FROM tracking`;
```

### Note
Functions created are connection-specific. You must create the function on each `SQLocal` instance if you want to use it across multiple connections.
```

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

### export

Source: https://sqlocal.dev/reference/index/interfaces/SQLocalDriver

Exports the current database as an ArrayBuffer or Uint8Array.

```APIDOC
## export

### Description
Exports the current database as an ArrayBuffer or Uint8Array.

### Method
`export(): Promise<{ data: ArrayBuffer | Uint8Array<ArrayBuffer>; name: string }>`

### Parameters
#### Path Parameters
None

#### Query Parameters
None

#### Request Body
None

### Request Example
```typescript
const exportedData = await driver.export();
console.log(exportedData.name);
// exportedData.data contains the database content
```

### Response
#### Success Response (200)
- **data** (ArrayBuffer | Uint8Array<ArrayBuffer>) - The database content.
- **name** (string) - The name of the exported database file.

#### Response Example
```json
{
  "data": "<ArrayBuffer or Uint8Array data>",
  "name": "mydatabase.db"
}
```
```

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

### getDatabaseFile Method

Source: https://sqlocal.dev/api/getdatabasefile

Retrieves the SQLite database file as a Promise resolving to a File object. This method takes no arguments.

```APIDOC
## getDatabaseFile

### Description
Access the SQLite database file so that it can be uploaded to the server or allowed to be downloaded by the user.

### Method
`getDatabaseFile()`

### Parameters
This method takes no arguments.

### Response
- **Promise<File>** - A Promise that resolves to a File object representing the SQLite database.

### Usage Example (Upload)
```javascript
import { SQLocal } from 'sqlocal';

const { getDatabaseFile } = new SQLocal('database.sqlite3');

const databaseFile = await getDatabaseFile();
const formData = new FormData();
formData.set('databaseFile', databaseFile);

await fetch('https://example.com/upload', {
	method: 'POST',
	body: formData,
});
```

### Usage Example (Download)
```javascript
import { SQLocal } from 'sqlocal';

const { getDatabaseFile } = new SQLocal('database.sqlite3');

const databaseFile = await getDatabaseFile();
const fileUrl = URL.createObjectURL(databaseFile);

const a = document.createElement('a');
a.href = fileUrl;
a.download = 'database.sqlite3';
a.click();
a.remove();

URL.revokeObjectURL(fileUrl);
```
```

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

### Insert Data with Parameterized Query

Source: https://sqlocal.dev/api/sql

Use the `sql` tagged template to insert data into the database. Interpolated values are safely passed as parameters.

```javascript
const item = 'Bread';
const quantity = 2;
await sql`INSERT INTO groceries (name, quantity) VALUES (${item}, ${quantity})`;
```

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

### Executing SQL Queries

Source: https://sqlocal.dev/api/sql

Use the `sql` tagged template literal to execute SQL queries. Interpolated values are automatically parameterized to prevent SQL injection. `SELECT` and `RETURNING` queries return an array of objects.

```APIDOC
## Execute SQL Queries

### Description
Execute SQL queries against the database. Values interpolated into the query string will be passed to the database as parameters to that query.

### Method
Tagged Template Literal (`sql`)

### Parameters
- **SQL String**: The SQL query string with interpolated values.
- **Interpolated Values**: Values to be safely passed as parameters to the SQL query.

### Request Example
```javascript
const item = 'Bread';
const quantity = 2;
await sql`INSERT INTO groceries (name, quantity) VALUES (${item}, ${quantity})`;

const data = await sql`SELECT * FROM groceries`;
console.log(data);
```

### Response
#### Success Response
- For `SELECT` and `RETURNING` queries: An array of objects representing the matched records.
- For other queries (e.g., `INSERT`, `UPDATE`, `DELETE`): Typically an empty array or a status indicating success.

#### Response Example
```javascript
[
	{ id: 1, name: 'Rice', quantity: 4 },
	{ id: 2, name: 'Milk', quantity: 1 },
	{ id: 3, name: 'Bread', quantity: 2 },
]
```

### Notes
- Multiple statements can be passed, but only the first value-returning statement's results are returned.
- Only one statement in the query can have parameter bindings.
- For multiple independent statements, use the `batch` method.
```

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

### Insert data to trigger the callback

Source: https://sqlocal.dev/api/createcallbackfunction

Perform a database insertion that will activate the previously defined SQL trigger and execute the JavaScript callback.

```sql
await sql`INSERT INTO groceries (name) VALUES ('bread')`;
```

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

### Configure Vite with SQLocal Plugin

Source: https://sqlocal.dev/guide/setup

Import and add the SQLocal Vite plugin to your Vite configuration to enable web worker handling and cross-origin isolation for the development server.

```javascript
import { defineConfig } from 'vite';
import sqlocal from 'sqlocal/vite';

export default defineConfig({
	plugins: [sqlocal()],
});
```

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

### Make Type-Safe Queries with Kysely

Source: https://sqlocal.dev/kysely/setup

Import your Kysely instance to build and execute type-safe queries against your database.

```typescript
const data = await db
	.selectFrom('groceries')
	.select(['name', 'quantity'])
	.orderBy('name', 'asc')
	.execute();
console.log(data);
```

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

### getDatabaseFile()

Source: https://sqlocal.dev/reference/index/classes/SQLocal

Retrieves the SQLite database file, allowing it to be uploaded or downloaded.

```APIDOC
## getDatabaseFile(): Promise<File>

### Description
Access the SQLite database file so that it can be uploaded to the server or allowed to be downloaded by the user.

### Returns
`Promise<File>`
```

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

### exec

Source: https://sqlocal.dev/reference/index/interfaces/SQLocalDriver

Executes a single SQL statement against the database.

```APIDOC
## exec

### Description
Executes a single SQL statement against the database.

### Method
`exec(statement: DriverStatement): Promise<RawResultData>`

### Parameters
#### Path Parameters
None

#### Query Parameters
None

#### Request Body
- **statement** (DriverStatement) - Required - The SQL statement to execute.

### Request Example
```typescript
const result = await driver.exec('SELECT * FROM users');
```

### Response
#### Success Response (200)
- **RawResultData** - The result of the executed statement.

#### Response Example
```json
{
  "columns": ["id", "name"],
  "values": [
    [1, "Alice"],
    [2, "Bob"]
  ]
}
```
```

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

### Overwrite Database with User-Imported File

Source: https://sqlocal.dev/api/overwritedatabasefile

Allow users to import a database file through an HTML file input element and use the selected file to overwrite the local database.

```javascript
const fileInput = document.querySelector('input[type="file"]');
const databaseFile = fileInput.files[0];
await overwriteDatabaseFile(databaseFile);
```

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

### getDatabaseInfo Method

Source: https://sqlocal.dev/api/getdatabaseinfo

Retrieves information about the SQLite database file. This method takes no arguments and returns a Promise for an object containing database details.

```APIDOC
## getDatabaseInfo

### Description
Retrieve information about the SQLite database file.

### Method
`getDatabaseInfo()`

### Parameters
This method takes no arguments.

### Response
#### Success Response
Returns a `Promise` for an object with the following properties:
- **`databasePath`** (`string`) - The name of the database file.
- **`databaseSizeBytes`** (`number`) - The current file size of the database in bytes.
- **`storageType`** (`'memory' | 'local' | 'session' | 'opfs'`) - Indicates the storage location of the database.
- **`persisted`** (`boolean`) - `true` if the database is saved in origin private file system and storage persistence is enabled.

If the `SQLocal` instance failed to initialize a database connection, these properties may be `undefined`.

### Request Example
```javascript
const databaseInfo = await getDatabaseInfo();
console.log(databaseInfo);
```

### Response Example
```json
{
  "databasePath": "database.sqlite3",
  "databaseSizeBytes": 10240,
  "storageType": "local",
  "persisted": true
}
```
```

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

### SQLocalKysely.batch

Source: https://sqlocal.dev/reference/kysely/classes/SQLocalKysely

Executes a batch of SQL queries against the database atomically.

```APIDOC
## Methods

### batch()

```ts
batch<Result>(passStatements): Promise<Result[][]>;
```

Description: Execute a batch of SQL queries against the database in an atomic way.

#### Type Parameters

* `Result` *extends* `Record`<`string`, `any`>

#### Parameters

* `passStatements` (`sql`) => `Statement`[] - A function that takes an SQL tag and returns an array of statements.

#### Returns

`Promise`<`Result`[]>

#### See

<https://sqlocal.dev/api/batch>

#### Inherited from

[`SQLocal`](../../index/classes/SQLocal.md).[`batch`](../../index/classes/SQLocal.md#batch)
```

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

### Handle Multiple SQL Statements

Source: https://sqlocal.dev/api/sql

When executing multiple statements, only the first value-returning statement's results are returned. Parameter bindings are also limited to one statement. It is recommended to execute one statement per query or use the `batch` method.

```javascript
// Warning: only returns the row with id 1.
const result = await sql`
	SELECT * FROM foo WHERE id = 1; 
	SELECT * FROM foo WHERE id = 2;
`;

// Recommended: one statement per query
const result1 = await sql`SELECT * FROM foo WHERE id = 1;`;
const result2 = await sql`SELECT * FROM foo WHERE id = 2;`;
```

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

### SQLocalDrizzle Constructor with Config

Source: https://sqlocal.dev/reference/drizzle/classes/SQLocalDrizzle

Initializes SQLocalDrizzle with a configuration object. This constructor is inherited from the base SQLocal class.

```typescript
new SQLocalDrizzle(config): SQLocalDrizzle;
```

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

### Initialize Kysely with Schema Type

Source: https://sqlocal.dev/kysely/setup

Pass your defined database schema type to the Kysely instance to enable full type safety for your queries.

```typescript
export const db = new Kysely<Database>({ dialect });
```

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

### getDatabaseInfo()

Source: https://sqlocal.dev/reference/index/classes/SQLocal

Retrieves information about the SQLite database file.

```APIDOC
## getDatabaseInfo(): Promise<DatabaseInfo>

### Description
Retrieve information about the SQLite database file.

### Returns
`Promise<DatabaseInfo>`
```

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

### SQLocalDrizzle Constructor with Database Path

Source: https://sqlocal.dev/reference/drizzle/classes/SQLocalDrizzle

Initializes SQLocalDrizzle with a database path. This constructor is inherited from the base SQLocal class.

```typescript
new SQLocalDrizzle(databasePath): SQLocalDrizzle;
```

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

### ClientConfig Type Definition

Source: https://sqlocal.dev/reference/index/type-aliases/ClientConfig

This snippet shows the structure of the ClientConfig type, which is used to configure SQLocal instances. It includes properties for the database path, optional callbacks for connection and initialization, a processor for handling operations, and boolean flags for various operational modes.

```APIDOC
## Type Alias: ClientConfig

This type alias defines the configuration object for initializing SQLocal.

### Properties

*   **`databasePath`** (`DatabasePath`) - Required - The path to the SQLite database file.
*   **`onConnect?`** (`(reason) => void`) - Optional - A callback function executed when a connection is established. It receives a `reason` argument.
*   **`onInit?`** (`(sql) => void | Statement[]`) - Optional - A callback function executed during the initialization phase. It receives the SQL connection object and can return an array of `Statement` objects to be executed.
*   **`processor?`** (`SQLocalProcessor | Worker`) - Optional - Specifies a processor for handling operations, which can be an `SQLocalProcessor` instance or a `Worker`.
*   **`reactive?`** (`boolean`) - Optional - If true, enables reactive mode for the database.
*   **`readOnly?`** (`boolean`) - Optional - If true, opens the database in read-only mode.
*   **`verbose?`** (`boolean`) - Optional - If true, enables verbose logging.
```

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

### overwriteDatabaseFile Method

Source: https://sqlocal.dev/api/overwritedatabasefile

The overwriteDatabaseFile method takes a database file as a File, Blob, ReadableStream, ArrayBuffer, or Uint8Array object and replaces the SQLocal instance's associated database file. It can optionally accept a callback function to run after the overwrite but before other SQLocal client instances can access the new database.

```APIDOC
## overwriteDatabaseFile

### Description
Replace the contents of the SQLite database file.

### Method Signature
`overwriteDatabaseFile(databaseFile: File | Blob | ReadableStream | ArrayBuffer | Uint8Array, callback?: () => Promise<void>): Promise<void>`

### Parameters
#### `databaseFile`
- **Type**: `File | Blob | ReadableStream | ArrayBuffer | Uint8Array` - Required - The new database file content.

#### `callback`
- **Type**: `() => Promise<void>` - Optional - A function to run after the overwrite but before connections from other SQLocal client instances are allowed to access the new database. Useful for running migrations.

### Usage Example (with Blob)
```javascript
const response = await fetch('https://example.com/download?id=12345');
const databaseFile = await response.blob();
await overwriteDatabaseFile(databaseFile);
```

### Usage Example (with ReadableStream)
```javascript
const response = await fetch('https://example.com/download?id=12345');
const databaseStream = response.body;
if (databaseStream === null) throw new Error('No database found');
await overwriteDatabaseFile(databaseStream);
```

### Usage Example (with File input)
```javascript
const fileInput = document.querySelector('input[type="file"]');
const databaseFile = fileInput.files[0];
await overwriteDatabaseFile(databaseFile);
```

### Usage Example (with callback)
```javascript
await overwriteDatabaseFile(databaseFile, async () => {
	// Run your migrations
});
```

### Notes
- Calling `overwriteDatabaseFile` will reset all connections to the database file.
- The `onInit` statements and `onConnect` hook will re-run on any SQLocal clients connected to the database when it is overwritten.
- The client that initiated the overwrite will have its `onConnect` hook run first, before the method's callback, and other clients' `onConnect` hooks will run after the callback.
```

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

### drizzle() Function Signature

Source: https://sqlocal.dev/reference/drizzle/functions/drizzle

This signature shows the basic usage of the drizzle function with a callback and an optional configuration object.

```APIDOC
## Function: drizzle()

### Call Signature

```ts
function drizzle<TSchema>(callback, config?): SqliteRemoteDatabase<TSchema>;
```

### Type Parameters

| Type Parameter | Default type |
| ------ | ------ |
| `TSchema` *extends* `Record`<`string`, `unknown`> | `Record`<`string`, `never`> |

### Parameters

| Parameter | Type |
| ------ | ------ |
| `callback` | `AsyncRemoteCallback` |
| `config?` | `DrizzleConfig`<`TSchema`> |

### Returns

`SqliteRemoteDatabase`<`TSchema`>
```

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

### Kysely Query Builder Reactive Query Subscription

Source: https://sqlocal.dev/api/reactivequery

With Kysely, compile your query using the .compile() method and pass the result to reactiveQuery for a typed subscription.

```javascript
const subscription = reactiveQuery(
	db.selectFrom('groceries').select('name').compile()
).subscribe((data) => {
	// data is typed as { name: string; }[]
	console.log('Grocery List Updated:', data);
});
```

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

### reactiveQuery()

Source: https://sqlocal.dev/reference/index/classes/SQLocal

Subscribes to a SQL query and receives results reactively as the underlying tables change.

```APIDOC
## reactiveQuery<Result>(passStatement: StatementInput<Result>): ReactiveQuery<Result>

### Description
Subscribe to a SQL query and receive the latest results whenever the read tables change.

### Type Parameters
- **Result** *extends* `Record<string, any>`

### Parameters
#### Path Parameters
- **passStatement** (StatementInput<Result>) - Required - The SQL statement input.

### Returns
`ReactiveQuery<Result>`
```

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

### Transaction with Kysely Query Builder

Source: https://sqlocal.dev/api/transaction

Integrate Kysely queries into a transaction by compiling them and passing them to the `tx.query` function. This method ensures atomicity and isolation, similar to SQLocal's native transaction handling.

```APIDOC
## Transaction with Kysely

### Description
Executes Kysely Query Builder queries within a transaction context provided by SQLocal.

### Method
`transaction(callback: (tx: TransactionContext) => Promise<T>) => Promise<T>`

### Parameters
#### Callback Function (`callback`)
- `tx` (TransactionContext) - An object providing methods to execute queries within the transaction.
  - `query`: A function that accepts a compiled Kysely query and executes it within the transaction.

### Returns
- `Promise<T>`: A promise that resolves with the return value of the callback function if the transaction is successful, or rejects if an error occurs.

### Request Example
```javascript
const productName = 'rice';
const productPrice = 2.99;

const newProductId = await transaction(async (tx) => {
	const [product] = await tx.query(
		db
			.insertInto('groceries')
			.values({ name: productName })
			.returningAll()
			.compile()
	);
	await tx.query(
		db
			.insertInto('prices')
			.values({ groceryId: product.id, price: productPrice })
			.compile()
	);
	return product.id;
});
```
```

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

### getDatabaseFile

Source: https://sqlocal.dev/reference/kysely/classes/SQLocalKysely

Accesses the SQLite database file, making it available for upload or download.

```APIDOC
## getDatabaseFile()

### Description
Access the SQLite database file so that it can be uploaded to the server or allowed to be downloaded by the user.

### Returns
Promise<File>

### See
<https://sqlocal.dev/api/getdatabasefile>
```

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

### Make Type-Safe Queries with Drizzle

Source: https://sqlocal.dev/drizzle/setup

Import your Drizzle instance and defined schema to make type-safe queries.

```typescript
const data = await db
	.select({ name: groceries.name, quantity: groceries.quantity })
	.from(groceries)
	.orderBy(groceries.name)
	.all();
console.log(data);
```

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

### onWrite

Source: https://sqlocal.dev/reference/index/interfaces/SQLocalDriver

Registers a callback function to be executed whenever the database is written to.

```APIDOC
## onWrite

### Description
Registers a callback function to be executed whenever the database is written to.

### Method
`onWrite(callback: (change) => void): () => void`

### Parameters
#### Path Parameters
None

#### Query Parameters
None

#### Request Body
- **callback** ((change) => void) - Required - The function to call on write events.

### Request Example
```typescript
const unsubscribe = driver.onWrite((change) => {
  console.log('Database written to:', change);
});

// To unsubscribe later:
unsubscribe();
```

### Response
#### Success Response (200)
- **() => void** - A function that can be called to unsubscribe the callback.

#### Response Example
```json
// The return value is a function, not a JSON object.
// Example: const unsubscribe = driver.onWrite(...);
```
```

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

### Angular Build Configuration for WASM Assets

Source: https://sqlocal.dev/guide/setup

Configure the Angular build architect in `angular.json` to serve `sqlite3.wasm` from the root of your site. This ensures the necessary WebAssembly file is available.

```json
"architect": {
  "build": {
    "builder": "@angular/build:application",
    "options": {
      "outputPath": "dist/my-app",
      "index": "src/index.html",
      "browser": "src/main.ts",
      "tsConfig": "tsconfig.app.json",
      "assets": [
        {
          "glob": "**/*",
          "input": "node_modules/@sqlite.org/sqlite-wasm/dist"
        },
        {
          "glob": "**/*",
          "input": "public"
        }
      ],
      "styles": ["src/styles.scss"],
      "scripts": [],
      "webWorkerTsConfig": "tsconfig.worker.json"
    }
  },
  "serve": { ... }
}
```

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

### Angular Serve Configuration for SQLocal

Source: https://sqlocal.dev/guide/setup

Configure the Angular development server in `angular.json` to disable prebundling and set cross-origin isolation headers. This is necessary for Vite to compile web workers correctly.

```json
"architect": {
  "build": { ... },
  "serve": {
    "builder": "@angular/build:dev-server",
    "configurations": {
      "development": {
        "buildTarget": "my-app:build:development",
        "headers": {
          "Cross-Origin-Embedder-Policy": "require-corp",
          "Cross-Opener-Policy": "same-origin"
        },
        "prebundle": false
      }
    },
    "defaultConfiguration": "development"
  }
}
```

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

### SQLocal with Kysely ORM

Source: https://sqlocal.dev/guide/introduction

Integrate SQLocal with Kysely for type-safe SQL queries. Initialize SQLocalKysely, pass its dialect to Kysely, define your database schema, and then perform type-safe selections.

```typescript
import { SQLocalKysely } from 'sqlocal/kysely';
import { Kysely, Generated } from 'kysely';

// Initialize SQLocalKysely and pass the dialect to Kysely
const { dialect } = new SQLocalKysely('database.sqlite3');
const db = new Kysely<DB>({ dialect });

// Define your schema
// (passed to the Kysely generic above)

type DB = {
	groceries: {
		id: Generated<number>;
		name: string;
	};
};

// Make type-safe queries
const data = await db
	.selectFrom('groceries')
	.select('name')
	.orderBy('name', 'asc')
	.execute();
console.log(data);
```
Tokens:
Raw