Better SQLite3

https://github.com/wiselibs/better-sqlite3
Admin
The fastest and simplest library for SQLite in Node.js, offering full transaction support, high...
Tokens:27,651
Snippets:186
Trust Score:6.8
License:MIT
Update:1 week ago
Show doc for...
Tokens:
Raw
### Open database and query a row with better-sqlite3

Source: https://github.com/wiselibs/better-sqlite3/blob/master/README.md

Open a database file and run a prepared statement to fetch a single row. The `get` method returns the first matching row, and the example logs the row's fields.

```js
const db = require('better-sqlite3')('foobar.db', options);

const row = db.prepare('SELECT * FROM users WHERE id = ?').get(userId);
console.log(row.firstName, row.lastName, row.email);
```

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

### Run benchmark suite

Source: https://github.com/wiselibs/better-sqlite3/blob/master/docs/benchmark.md

Commands to clone the repository, install dependencies, and execute the benchmark script. Note that --unsafe-perm may be required if running as root.

```bash
git clone https://github.com/WiseLibs/better-sqlite3.git
cd better-sqlite3
npm install # if you're doing this as the root user, --unsafe-perm is required
node benchmark
```

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

### Install better-sqlite3 with custom SQLite amalgamation

Source: https://github.com/wiselibs/better-sqlite3/blob/master/docs/compilation.md

Use this command to install better-sqlite3 from source, pointing to a custom SQLite amalgamation directory. The --sqlite3 flag must point to the folder containing sqlite3.c and sqlite3.h.

```bash
npm install better-sqlite3 --build-from-source --sqlite3=/path/to/sqlite-amalgamation
```

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

### Install better-sqlite3 with npm

Source: https://github.com/wiselibs/better-sqlite3/blob/master/README.md

Install the better-sqlite3 package via npm. Requires a currently supported Node.js version; prebuilt binaries are available for major platforms and architectures.

```bash
npm install better-sqlite3
```

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

### Preinstall script for custom SQLite build

Source: https://github.com/wiselibs/better-sqlite3/blob/master/docs/compilation.md

Add this preinstall script to your package.json to automatically install better-sqlite3 with the custom SQLite flags when running npm install. The --no-save flag prevents it from being added to dependencies.

```json
{
  "scripts": {
    "preinstall": "npm install better-sqlite3@'^7.0.0' --no-save --build-from-source --sqlite3=\"$(pwd)/sqlite-amalgamation\""
  }
}
```

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

### Aggregate with result() and window support

Source: https://github.com/wiselibs/better-sqlite3/blob/master/docs/api.md

Registers an aggregate with a result() function to transform the final value. The example calculates an average. Providing an inverse() function enables use as a window function, where step() adds rows and inverse() removes them. The example shows a window query using the aggregate.

```js
db.aggregate('getAverage', {
  start: () => [],
  step: (array, nextValue) => {
    array.push(nextValue);
  },
  result: array => array.reduce(sum) / array.length,
});

db.prepare('SELECT getAverage(dollars) FROM expenses').pluck().get(); // => 20.2
```

```js
db.aggregate('addAll', {
  start: 0,
  step: (total, nextValue) => total + nextValue,
  inverse: (total, droppedValue) => total - droppedValue,
  result: total => Math.round(total),
});

db.prepare(`
  SELECT timestamp, dollars, addAll(dollars) OVER day as dayTotal
  FROM expenses
  WINDOW day AS (PARTITION BY date(timestamp))
  ORDER BY timestamp
`).all();
```

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

### Load a SQLite extension with db.loadExtension()

Source: https://github.com/wiselibs/better-sqlite3/blob/master/docs/api.md

Loads a compiled SQLite extension and applies it to the current database connection. Ensure the extension is compiled against a compatible SQLite version. The example loads './my-extensions/compress.so'.

```js
db.loadExtension('./my-extensions/compress.so');
```

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

### Execute PRAGMA with db.pragma() and simple option

Source: https://github.com/wiselibs/better-sqlite3/blob/master/docs/api.md

Sets the cache_size PRAGMA and then reads it back with the simple option, which returns only the first column of the first row. The example logs 32000.

```javascript
db.pragma('cache_size = 32000');
console.log(db.pragma('cache_size', { simple: true })); // => 32000
```

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

### Register a user-defined aggregate with .aggregate()

Source: https://github.com/wiselibs/better-sqlite3/blob/master/docs/api.md

Registers an aggregate function using start, step, and result. The step function is called for each row, and its return value becomes the new aggregate value. The example sums all values in a column and returns the total.

```js
db.aggregate('addAll', {
  start: 0,
  step: (total, nextValue) => total + nextValue,
});

db.prepare('SELECT addAll(dollars) FROM expenses').pluck().get(); // => 92
```

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

### .aggregate(name, options) -> this

Source: https://github.com/wiselibs/better-sqlite3/blob/master/docs/api.md

Registers a user-defined aggregate function. Supports start, step, result, inverse, varargs, directOnly, and deterministic options.

```APIDOC
## .aggregate(name, options) -> this

### Description
Registers a user-defined aggregate function that can be used in SQL aggregate queries. The aggregate processes rows via a step function and returns a final value via an optional result function.

### Method
aggregate registration

### Parameters
- **name** (string) - Required - The name of the aggregate function as used in SQL.
- **options** (object) - Required - Configuration options:
  - **start** (any or function) - Optional - Initial aggregate value. If a function, its return value is used as the initial value; otherwise used as-is. Defaults to null.
  - **step** (function) - Required - Invoked for each row; its return value becomes the new aggregate value.
  - **result** (function) - Optional - Transforms the final aggregate value.
  - **inverse** (function) - Optional - Used to remove a row from the current window when used as a window function.
  - **varargs** (boolean) - Optional - If true, the aggregate can accept any number of arguments.
  - **directOnly** (boolean) - Optional - If true, the aggregate can only be invoked from top-level SQL.
  - **deterministic** (boolean) - Optional - If true, indicates the aggregate is deterministic.

### Request Example
```js
db.aggregate('addAll', {
  start: 0,
  step: (total, nextValue) => total + nextValue,
});
```

### Response
Returns `this` (the database object) for chaining.

### Usage Example
```js
db.prepare('SELECT addAll(dollars) FROM expenses').pluck().get(); // => 92
```
```

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

### Virtual table with default parameter values and required check

Source: https://github.com/wiselibs/better-sqlite3/blob/master/docs/api.md

Creates a 'sequence' virtual table with parameters 'length' and 'start' (default 0). Throws a TypeError if 'length' is omitted. Demonstrates using omitted parameters as undefined to implement required parameters.

```javascript
db.table('sequence', {
  columns: ['value'],
  parameters: ['length', 'start'],
  rows: function* (length, start = 0) {
    if (length === undefined) {
      throw new TypeError('missing required parameter "length"');
    }

    const end = start + length;
    for (let n = start; n < end; ++n) {
      yield { value: n };
    }
  },
});

db.prepare('SELECT * FROM sequence(10)').pluck().all();
// => [0, 1, 2, 3, 4, 5, 6, 7, 8, 9]
```

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

### Write statement results to CSV using stmt.columns() and stmt.raw()

Source: https://github.com/wiselibs/better-sqlite3/blob/master/docs/api.md

Example showing how to use .columns() and .raw() together to write query results to a CSV file. The generator yields column names and raw rows, and the function writes them to a file.

```javascript
const fs = require('fs');

function* toRows(stmt) {
  yield stmt.columns().map(column => column.name);
  yield* stmt.raw().iterate();
}

function writeToCSV(filename, stmt) {
  return new Promise((resolve, reject) => {
    const stream = fs.createWriteStream(filename);
    for (const row of toRows(stmt)) {
      stream.write(row.join(',') + '\n');
    }
    stream.on('error', reject);
    stream.end(resolve);
  });
}
```

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

### Register a deterministic varargs function with .function()

Source: https://github.com/wiselibs/better-sqlite3/blob/master/docs/api.md

Registers a function that accepts any number of arguments when options.varargs is true. Setting options.deterministic to true may improve performance for deterministic functions. The example shows a void function that returns null regardless of arguments.

```js
db.function('void', { deterministic: true, varargs: true }, () => {});

db.prepare("SELECT void()").pluck().get(); // => null
db.prepare("SELECT void(?, ?)").pluck().get(55, 19); // => null
```

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

### Worker thread pool for better-sqlite3 queries

Source: https://github.com/wiselibs/better-sqlite3/blob/master/docs/threads.md

Worker thread pool example for better-sqlite3. The worker.js file listens for messages containing SQL and parameters, executes them, and posts results back. The master.js file spawns workers, queues jobs, and respawns crashed workers.

```javascript
const { parentPort } = require('worker_threads');
const db = require('better-sqlite3')('foobar.db');

parentPort.on('message', ({ sql, parameters }) => {
  const result = db.prepare(sql).all(...parameters);
  parentPort.postMessage(result);
});
```

```javascript
const { Worker } = require('worker_threads');
const os = require('os');

/*
  Export a function that queues pending work.
 */

const queue = [];
exports.asyncQuery = (sql, ...parameters) => {
  return new Promise((resolve, reject) => {
    queue.push({
      resolve,
      reject,
      message: { sql, parameters },
    });
    drainQueue();
  });
};

/*
  Instruct workers to drain the queue.
 */

let workers = [];
function drainQueue() {
  for (const worker of workers) {
    worker.takeWork();
  }
}

/*
  Spawn workers that try to drain the queue.
 */

new Array(os.availableParallelism()).fill(null).forEach(function spawn() {
  const worker = new Worker('./worker.js');

  let job = null; // Current item from the queue
  let error = null; // Error that caused the worker to crash

  function takeWork() {
    if (!job && queue.length) {
      // If there's a job in the queue, send it to the worker
      job = queue.shift();
      worker.postMessage(job.message);
    }
  }

  worker
    .on('online', () => {
      workers.push({ takeWork });
      takeWork();
    })
    .on('message', (result) => {
      job.resolve(result);
      job = null;
      takeWork(); // Check if there's more work to do
    })
    .on('error', (err) => {
      console.error(err);
      error = err;
    })
    .on('exit', (code) => {
      workers = workers.filter(w => w.takeWork !== takeWork);
      if (job) {
        job.reject(error || new Error('worker died'));
      }
      if (code !== 0) {
        console.error(`worker exited with code ${code}`);
        spawn(); // Worker died, so spawn a new one
      }
    });
});
```

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

### Run EXPLAIN with .explain()

Source: https://github.com/wiselibs/better-sqlite3/blob/master/docs/api.md

Runs EXPLAIN on a SQL string, returning the bytecode or query plan. Parameters may be left unbound. Throws an Error if the SQL is invalid.

```javascript
db.explain('SELECT * FROM cats WHERE name = ?');
db.explain('QUERY PLAN SELECT * FROM cats WHERE name = ?');
```

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

### new Database(path, [options])

Source: https://github.com/wiselibs/better-sqlite3/blob/master/docs/api.md

Creates a new database connection. If the database file does not exist, it is created. Options include readonly, fileMustExist, timeout, verbose, and nativeBinding.

```APIDOC
## new Database(path, [options])

### Description
Creates a new database connection. If the database file does not exist, it is created. This happens synchronously, so you can start executing queries right away. You can create an in-memory database by passing `":memory:"` as the first argument, or a temporary database by passing an empty string (or omitting all arguments).

### Parameters
- **path** (string) - Required - The path to the database file, or `":memory:"` for an in-memory database, or a buffer from `.serialize()`.
- **options** (object) - Optional - Configuration options.
  - **options.readonly** (boolean) - Optional - Open the database connection in readonly mode (default: `false`).
  - **options.fileMustExist** (boolean) - Optional - If the database does not exist, an `Error` will be thrown instead of creating a new file. Ignored for in-memory, temporary, or readonly connections (default: `false`).
  - **options.timeout** (number) - Optional - Milliseconds to wait when executing queries on a locked database before throwing a `SQLITE_BUSY` error (default: `5000`).
  - **options.verbose** (function) - Optional - A function called with every SQL string executed by the database connection (default: `null`).
  - **options.nativeBinding** (string) - Optional - File path of `better_sqlite3.node` if the native addon cannot be located automatically.

### Example
```js
const Database = require('better-sqlite3');
const db = new Database('foobar.db', { verbose: console.log });
```
```

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

### .explain(string) -> array of rows

Source: https://github.com/wiselibs/better-sqlite3/blob/master/docs/api.md

Runs EXPLAIN on the given SQL string and returns the result. By default returns the low-level bytecode program, or the query plan if prefixed with 'QUERY PLAN'. Throws an Error if the SQL is invalid.

```APIDOC
## .explain(string) -> array of rows

### Description
Runs EXPLAIN on the given SQL string and returns the result. By default returns the low-level bytecode program that SQLite would use to execute the statement. To get the higher-level query plan instead, prefix your SQL with `QUERY PLAN`.

### Method
EXPLAIN (SQLite command)

### Parameters
- **string** (string) - Required - The SQL string to explain. May contain unbound parameters.

### Request Example
```js
db.explain('SELECT * FROM cats WHERE name = ?');
db.explain('QUERY PLAN SELECT * FROM cats WHERE name = ?');
```

### Response
#### Success Response
- **array of rows** - The result of the EXPLAIN command.

#### Error Handling
- If the given SQL is invalid, an `Error` is thrown.
```

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

### Backup database with .backup() and progress callback

Source: https://github.com/wiselibs/better-sqlite3/blob/master/docs/api.md

Initiates an online backup, returning a promise. The progress callback can control transfer size; returning 0 pauses. The backup aborts if the callback throws or the parent connection closes.

```javascript
let paused = false;
db.backup(`backup-${Date.now()}.db`, {
  progress({ totalPages: t, remainingPages: r }) {
    console.log(`progress: ${((t - r) / t * 100).toFixed(1)}%`);
    return paused ? 0 : 200;
  }
});
```

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

### Create a new Database with verbose logging

Source: https://github.com/wiselibs/better-sqlite3/blob/master/docs/api.md

Creates a new database connection to 'foobar.db' with verbose logging enabled, which logs every SQL string executed. The constructor is synchronous, so queries can be run immediately.

```javascript
const Database = require('better-sqlite3');
const db = new Database('foobar.db', { verbose: console.log });
```

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

### Receive BigInts in user-defined aggregates

Source: https://github.com/wiselibs/better-sqlite3/blob/master/docs/integer.md

Defines a user-defined aggregate that receives BigInt arguments by setting safeIntegers to true. The aggregate starts at 0n and adds each next value.

```javascript
db.aggregate('addInts', {
  safeIntegers: true,
  start: 0n,
  step: (total, nextValue) => total + nextValue,
});
```

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

### Receive BigInts in user-defined functions

Source: https://github.com/wiselibs/better-sqlite3/blob/master/docs/integer.md

Defines a user-defined function that receives BigInt arguments when safeIntegers is enabled. The example shows the function returning a string indicating whether the argument is a bigint.

```javascript
db.function('isInt', { safeIntegers: true }, (value) => {
  return String(typeof value === 'bigint');
});

db.prepare('SELECT isInt(?)').pluck().get(10); // => "false"
db.prepare('SELECT isInt(?)').pluck().get(10n); // => "true"
```

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

### Serialize database with .serialize()

Source: https://github.com/wiselibs/better-sqlite3/blob/master/docs/api.md

Returns a Buffer with the serialized database. The buffer can be written to disk or passed to new Database() to open as an in-memory database.

```javascript
const buffer = db.serialize();
db.close();
db = new Database(buffer);
```

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

### Close database on process exit with db.close()

Source: https://github.com/wiselibs/better-sqlite3/blob/master/docs/api.md

Closes the database connection. After calling, no statements can be created or executed. The example registers close handlers for process exit, SIGHUP, SIGINT, and SIGTERM signals.

```js
process.on('exit', () => db.close());
process.on('SIGHUP', () => process.exit(128 + 1));
process.on('SIGINT', () => process.exit(128 + 2));
process.on('SIGTERM', () => process.exit(128 + 15));
```

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

### Statement#get([...bindParameters]) -> row

Source: https://github.com/wiselibs/better-sqlite3/blob/master/docs/api.md

Executes the prepared statement and returns the first row retrieved by the query as an object. Returns undefined if no data is found. Throws an Error if execution fails.

```APIDOC
## Statement#get([...bindParameters]) -> row

### Description
Executes the prepared statement. When execution completes it returns an object that represents the first row retrieved by the query. The object's keys represent column names. If the statement was successful but found no data, `undefined` is returned. If execution fails, an `Error` is thrown.

### Method
get

### Parameters
#### Bind Parameters
- **...bindParameters** (any) - Optional - Values to bind to the statement's parameters for this execution.

### Request Example
```js
const stmt = db.prepare('SELECT age FROM cats WHERE name = ?');
const cat = stmt.get('Joey');
```

### Response
#### Success Response
- **row** (object) - The first row retrieved, with keys representing column names.

#### Response Example
```js
console.log(cat.age); // => 2
```
```

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

### Prepare a statement with db.prepare()

Source: https://github.com/wiselibs/better-sqlite3/blob/master/docs/api.md

Prepares a SQL statement for later execution. The returned Statement object can be used to run the query with parameters.

```javascript
const stmt = db.prepare('SELECT name, age FROM cats');
```

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

### Create and inspect a BigInt

Source: https://github.com/wiselibs/better-sqlite3/blob/master/docs/integer.md

Shows how to create a BigInt and verify its value, string representation, and type. Useful when working with 64-bit integers that exceed JavaScript's number precision.

```javascript
const big = BigInt('1152735103331642317');
big === 1152735103331642317n; // returns true
big.toString(); // returns "1152735103331642317"
typeof big; // returns "bigint"
```

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

### .backup(destination, [options]) -> promise

Source: https://github.com/wiselibs/better-sqlite3/blob/master/docs/api.md

Initiates a backup of the database, returning a promise for when the backup is complete. Supports optional attached database and progress monitoring. The promise resolves with an object containing totalPages and remainingPages (0 on success).

```APIDOC
## .backup(destination, [options]) -> promise

### Description
Initiates a backup of the database, returning a promise for when the backup is complete. If the backup fails, the promise is rejected with an Error. You can optionally backup an attached database by setting the `attached` option. You can monitor progress with the `progress` callback.

### Parameters
#### Path Parameters
- **destination** (string) - Required - The file path for the backup file.

#### Options
- **attached** (string) - Optional - The name of the attached database to backup.
- **progress** (function) - Optional - A callback invoked on progress, receiving an object with `totalPages` and `remainingPages`. May return a number to set the transfer size (default 100).

### Request Example
```js
db.backup(`backup-${Date.now()}.db`)
  .then(() => {
    console.log('backup complete!');
  })
  .catch((err) => {
    console.log('backup failed:', err);
  });
```

### Response
#### Success Response
- **promise** - Resolves with an object containing `totalPages` and `remainingPages` (0 on success).

#### Error Handling
- If the backup fails, the promise is rejected with an `Error`.
- If the `progress` callback throws, the backup is aborted.
- If the parent database connection is closed, all pending backups are automatically aborted.
```

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

### Virtual table factory function for CREATE VIRTUAL TABLE

Source: https://github.com/wiselibs/better-sqlite3/blob/master/docs/api.md

Defines a virtual table 'csv' using a factory function. The factory is called for each CREATE VIRTUAL TABLE statement, receiving module arguments. The example creates a table 'my_data' from a CSV file.

```javascript
const fs = require('fs');

db.table('csv', (filename) => {
  const firstLine = getFirstLineOfFile(filename);
  return {
    columns: firstLine.split(','),
    rows: function* () {
      // This is just an example. Real CSV files are more complicated to parse.
      const contents = fs.readFileSync(filename, 'utf8');
      for (const line of contents.split('\n')) {
        yield line.split(',');
      }
    },
  };
});

db.exec('CREATE VIRTUAL TABLE my_data USING csv(my_data.csv)');
const allData = db.prepare('SELECT * FROM my_data').all();
```

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

### .loadExtension(path, [entryPoint]) -> this

Source: https://github.com/wiselibs/better-sqlite3/blob/master/docs/api.md

Loads a compiled SQLite extension and applies it to the current database connection. It is your responsibility to ensure the extension is compatible with the SQLite version used by better-sqlite3.

```APIDOC
## .loadExtension(path, [entryPoint]) -> this

### Description
Loads a compiled SQLite extension and applies it to the current database connection.

### Method
loadExtension

### Parameters
- **path** (string) - Required - The path to the compiled extension file.
- **entryPoint** (string) - Optional - The entry point of the extension.

### Request Example
```js
db.loadExtension('./my-extensions/compress.so');
```

### Response
Returns the database object (`this`) for chaining.
```

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

### Bind named parameters with different syntaxes

Source: https://github.com/wiselibs/better-sqlite3/blob/master/docs/api.md

Shows the three SQLite named parameter syntaxes (@, :, $) and that they can be mixed in a single statement.

```js
// The following are equivalent.
const stmt = db.prepare('INSERT INTO people VALUES (@firstName, @lastName, @age)');
const stmt = db.prepare('INSERT INTO people VALUES (:firstName, :lastName, :age)');
const stmt = db.prepare('INSERT INTO people VALUES ($firstName, $lastName, $age)');
const stmt = db.prepare('INSERT INTO people VALUES (@firstName, :lastName, $age)');

stmt.run({
  firstName: 'John',
  lastName: 'Smith',
  age: 45
});
```

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

### Execute a migration file with db.exec()

Source: https://github.com/wiselibs/better-sqlite3/blob/master/docs/api.md

Executes a SQL string that may contain multiple statements. Use only for SQL from external sources like files. On error, execution stops and further statements are not executed; manual rollback is required.

```js
const migration = fs.readFileSync('migrate-schema.sql', 'utf8');
db.exec(migration);
```

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

### Register a user-defined function with .function()

Source: https://github.com/wiselibs/better-sqlite3/blob/master/docs/api.md

Registers a JavaScript function callable from SQL. The number of arguments is strict by default, based on function.length. Registering multiple functions with the same name but different argument counts allows SQLite to choose based on the number of arguments passed. The example shows a function that adds two numbers, and the error when calling with three arguments.

```js
db.function('add2', (a, b) => a + b);

db.prepare('SELECT add2(?, ?)').pluck().get(12, 4); // => 16
db.prepare('SELECT add2(?, ?)').pluck().get('foo', 'bar'); // => "foobar"
db.prepare('SELECT add2(?, ?, ?)').pluck().get(12, 4, 18); // => Error: wrong number of arguments
```

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

### Enable WAL mode with db.pragma

Source: https://github.com/wiselibs/better-sqlite3/blob/master/docs/performance.md

Enable WAL mode for better concurrency and performance. Note the trade-offs: atomicity is per attached database, and there is a slight durability loss due to the default NORMAL synchronous setting.

```javascript
db.pragma('journal_mode = WAL');
```

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

### Run a prepared statement with bind parameters

Source: https://github.com/wiselibs/better-sqlite3/blob/master/docs/api.md

Executes a prepared INSERT statement with bind parameters and logs the number of changes. The info object contains changes and lastInsertRowid. Throws an Error if execution fails.

```js
const stmt = db.prepare('INSERT INTO cats (name, age) VALUES (?, ?)');
const info = stmt.run('Joey', 2);

console.log(info.changes); // => 1
```

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

### Statement#run([...bindParameters]) -> object

Source: https://github.com/wiselibs/better-sqlite3/blob/master/docs/api.md

Executes the prepared statement and returns an info object with changes and lastInsertRowid. Throws an Error if execution fails.

```APIDOC
## Statement#run([...bindParameters]) -> object

### Description
Executes the prepared statement. When execution completes it returns an `info` object describing any changes made. The `info` object has two properties: `changes` (total number of rows inserted, updated, or deleted) and `lastInsertRowid` (rowid of the last inserted row). If execution fails, an `Error` is thrown.

### Method
run

### Parameters
#### Bind Parameters
- **...bindParameters** (any) - Optional - Values to bind to the statement's parameters for this execution.

### Request Example
```js
const stmt = db.prepare('INSERT INTO cats (name, age) VALUES (?, ?)');
const info = stmt.run('Joey', 2);
```

### Response
#### Success Response
- **info.changes** (number) - Total number of rows inserted, updated, or deleted by this operation.
- **info.lastInsertRowid** (number) - Rowid of the last row inserted into the database.

#### Response Example
```js
console.log(info.changes); // => 1
```
```

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

### Retrieve all rows with .all()

Source: https://github.com/wiselibs/better-sqlite3/blob/master/docs/api.md

Prepares a SELECT statement and retrieves all matching rows as an array. Returns an empty array if no rows found, throws an Error on failure.

```js
const stmt = db.prepare('SELECT * FROM cats WHERE name = ?');
const cats = stmt.all('Joey');

console.log(cats.length); // => 1
```

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

### Using toString() with bound parameters

Source: https://github.com/wiselibs/better-sqlite3/blob/master/docs/api.md

Shows how toString() returns the original SQL when no parameters are bound, and the expanded SQL with substituted values after bind(). Only permanently bound parameters via bind() are expanded; temporary parameters passed to execution methods are not reflected.

```javascript
const stmt = db.prepare('SELECT * FROM cats WHERE name = ?');

console.log(stmt.toString()); // => "SELECT * FROM cats WHERE name = ?"

stmt.bind('Joey');
console.log(stmt.toString()); // => "SELECT * FROM cats WHERE name = 'Joey'"
```

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

### Checkpoint WAL file with wal_checkpoint(RESTART)

Source: https://github.com/wiselibs/better-sqlite3/blob/master/docs/performance.md

Periodically check the WAL file size and run the wal_checkpoint(RESTART) pragma when it exceeds a threshold, to prevent checkpoint starvation. The interval is unref'd so it doesn't keep the process alive.

```javascript
setInterval(fs.stat.bind(null, 'foobar.db-wal', (err, stat) => {
  if (err) {
    if (err.code !== 'ENOENT') throw err;
  } else if (stat.size > someUnacceptableSize) {
    db.pragma('wal_checkpoint(RESTART)');
  }
}), 5000).unref();
```

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

### Create virtual table with .table() and query it

Source: https://github.com/wiselibs/better-sqlite3/blob/master/docs/api.md

Creates a virtual table 'filesystem_directory' that reads files from the current directory. The rows generator yields objects with 'filename' and 'data' keys. Querying with SELECT returns all rows.

```javascript
const fs = require('fs');

db.table('filesystem_directory', {
  columns: ['filename', 'data'],
  rows: function* () {
    for (const filename of fs.readdirSync(process.cwd())) {
      const data = fs.readFileSync(filename);
      yield { filename, data };
    }
  },
});

const files = db.prepare('SELECT * FROM filesystem_directory').all();
// => [{ filename, data }, { filename, data }]
```

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

### Database#prepare(string) -> Statement

Source: https://github.com/wiselibs/better-sqlite3/blob/master/docs/api.md

Creates a new prepared Statement from the given SQL string.

```APIDOC
## Database#prepare(string) -> Statement

### Description
Creates a new prepared Statement from the given SQL string.

### Parameters
- **string** (string) - Required - The SQL string to prepare.

### Returns
- **Statement** - A prepared statement object.

### Example
```js
const stmt = db.prepare('SELECT name, age FROM cats');
```
```

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

### Mix anonymous and named parameters

Source: https://github.com/wiselibs/better-sqlite3/blob/master/docs/api.md

Shows how to mix anonymous and named parameters in a single prepared statement, passing anonymous values as positional arguments and named values as an object.

```js
const stmt = db.prepare('INSERT INTO people VALUES (@name, @name, ?)');
stmt.run(45, { name: 'Henry' });
```

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

### Retrieve first row with .get()

Source: https://github.com/wiselibs/better-sqlite3/blob/master/docs/api.md

Prepares a SELECT statement and retrieves the first row matching the bind parameter. Returns undefined if no data found, throws an Error on failure.

```js
const stmt = db.prepare('SELECT age FROM cats WHERE name = ?');
const cat = stmt.get('Joey');

console.log(cat.age); // => 2
```

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

### Statement#all([...bindParameters]) -> array of rows

Source: https://github.com/wiselibs/better-sqlite3/blob/master/docs/api.md

Executes the prepared statement and returns an array of all matching rows. Returns an empty array if no rows are found. Throws an Error if execution fails.

```APIDOC
## Statement#all([...bindParameters]) -> array of rows

### Description
Similar to `.get()`, but instead of only retrieving one row all matching rows will be retrieved. The return value is an array of row objects. If no rows are found, the array will be empty. If execution fails, an `Error` is thrown.

### Method
all

### Parameters
#### Bind Parameters
- **...bindParameters** (any) - Optional - Values to bind to the statement's parameters for this execution.

### Request Example
```js
const stmt = db.prepare('SELECT * FROM cats WHERE name = ?');
const cats = stmt.all('Joey');
```

### Response
#### Success Response
- **array of rows** (array) - An array of row objects.

#### Response Example
```js
console.log(cats.length); // => 1
```
```

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

### Use better-sqlite3 with ES6 module imports

Source: https://github.com/wiselibs/better-sqlite3/blob/master/README.md

ES6 module notation for opening a database and enabling WAL mode. Uses the default export `Database` and the `new` keyword.

```js
import Database from 'better-sqlite3';
const db = new Database('foobar.db', options);
db.pragma('journal_mode = WAL');
```

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

### Bind anonymous parameters

Source: https://github.com/wiselibs/better-sqlite3/blob/master/docs/api.md

Shows equivalent ways to pass anonymous parameters to a prepared statement: as separate arguments, as an array, or as multiple arrays.

```js
const stmt = db.prepare('INSERT INTO people VALUES (?, ?, ?)');

// The following are equivalent.
stmt.run('John', 'Smith', 45);
stmt.run(['John', 'Smith', 45]);
stmt.run(['John'], ['Smith', 45]);
```

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

### Virtual table with explicit parameters option

Source: https://github.com/wiselibs/better-sqlite3/blob/master/docs/api.md

Shows how to explicitly declare parameters using the 'parameters' option. This is necessary when using default parameter values because function.length does not include optional parameters.

```javascript
db.table('regex_matches', {
  columns: ['match', 'capture'],
  parameters: ['pattern', 'text'],
  rows: function* (pattern, text) {
    ...
  },
});
```
Tokens:
Raw