Skip to content


Switch branches/tags

Name already in use

A tag already exists with the provided branch name. Many Git commands accept both tag and branch names, so creating this branch may cause unexpected behavior. Are you sure you want to create this branch?

Latest commit


Git stats


Failed to load latest commit information.
Latest commit message
Commit time


better-sqlite3 with tagged templates

Source Package Continuous Integration



Code Review


$ npm install @leafac/sqlite

Use @leafac/sqlite with the es6-string-html Visual Studio Code extension for syntax highlighting on the queries in the tagged templates.

Features, Usage, and Examples

@leafac/sqlite is a thin wrapper (approximately 100 lines of code) around better-sqlite3 which adds the following features:

Prepared Statements Management

To use better-sqlite3 you must create prepared statements and then call them with parameters, for example:

import BetterSqlite3Database from "better-sqlite3";

const betterSqlite3Database = new BetterSqlite3Database(":memory:");

const statement = betterSqlite3Database.prepare(
  `INSERT INTO "users" ("name") VALUES (?)`
console.log("Leandro Facchinetti")); // => { changes: 1, lastInsertRowid: 1 }

The benefit of this approach is that you may reuse the statements, which leads to better performance.

The problem with this approach is that you must manage statements in your application, and running simple queries becomes a two-step process.

@leafac/sqlite brings back the simplicity of issuing queries directly to the database object without losing the performance benefits of reuseable prepared statements (see § How It Works).

The sql Tagged Template

Queries in @leafac/sqlite must be created with the sql tagged template; simple untagged strings don’t work. @leafac/sqlite needs the tagged template to manage the prepared statements and to guarantee that the parameters are escaped safely (see § How It Works).

For example:

import { Database, sql } from "@leafac/sqlite";

const database = new Database(":memory:");
    sql`INSERT INTO "users" ("name") VALUES (${"Leandro Facchinetti"})`
); // => { changes: 1, lastInsertRowid: 1 }
console.log(database.get<{ name: string }>(sql`SELECT * from "users"`)); // => { id: 1, name: 'Leandro Facchinetti' }

You may interpolate raw SQL with the $${...} form, for example:

sql`SELECT * FROM "users" WHERE "name" = ${"Leandro Facchinetti"} $${sql` AND "age" = ${30}`}`;

Convenience Methods for Transactions

In better-sqlite3, transactions follow a preparation/execution two-step process similar to the one followed by statements, as described in § Prepared Statements Management, for example:

const transaction = database.transaction(() => {
  // Doesn’t execute immediately
// Execute the transaction

@leafac/sqlite introduces convenience methods to execute a transaction in one step, for example:

database.executeTransaction(() => {
  // Executes immediately

The function passed to the better-sqlite3 .transaction() method may have parameters, which will correspond to the arguments passed when executing the transaction. The function passed to the @leafac/sqlite .executeTransaction() method must not have any parameters.

Native TypeScript Support

No need for npm install --save-dev @types/....

A Lightweight Migration System

For example:

// At an early point in the process of developing an application:

// At a later point a new migration is added:

  (database) => {
      sql`INSERT INTO "users" ("name") VALUES (${"Leandro Facchinetti"})`

The .migrate() method receives as parameters sql`...` queries and arbitrary functions. Only the parameters that have not been run before are executed to bring the database up to the most recent version, so you should call .migrate() at your application startup. Migrations are run on a transaction, so if one of them fails everything rolls back (if your arbitrary functions have side-effects you’ll have to manage them yourself).

No Down Migrations

Most migration systems provide a way to undo migrations; something called down migrations. .migrate() doesn’t provide a down migration mechanism.

I believe that down migrations are more trouble to maintain (they can be a lot of work!) than they’re worth, particularly in small applications. Why? Because down migrations have two main selling points:

  1. You may go back and forward with the database schema in development (think of alternating back and forth while working on different feature branches that change the database schema).
  2. You may rollback a deployment that goes wrong in production.

But I don’t think these selling points hold up:

  1. You may recreate the database from scratch whenever you need in development.
  2. You almost never want to run a down migration in production because that would make you lose data.

In case something goes wrong, .migrate() requires you to write a new migration that undoes the troublesome previous migration. The only way through is forward!

Don’t Change Migrations That Already Run

.migrate() doesn’t run migrations that it ran in the past, so if you change an existing migration, it won’t take effect. .migrate() has no mechanism to detect and warn about this kind of issue (it can’t, because arbitrary functions don’t lend themselves to this kind of inspection).


The Database class is a subclass of the better-sqlite3 database, so all better-sqlite3 database’s methods are available in Database. If you need to use the traditional two-step workflow of explicitly preparing a statement as mentioned in § Prepared Statements Management, you can do that.

The Database class introduces the following new methods:

  • .run(query, options), .get<T>(query, options), .all<T>(query, options), and .iterate<T>(query, options): Equivalent to the corresponding methods in better-sqlite3’s statements. The differences are:

    1. These methods must be called on the database instead of on a prepared statement.

    2. These methods work with queries generated with the sql tagged template.

    3. Advanced: These methods accept an optional options parameter which should be an object with the safeIntegers field to control the use of BigInt in the result. This changes the underlying statement until another query with the same statement sets safeIntegers to a different value. For example:

        database.get<{ name: string }>(sql`SELECT * from "users"`, {
          safeIntegers: true,
      ); // => { id: 1n, name: 'Leandro Facchinetti' }
      console.log(database.get<{ name: string }>(sql`SELECT * from "users"`)); // => { id: 1n, name: 'Leandro Facchinetti' }
        database.get<{ name: string }>(sql`SELECT * from "users"`, {
          safeIntegers: false,
      ); // => { id: 1, name: 'Leandro Facchinetti' }
  • .execute<T>(query): Equivalent to better-sqlite3’s .exec(), but adapted to work with the queries generated with the sql tagged template.

  • .executeTransaction<T>(fn), .executeTransactionImmediate<T>(fn), and .executeTransactionExclusive<T>(fn): Equivalent to better-sqlite3’s .transaction(), .transaction().immediate(), and .transaction().exclusive(), but execute the transaction immediately (see § Convenience Methods for Transactions).

How It Works

Prepared Statements Management & The sql Tagged Template

The sql tag produces a data structure with the source of the query along with the parameters, for example, the following query:

sql`INSERT INTO "users" ("name") VALUES (${"Leandro Facchinetti"})`;

becomes the following data structure:

  "source": "INSERT INTO \"users\" (\"name\") VALUES (?)",
  "parameters": ["Leandro Facchinetti"]

The Database keeps a map from query sources to better-sqlite3 prepared statements (a cache; a technique called memoization). To run a query, Database picks up on the data structure produced by the sql tag and looks for the query source in the map; if it’s a hit, then Database reuses the prepared statement and only binds the new parameters; otherwise Database creates the prepared statement, uses it, and stores it for later.

There’s no cache eviction policy in @leafac/sqlite. The prepared statements for every query ever run hang around in memory for as long as the database object is alive (the statements aren’t eligible for garbage collection because they’re in the map). In most cases, that’s fine because there are only a limited number of queries; it’s the parameters that change. If that becomes a problem for you, you may access the cache under the statements property and implement your own cache eviction policy.

You may also use the low-level .getStatement(query: Query, options: Options) method to get a hold of the underlying prepared statement in the cache (for example, to use .pluck(), .expand(), .raw(), .columns(), and .bind()—though .bind() will probably render the prepared statement unusable by @leafac/sqlite).

Migration System

.migrate() uses the user_version SQLite PRAGMA to store the number of migrations it ran in the past, and consults this number to avoid re-running migrations.

Related Projects

Prior Art


4.0.0 · 2022-12-02

Breaking Change

Use default import instead of named import for sql`...`.


import { sql, Database } from "@leafac/sqlite";


import sql, { Database } from "@leafac/sqlite";

3.3.1 · 2022-12-02

  • Updated better-sqlite3 to 8.0.1.

3.3.0 · 2022-11-22

  • Updated better-sqlite3 to 8.0.0.


  • Added support for JavaScript Sets in queries—they behave like arrays.


  • Added support for asynchronous migration functions in .migrate(). Asynchronous migrations can be useful, for example, if need to ask the user for some input to add initial data to new columns in existing tables.

    Note: Now .migrate() itself is asynchronous, remember to await on it.

    Technical Sidenote: Migration functions run in a transaction, and generally speaking transactions shouldn’t persist across ticks of the event loop, but migrations are a special case: They run at most once, and typically at the application startup, while it’s the only transaction.



  • Added support for interpolation of parameters into queries passed to .execute(), for example:

      sql`INSERT INTO "users" ("name") VALUES (${"Leandro Facchinetti"})`

    This required a change to the Query data type, hence the major version bump, but most people don’t need any extra work to upgrade.


  • ESM-only.
  • Added support for the IN operator (#2, thanks @mfbx9da4).