Migrations

This tool is responsible for applying schema migrations from .sql files in a consistent and idempotent manner across both the new and legacy backend projects. It ensures each migration is applied only once and maintains a history of applied migrations in the database.

To ensure consistent schema migrations across both the new backend and legacy backend projects—and to preserve backward compatibility—we will not use the built-in migration tools provided by individual ORMs. Instead, all schema changes will be defined and applied through a shared migrations project. This centralized approach serves as the single source of truth and guarantees alignment between systems.

Environment Variables

The following environment variables are expected:

Variable	Description	Required
DATABASE_URL	Connection string to the target database.	✅

How It Works

1. Initial Setup:

• Ensures the schema_migrations table exists, which tracks previously applied migrations.

2. Migration Discovery:

• Scans the src/ directory for .sql files using glob matching.
• Filters out any files that have already been applied based on entries in the schema_migrations table.

3. Execution:

• Applies each new migration file in sorted order.
• After each successful execution, it records the migration file name in the schema_migrations table to prevent reapplication.

4. Error Handling:

• If any migration fails, the process stops and throws an error with details.

File Structure Example

migrations/
├── src/
│   ├── 001_create_users_table.sql
│   ├── 002_add_index_to_users.sql
├── scripts/migrate.ts

Usage

moon migrations:migrate

This command will:

• Connect to the database using DATABASE_URL.
• Run any unapplied .sql migration files found in src/.
• Record each successful migration in the database.

Notes

• This script assumes all migration files are pure SQL.
• Migration files must be idempotent or applied only once; rollback is not supported.
• File naming should follow a sequential pattern (e.g., 001_name.sql, 002_name.sql) to ensure correct application order.