SQL migrations

The database schema is managed by the graphql project. Several approaches were evaluated and we found that graphql has the biggest dependence on SQL access, and TypeORM was the best available TypeScript-capable ORM available at the time.

Beware that TypeORM doesn't check that the database schema matches its expected structure. It's easy to put your database out-of-sync with the migrations on the filesystem by changing branch before reverting, generating migrations when there have been manual changes, changing migrations after they've run, etc. Be careful and follow the below processes when making migrations.

To make changes to the database schema

0. Run yarn exec typeorm migration:run to make sure you are on the latest migration.
1. Make the changes to the TypeORM entity models.
2. If you are running graphql in any form that automatically reloads on code change (e.g. in the development docker containers), stop it so that it doesn't automatically apply the migration.
3. Run yarn exec typeorm -- migration:generate -n YourMigrationName.
4. Open the new file in src/migrations.
5. Comment out the bugged SET DEFAULT lines generated by TypeORM every migration:

      public async up(queryRunner: QueryRunner): Promise<any> {
          // Comment out these "SET DEFAULT" lines 
          // await queryRunner.query(`ALTER TABLE "graphql"."project" ALTER COLUMN "created_dt" SET DEFAULT (now() at time zone 'utc')`);
          // await queryRunner.query(`ALTER TABLE "graphql"."coloc_job" ALTER COLUMN "start" SET DEFAULT (now() at time zone 'utc')`);
          // await queryRunner.query(`ALTER TABLE "graphql"."coloc_job" ALTER COLUMN "finish" SET DEFAULT (now() at time zone 'utc')`);
      }
   
      public async down(queryRunner: QueryRunner): Promise<any> {
          // Comment out these "SET DEFAULT" lines 
          // await queryRunner.query(`ALTER TABLE "graphql"."coloc_job" ALTER COLUMN "finish" SET DEFAULT timezone('utc'`);
          // await queryRunner.query(`ALTER TABLE "graphql"."coloc_job" ALTER COLUMN "start" SET DEFAULT timezone('utc'`);
          // await queryRunner.query(`ALTER TABLE "graphql"."project" ALTER COLUMN "created_dt" SET DEFAULT timezone('utc'`);
      }
   

   (BUG: https://github.com/typeorm/typeorm/issues/3076 )
6. Run yarn exec typeorm migration:run and check that it runs correctly.
7. If it runs without error, but the changes are incorrect, run yarn exec typeorm migration:revert to revert the last migration BEFORE attempting to fix the migration file.
8. Make sure to commit the migration file. WebStorm doesn't automatically add it to git - you have to find it in the "Unversioned Files" section of the commit window.
9. Run yarn run gen-sql-schema to re-generate the db_schema.sql script.

To make manual changes to the schema, or changes to the data

0. Run yarn exec typeorm migration:run to make sure you are on the latest migration.
1. Run yarn exec typeorm migration:create to make a new empty migration file.

In case everything is broken

The following steps can help debugging, if you have made a mistake in a feature branch:

0. Change to the master branch.
1. Delete branch-specific tables/columns in the database.
2. Use yarn exec typeorm migration:show to show migrations from the current branch, and manually delete any other migrations from the graphql.migrations table.
3. Run yarn exec typeorm migration:run to ensure you have all migrations from master applied.
4. Run yarn exec typeorm schema:sync to re-synchronize the schema.