使用Drizzle ORM连接PlanetScale时外键约束不允许的问题

错误详情 #

执行npx drizzle-kit push:mysql时触发以下错误：

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/'
}

原Schema代码 #

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

解决方案 #

PlanetScale基于Vitess构建，不支持外键约束——外键会限制在线DDL操作的灵活性，影响数据库的可扩展性和可用性。针对这种情况，需要调整Schema并在应用层维护数据关联：

1. 修改Schema移除外键关联
   去掉playlists表中user_id字段的.references(() => users.id)配置，保留字段本身用于逻辑关联：

   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 }).notNull(), // 移除外键约束
   }, (playlists) => ({
       userIndex: uniqueIndex('user_idx').on(playlists.user_id)
   }));
   

2. 应用层维护数据完整性
   在业务代码中自行处理关联逻辑：

   • 删除用户前，先清理其关联的所有播放列表
   • 查询播放列表时，验证user_id对应的用户是否存在
   • 新增播放列表时，确保传入的user_id是有效用户ID

3. 重新同步数据库
   执行以下命令更新迁移并推送至PlanetScale：

   npx drizzle-kit generate:mysql
   npx drizzle-kit push:mysql
   

内容的提问来源于stack exchange，提问作者Braz