Managing Database Schemas

Database Schema Manager

When to use this skill

• User asks to analyze or review database schema
• User wants to create or modify migrations
• User mentions Prisma, TypeORM, or Drizzle
• User asks to generate TypeScript types from schema
• User wants to detect schema issues or duplications

Workflow

• Detect ORM/schema tool in use
• Read current schema files
• Analyze schema structure
• Identify issues and improvements
• Suggest DRY refactors if applicable
• Generate TypeScript types if requested

Instructions

Step 1: Detect Schema Tool

Check for ORM configurations:

ls prisma/schema.prisma 2>/dev/null && echo "Prisma"
ls drizzle.config.* 2>/dev/null && echo "Drizzle"
grep -l "typeorm\|TypeOrmModule" src/**/*.ts 2>/dev/null | head -1 && echo "TypeORM"

Step 2: Read Schema Files

Prisma:

cat prisma/schema.prisma

Drizzle:

cat src/db/schema.ts src/db/schema/*.ts 2>/dev/null

TypeORM:

find src -name "*.entity.ts" -exec cat {} \;

Step 3: Analyze Schema Structure

Extract and categorize:

• Models/tables and their fields
• Relationships (one-to-one, one-to-many, many-to-many)
• Indexes and constraints
• Enums and custom types

Look for:

• Field naming conventions
• Consistent timestamp fields (createdAt, updatedAt)
• Soft delete patterns (deletedAt)
• Audit fields consistency

Step 4: Identify Issues

Common schema issues:

Step 5: Suggest DRY Refactors

Prisma — Abstract fields pattern:

// Before: Repeated in every model
model User {
  id        String   @id @default(cuid())
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
  // ...
}

model Post {
  id        String   @id @default(cuid())
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
  // ...
}

Document the pattern (Prisma doesn't support inheritance, but document for consistency):

// Standard fields for all models:
// id        String   @id @default(cuid())
// createdAt DateTime @default(now())
// updatedAt DateTime @updatedAt

Drizzle — Shared columns:

// src/db/shared.ts
import { timestamp, varchar } from "drizzle-orm/pg-core";

export const timestamps = {
  createdAt: timestamp("created_at").defaultNow().notNull(),
  updatedAt: timestamp("updated_at").defaultNow().notNull(),
};

export const primaryId = {
  id: varchar("id", { length: 36 }).primaryKey(),
};

// src/db/schema/users.ts
import { pgTable, varchar } from "drizzle-orm/pg-core";
import { primaryId, timestamps } from "../shared";

export const users = pgTable("users", {
  ...primaryId,
  ...timestamps,
  email: varchar("email", { length: 255 }).notNull().unique(),
});

TypeORM — Base entity:

// src/entities/base.entity.ts
import {
  PrimaryGeneratedColumn,
  CreateDateColumn,
  UpdateDateColumn,
} from "typeorm";

export abstract class BaseEntity {
  @PrimaryGeneratedColumn("uuid")
  id: string;

  @CreateDateColumn()
  createdAt: Date;

  @UpdateDateColumn()
  updatedAt: Date;
}

// src/entities/user.entity.ts
import { Entity, Column } from "typeorm";
import { BaseEntity } from "./base.entity";

@Entity("users")
export class User extends BaseEntity {
  @Column({ unique: true })
  email: string;
}

Step 6: Generate TypeScript Types

From Prisma (built-in):

npx prisma generate
# Types available at @prisma/client

From Drizzle (built-in):

import { InferSelectModel, InferInsertModel } from "drizzle-orm";
import { users } from "./schema";

export type User = InferSelectModel<typeof users>;
export type NewUser = InferInsertModel<typeof users>;

From TypeORM entities:

// Types are the entity classes themselves
import { User } from "./entities/user.entity";

// For DTOs, create separate types
export type CreateUserDto = Pick<User, "email" | "name">;
export type UpdateUserDto = Partial<CreateUserDto>;

Migration Commands

Prisma:

# Create migration
npx prisma migrate dev --name <migration-name>

# Apply migrations (production)
npx prisma migrate deploy

# Reset database
npx prisma migrate reset

# View migration status
npx prisma migrate status

Drizzle:

# Generate migration
npx drizzle-kit generate

# Apply migrations
npx drizzle-kit migrate

# Push changes directly (dev only)
npx drizzle-kit push

# View schema diff
npx drizzle-kit diff

TypeORM:

# Generate migration
npx typeorm migration:generate -n <MigrationName>

# Run migrations
npx typeorm migration:run

# Revert last migration
npx typeorm migration:revert

# Show migrations
npx typeorm migration:show

Schema Change Impact Analysis

When analyzing schema changes, report:

## Schema Change Analysis

### Added

- `users.phoneNumber` (varchar, nullable) — No migration needed for existing data

### Modified

- `posts.status` enum — Added 'archived' value — Existing rows unaffected

### Removed

- `users.legacyId` — ⚠️ Ensure no code references before removing

### Index Changes

- Added index on `orders.userId` — Improves query performance, no data impact

### Breaking Changes

- ⚠️ `users.email` now NOT NULL — Requires data migration for null values

Validation

Before completing:

• Schema syntax is valid
• All relations have proper back-references
• Indexes exist for foreign keys
• Naming conventions are consistent
• Migration files are generated if needed
• TypeScript types are in sync

Error Handling

• Schema validation fails: Run ORM-specific validate command (prisma validate, etc.).
• Migration conflicts: Check migration history and resolve manually.
• Type generation fails: Ensure schema is valid and ORM client is installed.
• Unsure about command: Run npx <tool> --help for available options.

Resources

• Prisma Documentation
• Drizzle Documentation
• TypeORM Documentation