Node + SQLite

Use SQLite from Node with better-sqlite3 — a fast, synchronous, zero-config SQL database. Cover prepared statements and transactions.

SQLitebetter-sqlite3prepared statementtransaction

Duration

⏱ About 2 hours

Level

📊 Intermediate

Prerequisite

🎯 db-03 + node-04

OUTCOME

Wire an Express API to SQLite with safe prepared statements

What you'll learn

1Install and initialize better-sqlite3
2Run prepared statements to prevent SQL injection
3Group writes in a transaction for atomicity
4Migrate the JSON-based REST API to SQLite

1. Setup

bash

npm install better-sqlite3

javascript

import Database from "better-sqlite3";
const db = new Database("app.db");

db.exec(`
  CREATE TABLE IF NOT EXISTS tasks (
    id     INTEGER PRIMARY KEY AUTOINCREMENT,
    title  TEXT NOT NULL,
    done   INTEGER NOT NULL DEFAULT 0,
    created_at TEXT NOT NULL DEFAULT (datetime('now'))
  );
`);

2. Prepared Statements

javascript

// Insert
const insert = db.prepare("INSERT INTO tasks (title) VALUES (?)");
const { lastInsertRowid } = insert.run("Buy milk");

// Query
const all = db.prepare("SELECT * FROM tasks ORDER BY id");
const tasks = all.all();

const byId = db.prepare("SELECT * FROM tasks WHERE id = ?");
const task = byId.get(1);

⚠️

Always use placeholders (?). String concatenation in SQL is the path to SQL injection.

3. Transactions

javascript

const transfer = db.transaction((fromId, toId, amount) => {
  db.prepare("UPDATE accounts SET balance = balance - ? WHERE id = ?").run(amount, fromId);
  db.prepare("UPDATE accounts SET balance = balance + ? WHERE id = ?").run(amount, toId);
});

transfer(1, 2, 100);   // both UPDATEs succeed or both roll back

4. Wire to Express

javascript

app.get("/tasks", (req, res) => {
  res.json(all.all());
});

app.post("/tasks", (req, res) => {
  const info = insert.run(req.body.title);
  res.status(201).json(byId.get(info.lastInsertRowid));
});

Example code / lecture materials

All lecture materials and example code are openly available on GitHub.

View on GitHub ↗