Install SQLite3 in Node.js Projects

Node.js applications use SQLite through native binding packages that connect JavaScript code to the SQLite C library. These packages handle database operations, query execution, and result processing within your Node.js runtime.

This guide covers three popular SQLite packages for Node.js, installation methods for different project types, and configuration steps for development and production environments.

Understanding SQLite Packages for Node.js

Node.js doesn’t include SQLite support in its standard library. You need to install a third-party package that provides bindings to SQLite’s C library.

Three packages dominate the Node.js ecosystem:

better-sqlite3 provides synchronous API calls that execute immediately and return results. This matches how most application code works and simplifies error handling. It’s the fastest option for most use cases.

node-sqlite3 uses asynchronous callbacks for all database operations. This prevents blocking but requires callback management or promise wrappers. It’s mature and widely adopted in existing projects.

sql.js runs SQLite compiled to WebAssembly. It works in browsers and environments without native compilation support. Performance is lower than native bindings, but it’s useful for client-side applications.

This guide focuses on better-sqlite3 and node-sqlite3, which suit server-side Node.js applications.

Prerequisites for Installing SQLite Packages

Node.js SQLite packages require native compilation. Your system needs build tools to compile C++ bindings during installation.

Required Build Tools by Operating System

Windows: Install windows-build-tools using npm:

npm install --global windows-build-tools

This installs Visual Studio Build Tools and Python automatically.

macOS: Install Xcode Command Line Tools:

xcode-select --install

Linux (Ubuntu/Debian):

sudo apt update
sudo apt install build-essential python3

Linux (Fedora/CentOS):

sudo dnf groupinstall "Development Tools"
sudo dnf install python3

Verifying Node.js and npm Installation

Check your Node.js version:

node --version

You need Node.js 14.0.0 or higher for modern SQLite packages. Update if necessary using nvm or downloading from nodejs.org.

Method 1: Installing better-sqlite3

better-sqlite3 provides the most straightforward API for SQLite operations. It uses synchronous calls that return results immediately.

Installing better-sqlite3 in a New Project

Create a project directory and initialize npm:

mkdir my-sqlite-app
cd my-sqlite-app
npm init -y

Install better-sqlite3:

npm install better-sqlite3

The installation compiles native bindings. You’ll see output showing the compilation progress. This takes 30-60 seconds depending on your system.

Verifying Installation with a Test Script

Create a file named test-database.js:

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

// Create or open database file
const db = new Database('test.db');

// Create table
db.exec(`
  CREATE TABLE IF NOT EXISTS users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    email TEXT UNIQUE NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
  )
`);

// Insert data
const insert = db.prepare('INSERT INTO users (name, email) VALUES (?, ?)');
insert.run('Sarah Martinez', '[email protected]');
insert.run('James Wilson', '[email protected]');

// Query data
const users = db.prepare('SELECT * FROM users').all();
console.log('Users in database:', users);

// Close connection
db.close();

Run the script:

node test-database.js

Expected output:

Users in database: [
  {
    id: 1,
    name: 'Sarah Martinez',
    email: '[email protected]',
    created_at: '2024-01-30 14:23:45'
  },
  {
    id: 2,
    name: 'James Wilson',
    email: '[email protected]',
    created_at: '2024-01-30 14:23:45'
  }
]

A test.db file now exists in your project directory.

Understanding better-sqlite3 API Patterns

better-sqlite3 uses three primary methods for database operations:

Database.exec() runs SQL statements without returning data. Use this for schema changes and bulk operations:

db.exec('CREATE TABLE products (id INTEGER PRIMARY KEY, name TEXT)');

Database.prepare() creates prepared statements for repeated queries. This prevents SQL injection and improves performance:

const stmt = db.prepare('INSERT INTO products (name) VALUES (?)');
stmt.run('Widget');
stmt.run('Gadget');

Statement methods execute prepared statements:

• .run() executes INSERT, UPDATE, DELETE statements
• .get() returns the first matching row
• .all() returns all matching rows

Configuring Database Connection Options

better-sqlite3 accepts configuration options when creating database connections:

const db = new Database('myapp.db', {
  verbose: console.log,  // Log all SQL statements
  fileMustExist: false,  // Create file if it doesn't exist
  timeout: 5000          // Wait up to 5 seconds for locks
});

verbose helps during development by showing executed SQL. Remove this in production.

fileMustExist prevents accidentally creating databases from typos. Set to true when opening existing databases.

timeout controls how long operations wait for database locks. Increase for high-concurrency scenarios.

Method 2: Installing node-sqlite3

node-sqlite3 uses asynchronous callbacks for all operations. This approach suits applications that need non-blocking database access.

Installing node-sqlite3 in Your Project

npm install sqlite3

Like better-sqlite3, this compiles native bindings during installation.

Creating a Working Database Connection

Create test-async.js:

const sqlite3 = require('sqlite3').verbose();

// Create database connection
const db = new sqlite3.Database('test-async.db', (err) => {
  if (err) {
    console.error('Connection error:', err.message);
    return;
  }
  console.log('Connected to SQLite database');
});

// Create table
db.run(`
  CREATE TABLE IF NOT EXISTS tasks (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    title TEXT NOT NULL,
    completed INTEGER DEFAULT 0
  )
`, (err) => {
  if (err) {
    console.error('Table creation error:', err.message);
    return;
  }
  
  // Insert data
  const stmt = db.prepare('INSERT INTO tasks (title) VALUES (?)');
  stmt.run('Review pull requests');
  stmt.run('Update documentation');
  stmt.finalize();
  
  // Query data
  db.all('SELECT * FROM tasks', [], (err, rows) => {
    if (err) {
      console.error('Query error:', err.message);
      return;
    }
    console.log('Tasks:', rows);
    
    // Close connection
    db.close();
  });
});

Run the script:

node test-async.js

Converting node-sqlite3 to Promises

Callback-based code becomes difficult to manage in complex applications. Wrap node-sqlite3 methods in promises:

const sqlite3 = require('sqlite3').verbose();

function openDatabase(filename) {
  return new Promise((resolve, reject) => {
    const db = new sqlite3.Database(filename, (err) => {
      if (err) reject(err);
      else resolve(db);
    });
  });
}

function runQuery(db, sql, params = []) {
  return new Promise((resolve, reject) => {
    db.all(sql, params, (err, rows) => {
      if (err) reject(err);
      else resolve(rows);
    });
  });
}

// Use with async/await
async function getTasks() {
  const db = await openDatabase('test-async.db');
  const tasks = await runQuery(db, 'SELECT * FROM tasks');
  console.log(tasks);
  db.close();
}

getTasks();

Alternatively, use the sqlite package which wraps node-sqlite3 with promises:

npm install sqlite sqlite3

const sqlite = require('sqlite');
const sqlite3 = require('sqlite3');

async function setupDatabase() {
  const db = await sqlite.open({
    filename: 'test-async.db',
    driver: sqlite3.Database
  });
  
  await db.exec('CREATE TABLE IF NOT EXISTS tasks (id INTEGER PRIMARY KEY, title TEXT)');
  await db.run('INSERT INTO tasks (title) VALUES (?)', 'Deploy application');
  
  const tasks = await db.all('SELECT * FROM tasks');
  console.log(tasks);
  
  await db.close();
}

setupDatabase();

Installing SQLite Packages in TypeScript Projects

TypeScript projects need type definitions for SQLite packages. better-sqlite3 includes TypeScript definitions by default. node-sqlite3 requires separate installation.

Setting Up better-sqlite3 with TypeScript

Install the package:

npm install better-sqlite3

Create database.ts:

import Database from 'better-sqlite3';

interface User {
  id: number;
  name: string;
  email: string;
}

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

db.exec(`
  CREATE TABLE IF NOT EXISTS users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    email TEXT UNIQUE NOT NULL
  )
`);

const insertUser = db.prepare<[string, string]>(
  'INSERT INTO users (name, email) VALUES (?, ?)'
);

insertUser.run('Alice Chen', '[email protected]');

const getUsers = db.prepare<[], User>('SELECT * FROM users');
const users: User[] = getUsers.all();

console.log(users);
db.close();

Setting Up node-sqlite3 with TypeScript

Install the package and type definitions:

npm install sqlite3
npm install --save-dev @types/sqlite3

The @types/sqlite3 package provides TypeScript definitions for node-sqlite3’s callback-based API.

Configuring SQLite for Different Environments

Node.js applications run in development, testing, and production environments. Each requires different SQLite configuration.

Development Environment Setup

Development databases should be easy to reset and inspect. Create a setup script that initializes fresh databases:

Create scripts/setup-dev-db.js:

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

// Remove existing database
if (fs.existsSync('dev.db')) {
  fs.unlinkSync('dev.db');
}

// Create fresh database
const db = new Database('dev.db');

// Load schema
const schema = fs.readFileSync('schema.sql', 'utf8');
db.exec(schema);

// Load seed data
const seedData = fs.readFileSync('seed-data.sql', 'utf8');
db.exec(seedData);

console.log('Development database created');
db.close();

Add this to your package.json scripts:

{
  "scripts": {
    "db:setup": "node scripts/setup-dev-db.js",
    "dev": "npm run db:setup && node app.js"
  }
}

Testing Environment Configuration

Tests need isolated databases that reset between test runs. Use in-memory databases for fast test execution:

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

// In-memory database exists only in RAM
const db = new Database(':memory:');

// Setup schema
db.exec(`
  CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT);
  INSERT INTO users (name) VALUES ('Test User');
`);

// Run tests
const user = db.prepare('SELECT * FROM users WHERE id = ?').get(1);
console.assert(user.name === 'Test User');

// Database disappears when connection closes
db.close();

For tests that need persistent data, create temporary files:

const Database = require('better-sqlite3');
const os = require('os');
const path = require('path');
const fs = require('fs');

// Create temporary database file
const tempDir = os.tmpdir();
const dbPath = path.join(tempDir, `test-${Date.now()}.db`);
const db = new Database(dbPath);

// Run tests...

// Cleanup
db.close();
fs.unlinkSync(dbPath);

Production Environment Configuration

Production databases require reliability settings. Configure Write-Ahead Logging (WAL) mode for better concurrency:

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

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

// Enable WAL mode
db.pragma('journal_mode = WAL');

// Set synchronous mode for durability
db.pragma('synchronous = NORMAL');

// Enable foreign keys
db.pragma('foreign_keys = ON');

journal_mode = WAL allows readers and writers to work simultaneously. This improves performance for applications with concurrent users.

synchronous = NORMAL balances durability and performance. Data is safe from application crashes but not power failures. Use FULL for critical data.

foreign_keys = ON enforces referential integrity. Enable this to prevent orphaned records.

Handling Database Migrations

Applications evolve and require schema changes. Migration systems track and apply database changes systematically.

Creating a Basic Migration System

Create migrations/001-initial-schema.sql:

CREATE TABLE users (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  username TEXT UNIQUE NOT NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE migrations (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  name TEXT UNIQUE NOT NULL,
  applied_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

Create migrations/002-add-email-column.sql:

ALTER TABLE users ADD COLUMN email TEXT;
CREATE UNIQUE INDEX idx_users_email ON users(email);

Create scripts/migrate.js:

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

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

// Create migrations table if it doesn't exist
db.exec(`
  CREATE TABLE IF NOT EXISTS migrations (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT UNIQUE NOT NULL,
    applied_at DATETIME DEFAULT CURRENT_TIMESTAMP
  )
`);

// Get applied migrations
const appliedMigrations = db
  .prepare('SELECT name FROM migrations')
  .all()
  .map(row => row.name);

// Get migration files
const migrationsDir = path.join(__dirname, '..', 'migrations');
const migrationFiles = fs
  .readdirSync(migrationsDir)
  .filter(file => file.endsWith('.sql'))
  .sort();

// Apply pending migrations
for (const file of migrationFiles) {
  if (appliedMigrations.includes(file)) {
    console.log(`Skipping ${file} (already applied)`);
    continue;
  }
  
  console.log(`Applying ${file}...`);
  const sql = fs.readFileSync(path.join(migrationsDir, file), 'utf8');
  
  db.exec(sql);
  db.prepare('INSERT INTO migrations (name) VALUES (?)').run(file);
  
  console.log(`Applied ${file}`);
}

console.log('All migrations applied');
db.close();

Run migrations:

node scripts/migrate.js

Troubleshooting Common Installation Issues

Issue: Native Module Compilation Fails

Symptom: Installation fails with “gyp ERR!” or “node-gyp” errors.

Solution: Verify build tools are installed correctly. On Windows, ensure windows-build-tools installed successfully. On macOS, run xcode-select --install again. On Linux, install build-essential or equivalent package.

If problems persist, try using prebuilt binaries:

npm install better-sqlite3 --build-from-source=false

Issue: Database File Locked Error

Symptom: “SQLITE_BUSY: database is locked” when running queries.

Solution: Another process has the database open. Close all connections before opening new ones:

// Always close connections when done
const db = new Database('app.db');
// ... do work ...
db.close();

For long-running applications, use a single database connection instead of opening/closing repeatedly:

// Create one connection for the application lifetime
const db = new Database('app.db');

// Export for use throughout the application
module.exports = db;

Issue: Module Version Mismatch

Symptom: “The module was compiled against a different Node.js version.”

Solution: Rebuild the native module after updating Node.js:

npm rebuild better-sqlite3

If rebuilding fails, remove and reinstall:

npm uninstall better-sqlite3
npm install better-sqlite3

Integrating SQLite with Express.js Applications

Express.js applications use SQLite for persistent storage. This example shows basic integration patterns.

Creating a Database Module

Create database.js:

const Database = require('better-sqlite3');
const db = new Database('app.db');

// Configure database
db.pragma('journal_mode = WAL');
db.pragma('foreign_keys = ON');

// Initialize schema
db.exec(`
  CREATE TABLE IF NOT EXISTS products (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    price REAL NOT NULL,
    stock INTEGER DEFAULT 0
  )
`);

module.exports = db;

Using the Database in Routes

Create app.js:

const express = require('express');
const db = require('./database');

const app = express();
app.use(express.json());

// Get all products
app.get('/api/products', (req, res) => {
  const products = db.prepare('SELECT * FROM products').all();
  res.json(products);
});

// Get single product
app.get('/api/products/:id', (req, res) => {
  const product = db.prepare('SELECT * FROM products WHERE id = ?').get(req.params.id);
  if (!product) {
    return res.status(404).json({ error: 'Product not found' });
  }
  res.json(product);
});

// Create product
app.post('/api/products', (req, res) => {
  const { name, price, stock } = req.body;
  const result = db.prepare('INSERT INTO products (name, price, stock) VALUES (?, ?, ?)')
    .run(name, price, stock);
  res.status(201).json({ id: result.lastInsertRowid });
});

// Update product
app.put('/api/products/:id', (req, res) => {
  const { name, price, stock } = req.body;
  const result = db.prepare('UPDATE products SET name = ?, price = ?, stock = ? WHERE id = ?')
    .run(name, price, stock, req.params.id);
  
  if (result.changes === 0) {
    return res.status(404).json({ error: 'Product not found' });
  }
  res.json({ updated: true });
});

// Delete product
app.delete('/api/products/:id', (req, res) => {
  const result = db.prepare('DELETE FROM products WHERE id = ?').run(req.params.id);
  if (result.changes === 0) {
    return res.status(404).json({ error: 'Product not found' });
  }
  res.json({ deleted: true });
});

const PORT = 3000;
app.listen(PORT, () => {
  console.log(`Server running on http://localhost:${PORT}`);
});

Next Steps After Installation

Your Node.js project now has working SQLite integration. Consider these next steps:

Learn prepared statements to prevent SQL injection and improve performance for repeated queries.

Implement connection pooling if your application handles high concurrent load (though SQLite’s WAL mode handles most concurrency needs).

Add query result validation using libraries like Zod or Joi to ensure data meets your application’s requirements.

Set up automated backups by copying the database file regularly or using SQLite’s backup API.

Consider using an ORM like Prisma or TypeORM for complex applications that need schema management and migrations.