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:

•Volume mounting - Database file must live on a Railway volume (not container filesystem)
•No remote access - Cannot connect database GUI tools directly to production
•Single container - Only one instance can write to the database
•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.

code

# 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

bash

npm install @prisma/client
npm install -D prisma

Directory Structure

code

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

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

Environment Configuration

bash

# .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:

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:

typescript

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

json

{
  "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:

prisma

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

Timestamps

Always include created/updated timestamps:

prisma

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

Relations

One-to-many:

prisma

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):

prisma

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

prisma

enum Role {
  ADMIN
  USER
  GUEST
}

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

Indexes

prisma

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

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

Unique Constraints

prisma

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

  @@unique([bookId, userId])
}

Migration Workflow

Development

bash

# 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

bash

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

Migration Best Practices

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

Query Patterns

Basic CRUD

typescript

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

// Create
const user = await prisma.user.create({
  data: {
    email: 'user@example.com',
    name: 'John Doe',
  },
})

// Read one
const user = await prisma.user.findUnique({
  where: { email: 'user@example.com' },
})

// 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

typescript

// 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: 'author@example.com',
    posts: {
      create: {
        title: 'My First Post',
      },
    },
  },
  include: { posts: true },
})

Transactions

typescript

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

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

Upsert

typescript

const user = await prisma.user.upsert({
  where: { email: 'user@example.com' },
  update: { name: 'Updated Name' },
  create: { email: 'user@example.com', name: 'New User' },
})

Railway Operations

Environment Variables

Required Railway configuration:

code

DATABASE_URL=file:/data/app.db

Ensure volume is mounted at /data.

Shell Access

bash

# 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

bash

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

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

Minimal Setup

bash

# 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:

bash

npm run db:generate

"Migration failed"

Check migration status and pending migrations:

bash

npx prisma migrate status

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

bash

# 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:

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

Production Database Issues

•Check Litestream backup status
•Restore from backup if needed
•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