Prisma 数据库同步与迁移命令详解

npx prisma db pull                    # 同步到 schema.prisma
npx prisma db pull --print            # 打印但不写入文件
npx prisma db pull --force            # 强制覆盖现有 Schema
npx prisma db pull --schema=./custom/path.prisma

# 1. 初始化项目
npx prisma init

# 2. 配置 .env
# DATABASE_URL="mysql://user:password@localhost:3306/mydb"

# 3. 拉取 Schema
npx prisma db pull

# 4. 生成 Client
npx prisma generate

# 5. 建立迁移基线
npx prisma migrate dev --name init

// package.json
{
  "prisma": {
    "seed": "ts-node prisma/seed.ts"
  }
}

// prisma/seed.ts
import { PrismaClient, Prisma } from '@prisma/client';

const prisma = new PrismaClient();

const userData: Prisma.UserCreateInput[] = [
  { username: 'admin', email: 'admin@example.com', name: 'Admin' },
  { username: 'editor', email: 'editor@example.com', name: 'Editor' },
];

async function main() {
  // 清空（注意外键约束顺序）
  await prisma.post.deleteMany();
  await prisma.user.deleteMany();
  await prisma.category.deleteMany();

  // 创建数据
  const users = await Promise.all(
    userData.map(data => prisma.user.create({ data })),
  );

  const categories = await Promise.all(
    ['技术', '前端', '后端'].map(name => prisma.category.create({ data: { name } })),
  );

  // 创建关联数据
  await prisma.post.create({
    data: {
      title: 'Prisma 入门',
      content: '...',
      published: true,
      author: { connect: { id: users[0].id } },
      categories: { connect: [{ id: categories[0].id }] },
    },
  });

  console.log('数据填充完成');
}

main()
  .catch(e => { console.error(e); process.exit(1); })
  .finally(() => prisma.$disconnect());

// 使用 upsert 代替 create，确保可重复执行
await prisma.user.upsert({
  where: { email: 'admin@example.com' },
  update: {},
  create: { username: 'admin', email: 'admin@example.com', name: 'Admin' },
});

# 执行 SQL 文件
npx prisma db execute --file=./scripts/init.sql

# 执行单条 SQL
npx prisma db execute --stdin <<< "CREATE INDEX idx_users_email ON users(email)"

# 指定数据库 URL
npx prisma db execute --file=./scripts/init.sql --url="postgresql://..."

-- scripts/init.sql
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_posts_author ON posts(author_id);

CREATE VIEW active_users AS
SELECT id, username, email FROM users WHERE deleted_at IS NULL;

# 修改 schema.prisma 后
npx prisma migrate dev --name add_user_avatar

# 只生成迁移文件不执行
npx prisma migrate dev --create-only --name add_field

# 查看迁移状态
npx prisma migrate status

# 解决迁移冲突
npx prisma migrate resolve --applied "migration_name"

# 只执行未应用的迁移（不生成新迁移）
npx prisma migrate deploy

enum Status {
  ACTIVE
  INACTIVE
  PENDING
}

# 检查配置
cat package.json | grep -A 2 "prisma"
# 确保 Prisma Client 已生成
npx prisma generate
# 手动执行排查
node prisma/seed.js

npx prisma migrate status
npx prisma migrate resolve --applied "migration_name"
# 最后手段（会清空数据）
npx prisma migrate reset

{
  "scripts": {
    "db:pull": "prisma db pull",
    "db:push": "prisma db push",
    "db:seed": "prisma db seed",
    "db:studio": "prisma studio",
    "migrate:dev": "prisma migrate dev",
    "migrate:deploy": "prisma migrate deploy",
    "migrate:reset": "prisma migrate reset",
    "generate": "prisma generate"
  }
}