Drizzle TeamDT
Drizzle Team•3y 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();

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`);


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"
}


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?
Was this page helpful?