Specify table charset - Githubissues

I'm submitting a...

[ ] Bug report
[x] Feature request
[ ] Question

Current behavior

When creating a table using db-migrate's API (db.createTable) it appears it's not possible to specify the default charset.

Scenario: I have a table which I'd like to create a foreign key reference to. This is currently failing as the parent table is set to 'utf8' where as the newly created table via db-migrate is set to latin1

Expected behavior

Allow specifying charset for table creation

Minimal reproduction of the problem with instructions

Existing table:

CREATE TABLE `bm_web_servers` (
  `id` varchar(255) NOT NULL,
  `name` varchar(255) NOT NULL,
  `host` varchar(255) NOT NULL,
  `port` int(10) unsigned NOT NULL DEFAULT '3306',
  `database` varchar(255) NOT NULL,
  `user` varchar(255) NOT NULL,
  `password` varchar(255) NOT NULL DEFAULT '',
  `console` varbinary(16) NOT NULL,
  `tables` text NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `bm_web_servers_name_unique` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

Migration:

exports.up = async function (db) {
  await db.createTable('bm_web_appeal_states', {
    id: { type: 'int', notNull: true, primaryKey: true, autoIncrement: true },
    name: { type: 'string', notNull: true }
  })
  await db.createTable('bm_web_appeals', {
    id: { type: 'int', notNull: true, primaryKey: true, autoIncrement: true },
    server_id: {
      type: 'string',
      notNull: true,
      foreignKey: {
        name: 'bm_web_appeals_server_id_fk',
        table: 'bm_web_servers',
        mapping: 'id',
        rules: {
          onDelete: 'CASCADE',
          onUpdate: 'CASCADE'
        }
      }
    },
    actor_id: { type: 'binary', length: 16, notNull: true },
    assignee_id: { type: 'binary', length: 16, notNull: true },
    punishment_id: { type: 'int', notNull: true },
    punishment_type: { type: 'string', notNull: true },
    state_id: {
      type: 'int',
      notNull: true,
      foreignKey: {
        name: 'bm_web_appeals_state_id_fk',
        table: 'bm_web_appeal_states',
        mapping: 'id',
        rules: {
          onDelete: 'RESTRICT',
          onUpdate: 'CASCADE'
        }
      }
    },
    created: { type: 'int', length: 10, notNull: true },
    updated: { type: 'int', length: 10, notNull: true },
    reason: { type: 'text', notNull: true }
  })

  await db.addIndex('bm_web_appeals', 'bm_web_appeals_server_idx', ['server_id'])
  await db.addIndex('bm_web_appeals', 'bm_web_appeals_actor_idx', ['actor_id'])

  await db.insert('bm_web_appeal_states', ['id', 'name'], [1, 'Open'])
  await db.insert('bm_web_appeal_states', ['id', 'name'], [2, 'Assigned'])
  await db.insert('bm_web_appeal_states', ['id', 'name'], [3, 'Resolved'])
  await db.insert('bm_web_appeal_states', ['id', 'name'], [4, 'Rejected'])
}

exports.down = async function (db) {
  await db.dropTable('bm_web_appeals')
  await db.dropTable('bm_web_appeal_states')
}

exports._meta = {
  version: 1
}

Produces:

CREATE TABLE  `bm_web_appeal_states` (`id` INTEGER  PRIMARY KEY AUTO_INCREMENT NOT NULL, `name` VARCHAR (255) NOT NULL) ;
CREATE TABLE  `bm_web_appeals` (`id` INTEGER  PRIMARY KEY AUTO_INCREMENT NOT NULL, `server_id` VARCHAR (255) NOT NULL, `actor_id` BINARY (16) NOT NULL, `assignee_id` BINARY (16) NOT NULL, `punishment_id` INTEGER  NOT NULL, `punishment_type` VARCHAR (255) NOT NULL, `state_id` INTEGER  NOT NULL, `created` INTEGER (10) NOT NULL, `updated` INTEGER (10) NOT NULL, `reason` TEXT  NOT NULL) ;
ALTER TABLE `bm_web_appeals` ADD CONSTRAINT `bm_web_appeals_state_id_fk` FOREIGN KEY (`state_id`) REFERENCES `bm_web_appeal_states` (`id`) ON DELETE RESTRICT ON UPDATE CASCADE;
ALTER TABLE `bm_web_appeals` ADD CONSTRAINT `bm_web_appeals_server_id_fk` FOREIGN KEY (`server_id`) REFERENCES `bm_web_servers` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE `bm_web_appeals` ADD  INDEX `bm_web_appeals_server_idx` (`server_id`);
ALTER TABLE `bm_web_appeals` ADD  INDEX `bm_web_appeals_actor_idx` (`actor_id`);
INSERT INTO `bm_web_appeal_states` (`id`,`name`) VALUES (1,'Open');;
INSERT INTO `bm_web_appeal_states` (`id`,`name`) VALUES (2,'Assigned');;
INSERT INTO `bm_web_appeal_states` (`id`,`name`) VALUES (3,'Resolved');;
INSERT INTO `bm_web_appeal_states` (`id`,`name`) VALUES (4,'Rejected');;

ALTER TABLE `bm_web_appeals` ADD CONSTRAINT `bm_web_appeals_server_id_fk` FOREIGN KEY (`server_id`) REFERENCES `bm_web_servers` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;

Fails with

Error: Cannot add foreign key constraint Code: ER_CANNOT_ADD_FOREIGN

What is the motivation / use case for changing the behavior?

Environment


db-migrate version: 0.11.12
db-migrate driver with versions: mysql v2.2.0

Additional information:
- Node version: v14.17.5  
- Platform:  Mac, Linux, Windows

db-migrate / node-db-migrate