DT
Drizzle Team•2y ago
JT

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
JTOP•2y ago
FYI, it does connect fine because it creates the migrations table without issue
Dan
Dan•2y ago
@Andrii Sherman
Andrii Sherman
Andrii Sherman•2y 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
JTOP•2y ago
very nice thank you
Want results from more Discord servers?
Add your server