supabase-prisma-database-management

Compare original and translation side by side

🇺🇸

Original

English

🇨🇳

Translation

Chinese

Supabase + Prisma Database Management

Supabase + Prisma 数据库管理

Overview

概述

Manage database schema, migrations, and seed data using Prisma ORM with Supabase PostgreSQL, including shadow database configuration, seed files, and automated schema checks in CI.

使用Prisma ORM与Supabase PostgreSQL管理数据库架构、迁移和种子数据，包括影子数据库配置、种子文件以及CI中的自动化架构检查。

Installation and Setup

安装与设置

1. Install Prisma

1. 安装Prisma

Install Prisma CLI and client:

bash

npm install -D prisma
npm install @prisma/client

安装Prisma CLI和客户端：

bash

npm install -D prisma
npm install @prisma/client

2. Initialize Prisma

2. 初始化Prisma

Initialize Prisma in your project:

bash

npx prisma init

This creates:

```
prisma/schema.prisma
```
- Database schema definition
```
.env
```
- Environment variables (add
```
DATABASE_URL
```
)

在项目中初始化Prisma：

bash

npx prisma init

此命令会创建：

```
prisma/schema.prisma
```
- 数据库架构定义文件
```
.env
```
- 环境变量文件（需添加
```
DATABASE_URL
```
）

3. Configure Supabase Connection

3. 配置Supabase连接

Get your Supabase database URL from:

Supabase Dashboard > Project Settings > Database > Connection String > URI

Add to

.env

env

undefined

从以下位置获取Supabase数据库URL：

Supabase控制台 > 项目设置 > 数据库 > 连接字符串 > URI

将其添加到

.env

文件中：

env

undefined

Transaction pooler for Prisma migrations

用于Prisma迁移的事务池

DATABASE_URL="postgresql://postgres:[YOUR-PASSWORD]@db.[PROJECT-REF].supabase.co:5432/postgres"

Session pooler for queries (with pgBouncer)

用于查询的会话池（搭配pgBouncer）

DIRECT_URL="postgresql://postgres:[YOUR-PASSWORD]@db.[PROJECT-REF].supabase.co:6543/postgres?pgbouncer=true"


Update `prisma/schema.prisma` to use both URLs:

```prisma
datasource db {
  provider  = "postgresql"
  url       = env("DIRECT_URL")
  directUrl = env("DATABASE_URL")
}

Why two URLs?

```
DATABASE_URL
```
: Direct connection for migrations (required)
```
DIRECT_URL
```
: Pooled connection for application queries (optional, better performance)

DIRECT_URL="postgresql://postgres:[YOUR-PASSWORD]@db.[PROJECT-REF].supabase.co:6543/postgres?pgbouncer=true"


更新`prisma/schema.prisma`以使用这两个URL：

```prisma
datasource db {
  provider  = "postgresql"
  url       = env("DIRECT_URL")
  directUrl = env("DATABASE_URL")
}

为什么需要两个URL？

```
DATABASE_URL
```
：用于迁移的直接连接（必填）
```
DIRECT_URL
```
：用于应用查询的池化连接（可选，性能更优）

4. Configure Shadow Database (Required for Migrations)

4. 配置影子数据库（迁移必填）

For migration preview and validation, configure a shadow database in

prisma/schema.prisma

prisma

datasource db {
  provider  = "postgresql"
  url       = env("DIRECT_URL")
  directUrl = env("DATABASE_URL")
  shadowDatabaseUrl = env("SHADOW_DATABASE_URL")
}

Add to

.env

env

SHADOW_DATABASE_URL="postgresql://postgres:[PASSWORD]@db.[PROJECT-REF].supabase.co:5432/postgres"

Note: Supabase free tier allows using the same database for shadow. For production, use a separate database.

为了迁移预览和验证，在

prisma/schema.prisma

中配置影子数据库：

prisma

datasource db {
  provider  = "postgresql"
  url       = env("DIRECT_URL")
  directUrl = env("DATABASE_URL")
  shadowDatabaseUrl = env("SHADOW_DATABASE_URL")
}

将其添加到

.env

文件中：

env

SHADOW_DATABASE_URL="postgresql://postgres:[PASSWORD]@db.[PROJECT-REF].supabase.co:5432/postgres"

注意：Supabase免费套餐允许使用同一个数据库作为影子数据库。生产环境建议使用独立的数据库。

Schema Definition

架构定义

1. Define Your Schema

1. 定义你的架构

Edit

prisma/schema.prisma

using the example from

assets/example-schema.prisma

. This example includes:

User profiles with auth integration
Timestamps with
```
@default(now())
```
and
```
@updatedAt
```
Relations between entities
Indexes for performance
Unique constraints

Key Prisma features:

```
@id @default(uuid())
```
- Auto-generated UUIDs
```
@default(now())
```
- Automatic timestamps
```
@updatedAt
```
- Auto-update on modification
```
@@index([field])
```
- Database indexes
```
@relation
```
- Define relationships

使用

assets/example-schema.prisma

中的示例编辑

prisma/schema.prisma

。该示例包含：

与认证集成的用户配置文件
带
```
@default(now())
```
和
```
@updatedAt
```
的时间戳
实体间的关联关系
用于性能优化的索引
唯一约束

Prisma核心特性：

```
@id @default(uuid())
```
- 自动生成UUID
```
@default(now())
```
- 自动生成时间戳
```
@updatedAt
```
- 修改时自动更新
```
@@index([field])
```
- 数据库索引
```
@relation
```
- 定义关联关系

2. Link to Supabase Auth

2. 关联Supabase认证

To integrate with Supabase Auth, reference the

auth.users

table:

prisma

model Profile {
  id        String   @id @db.Uuid
  email     String   @unique
  // Other fields...

  // This doesn't create a foreign key, just documents the relationship
  // The actual user exists in auth.users (managed by Supabase)
}

Important: Don't create a foreign key to

auth.users

as it's in a different schema. Handle the relationship in application logic.

要与Supabase认证集成，引用

auth.users

表：

prisma

model Profile {
  id        String   @id @db.Uuid
  email     String   @unique
  // 其他字段...

  // 这不会创建外键，仅用于记录关联关系
  // 实际用户存储在auth.users表中（由Supabase管理）
}

重要提示：不要创建指向

auth.users

的外键，因为它位于不同的架构中。请在应用逻辑中处理关联关系。

Migrations

迁移

1. Create Migration

1. 创建迁移

After defining/modifying schema, create a migration:

bash

npx prisma migrate dev --name add_profiles_table

This:

Generates SQL migration in
```
prisma/migrations/
```
Applies migration to database
Regenerates Prisma Client
Runs seed script (if configured)

定义/修改架构后，创建迁移：

bash

npx prisma migrate dev --name add_profiles_table

此命令会：

在
```
prisma/migrations/
```
目录中生成SQL迁移文件
将迁移应用到数据库
重新生成Prisma Client
运行种子脚本（如果已配置）

2. Review Migration SQL

2. 审核迁移SQL

Always review generated SQL in

prisma/migrations/[timestamp]_[name]/migration.sql

sql

-- CreateTable
CREATE TABLE "Profile" (
    "id" UUID NOT NULL,
    "email" TEXT NOT NULL,
    -- ...

    CONSTRAINT "Profile_pkey" PRIMARY KEY ("id")
);

-- CreateIndex
CREATE UNIQUE INDEX "Profile_email_key" ON "Profile"("email");

Make manual adjustments if needed before applying to production.

务必审核

prisma/migrations/[timestamp]_[name]/migration.sql

中生成的SQL：

sql

-- CreateTable
CREATE TABLE "Profile" (
    "id" UUID NOT NULL,
    "email" TEXT NOT NULL,
    -- ...

    CONSTRAINT "Profile_pkey" PRIMARY KEY ("id")
);

-- CreateIndex
CREATE UNIQUE INDEX "Profile_email_key" ON "Profile"("email");

在应用到生产环境前，如有需要可手动调整。

3. Apply Migrations in Production

3. 在生产环境中应用迁移

For production deployments:

bash

npx prisma migrate deploy

This applies pending migrations without prompts or seeds.

CI/CD Integration: Add to your deployment pipeline:

yaml

undefined

对于生产环境部署：

bash

npx prisma migrate deploy

此命令会应用待处理的迁移，无提示且不运行种子脚本。

CI/CD集成：将以下步骤添加到部署流水线中：

yaml

undefined

Example GitHub Actions step

GitHub Actions示例步骤

name: Run migrations run: npx prisma migrate deploy env: DATABASE_URL: ${{ secrets.DATABASE_URL }}

undefined

name: Run migrations run: npx prisma migrate deploy env: DATABASE_URL: ${{ secrets.DATABASE_URL }}

undefined

4. Reset Database (Development Only)

4. 重置数据库（仅开发环境）

To reset database to clean state:

bash

npx prisma migrate reset

This:

Drops database
Creates database
Applies all migrations
Runs seed script

Warning: This deletes all data. Only use in development.

要将数据库重置为干净状态：

bash

npx prisma migrate reset

此命令会：

删除数据库
创建新数据库
应用所有迁移
运行种子脚本

警告：此操作会删除所有数据。仅在开发环境中使用。

Seeding Data

数据播种

1. Create Seed Script

1. 创建种子脚本

Create

prisma/seed.ts

using the template from

assets/seed.ts

. This script:

Uses Prisma Client to insert data
Creates initial users, settings, or reference data
Can be run manually or after migrations
Supports idempotent operations (safe to run multiple times)

使用

assets/seed.ts

中的模板创建

prisma/seed.ts

。该脚本：

使用Prisma Client插入数据
创建初始用户、设置或参考数据
可手动运行或在迁移后自动运行
支持幂等操作（可安全多次运行）

2. Configure Seed in package.json

2. 在package.json中配置种子脚本

Add seed configuration to

package.json

json

{
  "prisma": {
    "seed": "ts-node --compiler-options {\"module\":\"CommonJS\"} prisma/seed.ts"
  }
}

Install ts-node for TypeScript execution:

bash

npm install -D ts-node

将种子配置添加到

package.json

：

json

{
  "prisma": {
    "seed": "ts-node --compiler-options {\"module\":\"CommonJS\"} prisma/seed.ts"
  }
}

安装ts-node以执行TypeScript：

bash

npm install -D ts-node

3. Run Seed Manually

3. 手动运行种子脚本

Execute seed script:

bash

npx prisma db seed

Seed runs automatically after

prisma migrate dev

and

prisma migrate reset

执行种子脚本：

bash

npx prisma db seed

种子脚本会在

prisma migrate dev

和

prisma migrate reset

后自动运行。

4. Idempotent Seeding

4. 幂等播种

Make seeds safe to run multiple times using upsert:

typescript

await prisma.user.upsert({
  where: { email: 'admin@example.com' },
  update: {}, // No updates if exists
  create: {
    email: 'admin@example.com',
    name: 'Admin User',
  },
});

使用upsert确保种子脚本可安全多次运行：

typescript

await prisma.user.upsert({
  where: { email: 'admin@example.com' },
  update: {}, // 若存在则不更新
  create: {
    email: 'admin@example.com',
    name: 'Admin User',
  },
});

Prisma Client Usage

Prisma Client 使用

1. Generate Client

1. 生成Client

After schema changes, regenerate Prisma Client:

bash

npx prisma generate

This updates

node_modules/@prisma/client

with types matching your schema.

架构变更后，重新生成Prisma Client：

bash

npx prisma generate

此命令会更新

node_modules/@prisma/client

，使其包含与架构匹配的类型。

2. Use in Next.js Server Components

2. 在Next.js Server Components中使用

Create a Prisma client singleton using

assets/prisma-client.ts

typescript

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

export default async function UsersPage() {
  const users = await prisma.profile.findMany();

  return (
    <ul>
      {users.map((user) => (
        <li key={user.id}>{user.name}</li>
      ))}
    </ul>
  );
}

使用

assets/prisma-client.ts

创建Prisma Client单例：

typescript

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

export default async function UsersPage() {
  const users = await prisma.profile.findMany();

  return (
    <ul>
      {users.map((user) => (
        <li key={user.id}>{user.name}</li>
      ))}
    </ul>
  );
}

3. Use in Server Actions

3. 在Server Actions中使用

typescript

'use server';

import { prisma } from '@/lib/prisma';
import { revalidatePath } from 'next/cache';

export async function createProfile(formData: FormData) {
  const name = formData.get('name') as string;

  await prisma.profile.create({
    data: {
      name,
      email: formData.get('email') as string,
    },
  });

  revalidatePath('/profiles');
}

typescript

'use server';

import { prisma } from '@/lib/prisma';
import { revalidatePath } from 'next/cache';

export async function createProfile(formData: FormData) {
  const name = formData.get('name') as string;

  await prisma.profile.create({
    data: {
      name,
      email: formData.get('email') as string,
    },
  });

  revalidatePath('/profiles');
}

CI/CD Integration

CI/CD集成

1. Add Schema Validation to CI

1. 为CI添加架构验证

Create

.github/workflows/schema-check.yml

using the template from

assets/github-workflows-schema-check.yml

. This workflow:

Runs on pull requests
Validates schema syntax
Checks for migration drift
Ensures migrations are generated
Verifies Prisma Client generation

使用

assets/github-workflows-schema-check.yml

中的模板创建

.github/workflows/schema-check.yml

。该工作流：

在拉取请求时运行
验证架构语法
检查迁移漂移
确保已生成迁移文件
验证Prisma Client的生成

2. Migration Deployment

2. 迁移部署

Add migration step to deployment workflow:

yaml

- name: Apply database migrations
  run: npx prisma migrate deploy
  env:
    DATABASE_URL: ${{ secrets.PROD_DATABASE_URL }}

将迁移步骤添加到部署工作流中：

yaml

- name: Apply database migrations
  run: npx prisma migrate deploy
  env:
    DATABASE_URL: ${{ secrets.PROD_DATABASE_URL }}

3. Environment-Specific Databases

3. 环境专属数据库

Use different database URLs for each environment:

env

undefined

为每个环境使用不同的数据库URL：

env

undefined

Development

开发环境

DATABASE_URL="postgresql://localhost:5432/dev"

Staging

预发布环境

DATABASE_URL="postgresql://staging-db.supabase.co:5432/postgres"

Production

生产环境

DATABASE_URL="postgresql://prod-db.supabase.co:5432/postgres"

undefined

DATABASE_URL="postgresql://prod-db.supabase.co:5432/postgres"

undefined

Best Practices

最佳实践

Schema Design

架构设计

Use UUIDs for IDs: Better for distributed systems
Add Timestamps: Track
```
createdAt
```
and
```
updatedAt
```
Define Indexes: Improve query performance on filtered fields
Use Enums: Type-safe status/role fields
Validate at DB Level: Use unique constraints and checks

使用UUID作为ID：更适合分布式系统
添加时间戳：跟踪
```
createdAt
```
和
```
updatedAt
```
定义索引：提升过滤字段的查询性能
使用枚举：类型安全的状态/角色字段
数据库层验证：使用唯一约束和检查

Migration Management

迁移管理

Review Before Applying: Always check generated SQL
Name Descriptively: Use clear migration names
Keep Atomic: One logical change per migration
Test Locally First: Verify migrations work before production
Never Modify Applied Migrations: Create new ones instead

应用前审核：务必检查生成的SQL
命名清晰：使用明确的迁移名称
保持原子性：每个迁移对应一个逻辑变更
先本地测试：在生产环境前验证迁移效果
不要修改已应用的迁移：如需变更，请创建新迁移

Prisma Client

Use Singleton Pattern: Prevent connection exhaustion
Close in Serverless: Disconnect after operations
Type Everything: Leverage Prisma's TypeScript types
Use Select: Only fetch needed fields
Batch Operations: Use
```
createMany
```
,
```
updateMany
```
for bulk ops

使用单例模式：避免连接耗尽
无服务器环境中关闭连接：操作完成后断开连接
全类型化：利用Prisma的TypeScript类型
使用Select：仅获取所需字段
批量操作：使用
```
createMany
```
、
```
updateMany
```
处理批量操作

Troubleshooting

故障排除

Migration fails with "relation already exists": Reset development database with

npx prisma migrate reset

. For production, manually fix conflicts.

Prisma Client out of sync: Run

npx prisma generate

after schema changes.

Connection pool exhausted: Use connection pooling via

DIRECT_URL

with pgBouncer.

Shadow database errors: Ensure shadow database URL is correct and accessible. For Supabase free tier, same DB can be used.

Type errors after schema changes: Restart TypeScript server in IDE after

prisma generate

迁移失败并提示"relation already exists"：使用

npx prisma migrate reset

重置开发数据库。生产环境需手动修复冲突。

Prisma Client与架构不同步：架构变更后运行

npx prisma generate

。

连接池耗尽：使用

DIRECT_URL

搭配pgBouncer进行连接池管理。

影子数据库错误：确保影子数据库URL正确且可访问。Supabase免费套餐可使用同一数据库。

架构变更后出现类型错误：运行

prisma generate

后重启IDE中的TypeScript服务器。

Resources

资源

scripts/

No executable scripts needed for this skill.

此技能无需可执行脚本。

references/

```
prisma-best-practices.md
```
- Comprehensive guide to Prisma patterns, performance optimization, and common pitfalls
```
supabase-integration.md
```
- Specific considerations for using Prisma with Supabase, including RLS integration

```
prisma-best-practices.md
```
- Prisma模式、性能优化和常见陷阱的综合指南
```
supabase-integration.md
```
- 使用Prisma与Supabase集成的特定注意事项，包括RLS集成

assets/

```
example-schema.prisma
```
- Complete schema example with common patterns (auth, timestamps, relations, indexes)
```
seed.ts
```
- Idempotent seed script template for initial data
```
prisma-client.ts
```
- Singleton Prisma Client for Next.js to prevent connection exhaustion
```
github-workflows-schema-check.yml
```
- CI workflow for schema validation and migration checks

```
example-schema.prisma
```
- 包含常见模式（认证、时间戳、关联、索引）的完整架构示例
```
seed.ts
```
- 用于初始化数据的幂等种子脚本模板
```
prisma-client.ts
```
- 用于Next.js的Prisma Client单例，防止连接耗尽
```
github-workflows-schema-check.yml
```
- 用于架构验证和迁移检查的CI工作流