Skip to content

TOT-44: Fix errors in database migration files

Ghost User requested to merge tot44/fix-errors-in-database-migration-files into dev

There was only one remaining bug to be fixed in the migration files: a syntax error. On previous runs, the error output seemed to indicate that this would require a larger undertaking.

@sbiri shared a useful script to automate the application of migration files.

#!/bin/bash

# Load the .env file
source app/.env

# Export the PGPASSWORD variable
export PGPASSWORD=$DB_PASS

# List SQL files in 'changes' directory, sort them by date, and apply them
for file in $(ls database/changes/*.sql)
do
  echo "Applying migration: $file"
  #psql -h $DB_HOST -p $DB_PORT -U $DB_USER -d $DB_NAME -f $file
done

# Unset the PGPASSWORD variable
unset PGPASSWORD

It turns out that the script that was applying the migrations was sorting on modification time.

for file in $(ls -tr database/changes/*.sql)

This would typically be fine, except that recent updates to the migration files invalidated the sort on modification time.

Since the current convention is to prefix the filenames with the creation date, we can remove the -tr flags and instead rely on the default alphabetical sort to produce a "more reliable" ordering of what's expected.

for file in $(ls database/changes/*.sql)

However, caution must be taken and the output should be carefully observed.

The desired order to apply the current set of migration files.

npm run load-sql ../database/changes/2023-05-12_add_user_columns.sql
npm run load-sql ../database/changes/2023-05-23_table_aoi_public_overlay_add_foreign_key_constraint.sql
npm run load-sql ../database/changes/2023-05-24_add_app_alert_user_reference.sql
npm run load-sql ../database/changes/2023-05-24_add_constant_role_values.sql
npm run load-sql ../database/changes/2023-05-24_remove_aois_from_users_table.sql

Merge request reports