Database Schema Validator

Database Schema Validator Skill

This skill provides database schema validation for Kysely-based projects. It ensures type safety, migration correctness, and schema best practices.

Capabilities

Schema Type Validation

• Verify Kysely schema type definitions
• Check column type consistency
• Validate foreign key relationships
• Ensure proper nullable/required field definitions
• Detect missing or incorrect type mappings

Migration Analysis

• Validate migration file structure
• Check migration naming conventions
• Verify migration ordering
• Detect unsafe migrations (data loss risks)
• Ensure rollback compatibility

Best Practices Enforcement

• Check for proper indexing
• Verify primary key definitions
• Validate unique constraints
• Ensure proper use of timestamps
• Check for naming convention compliance

Consistency Checks

• Compare schema types with actual migrations
• Verify consistency between schema.ts and migration files
• Check for orphaned foreign keys
• Detect schema drift

Usage Examples

Validate Schema Type Definitions

// Good: Properly typed schema
export interface Database {
  users: {
    id: Generated<string>;
    email: string;
    name: string | null;
    created_at: Generated<string>;
    updated_at: string;
  };
  posts: {
    id: Generated<string>;
    user_id: string;  // Foreign key
    title: string;
    content: string;
    published: Generated<boolean>;
    created_at: Generated<string>;
  };
}

// Bad: Inconsistent or missing types
export interface Database {
  users: {
    id: string;  // ❌ Should use Generated<string>
    email: any;  // ❌ Should have specific type
    // ❌ Missing created_at
  };
}

Migration File Structure

// Good: Proper migration structure
import { Kysely, sql } from 'kysely';

export async function up(db: Kysely<any>): Promise<void> {
  await db.schema
    .createTable('users')
    .addColumn('id', 'text', (col) => col.primaryKey())
    .addColumn('email', 'text', (col) => col.notNull().unique())
    .addColumn('name', 'text')
    .addColumn('created_at', 'text', (col) =>
      col.notNull().defaultTo(sql`CURRENT_TIMESTAMP`)
    )
    .execute();

  // Create index for frequently queried columns
  await db.schema
    .createIndex('users_email_idx')
    .on('users')
    .column('email')
    .execute();
}

export async function down(db: Kysely<any>): Promise<void> {
  await db.schema.dropTable('users').execute();
}

// Bad: Missing down migration
export async function up(db: Kysely<any>): Promise<void> {
  // ... migration code
}

// ❌ No down function - can't rollback!

Foreign Key Validation

// Good: Proper foreign key with constraints
await db.schema
  .createTable('posts')
  .addColumn('id', 'text', (col) => col.primaryKey())
  .addColumn('user_id', 'text', (col) =>
    col.notNull().references('users.id').onDelete('cascade')
  )
  .execute();

// Bad: No foreign key constraint
await db.schema
  .createTable('posts')
  .addColumn('user_id', 'text')  // ❌ No foreign key reference
  .execute();

Validation Checks

Schema Type Checks

1. Primary Keys

// ✅ Correct
id: Generated<string>;

// ❌ Incorrect (missing Generated)
id: string;

2. Timestamps

// ✅ Correct (auto-generated)
created_at: Generated<string>;

// ✅ Correct (manually set)
updated_at: string;

// ❌ Incorrect (should be Generated or string)
created_at: Date;

3. Nullable Fields

// ✅ Correct
name: string | null;

// ❌ Incorrect (TypeScript null vs SQL NULL)
name?: string;  // Optional property, not nullable column

4. Foreign Keys

// ✅ Correct (matches referenced table's PK type)
users: {
  id: Generated<string>;
};
posts: {
  user_id: string;  // Matches users.id type
};

// ❌ Incorrect (type mismatch)
users: {
  id: Generated<number>;
};
posts: {
  user_id: string;  // ❌ Should be number
};

Migration Safety Checks

Safe Migrations:

• Adding new tables
• Adding new columns (with defaults or nullable)
• Adding indexes
• Creating new foreign keys (if data integrity allows)

Unsafe Migrations (require caution):

• Dropping tables (data loss)
• Dropping columns (data loss)
• Changing column types (potential data loss)
• Adding NOT NULL constraints (fails if existing NULLs)
• Modifying foreign keys (may fail on existing data)

Migration Naming Convention

Format: <sequence>_<description>.ts

Good examples:
- 001_initial_schema.ts
- 002_add_user_roles.ts
- 003_add_post_categories.ts

Bad examples:
- migration.ts           // ❌ No sequence number
- 1_users.ts            // ❌ Inconsistent padding
- add-posts.ts          // ❌ No sequence number
- 002_AddPosts.ts       // ❌ PascalCase instead of snake_case

Validation Workflow

1. Schema Type Validation

# Check TypeScript compilation
npx tsc --noEmit

# Verify schema types match database
# (Manual review or custom validation script)

2. Migration File Validation

Check each migration for:

• Proper naming convention
• Presence of both up and down functions
• Correct Kysely API usage
• Proper error handling
• Transaction safety

3. Schema Consistency Check

// Example validation script
import { Database } from './schema';
import { Kysely } from 'kysely';

async function validateSchema(db: Kysely<Database>) {
  // 1. Check if all tables exist
  const tables = await db.introspection.getTables();

  // 2. Verify foreign key references
  for (const table of tables) {
    const foreignKeys = await db.introspection.getTableMetadata(table.name);
    // Validate each FK points to existing table/column
  }

  // 3. Check for orphaned rows
  // Custom queries to detect FK violations
}

Common Issues and Solutions

Issue: Schema Type Mismatch

Problem:

// schema.ts
posts: {
  user_id: string;
};

// Migration has:
.addColumn('user_id', 'integer')  // ❌ Type mismatch

Solution:

// Fix migration to match schema
.addColumn('user_id', 'text')  // ✅ Now matches

Issue: Missing Down Migration

Problem:

export async function up(db: Kysely<any>): Promise<void> {
  await db.schema.createTable('users').execute();
}
// ❌ No down function

Solution:

export async function down(db: Kysely<any>): Promise<void> {
  await db.schema.dropTable('users').execute();
}

Issue: Unsafe Column Deletion

Problem:

// Migration that drops column
await db.schema
  .alterTable('users')
  .dropColumn('old_field')
  .execute();
// ❌ Data loss!

Solution:

// 1. First, migrate data to new column
await db
  .updateTable('users')
  .set({ new_field: sql`old_field` })
  .execute();

// 2. Then drop old column in a later migration
// Give users time to rollback if needed

Issue: Missing Foreign Key Constraint

Problem:

// Schema defines relationship
posts: {
  user_id: string;  // Implies FK to users.id
};

// But migration doesn't enforce it
.addColumn('user_id', 'text')  // ❌ No constraint

Solution:

.addColumn('user_id', 'text', (col) =>
  col.notNull().references('users.id').onDelete('cascade')
)

Best Practices

Schema Design

1. Use Consistent Naming

• Tables: plural, snake_case (e.g., user_profiles)
• Columns: singular, snake_case (e.g., created_at)
• Foreign keys: {table}_id (e.g., user_id)

2. Always Include Timestamps

created_at: Generated<string>;
updated_at: string;

3. Use Appropriate Types

• IDs: text (for UUIDs) or integer (for auto-increment)
• Booleans: boolean with defaults
• Timestamps: text (ISO 8601) or integer (Unix timestamp)
• Money: integer (cents) to avoid floating point issues

4. Index Frequently Queried Columns

// Create index for email lookups
await db.schema
  .createIndex('users_email_idx')
  .on('users')
  .column('email')
  .execute();

Migration Best Practices

1. Make Migrations Atomic

Use transactions when possible:

await db.transaction().execute(async (trx) => {
  await trx.schema.createTable('users').execute();
  await trx.schema.createTable('posts').execute();
});

2. Test Migrations Both Ways

• Test up migration applies successfully
• Test down migration rolls back completely
• Verify data integrity after each

3. Version Control

• Always commit schema.ts and migrations together
• Never modify existing migrations that have been applied
• Create new migrations for schema changes

4. Document Complex Migrations

/**
 * Migration: Add user_roles table
 *
 * This migration creates a many-to-many relationship between
 * users and roles through the user_roles join table.
 *
 * Rollback: Drops user_roles table and all role assignments.
 */
export async function up(db: Kysely<any>): Promise<void> {
  // ...
}

Output Format

When reporting validation results, use this format:

# Database Schema Validation Report

## Summary
- Total Tables: X
- Total Migrations: Y
- Issues Found: Z

## Critical Issues
[Issues that must be fixed immediately]

### Schema Type Mismatch
**Table:** `posts`
**Column:** `user_id`
**Issue:** Schema type `string` doesn't match migration type `integer`
**Fix:**
\`\`\`typescript
// Update migration or schema to match
.addColumn('user_id', 'text')  // Match schema type
\`\`\`

## Warnings
[Issues that should be addressed]

## Recommendations
[Suggestions for improvement]

## Schema Consistency
✅ All foreign keys have proper constraints
✅ All tables have timestamps
✅ All migrations have down functions
⚠️ Missing indexes on frequently queried columns

Validation Checklist

• All tables defined in schema.ts have corresponding migrations
• All foreign keys in schema match migration definitions
• All migrations have both up and down functions
• Migration files follow naming convention
• No any types in schema definitions
• All required fields use notNull() in migrations
• Timestamps use Generated<string> for auto-generated fields
• Foreign keys have proper onDelete behavior
• Frequently queried columns are indexed
• No unsafe migrations without data migration plan