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:

Tool Config Files

Prisma prisma/schema.prisma , package.json (prisma)

TypeORM ormconfig.* , data-source.ts , typeorm in pkg

Drizzle drizzle.config.* , drizzle in package.json

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:

Issue Detection Suggestion

Missing indexes Foreign keys without @index

Add index for query performance

Inconsistent naming Mixed camelCase /snake_case

Standardize naming convention

Missing timestamps Tables without createdAt

Add audit timestamps

Duplicate field groups Same fields across models Extract to shared type/mixin

Missing relations IDs without @relation

Add proper relation decorators

N+1 risk Nested relations without eager Document loading strategy

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