Using node:sqlite in Node 24 to Build a Tiny Express Service Without a Separate Database Server

Introduction

I’m Kaleb, a web developer in Pompano Beach, Florida. For my own website and the home automation server I run, I needed a lightweight API that could store a few dozen records – things like scheduled tasks, device metadata, and simple logs. I didn’t want to spin up a separate database server, so I turned to the new node:sqlite module that ships with Node 24. In this article I walk through the exact steps I used to create a small Express service that talks directly to a SQLite file.

Why SQLite with Node 24?

• Zero external server – the database lives in a single file on disk, perfect for a home box.
• Built‑in driver – node:sqlite is part of the Node standard library, no extra native bindings to compile.
• Async/await API – the API mirrors the modern fetch style I already use in my front‑end code.

I chose SQLite because the data model is simple and the performance is more than enough for a few hundred reads/writes per day. If you need massive concurrency or complex analytics, a full RDBMS would be a better fit.

Setting Up the Project

mkdir home‑api && cd home‑api
npm init -y
npm i express
# node:sqlite is built‑in, no npm install needed

The folder now contains a package.json and a node_modules directory for Express.

Creating the SQLite Database

I keep the database file next to the source code for easy backup.

// db/init.js
import { open } from 'node:sqlite';
import { promises as fs } from 'node:fs';

const DB_PATH = './data/home.db';

export async function initDb() {
  // Ensure the data directory exists
  await fs.mkdir('data', { recursive: true });

  const db = await open(DB_PATH, {
    // `sqlite3` driver is used under the hood
    // The `mode` flag defaults to READWRITE | CREATE
  });

  // Simple schema for scheduled tasks
  await db.exec(`
    CREATE TABLE IF NOT EXISTS tasks (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      name TEXT NOT NULL,
      cron TEXT NOT NULL,
      enabled INTEGER NOT NULL DEFAULT 1,
      created_at TEXT DEFAULT (datetime('now'))
    );
  `);

  return db;
}

Running this script once creates data/home.db and the tasks table.

Wiring Express to SQLite

// index.js
import express from 'express';
import { initDb } from './db/init.js';

const app = express();
app.use(express.json());

let db;

// Initialise DB before handling any request
app.use(async (req, res, next) => {
  if (!db) db = await initDb();
  req.db = db;
  next();
});

// GET /tasks – list all scheduled tasks
app.get('/tasks', async (req, res) => {
  try {
    const rows = await req.db.all('SELECT * FROM tasks ORDER BY id DESC');
    res.json(rows);
  } catch (e) {
    console.error(e);
    res.status(500).json({ error: 'Database error' });
  }
});

// POST /tasks – create a new task
app.post('/tasks', async (req, res) => {
  const { name, cron, enabled = 1 } = req.body;
  if (!name || !cron) return res.status(400).json({ error: 'Missing fields' });
  try {
    const stmt = await req.db.run(
      'INSERT INTO tasks (name, cron, enabled) VALUES (?, ?, ?)',
      [name, cron, enabled]
    );
    const newTask = await req.db.get('SELECT * FROM tasks WHERE id = ?', stmt.lastID);
    res.status(201).json(newTask);
  } catch (e) {
    console.error(e);
    res.status(500).json({ error: 'Database error' });
  }
});

// PUT /tasks/:id – toggle enable flag
app.put('/tasks/:id', async (req, res) => {
  const { id } = req.params;
  const { enabled } = req.body;
  if (enabled === undefined) return res.status(400).json({ error: 'enabled required' });
  try {
    await req.db.run('UPDATE tasks SET enabled = ? WHERE id = ?', [enabled, id]);
    const updated = await req.db.get('SELECT * FROM tasks WHERE id = ?', id);
    res.json(updated);
  } catch (e) {
    console.error(e);
    res.status(500).json({ error: 'Database error' });
  }
});

// DELETE /tasks/:id – remove a task
app.delete('/tasks/:id', async (req, res) => {
  const { id } = req.params;
  try {
    await req.db.run('DELETE FROM tasks WHERE id = ?', id);
    res.status(204).end();
  } catch (e) {
    console.error(e);
    res.status(500).json({ error: 'Database error' });
  }
});

const PORT = process.env.PORT || 3000;
app.listen(PORT, () => console.log(`API listening on http://localhost:${PORT}`));

The code is deliberately minimal: each request gets a reference to the same db instance via req.db. Because SQLite locks the file per write, the built‑in driver queues writes automatically – a convenient default for low‑traffic services.

Querying with node:sqlite

node:sqlite exposes three primary methods:

• db.get(sql, params?) – returns a single row.
• db.all(sql, params?) – returns an array of rows.
• db.run(sql, params?) – executes a statement and returns metadata (lastID, changes). All of them return promises, so you can await them directly. The API feels like the older sqlite3 package but without the callback hell.

Error Handling & Validation

I keep validation lightweight – the service only checks for required fields and returns 400 when they’re missing. For production‑grade APIs you’d want a schema validator (e.g., zod or ajv).

All database errors are logged to the console and turned into a generic 500 response. This prevents leaking internal SQLite messages to callers while still giving me enough information in the logs to debug.

Running the Service

node index.js

The API is now reachable at http://localhost:3000. Because the database file lives on the same machine, you can back it up with a simple cp data/home.db backup/ script.

Deploying on My Home Server

I run the service inside a Docker container on a Raspberry Pi that also hosts my personal site. The Dockerfile is tiny:

FROM node:24-alpine
WORKDIR /app
COPY package*.json ./
RUN npm ci --only=production
COPY . .
EXPOSE 3000
CMD ["node", "index.js"]

Building and running:

docker build -t home‑api .
docker run -d -p 3000:3000 --restart unless-stopped -v $(pwd)/data:/app/data home‑api

The volume mount ensures the SQLite file persists across container restarts.

Lessons Learned

• SQLite is perfect for low‑volume services – the simplicity outweighs the lack of horizontal scaling.
• node:sqlite removes a native‑module pain point – no need to compile sqlite3 binaries on every platform.
• File‑based DB means backup is trivial – a single cp or rsync captures the whole state.
• Be mindful of write contention – SQLite locks the whole file on write, so heavy concurrent writes can become a bottleneck. For my use‑case (a few dozen writes per hour) it’s not an issue.
• Keep the DB close to the app – latency is essentially zero because the file is on the same filesystem.

If you’re building a small internal tool, a personal dashboard, or a home‑automation hub, this pattern lets you get a fully functional REST API up in under an hour.

I build sites and automations for small businesses at dreamfuturestech.com.