SuperAPI AI Directory

database-schema-impl

Coding & development

View repository

Updated 10-16 08:49

About this skill

Rapid Table Creation + ORM (Database Schema Implementation)

Applicable Scenarios

  • Rapid table creation for full-stack projects
  • ORM Schema definition
  • Migration generation and execution
  • Seed data
  • Basic indexes

Core Principles

1. Schema First
   先定义数据模型,再写代码

2. 用 ORM 不手写 SQL
   防注入 + 类型安全 + 迁移管理
  1. Basic constraints must be added NOT NULL / UNIQUE / FK

  2. Three time fields created_at / updated_at / deleted_at (if needed)

  3. For deep optimization, switch to database-engineer Index tuning / large table migration / partitioning → specialized workflow


## Prisma(推荐 TypeScript 全栈)

```prisma
// prisma/schema.prisma
generator client {
  provider = "prisma-client-js"
}

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

model User {
  id        Int      @id @default(autoincrement())
  email     String   @unique
  name      String
  password  String
  role      Role     @default(USER)
  orders    Order[]
  createdAt DateTime @default(now()) @map("created_at")
  updatedAt DateTime @updatedAt @map("updated_at")

  @@map("users")
}

model Order {
  id        Int         @id @default(autoincrement())
  userId    Int         @map("user_id")
  user      User        @relation(fields: [userId], references: [id])
  status    OrderStatus @default(DRAFT)
  total     Int         // 分为单位
  items     OrderItem[]
  createdAt DateTime    @default(now()) @map("created_at")
  updatedAt DateTime    @updatedAt @map("updated_at")

  @@index([userId, status])
  @@map("orders")
}

model OrderItem {
  id        Int   @id @default(autoincrement())
  orderId   Int   @map("order_id")
  order     Order @relation(fields: [orderId], references: [id])
  productId Int   @map("product_id")
  quantity  Int
  price     Int   // 分

  @@map("order_items")
}

enum Role {
  USER
  ADMIN
}

enum OrderStatus {
  DRAFT
  SUBMITTED
  PAID
  SHIPPED
  CANCELLED
}
# 生成 migration
npx prisma migrate dev --name init

# 生成 client
npx prisma generate

# 种子数据
npx prisma db seed

Django ORM

from django.db import models

class User(models.Model):
    email = models.EmailField(unique=True)
    name = models.CharField(max_length=100)
    role = models.CharField(max_length=20, default='user')
    created_at = models.DateTimeField(auto_now_add=True)
    updated_at = models.DateTimeField(auto_now=True)

class Order(models.Model):
    class Status(models.TextChoices):
        DRAFT = 'DRAFT'
        SUBMITTED = 'SUBMITTED'
        PAID = 'PAID'

    user = models.ForeignKey(User, on_delete=models.CASCADE, related_name='orders')
    status = models.CharField(max_length=20, choices=Status.choices, default=Status.DRAFT)
    total = models.IntegerField()  # 分
    created_at = models.DateTimeField(auto_now_add=True)
    updated_at = models.DateTimeField(auto_now=True)

    class Meta:
        indexes = [models.Index(fields=['user', 'status'])]
python manage.py makemigrations
python manage.py migrate

ActiveRecord(Rails)

# db/migrate/001_create_users.rb
class CreateUsers < ActiveRecord::Migration[7.1]
  def change
    create_table :users do |t|
      t.string :email, null: false, index: { unique: true }
      t.string :name, null: false
      t.string :role, default: 'user'
      t.timestamps
    end
  end
end

# app/models/user.rb
class User < ApplicationRecord
  has_many :orders
  validates :email, presence: true, uniqueness: true
end

Seed Data

// prisma/seed.ts
import { PrismaClient } from '@prisma/client';
const prisma = new PrismaClient();

async function main() {
  await prisma.user.create({
    data: {
      email: 'admin@example.com',
      name: 'Admin',
      role: 'ADMIN',
      password: await hash('password123'),
    },
  });
}

main();

Supporting Templates

  • templates/schema-template.md — Prisma / Django / Rails schema templates

Quality Self-Check

□ Schema 定义完成
□ 关系正确(FK)
□ 唯一约束(业务唯一字段)
□ NOT NULL(必填字段)
□ 时间字段(created_at / updated_at)
□ 枚举(状态字段)
□ 基本索引(高频查询字段)
□ Migration 可执行
□ 种子数据(开发用)
□ 深度优化转 database-engineer

Common Pitfalls

  1. Not adding unique constraints——duplicate data
  2. Not adding FK——orphan data
  3. Using float for amounts——precision loss
  4. Not adding time fields——no clues for troubleshooting
  5. Enums using string without constraints——dirty data
  6. Not writing migrations——manual table changes
  7. Not adding indexes——slow list queries

Collaboration with Other Skills

上游:
  fullstack-architecture → ORM 选型
  e2e-feature-delivery → Schema 是第一步

下游:
  api-frontend-integration → 字段来源
  auth-e2e → users 表
  database-engineer(深度)→ 索引 / 迁移 / 大表