DEV Community

Arslan Ahmad
Arslan Ahmad

Posted on

How We Built a Multi-Tenant SaaS Using SQLite and Node.js

#webdev #javascript #database #sqlite

When building a multi-tenant SaaS, the default industry advice is to boot up a distributed PostgreSQL cluster, configure complex connection pools, and manage microservices. But for a bootstrapped team, this architecture represents a massive developer tax and cash leak.

For Fintasko (our project and financial workspace OS), we took a different route: a single local SQLite database running on a low-cost VPS.

Here is how we made SQLite safe, fast, and secure for a multi-tenant SaaS.

1. The Engine: Node.js + better-sqlite3 + WAL Mode

SQLite is often dismissed as a file-system toy. This is a mistake. When paired with better-sqlite3 and configured in Write-Ahead Logging (WAL) mode, SQLite easily handles concurrent read-write workloads.

By default, SQLite locks the database file on writes. WAL mode changes this. It allows multiple readers to read the database while another process writes to it. We execute this during initialization:

import Database from 'better-sqlite3';
const db = new Database('server/db/fintasko.sqlite');

// Enable WAL mode for high concurrency
db.pragma('journal_mode = WAL');
db.pragma('synchronous = NORMAL');
Enter fullscreen mode Exit fullscreen mode

This simple setup eliminates network latency. Because the database sits locally on the same SSD as our Node server, database queries execute in microseconds (under 0.1ms) rather than the 5-15ms required to query a managed cloud database.

2. Multi-Tenant Security: Strict Workspace Scoping

In a SaaS database, the biggest risk is data leakage. We enforce strict data isolation at the query level. Every table (projects, tasks, clients, invoices) contains a workspace_id column.

Every backend route is guarded by auth middleware that extracts the tenant's workspace_id from their secure JWT session cookie. No database query is allowed to run without a workspace_id = ? check:

SELECT * FROM tasks 
WHERE project_id = ? AND workspace_id = ?;
Enter fullscreen mode Exit fullscreen mode

If a user tries to query a task ID belonging to another company, the query returns nothing. We treat workspace-scoping as a security hard block.

3. Self-Healing Schema Migrations

Deploying schema updates to production shouldn't require manual database operations. We built a self-healing migration layer. When the app boots, it executes a migration runner that auto-adds columns or tables if they don't exist:

// server/db/migrate.js
import Database from 'better-sqlite3';
const db = new Database('server/db/fintasko.sqlite');

try {
  // Safe migrations
  db.prepare("ALTER TABLE blogs ADD COLUMN scheduled_at TEXT").run();
} catch (e) {
  // Column already exists, ignore error
}
Enter fullscreen mode Exit fullscreen mode

This prevents database crashes during automated deployments and makes staging/production parity seamless.

4. Zero-Network Security Profile

Because SQLite runs in-process, it does not open any network ports. There is no database port to attack, no firewall rule to misconfigure, and no network packet sniffer that can inspect query results. The standard filesystem permissions of our Ubuntu node protect the database file, providing enterprise-grade security with zero operational overhead.

5. Automated backups

A single-file database makes backups simple. Instead of setting up replica nodes, we run a daily cron task that zips the sqlite file and sends it to offsite encrypted storage:

zip -r /backups/db-$(date +%F).zip /home/fintasko/fintasko/server/db/fintasko.sqlite
Enter fullscreen mode Exit fullscreen mode

Conclusion

By avoiding distributed databases and focusing on SQLite, we reduced our hosting infrastructure cost to a $10/month cloud node while delivering speeds that blow complex microservice setups out of the water.

Check out Fintasko to see our work in action.

Top comments (1)

Collapse
 
mads_hansen_27b33ebfee4c9 profile image
Mads Hansen •

SQLite can be a strong fit here, but I would tighten three production boundaries. Use versioned migrations in explicit transactions rather than catching every ALTER TABLE error as “already exists”; omitted tenant filters need centralized scoped query helpers plus planted cross-tenant tests (or file-per-tenant isolation); and do not zip a live database while WAL is active—use SQLite’s online backup API, .backup, or VACUUM INTO, then test restores. File permissions reduce network exposure, but they do not isolate tenants from a compromised application process.