A database migration tool for knex.js, which supports MySQL and SQlite3.
- JS API
- CLI Tool
- Differentiation between database initialization and migration (Support for a database schema, like we use in Ghost)
- Support for database creation
- Hooks
- Rollback to latest version
- Auto-Rollback on error
- Database health check
- Supports transactions
- Full atomic, support for separate DML/DDL scripts (no autocommit)
- Migration lock
- Full debug & pretty log support
- Custom migration folder structure
- Stable (Used in Ghost for many years in thousands of blogs in production mode)
npm install knex-migrator --save
or
yarn add knex-migrator
Add me to your globals:
npm install --global knex-migrator
- Replicas are unsupported, because Knex.js doesn't support them.
- Sqlite does not support read locks by default. Read here why.
- Comparison with other available migration tools.
- Don't mix DDL/DML statements in a migration script. In MySQL DDL statements use implicit commits.
- It's highly recommended to write both the
up
and thedown
function to ensure a full rollback. - If your process dies while migrations are running, knex-migrator won't be able to release the migration lock.
To release to lock you can run
knex-migrator rollback
. But it's recommended to check your database first to see in which state it is. You can check the tablesmigrations
andmigrations_lock
. The rollback will rollback any migrations which were executed based on your current version.
The tool requires a config file in your project root.
Please add a file named MigratorConfig.js
. Knex-migrator will load the config file.
module.exports = {
database: {
client: String (Required) ['mysql', 'mysql2', 'sqlite3']
connection: {
host: String, (Required) [e.g. '127.0.0.1']
user: String, (Required)
password: String, (Required)
charset: String, (Optional) [Default: 'utf8mb4']
database: String (Required)
}
},
migrationPath: String, (Required) [e.g. '/var/www/project/migrations']
currentVersion: String, (Required) [e.g. '2.0']
subfolder: String (Optional) [Default: 'versions']
}
Please take a look at this real example.
project/
migrations/
hooks/
init/
index.js
before.js
shutdown.js
migrate/
index.js
after.js
shutdown.js
init/
1-add-tables.js
versions/
1.0/
1-add-events-table.js
2-normalise-settings.js
2.0/
1-add-timestamps-columns.js
2.1/
1-remove-empty-strings.js
2-add-webhooks-table.js
3-add-permissions.js
Please take a look at this real example.
Knex-migrator offers a couple of hooks, which makes it possible to hook into the migration process. You can create a hook per type: 'init' or 'migrate'. The folder name must be hooks
and is not configurable. Please create an index.js file to export your functions, see example.
hook | description |
---|---|
before | is called before anything happens |
beforeEach | is called before each migration script |
after | is called after everything happened |
afterEach | is called after each migration script |
shutdown | is called before the migrator shuts down |
You can configure each migration script.
module.exports.config = {
transaction: Boolean
}
module.exports.up = function(options) {
const connection = options.connection;
...
return Promise.resolve();
};
module.exports.down = function(options) {
const connection = options.connection;
...
return Promise.resolve();
}
module.exports.config = {
transaction: true
};
module.exports.up = function(options) {
const connection = options.transacting;
...
return Promise.resolve();
};
module.exports.down = function(options) {
const connection = options.transacting;
...
return Promise.resolve();
}
$ knex-migrator help
Usage: knex-migrator [options] [command]
Options:
-v, --version output the version number
-h, --help output usage information
Commands:
init|i [config] init db
migrate|m [config] migrate db
reset|r reset db
health|h health of db
rollback|ro rollbacks your db
help [cmd] display help for [cmd]
- Returns the database health/state
- Based on your current version and your migration scripts
- Initializes your database based on your init scripts
- Creates the database if it was not created yet
# Skips a specific migration script
--skip
# Runs only a specific migration script
--only
# Path to MigratorConfig.js
--mgpath
- Migrates your database to latest version
- Automatic rollback if an error occurs
# The version you would like to migrate to
--v
# Combo Feature to check whether the database was already initialized
--init
# Force the execution no matter which current version you are on
--force
# Path to MigratorConfig.js
--mgpath
- Rolls back your database
- By default, you can only rollback if the database is locked
# Ignores the migration lock
--force
# Version you would like to rollback to
--v
- Resets your database
- Removes the database
# Ignores the migration lock
--force
DEBUG=knex-migrator:* knex-migrator migrate
const KnexMigrator = require('knex-migrator');
# Option 1: Pass path to MigratorConfig.js
const knexMigrator = new KnexMigrator({
knexMigratorFilePath: process.cwd()
});
# Option 2: Pass object with config
const knexMigrator = new KnexMigrator({
knexMigratorConfig: { ... }
});
# Health
knexMigrator.isDatabaseOK
# Initialise database
knexMigrator.init
# Migrate database
knexMigrator.migrate
# Rollback database
knexMigrator.rollback
# Reset database
knexMigrator.reset
knexMigrator.isDatabaseOK()
.then(function() {
// database is OK
// initialization & migrations are not missing
})
.catch(function(err) {
if (err.code === 'DB_NOT_INITIALISED') {
return knexMigrator.init();
}
if (err.code === 'DB_NEEDS_MIGRATION') {
return knexMigrator.migrate();
}
});
yarn lint
run just eslintyarn test
run eslint && then testsNODE_ENV=testing-mysql yarn test
to test with MySQL
yarn ship
Copyright (c) 2013-2023 Ghost Foundation - Released under the MIT license.