Feature request: Database migration system with cross-database compatibility

[liolocs]

Description

• Problem: Primate lacks a migration system for managing database schema changes. When users modify store shapes (e.g., adding/removing fields, changing types), the database breaks without a way to apply incremental changes. This is a critical blocker for production applications where schema evolution is inevitable.

• Proposal:

  • Add migrate:create command to generate migration files based on store changes.
  • Add migrate:apply command to apply pending migrations to the database.
  • Make migrations database-agnostic, working across Postgres, SQLite, MySQL, SurrealDB, and MongoDB.
  • Support database switching with automatic schema conversion.
  • Track applied migrations to prevent duplicate application.

Current situation (no migrations)

import p from "pema";
import store from "primate/store";

export default store({
  id: p.primary,
  counter: p.i8.range(-20, 20),
});

If a user later modifies the store:

export default store({
  id: p.primary,
  counter: p.i8.range(-20, 20),
  username: p.string,  // NEW FIELD - breaks existing database!
});

Result: Application crashes or data corruption. No safe path to evolve schema.

Desired functionality

Create migration:

bun run migrate:create add-username-field
# Creates: migrations/2025-12-12_add-username-field.ts

Generated migration file:

export default {
  up: async (db) => {
    // Database-agnostic operations
    await db.alter("User", {
      add: { username: "string" }
    });
  },
  down: async (db) => {
    await db.alter("User", {
      remove: ["username"]
    });
  }
};

Apply migrations:

bun run migrate:apply
# Applies all pending migrations in order

Switch databases ( in the future and not part of this request ):

bun run migrate:convert --from sqlite --to postgres
# Converts migration history and schema

Benefits

• Production-ready: Safe schema evolution without data loss.
• Database flexibility: Switch databases (e.g., SQLite → Postgres) as app scales.
• Team collaboration: Track schema changes in version control.
• Rollback safety: down migrations allow reverting changes.
• Type safety: Migrations can leverage pema types for validation.
• Zero-config: Auto-detect stores and generate migrations from diffs.

Acceptance criteria

• Commands: migrate:create, migrate:apply, migrate:rollback, migrate:status, work across all supported databases.
• Database-agnostic: Migration DSL abstracts database-specific SQL/queries.
• Tracking: System tracks which migrations have been applied (e.g., _migrations table).
• Automatic generation: Optional migrate:create --auto detects store changes and generates migration.
• Validation: Warn if store schema and database are out of sync.
• Idempotency: Re-running migrate:apply is safe (only applies pending).
• Backwards compatibility: Existing projects without migrations continue working.

Additional considerations

• Seed data: Optional db:seed command for test data.
• Migration templates: Support custom templates for complex operations.
• Dry run: migrate:apply --dry-run to preview changes.
• Production safety: Confirmation prompts for destructive operations.