Knex migration with raw string

Question

I'm trying to run a knex migration with raw sql to create a table rather then writing it out in knex. What's going wrong here? I'm using es6 template strings to span multiple lines but it's not migrating.

Here's the error I'm getting

Error: ER_PARSE_ERROR: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CREATE TABLE mg_customer_entity_varchar ( value_id int(11) NOT NULL AU' at line 22

Here's my code:

require('babel-register')


exports.up = function(knex, Promise) {
  let raw = `
    CREATE TABLE \`mg_customer_entity\` (
      \`entity_id\` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Entity Id',
      \`entity_type_id\` smallint(5) unsigned NOT NULL DEFAULT '0' COMMENT 'Entity Type Id',
      \`attribute_set_id\` smallint(5) unsigned NOT NULL DEFAULT '0' COMMENT 'Attribute Set Id',
      \`website_id\` smallint(5) unsigned DEFAULT NULL COMMENT 'Website Id',
      \`email\` varchar(255) DEFAULT NULL COMMENT 'Email',
      \`group_id\` smallint(5) unsigned NOT NULL DEFAULT '0' COMMENT 'Group Id',
      \`increment_id\` varchar(50) DEFAULT NULL COMMENT 'Increment Id',
      \`store_id\` smallint(5) unsigned DEFAULT '0' COMMENT 'Store Id',
      \`created_at\` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'Created At',
      \`updated_at\` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT 'Updated At',
      \`is_active\` smallint(5) unsigned NOT NULL DEFAULT '1' COMMENT 'Is Active',
      \`disable_auto_group_change\` smallint(5) unsigned NOT NULL DEFAULT '0' COMMENT 'Disable automatic group change based on VAT ID',
      PRIMARY KEY (\`entity_id\`),
      UNIQUE KEY \`UNQ_MG_CUSTOMER_ENTITY_EMAIL_WEBSITE_ID\` (\`email\`,\`website_id\`),
      KEY \`IDX_mg_CUSTOMER_ENTITY_STORE_ID\` (\`store_id\`),
      KEY \`IDX_mg_CUSTOMER_ENTITY_ENTITY_TYPE_ID\` (\`entity_type_id\`),
      KEY \`IDX_mg_CUSTOMER_ENTITY_EMAIL_WEBSITE_ID\` (\`email\`,\`website_id\`),
      KEY \`IDX_mg_CUSTOMER_ENTITY_WEBSITE_ID\` (\`website_id\`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Customer Entity';

    CREATE TABLE \`mg_customer_entity_varchar\` (
      \`value_id\` int(11) NOT NULL AUTO_INCREMENT COMMENT 'Value Id',
      \`entity_type_id\` smallint(5) unsigned NOT NULL DEFAULT '0' COMMENT 'Entity Type Id',
      \`attribute_id\` smallint(5) unsigned NOT NULL DEFAULT '0' COMMENT 'Attribute Id',
      \`entity_id\` int(10) unsigned NOT NULL DEFAULT '0' COMMENT 'Entity Id',
      \`value\` varchar(255) DEFAULT NULL COMMENT 'Value',
      PRIMARY KEY (\`value_id\`),
      UNIQUE KEY \`UNQ_mg_CUSTOMER_ENTITY_VARCHAR_ENTITY_ID_ATTRIBUTE_ID\` (\`entity_id\`,\`attribute_id\`),
      KEY \`IDX_mg_CUSTOMER_ENTITY_VARCHAR_ENTITY_TYPE_ID\` (\`entity_type_id\`),
      KEY \`IDX_mg_CUSTOMER_ENTITY_VARCHAR_ATTRIBUTE_ID\` (\`attribute_id\`),
      KEY \`IDX_mg_CUSTOMER_ENTITY_VARCHAR_ENTITY_ID\` (\`entity_id\`),
      KEY \`IDX_mg_CUSTOMER_ENTITY_VARCHAR_ENTITY_ID_ATTRIBUTE_ID_VALUE\` (\`entity_id\`,\`attribute_id\`,\`value\`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Customer Entity Varchar';
  `
  return knex.schema.raw(raw)
};

exports.down = function(knex, Promise) {
  return knex.schema
    .dropTableIfExists('mg_customer_entity')
    .dropTableIfExists('mg_customer_entity_varchar')
};

Answer 1

1. I wasn't using babel-register correctly
2. had to change updated_at default from '0000-00-00 00:00:00' to CURRENT_TIMESTAMP

migrations/20160908144316_beerhawkAuth.js

require('babel-register')
let value = require('../migrations-es6/20160908144316_beerhawkAuth.js')
exports.up = value.up
exports.down = value.down

migrations-es6/20160908144316_beerhawkAuth.js

export let up = function(knex, Promise) {
  let create_mg_customer_entity = `
  CREATE TABLE \`mg_customer_entity\` (
    \`entity_id\` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Entity Id',
    \`entity_type_id\` smallint(5) unsigned NOT NULL DEFAULT '0' COMMENT 'Entity Type Id',
    \`attribute_set_id\` smallint(5) unsigned NOT NULL DEFAULT '0' COMMENT 'Attribute Set Id',
    \`website_id\` smallint(5) unsigned DEFAULT NULL COMMENT 'Website Id',
    \`email\` varchar(255) DEFAULT NULL COMMENT 'Email',
    \`group_id\` smallint(5) unsigned NOT NULL DEFAULT '0' COMMENT 'Group Id',
    \`increment_id\` varchar(50) DEFAULT NULL COMMENT 'Increment Id',
    \`store_id\` smallint(5) unsigned DEFAULT '0' COMMENT 'Store Id',
    \`created_at\` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'Created At',
    \`updated_at\` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Updated At',
    \`is_active\` smallint(5) unsigned NOT NULL DEFAULT '1' COMMENT 'Is Active',
    \`disable_auto_group_change\` smallint(5) unsigned NOT NULL DEFAULT '0' COMMENT 'Disable automatic group change based on VAT ID',
    PRIMARY KEY (\`entity_id\`),
    UNIQUE KEY \`UNQ_MG_CUSTOMER_ENTITY_EMAIL_WEBSITE_ID\` (\`email\`,\`website_id\`),
    KEY \`IDX_mg_CUSTOMER_ENTITY_STORE_ID\` (\`store_id\`),
    KEY \`IDX_mg_CUSTOMER_ENTITY_ENTITY_TYPE_ID\` (\`entity_type_id\`),
    KEY \`IDX_mg_CUSTOMER_ENTITY_EMAIL_WEBSITE_ID\` (\`email\`,\`website_id\`),
    KEY \`IDX_mg_CUSTOMER_ENTITY_WEBSITE_ID\` (\`website_id\`)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Customer Entity';
  `
  let create_mg_customer_entity_varchar = `
  CREATE TABLE \`mg_customer_entity_varchar\` (
    \`value_id\` int(11) NOT NULL AUTO_INCREMENT COMMENT 'Value Id',
    \`entity_type_id\` smallint(5) unsigned NOT NULL DEFAULT '0' COMMENT 'Entity Type Id',
    \`attribute_id\` smallint(5) unsigned NOT NULL DEFAULT '0' COMMENT 'Attribute Id',
    \`entity_id\` int(10) unsigned NOT NULL DEFAULT '0' COMMENT 'Entity Id',
    \`value\` varchar(255) DEFAULT NULL COMMENT 'Value',
    PRIMARY KEY (\`value_id\`),
    UNIQUE KEY \`UNQ_mg_CUSTOMER_ENTITY_VARCHAR_ENTITY_ID_ATTRIBUTE_ID\` (\`entity_id\`,\`attribute_id\`),
    KEY \`IDX_mg_CUSTOMER_ENTITY_VARCHAR_ENTITY_TYPE_ID\` (\`entity_type_id\`),
    KEY \`IDX_mg_CUSTOMER_ENTITY_VARCHAR_ATTRIBUTE_ID\` (\`attribute_id\`),
    KEY \`IDX_mg_CUSTOMER_ENTITY_VARCHAR_ENTITY_ID\` (\`entity_id\`),
    KEY \`IDX_mg_CUSTOMER_ENTITY_VARCHAR_ENTITY_ID_ATTRIBUTE_ID_VALUE\` (\`entity_id\`,\`attribute_id\`,\`value\`)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Customer Entity Varchar';
  `
  return knex.schema
  .raw(create_mg_customer_entity)
  .raw(create_mg_customer_entity_varchar)
};

export let down = function(knex, Promise) {
  return knex.schema
    .dropTableIfExists('mg_customer_entity')
    .dropTableIfExists('mg_customer_entity_varchar')
};