managing-database-schemas

name	managing-database-schemas
description	Analyzes database schemas and suggests improvements for Prisma, TypeORM, or Drizzle. Use when the user asks about database schema, migrations, ORM setup, or wants to generate TypeScript types from database models.

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