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.