This package has been deprecated

Author message:

Package no longer supported. Contact Support at https://www.npmjs.com/support for more info.

@leafac/sqlite

better-sqlite3 with tagged template literals

Videos

Demonstration

Code Review

Installation

$ 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 template literals.

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:");

betterSqlite3Database.exec(
  `CREATE TABLE "users" ("id" INTEGER PRIMARY KEY AUTOINCREMENT, "name" TEXT);`
);
const statement = betterSqlite3Database.prepare(
  `INSERT INTO "users" ("name") VALUES (?)`
);
console.log(statement.run("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 Literal

Queries in @leafac/sqlite must be created with the sql tagged template literal; simple untagged strings don’t work. @leafac/sqlite needs the tagged template literal 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:");
database.execute(
  sql`CREATE TABLE "users" ("id" INTEGER PRIMARY KEY AUTOINCREMENT, "name" TEXT);`
);
console.log(
  database.run(
    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
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:
database.migrate(
  sql`CREATE TABLE "users" ("id" INTEGER PRIMARY KEY AUTOINCREMENT, "name" TEXT);`
);

// At a later point a new migration is added:
database.migrate(
  sql`CREATE TABLE "users" ("id" INTEGER PRIMARY KEY AUTOINCREMENT, "name" TEXT);`,

  (database) => {
    database.run(
      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).

API

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 literal.

  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:

     console.log(
       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' }
     console.log(
       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 literal.

• .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 Literal

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

• https://npm.im/@leafac/html: Use tagged template literals as an HTML template engine.

Prior Art

• https://npm.im/better-sqlite3: The basis for @leafac/sqlite. The rest of this document explains how they’re different.
• https://npm.im/sql-template-strings: This was the inspiration for using tagged template literals in this way. Unfortunately, sql-template-strings is incompatible with better-sqlite3, thus @leafac/sqlite.
• https://npm.im/html-template-tag: I love (and stole) the idea of using $${...} to mark safe interpolation from html-template-tag.
• https://npm.im/package/pg-lit, https://npm.im/package/slonik: These packages also feature tagged template literals for SQL, but they’re for PostgreSQL instead of SQLite.
• https://npm.im/sqlite, and https://npm.im/better-sqlite3-helper: These packages include lightweight migration systems. .migrate() is even more lightweight: It doesn’t support down migrations and it requires the migrations to be passed as an array, as opposed to, for example, being stored in SQL files. (But you can come up with this array in any way you want, including, for example, reading from a bunch of SQL files.)
• https://github.com/trevyn/turbosql: After having published .migrate() the author of Turbosql reached out to say that they independently arrived at a similar design, but in the Rust ecosystem instead of Node.js. It’s great to have company!

Changelog

4.0.0 · 2022-12-02

Breaking Change

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

Before:

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

After:

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.

3.2.1

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

3.2.0

• 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.

3.1.0

• Changed .migrate() to conform to https://www.sqlite.org/lang_altertable.html#making_other_kinds_of_table_schema_changes.

3.0.0

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

  database.execute(
    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.

2.0.0

• ESM-only.
• Added support for the IN operator (https://github.com/leafac/sqlite/pull/2, thanks @mfbx9da4).