[BUG]: Foreign key constraint incompatible

[rajatbarman]

What version of drizzle-orm are you using?

0.23.2

Describe the Bug

package.json

"drizzle-kit": "^0.17.1",
"drizzle-orm": "^0.23.2",

Given the schema -

export const users = mysqlTable("users", {
  id: serial("id").primaryKey(),
 });

export const tokens = mysqlTable("tokens", {
  id: serial("id").primaryKey(),
  userId: int('user_id').references(() => users.id).notNull(),
});

drizzle-kit generates

CREATE TABLE `users` (
    `id` serial AUTO_INCREMENT PRIMARY KEY NOT NULL,
);

CREATE TABLE `tokens` (
    `id` serial AUTO_INCREMENT PRIMARY KEY NOT NULL,
    `user_id` int NOT NULL,
);

ALTER TABLE tokens ADD CONSTRAINT tokens_user_id_users_id_fk FOREIGN KEY (`user_id`) REFERENCES users(`id`) ;

where the query for adding foreign key constraint fails for the reason of incompatible types as users.id is actually serial and tokens.user_id is int.

Now the way that this works is if I do the following in my schema -

export const users = mysqlTable("users", {
  id: int("id").autoincrement().notNull().primaryKey(),
 });

export const tokens = mysqlTable("tokens", {
  id: int("id").autoincrement().notNull().primaryKey(),
  userId: int('user_id').references(() => users.id).notNull(),
});

where I replace serial() with int().autoincrement().notNull()

and the documentation for drizzle-orm illustrates the use of foreign key in this way -

// db.ts
import {
  int,
  mysqlEnum,
  mysqlTable,
  serial,
  uniqueIndex,
  varchar,
} from 'drizzle-orm/mysql-core';

// declaring enum in database
export const countries = mysqlTable('countries', {
  id: serial('id').primaryKey(),
  name: varchar('name', { length: 256 }),
}, (countries) => ({
  nameIndex: uniqueIndex('name_idx').on(countries.name),
}));

export const cities = mysqlTable('cities', {
  id: serial('id').primaryKey(),
  name: varchar('name', { length: 256 }),
  countryId: int('country_id').references(() => countries.id),
  popularity: mysqlEnum('popularity', ['unknown', 'known', 'popular']),
});

which doesn't work.

So I believe you can figure this out by 1) Changing illustration in documentation 2) Promote (in documentation) using bigint().autoincrement().notNull() for ids as they can easily be related by a foreign key.

I can't really think of a programmatic solution for this for you guys, but maybe there is something you can do while generating SQL.

[AndriiSherman]

@rajatbarman That is actually super helpful! Thanks a lot for this case. Will handle this part while migration generation and suggest better options that will work Will change docs as well

[carbontwelve]

I was just caught out by this myself.