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

[RobinBuschmann]

As mentioned here #274 and due to sequelize/cli#441 (comment) 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 ab0b572 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.