migration on mysql errors

I've written this migrator:

import { migrate } from 'drizzle-orm/mysql2/migrator';
import { drizzle } from 'drizzle-orm/mysql2';
import mysql from 'mysql2/promise';
import * as dotenv from 'dotenv';
dotenv.config();
const dbConfig = new URL(process.env.DRIZZLE_DATABASE || '');

async function main() {
    const con = await mysql.createConnection({
        host: dbConfig.hostname,
        port: parseInt(dbConfig.port),
        user: dbConfig.username,
        password: dbConfig.password,
        database: dbConfig.pathname.slice(1),
    });

    const db = drizzle(con);
    await migrate(db, { migrationsFolder: 'drizzle/migrations' });
    await con.end();
}
main();

import { migrate } from 'drizzle-orm/mysql2/migrator';
import { drizzle } from 'drizzle-orm/mysql2';
import mysql from 'mysql2/promise';
import * as dotenv from 'dotenv';
dotenv.config();
const dbConfig = new URL(process.env.DRIZZLE_DATABASE || '');

async function main() {
    const con = await mysql.createConnection({
        host: dbConfig.hostname,
        port: parseInt(dbConfig.port),
        user: dbConfig.username,
        password: dbConfig.password,
        database: dbConfig.pathname.slice(1),
    });

    const db = drizzle(con);
    await migrate(db, { migrationsFolder: 'drizzle/migrations' });
    await con.end();
}
main();

and tried to run this migration, which was generated by drizzle-kit:

CREATE TABLE `users` (
    `id` serial AUTO_INCREMENT PRIMARY KEY NOT NULL,
    `createdAt` timestamp NOT NULL DEFAULT (now()),
    `updatedAt` timestamp NOT NULL DEFAULT (now()),
    `username` varchar(60) NOT NULL,
    `email` varchar(255) NOT NULL,
    `realName` varchar(60) NOT NULL,
    `password` varchar(255) NOT NULL DEFAULT 'no-password-specified',
    `passwordType` enum('bcrypt') NOT NULL DEFAULT 'bcrypt',
    `useAsDisplayName` enum('username','email','realName') NOT NULL DEFAULT 'username',
    `admin` boolean NOT NULL DEFAULT false
);

CREATE UNIQUE INDEX usernameIndex ON users (`username`);
CREATE UNIQUE INDEX emailIndex ON users (`email`);

CREATE TABLE `users` (
    `id` serial AUTO_INCREMENT PRIMARY KEY NOT NULL,
    `createdAt` timestamp NOT NULL DEFAULT (now()),
    `updatedAt` timestamp NOT NULL DEFAULT (now()),
    `username` varchar(60) NOT NULL,
    `email` varchar(255) NOT NULL,
    `realName` varchar(60) NOT NULL,
    `password` varchar(255) NOT NULL DEFAULT 'no-password-specified',
    `passwordType` enum('bcrypt') NOT NULL DEFAULT 'bcrypt',
    `useAsDisplayName` enum('username','email','realName') NOT NULL DEFAULT 'username',
    `admin` boolean NOT NULL DEFAULT false
);

CREATE UNIQUE INDEX usernameIndex ON users (`username`);
CREATE UNIQUE INDEX emailIndex ON users (`email`);

And I get this error:

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 UNIQUE INDEX usernameIndex ON users (`username`);
CREATE UNIQUE INDEX ema' at line 14
    at PromiseConnection.query (/Users/jtsmith/ving/.output/drizzle/migrate.js:18762:26)
    at MySql2PreparedQuery.<anonymous> (/Users/jtsmith/ving/.output/drizzle/migrate.js:1263:32)
    at Generator.next (<anonymous>)
    at /Users/jtsmith/ving/.output/drizzle/migrate.js:1220:71
    at new Promise (<anonymous>)
    at __awaiter (/Users/jtsmith/ving/.output/drizzle/migrate.js:1202:14)
    at MySql2PreparedQuery.execute (/Users/jtsmith/ving/.output/drizzle/migrate.js:1258:16)
    at MySql2Session.execute (/Users/jtsmith/ving/.output/drizzle/migrate.js:1094:82)
    at MySqlDialect.<anonymous> (/Users/jtsmith/ving/.output/drizzle/migrate.js:871:33)
    at Generator.next (<anonymous>) {
  code: 'ER_PARSE_ERROR',
  errno: 1064,
  sql: 'CREATE TABLE `users` (\n' +
    '\t`id` serial AUTO_INCREMENT PRIMARY KEY NOT NULL,\n' +
    '\t`createdAt` timestamp NOT NULL DEFAULT (now()),\n' +
    '\t`updatedAt` timestamp NOT NULL DEFAULT (now()),\n' +
    '\t`username` varchar(60) NOT NULL,\n' +
    '\t`email` varchar(255) NOT NULL,\n' +
    '\t`realName` varchar(60) NOT NULL,\n' +
    "\t`password` varchar(255) NOT NULL DEFAULT 'no-password-specified',\n" +
    "\t`passwordType` enum('bcrypt') NOT NULL DEFAULT 'bcrypt',\n" +
    "\t`useAsDisplayName` enum('username','email','realName') NOT NULL DEFAULT 'username',\n" +
    '\t`admin` boolean NOT NULL DEFAULT false\n' +
    ');\n' +
    '\n' +
    'CREATE UNIQUE INDEX usernameIndex ON users (`username`);\n' +
    'CREATE UNIQUE INDEX emailIndex ON users (`email`);',
  sqlState: '42000',
  sqlMessage: "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 UNIQUE INDEX usernameIndex ON users (`username`);\n" +
    "CREATE UNIQUE INDEX ema' at line 14"
}

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 UNIQUE INDEX usernameIndex ON users (`username`);
CREATE UNIQUE INDEX ema' at line 14
    at PromiseConnection.query (/Users/jtsmith/ving/.output/drizzle/migrate.js:18762:26)
    at MySql2PreparedQuery.<anonymous> (/Users/jtsmith/ving/.output/drizzle/migrate.js:1263:32)
    at Generator.next (<anonymous>)
    at /Users/jtsmith/ving/.output/drizzle/migrate.js:1220:71
    at new Promise (<anonymous>)
    at __awaiter (/Users/jtsmith/ving/.output/drizzle/migrate.js:1202:14)
    at MySql2PreparedQuery.execute (/Users/jtsmith/ving/.output/drizzle/migrate.js:1258:16)
    at MySql2Session.execute (/Users/jtsmith/ving/.output/drizzle/migrate.js:1094:82)
    at MySqlDialect.<anonymous> (/Users/jtsmith/ving/.output/drizzle/migrate.js:871:33)
    at Generator.next (<anonymous>) {
  code: 'ER_PARSE_ERROR',
  errno: 1064,
  sql: 'CREATE TABLE `users` (\n' +
    '\t`id` serial AUTO_INCREMENT PRIMARY KEY NOT NULL,\n' +
    '\t`createdAt` timestamp NOT NULL DEFAULT (now()),\n' +
    '\t`updatedAt` timestamp NOT NULL DEFAULT (now()),\n' +
    '\t`username` varchar(60) NOT NULL,\n' +
    '\t`email` varchar(255) NOT NULL,\n' +
    '\t`realName` varchar(60) NOT NULL,\n' +
    "\t`password` varchar(255) NOT NULL DEFAULT 'no-password-specified',\n" +
    "\t`passwordType` enum('bcrypt') NOT NULL DEFAULT 'bcrypt',\n" +
    "\t`useAsDisplayName` enum('username','email','realName') NOT NULL DEFAULT 'username',\n" +
    '\t`admin` boolean NOT NULL DEFAULT false\n' +
    ');\n' +
    '\n' +
    'CREATE UNIQUE INDEX usernameIndex ON users (`username`);\n' +
    'CREATE UNIQUE INDEX emailIndex ON users (`email`);',
  sqlState: '42000',
  sqlMessage: "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 UNIQUE INDEX usernameIndex ON users (`username`);\n" +
    "CREATE UNIQUE INDEX ema' at line 14"
}

Now each of the commands individually inside that migration work. So I think it's croaking on the fact that all 3 lines are being sent at once. Any thoughts on how to get around this?

4 Replies

JT•17mo ago

FYI, it does connect fine because it creates the migrations table without issue

bloberenober•17mo ago

@Andrii Sherman

Andrii Sherman•17mo ago

Just add multipleStatements: true to connection config mysql2 is handling multiple statements execution with this config Will try to mark is properly in new docs we are building. Getting this question often lately

JT•17mo ago

very nice thank you

Gaming

Programming

migration on mysql errors