AutoSync runs before Migrations

[rightisleft]

Autosync doesn't always work on type safe database like postgres.

I have a TEXT column that was supposed to be a Boolean.

(node:67913) UnhandledPromiseRejectionWarning: Unhandled promise rejection (rejection id: 1): error: column "isUnclear" cannot be cast automatically to type boolean

I wrote a migration:

import {Connection, EntityManager, MigrationInterface, QueryRunner} from "typeorm";

export class TransformIsUnclear1489444496065 implements MigrationInterface {
  
  public async up(queryRunner: QueryRunner, connection: Connection, entityManager?: EntityManager): Promise<any> {
    try {
      let query1 = `ALTER TABLE d_t_o_modifier ALTER COLUMN "isUnclear" DROP DEFAULT;`
      let query2 = `ALTER TABLE d_t_o_modifier ALTER "isUnclear" TYPE bool USING CASE WHEN "isUnclear" = 'false' THEN FALSE ELSE TRUE END;`
      let query3 = `ALTER TABLE d_t_o_modifier ALTER COLUMN "isUnclear" SET DEFAULT FALSE;`
      let responsea = await queryRunner.query(query1);
      let responseb = await queryRunner.query(query2);
      let responsec = await queryRunner.query(query3);
    } catch (e) {
      console.log(e);
      throw e;
    }
  }
  
  public async down(queryRunner: QueryRunner, connection: Connection, entityManager?: EntityManager): Promise<any> {
  }
 
}

But the AutoSync error still occurs before the migration is run.

Expected Behavior: AutoSync should run AFTER migrations so edge cases can be handled.

[pleerock]

looks like your error because you didn't wrote migration correctly. You need to transform exist data in columns correct way before you change column type. I didn't understand how schema sync is related to this issue.

[rightisleft]

Im not sure i follow. I've run the migration raw in PSQL and it transforms the column, and the values, as expected. (See case statement)

I then reverts the table changes and run with migrations & autosync and it fails.

I put break point inside the above migration, and they do not hit.
The AutoSync error is thrown.

Is AutoSync supposed to be executed before or after migrations? My setup is showing AutoSync executes BEFORE migrations.

[pleerock]

Im not sure to be honest what is correct order should be. Right now schema sync is executed before migrations. If we do inverse it may work, however what about first-time schema sync? By current design schema sync and migration should not be used together. You should use schema sync only first time you deploy the app, next time you deploy the app you just run your migrations, because schema sync cant guarantee you that you'll not loose your data, thats why you control it using your migrations only.

[rightisleft]

Good comment. How about a config param for firstTimeSchemaSync.

Once it has successfully executed, it stores an entry in the migrations table preventing it from being run in the future.

This is different that autoSchemaSync which currently would require a code change to disable, which will otherwise run on startup.

[pleerock]

no, this option wont be obvious and make things confusing. Simply use CLI instead. Those options in connection wasnt created for production, they was just created for development purposes.

[rightisleft]

The CLI isn't always an option in deployed environments.

[pleerock]

why? in any deployed environment you execute commands. Simply create a script you need to execute cli commands

[rightisleft]

Cloud hosting providers advise against that approach. Thats the whole point of having your build server generate a distribution bundle. The bundle that goes to a production environments like Heroku end up being pure Javascript. This allows the nodejs host to have minimal dependencies. Production servers should never know about Typescript or other build tools.

(This would be like putting the JDK into production instead of simply the JVM)

[rightisleft]

To use correctly, the CLI would need to generate an artifact that does not have any dependencies on tsc

[rightisleft]

I think at a high level we need to discuss what artifacts the CLI generates, and what the expected behavior is supposed to be through out the application life cycle.

Let's see if we can decouple "Design Time Syncs" and "Production Syncs"

Suggestions

• rename existing autoSchemaSync to: designMode: true | false
• introduce productionSchemaSync: true | false for auto schema changes that execute after migrations.
• introduce typeorm schema:write

Assertion 1:
'sync' implies that there is something to keep in sync. An empty database is different than a populated database.

-- Sync should be reserved for updating existing entities.
-- autoSchemaSync and productionSchemaSync are different
-- New @Entities should be auto created ONLY if an initial schema has already been applied to the database, and tables exist.

Assertion 2:
A developer should have control over how and when to initially populate a production database. This should be done through a schema artifact in source control.

Goal:
Expose typeorm schema:write command to generate a baseline schema file that can be checked into source control. The schema itself is still derived from referenced entities.

This seeding process will only be run if the defined database contains no tables. (first time or on all tables having been dropped)

Edge Case:
If i rerun typeorm schema:write command after being in production for months, the same schema file itself will be over written, then checked into src control, but nothing will happen at runtime.

Edge Case:
If i'm loading a developer database, or new instance, after being in production for months, i might want to generate an updated schema artifact and delete my migrations. That's a team call. Rerunning typeorm schema:write command would allow a new artifact with the latest schema.

Assertion 3:
The existing autoSchemaSync (DesignMode) functionality is a pre cursor to Assertion 2. It should be used only for local development / design time. Defining autoSchemaSync: true, and providing a schema artifact would result in a runtime error. (Maybe rename to designMode)

DesignMode would primarily be run against an empty developer database, with no migrations. It exists purely to test and create the initial database schema, or to work locally to modify the table schemas with disregard for local data. (Force dev to drop table or manually change)

ProductionSchemaSync: this would enable changes to the TS entities to cause an auto update of the table schema. Setting this to false would require all changes to occur in migrations. This has a dependency of being run after an initial schema is applied to the database.

Assertion 4:
In order to allow productionSchemaSync, a developer sometimes needs to be able to run pre-emptive migrations. The symptoms requiring these migrations surface at author time when the entity declaration is changed and run on the developers machine.

Goal:
Migrations need to run BEFORE auto sync to handle edge cases

• This would allow Entities that have been renamed to be properly altered to then be picked up by the productionSchemaSync. (Rename vs New)
• This would allow Entities that need content updated prior to an Entity schema change.

Assertion 5:

This all would all work with a traditional node config:

https://www.npmjs.com/package/config

Design Time - Developer Box

development.json
{
	"designMode": true,
	"productionSchemaSync": false,
	"initialSchema": null
}

Some Schema Change Detection:

production.json
{
	"designMode": false,
	"productionSchemaSync": true,
	"initialSchema": somefile.js
}

Migrations Only:

production.json
{
	"designMode": false,
	"productionSchemaSync": false,
	"initialSchema": somefile.js
}

[rightisleft]

Anyone have thoughts on this? Definitely looking for feedback.

[rightisleft]

This ticket should get some love!

[pleerock]

I agree process we have right now is basic and need improvements, and that quite a big work. Migrations is a big topic and I'll come back to it later once I finish some other tasks. Until that Im expecting people to leave as much feedback as possible :)