aussiegingersnap/db-sqlite

SQLite Database Skill

Comprehensive patterns for SQLite database management in Node.js/TypeScript projects using Prisma ORM, including schema-first development and Railway deployment with Litestream backup.

When to Use This Skill

• Setting up SQLite in a new project
• Defining database schemas with Prisma
• Running migrations with prisma migrate
• Deploying SQLite to Railway with persistent volumes
• Backing up production databases with Litestream
• Troubleshooting database issues

Core Concepts

SQLite vs Network Databases

SQLite is appropriate when:

• Single server/container deployment
• Read-heavy workloads (or moderate writes)
• Simplicity is valued over horizontal scaling
• Local-first or embedded scenarios
• Cost-sensitive deployments

Consider PostgreSQL when:

• Multiple servers need database access
• Remote database inspection is required
• High write concurrency is expected
• Team needs direct database access for debugging

Railway Deployment Constraints

SQLite on Railway requires understanding these constraints:

1. Volume mounting - Database file must live on a Railway volume (not container filesystem)
2. No remote access - Cannot connect database GUI tools directly to production
3. Single container - Only one instance can write to the database
4. Backup strategy - Use Litestream for continuous backup to Railway Bucket

Critical: Railway Volume Path vs Container Path

This is the #1 cause of data loss on Railway SQLite deployments.

Railway volumes mount at a specific path (e.g., /data). But your app runs in /app/ by default. If your code writes to ./prisma/app.db, it creates the file at /app/prisma/app.db — which is NOT on the volume and gets destroyed on every deploy.

Solution: Set DATABASE_URL to use the volume path in production.

# Wrong (data lost on each deploy):
file:/app/prisma/app.db  ← Container filesystem, not persistent

# Correct (data persists):
file:/data/app.db        ← Railway volume, persistent + backed up

Database Setup Pattern

Package Installation

npm install @prisma/client
npm install -D prisma

Directory Structure

prisma/
├── schema.prisma    # Database schema (source of truth)
└── migrations/      # Generated SQL migrations

src/lib/db/
└── index.ts         # Prisma client singleton

Environment Configuration

# .env.local (development)
DATABASE_URL="file:./prisma/dev.db"

# Railway (production) — REQUIRED
# Must point to volume mount path
DATABASE_URL="file:/data/app.db"

Prisma Schema Setup

Create prisma/schema.prisma:

generator client {
  provider = "prisma-client-js"
}

datasource db {
  provider = "sqlite"
  url      = env("DATABASE_URL")
}

// Define your models here
model User {
  id        String   @id @default(cuid())
  email     String   @unique
  name      String?
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
}

Prisma Client Singleton

Create src/lib/db/index.ts:

import { PrismaClient } from '@prisma/client'

const globalForPrisma = globalThis as unknown as {
  prisma: PrismaClient | undefined
}

export const prisma = globalForPrisma.prisma ?? new PrismaClient({
  log: process.env.NODE_ENV === 'development' ? ['query', 'error', 'warn'] : ['error'],
})

if (process.env.NODE_ENV !== 'production') {
  globalForPrisma.prisma = prisma
}

export default prisma

Package.json Scripts

{
  "scripts": {
    "db:generate": "prisma generate",
    "db:migrate": "prisma migrate dev",
    "db:migrate:prod": "prisma migrate deploy",
    "db:push": "prisma db push",
    "db:studio": "prisma studio",
    "db:reset": "prisma migrate reset"
  }
}

Schema Patterns

Primary Keys

Use CUID or UUID for user-facing entities:

model User {
  id String @id @default(cuid())
  // or: id String @id @default(uuid())
}

Timestamps

Always include created/updated timestamps:

model Post {
  id        String   @id @default(cuid())
  title     String
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
}

Relations

One-to-many:

model User {
  id    String @id @default(cuid())
  posts Post[]
}

model Post {
  id       String @id @default(cuid())
  authorId String
  author   User   @relation(fields: [authorId], references: [id], onDelete: Cascade)
}

Many-to-many (explicit junction table):

model Post {
  id   String     @id @default(cuid())
  tags PostTag[]
}

model Tag {
  id    String    @id @default(cuid())
  name  String    @unique
  posts PostTag[]
}

model PostTag {
  postId String
  tagId  String
  post   Post   @relation(fields: [postId], references: [id], onDelete: Cascade)
  tag    Tag    @relation(fields: [tagId], references: [id], onDelete: Cascade)

  @@id([postId, tagId])
}

Enums

enum Role {
  ADMIN
  USER
  GUEST
}

model User {
  id   String @id @default(cuid())
  role Role   @default(USER)
}

Indexes

model Post {
  id        String   @id @default(cuid())
  authorId  String
  published Boolean  @default(false)
  createdAt DateTime @default(now())

  @@index([authorId])
  @@index([published, createdAt])
}

Unique Constraints

model BookCollaborator {
  id     String @id @default(cuid())
  bookId String
  userId String

  @@unique([bookId, userId])
}

Migration Workflow

Development

# Make schema changes in prisma/schema.prisma

# Create and apply migration
npm run db:migrate
# Prompts for migration name, e.g., "add_posts_table"

# Quick iteration (no migration file, just sync)
npm run db:push

Production

# Apply pending migrations (run in CI/CD or startup)
npm run db:migrate:prod

Migration Best Practices

1. Never edit applied migrations - They may have run in production
2. Name migrations descriptively - add_user_avatar, create_posts_table
3. Review generated SQL - Check prisma/migrations/ before deploying
4. Use db:push for prototyping - Switch to migrations when schema stabilizes
5. Commit migrations - They're part of your codebase

Query Patterns

Basic CRUD

import { prisma } from '@/lib/db'

// Create
const user = await prisma.user.create({
  data: {
    email: '[email protected]',
    name: 'John Doe',
  },
})

// Read one
const user = await prisma.user.findUnique({
  where: { email: '[email protected]' },
})

// Read many with filters
const users = await prisma.user.findMany({
  where: {
    role: 'ADMIN',
    createdAt: { gte: new Date('2024-01-01') },
  },
  orderBy: { createdAt: 'desc' },
  take: 10,
})

// Update
const updated = await prisma.user.update({
  where: { id: userId },
  data: { name: 'Jane Doe' },
})

// Delete
await prisma.user.delete({
  where: { id: userId },
})

With Relations

// Include related data
const userWithPosts = await prisma.user.findUnique({
  where: { id: userId },
  include: {
    posts: {
      where: { published: true },
      orderBy: { createdAt: 'desc' },
    },
  },
})

// Select specific fields
const userEmail = await prisma.user.findUnique({
  where: { id: userId },
  select: {
    email: true,
    posts: {
      select: { title: true },
    },
  },
})

// Nested create
const userWithPost = await prisma.user.create({
  data: {
    email: '[email protected]',
    posts: {
      create: {
        title: 'My First Post',
      },
    },
  },
  include: { posts: true },
})

Transactions

// Sequential operations
const [user, post] = await prisma.$transaction([
  prisma.user.create({ data: { email: '[email protected]' } }),
  prisma.post.create({ data: { title: 'Hello', authorId: 'temp' } }),
])

// Interactive transaction
await prisma.$transaction(async (tx) => {
  const user = await tx.user.create({
    data: { email: '[email protected]' },
  })
  
  await tx.post.create({
    data: {
      title: 'My Post',
      authorId: user.id,
    },
  })
})

Upsert

const user = await prisma.user.upsert({
  where: { email: '[email protected]' },
  update: { name: 'Updated Name' },
  create: { email: '[email protected]', name: 'New User' },
})

Railway Operations

Environment Variables

Required Railway configuration:

DATABASE_URL=file:/data/app.db

Ensure volume is mounted at /data.

Shell Access

# Open interactive shell in Railway container
railway shell

# Inside container - use Prisma Studio (opens web UI)
npx prisma studio

# Or use sqlite3 directly
sqlite3 /data/app.db
.tables
.schema User
SELECT * FROM User LIMIT 5;
.quit

Check Migration Status

railway shell
npx prisma migrate status

Continuous Backup with Litestream

Litestream provides real-time SQLite replication to S3-compatible storage. Combined with Railway Buckets, this gives you continuous backups without external providers.

See references/litestream.md for complete setup guide.

Quick Overview

1. Litestream monitors SQLite WAL changes
2. Streams changes to Railway Bucket every ~10 seconds
3. On container restart, restores from bucket if local DB is missing
4. ~10 second recovery point objective (RPO)

Minimal Setup

# 1. Create Railway Bucket
railway add --service bucket

# 2. Add litestream.yml to project root
# 3. Update nixpacks.toml to install litestream
# 4. Update railway.toml start command
# 5. Add restore script for empty volumes

Troubleshooting

"Cannot find module '@prisma/client'"

Generate the client after schema changes:

npm run db:generate

"Migration failed"

Check migration status and pending migrations:

npx prisma migrate status

For stuck migrations, you may need to mark as applied or reset:

# Mark a migration as applied (use with caution)
npx prisma migrate resolve --applied <migration_name>

# Reset database (development only)
npx prisma migrate reset

"Database is locked"

SQLite allows only one writer at a time. Solutions:

1. Keep transactions short
2. Avoid long-running queries during writes
3. Use connection pooling sparingly (usually singleton is fine)

Production Database Issues

1. Check Litestream backup status
2. Restore from backup if needed
3. Review references/litestream.md for restore procedures

References

• references/boilerplate.md - Complete Prisma setup code
• references/migrations.md - Migration patterns and examples
• references/litestream.md - Continuous backup setup with Railway Buckets
• Prisma Docs - Official documentation
• Railway SQLite Guide - Railway-specific patterns