better-sqlite3

The fastest and simplest library for SQLite in Node.js.

weekly downloads3.2M

versionv12.6.2

licenseMIT

categorydatabase

sqlsqlitesqlite3transactionsuser-defined functionsaggregate functionswindow functionsdatabase

Overview

better-sqlite3 is a Node.js library that provides a fully synchronous interface to SQLite databases, designed to maximize performance for the serialized nature of SQLite operations. Unlike asynchronous SQLite wrappers, it avoids event loop overhead by executing queries synchronously, resulting in significantly higher throughput for typical SQLite workloads where operations are inherently sequential.

The library implements prepared statement reuse, allowing you to compile SQL once and execute it multiple times with different parameters. This approach delivers substantial performance improvements over libraries that recompile statements on every execution. With over 3 million weekly downloads, it's become the de facto choice for developers building Node.js applications that need local database storage without the complexity of client-server databases.

better-sqlite3 supports advanced features including user-defined functions (allowing JavaScript functions to be called from SQL), aggregate functions, window functions, and streamlined transaction management. It leverages Node's garbage collector for memory management and provides a straightforward API that eliminates callback pyramids and promise chains. The library includes TypeScript type definitions via @types/better-sqlite3 and is actively maintained under the MIT license.

This package excels in scenarios where SQLite's write serialization makes asynchronous APIs counterproductive: CLI tools, Electron apps, serverless functions, embedded systems, and I/O-bound web servers handling moderate request volumes. It's optimized for applications where database operations are the bottleneck, not CPU computation, making it ideal for local caching, session storage, and data persistence in desktop or small-scale server applications.

Quick Start

typescript

const Database = require('better-sqlite3');

const db = new Database('myapp.db');

db.exec(`
  CREATE TABLE IF NOT EXISTS users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    username TEXT UNIQUE NOT NULL,
    email TEXT NOT NULL,
    created_at INTEGER DEFAULT (strftime('%s', 'now'))
  )
`);

const insertUser = db.prepare('INSERT INTO users (username, email) VALUES (?, ?)');
const findByUsername = db.prepare('SELECT * FROM users WHERE username = ?');
const updateEmail = db.prepare('UPDATE users SET email = ? WHERE id = ?');

const insertMany = db.transaction((users) => {
  for (const user of users) {
    insertUser.run(user.username, user.email);
  }
});

insertMany([
  { username: 'alice', email: 'alice@example.com' },
  { username: 'bob', email: 'bob@example.com' }
]);

const user = findByUsername.get('alice');
console.log(user);

db.function('email_domain', (email) => {
  return email.split('@')[1];
});

const domains = db.prepare('SELECT DISTINCT email_domain(email) as domain FROM users').all();
console.log(domains);

updateEmail.run('alice.new@example.com', user.id);

db.close();

Use Cases

Desktop applications (Electron/Tauri): Store application state, user preferences, and cached data locally without requiring a separate database server. The synchronous API simplifies state management in renderer processes where async complexity adds no benefit.

Command-line tools and scripts: Build data processing pipelines, migration scripts, or ETL tools that need reliable local storage. The synchronous nature makes sequential data transformations straightforward without async/await boilerplate.

Web server session and cache storage: Handle session persistence or caching layers in Express/Fastify applications where database reads/writes are serialized per-request. Performance exceeds async alternatives because SQLite's write lock makes concurrency impossible anyway.

Testing and development databases: Create ephemeral test databases for integration tests with simple setup/teardown. In-memory mode (:memory:) provides isolated test environments without filesystem overhead.

Edge computing and serverless functions: Store and query data in Lambda, Cloudflare Workers (with Durable Objects), or similar environments where lightweight embedded databases are preferable to network-based solutions for cold start performance.

Pros & Cons

Pros

+Significantly faster than async SQLite libraries due to avoiding event loop overhead for inherently serialized operations
+Prepared statement reuse and straightforward synchronous API reduce code complexity and improve maintainability
+Built-in support for user-defined functions, transactions, and backups without external dependencies
+Excellent TypeScript support and comprehensive documentation with clear error messages
+Active maintenance with regular updates for modern Node.js versions and security patches

Cons

−Synchronous operations can block the event loop if queries take excessive time, unsuitable for long-running analytical queries
−Native dependency requires compilation during installation, which can complicate deployment in some container or serverless environments
−Not ideal for applications requiring true concurrent writes from multiple processes (SQLite limitation, not library-specific)
−Limited to Node.js runtime; cannot be used in browser environments or web workers
−Larger bundle size compared to pure JavaScript SQLite implementations due to native bindings

Install

bash

npm install better-sqlite3

bash

pnpm add better-sqlite3

bash

bun add better-sqlite3

Overview

Quick Start

typescript

const Database = require('better-sqlite3');

const db = new Database('myapp.db');

db.exec(`
  CREATE TABLE IF NOT EXISTS users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    username TEXT UNIQUE NOT NULL,
    email TEXT NOT NULL,
    created_at INTEGER DEFAULT (strftime('%s', 'now'))
  )
`);

const insertUser = db.prepare('INSERT INTO users (username, email) VALUES (?, ?)');
const findByUsername = db.prepare('SELECT * FROM users WHERE username = ?');
const updateEmail = db.prepare('UPDATE users SET email = ? WHERE id = ?');

const insertMany = db.transaction((users) => {
  for (const user of users) {
    insertUser.run(user.username, user.email);
  }
});

insertMany([
  { username: 'alice', email: 'alice@example.com' },
  { username: 'bob', email: 'bob@example.com' }
]);

const user = findByUsername.get('alice');
console.log(user);

db.function('email_domain', (email) => {
  return email.split('@')[1];
});

const domains = db.prepare('SELECT DISTINCT email_domain(email) as domain FROM users').all();
console.log(domains);

updateEmail.run('alice.new@example.com', user.id);

db.close();

Use Cases

Pros & Cons

Pros

+Significantly faster than async SQLite libraries due to avoiding event loop overhead for inherently serialized operations
+Prepared statement reuse and straightforward synchronous API reduce code complexity and improve maintainability
+Built-in support for user-defined functions, transactions, and backups without external dependencies
+Excellent TypeScript support and comprehensive documentation with clear error messages
+Active maintenance with regular updates for modern Node.js versions and security patches

Cons

−Synchronous operations can block the event loop if queries take excessive time, unsuitable for long-running analytical queries
−Native dependency requires compilation during installation, which can complicate deployment in some container or serverless environments
−Not ideal for applications requiring true concurrent writes from multiple processes (SQLite limitation, not library-specific)
−Limited to Node.js runtime; cannot be used in browser environments or web workers
−Larger bundle size compared to pure JavaScript SQLite implementations due to native bindings