Database

The app stores its data in a local SQLite  database with better-sqlite3  and Drizzle ORM .

• File – app.db in the user data folder (app.getPath('userData')), for example ~/Library/Application Support/Electron App/app.db on macOS
• Mode – WAL journal and foreign keys enabled
• Migrations – generated by drizzle-kit, applied automatically on startup

Schema

Tables are defined in src/main/db/schema.ts:

import { sql } from 'drizzle-orm'
import { integer, sqliteTable, text } from 'drizzle-orm/sqlite-core'
 
export const todos = sqliteTable('todos', {
  id: integer().primaryKey({ autoIncrement: true }),
  title: text().notNull(),
  done: integer({ mode: 'boolean' }).notNull().default(false),
  createdAt: integer({ mode: 'timestamp_ms' })
    .notNull()
    .default(sql`(unixepoch('subsec') * 1000)`)
})

Column names are inferred from the keys. SQLite has few types: use integer({ mode: 'boolean' }) for booleans and integer({ mode: 'timestamp_ms' }) for dates, Drizzle converts them to boolean and Date.

Migrations

Why migrations

The schema file only describes what the database should look like. The actual database lives on each user’s computer, and it keeps its tables and data from one version of the app to the next. When you add a table or a column, the new code expects it to exist, but the app.db of every existing user was created by an older version and does not have it.

A migration is the SQL that brings a database from one version of the schema to the next (CREATE TABLE, ALTER TABLE ADD COLUMN…). Migrations are applied in order, one after the other, so any database, whatever version created it, ends up with the current schema without losing its data. Deleting the database and recreating it is fine in development, never for your users.

Create a migration

After changing the schema, generate a migration:

npm run db:generate -- --name add_notes

drizzle-kit compares the schema with the last snapshot in drizzle/meta/ and writes the SQL needed to go from one to the other:

• drizzle
  • 0000_init.sql
  • 0001_add_notes.sqlNew migration
  • meta
    • _journal.jsonOrdered list of migrations, with their timestamp
    • 0000_snapshot.json
    • 0001_snapshot.jsonSchema after this migration

Review the SQL before committing it. SQLite cannot alter some columns (change a type, add a constraint…): drizzle-kit then recreates the table and copies the rows, check the copy is correct. Commit the whole drizzle/ folder, including meta/.

When migrations run

Migrations are never run by hand. On startup, before the window opens, getDb() (src/main/db/client.ts) opens the database and calls Drizzle’s migrate():

1. The __drizzle_migrations table records every migration already applied to this database, with its timestamp.
2. Every migration in drizzle/ newer than the last applied one runs, in order.
3. They run in a single transaction: if one fails, the database is rolled back to its previous state, the error is thrown and the window never opens. The user keeps a working database, and the next version can ship a fix.

The window and every tRPC procedure only access the database after this step, so the code never sees an outdated schema.

• In development – restart npm run dev to apply a new migration. To start over with an empty database, quit the app and run npm run db:reset.
• In production – electron-builder copies drizzle/ next to the app (extraResources), and it is read from process.resourcesPath/drizzle. The SQL files ship inside the app, no network or tool is needed.

What happens on update

An update (auto update or a manual install) replaces the application, including its drizzle/ folder, but never touches the user data folder: app.db and its data stay where they are.

On the first launch of the new version, getDb() finds the migrations the database has not seen yet and applies them. A user who skipped versions, for example from 1.0 to 1.3, gets the migrations of 1.1, 1.2 and 1.3 in one go, in order. A new user starts with an empty database and gets every migration from 0000_init.

This is why migrations follow strict rules:

• Never edit or delete a migration that has been released. Users already applied it, the change would never reach them, and their database would no longer match the snapshots. Fix a mistake with a new migration.
• Prefer additive changes. Adding a table or a nullable column (or one with a default) is safe. Renaming or dropping a column breaks any code still using it, and data cannot come back after a DROP.
• Downgrades do not undo migrations. A user who reinstalls an older version keeps the newer schema. Additive changes keep working with older code, a renamed or dropped column does not.

Queries

The database is available as ctx.db in every tRPC procedure, or with getDb() anywhere in the main process:

const done = ctx.db.select().from(todos).where(eq(todos.done, true)).all()
const todo = ctx.db.query.todos.findFirst({ where: (t, { eq }) => eq(t.id, 1) })

better-sqlite3 is synchronous: queries are fast and simple, but long queries block the main process. Keep them small or move heavy work to a utility process .

The renderer never queries the database directly: it calls tRPC procedures, which run in the main process.

Native module

better-sqlite3 is a native module. Version 13 ships prebuilt binaries for macOS, Windows and Linux that work in Electron, and the postinstall script (electron-builder install-app-deps) rebuilds native modules for Electron if needed. Packaged apps only include the binary of their platform.

drizzle-kit studio is not set up: it would need the module built for Node.js instead of Electron.

Example: add a notes table

This example adds a notes table, from the schema to the screen.

export const notes = sqliteTable('notes', {
  id: integer().primaryKey({ autoIncrement: true }),
  content: text().notNull(),
  pinned: integer({ mode: 'boolean' }).notNull().default(false),
  createdAt: integer({ mode: 'timestamp_ms' })
    .notNull()
    .default(sql`(unixepoch('subsec') * 1000)`)
})

npm run db:generate -- --name add_notes

CREATE TABLE `notes` (
	`id` integer PRIMARY KEY AUTOINCREMENT NOT NULL,
	`content` text NOT NULL,
	`pinned` integer DEFAULT false NOT NULL,
	`createdAt` integer DEFAULT (unixepoch('subsec') * 1000) NOT NULL
);

import { desc, eq } from 'drizzle-orm'
import { z } from 'zod'
import { notes } from '../../db/schema'
import { publicProcedure, router } from '../trpc'
 
export const notesRouter = router({
  list: publicProcedure.query(({ ctx }) =>
    ctx.db.select().from(notes).orderBy(desc(notes.pinned), desc(notes.createdAt)).all()
  ),
 
  create: publicProcedure
    .input(z.object({ content: z.string().trim().min(1) }))
    .mutation(({ ctx, input }) => ctx.db.insert(notes).values(input).returning().get()),
 
  setPinned: publicProcedure
    .input(z.object({ id: z.number().int(), pinned: z.boolean() }))
    .mutation(({ ctx, input }) => {
      ctx.db.update(notes).set({ pinned: input.pinned }).where(eq(notes.id, input.id)).run()
    })
})

import { notesRouter } from './routers/notes'
 
export const appRouter = router({
  // …
  notes: notesRouter
})

import { trpc } from '@renderer/lib/trpc'
import { useMutation, useQueryClient, useSuspenseQuery } from '@tanstack/react-query'
import { createFileRoute } from '@tanstack/react-router'
 
export const Route = createFileRoute('/notes')({
  loader: ({ context }) => context.queryClient.ensureQueryData(trpc.notes.list.queryOptions()),
  component: Notes
})
 
function Notes() {
  const queryClient = useQueryClient()
  const { data: notes } = useSuspenseQuery(trpc.notes.list.queryOptions())
 
  const invalidate = () => queryClient.invalidateQueries({ queryKey: trpc.notes.list.queryKey() })
  const create = useMutation(trpc.notes.create.mutationOptions({ onSuccess: invalidate }))
 
  return (
    <>
      <button type="button" onClick={() => create.mutate({ content: 'New note' })}>
        Add
      </button>
      <ul>
        {notes.map((note) => (
          <li key={note.id}>
            {note.content} – {note.createdAt.toLocaleDateString()}
          </li>
        ))}
      </ul>
    </>
  )
}