my avatar Bahaa Zidan
Search
/

Self Referencing Foreign Key in Drizzle ORM

Published on Self Referencing Foreign Key in Drizzle ORM hero image

I was implementing an infinitely nested commenting feature in my project today when I ran into this little problem. You see, in Drizzle ORM whenever you want to declare a column as a foreign key, you usually need to chain a referencing call to the column defenition like this:

export const website = sqliteTable('website', {
	id: integer('id').primaryKey(),
	ownerId: text('owner_id')
		.notNull()
		.references(() => user.id, { onDelete: 'cascade' }),
	name: text().notNull(),
});

As you can see, the ownerId is a foreign key that references the users table. But what if we need to a foreign key that references the primary key of the same table ?

import { foreignKey } from 'drizzle-orm/sqlite-core';
 
export const comment = sqliteTable(
	'comment',
	{
		id: integer('id').primaryKey(),
		content: text('content').notNull(),
		/** OTHER FIELDS */
		parentId: integer('parent_id'),
	},
	(self) => [
		foreignKey({
			columns: [self.parentId],
			foreignColumns: [self.id],
		}).onDelete('cascade'),
	]
);

If you want to do a self reference, due to a TypeScript limitations you will have to either explicitly set return type for reference callback or use a standalone foreignKey operator.

Drizzle ORM Docs