Theo's Typesafe Cult•12mo ago

Drizzle Foreign Key Constraints are not allowed

Can anyone lend me a hand with understanding this error message? I've got some relations in my schema that are causing the following error on db:push to planetscale:

Error: VT10001: foreign key constraints are not allowed
    at PromiseConnection.query (/home/hyhy/dev/modulation/node_modules/.pnpm/[email protected]/node_modules/drizzle-kit/index.cjs:35481:26)
    at Command.<anonymous> (/home/hyhy/dev/modulation/node_modules/.pnpm/[email protected]/node_modules/drizzle-kit/index.cjs:53292:33)
    at process.processTicksAndRejections (node:internal/process/task_queues:95:5) {
  code: 'ER_UNKNOWN_ERROR',
  errno: 1105,
  sql: 'ALTER TABLE `modulation_album` ADD CONSTRAINT `modulation_album_artist_id_modulation_artist_id_fk` FOREIGN KEY (`artist_id`) REFERENCES `modulation_artist`(`id`) ON DELETE no action ON UPDATE no action;',
  sqlState: 'HY000',
  sqlMessage: 'VT10001: foreign key constraints are not allowed'
}

Error: VT10001: foreign key constraints are not allowed
    at PromiseConnection.query (/home/hyhy/dev/modulation/node_modules/.pnpm/[email protected]/node_modules/drizzle-kit/index.cjs:35481:26)
    at Command.<anonymous> (/home/hyhy/dev/modulation/node_modules/.pnpm/[email protected]/node_modules/drizzle-kit/index.cjs:53292:33)
    at process.processTicksAndRejections (node:internal/process/task_queues:95:5) {
  code: 'ER_UNKNOWN_ERROR',
  errno: 1105,
  sql: 'ALTER TABLE `modulation_album` ADD CONSTRAINT `modulation_album_artist_id_modulation_artist_id_fk` FOREIGN KEY (`artist_id`) REFERENCES `modulation_artist`(`id`) ON DELETE no action ON UPDATE no action;',
  sqlState: 'HY000',
  sqlMessage: 'VT10001: foreign key constraints are not allowed'
}

Using the stock drizzle config:

import { type Config } from "drizzle-kit";

import { env } from "~/env";

export default {
  schema: "./src/server/db/schema.ts",
  driver: "mysql2",
  dbCredentials: {
    connectionString: env.DATABASE_URL,
  },
  tablesFilter: ["modulation_*"],
} satisfies Config;

import { type Config } from "drizzle-kit";

import { env } from "~/env";

export default {
  schema: "./src/server/db/schema.ts",
  driver: "mysql2",
  dbCredentials: {
    connectionString: env.DATABASE_URL,
  },
  tablesFilter: ["modulation_*"],
} satisfies Config;

Solution:

https://planetscale.com/blog/working-with-related-data-using-drizzle-and-planetscale looks like the one, trying it now

Emulating foreign key constraints with Drizzle relationships — Plan...

Learn how to build virtual relationships between tables in PlanetScale while using the Drizzle TypeScript ORM.

Jump to solution

4 Replies

hyhyOP•12mo ago

Relations in my schema in question, for example each episode has a mix:

export const episodes = mysqlTable(
  "episode",
  {
    id: bigint("id", { mode: "number" }).primaryKey().autoincrement(),
    name: varchar("name", { length: 256 }),
    mixId: bigint("mix_id", { mode: "number" }).references(() => mixes.id),
  },
  (episode) => ({
    nameIndex: index("name_idx").on(episode.name),
  }),
);

export const mixes = mysqlTable("mix", {
  id: bigint("id", { mode: "number" }).primaryKey().autoincrement(),
});

export const episodes = mysqlTable(
  "episode",
  {
    id: bigint("id", { mode: "number" }).primaryKey().autoincrement(),
    name: varchar("name", { length: 256 }),
    mixId: bigint("mix_id", { mode: "number" }).references(() => mixes.id),
  },
  (episode) => ({
    nameIndex: index("name_idx").on(episode.name),
  }),
);

export const mixes = mysqlTable("mix", {
  id: bigint("id", { mode: "number" }).primaryKey().autoincrement(),
});

or a many to many between albums and episodes

export const relAlbumsEpisodes = mysqlTable("rel_albums_episodes", {
  id: bigint("id", { mode: "number" }).primaryKey().autoincrement(),
  albumId: bigint("album_id", { mode: "number" }).references(() => albums.id),
  episodeId: bigint("episode_id", { mode: "number" }).references(() => episodes.id),
});

export const relAlbumsEpisodes = mysqlTable("rel_albums_episodes", {
  id: bigint("id", { mode: "number" }).primaryKey().autoincrement(),
  albumId: bigint("album_id", { mode: "number" }).references(() => albums.id),
  episodeId: bigint("episode_id", { mode: "number" }).references(() => episodes.id),
});

Assuming I'm either missing something in my config, or defining the relations incorrectly

Solution

hyhy•12mo ago

https://planetscale.com/blog/working-with-related-data-using-drizzle-and-planetscale looks like the one, trying it now

Emulating foreign key constraints with Drizzle relationships — Plan...

Learn how to build virtual relationships between tables in PlanetScale while using the Drizzle TypeScript ORM.

Rhys•12mo ago

If you use the MySQL 2 driver you need to set drizzle to emulate forgien keys

hyhyOP•12mo ago

i think this works correctly now but will have to see, it pushed up at least

export const relAlbumsEpisodes = mysqlTable("rel_albums_episodes", {
  id: serial("id").primaryKey(),
  albumId: int("album_id"),
  episodeId: int("episode_id"),
  createdAt: timestamp("created_at")
    .default(sql`CURRENT_TIMESTAMP`)
    .notNull(),
  updatedAt: timestamp("updatedAt").onUpdateNow(),
});

export const relAlbumsEpisodesRelations = relations(
  relAlbumsEpisodes,
  ({ one }) => ({
    artist: one(albums, {
      fields: [relAlbumsEpisodes.albumId],
      references: [albums.id],
    }),
    genre: one(episodes, {
      fields: [relAlbumsEpisodes.episodeId],
      references: [episodes.id],
    }),
  }),
);

export const relAlbumsEpisodes = mysqlTable("rel_albums_episodes", {
  id: serial("id").primaryKey(),
  albumId: int("album_id"),
  episodeId: int("episode_id"),
  createdAt: timestamp("created_at")
    .default(sql`CURRENT_TIMESTAMP`)
    .notNull(),
  updatedAt: timestamp("updatedAt").onUpdateNow(),
});

export const relAlbumsEpisodesRelations = relations(
  relAlbumsEpisodes,
  ({ one }) => ({
    artist: one(albums, {
      fields: [relAlbumsEpisodes.albumId],
      references: [albums.id],
    }),
    genre: one(episodes, {
      fields: [relAlbumsEpisodes.episodeId],
      references: [episodes.id],
    }),
  }),
);

Gaming

Programming

Drizzle Foreign Key Constraints are not allowed