sheerun / knex-migrate

Modern database migration toolkit for knex.js

Modern migration toolkit for knex.js

Features

• 100% compatible with knex.js migrations cli
• can migrate upto and downto any migration
• able to run individual migrations
• quickly rollback recent migrations
• redo feature: rollback and migrate again for quick testing
• runs migrations in transactions
• friendly ui 🌹

Installation

npm install --save knex-migrate

You should also install knex as it's a peer dependency of this package.

Usage

First, init project with knex init, and then:

Usage
  $ knex-migrate <command> [options]

Commands
  generate  Generate migration
  pending   Lists all pending migrations
  list      Lists all executed migrations
  up        Performs all pending migrations
  down      Rollbacks last migration
  rollback  Rollbacks last batch of migrations
  redo      Rollbacks last batch and performs all migrations

Options for "up" and "down":
  --to, -t    Migrate upto (downto) specific version
  --from, -f  Start migration from specific version
  --only, -o  Migrate only specific version
  --step, -s  Limit the number of migrations to apply

Global options:
  --cwd         Specify the working directory
  --knexfile    Specify the knexfile path ($cwd/knexfile.js)
  --migrations  Specify migrations path ($cwd/migrations)
  --env         Specify environment ($KNEX_ENV || $NODE_ENV || 'development')
  --raw         Disable transactions
  --verbose     Be more verbose

As a convenience, you can skip --to flag, and just provide migration name.

Examples
  $ knex-migrate up                    # migrate to the latest version
  $ knex-migrate up 20160905           # migrate to a specific version
  $ knex-migrate up --to 20160905      # the same as above
  $ knex-migrate up --only 201609085   # apply a single migration
  $ knex-migrate up --step             # apply only the next migration
  $ knex-migrate up --step 2           # apply only the next two migrations
  $ knex-migrate down --to 0           # rollback all migrations
  $ knex-migrate down                  # rollback single migration
  $ knex-migrate down --step 2         # rollback the previous two migrations
  $ knex-migrate rollback              # rollback previous "up"
  $ knex-migrate redo --verbose        # rollback and migrate everything
  $ knex-migrate generate create_users # generate migration creating users table

Programmatic API

import knexMigrate from 'knex-migrate'

// It has following signature:
// knexMigrate(command: String, flags: Object, progress: Function)

async function run() {
  // Action can be: migrate, revert. Migration is migration name. For example:
  // Doing migrate on 20170427093232_add_users
  // Doing revert on 20170427093232_add_users
  const log = ({ action, migration }) =>
    console.log('Doing ' + action + ' on ' + migration)

  await knexMigrate('up', { to: '20170727093232' }, log)
  await knexMigrate('down', { step: 2 }, log)
  await knexMigrate('down', { to: 0 }, log)
  await knexMigrate('up', {}, log)
  await knexMigrate('redo', {}, log)
  await knexMigrate('rollback', {}, log)
  await knexMigrate('redo', {}, log)
  await knexMigrate('down', { to: 0 }, log)
}

run()

Thank you

• @marcbachmann for inspiration and starting point (knex-umzug and umzug-cli)
• @carlbennettnz for suggesting and implementing --step option
• @chadxz for suggesting and implementing various improvements

License

MIT

knex-migrate's Issues

Support for TypeScript supporting version of knex migrate command

Knex team has recently added TypeScript support for knex migrate command. knex-migrate will require update to support the new version of migrate command.

No knexfile at '/path/to/project/knexfile.js'
Please create one or bootstrap using 'knex init'

because of having knexfile.ts not knexfile.js

Running with `--only` should not fail if migration has already been run

When a migration has already been run, right now it bails out saying that the specified migration is not pending, and exits with an error status.

I think in a scenario where the specified migration doesn't exist, it makes sense to exit with an error status. But if the migration does exist and has already been run, I think it would make sense for the app to simply say "migration already applied" and exit with a success status. If the migration is already applied, the goal of running the command is already accomplished, so it isn't an error right?

This seems to be the logic that works for up... if you're already at the latest migration, then it simply does nothing and exits with a success status. I'm thinking this logic should be extended to running with --only as well.

LMK what you think.

Passing config object to knex-migrate('redo') is not parsed.

I'm trying to run the "redo" command programatically and am passing a config object as follows:

{ config:
   { client: 'mysql',
     connection:
      { host: 'db',
        database: 'dashboard',
        user: ...,
        password: ...,
        port: ... },
     migrations:
      { tableName: 'migrations',
        directory: '/opt/app/src/migrations' } } }

This works for up and rollback commands, but it seems like the code ignores the config "flag" here: https://github.com/sheerun/knex-migrate/blob/master/src/index.js#L294 - my config object never gets passed to up and rollback.

Support migrations.schemaName

knex-migrate seems to ignore the migrations.schemaName configuration from the knexfile, and always creates the migrations table in the default schema (e.g. public).

This makes it incompatible with the native knex migration functionality.

No knexfile at ...

The "No knexfile at" error message is confusing when the actual problem is a "Cannot find module" error occurring deeper in the recursive module resolution of your knexfile.js.

For example, create a knexfile.js and add to it require("module-that-does-not-exist")

knex-migrate will tell you that you:

No knex file at /path/to/your/project/knexfile.js

Which isn't exactly true.

Allow a pre-configured knex implementation to be passed to the API

I am trying to use knex-migrate as part of my integration tests. I will have a manually configured knex instance setup and I'd like to pass it into the knex-migrate, probably something along the lines of:

import knexMigrate from 'knex-migrate';
// init knex somewhere else
const km = knexMigrate(knex);

Is this something on your roadmap? Would a PR help?

v1.5.1 hangs after `executed` call(but works with 1.4.0)

I have following scripts at package.json

{
  "scripts": {
     "db:migrate": "knex-migrate up",
     "db:migrate:drop": "knex-migrate down --to 0",
     "db:migrate:reset": "npm run db:migrate:drop && npm run db:migrate"
  }
}

When I run npm run db:migrate:reset with knex-migrate(v1.5.1), it will hang (looks like waiting for reponse forever?) after this log (of knex with debug:true option)

{ method: 'pluck',
  options: {},
  timeout: false,
  cancelOnTimeout: false,
  bindings: [],
  __knexQueryUid: '794b75ef-f7e5-4600-ac2d-e9224b1bb30a',
  toNative: [Function: toNative],
  sql: 'select "name" from "knex_migrations" order by "id" asc',
  pluck: 'name' }

knex-migrate/src/storage.js

Line 69 in ceefeed

executed () {

But when I downgrade knex-migrate from v1.5.1 to v1.4.0, It works with same configuration(env) 🤔

Postgres logs

I'm not sure is this related...

ERROR:  relation "knex_migrations" does not exist at character 25
STATEMENT:  select count("id") from "knex_migrations"

Versions

npm packages

• [email protected]
• [email protected]

others

• npm - 4.2.0
• node - v7.10.0
• Uses docker image of postgres:10-alpine - PostgreSQL 10.1 on x86_64-pc-linux-musl, compiled by gcc (Alpine 6.3.0) 6.3.0, 64-bit

Create Knex Tables Upon the First Up Run

Hi,
Did you consider having knex-migrate up create the knex migration history tables upon the first upward run? The base tool knex migrate:latest creates the knex_migrations and knex_migrations_lock tables if they don't exist.

Currently knex-migrate throws an error if the tables are not present:

`select count(`id`) from `knex_migrations` - Table 'ip_hoarder.knex_migrations' doesn't exist`

Commands fail with "basic" configuration

knex-migrate fails to read basic configuations from the knexfile.

This works:

module.exports = {
  production: {
    client: '...',
    connection: '...'
  }
};

This doesn't:

module.exports = {
  client: '...',
  connection: '...'
};

Example:

$ knex-migrate down
Cannot read property 'client' of undefined

Basic configurations omitting specific environment blocks (development, staging, production, ...) are supported by knex itself, as shown in the documentation.

Support disabling transactions

I have to admit I haven't tried but from glancing at the code it seems like it always runs migrations in a transaction. Knex recently added support for disabling this, which in my experience is necessary for certain Postgres migrations (often around enums) http://knexjs.org/#Migrations-API-transactions

--verbose without db password

We are running in an automated environment and I would like to use knex-migrate with the verbose flag, but noticed it prints the db password. That is not so great as we also have a logging system, so anyone with access to the logging system would also get to see the db password. Should it maybe just be hidden? Or have some extra flag?

Document differences from knex's implementation

I just spent waaaaay too long discovering that knex migrate:latest runs multiple migrations in one transaction, while knex-migrate up will run each migration in a single transaction. This library also seems to ignore exports.config.transaction. Are there other small differences like this? Could we add a section to the README documenting them?

Migrations started with knex_migrate do not have '_lock' table

Using knex when a migration is performed for the first type typically we'll get two tables created automatically by knex:

migrations
migrations_lock

However when using knex-migrate, and stepping forward a specific number of 'step' only one table is created, not the 'lock' table:

migrations

Any idea why this is happening? I have knex 0.15.2 installed in my environment. I am using a postgresql database.

Typescript support

I know this is unmaintained now but figured I'd request it anyway.

#47 tried to add support for TS but I keep getting import or export TS bugs with it.

Back to manually 50 migrations. I just discovered knex migrate:rollback --all && knex migrate:latest to flush my db. 🤤

--knexfile argument doesn't work with redo

I have multiples databases in my project so i use multiples knexfile.
When using knex-migrate redo with --knexfile arg the command failed with error:

No knexfile at '/Users/marc/Projets/myproject/knexfile.js'
Please create one or bootstrap using 'knex init'

Respect `migrations.directory` in knexfile.js

I have the following in my knexfile.js:

  ...
  migrations: {
    directory: 'src/db/migrations'
  }

but knex-migrate does not appear to use the config. Instead, I have to specify it again using --migrations flag.

Use typescript (*.ts) file for migrations file

knex-migrate force the migrations files pattern to be *.js

Actually, i need to patch knex-migrate index.js to change the pattern to use .ts file

SyntaxError: missing ) after argument list

Getting this error on Windows:

E:\Repositories\quandoo-api\node_modules\.bin\knex-migrate:2
basedir=$(dirname "$(echo "$0" | sed -e 's,\\,/,g')")
          ^^^^^^^

SyntaxError: missing ) after argument list
    at new Script (vm.js:79:7)
    at createScript (vm.js:251:10)
    at Object.runInThisContext (vm.js:303:10)
    at Module._compile (internal/modules/cjs/loader.js:656:28)
    at Object.Module._extensions..js (internal/modules/cjs/loader.js:699:10)
    at Module.load (internal/modules/cjs/loader.js:598:32)
    at tryModuleLoad (internal/modules/cjs/loader.js:537:12)
    at Function.Module._load (internal/modules/cjs/loader.js:529:3)
    at Function.Module.runMain (internal/modules/cjs/loader.js:741:12)
    at startup (internal/bootstrap/node.js:285:19)
npm ERR! code ELIFECYCLE
npm ERR! errno 1
npm ERR! [email protected] ts-node: `node -r ./node_modules/ts-node/register -r ./src/env "./node_modules/.bin/knex-migrate" "up"`
npm ERR! Exit status 1
npm ERR!
npm ERR! Failed at the [email protected] ts-node script.
npm ERR! This is probably not a problem with npm. There is likely additional logging output above.

Provide a way to undo a migration if the migration file can't be found

Since we started using knex migrations, me and my team all been repeating this – horribly inefficient – workflow:

1. Switch from git branch A to B.
2. Try to start the app. knex-migrate up is run as an npm prestart script.
3. knex-migrate fails because a migration from branch A can't be found. 😠
4. Switch back to A.
5. knex-migrate down a few times until all of A's migrations have been undone. This often requires looking through git history to figure out which migrations were added before or after B forked.
6. Switch back to B.
7. npm start successfully.

It's a bit of a pain.

I can only think of two potential solutions here:

Git hooks

Some fancy git hook scripts that apply and reverse migrations as you jump from branch to branch. I don't even know if this is possible, but even if it is, this would only solve the problem for my team, not anyone else.

Storing JavaScript in the database 🙈

Store the migration file in the knex_migrations table, then have knex-migrate down eval it to undo each migration. You could even add a command that applies down until it reaches a migration that can be found in the migrations folder, then applies newer migrations. If we ran that command as an npm prestart script, the workflow problem above would go away 😃

One potential drawback is that bugs in your down() become a bit trickier to deal with. This could be mitigated by defaulting to the real file, only using the DB version if it's not found. An error in a down() from the database could notify the user that they should checkout the real file first. We could potentially record the git branch and commit they had checked out when they initially applied the migration too.

This also makes the entire concept of migrations a bit harder to understand. Would it be worth it? I don't know. But that's why this is a long issue description, not a PR 😄 What do you think?

ES6 support (arrow function)

It's really common to use arrow functions today for the node.js support for that.

Instead of function use () => {}

https://github.com/sheerun/knex-migrate/blob/master/src/stub/js.stub#L1

QUESTION: There is support for generate automatic migration files?

Does this tool support automatic generation of migration files bases on schema or database connection?

Wrong Order of Roll-back Migrations

If a set of migrations is executed within the same second, their roll-back may be executed in a wrong order.

I have these migrations in my MySql:

id	name	batch	migration_time
39	20170406171908_mysql_create_table_status_type.js	1	2017-04-26 21:48:34
40	20170406172149_mysql_init_status_type	1	2017-04-26 21:48:34

In my case, the rollback 1st deletes the table, than tries to de-initialize contents of the deleted table, which results in an error.

knex-migrate down --to 0 grabs all the migrations, then orders them by the migration_date. It should order the migrations by their identifiers. Here is the culprit:
https://github.com/sheerun/knex-migrate/blob/master/src/storage.js#L59

knex-migrate: command not found.

running knex: 0.14.6 and knex-migrate: 1.6.1

and I get the following error:
knex-migrate: command not found.

Use knex defined stub if it exists

Knex allows you define a migration stub in the knex config. At the moment this is ignored by knex-migrate which means that setting up a stub in knex migrations won't affect any newly generated migrations when using knex-migrate.

It would be good if it could use the migration stubs in the following order:

1. Stub passed by flag
2. Stub defined in knex config
3. Default stub

Add option to move up or down a set number of migrations

When testing and debugging migrations, I often want to apply migrations one-at-a-time, checking the changes made by each one. Currently, this requires finding and typing out the timestamp for each migration.

An option like this would simplify this workflow:

$ knex-migrate up --number 1
$ knex-migrate down -n 1

Alternatively, to support just the more common use case of a single migration at a time:

$ knex-migrate up --one
$ knex-migrate down -1

Happy to send in a PR if you think this would be useful.

Umzug Error: "Migration is not pending"

Is there documentation either here or in the umzug project which explains what this error means? I had assumed this library would let me specify an arbitrary migration to run up to but that does not appear to be the case, at least not out of the box with a non-standard location for the knexfile.

js.stub file not included in release

Steps to reproduce:

$ npm i knex knex-migrate sqlite3
$ node ./node_modules/.bin/knex init
$ mkdir migrations
$ node ./node_modules/.bin/knex-migrate generate foo
ENOENT: no such file or directory, open '/Users/xxx/node_modules/knex-migrate/stub/js.stub'

The stub directory is missing from the node_modules/knex-migrate:

$ ls -d ./node_modules/knex-migrate/*/
./node_modules/knex-migrate/lib/ ./node_modules/knex-migrate/src/

Version information:

$ cat ./node_modules/knex-migrate/package.json | grep version
  "version": "1.5.0"
$ node -v
v8.9.3
$ npm -v
5.5.1

Add knex_migrate table check for Sql Server

Here: https://github.com/sheerun/knex-migrate/blob/master/src/storage.js#L8
Add this check: new RegExp(Invalid object name '${table}').test(err.message)

I would create a PR but am currently contractually blocked from non-approved open source contributions.

Thanks!

knexfile location

This library is super-useful, but I use a custom knexfile location in my projects. Can you add that as an option to the CLI?

e.g., knex-migrate list --knexfile ./src/orm/knexfile.js

--only now requires full migration name

I recently upgraded from 1.5.1 to 1.5.4 and noticed that a command that i used to run, namely

knex-migrate up --only 20180315084901

Didn't run only that migration, but instead, ran all migrations. Upon investigating, I found that the following command does work:

knex-migrate up --only 20180315084901_create_rj_object

This was the full name of the migration, minus the .js extension.

I haven't investigated, but I'd be willing to bet that the umzug upgrade from ^1.11 to ^2.1 is the likely culprit

anyway, might be worth possibly releasing a new major version and deprecating 1.5.2, 1.5.3, and 1.5.4

using a pre-configured knex object

I'm trying to use the programatic API and pass a knex object in.

Based on your last comment in #23 this capability is implemented but I don't see any reference to it in the latest README? Please advise.

If it is implemented, can other Global options with it:

  --cwd         Specify the working directory
  --migrations  Specify migrations path ($cwd/migrations)

Migration file pattern dosen't match

When I use generate command, Some filename doesn't match.

example

1. use knex-migrate generate [hoge]
2. YYYYMMDDhhmmss_[hoge].js is created successflly.
3. use knex-migrate up
4. But YYYYMMDDhhmmss_[hoge].js doesn't recognize.

reason

https://github.com/sheerun/knex-migrate/blob/master/src/index.js#L115

This regexp accepts only [\w-_]+. So, some filename doesn't match this regexp; e.g. [ and ]

knex.js migrations cli supports any filename, so, I want knex-migrate supports it too.

`pending` and `list` should not fail when no migrations table exists

Currently, if you run knex-migrate up and the corresponding migrations table doesn't exist, it creates the table and performs the migrations.

However, if you run knex-migrate pending or knex-migrate list with no migrations table, it throws and error stating that the table doesn't exist and exits with an error status.

Ideally, the following would happen when the migrations table doesn't exist:

• knex-migrate pending would return no output and exit with a success status
• knex-migrate list would return all migrations and exit with a success status

P.S. Thanks for this project it's really a step up from the built-in commands in knex. 🍺

Which command create migrations table?

I installed knex-migrate
Then run knex-migrate generate create_boo
Then run knex-migrate up, but see error table migrations not exists...
Thanks!