sequelize/umzug

Framework agnostic migration tool for Node.js

Umzug

Umzug is a framework-agnostic migration tool for Node. It provides a clean API for running and rolling back tasks.

Highlights

• Written in TypeScript
  • Built-in typings
  • Auto-completion right in your IDE
  • Documentation right in your IDE
• Programmatic API for migrations
• Built-in CLI
• Database agnostic
• Supports logging of migration process
• Supports multiple storages for migration data
• Usage examples

Documentation

Note: these are the docs for the latest version of umzug, which has several breaking changes from v2.x. See the upgrading section for a migration guide. For the previous stable version, please refer to the v2.x branch.

Minimal Example

The following example uses a Sqlite database through sequelize and persists the migration data in the database itself through the sequelize storage. There are several more involved examples covering a few different scenarios in the examples folder. Note that although this uses Sequelize, Umzug isn't coupled to Sequelize, it's just one of the (most commonly-used) supported storages.

// index.js
const {Sequelize} = require('sequelize')
const {Umzug, SequelizeStorage} = require('umzug')

const sequelize = new Sequelize({dialect: 'sqlite', storage: './db.sqlite'})

const umzug = new Umzug({
  migrations: {glob: 'migrations/*.js'},
  context: sequelize.getQueryInterface(),
  storage: new SequelizeStorage({sequelize}),
  logger: console,
})

// Checks migrations and run them if they are not already applied. To keep
// track of the executed migrations, a table (and sequelize model) called SequelizeMeta
// will be automatically created (if it doesn't exist already) and parsed.
await umzug.up()

// migrations/00_initial.js

const {Sequelize} = require('sequelize')

async function up({context: queryInterface}) {
  await queryInterface.createTable('users', {
    id: {
      type: Sequelize.INTEGER,
      allowNull: false,
      primaryKey: true,
    },
    name: {
      type: Sequelize.STRING,
      allowNull: false,
    },
    createdAt: {
      type: Sequelize.DATE,
      allowNull: false,
    },
    updatedAt: {
      type: Sequelize.DATE,
      allowNull: false,
    },
  })
}

async function down({context: queryInterface}) {
  await queryInterface.dropTable('users')
}

module.exports = {up, down}

Note that we renamed the context argument to queryInterface for clarity. The context is whatever we specified when creating the Umzug instance in index.js.

You can also write your migrations in typescript by using `ts-node` in the entrypoint:

// index.ts
require('ts-node/register')

import { Sequelize } from 'sequelize';
import { Umzug, SequelizeStorage } from 'umzug';

const sequelize = new Sequelize({ dialect: 'sqlite', storage: './db.sqlite' });

const umzug = new Umzug({
  migrations: { glob: 'migrations/*.ts' },
  context: sequelize.getQueryInterface(),
  storage: new SequelizeStorage({ sequelize }),
  logger: console,
});

// export the type helper exposed by umzug, which will have the `context` argument typed correctly
export type Migration = typeof umzug._types.migration;

(async () => {
  await umzug.up();
})();

// migrations/00_initial.ts
import type { Migration } from '..';

// types will now be available for `queryInterface`
export const up: Migration = ({ context: queryInterface }) => queryInterface.createTable(...)
export const down: Migration = ({ context: queryInterface }) => queryInterface.dropTable(...)

See these tests for more examples of Umzug usage, including:

• passing ignore and cwd parameters to the glob instructions
• customising migrations ordering
• finding migrations from multiple different directories
• using non-js file extensions via a custom resolver, e.g. .sql

Usage

Installation

Umzug is available on npm by specifying the correct tag:

npm install umzug

Umzug instance

It is possible to configure an Umzug instance by passing an object to the constructor.

const {Umzug} = require('umzug')

const umzug = new Umzug({
  /* ... options ... */
})

Detailed documentation for these options are in the UmzugOptions TypeScript interface, which can be found in src/types.ts.

Getting all pending migrations

You can get a list of pending (i.e. not yet executed) migrations with the pending() method:

const migrations = await umzug.pending()
// returns an array of all pending migrations.

Getting all executed migrations

You can get a list of already executed migrations with the executed() method:

const migrations = await umzug.executed()
// returns an array of all already executed migrations

Executing pending migrations

The up method can be used to execute all pending migrations.

const migrations = await umzug.up()
// returns an array of all executed migrations

It is also possible to pass the name of a migration in order to just run the migrations from the current state to the passed migration name (inclusive).

await umzug.up({to: '20141101203500-task'})

To limit the number of migrations that are run, step can be used:

// This will run the next two migrations
await umzug.up({step: 2})

Running specific migrations while ignoring the right order, can be done like this:

await umzug.up({migrations: ['20141101203500-task', '20141101203501-task-2']})

Reverting executed migration

The down method can be used to revert the last executed migration.

const migration = await umzug.down()
// reverts the last migration and returns it.

To revert more than one migration, you can use step:

// This will revert the last two migrations
await umzug.down({step: 2})

It is possible to pass the name of a migration until which (inclusive) the migrations should be reverted. This allows the reverting of multiple migrations at once.

const migrations = await umzug.down({to: '20141031080000-task'})
// returns an array of all reverted migrations.

To revert all migrations, you can pass 0 as the to parameter:

await umzug.down({to: 0})

Reverting specific migrations while ignoring the right order, can be done like this:

await umzug.down({migrations: ['20141101203500-task', '20141101203501-task-2']})

Migrations

There are two ways to specify migrations: via files or directly via an array of migrations.

Migration files

A migration file ideally exposes an up and a down async functions. They will perform the task of upgrading or downgrading the database.

module.exports = {
  async up() {
    /* ... */
  },
  async down() {
    /* ... */
  },
}

Migration files can be located anywhere - they will typically be loaded according to a glob pattern provided to the Umzug constructor.

Direct migrations list

You can also specify directly a list of migrations to the Umzug constructor:

const {Umzug} = require('umzug')

const umzug = new Umzug({
  migrations: [
    {
      // the name of the migration is mandatory
      name: '00-first-migration',
      async up({context}) {
        /* ... */
      },
      async down({context}) {
        /* ... */
      },
    },
    {
      name: '01-foo-bar-migration',
      async up({context}) {
        /* ... */
      },
      async down({context}) {
        /* ... */
      },
    },
  ],
  context: sequelize.getQueryInterface(),
  logger: console,
})

Modifying the parameters passed to your migration methods

Sometimes it's necessary to modify the parameters umzug will pass to your migration methods when the library calls the up and down methods for each migration. This is the case when using migrations currently generated using sequelize-cli. In this case you can use the resolve fuction during migration configuration to determine which parameters will be passed to the relevant method

import {Sequelize} from 'sequelize'
import {Umzug, SequelizeStorage} from 'umzug'

const sequelize = new Sequelize(/* ... */)

const umzug = new Umzug({
  migrations: {
    glob: 'migrations/*.js',
    resolve: ({name, path, context}) => {
      const migration = require(path)
      return {
        // adjust the parameters Umzug will
        // pass to migration methods when called
        name,
        up: async () => migration.up(context, Sequelize),
        down: async () => migration.down(context, Sequelize),
      }
    },
  },
  context: sequelize.getQueryInterface(),
  storage: new SequelizeStorage({sequelize}),
  logger: console,
})

Additional migration configuration options

To load migrations in another format, you can use the resolve function:

const fs = require('fs')
const {Sequelize} = require('sequelize')
const {Umzug} = require('umzug')

const umzug = new Umzug({
  migrations: {
    glob: 'migrations/*.up.sql',
    resolve: ({name, path, context: sequelize}) => ({
      name,
      up: async () => {
        const sql = fs.readFileSync(path).toString()
        return sequelize.query(sql)
      },
      down: async () => {
        // Get the corresponding `.down.sql` file to undo this migration
        const sql = fs
          .readFileSync(path.replace('.up.sql', '.down.sql'))
          .toString()
        return sequelize.query(sql)
      },
    }),
  },
  context: new Sequelize(/* ... */),
  logger: console,
})

You can support mixed migration file types, and use umzug's default resolver for javascript/typescript:

const fs = require('fs')
const {Sequelize} = require('sequelize')
const {Umzug} = require('umzug')

const umzug = new Umzug({
  migrations: {
    glob: 'migrations/*.{js,ts,up.sql}',
    resolve: params => {
      if (!params.path.endsWith('.sql')) {
        return Umzug.defaultResolver(params)
      }
      const {context: sequelize} = params
      return {
        name: params.name,
        up: async () => {
          const sql = fs.readFileSync(params.path).toString()
          return sequelize.query(sql)
        },
        down: async () => {
          // Get the corresponding `.down.sql` file to undo this migration
          const sql = fs
            .readFileSync(params.path.replace('.up.sql', '.down.sql'))
            .toString()
          return sequelize.query(sql)
        },
      }
    },
  },
  logger: console,
  context: new Sequelize(/* ... */),
})

The glob syntax allows loading migrations from multiple locations:

const {Sequelize} = require('sequelize')
const {Umzug} = require('umzug')

const umzug = new Umzug({
  migrations: {
    glob: '{first-folder/*.js,second-folder-with-different-naming-convention/*.js}',
  },
  context: new Sequelize(/* ... */),
  logger: console,
})

Note on migration file sorting:

• file matches, found using fast-glob, will be lexicographically sorted based on their paths
  • so if your migrations are one/m1.js, two/m2.js, three/m3.js, the resultant order will be one/m1.js, three/m3.js, two/m2.js
  • similarly, if your migrations are called m1.js, m2.js, ... m10.js, m11.js, the resultant ordering will be m1.js, m10.js, m11.js, ... m2.js
• The easiest way to deal with this is to ensure your migrations appear in a single folder, and their paths match lexicographically with the order they should run in
• If this isn't possible, the ordering can be customised using a new instance (previously, in the beta release for v3, this could be done with .extend(...) - see below for example using a new instance)

Upgrading from v2.x

The Umzug class should be imported as a named import, i.e. import { Umzug } from 'umzug'.

The MigrationMeta type, which is returned by umzug.executed() and umzug.pending(), no longer has a file property - it has a name and optional path - since migrations are not necessarily bound to files on the file system.

The migrations.glob parameter replaces path, pattern and traverseDirectories. It can be used, in combination with cwd and ignore to do much more flexible file lookups. See https://npmjs.com/package/fast-glob for more information on the syntax.

The migrations.resolve parameter replaces customResolver. Explicit support for wrap and nameFormatter has been removed - these can be easily implemented in a resolve function.

The constructor option logging is replaced by logger to allow for warn and error messages in future. NodeJS's global console object can be passed to this. To disable logging, replace logging: false with logger: undefined.

Events have moved from the default nodejs EventEmitter to emittery. It has better design for async code, a less bloated API surface and strong types. But, it doesn't allow passing multiple arguments to callbacks, so listeners have to change slightly, as well as .addListener(...) and .removeListener(...) no longer being supported (.on(...) and .off(...) should now be used):

Before:

umzug.on('migrating', (name, m) => console.log({name, path: m.path}))

After:

umzug.on('migrating', ev => console.log({name: ev.name, path: ev.path}))

The Umzug#execute method is removed. Use Umzug#up or Umzug#down.

The options for Umguz#up and Umzug#down have changed:

• umzug.up({ to: 'some-name' }) and umzug.down({ to: 'some-name' }) are still valid.
• umzug.up({ from: '...' }) and umzug.down({ from: '...' }) are no longer supported. To run migrations out-of-order (which is not generally recommended), you can explicitly use umzug.up({ migrations: ['...'] }) and umzug.down({ migrations: ['...'] }).
• name matches must be exact. umzug.up({ to: 'some-n' }) will no longer match a migration called some-name.
• umzug.down({ to: 0 }) is still valid but umzug.up({ to: 0 }) is not.
• umzug.up({ migrations: ['m1', 'm2'] }) is still valid but the shorthand umzug.up(['m1', 'm2']) has been removed.
• umzug.down({ migrations: ['m1', 'm2'] }) is still valid but the shorthand umzug.down(['m1', 'm2']) has been removed.
• umzug.up({ migrations: ['m1', 'already-run'] }) will throw an error, if already-run is not found in the list of pending migrations.
• umzug.down({ migrations: ['m1', 'has-not-been-run'] }) will throw an error, if has-not-been-run is not found in the list of executed migrations.
• umzug.up({ migrations: ['m1', 'm2'], rerun: 'ALLOW' }) will re-apply migrations m1 and m2 even if they've already been run.
• umzug.up({ migrations: ['m1', 'm2'], rerun: 'SKIP' }) will skip migrations m1 and m2 if they've already been run.
• umzug.down({ migrations: ['m1', 'm2'], rerun: 'ALLOW' }) will "revert" migrations m1 and m2 even if they've never been run.
• umzug.down({ migrations: ['m1', 'm2'], rerun: 'SKIP' }) will skip reverting migrations m1 and m2 if they haven't been run or are already reverted.
• umzug.up({ migrations: ['m1', 'does-not-exist', 'm2'] }) will throw an error if the migration name is not found. Note that the error will be thrown and no migrations run unless all migration names are found - whether or not rerun: 'ALLOW' is added.

The context parameter replaces params, and is passed in as a property to migration functions as an options object, alongs side name and path. This means the signature for migrations, which in v2 was (context) => Promise<void>, has changed slightly in v3, to ({ name, path, context }) => Promise<void>.

Handling existing v2-format migrations

The resolve function can also be used to upgrade your umzug version to v3 when you have existing v2-compatible migrations:

const {Umzug} = require('umzug')

const umzug = new Umzug({
  migrations: {
    glob: 'migrations/umzug-v2-format/*.js',
    resolve: ({name, path, context}) => {
      // Adjust the migration from the new signature to the v2 signature, making easier to upgrade to v3
      const migration = require(path)
      return {
        name,
        up: async () => migration.up(context),
        down: async () => migration.down(context),
      }
    },
  },
  context: sequelize.getQueryInterface(),
  logger: console,
})

Similarly, you no longer need migrationSorting, you can instantiate a new Umzug instance to manipulate migration lists directly:

const {Umzug} = require('umzug')

const parent = new Umzug({
  migrations: {glob: 'migrations/**/*.js'},
  context: sequelize.getQueryInterface(),
})

const umzug = new Umzug({
  ...parent.options,
  migrations: async ctx =>
    (await parent.migrations()).sort((a, b) => b.path.localeCompare(a.path)),
})

Storages

Storages define where the migration data is stored.

JSON Storage

Using JSONStorage will create a JSON file which will contain an array with all the executed migrations. You can specify the path to the file. The default for that is umzug.json in the working directory of the process.

Detailed documentation for the options it can take are in the JSONStorageConstructorOptions TypeScript interface, which can be found in src/storage/json.ts.

Memory Storage

Using memoryStorage will store migrations with an in-memory array. This can be useful for proof-of-concepts or tests, since it doesn't interact with databases or filesystems.

It doesn't take any options, just import the memoryStorage function and call it to return a storage instance:

import { Umzug, memoryStorage } from 'umzug'

const umzug = new Umzug({
  migrations: ...,
  storage: memoryStorage(),
  logger: console,
})

Sequelize Storage

Using SequelizeStorage will create a table in your SQL database called SequelizeMeta containing an entry for each executed migration. You will have to pass a configured instance of Sequelize or an existing Sequelize model. Optionally you can specify the model name, table name, or column name. All major Sequelize versions are supported.

Detailed documentation for the options it can take are in the _SequelizeStorageConstructorOptions TypeScript interface, which can be found in src/storage/sequelize.ts.

This library has been tested with sequelize v6. It may or may not work with lower versions - use at your own risk.

MongoDB Storage

Using MongoDBStorage will create a collection in your MongoDB database called migrations containing an entry for each executed migration. You will have either to pass a MongoDB Driver Collection as collection property. Alternatively you can pass a established MongoDB Driver connection and a collection name.

Detailed documentation for the options it can take are in the MongoDBStorageConstructorOptions TypeScript interface, which can be found in src/storage/mongodb.ts.

Custom

In order to use a custom storage, you can pass your storage instance to Umzug constructor.

class CustomStorage {
  constructor() {}
  logMigration() {}
  unlogMigration() {}
  executed() {}
}

const umzug = new Umzug({
  storage: new CustomStorage(/* ... */),
  logger: console,
})

Your instance must adhere to the UmzugStorage interface. If you're using TypeScript you can ensure this at compile time, and get IDE type hints by importing it:

import { UmzugStorage } from 'umzug'

class CustomStorage implements UmzugStorage {
  /* ... */
}

Events

Umzug is an emittery event emitter. Each of the following events will be called with migration parameters as its payload (with context, name, and nullable path properties). Events are a convenient place to implement application-specific logic that must run around each migration:

• migrating - A migration is about to be executed.
• migrated - A migration has successfully been executed.
• reverting - A migration is about to be reverted.
• reverted - A migration has successfully been reverted.

These events run at the beginning and end of up and down calls. They'll receive an object containing a context property:

• beforeCommand - Before any command ('up' | 'down' | 'executed' | 'pending') is run.
• afterCommand - After any command ('up' | 'down' | 'executed' | 'pending') is run. Note: this will always run, even if the command throws an error.

The FileLocker class uses beforeAll and afterAll to implement a simple filesystem-based locking mechanism.

All events are type-safe, so IDEs will prevent typos and supply strong types for the event payloads.

Errors

When a migration throws an error, it will be wrapped in a MigrationError which captures the migration metadata (name, path etc.) as well as the original error message, and will be rethrown. In most cases, this is expected behaviour, and doesn't require any special handling beyond standard error logging setups.

If you expect failures and want to try to recover from them, you will need to try-catch the call to umzug.up(). You can access the original error from the .cause property if necessary:

try {
  await umzug.up()
} catch (e) {
  if (e instanceof MigrationError) {
    const original = e.cause
    // do something with the original error here
  }
  throw e
}

Under the hood, verror is used to wrap errors.

CLI

🚧🚧🚧 The CLI is new to Umzug v3. Feedback on it is welcome in discussions 🚧🚧🚧

Umzug instances provide a .runAsCLI() method. When called, this method will automatically cause your program to become a complete CLI, with help text and such:

// migrator.js
const {Umzug} = require('umzug')

const umzug = new Umzug({
  /* ... */
})

exports.umzug = umzug

if (require.main === module) {
  umzug.runAsCLI()
}

Note that this uses the @rushstack/ts-command-line package, which shows only the top-level message of any errors throw by default. See here for how you can see a full stack trace.

CLI Usage

A script like the one above is now a runnable CLI program. You can run node migrator.js --help to see how to use it. It will print something like:

usage: <script> [-h] <command> ...

Umzug migrator

Positional arguments:
  <command>
    up        Applies pending migrations
    down      Revert migrations
    pending   Lists pending migrations
    executed  Lists executed migrations
    create    Create a migration file

Optional arguments:
  -h, --help  Show this help message and exit.

For detailed help about a specific command, use: <script> <command> -h

Running migrations

node migrator up and node migrator down apply and revert migrations respectively. They're the equivalent of the .up() and .down() methods.

Use node migrator up --help and node migrator down --help for options (running "to" a specific migration, passing migration names to be run explicitly, and specifying the rerun behavior):

Up:

usage: <script> up [-h] [--to NAME] [--step COUNT] [--name MIGRATION]
                   [--rerun {THROW,SKIP,ALLOW}]
                   

Performs all migrations. See --help for more options

Optional arguments:
  -h, --help            Show this help message and exit.
  --to NAME             All migrations up to and including this one should be 
                        applied
  --step COUNT          Apply this many migrations. If not specified, all 
                        will be applied.
  --name MIGRATION      Explicity declare migration name(s) to be applied. 
                        Only these migrations will be applied.
  --rerun {THROW,SKIP,ALLOW}
                        Specify what action should be taken when a migration 
                        that has already been applied is passed to --name. 
                        The default value is "THROW".

Down:

usage: <script> down [-h] [--to NAME] [--step COUNT] [--name MIGRATION]
                     [--rerun {THROW,SKIP,ALLOW}]
                     

Undoes previously-applied migrations. By default, undoes the most recent 
migration only. Use --help for more options. Useful in development to start 
from a clean slate. Use with care in production!

Optional arguments:
  -h, --help            Show this help message and exit.
  --to NAME             All migrations up to and including this one should be 
                        reverted. Pass '0' to revert all.
  --step COUNT          Revert this many migrations. If not specified, only 
                        the most recent migration will be reverted.
  --name MIGRATION      Explicity declare migration name(s) to be reverted. 
                        Only these migrations will be reverted.
  --rerun {THROW,SKIP,ALLOW}
                        Specify what action should be taken when a migration 
                        that has already been applied is passed to --name. 
                        The default value is "THROW".

Listing migrations

node migrator pending # list migrations yet to be run
node migrator executed # list migrations that have already run

node migrator pending --json # list pending migrations including names and paths, in a json array format
node migrator executed --json # list executed migrations including names and paths, in a json array format

node migrator pending --help # show help/options
node migrator executed --help # show help/options

usage: <script> pending [-h] [--json]

Prints migrations returned by `umzug.pending()`. By default, prints migration 
names one per line.

Optional arguments:
  -h, --help  Show this help message and exit.
  --json      Print pending migrations in a json format including names and 
              paths. This allows piping output to tools like jq. Without this 
              flag, the migration names will be printed one per line.

usage: <script> executed [-h] [--json]

Prints migrations returned by `umzug.executed()`. By default, prints 
migration names one per line.

Optional arguments:
  -h, --help  Show this help message and exit.
  --json      Print executed migrations in a json format including names and 
              paths. This allows piping output to tools like jq. Without this 
              flag, the migration names will be printed one per line.

Creating migrations - CLI

Usually, migrations correspond to files on the filesystem. The CLI exposes a way to create migration files easily:

node migrator create --name my-migration.js

This will create a file with a name like 2000.12.25T12.34.56.my-migration.js in the same directory as the most recent migration file. If it's the very first migration file, you need to specify the folder explicitly:

node migrator create --name my-migration.js --folder path/to/directory

The timestamp prefix can be customized to be date-only or omitted, but be aware that it's strongly recommended to ensure your migrations are lexicographically sortable so it's easy for humans and tools to determine what order they should run in - so the default prefix is recommended.

This will generate a migration file called <<timestamp>>.my-migration.js with the default migration template for .js files that ships with Umzug.

Umzug also ships with default templates for .ts, .cjs, .mjs and .sql files. Umzug will choose the template based on the extension you provide in name.

You can specify a custom template for your project when constructing an umzug instance via the template option. It should be a function which receives a filepath string, and returns an array of [filepath, content] pairs. Usually, just one pair is needed, but a second could be used to include a "down" migration in a separate file:

const umzug = new Umzug({
  migrations: {
    /*...*/
  },
  create: {
    template: filepath => [
      [filepath, fs.readFileSync('path/to/your/template/file').toString()],
    ],
  },
})

The create command includes some safety checks to make sure migrations aren't created with ambiguous ordering, and that they will be picked up by umzug when applying migrations. The first pair is expected to be the "up" migration file, and to be picked up by the pending command.

Use node migrator create --help for more options:

usage: <script> create [-h] --name NAME [--prefix {TIMESTAMP,DATE,NONE}]
                       [--folder PATH] [--allow-extension EXTENSION]
                       [--skip-verify] [--allow-confusing-ordering]
                       

Generates a placeholder migration file using a timestamp as a prefix. By 
default, mimics the last existing migration, or guesses where to generate the 
file if no migration exists yet.

Optional arguments:
  -h, --help            Show this help message and exit.
  --name NAME           The name of the migration file. e.g. my-migration.js, 
                        my-migration.ts or my-migration.sql. Note - a prefix 
                        will be added to this name, usually based on a 
                        timestamp. See --prefix
  --prefix {TIMESTAMP,DATE,NONE}
                        The prefix format for generated files. TIMESTAMP uses 
                        a second-resolution timestamp, DATE uses a 
                        day-resolution timestamp, and NONE removes the prefix 
                        completely. The default value is "TIMESTAMP".
  --folder PATH         Path on the filesystem where the file should be 
                        created. The new migration will be created as a 
                        sibling of the last existing one if this is omitted.
  --allow-extension EXTENSION
                        Allowable extension for created files. By default .js,
                         .ts and .sql files can be created. To create txt 
                        file migrations, for example, you could use '--name 
                        my-migration.txt --allow-extension .txt' This 
                        parameter may alternatively be specified via the 
                        UMZUG_ALLOW_EXTENSION environment variable.
  --skip-verify         By default, the generated file will be checked after 
                        creation to make sure it is detected as a pending 
                        migration. This catches problems like creation in the 
                        wrong folder, or invalid naming conventions. This 
                        flag bypasses that verification step.
  --allow-confusing-ordering
                        By default, an error will be thrown if you try to 
                        create a migration that will run before a migration 
                        that already exists. This catches errors which can 
                        cause problems if you change file naming conventions. 
                        If you use a custom ordering system, you can disable 
                        this behavior, but it's strongly recommended that you 
                        don't! If you're unsure, just ignore this option.

Creating migrations - API

Umzug includes an optional helper for generating migration files. It's often most convenient to create files using the CLI helper, but the equivalent API also exists on an umzug instance:

await umzug.create({name: 'my-new-migration.js'})

License

See the LICENSE file

umzug's Issues

lib/index.js is missing in the published package

$ yarn list umzug
yarn list v0.20.3
├─ [email protected]
│  └─ [email protected]
└─ [email protected]
Done in 1.79s.

$ ls node_modules/umzug
CHANGELOG.md  LICENSE  README.md  gulpfile.js  node_modules  package.json  src  test  yarn.lock

$ node
> const umzug = require('umzug')
Error: Cannot find module 'umzug'
    at Function.Module._resolveFilename (module.js:470:15)
    at Function.Module._load (module.js:418:25)
    at Module.require (module.js:498:17)
    at require (internal/module.js:20:19)
    at repl:1:14
    at ContextifyScript.Script.runInThisContext (vm.js:23:33)
    at REPLServer.defaultEval (repl.js:336:29)
    at bound (domain.js:280:14)
    at REPLServer.runBound [as eval] (domain.js:293:12)
    at REPLServer.onLine (repl.js:533:10)
>

Related: tsg-ut/mnemo#272

umzug.up({to: '...'}) runs all pending migrations instead of only those from the current state to the given name

According to the readme:

It is also possible to pass the name of a migration in order to just run the migrations from the current state to the passed migration name.

umzug.up({ to: '20141101203500-task' }).then(function (migrations) {});

However, this doesn't appear to work as described. Using umzug 1.6.0, when I pass an options object like {to: '201510301204-add-index'}, umzug runs all pending migrations, not just the migrations between the current state and 201510301204-add-index. It also runs all pending migrations if I pass a completely bogus migration name like {to: 'bogus'}.

I peeked at the unit tests to see if they held any clues, but passing an options object to up() doesn't seem to be tested. Does this feature actually exist?

Minifying code gives an error

Hi,

when I try to minify my project including umzug I get the following error: Unexpected token: punc ([)

My investigation has shown that this line is the reason: SequelizeStorage.js Line 77. Any chance to get it fixed?

Thanks and kind regards,
Thomas

Tests currently fail

I have node v5.9.0 and npm 3.7.3. Tests currently fail for me on the master branch (with the missing jshint dependency added):

  106 passing (1s)
  2 failing

  1) storages sequelize constructor accepts a "sequelize" option and creates a model:
     Error: expected undefined to sort of equal null
    at Assertion.assert (node_modules/expect.js/index.js:96:13)
    at Assertion.eql (node_modules/expect.js/index.js:230:10)
    at test/storages/sequelize.test.js:56:52
    at bound (domain.js:287:14)
    at runBound (domain.js:300:12)
    at tryCatcher (node_modules/bluebird/js/release/util.js:16:23)
    at Promise._settlePromiseFromHandler (node_modules/bluebird/js/release/promise.js:503:31)
    at Promise._settlePromise (node_modules/bluebird/js/release/promise.js:560:18)
    at Promise._settlePromise0 (node_modules/bluebird/js/release/promise.js:605:10)
    at Promise._settlePromises (node_modules/bluebird/js/release/promise.js:684:18)
    at Async._drainQueue (node_modules/bluebird/js/release/async.js:126:16)
    at Async._drainQueues (node_modules/bluebird/js/release/async.js:136:10)
    at Immediate.Async.drainQueues [as _onImmediate] (node_modules/bluebird/js/release/async.js:16:14)


  2) storages sequelize constructor accepts a "columnType" option:
     Error: expected undefined to sort of equal null
    at Assertion.assert (node_modules/expect.js/index.js:96:13)
    at Assertion.eql (node_modules/expect.js/index.js:230:10)
    at test/storages/sequelize.test.js:120:52
    at bound (domain.js:287:14)
    at runBound (domain.js:300:12)
    at tryCatcher (node_modules/bluebird/js/release/util.js:16:23)
    at Promise._settlePromiseFromHandler (node_modules/bluebird/js/release/promise.js:503:31)
    at Promise._settlePromise (node_modules/bluebird/js/release/promise.js:560:18)
    at Promise._settlePromise0 (node_modules/bluebird/js/release/promise.js:605:10)
    at Promise._settlePromises (node_modules/bluebird/js/release/promise.js:684:18)
    at Async._drainQueue (node_modules/bluebird/js/release/async.js:126:16)
    at Async._drainQueues (node_modules/bluebird/js/release/async.js:136:10)
    at Immediate.Async.drainQueues [as _onImmediate] (node_modules/bluebird/js/release/async.js:16:14)

What is the meaning of 'upName' and 'downName'?

The README mentions the two config parameters upName and downName:

// The name of the positive method in migrations.
upName: 'up',

// The name of the negative method in migrations.
downName: 'down',

Am I wrong to assume they can be used to configure the methods that will be called in the migration files (default being up and down)? It doesn't seem to work for me, nor can I find any usage of those params in the umzug code.

My config:

this.umzug = new Umzug({
      storage: 'sequelize',
      storageOptions: {
        sequelize,
        modelName: 'SeedsMeta',
        tableName: 'SeedsMeta',
      },
      upName: 'plant',
      downName: 'burn',
      logging: msg => logger.info(msg),
      migrations: {
        params: [sequelize.getQueryInterface(), require('sequelize')],
        path: 'seeders',
        pattern: /^\d+-[\w-]+\.js$/,
      },
    });

This calls the up and down methods instead of the expected plant and burn.

Any hint?

Support for multiple migrations paths in Umzug options

Hello, is there any possibility to define multiple directories in Umzug migration.path option?
For example:

const umzug = new Umzug({
  migrations: {
    path: ['./dir1', './dir2'],
  }
})

I see that there is one rejected PR which implements this feature.

Is there a way to programmatically define migrations?

I'd like to generate migration up and down functions programmatically (without writing to the file system) is there a way to do that?

Maintainer needed

I don't have enough free time to maintain this project so I'm asking your help. You will be the main maintainer and I'll help you when you ever need some help. If you got interested, please comment bellow or contact me (@jukkah) on Sequelize's Slack.

SequelizeMeta table is created with THREE indexes (for a single field)

hi there,

I just ran [email protected] (with [email protected]) against a new database and got a [MySQL] table that has the following schema:

CREATE TABLE `sequelize_meta` (
  `name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`name`),
  UNIQUE KEY `name` (`name`),
  UNIQUE KEY `sequelize_meta_name_unique` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

I guess that this is probably a sequelize issue rather than sequelize-cli based on this line of code

creating three indexes where one will do is rather wasteful, in the case of the SequelizeMeta the extraneous indexes are not a practical problem because the number of records stored is, from a RDMS point-of-view, minimal. still, it looks messy (IMO).

If this is what sequelize's Model.sync method does then that seems to me to be problematic - it's rather inefficient to create/maintain these duplicate indexes - at the very least Model.sync should be documented as having caveats, no?

This issue seems, to me, to tie in with the whole "should-I-use-model.sync-or-migrations" question ... one that I personally find a little confusing (okay that is a lie, I'm quite adamant that model.sync should be avoided like the plague and [single-query] migrations should be used, but that is just my opinion!) ... the [sequelize] documentation show 2 distinct methods of tackling schema creation/mutation but does offer any guidance about why you should use one or the other, additionally it's not immediately clear that the 2 methods are (AFAICT) mutually exclusive.

Umzug does not find any migrations in a folder

It seems that umzug (1.3.1) does not "see" any migrations. I have played around source code and found something strange:

// index.js

_findMigrations: function () {
    console.log(fs.readdirSync(this.options.migrations.path)) // <------------- this line prints correct list of files
    return Bluebird
      .promisify(fs.readdir)(this.options.migrations.path)
      .bind(this)
      .filter(function (file) {
        console.log('migration:' + file) // <---------------------- this line does not print anything (never executed)
        return this.options.migrations.pattern.test(file);
      })
      .map(function (file) {
        return path.resolve(this.options.migrations.path, file);
      })
      .map(function (path) {
        return new Migration(path, this.options);
      });
  },

output is:

[ '20150210121013-unnamed-migration.js' ]

Get 'ER_TOO_LONG_KEY error running migrations in the utf8mb4 mysql database

When running migrations, get the ER_TOO_LONG_KEY, probably because the mysql database charset is utf8mb4. How to make it work?

Here is the database config:

"staging": {
    "username": "USERNAME",
    "password": "PASSWORD",
    "database": "DATABASE",
    "host": "HOST",
    "dialect": "mysql",
    "port": 3306,
    "define": {
      "charset": "utf8mb4",
      "collate": "utf8mb4_general_ci"
    }
  }

The table trying to create:

module.exports = {
  up: function (queryInterface, Sequelize) {
    return queryInterface.createTable('users', {
      id: {
        type:Sequelize.INTEGER,
        primaryKey: true,
        autoIncrement: true
      },
      openid: Sequelize.STRING,
      nickname: Sequelize.STRING,
      sex: {
        type: Sequelize.INTEGER,
        defaultValue: 1
      },
      headimgurl: Sequelize.STRING,
      createdAt: Sequelize.DATE,
      updatedAt: Sequelize.DATE
    });
  },

  down: function (queryInterface, Sequelize) {
    return queryInterface.dropTable('users');
  }
};

And the sql that executed and got the error was

CREATE TABLE IF NOT EXISTS `SequelizeMeta` (`name` VARCHAR(255) NOT NULL UNIQUE , PRIMARY KEY (`name`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_general_ci;

Based on the discussion sequelize/sequelize#4171 (comment), @janmeier suggested it was an issue of the migrator thus open an issue here.

.up(tasks/task) / .down(tasks/task)

Add support for:

.up(['tasks']);
.up('task');
.down(['tasks']);
.down('task');

which, alternative to pure down and down({to: }) would only run the specific tasks regardless of what the state is.

Reference to Sequelize

It's possible pass the reference to Sequelize in the sequelize storage constructor instead of doing this?

https://github.com/sequelize/umzug/blob/master/lib/storages/sequelize.js#L8

var Sequelize = require(resolve('sequelize', { basedir: process.cwd() }));

I'm working on a framework that depends on this library, but the applications using the framework doesn't depends directly on sequelize.

https://github.com/axelhzf/barbakoa/blob/master/app/server/migrations.js

Does this make sense?

Sequelize migration example

Hi there!

I'm trying to wrap my head around umzug and can't seem to figure out how to actually run a Sequalize migration in the proposed format given here: https://github.com/sequelize/umzug#format

I'm probably in error having read the Sequalize migration documentation, because I'm missing the passed migration object which is referenced in those docs.

An example on how to execute a Sequalize migration in the proposed format is very welcome!

Thanks in advanced for the effort.

Regards,

Rutger

revert last migration - reverts all if param is empty object

Since introduction of to: 0 to revert all migrations I think a bug was introduced;

umzug.down({}).then(function (migration) {
   // will be same as using {to: 0} i.e. will revert ALL migrations
});

using undefined as params works as expected. If this is intentional, I think readme should be updated so users will know of this as well.

How to listen for migration events

Hello, Guys.

I know this is not really an issue, but I have asked this on Stackoverflow but answer yet for over 13 hours

You know when you use SequelizeJS with migration and not .sync, when you want to make changes to your DB structure, you generate and edit a migration for it.
Then you run sequelize db:migrate
This updates the db schema structure but not the corresponding Sequelize model.
You have to manually edit the model to match the current state of the actual DB

Now that is what I want to automate (kind of).
There is a function in Sequelize QueryInterface: showAllTables and describeTable that list all tables in the database and get table schema respectively.

I want to use those functions to fetch the current database schema and populate it to a file each time I run sequelize db:migrate or sequlize db:migrate:undo (Just like rails migration does)

Now my problem is hooking into that migration event.

I know that Sequlize CLI use Umzug for migration and the Umzug README says:

Umzug is an EventEmitter. Each of the following events will be called with name, migration as arguments. Events are a convenient place to implement application-specific logic that must run around each migration:

• migrating - A migration is about to be executed.
• migrated - A migration has successfully been executed.
• reverting - A migration is about to be reverted.
• reverted - A migration has successfully been reverted.

How do I hook in and listen for those events with my current Sequelize object and perform actions when the event happens?

Thanks

Squashed migrations

Is there any way to combine migrations to squashed migrations and run them instead of the original migrations? Currently no, I guess.

As you know, if there is a zillion of migrations, running them all from the beginning to the end may take a while. In addition, if you have [email protected] and you upgrade it to [email protected], performance of the database migration matters.

For example, Django have some kind of implementation for squashed migrations. I'm not sure if Django replaces migrations with squashed ones, but I think it's better to save them just in case. So there should be all the migrations plus some squashed migrations.

In the picture above, black dots are migrations created between releases. Bellow the migrations are squashed migrations. In this example, there are squashes for each minor and major release and one from the beginning to the latest release.

I think, in umzug, we need only

• a list of migrations combined into each squashed migration,
• a way to figure out what squash to use if any, and
• a way to make difference between migrations and squashes.

I can implement this feature but it needs some discussion first.

Trying to run tests gives an error

When running npm run test I get this:

> [email protected] test /home/bjn/src/umzug
> gulp

module.js:341
    throw err;
    ^

Error: Cannot find module 'jshint/src/cli'
    at Function.Module._resolveFilename (module.js:339:15)
    at Function.Module._load (module.js:290:25)
    at Module.require (module.js:367:17)
    at require (internal/module.js:16:19)
    at Object.<anonymous> (/home/bjn/src/umzug/node_modules/gulp-jshint/src/extract.js:1:79)
    at Module._compile (module.js:413:34)
    at Object.Module._extensions..js (module.js:422:10)
    at Module.load (module.js:357:32)
    at Function.Module._load (module.js:314:12)
    at Module.require (module.js:367:17

feat: Add support for multiple models and migrations folders

This will accomodate an HMVC structure which the models and migrations are segmented across modules.

Problem not finding the migration file

Hi, first day working with the migration and i am stuck.
I have a migration working on console (db:migrate), but when i remove it and try to use umzug it gives me this error:
Unhandled rejection Error: Unable to find migration: [object Object]

The migration object is:

{ path: '(nothing to show)/backend/server/migrations/20150724131728-create-sections.js',
file: '20150724131728-create-sections.js',
options:
{ storage: 'sequelize',
storageOptions:
{ modelName: 'SequelizeMeta',
columnName: 'name',
sequelize: [Object],
model: [Object] },
logging: [Function],
upName: 'up',
downName: 'down',
migrations:
{ params: [],
path: 'server/migrations',
pattern: /^\d+[\w-]+.js$/,
wrap: [Function] } } }

The only difference i think is that the script is running with "server/app.js" and the migrations are in folder server/migrations, i properly configured umzung for that with:

var umzug = new Umzug({
storage: 'sequelize',
logging: console.log,
storageOptions: {
sequelize: app.get("sequelize")
},
migrations: {
path: 'server/migrations'
}
});

I also tried to just move the migration folder outside the server folder and changing the configuration but that did not worked either.

Please help, i really want to use this but i cannot, if i cannot make it work with just one migration file.

how to use migration.migrator.sequelize ??

I've just updated to rc4, and per the previous warning support for the migrations in core is fading out. the up/down method are still being run, however I can't work out how to use the migration.migrator.sequelize class that was available before. a stripped down version of my code

migration.createTable('posts', {
    id: {
        type: DataTypes.STRING(24),
        allowNull: false,
        primaryKey: true
    })
.complete(function(err){
    var database = migration.migrator.sequelize;
    database.query('ALTER TABLE posts ALTER COLUMN id SET DEFAULT mongodb_object_id()')
            .complete(function(){
                done();
            });
});

How do i execute database.query('ALTER TABLE posts ALTER COLUMN id SET DEFAULT mongodb_object_id()') using the new umzug library?

Umzug Questions

Hi guys I'm using umzug in this days :D and I have a questions

1- Is need describe the properties of the table in the model and migration ?. I seem redundant make in two places.

2 - When in my migration delete a column email , and my user model too have the property email , when the migration is executed this delete the email column , but my model still follow with the email property , I need delete the email property of the model manually ?

3- Is possible execute the umzug migrations by cli ?, because now I need run the server for up migrations and execute another time for down migrations.

thanks :D

Should I make associations in migrations?

Hi guys! One quick question:

Should I make associations with umzug migrations or Sequelize? Or both?

Best regards!

FYI: Demonstration project utilizing postgres/sequelize/umzug

Hey there. Figured it was worth letting you know. I put together a demo project to learn how umzug works with sequelize and postgres. Figured it was worth sharing with you guys:

https://github.com/abelnation/sequelize-migration-hello

Collation and charset not match error when migrating utf8mb4 databases

In the config file define something like

{
  "production": {
    ...,
   {
     "define": {
       "charset": "utf8mb4",
       "collate": "utf8mb4_general_ci",
     }
   }
  }
}

to work with utf8mb4.

When running migration, it throws the error

{ [SequelizeDatabaseError: ER_COLLATION_CHARSET_MISMATCH: COLLATION 'utf8_unicode_ci' is not valid for CHARACTER SET 'utf8mb4']
  name: 'SequelizeDatabaseError',
  message: 'ER_COLLATION_CHARSET_MISMATCH: COLLATION \'utf8_unicode_ci\' is not valid for CHARACTER SET \'utf8mb4\'',
  parent:
   { [Error: ER_COLLATION_CHARSET_MISMATCH: COLLATION 'utf8_unicode_ci' is not valid for CHARACTER SET 'utf8mb4']
     code: 'ER_COLLATION_CHARSET_MISMATCH',
     errno: 1253,
     sqlState: '42000',
     index: 0,
     sql: 'CREATE TABLE IF NOT EXISTS `SequelizeMeta` (`name` VARCHAR(255) NOT NULL UNIQUE , PRIMARY KEY (`name`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE utf8_unicode_ci;' },
  original:
   { [Error: ER_COLLATION_CHARSET_MISMATCH: COLLATION 'utf8_unicode_ci' is not valid for CHARACTER SET 'utf8mb4']
     code: 'ER_COLLATION_CHARSET_MISMATCH',
     errno: 1253,
     sqlState: '42000',
     index: 0,
     sql: 'CREATE TABLE IF NOT EXISTS `SequelizeMeta` (`name` VARCHAR(255) NOT NULL UNIQUE , PRIMARY KEY (`name`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE utf8_unicode_ci;' },
  sql: 'CREATE TABLE IF NOT EXISTS `SequelizeMeta` (`name` VARCHAR(255) NOT NULL UNIQUE , PRIMARY KEY (`name`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE utf8_unicode_ci;' }

It is due to the collate is hardcoded as utf8_unicode_ci (3760017) while it uses the charset that defined in define.charset in the config file.

Propose to explicitly set the charset as 'utf8' in 3760017 too.

Would be happy to create a PR if this looks good.

Example of custom storage

I'm currently trying to write a custom storage option for Mongoose, but it's proving quite difficult. Do you have some examples you could share on how to achieve this?

I've looked through the built in storage options and the documentation and it hasn't made it clear.

custom sorting function

Umzug sorts migration filenames by name, thus files named 1, 2, 12 will be sorted 1, 12, 2. Files can also be named using different paterns (major.minor etc.), so it's best to allow to supply custom sorting function.

Understanding Sync

I am having a hard time to understand how can I sync my Sequelize models with the mysql database. After doing lot of googling I came to Umzug and really was looking for an example which could show me how can I sync my models written in Sequelize with mysql or mariadb using Umzug. Also I did not really find the gulp module which could do that.

Can you guys point me to some place where I can find an example of using Umzug with Sequelize.

Tag and publish new minor release

@jukka I know you're working on v2 but can you tag and publish 1.12.0 since it includes 0f8b701?
Thanks!

Generating raw SQL migration query files

In rails, generating migrations will also generate an .sql file to be used with SQL.
E.g, when creating a new migration, it will be accompanied with something like this file:
2103232813.sql

ALTER TABLE `products` CHANGE `group` `group` varchar(50) DEFAULT NULL
ALTER TABLE `products` CHANGE `person` `person` varchar(50) DEFAULT NULL
ALTER TABLE `products` CHANGE `store_name` `store_name` varchar(50) DEFAULT NULL

Is this possible with sequelize / umzug as well? At our company, engineers do not have access to production servers so we usually give the raw queries to our devops which they use to execute migrations on our production database.

I was thinking that this would be possible by using a custom storage adapter in new Umzug({storage: CustomStorage})? I would appreciate if someone could guide me to the right direction to achieve this functionality.

It seems I need to migrate SequalizeMeta itself... which provides a chicken and egg problem.

I've been using Sequelize's built-in migrator up until now, and it created a SequelizeMeta table like so:

+----+----------------+----------------+
| id | from           | to             |
+----+----------------+----------------+
|  1 | 20140630023740 | 20140630023740 |
|  2 | 20140630023740 | 20140630050120 |
|  3 | 20140630023740 | 20140630050310 |
|  4 | 20140630023740 | 20140630051502 |
|  5 | 20140630023740 | 20140704030610 |
|  6 | 20140630023740 | 20140704032403 |
|  7 | 20140630023740 | 20140706051950 |
|  8 | 20140630023740 | 20140707001031 |
|  9 | 20140630023740 | 20140707012805 |
| 10 | 20140630023740 | 20140720193056 |
| 11 | 20140630023740 | 20140720193512 |
| 12 | 20150221155025 | 20150221155025 |
+----+----------------+----------------+

But it seems that the SequelizeMeta table that umzug is trying to read must look something like this:

+----+-------------------------------------------------------+
| id | name                                                  |
+----+-------------------------------------------------------+
|  1 | 20140630023740-create-user.js                         |
|  2 | 20140630050120-create-room.js                         |
|  3 | 20140630050310-create-roomadmin.js                    |
|  4 | 20140630051502-drop-room-user-tables.js               |
|  5 | 20140704030610-fix-table-name-case.js                 |
|  6 | 20140704032403-seed-default-rooms.js                  |
|  7 | 20140706051950-create-song-file-queuedsong-tables.js  |
|  8 | 20140707001031-migrate-song-file-queuedsong-data.js   |
|  9 | 20140707012805-drop-song-file-queued_song-tables.js   |
| 10 | 20140720193056-create-songsourcemap.js                |
| 11 | 20140720193512-create-upload-songsourcemappings.js    |
| 12 | 20150221155025-create-song-statistic-table.js         |
+----+-------------------------------------------------------+

How can I migrate the migration table itself? Or more desirably, how can I make umzug use sequelize's old format?

This issue is preventing me from switching from sequelizes' deprecated migrator to umzug's. Thanks!

Accessing missing properties should give an early sequelize error

This may be a fundamental design flaw in Sequelize and impossible to fix, but I think it is important to document it somewhere.

Basically the problem is when trying to refer to a table column that does not exist in the model. Sequelize does not seem to care to validate its existence before doing a query. This results in the database giving an exception. The problem with this approach is that I loose the ability to know where in my code this invalid query is being performed. I guess the underlying issue here is that since .sync is not really syncing the models with SQL, the developers of Sequelize decided to delegate the validation to the SQL server instead. I do not think this is the correct approach, it seems to be wiser to make the validation before making the query, independently if that column exists in SQL or not.

Warning: a promise was created in a handler but was not returned from it

I've started a new project and am using postgres and sequelize, and umzug for migrations. My migrations are working, as in the tables are created, fields and constraints correct, etc... but I'm continuously getting this error:
Warning: a promise was created in a handler but was not returned from it

My migrations look like this (note, I've used both es6 and es5 syntax with no difference):

//######-someSchema.js
module.exports = {
  up: (queryInterface, DataTypes) => {
    return queryInterface.createTable(......)
        .then(() => queryInterface.createTable('some_table_with_fk_to_first_table', .....);

    //ES5 syntax, no difference:
    return queryInterface.createTable(...)
        .then(function() { 
            return queryInterface.createTable(...);
        });
  }, 
  down: (queryInterface, DataTypes) => {

  }
};

and here's where I'm invoking umzug from (again, it's technically working but this error is obnoxious and I want to make sure it's not something I should be worried about...)

//migrations.js
umzug.pending()
    .then(pendingMigrations => {
        console.log('pendingMigrations: ', pendingMigrations.map(p => p.file));
        umzug.up()
            .then(() => console.log('Migrations Done'))
            .catch(err => {
                console.log('MIGRATION ERROR!', arguments);
                //umzug.down() TODO: How to only run .down of failed migration?
            });
    })
    .catch(err => console.log('Error getting pending migrations: ', err));

As you may see, I also have a secondary question in the umzug.up().catch(...) handler - "How to run the "down" method of failed migration" - if any insight on this also, would appreciate it!

BEGIN-COMMIT is a migration

Hi,

I have a problem with BEGIN-COMMIT keywords in a migration.

I run umzug.up() for all of my migrations in a loop, and log errors if they occur. Unfortunately, as soon as umzug gets to this one:

BEGIN;
...
# Some SQL that produces an error
...
COMMIT;

all the next migrations error out with:

SequelizeDatabaseError: current transaction is aborted, commands ignored until end of transaction block

Is there a way to run migrations atomically with umzug?

Thanks

Officially supported sequelize versions

The latest release of sequelize is v3.21.0 and we are still officially supporting (in .travis.yml) only v.1.7.10, v2.0.0-rc8 and v2.0.0. Should we add official support for newer versions? It shouldn't be a huge task because at the moment of writing #69 I ran tests against almost all the sequelize versions without any other issues.

I vote for officially supported versions:

• ^1.0.0 := latest < v2.0.0, currently v1.7.9.
• ^2.0.0 := latest < v3.0.0, currently v2.1.3.
• ^3.0.0 := latest < v4.0.0, currently v3.21.0.

Umzug is finding deleted migration files

This is by far the oddest thing that has happened to me with computers lol.

I had two migrations, "1" and "2". I was resetting my DB and restarting my server which would run both migrations. Then I deleted the second migration file. Reset the db, and when I start my server umzug still lists both files as pending migrations. It starts my server without error! The second migration file had nothing in it though, was literally an empty file.

If I make a new directory with the name "migration" (old directory was "migrations") and copy only the first migration into it, and update umzug settings to look at the new directory, umzug lists only the first migration as pending (correct behavior, only one migration exists).

However then if I rename this NEWLY CREATED directory to the name I was using for the old location of "migrations" (a directory I have now deleted), reset my db, restart my server, umzug again lists both migrations as pending! There is only one migration file in the directory! Doing "ls migrations" lists only one file!

How? just how?

How to add a field to a model without affecting data

Hey guys,

I am trying to understand how to add fields to a created table using this library. Could someone please post an example? Say I want to add a field to this model:

var Services = sequelize.define("Services", {
service_type : {type: Sequelize.STRING},
service_name : {type: Sequelize.STRING, primaryKey: true, allowNull: false}
});

how would I go about doing it?

createTable doesn't create named unique constraint

When using createTable to create a table with a named unique constraint, the constraint doesn't get set. The same happens if I try to define the constraint using { name: '..', msg: '...' }. If unique is a boolean (= true), a unique constraint gets set (but obviously no named one).
I was using sqlite and haven't tried any other databases so far.

export const up = async (queryInterface, dataTypes) => {
  /* migrate up */
  await queryInterface.createTable('test', {
    a: { type: dataTypes.INTEGER, unique: 'My_unique_key' },
    b: { type: dataTypes.INTEGER, unique: 'My_unique_key' }
  });
};

Questions about migrations

Hi all.

This module looks great. Up until now I've been doing migrations by hand but with multiple servers running the same sequelize-based app, it's becoming a real pain.

A couple of questions:

1. Are there examples anywhere of migration files for common sequelize migrations e.g. adding a column to a table?
2. Is there a way to specify in migration files in what order they should be run (e.g. migration X must run after migration Y)?

What I'm thinking with (2) is that it'd be nice to specify dependencies in the migration files themselves so then you can just run umzug.up() without options, and know they'll run in the right order. Or is the solution to number the migration files to ensure correct order of execution?

Hope you can help...

Gulp lint target not running

I'm writing a MongoDB Storage (https://github.com/PascalPflaum/umzug/tree/feature/MongoDBStorage) right now and noticed that the gulp lint target is not working with the current code.

src/index.js
line 53 col 7 Expected '}' to match '{' from line 47 and instead saw '...'.
line 53 col 10 Missing semicolon.
line 54 col 5 Unexpected '}'.
line 53 col 17 Expected an assignment or function call and instead saw an expression.
line 53 col 18 Missing semicolon.
line 54 col 6 Unnecessary semicolon.
line 56 col 9 Expected an identifier and instead saw 'this' (a reserved word).
line 56 col 13 Expected ')' to match '(' from line 56 and instead saw '.'.
line 56 col 13 'function closure expressions' is only available in Mozilla JavaScript extensions (use moz option).
line 56 col 68 Unexpected ')'.
line 56 col 70 Unexpected '{'.
line 57 col 13 Class properties must be methods. Expected '(' but instead saw 'new'.
line 57 col 23 Expected an identifier and instead saw 'The logging-option should be either a function or false'.
line 57 col 80 'function closure expressions' is only available in Mozilla JavaScript extensions (use moz option).
line 57 col 81 Expected an identifier and instead saw ';'.
line 58 col 6 Missing semicolon.
line 64 col 23 Missing "use strict" statement.
line 65 col 7 Expected '}' to match '{' from line 60 and instead saw '...'.
line 65 col 10 Missing semicolon.
line 66 col 5 Unexpected '}'.
line 65 col 33 Expected an assignment or function call and instead saw an expression.
line 65 col 34 Missing semicolon.
line 65 col 33 Unrecoverable syntax error. (10% scanned).

✖ 12 errors
⚠ 11 warnings

If you need help fixing this issues, I would do so in a seperate branch.

How do I set the name of the foreign key on queryInterface.addColumn()?

I'm trying to add new columns to multiple tables called "projectId", which as you may have guessed, refers back to the id column in the Project table. The first table that I alter with addColumn() works fine, but the second table's addColumn() seems to fail because the FK name is not unique.

Here' s the code I'm running:

queryInterface.addColumn(
    { tableName: "CriticalProcessParameters" },

    "ProjectId",
    {
        type: DataTypes.INTEGER,
        references: {
            model: "Projects",
            key: 'id'
        },
    }
);

and here's the error I get:

Executing (default): ALTER TABLE `CriticalProcessParameters` ADD `ProjectId` INTEGER, ADD CONSTRAINT `ProjectId_foreign_idx` FOREIGN KEY (`ProjectId`) REFERENCES `Projects` (`id`);   Unhandled rejection SequelizeDatabaseError: ER_DUP_KEY: Can't write; duplicate key in table '#sql-1d44_502'
    at Query.formatError (C:\projects\cherrycircle\node_modules\sequelize\lib\dialects\mysql\query.js:175:14)
    at Query._callback (C:\projects\cherrycircle\node_modules\sequelize\lib\dialects\mysql\query.js:49:21)
    at Query.Sequence.end (C:\projects\cherrycircle\node_modules\mysql\lib\protocol\sequences\Sequence.js:86:24)
    at Query.ErrorPacket (C:\projects\cherrycircle\node_modules\mysql\lib\protocol\sequences\Query.js:94:8)
    at Protocol._parsePacket (C:\projects\cherrycircle\node_modules\mysql\lib\protocol\Protocol.js:280:23)
    at Parser.write (C:\projects\cherrycircle\node_modules\mysql\lib\protocol\Parser.js:74:12)
    at Protocol.write (C:\projects\cherrycircle\node_modules\mysql\lib\protocol\Protocol.js:39:16)
    at Socket.<anonymous> (C:\projects\cherrycircle\node_modules\mysql\lib\Connection.js:109:28)
    at emitOne (events.js:77:13)
    at Socket.emit (events.js:169:7)
    at readableAddChunk (_stream_readable.js:153:18)
    at Socket.Readable.push (_stream_readable.js:111:10)
    at TCP.onread (net.js:536:20)

How do I set name of the foreign key to be something other than 'ProjectId'?

Force umzug to mark all migrations as applied

I want my code to either create the db on startup if not present or otherwise migrate it to the latest version.

I think have two options:

1. Create an initial migrator that basically represents the first version of the DB (which usually involves quite a lot of tables...), and then apply all the migrations.
2. Do a sync().

If I do 2, which I would prefer in principle to avoid having to duplicate the DB schema already described on the sequelize model, then, after the sync, no migrations will have been recorded, so they will try to be reapplied, which causes issues since he model has been synced before.

Is there a way to tell umzug to record all the migrations as done even if they have not been actually executed? Or should I do that on the up() method itself? Example, add some argument somewhere and do:

up() {
  if (!creating_from_scratch) {migrate...}
}

Suggestions welcomed ;).

Format of json file for the json storage

Is there a special format that the .json file should have, when 'json' type of storage is chosen? F.e.

{
   "name": []
}

???

Because when I leave it empty (or as described above), my migrations aren't run and nothing actually happens, and nothing is logged... I debugged a bit, and I can verify that the file is being found and parsed correctly and all, but the code for logging a migration is never reached.

Umzug should reject a migration if it can't find the migration method

Note: I created a pull request for this a while back, but it hasn't been merged in yet. I'm not sure what the process is supposed to be, so I created an issue. This is the pull request:

#115

Rationale for change: you will never have a no-op migration. Currently, if the up or down methods can't be found when running a migration, Umzug replaces the method body with Promise.resolve() and tells you that the migration was run correctly. (It wasn't.)

Migrations with relations failing with "relation 'table' does not exist"

Hi there,

When attempting to create a table via migrations which references a table that does not exist yet, it fails with the "relation 'table' does not exist".

I understand that when handling this directly via the database, the same issue would occur, however I would have thought that Sequelize would handle that automatically?

foreignKeyId: {
    type: Sequelize.INTEGER,
    references: {
        model: 'ForeignTable',
        key: 'id'
    }
}

For clarity, is it expected for users to generate their table schema without relations first and then create additional migrations containing the relations afterwards or should this work?

Within my model it self, I do have

associate: function(models) {
    // associations can be defined here
    models.Primary.hasOne(models.Foreign);
}

Does this handle relations within the database, or does it only define the relationship within Sequelize?

Thanks

Create the migrations table in a certain schema

I want to have the SequelizeMeta table in a custom schema, but not in public. The only option I found, that relates to that. is the modelName property in the initialization options. So, I would try the following:

storageOptions:{
    modelName: 'myschema.SequelizeMeta',
}

but this will just create a "myschema.SequelizeMeta" table in the public` schema.

Is there a way to achieve that?

Use umzug to seed the database

I'm in love with how umzug is simple and easy to use.

And since it's a general task/migration runner, do you think it's a good idea to also use it to seed the database in the right time?

addColumn not working properly

I run following migration and active column successfully added with default value 1 but as last column not after the password column.

{dialect: 'sqlite'}

queryInterface.addColumn('users', 'active', {
      type: Sequelize.BOOLEAN,
      defaultValue: 1,
      after: 'password'
 });
``

Allow to pass storage object to Umzug constructor

class Storage {
  constructor() { ... }
  logMigration() { ... }
  unlogMigration() { ... }
  executed() { ... }
}

const umzug = new Umzug({ storage: Storage });

how is $in operator supposed to work?

I have a super simple case where I cannot make $in operator work, not sure if I am doing something wrong or if it is a bug in sequelize:

var User = sequelize.define('user', {
    username: Sequelize.STRING,
    emails: Sequelize.ARRAY(Sequelize.STRING)
  });

Trying to do a findOrCreate fails with an error:

var defaultUser = {
  username: 'default',
  emails: ['[email protected]']
}

User.findOrCreate({
  where: {
    emails: {$in: defaultUser.emails}
  },
  defaults: defaultUser
});

Error:

Unhandled rejection SequelizeDatabaseError: malformed array literal: "[email protected]"
    at Query.formatError (/backend/node_modules/sequelize/lib/dialects/postgres/query.js:357:14)
    at null.<anonymous> (/backend/node_modules/sequelize/lib/dialects/postgres/query.js:88:19)
...