Database Migrations

This document explains how to manage database schema changes using the project’s migration tools.

---

Generate a Migration: phantom-migration pull

This command generates new migration files by detecting differences between your resource meta-definitions (phantom-api-backend/meta/*.json) and the current state of the SQLite database (phantom.db).

Usage Example:

npx phantom-migration pull

Expected Behavior:

• The CLI (phantom-api) sends a request to your running backend.

• The backend:

• Reads the meta-definitions in phantom-api-backend/meta/.

• Compares them with the actual database structure.
• Generates a TypeScript migration file in phantom-api-backend/migrations/ if changes are detected.

Example Generated File:

// File automatically generated by the backend.
// Do not modify manually unless necessary.

import { Migration } from 'drizzle-orm/migrator';

export default {
  up: async (db: any) => {
    await db.exec(`
      CREATE TABLE IF NOT EXISTS your_table_name (
        id TEXT PRIMARY KEY NOT NULL,
        field1 TEXT,
        field2 INTEGER
      );
    `);
  },
  down: async (db: any) => {
    await db.exec(`DROP TABLE IF EXISTS your_table_name;`);
  },
} as Migration;

• The up function applies the changes.
• The down function rolls them back.

---

Apply a Migration

This command applies a specific migration file to the database via the backend.

Usage Example:

# Replace <fileName> with the exact migration file name
node phantom-api/src/migration-cli.ts apply <fileName>

Replace <fileName> with the exact name of the migration file (e.g., 20231027103000_create_posts_table.ts).

Concrete Example:

npx phantom-migration apply 20231027103000_create_posts_table.ts

---

Best Practices

• Backend Running: Ensure the backend server is started and accessible when running these commands.
• Generate Before Applying: Always run phantom-migration pull after modifying the meta-definitions, before applying migrations.
• Review Migrations: Check the contents of generated files before applying them, especially in production.
• Preserve Order: Apply migrations in the order they were generated to maintain database consistency.