epic-database

# 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:** ```typescript // ✅ 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:** ```typescript // ✅ 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 // prisma/schema.prisma generator client { provider = "prisma-client-js" previewFeatures = ["typedSql"] } datasource db { provider = "sqlite" url = env("DATABASE_URL") } ``` **Basic model:** ```prisma 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:** ```prisma model User { id String @id @default(cuid()) // Automatically generates CUID2 } ``` ### Timestamps **Standard fields:** ```prisma model User { createdAt DateTime @default(now()) updatedAt DateTime @updatedAt // Automatically updated } ``` ### Relationships **One-to-Many:** ```prisma 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:** ```prisma 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:** ```prisma model User { id String @id @default(cuid()) roles Role[] } model Role { id String @id @default(cuid()) users User[] } ``` ### Indexes **Create indexes:** ```prisma 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:** ```prisma 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:** ```bash npx prisma migrate dev --name add_user_field ``` **Apply migrations in production:** ```bash 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`:** ```prisma // 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:** ```typescript import { prisma } from '#app/utils/db.server.ts' ``` **Basic query:** ```typescript const user = await prisma.user.findUnique({ where: { id: userId }, }) ``` **Specific select:** ```typescript 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:** ```typescript const user = await prisma.user.findUnique({ where: { id: userId }, include: { notes: { select: { id: true, title: true, }, orderBy: { updatedAt: 'desc' }, }, roles: true, }, }) ``` **Complex queries:** ```typescript 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:** ```typescript 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:** ```typescript 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:** ```typescript 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:** ```typescript // 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:** ```bash 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):** ```typescript // ❌ 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:** ```typescript // 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