epic-database

Compare original and translation side by side

🇺🇸

Original

English

🇨🇳

Translation

Chinese

Epic Stack: Database

Epic Stack：数据库

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

在以下场景中使用该技能：

使用 Prisma 设计数据库 schema
创建迁移
操作 SQLite 和 LiteFS
优化查询与性能
创建种子脚本
处理多区域部署
管理备份与恢复

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

遵循 Epic Web 原则：

最小化操作 - 仅获取实际需要的数据。使用

select

来获取特定字段，而非整个模型。避免“以防万一”的过度获取——只在需要时获取所需的数据。

实用优先于纯粹 - 当有可衡量的收益时再优化查询，但不要过早过度优化。简单、易读的查询通常比复杂的优化查询更好。仅在查询变慢时添加索引，而非提前添加。

示例 - 仅获取所需内容：

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.
})

示例 - 务实的优化：

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])
}

Epic Stack 使用 Prisma 搭配 SQLite 作为数据库。

基础配置：

prisma

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

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

基础模型：

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

使用 CUID2 生成 ID

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
}

Epic Stack 使用 CUID2 生成唯一 ID。

优势：

全局唯一
可排序
安全（无暴露信息）
适合 URL

示例：

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
}

标准字段：

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[]
}

一对多：

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])
}

一对一：

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
}

多对多：

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

创建索引：

prisma

model Note {
  id      String @id @default(cuid())
  ownerId String
  updatedAt DateTime
  
  @@index([ownerId])              // Simple index
  @@index([ownerId, updatedAt])   // Composite index
}

最佳实践：

为外键创建索引
为频繁在
```
where
```
中使用的字段创建索引
为
```
orderBy
```
中使用的字段创建索引
为复杂查询使用复合索引

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

配置级联：

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
}

选项：

```
onDelete: Cascade
```
- 删除父记录时同时删除子记录
```
onDelete: SetNull
```
- 删除父记录时将子记录的关联字段设为 null
```
onDelete: Restrict
```
- 如果存在子记录，阻止父记录被删除

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:

Widen app - App accepts A or B
Widen db - DB provides A and B, app writes to both
Narrow app - App only uses B
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;

创建迁移：

bash

npx prisma migrate dev --name add_user_field

在生产环境中应用迁移：

bash

npx prisma migrate deploy

自动迁移： 迁移会通过

litefs.yml

在部署时自动应用。

“先扩缩再收窄”的零停机策略：

扩缩应用 - 应用同时兼容 A 和 B 两种格式
扩缩数据库 - 数据库同时提供 A 和 B 字段，应用向两者写入数据
收窄应用 - 应用仅使用 B 格式
收窄数据库 - 数据库仅提供 B 字段

示例：将字段
name
重命名为
firstName
和
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,
})

导入 Prisma Client：

typescript

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

基础查询：

typescript

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

指定字段查询：

typescript

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

包含关联关系：

typescript

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

复杂查询：

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

使用事务：

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

SQLite 搭配 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
}

使用 LiteFS 实现多区域部署：

仅主实例可写入
副本实例仅可读取
写入操作会自动同步到副本

检查主实例：

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: { /* ... */ } })
}

获取实例信息：

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

创建种子脚本：

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()
	})

运行种子脚本：

bash

npx prisma db seed

Or directly:

npx tsx prisma/seed.ts

undefined

npx tsx prisma/seed.ts

undefined

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
		},
	},
})

实用指南：

使用
```
select
```
仅获取所需字段——最小化操作
选择性使用
```
include
```
——仅包含实际需要的关联关系
为
```
where
```
和
```
orderBy
```
中使用的字段创建索引——但仅在查询变慢时添加
为复杂查询使用复合索引——当确实存在性能问题时
避免使用
```
select: true
```
（获取所有字段）——明确指定所需内容
先测量，再优化——不要提前优化

优化示例（最小化操作）：

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

Prisma 查询日志

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}`)
})

配置日志：

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

数据库 URL

Development:

bash

DATABASE_URL=file:./data/db.sqlite

Production (Fly.io):

bash

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

开发环境：

bash

DATABASE_URL=file:./data/db.sqlite

生产环境（Fly.io）：

bash

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

Connecting to DB in Production

连接生产环境数据库

SSH to Fly instance:

bash

fly ssh console --app [YOUR_APP_NAME]

Connect to DB CLI:

bash

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

Prisma Studio:

bash

undefined

通过 SSH 连接到 Fly 实例：

bash

fly ssh console --app [YOUR_APP_NAME]

连接到数据库 CLI：

bash

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

Prisma Studio：

bash

undefined

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

undefined

undefined

Common examples

常见示例

Example 1: Create model with relations

示例 1：创建带关联关系的模型

prisma

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])
}

prisma

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

示例 2：带分页的复杂查询

typescript

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),
	}
}

typescript

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

示例 3：包含多操作的事务

typescript

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

typescript

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

示例 4：带关联数据的种子脚本

typescript

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!')
}

typescript

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

❌ 获取不必要的数据：使用
```
select
```
仅获取所需内容——最小化操作
❌ 过早过度优化：先测量，再优化。不要“以防万一”添加索引
❌ 必要时未使用索引：为外键和频繁查询的字段添加索引，但仅在查询确实变慢时
❌ N+1 查询：当需要关联数据时，使用
```
include
```
在单次查询中获取
❌ 关联操作未使用事务：当多个操作需保证原子性时，务必使用事务
❌ 从副本实例写入：在生产环境中写入前，需验证
```
ensurePrimary()
```
❌ 无策略地修改迁移：使用“先扩缩再收窄”策略实现零停机
❌ 插入前未验证数据：在创建/更新前，始终使用 Zod 验证数据
❌ 关联关系中未指定
onDelete
：明确指定父记录删除时的处理逻辑
❌ 未使用 CUID2：Epic Stack 默认使用 CUID2，不要使用 UUID 或其他方式
❌ 未关闭 Prisma Client：Prisma 会自动处理，但脚本中需确保关闭
❌ 能用简单查询却使用复杂查询：优先选择简单、易读的查询，除非确实存在性能问题

References

参考资料

Epic Stack Database Docs
Epic Web Principles
Prisma Documentation
LiteFS Documentation
SQLite Documentation
```
prisma/schema.prisma
```
- Complete schema
```
prisma/seed.ts
```
- Seed example
```
app/utils/db.server.ts
```
- Prisma Client setup
```
app/utils/litefs.server.ts
```
- LiteFS utilities

Epic Stack Database Docs
Epic Web Principles
Prisma Documentation
LiteFS Documentation
SQLite Documentation
```
prisma/schema.prisma
```
- 完整 schema
```
prisma/seed.ts
```
- 种子脚本示例
```
app/utils/db.server.ts
```
- Prisma Client 配置
```
app/utils/litefs.server.ts
```
- LiteFS 工具类