Database Schema Generation

Schema Generation Rules

1. Entity Detection

• Each interface with id field becomes a model
• Nested objects become separate models with relations
• Arrays of primitives become JSON fields
• Arrays of objects become 1:n relations

2. Field Mapping

3. Relationship Detection

// 1:1 - User has one Profile
interface User {
  profile: Profile;
}

// 1:n - User has many Posts
interface User {
  posts: Post[];
}

// n:m - Post has many Tags, Tag has many Posts
interface Post {
  tags: Tag[];
}
interface Tag {
  posts: Post[];
}

Example Transformation

Input: TypeScript Contract

interface User {
  id: string;
  email: string;
  name: string;
  role: UserRole;
  posts: Post[];
  profile?: Profile;
  createdAt: Date;
  updatedAt: Date;
}

enum UserRole {
  ADMIN = "admin",
  USER = "user",
  GUEST = "guest"
}

interface Post {
  id: string;
  title: string;
  content: string;
  published: boolean;
  authorId: string;
  author: User;
  tags: Tag[];
}

interface Tag {
  id: string;
  name: string;
  posts: Post[];
}

interface Profile {
  id: string;
  bio?: string;
  avatar?: string;
  userId: string;
  user: User;
}

Output: Prisma Schema

generator client {
  provider = "prisma-client-js"
}

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

enum UserRole {
  ADMIN
  USER
  GUEST
}

model User {
  id        String    @id @default(uuid())
  email     String    @unique
  name      String
  role      UserRole  @default(USER)
  posts     Post[]
  profile   Profile?
  createdAt DateTime  @default(now())
  updatedAt DateTime  @updatedAt

  @@index([email])
  @@index([role])
}

model Post {
  id        String   @id @default(uuid())
  title     String
  content   String   @db.Text
  published Boolean  @default(false)
  author    User     @relation(fields: [authorId], references: [id], onDelete: Cascade)
  authorId  String
  tags      Tag[]
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt

  @@index([authorId])
  @@index([published])
}

model Tag {
  id    String @id @default(uuid())
  name  String @unique
  posts Post[]
}

model Profile {
  id     String  @id @default(uuid())
  bio    String? @db.Text
  avatar String?
  user   User    @relation(fields: [userId], references: [id], onDelete: Cascade)
  userId String  @unique
}

Migration Strategy

Initial Migration

npx prisma migrate dev --name init

Safe Migrations (preserve data)

1. Add new columns as nullable first
2. Migrate existing data
3. Add NOT NULL constraint

Migration Workflow

Schema Change Detected
       ↓
Generate Migration SQL
       ↓
Review for Data Loss
       ↓
Apply Migration
       ↓
DATABASE_MIGRATION_COMPLETE Event

Seed Data Generation

Generate seed data from requirements or sample data:

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

const prisma = new PrismaClient();

async function main() {
  // Create admin user
  const admin = await prisma.user.upsert({
    where: { email: '[email protected]' },
    update: {},
    create: {
      email: '[email protected]',
      name: 'Admin User',
      role: 'ADMIN',
      profile: {
        create: {
          bio: 'System administrator',
        },
      },
    },
  });

  console.log({ admin });
}

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

Database Client Setup

// src/lib/prisma.ts
// CRITICAL: Load env vars BEFORE PrismaClient initialization (ESM hoisting fix)
import 'dotenv/config';
import { PrismaClient } from '@prisma/client';

const globalForPrisma = globalThis as unknown as {
  prisma: PrismaClient | undefined;
};

export const prisma = globalForPrisma.prisma ?? new PrismaClient({
  log: process.env.NODE_ENV === 'development'
    ? ['query', 'error', 'warn']
    : ['error'],
});

if (process.env.NODE_ENV !== 'production') {
  globalForPrisma.prisma = prisma;
}

Anti-Mock Policy

NIEMALS generieren:

• Hardcoded Daten-Arrays als "Datenbank-Ersatz"
• In-Memory SQLite ohne Persistenz
• Mock-Datenbank-Clients
• Fake-Queries die keine echte DB ansprechen

IMMER generieren:

• Echtes Prisma/SQLAlchemy Schema
• Funktionierende Migrations
• Seed-Dateien mit echten Daten
• Prisma Client mit echten Queries

Output Files

output/
├── prisma/
│   ├── schema.prisma       # Database schema
│   ├── migrations/         # Auto-generated migrations
│   └── seed.ts            # Seed data script
├── src/
│   ├── lib/
│   │   └── prisma.ts      # Prisma client singleton
│   └── db/
│       └── queries.ts     # Common query helpers
└── package.json           # prisma, @prisma/client deps

Environment Requirements

# .env.local
DATABASE_URL="postgresql://user:password@localhost:5432/mydb?schema=public"

Docker Integration

# docker-compose.yml