1

I write the following schema in the Drizzle recommended syntax in order to initialise my project's database in PlanetScale (MySql). After completing the migration process and trying to npx drizzle-kit push:mysql, I got the following error:

No config path provided, using default 'drizzle.config.ts'
...
Error: foreign key constraints are not allowed, see https://vitess.io/blog/2021-06-15-online-ddl-why-no-fk/
    at PromiseConnection.query (/Users/jcbraz/Projects/sound-scout-13/web-app/node_modules/drizzle-kit/index.cjs:34122:26)
    at Command.<anonymous> (/Users/jcbraz/Projects/sound-scout-13/web-app/node_modules/drizzle-kit/index.cjs:51859:33)
    at process.processTicksAndRejections (node:internal/process/task_queues:95:5) {
  code: 'ER_UNKNOWN_ERROR',
  errno: 1105,
  sql: 'ALTER TABLE `playlists` ADD CONSTRAINT `playlists_user_id_users_id_fk` FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE no action ON UPDATE no action;',
  sqlState: 'HY000',
  sqlMessage: 'foreign key constraints are not allowed, see https://vitess.io/blog/2021-06-15-online-ddl-why-no-fk/'
}

Here's the schema according to the Drizzle ORM documentation:

import { boolean, decimal, int, mysqlTable, text, timestamp, tinyint, uniqueIndex, varchar } from 'drizzle-orm/mysql-core'
import { type InferModel } from 'drizzle-orm';


export const users = mysqlTable('users', {
    id: varchar('id', { length: 50 }).primaryKey(),
    email: varchar('email', { length: 320 }).notNull(),
    first_name: varchar('first_name', { length: 50 }),
    last_name: varchar('first_name', { length: 50 }),
    credits: int('credits').notNull().default(5),
    stripeCustomerId: text('stripeCustomerId')
});

export const playlists = mysqlTable('playlists', {
    id: varchar('id', { length: 30 }).primaryKey(),
    created_at: timestamp('created_at').notNull().defaultNow(),
    user_id: varchar('user_id', { length: 50 }).references(() => users.id),
}, (playlists) => ({
    userIndex: uniqueIndex('user_idx').on(playlists.user_id)
}));

export const products = mysqlTable('products', {
    id: tinyint('id').autoincrement().primaryKey(),
    price: decimal('price', { precision: 3, scale: 2 }).notNull(),
    active: boolean('active').default(false),
    name: varchar('name', { length: 30 }),
    description: varchar('description', { length: 250 })
});

export type User = InferModel<typeof users>;
export type Playlist = InferModel<typeof playlists>;
export type Product = InferModel<typeof products>;

After writing the schema, I ran npx drizzle-kit generate:mysql which generated the migration and the correspondent .sql file successfully.

-- UPDATE --

Found this really good explanation on PlanetScale approach on Foreign keys: https://github.com/planetscale/discussion/discussions/74

Shadow
  • 33,525
  • 10
  • 51
  • 64
Braz
  • 73
  • 6
  • The error message includes a link to a PlanetScale blog, did you open the link and read the blog? – Bill Karwin Jun 26 '23 at 18:01
  • I read it however I didn't quite understand the way to fix the issue. By other words, by removing the foreign key from the "playlists" table, what's the new approach to take In order to make the relation between the two tables? 1 user can have N playlist. That implies a foreign key in the playlists table. – Braz Jun 26 '23 at 18:08
  • Well there can still be a relationship between the tables. It just can't be enforced with a constraint when you use PlanetScale. It's up to your application code to avoid creating orphaned rows. That's a risk, but it can't be helped, given their implementation. – Bill Karwin Jun 26 '23 at 18:11
  • LOL - I was briefly confused that there is an ORM called Drizzle that supports MySQL (among other brands of SQL database), given that I remember a fork of MySQL called Drizzle about 15 years ago: https://en.wikipedia.org/wiki/Drizzle_(database_server) But that's tangential to the issue of foreign keys and PlanetScale. – Bill Karwin Jun 26 '23 at 18:14
  • Given the case, since foreign keys are not supported in PlanetScale, maintaining the "user_id" declaration in the "playlists" table to "pretend a relationship" would be a viable option or would you suggest something else? @BillKarwin – Braz Jun 26 '23 at 18:21
  • 1
    Yes, that is in fact what many projects I've seen do. In theory, foreign key constraints are the correct way to implement referential integrity. But the truth is that many projects create a column with referencing values, but without the constraint. Partly this is a holdover from many years ago when MySQL users used MyISAM (which doesn't support foreign key constraints). Also because constriants are, well, constraining. Hard to do things like data clean ups. – Bill Karwin Jun 26 '23 at 19:39

0 Answers0