search

kadena-io / chainweb-data

Data ingestion for Chainweb.
BSD 3-Clause "New" or "Revised" License
14 stars 8 forks source link

Implement script-based migrations #139

Closed enobayram closed 1 year ago

enobayram commented 1 year ago

This PR implements the second and last step of the transition to script based migrations (#101).

After this PR, new CW-D database migrations can be implemented by creating new scripts in the haskell-src/db-schema/migrations folder. The scripts in that folder must be named as 1.2.3.4_somename.sql. This file names correspond to the version components [1,2,3,4] along with the step name somename.sql. The version components can contain an arbitrary number of elements, so 1.2.3_othername.sql is also valid.

The migration logic implemented by this PR aims to be fairly conservative in that it expects the existing migrations to be a perfect prefix of the incoming migrations with the correct order. The order of the migrations are defined by the version components. The condition that the existing migrations need to be a prefix all the desired migrations means that once a set of migrations are run, we can only append new migrations and those migrations have to have version components that are bigger than the existing migrations.

The reason why we're being conservative like this is to avoid very subtle issues that occasionally arise due to migrations running in different orders in different deployments.

It's also worth noting that the --migrations-folder introduced by this PR is optional and when that argument is not provided, CW-D uses the set of migrations that get embedded into the binary from the repository during compilation. The purpose is to avoid increasing the operational complexity of running CW-D from a compiled binary. The set of migrations associated with a CW-D release are tightly coupled with the Haskell code that comes with it anyway.

Another point worth noting is that this migrations workflow also allows CW-D operators to interleave their own migrations with the official migrations that come with CW-D. If the operator of a particular CW-D node wants to include additional migrations, they can do so by maintaining a migrations folder of their own and including the official CW-D migrations side by side with their own migrations. In this setup, they need to name their own migration scripts to have version numbers that are compatible with this migration workflow.

Resolves #101

enobayram commented 1 year ago

Here's how I've tested this PR:

The CLI --help output

$ result/bin/chainweb-data migrate --help --dbstring $DBSTRING
Usage: chainweb-data migrate [--dbstring ARG | [--dbhost ARG] [--dbport ARG] 
                               [--dbuser ARG] [--dbpass ARG] --dbname ARG |
                               --dbdir ARG] [--level ARG] 
                             [--migrations-folder PATH]
  Run the database migrations only

Available options:
  --dbstring ARG           Postgres Connection String
  --dbhost ARG             Postgres DB hostname
  --dbport ARG             Postgres DB port
  --dbuser ARG             Postgres DB user
  --dbpass ARG             Postgres DB password
  --dbname ARG             Postgres DB name
  --dbdir ARG              Directory for self-run postgres
  --level ARG              Initial log threshold
  --migrations-folder PATH Path to the migrations folder
  -h,--help                Show this help text

Run with empty migrations folder

$ result/bin/chainweb-data migrate --dbstring $DBSTRING
2023-03-22T20:01:19.562Z [Info] [] No migrations to run
2023-03-22T20:01:19.610Z [Info] [] The DB schema is compatible with the ORM definition.
2023-03-22T20:01:19.610Z [Info] [] DB Tables Initialized

Run with test migration script

$ echo "CREATE TABLE migration_test(a int);" > haskell-src/db-schema/migrations/0.0.0_test.sql
$ nix-build -A default
$ result/bin/chainweb-data migrate --dbstring $DBSTRING
2023-03-22T20:08:57.538Z [Info] [] Running migration: 0.0.0_test.sql
2023-03-22T20:08:57.578Z [Info] [] The DB schema is compatible with the ORM definition.
2023-03-22T20:08:57.578Z [Info] [] DB Tables Initialized
$ psql $DBSTRING -c "SELECT * FROM schema_migrations"
    filename    |         checksum         |        executed_at        
----------------+--------------------------+---------------------------
 0.0.0_test.sql | weDSnSGL31GGEH95sknSmw== | 2023-03-22 21:08:57.53803
(1 row)
$ psql $DBSTRING -c "\d migration_test"
           Table "public.migration_test"
 Column |  Type   | Collation | Nullable | Default 
--------+---------+-----------+----------+---------
 a      | integer |           |          |

This step shows that compiling with a non-empty migrations folder picks up the script and runs it during the next migration.

No new migrations

$ result/bin/chainweb-data migrate --dbstring $DBSTRING
2023-03-22T22:08:51.024Z [Info] [] No migrations to run
2023-03-22T22:08:51.066Z [Info] [] The DB schema is compatible with the ORM definition.
2023-03-22T22:08:51.066Z [Info] [] DB Tables Initialized

Trying to migrate again with no new migrations doesn't do anything

--migrations-folder argument

$ result/bin/chainweb-data migrate --dbstring $DBSTRING --migrations-folder haskell-src/db-schema/migrations
2023-03-22T22:09:51.040Z [Info] [] No migrations to run
2023-03-22T22:09:51.090Z [Info] [] The DB schema is compatible with the ORM definition.
2023-03-22T22:09:51.090Z [Info] [] DB Tables Initialized

Duplicate migration step

$ cp haskell-src/db-schema/migrations/0.0.0_test.sql haskell-src/db-schema/migrations/0.0.0_test_duplicate.sql
$ result/bin/chainweb-data migrate --dbstring $DBSTRING --migrations-folder haskell-src/db-schema/migrations
2023-03-22T22:10:34.825Z [Error] [] Migration error: Duplicate step order: MigrationOrder [0,0,0] for steps "0.0.0_test_duplicate.sql" and "0.0.0_test.sql"

Unexpected migration step

$ rm haskell-src/db-schema/migrations/0.0.0_test.sql
$ result/bin/chainweb-data migrate --dbstring $DBSTRING --migrations-folder haskell-src/db-schema/migrations
2023-03-22T22:11:03.677Z [Error] [] Migration error: Steps out of order: Wanted step "test_duplicate.sql" but found step "test.sql"

Migration step with different contents

$ rm haskell-src/db-schema/migrations/0.0.0_test_duplicate.sql
$ echo "CREATE TABLE migration_test_different(a int);" > haskell-src/db-schema/migrations/0.0.0_test.sql
$ result/bin/chainweb-data migrate --dbstring $DBSTRING --migrations-folder haskell-src/db-schema/migrations
2023-03-22T22:12:03.182Z [Error] [] Migration error: Checksum mismatch: Wanted step "test.sql" with checksum "nN/1WQ94hgBOjB4htZDb3Q==" but found step "test.sql" with checksum "weDSnSGL31GGEH95sknSmw=="

Revert to the original step

$ echo "CREATE TABLE migration_test(a int);" > haskell-src/db-schema/migrations/0.0.0_test.sql
$ result/bin/chainweb-data migrate --dbstring $DBSTRING --migrations-folder haskell-src/db-schema/migrations
2023-03-22T22:12:54.256Z [Info] [] No migrations to run
2023-03-22T22:12:54.311Z [Info] [] The DB schema is compatible with the ORM definition.
2023-03-22T22:12:54.311Z [Info] [] DB Tables Initialized

Second migration step

$ echo "CREATE INDEX ON migration_test(a);" > haskell-src/db-schema/migrations/0.0.1_test_index.sql
$ result/bin/chainweb-data migrate --dbstring $DBSTRING --migrations-folder haskell-src/db-schema/migrations
2023-03-22T22:14:52.182Z [Info] [] Running migration: 0.0.1_test_index.sql
2023-03-22T22:14:52.257Z [Info] [] The DB schema is compatible with the ORM definition.
2023-03-22T22:14:52.257Z [Info] [] DB Tables Initialized
$ psql $DBSTRING -c "\d migration_test"
           Table "public.migration_test"
 Column |  Type   | Collation | Nullable | Default 
--------+---------+-----------+----------+---------
 a      | integer |           |          | 
Indexes:
    "migration_test_a_idx" btree (a)

Migration with error

$ echo "GIBBERISH!" > haskell-src/db-schema/migrations/0.0.2_test_with_error.sql
$ result/bin/chainweb-data migrate --dbstring $DBSTRING --migrations-folder haskell-src/db-schema/migrations
2023-03-22T22:16:32.400Z [Info] [] Running migration: 0.0.2_test_with_error.sql
chainweb-data: SqlError {sqlState = "42601", sqlExecStatus = FatalError, sqlErrorMsg = "syntax error at or near \"GIBBERISH\"", sqlErrorDetail = "", sqlErrorHint = ""}
$ result/bin/chainweb-data migrate --dbstring $DBSTRING --migrations-folder haskell-src/db-schema/migrations
2023-03-22T22:16:37.561Z [Info] [] Running migration: 0.0.2_test_with_error.sql
chainweb-data: SqlError {sqlState = "42601", sqlExecStatus = FatalError, sqlErrorMsg = "syntax error at or near \"GIBBERISH\"", sqlErrorDetail = "", sqlErrorHint = ""}
$ psql $DBSTRING -c "SELECT * FROM schema_migrations"
       filename       |         checksum         |        executed_at         
----------------------+--------------------------+----------------------------
 0.0.0_test.sql       | weDSnSGL31GGEH95sknSmw== | 2023-03-22 21:08:57.53803
 0.0.1_test_index.sql | sdVYZ45o3a1heg7dzulAvw== | 2023-03-22 23:14:52.177132
(2 rows)

A migration step with an error rolls back the whole migration

Migration from empty DB

enobayram commented 1 year ago

After the latest commit, I've repeated the following steps from the comment above:

And I got the same results