Migrations

The migrations are defined as YAML files that describe schema changes. The system keeps track of applied migrations in a dedicated internal table (_migrations) and compares them against migration files stored on the filesystem (data/migrations/).

Note

Schemas are append-only. You cannot rename columns in a database trough the interface. Dropping and renaming columns is a manual task.

Note

Tables and columns are always soft-deleted when using VSKI API. Data migration and breaking changes is an admin task that has to be done carefully and with cool head.

Key components:

• Migration Files: YAML files (e.g., 20240222_100000_000_add_users_table.yaml) stored in data/migrations/. Each file contains the "up" (apply) and "down" (rollback) SQL statements to modify the schema.
• _migrations Table: An internal VSKI table that records which migrations have been successfully applied to a database. It stores the migration version, name, and a snapshot of the schema before the migration was applied, enabling reliable rollbacks.
• Schema Snapshots: Before a migration is applied, VSKI takes a snapshot of the current database schema. This snapshot is stored in the _migrations table as rollback_data, allowing for accurate schema restoration during a rollback.
• Schema Diffing: VSKI can compare the current schema with a target schema (e.g., defined in code) to automatically generate a migration file that captures the differences.

Migration Files

Migration files are declarative YAML documents that describe schema changes using collection-level operations instead of raw SQL statements.

Example (data/migrations/YYYYMMDD_HHMMSS_mmm_migration_name.yaml):

version: "20260222_113100_770"
name: migration_20260222113100
created_at: "2026-02-22T11:31:00+01:00"
description: "Add new collections and fields"
databases:
  default:
    collections:
      posts:
        create_table:
          name: posts
          type: base
          schema:
            - id: 40be1635-a5c9-4489-8e55-0dd0af58bafe
              name: title
              type: text
              required: true
              unique: false
              hidden: false
              system: false
              default: null
            - id: 550e8400-e29b-41d4-a716-446655440100
              name: content
              type: text
              required: false
              unique: false
              hidden: false
              system: false
              default: null
          system: false
      comments:
        add_columns:
          - name: post_id
            type: relation
            required: false
            unique: false
            hidden: false
            default: null
            options:
              toCollection: posts
        drop_columns:
          - old_field_name
  stats:
    collections:
      _stats_api_hits:
        add_indexes:
          - name: idx_hits_timestamp
            sql: CREATE INDEX idx_hits_timestamp ON _stats_api_hits (created)

Migration Structure

Version: Timestamp-based unique identifier (YYYYMMDD_HHMMSS_mmm) Name: Human-readable migration name Created At: ISO 8601 timestamp of when migration was generated Description: Optional description of the migration

Collection Operations

• create_table: Creates a new collection with schema
• drop_table: Drops a collection (boolean)
• add_columns: Adds new columns to an existing collection
• drop_columns: Removes columns from collection metadata (soft delete - columns remain in database)
• add_indexes: Creates new indexes
• drop_indexes: Drops indexes by name
• create_views: Creates SQL views
• drop_views: Drops views by name
• create_fts: Creates a full-text search virtual table
• drop_fts: Drops FTS table (boolean)
• update_rules: Updates access control rules

Notes

• System columns (id, created, updated) and auth-specific columns are automatically handled
• Column changes are tracked for all databases (not just default)
• Empty collection changes are filtered out from generated migrations
• Dropped columns use soft delete - they're untracked from metadata but remain in database

Managing Migrations

VSKI provides both a Web UI and a programmatic API (via SDK or direct HTTP calls) for managing migrations.

Generating Migrations

You can generate a new migration file based on the differences between your current database schema and a desired target state. This is typically done when you modify your collection schemas and want to record those changes as a migration.

Web UI: Click the "Generate" button on the Migrations page. API:

// SDK example (conceptual, actual SDK method might vary slightly)
const diff = await client.migrations.generateMigration();
console.log(diff.yaml); // The generated YAML content

The generated migration is automatically saved to the data/migrations directory as a pending migration.

Applying Migrations

Applying a migration executes the defined collection operations against the database, updating the schema. VSKI records the applied migration in the _migrations table and stores a schema snapshot for rollback.

Web UI: Click "Apply" button next to a pending migration. API:

// SDK example (conceptual)
await client.migrations.applyMigration("YYYYMMDD_HHMMSS_mmm_migration_name");

The system will:

1. Execute all create_table operations
2. Add add_columns to existing collections
3. Remove drop_columns from collection metadata (soft delete)
4. Create/drop indexes, views, and FTS tables as specified
5. Update access rules if defined
6. Record the migration in _migrations table with a pre-migration snapshot

Rolling Back Migrations

Rolling back a migration restores the database schema to the state captured in the pre-migration snapshot stored in the _migrations table. This is done by comparing the snapshot against the current state and reverting changes.

Web UI: Click the "Rollback" button next to an applied migration. API:

// SDK example (conceptual)
await client.migrations.rollbackMigration("YYYYMMDD_HHMMSS_mmm_migration_name");

Listing Migrations

You can view a list of all migration files found in data/migrations/ and their current status (applied, pending, failed).

Web UI: The Migrations page displays both applied and pending migrations. API:

// SDK example (conceptual)
const migrations = await client.migrations.list();
migrations.forEach((m) =>
  console.log(`${m.version}: ${m.name} - Status: ${m.status}`)
);

Downloading / Exporting Migrations

Individual migration YAML files can be downloaded, and all migrations can be exported as a ZIP archive.

Web UI: "Download" button for individual migrations, "Export All" button for a ZIP archive. API:

// Download a single migration (conceptual)
const yamlContent = await client.migrations.downloadMigration(
  "YYYYMMDD_HHMMSS_mmm_migration_name",
);

// Export all as ZIP (conceptual)
const zipFile = await client.migrations.exportAllMigrations();

Uploading / Importing Migrations

You can upload individual migration YAML files or import a ZIP archive containing multiple migrations.

Web UI: "Upload" button on the Migrations page. API:

// Upload a single migration file (conceptual)
await client.migrations.uploadMigration(yamlFileContent);

// Import migrations from ZIP (conceptual)
await client.migrations.importMigrations(zipFileContent);

Deleting Migrations

Web UI: "Delete" button next to a pending migration. API:

// SDK example (conceptual)
await client.migrations.deleteMigration("YYYYMMDD_HHMMSS_mmm_migration_name");

Migration Status

The Web UI and API provide a consolidated status of your migration system, showing the count of applied and pending migrations, and the version of the last applied migration.

Web UI: Displays migration status at the top of the Migrations page. API:

// SDK example (conceptual)
const status = await client.migrations.getStatus();
console.log(
  `Applied: ${status.appliedCount}, Pending: ${status.pendingCount}, Last Applied: ${status.lastAppliedVersion}`,
);

Best Practices

1. Version Control: Always keep your migration files under version control (e.g., Git). This ensures a historical record of your schema changes.
2. Test Migrations: Thoroughly test your migrations in a development or staging environment before applying them to production.
3. Backup Your Data: Before applying any migration, especially in production, always back up your database.
4. Clear Naming Conventions: Use descriptive names for your migration files and within the YAML name field to clearly indicate their purpose.
5. Small, Focused Migrations: Prefer creating small, atomic migrations that address a single schema change, rather than large, monolithic ones. This makes them easier to understand, review, and rollback.
6. Never Edit Applied Migrations: Once a migration has been applied, do not modify its content. If you need to make changes, create a new migration or rollback the existing one and then create a new one.

API Endpoints

Method	Endpoint	Description
GET	/api/migrations	List all migrations (applied and pending)
GET	/api/migrations/status	Get the current status of the migration system
GET	/api/migrations/generate	Generate a new migration file from schema diff
POST	/api/migrations/save	Save a generated migration to a file
GET	/api/migrations/load	Load a migration from a file
POST	/api/migrations/upload	Upload a migration file
GET	/api/migrations/export	Export all migrations as a ZIP file
POST	/api/migrations/import	Import migrations from a ZIP file
GET	/api/migrations/{version}/download	Download a specific migration file as YAML
POST	/api/migrations/{version}/apply	Apply a migration
POST	/api/migrations/{version}/rollback	Rollback a migration
DELETE	/api/migrations/{version}	Delete a migration file and its record from the database