An SQLite database

A JSON file stops coping when there are thousands of posts or two requests try to write the file at the same time. Then the data moves to a database. SQLite is a small database stored in a single file that needs no separate server. In Node.js 22.13 and newer it is built in: the node:sqlite module. The SQL query language is used here in its simplest form.

Connecting and creating a table

The DatabaseSync class opens a database file (or creates it). The exec() method runs an SQL command:

import { DatabaseSync } from 'node:sqlite';
 
const db = new DatabaseSync('blog.db');
 
db.exec(`
  CREATE TABLE IF NOT EXISTS posts (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    title TEXT NOT NULL,
    body TEXT NOT NULL DEFAULT ''
  )
`);
 
console.log('The posts table is ready');

A blog.db file appears next to the program. CREATE TABLE IF NOT EXISTS creates the table only on the first run, and AUTOINCREMENT assigns post numbers itself. Instead of a file name you can pass ':memory:' — the database then lives only in memory.

Inserting

A query with data is prepared with prepare(), and the values replace the ? marks:

import { DatabaseSync } from 'node:sqlite';
 
const db = new DatabaseSync(':memory:');
db.exec('CREATE TABLE posts (id INTEGER PRIMARY KEY AUTOINCREMENT, title TEXT, body TEXT)');
 
const insert = db.prepare('INSERT INTO posts (title, body) VALUES (?, ?)');
const result = insert.run('Hello, SQLite', 'The first post in the database');
 
console.log(result.lastInsertRowid); // 1

run() returns an object with the new row's number and the number of changed rows (changes).

Querying

all() returns an array of all matching rows, get() one row or undefined:

import { DatabaseSync } from 'node:sqlite';
 
const db = new DatabaseSync(':memory:');
db.exec('CREATE TABLE posts (id INTEGER PRIMARY KEY AUTOINCREMENT, title TEXT)');
const insert = db.prepare('INSERT INTO posts (title) VALUES (?)');
insert.run('Hello, Node.js');
insert.run('Working with files');
 
const posts = db.prepare('SELECT id, title FROM posts ORDER BY id').all();
for (const post of posts) console.log(post.id, post.title);
 
const post = db.prepare('SELECT title FROM posts WHERE id = ?').get(2);
console.log(post.title); // Working with files

Two lines with posts are printed, then the title of the second one.

In the blog project

The src/posts.js module can be rewritten to use the database while keeping the same functions — the server will not notice:

// src/posts.js
import { DatabaseSync } from 'node:sqlite';
 
const db = new DatabaseSync('blog.db');
 
export async function loadPosts() {
  return db.prepare('SELECT * FROM posts ORDER BY id DESC').all();
}
 
export async function findPost(id) {
  return db.prepare('SELECT * FROM posts WHERE id = ?').get(id);
}

The node:sqlite methods are synchronous; async is kept so the server code with await works unchanged. If your Node.js is older than 22.13, use the better-sqlite3 package — its interface is very similar.

Common mistake

Never insert user data straight into the query text:

// src/posts.js
// Dangerous: db.prepare(`SELECT * FROM posts WHERE title = '${title}'`)

A title like ' OR '1'='1 changes the meaning of the query — this is SQL injection. Always pass values through ? and the arguments of run(), get() or all(): the database inserts them safely.