Add a how-to deal-with-db-migrations to the docs

[RobinBuschmann]

As mentioned here #274 and due to https://github.com/sequelize/cli/issues/441#issuecomment-365920223 we should inform users of sequelize-typescript how to deal with db migrations.

[Toub]

After reading the related issues, I am not sure to understand if it is possible (and how) to use sequelize-cli to migrate sequelize-typescript models, or if we need to use a pure SQL script.

[ghost]

As per the linked issue's discussion I can write a blog post that details how to use one of two tools, or both if I feel super gouda.

I'll comment updates here as I go, and I can PR a link to the blog in the readme, or we can adapt it as a Wiki post in this github repo (idk how wikis work or if they're any good >.> - do they markdown? Are the SEO friendly?)

[RobinBuschmann]

Hey @sanewell92, sounds great. I prefer the readme or another versioned *.md file over github wiki :) Regarding SEO: I think github will do the job for us.

[vschoener]

@sanewell92 Sounds good :) Did you start something? :)

[ghost]

I'm officially moving my GitHub presence to the @snewell92 account. This won't confusing at all 😆

I have some time to continue the work I started; if you want to follow my progress this is the branch on a fork on the aforementioned account.

Feel free to check out commit ab0b57294e8c70ed5a5c0ffc12bb0d3177150caf for the deets.

[cjancsar]

@snewell92 did you ever make any progress on this? That branch is dead.

[tommymarshall]

We are looking to have this feature as well.

[redevill]

So - I have managed to make the seeders and migrations work with "sequelize": "^6.6.2", "sequelize-typescript": "^2.1.0". There was only one slight hiccup:

import { SequelizeOptions } from 'sequelize-typescript/dist/sequelize/sequelize/sequelize-options'; the Options Spec does not include things like "seederStorage" and "seederStorageTableName". If these could be added as legitimate options, then we would not need the "lint disable"

[pj035]

@redevill can you elaborate more in detail how you approached and managed this? An example would be nice.

[redevill]

I will try:

Environment: I have a node(12.22.1) /npm@7.11.1/ express(4.16.4) / mySql(5.7.24) project, that is intended to run on a server, under a PM2 process manager. (Webstorm dev) The ORM is Sequelize-Typescript(2.1.0) (extension of Sequelize@6.6.2) and the dev dependency sequelize-cli(6.2.0) & Typescript(4.1.3)

Sequelize-cli - Setup. root directory .sequelizerc - this file is the starting point for the sqlz-cli.

`const path = require('path');

module.exports = { 'config': path.resolve('dist', 'server', 'db', 'sqlzCliConfig.js'), 'models-path': path.resolve('server', 'models'), 'seeders-path': path.resolve('server', 'db', 'seeders'), 'migrations-path': path.resolve('server', 'db', 'migrations') }`

Note the javascript file referenced in this file, is found in the Typescript OUTPUT. There is a corresponding .ts file in my source. As this implies, you have to compile the project before you can run your migrations.

The code in this file, needs to produce a set of "SequelizeOptions", mostly... because as mentioned above, the migration options are not included in this Type.

module.exports = { development: get_dbOptions(pwdSvcToSecurelySupplyEnvironmentPassword, serverConfigEnvironmentVariable), test: get_dbOptions(pwdSvcToSecurelySupplyEnvironmentPassword, serverConfigEnvironmentVariable), production: get_dbOptions(pwdSvcToSecurelySupplyEnvironmentPassword, serverConfigEnvironmentVariable), };

So the return JSON object which is the "Mostly" SequelizeOptions would look something like... (This one is rather generic with default values from configuration, or code overrides)

get_dbOptions returns (JSON Object below not marked as code, because it messes up the formatting)

{ database: ormDetails?.dbName || env.DB_NAME, dialect: 'mysql', username: ormDetails?.dbUserName || env.DB_USERNAME, password: pwdSvc.getPwd(PwdNamesEnum.DBase), seederStorage: 'sequelize', // This makes a table in your database for recording which seeds have run seederStorageTableName: 'sqlzSeed', // This says what that table will be called host: ormDetails?.dbUrl || env.DB_URL, port: ormDetails?.dbPort || env.DB_PORT, pool: { max: ormDetails?.poolMax || 5, min: ormDetails?.poolMin || 0, acquire: ormDetails?.poolAquire || 30000, idle: ormDetails?.poolIdle || 10000 }, define: { freezeTableName: true, timestamps: false } };

To use it, you compile your project, then use the instructions in the docs https://sequelize.org/master/manual/migrations.html e.g. npx sequelize-cli db:migrate and this should run all the items in the folder specified above (server\db\seeders in my example) that have not already successfully run and registered in the table (SequelizeMeta - default for migrations)

similarly - npx sequelize-cli db:seed:all will run all the seed scripts (location specified above for both the source, and the table name for the registration of the successfully run scripts (sqlzSeed table)

@pj035 - Hope this is enlightening. May it be of help to others also.