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éesTypeScriptNode.jsSQLiteCRUD
Lire en français

Introduction

SQLite is a lightweight, serverless embedded relational database, perfect for local Node.js apps or quick prototypes. Unlike PostgreSQL or MySQL, which need a dedicated server, SQLite stores everything in a single .db file, simplifying deployment and cutting costs. In 2026, with the rise of edge apps and offline-first experiences (like PWAs or CLI tools), SQLite remains essential for its reliability and portability.

This beginner tutorial shows you how to integrate SQLite using better-sqlite3, a synchronous and performant library. We'll build a full CRUD API to manage a todo list: table creation, insertion, reading, updating, and deletion. Each step includes functional TypeScript code ready to copy-paste. By the end, you'll master the basics for production-ready apps. Estimated time: 15 minutes.

Prerequisites

  • Node.js 20+ installed
  • Basic knowledge of TypeScript and SQL
  • An editor like VS Code
  • Terminal (bash or PowerShell)

Initialize the Project and Install Dependencies

terminal
mkdir sqlite-todo-app
cd sqlite-todo-app
npm init -y
npm install better-sqlite3 typescript @types/node ts-node
npm install -D @types/better-sqlite3

These commands create a Node.js project, initialize package.json, and install better-sqlite3 for synchronous SQLite interactions. TypeScript is added for static typing to prevent runtime errors. Run npx tsc --init afterward to set up tsconfig.json.

Set Up TypeScript and Create the Database

db.ts
import Database from 'better-sqlite3';

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

db.exec(`
  CREATE TABLE IF NOT EXISTS todos (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    title TEXT NOT NULL,
    completed BOOLEAN DEFAULT FALSE,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
  )
`);

console.log('Base de données initialisée avec table todos.');

export default db;

This code opens (or creates) the todos.db file and runs a SQL query to create the todos table if it doesn't exist. Columns include an auto-incrementing ID, title, boolean status, and creation date. It's idempotent thanks to IF NOT EXISTS, avoiding errors on rerun.

Implement Task Creation (INSERT)

createTodo.ts
import db from './db';

const insertTodo = db.prepare('INSERT INTO todos (title) VALUES (?)');

const newTodo = insertTodo.run('Apprendre SQLite');

console.log('Tâche créée :', newTodo.lastInsertRowid);

We prepare a parameterized INSERT query to add a new task. run() executes it and returns metadata like the generated ID. The ? placeholders prevent SQL injection—a key security best practice from the start.

Read All Tasks (SELECT)

readTodos.ts
import db from './db';

const selectAll = db.prepare('SELECT * FROM todos ORDER BY created_at DESC');

const todos = selectAll.all();

console.log('Toutes les tâches :', todos);

prepare() optimizes repeated queries. all() fetches all rows as an array of objects. ORDER BY sorts by recent date first for intuitive UX. Run this after creation to see your data.

Update a Task (UPDATE)

updateTodo.ts
import db from './db';

const updateTodo = db.prepare('UPDATE todos SET completed = ? WHERE id = ?');

const changes = updateTodo.run(true, 1);

console.log('Tâches modifiées :', changes.changes);

This UPDATE marks a task as completed by ID. run() returns the number of affected rows (changes). Always use WHERE to target precisely and avoid accidental mass updates.

Delete a Task (DELETE) and Close the DB

deleteTodo.ts
import db from './db';

const deleteTodo = db.prepare('DELETE FROM todos WHERE id = ?');

const changes = deleteTodo.run(1);

console.log('Tâches supprimées :', changes.changes);

db.close();
console.log('Base de données fermée.');

DELETE removes by ID. Always close the DB with close() at the end of your app to free resources and prevent file locks. Run scripts in order to test the full CRUD cycle.

Main Script to Run the Full CRUD

index.ts
import db from './db';
import './createTodo';
import './readTodos';
import './updateTodo';
import './deleteTodo';

// Nettoyage final
db.close();
console.log('Démonstration CRUD terminée. Vérifiez todos.db !');

This file orchestrates all scripts for a complete flow. Run with npx ts-node index.ts. It demonstrates a realistic CRUD cycle. Changes persist in todos.db across runs.

Best Practices

  • Always prepare queries: Use prepare() for performance and anti-injection security.
  • Handle transactions: Wrap multi-operation code in db.transaction() for atomicity.
  • Regular backups: Copy *.db files before migrations; use db.backup() for exports.
  • Index columns: Add CREATE INDEX idx_title ON todos(title); to speed up searches.
  • Close the DB: Call close() systematically at app end.

Common Errors to Avoid

  • Forgetting IF NOT EXISTS: Causes crashes on rerun; always make it idempotent.
  • No WHERE in UPDATE/DELETE: Risks modifying/deleting everything.
  • Not closing the DB: Locks the file, causing concurrent access errors.
  • Ignoring types: SQLite is loosely typed; validate in JS/TS for consistency.

Next Steps

Master migrations with prisma.io or knexjs.org. Explore Drizzle ORM for pure TypeScript. Check out our Learni trainings on databases for advanced Node.js and scalable architectures.