Import a batch of notes from Node.js

We’ll write a small batch importer that stores two related notes and checks its result after reopening the file. This puts the prepared statements and transactions from earlier lessons into application code.

Use a current supported Node.js release with node:sqlite and the connection timeout option. The node:sqlite guide explains the driver API, value types, lock handling, and synchronous execution.

Import a batch atomically

In an empty practice folder, save this as import-notes.mjs. It creates a dedicated exercise database, so it won’t change your course notes.db:

import assert from 'node:assert/strict'
import { DatabaseSync } from 'node:sqlite'

const db = new DatabaseSync('import-practice.db', { timeout: 3000 })

try {
  db.exec('PRAGMA foreign_keys = ON')
  assert.equal(db.prepare('PRAGMA foreign_keys').get().foreign_keys, 1)
  db.exec(`
    CREATE TABLE IF NOT EXISTS notebooks (
      id INTEGER PRIMARY KEY,
      name TEXT NOT NULL
    ) STRICT;
    CREATE TABLE IF NOT EXISTS notes (
      id INTEGER PRIMARY KEY,
      notebook_id INTEGER NOT NULL REFERENCES notebooks(id),
      title TEXT NOT NULL
    ) STRICT;
    INSERT OR IGNORE INTO notebooks (id, name) VALUES (1, 'Planning');
  `)

  const insert = db.prepare(`
    INSERT INTO notes (notebook_id, title) VALUES (?, ?)
  `)
  const before = db.prepare('SELECT count(*) AS total FROM notes').get().total
  const batch = [
    { notebook: 1, title: "Tomorrow's tasks" },
    { notebook: 1, title: 'Plan the week' }
  ]

  db.exec('BEGIN IMMEDIATE')
  try {
    for (const note of batch) {
      insert.run(note.notebook, note.title)
    }
    db.exec('COMMIT')
  } catch (error) {
    db.exec('ROLLBACK')
    throw error
  }

  const after = db.prepare('SELECT count(*) AS total FROM notes').get().total
  assert.equal(after, before + batch.length)
  console.log(`Imported ${after - before} notes`)
} finally {
  db.close()
}

const reopened = new DatabaseSync('import-practice.db', { timeout: 3000 })
try {
  console.log(reopened.prepare(`
    SELECT notes.title, notebooks.name
    FROM notes JOIN notebooks ON notebooks.id = notes.notebook_id
    ORDER BY notes.id DESC LIMIT 2
  `).all())
} finally {
  reopened.close()
}

Run node import-notes.mjs. You should get Imported 2 notes and the latest two titles, each with the notebook name Planning. Run it again: the count grows by two because this importer deliberately appends each batch. It doesn’t deduplicate.

The apostrophe in Tomorrow's tasks survives because it travels as a bound value. exec() creates the schema; prepare() makes reusable statements; run() writes, get() reads one row, and all() reads the result list.

Force the second insert to fail

Change only the second batch item’s notebook to 999. Read the row count with the SQLite shell before running the importer again:

sqlite3 import-practice.db 'SELECT count(*) FROM notes;'
node import-notes.mjs
sqlite3 import-practice.db 'SELECT count(*) FROM notes;'

The process should report a foreign-key failure. The before and after counts must match, even though the first insert was valid. The catch rolls back both inserts; the outer finally closes the connection on failure too.

Restore notebook 1 and run again. Successful imports must still append two rows. Then replace one title with ' OR 1=1 -- and confirm it becomes a literal note title.

Check the application boundary

The three-second timeout allows a short lock conflict to wait before failing. It doesn’t make a long transaction harmless. Keep file reading, parsing, and remote calls outside the transaction when you extend this importer.

DatabaseSync runs on the JavaScript thread. This is convenient for a small importer, but a large batch blocks that thread. Measure before moving the same loop into an HTTP request handler.

Keep this script for the later backup lesson. Reopening the file proves persistence; it does not prove that a backup restores correctly.