Migrations

[dtornow]

Describe the problem you are facing

During the deployment of new server versions, we often encounter the need to update our database schema to ensure compatibility with the latest features and improvements.

Describe the solution you'd like

I propose the implementation of a database migration system within our Resonate project. This system will manage and apply schema changes.

• Introduce a migrations table to track the applied schema versions, as shown below:

CREATE TABLE IF NOT EXISTS migrations (
  id    INTEGER PRIMARY KEY
);

INSERT INTO migrations (id) VALUES (1);

• On server startup, the Resonate server binary should query the migrations table for the highest id (representing the current database schema version).

• If the server detects a discrepancy between the expected schema version and the current version in the database, the server should halt the startup process and prompt the user to apply the necessary migrations to align the schema versions.

[dtornow]

Implementing a migration system is not critical for the initial release but is essential for supporting future releases.

For 1.0-rc we only need to include the migrations table for Sqlite and Postgres, no code changes required

CREATE TABLE IF NOT EXISTS migrations (
  id    INTEGER PRIMARY KEY
);

INSERT INTO migrations (id) VALUES (1);