Knex Migration Class

example.coffee

db = require('knex')(yourConfig)
Migration = require('../../lib/db').Migration

upgrade = new Migration(db)

upgrade.createTable 'documents', (t) ->
  t.uuid('id')
  t.text('title')

upgrade.createTable 'users', (t) ->
  t.uuid('id')
  t.text('name')

upgrade.createTable 'documents_of_user', (t) ->
  t.uuid('document_id')
  t.uuid('user_id')

upgrade.setPrimaryKey('documents', 'id')
upgrade.addForeignKeys [
  ['documents_of_user', 'users', column: 'user_id', deleteMethod: 'RESTRICT']
  ['documents_of_user', 'document', column: 'document_id', deleteMethod: 'CASCADE']
]

downgrade = new Migration(db)
downgrade.dropTable('documents')
downgrade.dropTable('users')
downgrade.dropTable('documents_of_user')

exports.up = (cb) ->
  upgrade.exec(cb)


exports.down = (cb) ->
  downgrade.exec (err) ->
    throw err if err
    cb()

migration.coffee

class Migration

  constructor: (connection) ->
    @db = connection
    @commands = []


  dropTable: (name) ->
    command = @db.raw("DROP TABLE IF EXISTS #{name} CASCADE")
    command.info = "dropTable #{name}"
    @commands.push(command)
    this


  createTable: (name, setupMethod) ->
    command = @db
      .schema
      .createTable(name, setupMethod)

    command.info = "createTable #{name}"
    @commands.push(command)
    this


  getTable: (name, alteration) ->
    command = @db
      .schema
      .table(name, alteration)

    command.info = "getTable #{name}"
    @commands.push(command)
    this


  renameTable: (oldName, newName) ->
    command = @db.schema.renameTable(oldName, newName)
    command.info = "renameTable #{oldName}"
    @commands.push(command)
    this


  dropAndRecreateTable: (name, setupMethod) ->
    @dropTable(name)
    @createTable(name, setupMethod)
    this


  setPrimaryKey: (table, column) ->
    command = @db.raw("""
      ALTER TABLE #{table}
        ADD PRIMARY KEY (#{column})
    """)
    command.info = "setPrimaryKey table: #{table}, column #{column}"
    @commands.push(command)
    this


  removePrimaryKey: (table) ->
    @dropConstraint(table, "#{table}_pkey")


  addForeignKey: (from, to, {column, updateMethod, deleteMethod }) ->
    updateMethod ?= 'NO ACTION'
    deleteMethod ?= 'NO ACTION'

    constraintName = "#{from}_#{column}_fk"
    command = @db.raw("""
      ALTER TABLE #{from}
        ADD CONSTRAINT #{constraintName} FOREIGN KEY (#{column})
          REFERENCES #{to} (id) MATCH SIMPLE
          ON UPDATE #{updateMethod}
          ON DELETE #{deleteMethod}
    """)
    command.info = "addForeignKey from: #{from}, to: #{to}"
    @commands.push(command)


  addForeignKeys: (keys) ->
    for key in keys
      @addForeignKey.apply(this, key)


  removeForeignKey: (fromTable, fromColumn) ->
    @dropConstraint(fromTable, "#{fromTable}_#{fromColumn}_fk")


  dropConstraint: (table, name) ->
    command = @db.raw("""
      ALTER TABLE #{table}
      DROP CONSTRAINT #{name}
    """)
    command.info = "dropConstraint table: #{table} name: #{name}"
    @commands.push(command)


  addUniqueConstraint: (table, column) ->
    constraintName = "#{table}_#{column}_unique"
    command = @db.raw("""
      ALTER TABLE #{table}
      ADD CONSTRAINT #{constraintName} UNIQUE (#{column})
    """)
    command.info = "addUniqueConstraint table: #{table}, column: #{column}"
    @commands.push(command)


  addNotNullConstraint: (table, column) ->
    command = @db.raw("""
      ALTER TABLE #{table}
      ALTER COLUMN #{column} SET NOT NULL
    """)
    command.info = "addNotNullConstraint table: #{table}, column: #{column}"
    @commands.push(command)

  @serialPromises: (trx, promises, afterEach, done) ->
    process.nextTick ->
      if arguments.length == 2
        done = afterEach
        afterEach = undefined

      current = promises.shift()
      current.transacting(trx)
      .then ->
        afterEach(current) if afterEach

        if promises.length
          Migration.serialPromises(trx, promises, afterEach, done)
        else
          done()

      , (err) ->
        done(err)


  exec: (callback) ->
    return callback(new Error("Can't call migration.exec twice")) if @gotExecuted
    @gotExecuted = true
    @db.transaction (trx) =>
      Migration.serialPromises trx, @commands
      , (current) ->
        message = "Command '#{current.info}' succeeded"
        if process.stdin.isTTY
          console.log(message)
        else
          console.log(JSON.stringify({message}))


      , (err) ->
        return trx.rollback(err) if err
        trx.commit()

    .then =>
      @closeConnection(null, callback)

    .catch (err) =>
      @closeConnection(err, callback)


  transaction: (callback, done) ->
    return done(new Error("Can't call migration.exec or .transaction twice")) if @gotExecuted
    @gotExecuted = true
    @db.transaction (trx) =>
      Migration.serialPromises trx, @commands
      , (current) ->
        message = "Command '#{current.info}' succeeded"
        if process.stdin.isTTY
          console.log(message)
        else
          console.log(JSON.stringify({message}))


      , (err) ->
        return trx.rollback(err) if err
        callback trx, (err) ->
          return trx.rollback(err) if err
          trx.commit()

    .then =>
      @closeConnection(null, done)

    .catch (err) =>
      @closeConnection(err, done)


  closeConnection: (err, callback) ->
    callback(err)


module.exports = Migration