It is handled in the swift projects:

SQL migrations

We have Migrations folder

The format of the migration files is:

YYYY.MM.DD__wisk_api__migration_name.sql

The file can contain multiple SQL statements separated by ; or by —-

drop table if exists bottles_onboarding;
ALTER TABLE venues DROP COLUMN IF EXISTS onboarding_type;
delete from timeline where type='venue' and json->>'type'='onboarding_type';

In this scenario we use —- because splitting by ; would not work because of the function. Please note that you cannot combine —- and ; in a migration, if you use —- once then this is the separator for the entire migration file

----
-- Trigger function to set realtime_sync_sequence_id using the sequence
-- and to increment version_number on updates
CREATE OR REPLACE FUNCTION manage_sequence_version()
RETURNS TRIGGER AS $$
BEGIN
    NEW.realtime_sync_sequence_id = NEXTVAL('realtime_sync_sequence_id_seq');

    -- If it's an update, increase the version_number
    IF TG_OP = 'UPDATE' THEN
        NEW.version_number = OLD.version_number + 1;
    END IF;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;
----

Queue operations

The migration also supports queue operations. add .que to the filename here is an example: V2023.07.20__wisk_api_migrate_billing_updates_default_true.que

{
	"post_body": {
		"type": "migrate_billing_updates_default_true"
	}
}

The queue operation is defined in the QueueOperation enum in the QueueOperation.swift file. The migration will add the job to the queue and it will be executed by the queue worker.

How it works

When the API is deployed, the migrations are executed automatically. The migrations are executed in the order they are registered in the MigrationsRepository.swift file.

The executed migrations are stored in the _fluent_migrations table in the database.

Inspiration

The format is inspired by the Flyway project.