Comparison of how Prisma and Kysely handle unordered migrations in parallel development

prisma-vs-kysely-unordered-migrations.md

🔄 Prisma vs Kysely: Unordered Migrations Comparison

📋 Executive Summary

Prisma does not suffer from the same unordered migrations problem that affects Kysely. While Kysely strictly enforces alphabetical/chronological ordering of migrations and fails when migrations are executed out of order, Prisma uses a more flexible approach that tracks migration history in the database and provides tools to handle divergent migration histories.

⚠️ The Kysely Problem

In Kysely, when developers work on parallel feature branches:

1. Each developer creates migrations with timestamps (e.g., 2024012510000_feature_a.ts, 2024012511000_feature_b.ts)
2. When branches are merged in a different order than creation, Kysely throws a "corrupted migrations" error
3. The strict alphabetical ordering enforcement blocks deployment
4. Teams must use the allowUnorderedMigrations flag to bypass this check

✨ How Prisma Handles This Differently

1. 📝 Migration Naming and Generation

Prisma generates timestamped migration names at creation time, but doesn't enforce strict chronological execution:

Source: packages/migrate/src/commands/MigrateDev.ts:258-269

const createMigrationResult = await migrate.createMigration({
  migrationName: migrationName || '',
  draft: args['--create-only'] ? true : false,
  schema: migrate.getPrismaSchema(),
})
// ...
return `Prisma Migrate created the following migration without applying it ${printMigrationId(
  createMigrationResult.generatedMigrationName!,
)}`

2. 🔢 Migration Ordering Strategy

Prisma sorts migrations lexicographically for consistency, but doesn't fail on out-of-order timestamps:

Source: packages/migrate/src/utils/listMigrations.ts:70-71

// Sort lexicographically by name
const sortedMigrations = migrationDirectories.sort((a, b) => a.path.localeCompare(b.path))

3. 🔍 Divergence Detection

Instead of failing on out-of-order migrations, Prisma detects and reports when migration histories diverge:

Source: packages/migrate/src/types.ts:69-74

{
  diagnostic: 'historiesDiverge'
  lastCommonMigrationName: string | null
  unpersistedMigrationNames: string[]
  unappliedMigrationNames: string[]
}

4. 🛠️ Conflict Resolution Tools

Prisma provides explicit commands to resolve divergent histories:

Source: packages/migrate/src/commands/MigrateStatus.ts:144-157

} else if (diagnoseResult.history?.diagnostic === 'historiesDiverge') {
  console.error(`Your local migration history and the migrations table from your database are different:

The last common migration is: ${diagnoseResult.history.lastCommonMigrationName}

The migration${diagnoseResult.history.unappliedMigrationNames.length > 1 ? 's' : ''} have not yet been applied:
${diagnoseResult.history.unappliedMigrationNames.join('\n')}

The migration${
  diagnoseResult.history.unpersistedMigrationNames.length > 1 ? 's' : ''
} from the database are not found locally in prisma/migrations:
${diagnoseResult.history.unpersistedMigrationNames.join('\n')}`)

5. 📊 Migration History Tracking

Prisma maintains a _prisma_migrations table in the database to track applied migrations:

Source: packages/migrate/src/Migrate.ts:191-200

public async applyMigrations(): Promise<EngineResults.ApplyMigrationsOutput> {
  if (!this.migrationsDirectoryPath) throw new Error('this.migrationsDirectoryPath is undefined')

  const migrationsList = await listMigrations(this.migrationsDirectoryPath, this.shadowDbInitScript)

  return this.engine.applyMigrations({
    migrationsList,
    filters: this.schemaFilter,
  })
}

🏗️ Key Architectural Differences

Aspect	Kysely	Prisma
Ordering Enforcement	Strict alphabetical order required	Lexicographic sorting, but flexible execution
Timestamp Handling	Must match execution order	Used for naming, not strict ordering
Conflict Detection	Throws "corrupted migrations" error	Detects and reports divergence gracefully
Resolution Method	allowUnorderedMigrations flag	prisma migrate resolve command
History Tracking	Tracks in migration table	_prisma_migrations table with metadata
Parallel Development	Problematic without flag	Supported with reconciliation tools

💡 Why Prisma Avoids This Problem

1. 🗄️ Database-First History

Prisma tracks what has actually been applied to the database, rather than enforcing a theoretical order. The diagnoseMigrationHistory function compares the database state with local migrations.

2. 🤝 Explicit Conflict Resolution

The prisma migrate resolve command allows teams to explicitly mark migrations as applied or rolled back, providing control over the reconciliation process.

3. 🚀 Development vs Production Workflows

Prisma distinguishes between development (migrate dev) and production (migrate deploy) workflows:

• packages/migrate/src/commands/MigrateDev.ts - Interactive development workflow
• packages/migrate/src/commands/MigrateDeploy.ts - Production deployment workflow

4. 📂 Migration List Management

The listMigrations utility provides a sorted list of migrations without enforcing strict execution order.

🎯 Practical Implications

👥 For Kysely Users

• Must coordinate migration creation across team members
• Need to enable allowUnorderedMigrations for parallel development
• Risk of migration conflicts during merges

🎉 For Prisma Users

• Can work on parallel branches without coordination
• Migration conflicts are detected and reported clearly
• Tools available to resolve conflicts intentionally
• Clear separation between development and production workflows

✅ Conclusion

Prisma's migration system is designed to handle the realities of parallel development and branch merging. By tracking migration history in the database and providing explicit tools for conflict resolution, Prisma avoids the strict ordering problems that affect Kysely. The system treats migrations as a versioned history that can diverge and be reconciled, rather than enforcing a rigid sequential order.

📚 References

• Prisma Migrate Documentation
• Kysely Migrations Documentation
• Original Kysely Unordered Migrations Issue