Drizzle Team•2y ago

Problem running a migration

Hello there, I'm currently facing an issue with a database migration. I have two simple tables defined in my code (shown below) that I'm attempting to migrate to a MariaDB server.

export const usersTable = mysqlTable('user', {
    id: int('id').primaryKey().autoincrement().notNull(),
    email: text('email').notNull(),
    password: varchar('password', { length: 255 }).notNull(),
  });

export const refreshTokensTable = mysqlTable('refresh_token', {
    id: int('id').primaryKey().autoincrement().notNull(),
    token: text('token').notNull(),
    user_id: int("user_id").references(() => usersTable.id).notNull(),
    created_at: timestamp("created_at").defaultNow(),
});

export const usersTable = mysqlTable('user', {
    id: int('id').primaryKey().autoincrement().notNull(),
    email: text('email').notNull(),
    password: varchar('password', { length: 255 }).notNull(),
  });

export const refreshTokensTable = mysqlTable('refresh_token', {
    id: int('id').primaryKey().autoincrement().notNull(),
    token: text('token').notNull(),
    user_id: int("user_id").references(() => usersTable.id).notNull(),
    created_at: timestamp("created_at").defaultNow(),
});

I generated the migration SQL from my code, and it looks good to me. When I run the SQL directly in my MariaDB server using a SQL editor, it runs without any issues.

CREATE TABLE `refresh_token` (
    `id` int AUTO_INCREMENT PRIMARY KEY NOT NULL,
    `token` text NOT NULL,
    `user_id` int NOT NULL,
    `created_at` timestamp DEFAULT (now())
);

CREATE TABLE `user` (
    `id` int AUTO_INCREMENT PRIMARY KEY NOT NULL,
    `email` text NOT NULL,
    `password` varchar(255) NOT NULL
);

ALTER TABLE `refresh_token` ADD CONSTRAINT `refresh_token_user_id_user_id_fk` FOREIGN KEY (`user_id`) REFERENCES `user`(`id`);

CREATE TABLE `refresh_token` (
    `id` int AUTO_INCREMENT PRIMARY KEY NOT NULL,
    `token` text NOT NULL,
    `user_id` int NOT NULL,
    `created_at` timestamp DEFAULT (now())
);

CREATE TABLE `user` (
    `id` int AUTO_INCREMENT PRIMARY KEY NOT NULL,
    `email` text NOT NULL,
    `password` varchar(255) NOT NULL
);

ALTER TABLE `refresh_token` ADD CONSTRAINT `refresh_token_user_id_user_id_fk` FOREIGN KEY (`user_id`) REFERENCES `user`(`id`);

However, when I run the migration in my code, I receive an error message:

Error: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use 
  sqlState: '42000',
  sqlMessage: "You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'CREATE TABLE user (\n" +
    '\tid int AUTO_INCREMENT PRIMARY KEY NOT NULL,\n' +
    "\temail text ...' at line 8"
}

Error: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use 
  sqlState: '42000',
  sqlMessage: "You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'CREATE TABLE user (\n" +
    '\tid int AUTO_INCREMENT PRIMARY KEY NOT NULL,\n' +
    "\temail text ...' at line 8"
}

I'm not sure why this error is occurring. Can you help me figure out what's wrong with my migration?

2 Replies

Dan•2y ago

Add breakpoints: true to your Drizzle config and regenerate the migrations

GabrielCOP•2y ago

thanks u so much, that works

Gaming

Programming

Problem running a migration