Database Migrations

Datafly Signal uses golang-migrate to manage PostgreSQL schema changes. Migrations must be run before starting services after any upgrade that includes database changes.

Migration Files

Migration files are located at:

database/migrations/
├── 000001_create_organisations.up.sql
├── 000001_create_organisations.down.sql
├── 000002_create_users.up.sql
├── 000002_create_users.down.sql
├── 000003_create_sources.up.sql
├── 000003_create_sources.down.sql
├── ...

Each migration has two files:

Migrations are applied in order by their numeric prefix. The schema_migrations table in PostgreSQL tracks which migrations have been applied.

Running Migrations

Apply all pending migrations

From the Application/ directory:

make migrate-up

This connects to PostgreSQL using the DATABASE_URL environment variable (default: postgresql://datafly:datafly@localhost:5432/datafly?sslmode=disable) and applies all pending migrations in order.

Revert the last migration

make migrate-down

This reverts the most recently applied migration.

Check migration status

To see which migrations have been applied:

psql -h localhost -U datafly -d datafly -c "SELECT * FROM schema_migrations ORDER BY version;"

Creating New Migrations

To create a new migration:

make migrate-create name=add_consent_categories

This creates two files:

database/migrations/000015_add_consent_categories.up.sql
database/migrations/000015_add_consent_categories.down.sql

Edit both files to define the forward and rollback SQL.

Migration Guidelines

• Always write both up and down migrations. The down migration should cleanly revert the up migration.
• Never modify an existing migration that has been applied to any environment. Create a new migration instead.
• Use transactions for multi-statement migrations. golang-migrate wraps each migration file in a transaction by default.
• Test migrations locally before applying to staging or production: run make migrate-up, verify the schema, then run make migrate-down to confirm the rollback works.

Production Migrations

Kubernetes

For Kubernetes deployments, run migrations as a Kubernetes Job before upgrading services:

kubectl run migrations --rm -it \
  --namespace customer-acme \
  --image=ghcr.io/datafly/migrations:v1.2.0 \
  --env="DATABASE_URL=postgresql://datafly:secret@db-host:5432/datafly?sslmode=require" \
  -- migrate-up

Or include the migration job in the Helm chart’s pre-upgrade hook:

migrations:
  enabled: true
  runAsPreUpgradeHook: true

Customer-Hosted

For customer-hosted deployments, the migration image is provided as part of the release. The customer or operations team runs the migration before upgrading services.

Seeding Development Data

For local development, seed the database with sample data:

make seed

This creates:

• A default organisation
• An admin user (admin@datafly.local / datafly)
• A sample source with a pipeline key
• Sample integrations

Troubleshooting

”dirty” migration state

If a migration fails partway through, golang-migrate marks the database as “dirty”. To fix:

1. Check the current state:

   psql -h localhost -U datafly -d datafly \
     -c "SELECT version, dirty FROM schema_migrations;"

2. Manually fix the database to match the expected state of the failed migration.

3. Force the version (mark the dirty migration as applied or revert it):

   # If the migration was partially applied and you fixed it manually:
   migrate -path database/migrations \
     -database "postgresql://datafly:datafly@localhost:5432/datafly?sslmode=disable" \
     force <version>

4. Re-run make migrate-up.

Connection refused

Ensure PostgreSQL is running and the DATABASE_URL is correct:

psql "$DATABASE_URL" -c "SELECT 1"

Permission denied

The database user must have CREATE TABLE, ALTER TABLE, and DROP TABLE privileges on the target database.