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:

// ✅ 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: '[email protected]',
			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' 

---

*Content truncated.*