Database (SQLite + Drizzle)

The Stack

SparkFeed’s data layer is built on two complementary tools:

Tool	Role
SQLite	The database engine: fast, embedded, zero-config
Drizzle ORM	Type-safe query builder and schema manager
better-sqlite3	Node.js SQLite driver (synchronous, fast)

Why `better-sqlite3`?

Most Node.js SQLite drivers are asynchronous. better-sqlite3 is synchronous. All queries run in-process and return immediately.

For a local-first app like SparkFeed, this is actually preferable:

Simpler code: No async/await for basic queries
Faster: No async overhead for in-process I/O
Predictable: FIFO query execution, no concurrency surprises

// With better-sqlite3 (sync)
const articles = db.prepare('SELECT * FROM articles WHERE is_read = 0').all();

// vs. a typical async driver
const articles = await db.query('SELECT * FROM articles WHERE is_read = 0');

Drizzle Schema

The full database schema is defined in TypeScript using Drizzle’s schema builder:

// src/db/schema.ts
import { sqliteTable, text, integer } from 'drizzle-orm/sqlite-core';

export const folders = sqliteTable('folders', {
  id: integer('id').primaryKey({ autoIncrement: true }),
  name: text('name').notNull(),
  createdAt: text('created_at').default(sql`CURRENT_TIMESTAMP`),
});

export const feeds = sqliteTable('feeds', {
  id: integer('id').primaryKey({ autoIncrement: true }),
  title: text('title').notNull(),
  url: text('url').notNull().unique(),
  siteUrl: text('site_url'),
  faviconUrl: text('favicon_url'),
  folderId: integer('folder_id').references(() => folders.id),
  lastFetchedAt: text('last_fetched_at'),
  createdAt: text('created_at').default(sql`CURRENT_TIMESTAMP`),
});

export const articles = sqliteTable('articles', {
  id: integer('id').primaryKey({ autoIncrement: true }),
  feedId: integer('feed_id').notNull().references(() => feeds.id, { onDelete: 'cascade' }),
  guid: text('guid').notNull().unique(),
  title: text('title').notNull(),
  url: text('url').notNull(),
  content: text('content'),
  summary: text('summary'),
  author: text('author'),
  isRead: integer('is_read', { mode: 'boolean' }).default(false),
  isFavorite: integer('is_favorite', { mode: 'boolean' }).default(false),
  publishedAt: text('published_at'),
  createdAt: text('created_at').default(sql`CURRENT_TIMESTAMP`),
});

Type Inference

Drizzle automatically infers TypeScript types from the schema:

import { type InferSelectModel, type InferInsertModel } from 'drizzle-orm';
import { articles } from '@/db/schema';

type Article = InferSelectModel<typeof articles>;
// {
//   id: number;
//   feedId: number;
//   guid: string;
//   title: string;
//   url: string;
//   content: string | null;
//   isRead: boolean;
//   isFavorite: boolean;
//   publishedAt: string | null;
//   ...
// }

type NewArticle = InferInsertModel<typeof articles>;
// Same but with all optional fields unset

Migrations

Drizzle manages schema changes through migration files:

# Generate a migration after changing schema
npm run db:generate

# Apply pending migrations
npm run db:migrate

Generated migration files are stored in src/db/migrations/ and committed to the repository. They’re automatically applied in CI/CD or when a user updates and runs npm run db:migrate.

Drizzle Studio

During development, use Drizzle Studio to inspect your data:

npm run db:studio

This opens a visual database browser at http://localhost:4983, with no extra tooling required.