Extend migration functionality

[wbyoung]

Migrations should support the following:

• Alter column definition
• Alter column default value
• Alter whether column can contain nulls
• Add reference (foreign key constraint) on existing column
• Remove reference (foreign key constraint) from existing column
• Specify unique in the creation of indexes

Other operations that exist in other tools, but do not seem warranted for various reasons:

• Retrieving current schema information
• Add/remove primary key from exiting table/column
• Adding specific types of indexes & specifying sorting on indexes
• Specifying order of columns

[wbyoung]

Changing the column definition, default value, and whether it allows nulls can be done with the below syntax.

Note that these examples are not intended to build off of each other as a true migration would. They're simply individual examples of syntax. The first alteration should be ignored when reading the second & so on.

alterTable('articles', function(table) {

  table.alter('column').string();
  // mysql -> SHOW COLUMNS FROM `articles` WHERE field = 'column';
  // mysql -> assume column info came back with NOT NULL and default of an empty string
  // mysql -> ALTER TABLE `articles` MODIFY `column` varchar(255) NOT NULL DEFAULT '';
  // postgres -> ALTER TABLE "articles" ALTER COLUMN "column" TYPE varchar(255);
  // sqlite3 -> recreate table

  table.alter('column').default('value');
  // mysql -> SHOW COLUMNS FROM `articles` WHERE field = 'column';
  // mysql -> assume column info came back with NOT NULL
  // mysql -> ALTER TABLE `articles` MODIFY `column` varchar(255) NOT NULL DEFAULT 'value';
  // postgres -> ALTER TABLE "articles" ALTER COLUMN "column" SET DEFAULT 'value';
  // sqlite3 -> recreate table

  table.alter('column').notNull();
  // mysql -> SHOW COLUMNS FROM `articles` WHERE field = 'column';
  // mysql -> assume column info came back with no default value set
  // mysql -> ALTER TABLE `articles` MODIFY `column` varchar(255) NOT NULL;
  // postgres -> ALTER TABLE "articles" ALTER COLUMN "column" SET NOT NULL;
  // sqlite3 -> recreate table

  table.alter('column').string().notNull();
  // mysql -> SHOW COLUMNS FROM `articles` WHERE field = 'column';
  // mysql -> assume column info came back with default of an empty string
  // mysql -> ALTER TABLE `articles` MODIFY `column` varchar(255) NOT NULL DEFAULT '';
  // postgres -> ALTER TABLE "articles" ALTER COLUMN "column" TYPE varchar(255);
  // postgres -> ALTER TABLE "articles" ALTER COLUMN "column" SET NOT NULL;
  // sqlite3 -> recreate table

  table.alter('column').string().notNull(false).default(null);
  table.alter('column').string().nullable().default(null);
  // mysql -> SHOW COLUMNS FROM `articles` WHERE field = 'column';
  // mysql -> column info is not required here, but it's probably simpler to always get it
  // mysql -> ALTER TABLE `articles` MODIFY `column` varchar(255) DEFAULT NULL;
  // postgres -> ALTER TABLE "articles" ALTER COLUMN "column" TYPE varchar(255);
  // postgres -> ALTER TABLE "articles" ALTER COLUMN "column" DROP NOT NULL;
  // postgres -> ALTER TABLE "articles" ALTER COLUMN "column" DROP DEFAULT;
  // sqlite3 -> recreate table
  // consider naming here between nullable(), acceptNull(), allowNull(), notNull(false)

});

Foreign keys can be handled with the code below. Note that the existing creation of foreign key constraints will require updating to provide the constraint name (in order to be dropped). A name option could be used here as well, so that like indexes these could be more general.

alterTable('articles', function(table) {

  table.alter('user_id').references('user.id');
  // mysql -> ALTER TABLE `articles` ADD CONSTRAINT `articles_user_id_fk` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`);
  // postgres -> ALTER TABLE "articles" ADD CONSTRAINT "articles_user_id_fk" FOREIGN KEY ("user_id") REFERENCES "users" ("id");
  // sqlite3 -> recreate table

  table.alter('column').references(null);
  // mysql -> ALTER TABLE `articles` DROP FOREIGN KEY `articles_user_id_fk`;
  // postgres -> ALTER TABLE "articles" DROP CONSTRAINT "articles_user_id_fk";
  // sqlite3 -> recreate table

});

A change of unique on a column would be done by creating an index and not through alter on a column:

alterTable('articles', function(table) {

  table.index('column', { unique: true });
  // all -> CREATE UNIQUE INDEX "articles_column_idx" ON "articles" ("column");

  table.dropIndex('column'); // nothing new here
  // mysql -> DROP INDEX `articles_column_idx` ON `articles`;
  // postgres -> DROP INDEX "articles_column_idx";

  try {
    table.alter('column').unique(); // throws for missing method
  }
  catch (e) {}

});