Building a Tiny Express API with node:sqlite on Node 24 – No Separate DB Server Needed

Why a local service business can get by without a separate database server

When I was setting up the backend for my own website and the home automation hub I run for Dream Futures Tech, I kept hitting a wall: I needed a place to store a few dozen records (appointments, device states, simple logs) but I didn’t want the overhead of a full‑blown MySQL or Postgres instance on a cheap home server. Node 24 introduced the node:sqlite built‑in module, which gives you a zero‑config, file‑based SQLite engine right inside the runtime. The result? A single‑file database that lives next to your code, no extra services to monitor, and an Express API that can be hit from my mobile app, a web dashboard, or even a webhook from a smart‑home device.

Project layout – keeping it simple

my‑api/
├─ src/
│  ├─ db.js          # SQLite helper
│  ├─ routes.js      # Express routes
│  └─ index.js       # Server entry point
├─ data/
│  └─ app.db         # SQLite file (auto‑created)
├─ package.json
└─ .gitignore        # ignore data/app.db in repo

All the code lives under src/. The data/ folder is where the SQLite file lives; I add it to .gitignore because it’s generated at runtime.

Step 1 – Bootstrap the project

# On my home server (Ubuntu 22.04) I install Node 24 via nvm
nvm install 24
nvm use 24

# Create the project folder
mkdir my-api && cd my-api
npm init -y
npm i express

No extra dependency for SQLite – the node:sqlite module is part of the runtime.

Step 2 – Create a tiny DB helper

src/db.js

import { open } from 'node:sqlite';
import sqlite3 from 'sqlite3'; // driver that node:sqlite expects
import { join } from 'node:path';

// One‑time async init – returns a DB handle you can reuse
export async function initDB() {
  const dbPath = join(process.cwd(), 'data', 'app.db');
  const db = await open({
    filename: dbPath,
    driver: sqlite3.Database,
  });

  // Create a table if it doesn't exist – id, name, scheduled_at
  await db.exec(`
    CREATE TABLE IF NOT EXISTS appointments (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      client_name TEXT NOT NULL,
      service TEXT NOT NULL,
      scheduled_at TEXT NOT NULL
    );
  `);

  return db;
}

A few notes:

• I use process.cwd() so the path works whether I run the server from the project root or via a PM2 script.
• The scheduled_at column is stored as ISO‑8601 text; SQLite has no native datetime type, but string sorting works for simple range queries.

Step 3 – Wire up Express routes

src/routes.js

import { Router } from 'express';

export function createRouter(db) {
  const router = Router();

  // GET /appointments – list all upcoming appointments
  router.get('/appointments', async (req, res) => {
    const rows = await db.all(
      `SELECT * FROM appointments WHERE scheduled_at >= datetime('now') ORDER BY scheduled_at`
    );
    res.json(rows);
  });

  // POST /appointments – create a new record
  router.post('/appointments', async (req, res) => {
    const { client_name, service, scheduled_at } = req.body;
    if (!client_name || !service || !scheduled_at) {
      return res.status(400).json({ error: 'Missing fields' });
    }
    const stmt = await db.run(
      `INSERT INTO appointments (client_name, service, scheduled_at)
       VALUES (?, ?, ?)`,
      client_name,
      service,
      scheduled_at
    );
    res.status(201).json({ id: stmt.lastID });
  });

  // DELETE /appointments/:id – cancel
  router.delete('/appointments/:id', async (req, res) => {
    const { id } = req.params;
    const result = await db.run(`DELETE FROM appointments WHERE id = ?`, id);
    if (result.changes === 0) {
      return res.status(404).json({ error: 'Not found' });
    }
    res.json({ deleted: id });
  });

  return router;
}

I keep the API deliberately tiny – just enough for my front‑end calendar widget and a webhook that lets my smart‑home hub automatically block out time when a service call is scheduled.

Step 4 – Assemble the server

src/index.js

import express from 'express';
import bodyParser from 'body-parser';
import { initDB } from './db.js';
import { createRouter } from './routes.js';

async function start() {
  const db = await initDB();
  const app = express();

  app.use(bodyParser.json());
  app.use('/api', createRouter(db));

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

start().catch(err => {
  console.error('Failed to start server', err);
  process.exit(1);
});

A couple of practical points:

• Error handling – I wrap the whole startup in a try/catch; if the DB file can’t be opened (e.g., permission issue) the process exits early so my supervisor (PM2) restarts it.
• Graceful shutdown – In production I listen for SIGTERM and call db.close() before exiting. The built‑in driver supports await db.close();.

Step 5 – Run it locally and test

# First run – the DB file will be created automatically
node src/index.js

From another terminal you can curl the endpoints:

# List (empty at first)
curl http://localhost:3000/api/appointments

# Add an appointment
curl -X POST http://localhost:3000/api/appointments \
  -H 'Content-Type: application/json' \
  -d '{"client_name":"Jane Doe","service":"Air‑Conditioner Tune‑up","scheduled_at":"2024-12-01T10:00:00"}'

# Verify it was stored
curl http://localhost:3000/api/appointments

You’ll see a JSON array with the newly inserted row. Because the DB lives in a single file, you can back it up with a simple cp data/app.db backup/ – no dump scripts required.

Practical takeaways for a small‑business owner

1. Zero‑maintenance data store – With node:sqlite you avoid the operational overhead of a separate DB server. The file is created on first run and can be version‑controlled or backed up like any other asset.
2. Fast enough for low‑traffic APIs – SQLite reads/writes are atomic and lock‑free for the tiny workloads typical of a local service business (dozens of requests per minute at most). You’ll notice sub‑millisecond latency on a modest Raspberry Pi or a cheap VPS.
3. Portable code base – Because the database is just a file, moving the whole service to a new host is as easy as scp -r my‑api/ new‑host:. No migration scripts, no network latency between app and DB.

When to consider scaling up

If you ever cross the threshold of a few hundred concurrent requests, or need advanced features like replication, you’d look at a dedicated server. Until then, node:sqlite gives me a reliable, low‑cost backend that fits on the same box as my Express server.

---

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