Local Database (SQLite)

Why SQLite?

SQLite is a self-contained, serverless, zero-configuration database engine. Unlike PostgreSQL or MySQL, SQLite doesn’t run as a separate process. It’s a library that your application links against, and it reads/writes a single file on disk.

This makes it ideal for SparkFeed’s local-first philosophy:

• No server to manage: No PostgreSQL service, no Docker container
• No network overhead: Queries happen in-process, in microseconds
• Portable: Your entire database is a single .db file you can move or share
• Reliable: SQLite is one of the most widely tested software libraries in existence

Tip

SQLite powers the majority of mobile apps, browsers, and embedded systems worldwide. It handles millions of reads/writes per day without breaking a sweat.

Database Schema

SparkFeed’s database consists of four core tables:

feeds

Stores all the RSS feed subscriptions.

CREATE TABLE feeds (
  id          INTEGER PRIMARY KEY AUTOINCREMENT,
  title       TEXT NOT NULL,
  url         TEXT NOT NULL UNIQUE,
  site_url    TEXT,
  favicon_url TEXT,
  folder_id   INTEGER REFERENCES folders(id),
  last_fetched_at DATETIME,
  created_at  DATETIME DEFAULT CURRENT_TIMESTAMP
);

articles

Stores fetched articles from all feeds.

CREATE TABLE articles (
  id           INTEGER PRIMARY KEY AUTOINCREMENT,
  feed_id      INTEGER NOT NULL REFERENCES feeds(id) ON DELETE CASCADE,
  guid         TEXT NOT NULL UNIQUE,
  title        TEXT NOT NULL,
  url          TEXT NOT NULL,
  content      TEXT,
  summary      TEXT,
  author       TEXT,
  is_read      INTEGER DEFAULT 0,
  is_favorite  INTEGER DEFAULT 0,
  published_at DATETIME,
  created_at   DATETIME DEFAULT CURRENT_TIMESTAMP
);

folders

Organizes feeds into named groups.

CREATE TABLE folders (
  id         INTEGER PRIMARY KEY AUTOINCREMENT,
  name       TEXT NOT NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

settings

Key-value store for user preferences.

CREATE TABLE settings (
  key   TEXT PRIMARY KEY,
  value TEXT NOT NULL
);

Drizzle ORM

SparkFeed uses Drizzle ORM to interact with SQLite. Drizzle is:

• Type-safe: Your queries are typed end-to-end, with TypeScript autocompletion
• SQL-first: Drizzle’s API is close to raw SQL, so it’s easy to understand
• Lightweight: No magic, no hidden behavior

Example: Fetching unread articles

import { db } from '@/db';
import { articles, feeds } from '@/db/schema';
import { eq, desc } from 'drizzle-orm';

const unreadArticles = await db
  .select({
    id: articles.id,
    title: articles.title,
    url: articles.url,
    publishedAt: articles.publishedAt,
    feedTitle: feeds.title,
  })
  .from(articles)
  .innerJoin(feeds, eq(articles.feedId, feeds.id))
  .where(eq(articles.isRead, 0))
  .orderBy(desc(articles.publishedAt))
  .limit(50);

Database Location

By default, the database file is stored at the project root:

sparkfeed/
└── local.db   ← Your database

You can change this path via the DATABASE_URL environment variable:

DATABASE_URL=./data/sparkfeed.db

Back up your database

Your local.db file contains all your feeds, articles, and favorites. Make sure to back it up regularly by copying it to another location, an external drive, or a cloud storage folder.

Running Migrations

When you update SparkFeed, new migrations may add columns or create new tables. Always run migrations after pulling updates:

npm run db:migrate

Drizzle tracks which migrations have already been applied and only runs new ones.