MySQL migration tool for typescript projects. Supports mysqljs and node-mysql2 driver.
npm i ts-mysql-migrate
Now it is possible to generate migration scripts with timestamps in order not to create problems when merging from different branches. That was an annoying issue in larger teams working on same project.
You can control behavior of strict order with environment variable MIGRATIONS_STRICT_ORDER
. See environment variables for details.
This project utilizes environment variables to configure various aspects of the migration process. You can define these variables in a .env
file at the root of your project. To use .env
files, ensure the dotenv
package is installed, as it is required for loading these variables into your environment.
Here are some key environment variables used in this project:
-
MIGRATIONS_STRICT_ORDER
: Ensures migration scripts follow a strict index/timeline order. Possible to override with passingstrictOrder
parameter to constructor. Default istrue
in production (NODE_ENV='production'
), otherwisefalse
. -
MIGRATIONS_NUMERIC_ORDER
: Enforces strict number order for migration scripts ordered by numbers. Should be disabled if scripts are prefixed with timestamps. Possible to override with passingnumOrder
parameter to constructor. Set tofalse
by default. -
MIGRATION_FOLDER
: Specifies the default path to the migrations folder for the CLI script generator. -
Database Configuration for running tests:
-
DB_HOST
: Database host -
DB_PORT
: Database port. -
DB_USER
: Database user. -
DB_PASSWORD
: Database password. -
DB_DATABASE
: Database name.
-
Ensures database connectivity and creates migration table on database, if not yet present. Must be awaited before running migration scripts.
Upgrades database by running upgrade functions in migration scripts. Parameter step
defines number of versions to upgrade which is number of migration scripts to execute. If omitted, all not-yet-deployed upgrade functions will execute - database will upgrade to last version.
Runs downgrade functions in migration scripts. Parameter step
defines how many versions should database downgrade. If omitted, one downgrade functions will be executed - database will downgrade one version.
Runs all downgrade and upgrade migrations. Depending od your upgrade and downgrade scripts, this could be used to clean database of all data.
Closes database connection and releases handles.
Create migration scripts and name them with number prefix to set the order of execution (versions). In v2 you can also generate it with CLI to have a timestamp as identifier.
Each migration script should have upgrade
and downgrade
functions exported. These functions must have queryFn
as parameter - see examples below.
Example: /src/migrations/1-init_db.ts
export async function upgrade(queryFn: (query: string, values?: any[]) => Promise<Array<any>>) {
// write upgrade logic as parameter of queryFn
await queryFn(`
CREATE TABLE TEST (ID INTEGER NULL, NAME VARCHAR(20) NULL);
`);
}
export async function downgrade(queryFn: (query: string, values?: any[]) => Promise<Array<any>>) {
// write downgrade logic as parameter of queryFn
await queryFn(`
DROP TABLE TEST;
`);
}
Example: /src/migrations/2-seed_data.ts
export async function upgrade(queryFn: (query: string, values?: any[]) => Promise<Array<any>>) {
// write upgrade logic as parameters of queryFn
await queryFn(`
INSERT INTO TEST VALUES (?, ?);
`, [1, 'Test']);
}
export async function downgrade(queryFn: (query: string, values?: any[]) => Promise<Array<any>>) {
// write downgrade logic as parameters of queryFn
await queryFn(`
DELETE FROM TEST WHERE ID = ?;
`, [1]);
}
Create scripts that will run your upgrade and/or downgrade migrations. You can write multiple scripts for different scenarios/environments (dev, staging, production).
Example: /src/scripts/upgrade.ts
(or /src/scripts/downgrade.ts
)
import { Migration, MigrationConnection } from 'ts-mysql-migrate';
const run = async () => {
/**** mysqljs example ****/
/*
const poolConfig: PoolConfig = {
host: process.env.DB_HOST,
port: parseInt(process.env.DB_PORT),
user: process.env.DB_USER,
password: process.env.DB_PASSWORD,
database: process.env.DB_DATABASE,
};
const pool = createPool(poolConfig);
migration = new Migration({
conn: pool,
tableName: 'migrations',
dir: `./src/migrations/`
});
*/
/**** node-mysql2 example ****/
const poolConfig: ConnectionOptions = {
host: process.env.DB_HOST,
port: parseInt(process.env.DB_PORT),
user: process.env.DB_USER,
password: process.env.DB_PASSWORD,
database: process.env.DB_DATABASE,
};
const pool = createPool(poolConfig); // make sure that you are not using mysl2/promise lib
const migration = new Migration({
conn: pool as unknown as mysql.Connection,
tableName: 'migrations',
dir: `./src/migrations/`
});
await migration.initialize();
await migration.up(); // use for upgrade script
// await migration.down(); // use for downgrade script
// await migration.reset(); // use for resetting database
await migration.destroy(); // close db connection
};
run().catch((err) => {
console.log(err);
});
All you have to do now is to run your starter scripts each time you want to upgrade or downgrade a database.
You can put them in package.json
and run it from npm. Example:
package.json
"scripts": {
"upgrade": "node -r ts-node/register ./src/scripts/upgrade-stg.ts",
"downgrade": "node -r ts-node/register ./src/scripts/downgrade-stg.ts",
"upgrade-production": "node -r ts-node/register ./src/scripts/upgrade-prod.ts",
"downgrade-production": "node -r ts-node/register ./src/scripts/downgrade-prod.ts",
},
New in v1.1.x
You can now save script names with any number as a prefix - this is meant to be used with unix timestamp. To generate script with current timestamp, you can run CLI command from local installation by running
npx generate-migration
Alternatively you can also install package globally
npm i ts-mysql-migrate -g
Then you can call the command in the root of your project.
generate-migration
You can set the env variable MIGRATION_FOLDER
to customize default or even set script in your package.json
for example:
{
"scripts":{
"new-migration": "cross-env MIGRATION_FOLDER=my-migration-folder generate-migration"
}
}
and then just call npm run new-migration
when you'd like to create new migration.
Please test your upgrade and downgrade scripts well before use it on production database!
Please also note the following paragraph from License:
THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.