epic-database

Guide on Prisma, SQLite, and LiteFS for Epic Stack

SKILL.md

GitHub

Epic Stack: Database

When to use this skill

Use this skill when you need to:

  • Design database schema with Prisma
  • Create migrations
  • Work with SQLite and LiteFS
  • Optimize queries and performance
  • Create seed scripts
  • Work with multi-region deployments
  • Manage backups and restores

Patterns and conventions

Database Philosophy

Following Epic Web principles:

Do as little as possible - Only fetch the data you actually need. Use select to fetch specific fields instead of entire models. Avoid over-fetching data "just in case" - fetch what you need, when you need it.

Pragmatism over purity - Optimize queries when there's a measurable benefit, but don't over-optimize prematurely. Simple, readable queries are often better than complex optimized ones. Add indexes when queries are slow, not before.

Example - Fetch only what you need:

// ✅ Good - Fetch only needed fields
const user = await prisma.user.findUnique({
    where: { id: userId },
    select: {
        id: true,
        username: true,
        name: true,
        // Only fetch what you actually use
    },
})

// ❌ Avoid - Fetching everything
const user = await prisma.user.findUnique({
    where: { id: userId },
    // Fetches all fields including password hash, email, etc.
})

Example - Pragmatic optimization:

// ✅ Good - Simple query first, optimize if needed
const notes = await prisma.note.findMany({
    where: { ownerId: userId },
    select: { id: true, title: true, updatedAt: true },
    orderBy: { updatedAt: 'desc' },
    take: 20,
})

// Only add indexes if this query is actually slow
// Don't pre-optimize

// ❌ Avoid - Over-optimizing before measuring
// Adding complex indexes, joins, etc. before knowing if it's needed

Prisma Schema

Epic Stack uses Prisma with SQLite as the database.

Basic configuration:

// prisma/schema.prisma
generator client {
  provider        = "prisma-client-js"
  previewFeatures = ["typedSql"]
}

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

Basic model:

model User {
  id        String   @id @default(cuid())
  email     String   @unique
  username  String   @unique
  name      String?

  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt

  notes     Note[]
  roles     Role[]
}

model Note {
  id      String @id @default(cuid())
  title   String
  content String

  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt

  owner   User   @relation(fields: [ownerId], references: [id])
  ownerId String

  @@index([ownerId])
  @@index([ownerId, updatedAt])
}

CUID2 for IDs

Epic Stack uses CUID2 to generate unique IDs.

Advantages:

  • Globally unique
  • Sortable
  • Secure (no exposed information)
  • URL-friendly

Example:

model User {
  id String @id @default(cuid()) // Automatically generates CUID2
}

Timestamps

Standard fields:

model User {
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt // Automatically updated
}

Relationships

One-to-Many:

model User {
  id    String @id @default(cuid())
  notes Note[]
}

model Note {
  id      String @id @default(cuid())
  owner   User   @relation(fields: [ownerId], references: [id])
  ownerId String

  @@index([ownerId])
}

One-to-One:

model User {
  id      String  @id @default(cuid())
  image   UserImage?
}

model UserImage {
  id        String @id @default(cuid())
  user      User   @relation(fields: [userId], references: [id])
  userId    String @unique
}

Many-to-Many:

model User {
  id    String @id @default(cuid())
  roles Role[]
}

model Role {
  id    String @id @default(cuid())
  users User[]
}

Indexes

Create indexes:

model Note {
  id      String @id @default(cuid())
  ownerId String
  updatedAt DateTime

  @@index([ownerId])              // Simple index
  @@index([ownerId, updatedAt])   // Composite index
}

Best practices:

  • Index foreign keys
  • Index fields used in where frequently
  • Index fields used in orderBy
  • Use composite indexes for complex queries

Cascade Delete

Configure cascade:

model User {
  id    String @id @default(cuid())
  notes Note[]
}

model Note {
  id      String @id @default(cuid())
  owner   User   @relation(fields: [ownerId], references: [id], onDelete: Cascade)
  ownerId String
}

Options:

  • onDelete: Cascade - Deletes children when parent is deleted
  • onDelete: SetNull - Sets to null when parent is deleted
  • onDelete: Restrict - Prevents deletion if there are children

Migrations

Create migration:

npx prisma migrate dev --name add_user_field

Apply migrations in production:

npx prisma migrate deploy

Automatic migrations: Migrations are automatically applied on deploy via litefs.yml.

"Widen then Narrow" strategy for zero-downtime:

  1. Widen app - App accepts A or B
  2. Widen db - DB provides A and B, app writes to both
  3. Narrow app - App only uses B
  4. Narrow db - DB only provides B

Example: Rename field name to firstName and lastName:

// Step 1: Widen app (accepts both)
model User {
  id        String @id @default(cuid())
  name      String?  // Deprecated
  firstName String?  // New
  lastName  String?  // New
}

// Step 2: Widen db (migration copies data)
// In SQL migration:
ALTER TABLE User ADD COLUMN firstName TEXT;
ALTER TABLE User ADD COLUMN lastName TEXT;
UPDATE User SET firstName = name;

// Step 3: Narrow app (only uses new fields)
// Code only uses firstName and lastName

// Step 4: Narrow db (removes old field)
ALTER TABLE User DROP COLUMN name;

Prisma Client

Import Prisma Client:

import { prisma } from '#app/utils/db.server.ts'

Basic query:

const user = await prisma.user.findUnique({
    where: { id: userId },
})

Specific select:

const user = await prisma.user.findUnique({
    where: { id: userId },
    select: {
        id: true,
        email: true,
        username: true,
        // Don't include password or sensitive data
    },
})

Include relations:

const user = await prisma.user.findUnique({
    where: { id: userId },
    include: {
        notes: {
            select: {
                id: true,
                title: true,
            },
            orderBy: { updatedAt: 'desc' },
        },
        roles: true,
    },
})

Complex queries:

const notes = await prisma.note.findMany({
    where: {
        ownerId: userId,
        title: { contains: searchTerm },
    },
    select: {
        id: true,
        title: true,
        updatedAt: true,
    },
    orderBy: { updatedAt: 'desc' },
    take: 20,
    skip: (page - 1) * 20,
})

Transactions

Use transactions:

await prisma.$transaction(async (tx) => {
    const user = await tx.user.create({
        data: {
            email,
            username,
            roles: { connect: { name: 'user' } },
        },
    })

    await tx.note.create({
        data: {
            title: 'Welcome',
            content: 'Welcome to the app!',
            ownerId: user.id,
        },
    })

    return user
})

SQLite con LiteFS

Multi-region with LiteFS:

  • Only the primary instance can write
  • Replicas can only read
  • Writes are automatically replicated

Check primary instance:

import { ensurePrimary, getInstanceInfo } from '#app/utils/litefs.server.ts'

export async function action({ request }: Route.ActionArgs) {
    // Ensure we're on primary instance for writes
    await ensurePrimary()

    // Now we can write safely
    await prisma.user.create({
        data: {
            /* ... */
        },
    })
}

Get instance information:

import { getInstanceInfo } from '#app/utils/litefs.server.ts'

const { currentIsPrimary, primaryInstance } = await getInstanceInfo()

if (currentIsPrimary) {
    // Can write
} else {
    // Read-only, redirect to primary if necessary
}

Seed Scripts

Create seed:

// prisma/seed.ts
import { prisma } from '#app/utils/db.server.ts'

async function seed() {
    // Create roles
    await prisma.role.createMany({
        data: [
            { name: 'user', description: 'Standard user' },
            { name: 'admin', description: 'Administrator' },
        ],
    })

    // Create users
    const user = await prisma.user.create({
        data: {
            email: 'user@example.com',
            username: 'testuser',
            roles: { connect: { name: 'user' } },
        },
    })

    console.log('Seed complete!')
}

seed()
    .catch((e) => {
        console.error(e)
        process.exit(1)
    })
    .finally(async () => {
        await prisma.$disconnect()
    })

Run seed:

npx prisma db seed
# Or directly:
npx tsx prisma/seed.ts

Query Optimization

Guidelines (pragmatic approach):

  • Use select to fetch only needed fields - do as little as possible
  • Use selective include - only include relations you actually use
  • Index fields used in where and orderBy - but only if queries are slow
  • Use composite indexes for complex queries - when you have a real performance problem
  • Avoid select: true (fetches everything) - be explicit about what you need
  • Measure first, optimize second - don't pre-optimize

Optimized example (do as little as possible):

// ❌ Avoid: Fetches everything unnecessarily
const user = await prisma.user.findUnique({
    where: { id: userId },
    // Fetches password hash, email, all relations, etc.
})

// ✅ Good: Only needed fields - do as little as possible
const user = await prisma.user.findUnique({
    where: { id: userId },
    select: {
        id: true,
        username: true,
        name: true,
        // Only what you actually use
    },
})

// ✅ Better: With selective relations (only if you need them)
const user = await prisma.user.findUnique({
    where: { id: userId },
    select: {
        id: true,
        username: true,
        notes: {
            select: {
                id: true,
                title: true,
            },
            take: 10, // Only fetch what you need
        },
    },
})

Prisma Query Logging

Configure logging:

// app/utils/db.server.ts
const client = new PrismaClient({
    log: [
        { level: 'query', emit: 'event' },
        { level: 'error', emit: 'stdout' },
        { level: 'warn', emit: 'stdout' },
    ],
})

client.$on('query', async (e) => {
    if (e.duration < 20) return // Only log slow queries

    console.info(`prisma:query - ${e.duration}ms - ${e.query}`)
})

Database URL

Development:

DATABASE_URL=file:./data/db.sqlite

Production (Fly.io):

DATABASE_URL=file:/litefs/data/sqlite.db

Connecting to DB in Production

SSH to Fly instance:

fly ssh console --app [YOUR_APP_NAME]

Connect to DB CLI:

fly ssh console -C database-cli --app [YOUR_APP_NAME]

Prisma Studio:

# Terminal 1: Start Prisma Studio
fly ssh console -C "npx prisma studio" -s --app [YOUR_APP_NAME]

# Terminal 2: Local proxy
fly proxy 5556:5555 --app [YOUR_APP_NAME]

# Open in browser
# http://localhost:5556

Common examples

Example 1: Create model with relations

model Post {
  id        String   @id @default(cuid())
  title     String
  content   String
  published Boolean  @default(false)

  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt

  author   User   @relation(fields: [authorId], references: [id], onDelete: Cascade)
  authorId String

  comments Comment[]
  tags     Tag[]

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

model Comment {
  id      String @id @default(cuid())
  content String

  createdAt DateTime @default(now())

  post   Post   @relation(fields: [postId], references: [id], onDelete: Cascade)
  postId String

  author   User   @relation(fields: [authorId], references: [id])
  authorId String

  @@index([postId])
  @@index([authorId])
}

Example 2: Complex query with pagination

export async function getPosts({
    userId,
    page = 1,
    perPage = 20,
    published,
}: {
    userId?: string
    page?: number
    perPage?: number
    published?: boolean
}) {
    const where: Prisma.PostWhereInput = {}

    if (userId) {
        where.authorId = userId
    }
    if (published !== undefined) {
        where.published = published
    }

    const [posts, total] = await Promise.all([
        prisma.post.findMany({
            where,
            select: {
                id: true,
                title: true,
                updatedAt: true,
                author: {
                    select: {
                        id: true,
                        username: true,
                    },
                },
            },
            orderBy: { updatedAt: 'desc' },
            take: perPage,
            skip: (page - 1) * perPage,
        }),
        prisma.post.count({ where }),
    ])

    return {
        posts,
        total,
        pages: Math.ceil(total / perPage),
    }
}

Example 3: Transaction with multiple operations

export async function createPostWithTags({
    authorId,
    title,
    content,
    tagNames,
}: {
    authorId: string
    title: string
    content: string
    tagNames: string[]
}) {
    return await prisma.$transaction(async (tx) => {
        // Create tags if they don't exist
        await Promise.all(
            tagNames.map((name) =>
                tx.tag.upsert({
                    where: { name },
                    update: {},
                    create: { name },
                }),
            ),
        )

        // Create post
        const post = await tx.post.create({
            data: {
                title,
                content,
                authorId,
                tags: {
                    connect: tagNames.map((name) => ({ name })),
                },
            },
        })

        return post
    })
}

Example 4: Seed with related data

async function seed() {
    // Create permissions
    const permissions = await Promise.all([
        prisma.permission.create({
            data: {
                action: 'create',
                entity: 'note',
                access: 'own',
                description: 'Can create own notes',
            },
        }),
        prisma.permission.create({
            data: {
                action: 'read',
                entity: 'note',
                access: 'own',
                description: 'Can read own notes',
            },
        }),
    ])

    // Create roles with permissions
    const userRole = await prisma.role.create({
        data: {
            name: 'user',
            description: 'Standard user',
            permissions: {
                connect: permissions.map((p) => ({ id: p.id })),
            },
        },
    })

    // Create user with role
    const user = await prisma.user.create({
        data: {
            email: 'user@example.com',
            username: 'testuser',
            roles: {
                connect: { id: userRole.id },
            },
        },
    })

    console.log('Seed complete!')
}

Common mistakes to avoid

  • ❌ Fetching unnecessary data: Use select to fetch only what you need - do as little as possible
  • ❌ Over-optimizing prematurely: Measure first, then optimize. Don't add indexes "just in case"
  • ❌ Not using indexes when needed: Index foreign keys and fields used in frequent queries, but only if they're actually slow
  • ❌ N+1 queries: Use include to fetch relations in a single query when you need them
  • ❌ Not using transactions for related operations: Always use transactions when multiple operations must be atomic
  • ❌ Writing from replicas: Verify ensurePrimary() before writes in production
  • ❌ Breaking migrations without strategy: Use "widen then narrow" for zero-downtime
  • ❌ Not validating data before inserting: Always validate with Zod before create/update
  • ❌ Forgetting onDelete in relations: Explicitly decide what to do when parent is deleted
  • ❌ Not using CUID2: Epic Stack uses CUID2 by default, don't use UUID or others
  • ❌ Not closing Prisma Client: Prisma handles this automatically, but ensure in scripts
  • ❌ Complex queries when simple ones work: Prefer simple, readable queries over complex optimized ones unless there's a real problem

References

Похожие навыки: data

electron-chromium-upgrade-electron-electron

Guide for performing Chromium version upgrades in the Electron project. Use when working on the roller/chromium/main bra...

Автор: electron
119,879

qiuzhi

指导Claude按照二哥的风格撰写求职类文章，包括公司薪资爆料、年终奖盘点、求职攻略、offer选择建议等内容。

Автор: itwanger
16,619

awesome-ai-agents-illacloud-illa-builder

The chat will provide safety guides, equipment suggestions, reviews, and techniques, with context-driven summary and mul...

Автор: illacloud
12,356