Skip to content
Learni
View all tutorials
Bases de données

How to Use SQLite with Node.js in 2026

12 minBEGINNER
Base de donnéesJavaScriptNode.jsSQLiteCRUD
Lire en français

Introduction

SQLite is a lightweight, serverless, and embedded relational database, ideal for small to medium-sized Node.js applications. Unlike PostgreSQL or MySQL, which require a dedicated server, SQLite stores everything in a single file, simplifying deployment and cutting costs. In 2026, with the rise of edge and serverless apps, SQLite shines for rapid prototypes, hybrid mobile apps, or CLI tools.

This beginner tutorial walks you through integrating SQLite with better-sqlite3, a modern synchronous and high-performance library. You'll learn to create a database, implement full CRUD operations, and handle transactions. By the end, you'll have a working script ready to bookmark. Every step includes copy-paste code tested on Node.js 20+. (128 words)

Prerequisites

  • Node.js 20+ installed (download here)
  • A terminal (VS Code integrated recommended)
  • Basic JavaScript knowledge (variables, functions, objects)
  • Code editor like VS Code

Initialize the Project and Install better-sqlite3

terminal
mkdir sqlite-nodejs-tutorial
cd sqlite-nodejs-tutorial
npm init -y
npm install better-sqlite3

These commands create a project folder, initialize package.json, and install better-sqlite3—a synchronous wrapper around sqlite3. Why better-sqlite3? It skips complex async callbacks for beginners while delivering native performance. Run in a terminal for setup in 30 seconds.

Create the Database and Users Table

01-setup-db.js
const Database = require('better-sqlite3');

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

db.exec(`
  CREATE TABLE IF NOT EXISTS users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    email TEXT UNIQUE NOT NULL,
    age INTEGER CHECK (age >= 0),
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
  )
`);

console.log('Base de données et table "users" créées avec succès.');

db.close();

This script opens (or creates) the users.db file and runs a DDL query to create the users table with constraints (PRIMARY KEY, UNIQUE, CHECK). Think of it as a mold for your data. Always use IF NOT EXISTS to avoid errors on re-runs. Run with node 01-setup-db.js.

Insert Data (CREATE)

02-insert.js
const Database = require('better-sqlite3');

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

const stmt = db.prepare('INSERT INTO users (name, email, age) VALUES (?, ?, ?)');

const info1 = stmt.run('Alice Dupont', 'alice@example.com', 28);
const info2 = stmt.run('Bob Martin', 'bob@example.com', 35);

console.log('Utilisateurs insérés:', info1.changes + info2.changes, 'lignes affectées');

Prepare a parameterized query with ? placeholders to prevent SQL injection (critical for security). run() returns an object with changes (rows affected). Like a reusable stamp for batch inserts. Test after 01-setup-db.js; re-run without duplicate emails.

Read Data (READ)

03-read.js
const Database = require('better-sqlite3');

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

const allUsers = db.prepare('SELECT * FROM users ORDER BY created_at DESC').all();
const userById = db.prepare('SELECT * FROM users WHERE id = ?').get(1);
const usersByAge = db.prepare('SELECT name, email FROM users WHERE age > ?').all(30);

console.log('Tous les users:', JSON.stringify(allUsers, null, 2));
console.log('User ID 1:', userById);
console.log('Users >30 ans:', usersByAge);

all() fetches all rows as an array, get() one row as an object. ORDER BY sorts results. Like filtering an Excel list. Great for display; use JSON.stringify for debugging. Run after inserts.

Update and Delete Data (UPDATE/DELETE)

04-update-delete.js
const Database = require('better-sqlite3');

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

const updateStmt = db.prepare('UPDATE users SET age = ? WHERE id = ?');
const deleteStmt = db.prepare('DELETE FROM users WHERE id = ?');

updateStmt.run(32, 2);
deleteStmt.run(1);

const remaining = db.prepare('SELECT * FROM users').all();

console.log('Après update/delete:', JSON.stringify(remaining, null, 2));

run() for UPDATE/DELETE returns changes. Always use WHERE to target precisely—otherwise everything changes! Like surgical edits. Check remaining to confirm. Cleans the DB for repeated tests.

Transactions and Backups

05-transactions-backup.js
const Database = require('better-sqlite3');
const fs = require('fs');

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

// Transaction
db.transaction(() => {
  db.prepare('INSERT INTO users (name, email, age) VALUES (?, ?, ?)').run('Charlie', 'charlie@example.com', 25);
  db.prepare('INSERT INTO users (name, email, age) VALUES (?, ?, ?)').run('Diana', 'diana@example.com', 29);
  // Si erreur ici, rollback auto
})();

// Backup
db.backup('users-backup.db');

console.log('Transaction et backup réussis.');
db.close();

transaction() ensures atomicity: all or nothing. backup() copies the entire DB. Essential for data integrity and migrations. Like a locked safe. Useful in production for daily snapshots.

Best Practices

  • Always close the DB: Call db.close() to free resources and avoid file locks.
  • Use prepared statements: Auto-prevents SQL injection, faster for batches.
  • Handle errors: Wrap in try/catch, check constraints (UNIQUE, CHECK).
  • Index filtered columns: db.exec('CREATE INDEX idx_email ON users(email)') to speed up SELECT WHERE.
  • Migrations: Use tools like sqlite3-migrate for team DB versioning.

Common Errors to Avoid

  • Forgetting db.close(): Causes locks on Windows/Mac, making DB unusable.
  • No prepared statements: Vulnerable to injection; never concatenate SQL strings.
  • Nested transactions: better-sqlite3 handles them, but test with large data.
  • Absolute DB paths: Use __dirname + '/data.db' for cross-platform portability.

Next Steps

Master SQLite in depth with our Learni trainings. Resources: