Exploring the technical decisions behind using SQLite as our database and how it enables true self-hosting.
When we started building Moneypile, one of our first and most important decisions was choosing a database. We needed something that aligned with our core principle: users should have complete control over their financial data. After extensive evaluation, SQLite emerged as the perfect choice. Here's why.
Most modern applications assume a cloud-first architecture with managed databases like PostgreSQL or MySQL. But this creates friction for self-hosters:
We wanted Moneypile to be as simple as downloading a single file and running it. SQLite makes this possible.
With SQLite, there's no database server to install, no users to create, no network to configure. The database is just a file:
import Database from 'better-sqlite3';
// That's it. Database created and ready to use.
const db = new Database('./config/moneypile.sqlite');
// Automatic schema creation on first run
db.exec(`
CREATE TABLE IF NOT EXISTS accounts (
id TEXT PRIMARY KEY,
name TEXT NOT NULL,
type TEXT NOT NULL,
balance INTEGER NOT NULL,
created_at INTEGER NOT NULL
)
`);
Contrary to popular belief, SQLite is incredibly fast for our use case. Personal finance data, even accumulated over decades, is relatively small:
-- Typical Moneypile database sizes:
-- 1 year of data: ~10MB
-- 5 years of data: ~40MB
-- 10 years of data: ~75MB
-- Query performance remains sub-millisecond:
EXPLAIN QUERY PLAN
SELECT
date(date/1000, 'unixepoch') as month,
SUM(amount) as total
FROM transactions
WHERE account_id = ?
GROUP BY month
ORDER BY month DESC;
-- Result: Using covering index, ~0.8ms for 50,000 transactions
Financial data demands reliability. SQLite provides full ACID compliance with robust transaction support:
// Transferring money between accounts - guaranteed consistency
async function transferMoney(
fromAccount: string,
toAccount: string,
amount: number
) {
const transfer = db.transaction(() => {
// Debit source account
db.prepare(`
UPDATE accounts
SET balance = balance - ?
WHERE id = ?
`).run(amount, fromAccount);
// Credit destination account
db.prepare(`
UPDATE accounts
SET balance = balance + ?
WHERE id = ?
`).run(amount, toAccount);
// Record transaction
db.prepare(`
INSERT INTO transactions (account_id, amount, transfer_id)
VALUES (?, ?, ?), (?, ?, ?)
`).run(
fromAccount, -amount, transferId,
toAccount, amount, transferId
);
});
// All or nothing - perfect for financial operations
transfer();
}
We optimized our schema for SQLite's strengths:
-- Denormalized for read performance
CREATE TABLE transactions (
id TEXT PRIMARY KEY,
account_id TEXT NOT NULL,
date INTEGER NOT NULL,
amount INTEGER NOT NULL, -- Store as cents to avoid float issues
payee_name TEXT,
category_id TEXT,
notes TEXT,
cleared INTEGER DEFAULT 0,
-- Denormalized account balance after transaction
running_balance INTEGER,
FOREIGN KEY (account_id) REFERENCES accounts(id)
);
-- Optimized indexes for common queries
CREATE INDEX idx_transactions_date ON transactions(date);
CREATE INDEX idx_transactions_account_date ON transactions(account_id, date);
CREATE INDEX idx_transactions_payee ON transactions(payee_name);
We enable WAL mode for better concurrency and performance:
// Enable WAL mode on startup
db.pragma('journal_mode = WAL');
db.pragma('synchronous = NORMAL');
db.pragma('cache_size = -64000'); // 64MB cache
db.pragma('temp_store = MEMORY');
// Benefits:
// - Readers don't block writers
// - Writers don't block readers
// - Better performance for concurrent access
// - Automatic checkpointing
One of SQLite's best features is dead-simple backups. No need for pg_dump or complex procedures:
// Automatic daily backups
async function backupDatabase() {
const date = new Date().toISOString().split('T')[0];
const backupPath = `./backups/moneypile-${date}.sqlite`;
// It's literally just copying a file!
await fs.copyFile('./config/moneypile.sqlite', backupPath);
// Optional: compress old backups
if (shouldCompress) {
await compressFile(backupPath);
}
}
// Point-in-time recovery using WAL files
async function createHotBackup() {
// SQLite's backup API ensures consistency
await db.backup(`./backups/moneypile-${Date.now()}.sqlite`);
}
Moneypile is designed for personal/family use, not thousands of concurrent users. SQLite excels here:
// Connection pooling for web requests
import { Database } from 'better-sqlite3';
class DatabasePool {
private db: Database;
constructor(path: string) {
this.db = new Database(path);
this.db.pragma('journal_mode = WAL');
// Handle busy timeout
this.db.pragma('busy_timeout = 5000');
}
// All reads can happen concurrently
async query(sql: string, params: any[]) {
return this.db.prepare(sql).all(params);
}
// Writes are serialized but fast
async execute(sql: string, params: any[]) {
return this.db.prepare(sql).run(params);
}
}
-- Built-in FTS5 for searching transactions
CREATE VIRTUAL TABLE transactions_fts USING fts5(
payee_name,
notes,
content=transactions,
content_rowid=id
);
-- Lightning-fast search across years of data
SELECT * FROM transactions
WHERE id IN (
SELECT rowid FROM transactions_fts
WHERE transactions_fts MATCH 'coffee OR starbucks'
)
ORDER BY date DESC;
-- Store flexible metadata as JSON
ALTER TABLE accounts ADD COLUMN metadata JSON;
-- Query JSON data efficiently
SELECT * FROM accounts
WHERE json_extract(metadata, '$.institution') = 'Chase';
-- Calculate running balances
SELECT
date,
amount,
SUM(amount) OVER (
PARTITION BY account_id
ORDER BY date, id
) as running_balance
FROM transactions
WHERE account_id = ?;
We've tested Moneypile with real-world datasets:
SQLite isn't perfect for every use case. Here's what we considered:
We built a robust migration system for schema updates:
// Automatic migrations on startup
async function runMigrations() {
const currentVersion = db.pragma('user_version')[0].user_version;
const migrations = [
{ version: 1, sql: 'CREATE TABLE ...' },
{ version: 2, sql: 'ALTER TABLE ...' },
// ...
];
for (const migration of migrations) {
if (migration.version > currentVersion) {
db.transaction(() => {
db.exec(migration.sql);
db.pragma(`user_version = ${migration.version}`);
})();
}
}
}
SQLite + Docker = Perfect self-hosting experience:
# Simple Dockerfile
FROM node:20-alpine
WORKDIR /app
COPY . .
RUN npm install
RUN npm run build
# SQLite database persisted via volume
VOLUME ["/app/config"]
CMD ["npm", "start"]
# Users just run:
docker run -v ./moneypile-data:/app/config moneypile:latest
# That's it! Database included, no configuration needed.
SQLite embodies Moneypile's philosophy: powerful, simple, and completely under your control. It's not just a databaseit's a statement about software design. By choosing SQLite, we're saying:
For Moneypile, SQLite isn't a compromiseit's the ideal choice. It allows us to deliver a powerful financial management tool that truly belongs to you, runs anywhere, and respects your privacy by design.
"The best database is the one you don't have to think about. With SQLite, our users focus on their finances, not database administration."
Want to explore our SQLite implementation? Check out our database layer on GitHub.