Skip to main content

Chapter 16 - SQLite Databases (JavaScript)

Here's a JavaScript-flavoured version of the same concepts, using the better-sqlite3 npm package for synchronous SQLite access from Node.js.

Why databases and why SQLite​

  • Same motivation: databases make complex queries easy compared with scanning JSON/CSV files.
  • Node.js doesn't include SQLite built-in, but better-sqlite3 is the most popular synchronous SQLite library for Node.
  • Install with npm install better-sqlite3.

Spreadsheets vs databases​

Same concepts apply:

  • A table ≈ a spreadsheet; rows = records, columns = fields.
  • SQLite automatically adds a rowid primary key column.
  • Databases hold raw, structured data; spreadsheets are formatted forms.

SQLite vs other SQL databases​

Same differences apply regardless of language: single file, in-process, small footprint, loose typing (with optional strict mode), no user/permission system, public domain.

Creating databases and tables​

Connecting to a database​

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

const db = new Database("example.db");
// better-sqlite3 is synchronous; no callbacks needed

Call db.close() when done.

Creating a table​

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

const db = new Database("example.db");
db.exec(`
  CREATE TABLE IF NOT EXISTS cats (
    name TEXT NOT NULL,
    birthdate TEXT,
    fur TEXT,
    weight_kg REAL
  ) STRICT
`);
  • db.exec() runs SQL that doesn't return rows.
  • Same convention: uppercase SQL keywords, lowercase table/column names.

Data types and STRICT mode​

Same SQLite types: NULL, INTEGER, REAL, TEXT, BLOB.

JavaScript mapping:

  • NULL → null
  • INTEGER → number (or BigInt for large values)
  • REAL → number
  • TEXT → string
  • BLOB → Buffer

STRICT mode works the same way — wrong-type inserts throw errors.

Listing tables and columns​

const tables = db
  .prepare('SELECT name FROM sqlite_schema WHERE type="table"')
  .all();
console.log(tables);  // [{ name: 'cats' }]

const cols = db.prepare("PRAGMA TABLE_INFO(cats)").all();
console.log(cols);
// [{ cid: 0, name: 'name', type: 'TEXT', notnull: 1, ... }, ...]

better-sqlite3 returns objects (not tuples), so you access row.name instead of row[0].

CRUD database operations​

Same CRUD pattern: INSERT, SELECT, UPDATE, DELETE.

Inserting data​

Simple insert:

db.exec(`
  INSERT INTO cats VALUES ("Zophie", "2021-01-24", "black", 5.6)
`);

Avoiding SQL injection with placeholders​

Use ? placeholders with .run():

Bad:

const catName = "Zophie";
// String interpolation is vulnerable to SQL injection
db.exec(`INSERT INTO cats VALUES ("${catName}", "2021-01-24", "black", 5.6)`);

Good:

const catName = "Zophie";
const catBday = "2021-01-24";
const furColor = "black";
const catWeight = 5.6;

db.prepare("INSERT INTO cats VALUES (?, ?, ?, ?)").run(
  catName, catBday, furColor, catWeight
);

prepare() creates a reusable statement; .run() executes it with bound parameters.

Transactions​

better-sqlite3 supports explicit transactions for batching writes:

const insertMany = db.transaction((cats) => {
  const stmt = db.prepare("INSERT INTO cats VALUES (?, ?, ?, ?)");
  for (const cat of cats) {
    stmt.run(cat.name, cat.birthdate, cat.fur, cat.weight);
  }
});

insertMany([
  { name: "Zophie", birthdate: "2021-01-24", fur: "black", weight: 5.6 },
  { name: "Pooka", birthdate: "2022-03-15", fur: "orange", weight: 4.2 },
]);

Reading data (SELECT)​

Basic read:

const rows = db.prepare("SELECT * FROM cats").all();
console.log(rows);
// [{ name: 'Zophie', birthdate: '2021-01-24', fur: 'black', weight_kg: 5.6 }]
  • .all() returns all rows as an array of objects.
  • .get() returns one row (or undefined).

Selecting specific columns:

const rows = db.prepare("SELECT rowid, name FROM cats").all();
console.log(rows);  // [{ rowid: 1, name: 'Zophie' }]

Looping over results​

Use .iterate() for memory-efficient row-by-row processing:

for (const row of db.prepare("SELECT * FROM cats").iterate()) {
  console.log("Row data:", row);
  console.log(row.name, "is one of my favorite cats.");
}

Filtering (WHERE), matching (LIKE / GLOB), sorting (ORDER BY), limiting (LIMIT)​

WHERE clause​

const rows = db
  .prepare('SELECT * FROM cats WHERE fur = "black"')
  .all();

With placeholders:

const rows = db
  .prepare("SELECT * FROM cats WHERE fur = ?")
  .all("black");

Combined conditions:

const rows = db
  .prepare(
    'SELECT * FROM cats WHERE fur = "black" OR birthdate >= "2024-01-01"'
  )
  .all();

LIKE with % wildcards (case-insensitive)​

const rows = db
  .prepare('SELECT rowid, name FROM cats WHERE name LIKE "%y"')
  .all();

GLOB with * wildcards (case-sensitive)​

const rows = db
  .prepare('SELECT rowid, name FROM cats WHERE name GLOB "*m*"')
  .all();

ORDER BY​

const rows = db
  .prepare("SELECT * FROM cats ORDER BY fur ASC, birthdate DESC")
  .all();

LIMIT​

const rows = db
  .prepare('SELECT * FROM cats WHERE fur="orange" ORDER BY birthdate LIMIT 4')
  .all();

Indexes for faster reads​

Same SQL commands work in better-sqlite3:

db.exec("CREATE INDEX idx_name ON cats (name)");
db.exec("CREATE INDEX idx_birthdate ON cats (birthdate)");

List indexes:

const idxs = db
  .prepare(
    'SELECT name FROM sqlite_schema WHERE type="index" AND tbl_name="cats"'
  )
  .all();
console.log(idxs);  // [{ name: 'idx_name' }, { name: 'idx_birthdate' }]

Drop an index:

db.exec("DROP INDEX idx_name");

Updating data (UPDATE)​

db.prepare("UPDATE cats SET fur = ? WHERE name = ?").run(
  "gray tabby",
  "Zophie"
);

Without a WHERE, all rows would be updated.

Deleting data (DELETE)​

Delete by rowid:

db.prepare("DELETE FROM cats WHERE rowid = ?").run(1);

Delete by condition:

db.prepare('DELETE FROM cats WHERE fur = "black"').run();

Omit WHERE only if you truly intend to delete all rows.

Overall idea of the chapter​

Chapter 16 applies to JavaScript with better-sqlite3: same SQLite concepts (single-file database, typed columns, STRICT mode, CRUD via SQL, WHERE/LIKE/GLOB/ORDER BY/LIMIT, indexes, ? placeholders for injection safety). The main difference is API style: db.prepare(sql).all() / .run() / .get() instead of conn.execute(sql).fetchall(), and results come back as objects with named properties instead of tuples.