[FEATURE]: Support Polymorphic Association

[leonard-henriquez]

Describe what you want

I'm looking for a Typesafe ORM that support for polymorphic associations.

To give a concrete example:
I have a Comment model. I need this model to be associated with both Article and Photo models.
Instead of creating separate tables or associations for comments on articles and comments on photos, I would prefer a unified approach where a single Comment can belong to either an Article or a Photo.

Most ORMs (and query builders) except Prisma support polymorphic association.
Prisma is not taking interest in solving this issue: prisma/prisma#1644.
If you plan on implementing Prisma, that would be a killer feature that would be a reason in itself to use Drizzle.
A few teams have mentioned it explicitly in that issue.

Do you plan to support and document this feature in the near future ?

[luxaritas]

Is this a duplicate of #207?

[leonard-henriquez]

@luxaritas yes it is ! However I didn't find it because the title is not accurate and the issue doesn't have a description.
@dankochetov Could you update the original issue (#207) so we can close this one ?

[Angelelz]

I worked around this by having nullable FKs in the Comment table for Article and Photo, say an article_id and a photo_id.
Of course you need to be mindful when you're doing your joins but it works pretty good.
Your Comments relation might look like this:

export const commentRelations = relations(comments, ({ one }) => ({
  user: one(users, {
    fields: [comments.user_id],
    references: [users.id]
  }),
  article: one(articles, {
    fields: [comments.article_id],
    references: [articles.id]
  }),
  photo: one(photos, {
    fields: [comments.photo_id],
    references: [photos.id]
  })
}));

I'm curious, how would you do polymorphic association in plain SQL?

[luxaritas]

The best approach I've seen which maintains the ability to have foreign keys is by adding a "commentable' table. That is, article and photo both have a foreign key (1:1 relation) to commentable, and comment has a foreign key (many:1) relation to commentable.

[Angelelz]

I see what you mean, Comment would have a not null FK. But in the approach I used, I never lost the ability to have foreign keys, even with on update and on delete. It's just that Comments would have several FKs columns, one for each of the other tables that needs comments.

[luxaritas]

Yep, that's true - though the more possible tables you add, the sparser the table becomes - not necessarily desirable (namely in terms of data modeling/efficiency/ensuring integrity given the constraint exactly one must be not-null)

[pspeter3]

Would it be possible to do something like Rails Polymorphic operations where you could have a type? https://guides.rubyonrails.org/association_basics.html#polymorphic-associations.

[pspeter3]

Would the configuration of the column type need to be on the one function call of the relation and just take multiple tables?

[CanRau]

Just now realized that Drizzle doesn't yet seem to have first class support for polymorphism, while trying to get my schema going.

I also have a commenting table which references to more than just 2 other tables so defining nullable fields for each would be really messy, also I have other tables like taggin (for tags) and likes which are polymophic.

[Angelelz]

The question I have for this feature request is what does it look like? I understand at the high level what is needed but I don't see what needs to happen in drizzle to support it?
I have something similar working in one of my projects and I mentioned before how I did it.
I also took a look at the way that was implemented in ruby on rails, and I spun up a quick db to test. See how I implemented the same table to hold comments for 3 different other tables:

// User section
export const users = mysqlTable("users", {
  id: int("id").autoincrement().primaryKey(),
  name: text("name").notNull(),
  createdAt: timestamp("created_at", { fsp: 3, mode: "string" }).default(
    sql`current_timestamp(3)`,
  ),
  commentType: varchar("comment_type", { length: 256 }).default(sql`'user'`),
});

export const usersRelations = relations(users, ({ many, one }) => ({
  comments: many(comments),
}));

// Country section
export const countries = mysqlTable(
  "countries",
  {
    id: int("id").autoincrement().primaryKey(),
    name: varchar("name", { length: 256 }),
    createdAt: datetime("created_at", { fsp: 3 }).default(
      sql`current_timestamp(3)`,
    ),
    commentType: varchar("comment_type", { length: 256 }).default(
      sql`'country'`,
    ),
  }
);

export const countriesRelations = relations(countries, ({ many }) => ({
  comments: many(comments),
}));

// City section
export const cities = mysqlTable("cities", {
  id: int("id").autoincrement().primaryKey(),
  name: varchar("name", { length: 256 }),
  createdAt: datetime("created_at", { fsp: 3 }).default(
    sql`current_timestamp(3)`,
  ),
  commentType: varchar("comment_type", { length: 256 }).default(sql`'city'`),
});

export const citiesRelations = relations(cities, ({ many }) => ({
  comments: many(comments),
}));

// The important part the comments. Notice how the commentableType is a enum, will have intelisense
export const comments = mysqlTable("comments", {
  id: int("id").autoincrement().primaryKey(),
  content: text("content").notNull(),
  commentableId: int("commentable_id").notNull(),
  commentableType: mysqlEnum("commentable_type", ["user", "city", "country"]),
});

export const commentRelations = relations(comments, ({ one }) => ({
  user: one(users, {
    fields: [comments.commentableId, comments.commentableType],
    references: [users.id, users.commentType],
  }),
  city: one(cities, {
    fields: [comments.commentableId, comments.commentableType],
    references: [cities.id, cities.commentType],
  }),
  country: one(countries, {
    fields: [comments.commentableId, comments.commentableType],
    references: [countries.id, countries.commentType],
  }),
}));

I tested insertion, selection and update, everything works as expected.
When inserting comments, your LSP will suggest the commentableType, and when selecting, you can use the commentableType to select the comments from whatever table you want. For example, the following query will return a user with it's comments:

const userWithComments = await db.query.users.findFirst({
  where: eq(users.id, 1),
  with: {
    comments: true,
  },
});

Both ways of doing it will have it's pros and cons, using this method, your comments table will be compact, but it won't have database level referential integrity. Doing it the other way, you table will be more sparse, but you keep referential integrity.

If somebody knows a way to have different foreign keys on the same column, referencing different tables, I would like to know.

[luxaritas]

My previous comment as well as my comments in #207 describe an alternative that maintains referential integrity without a sparse table, with the limitation of adding additional intermediate table(s)

[Angelelz]

I didn't test that, but I also don't see what limitation Drizzle has that prevents anybody from implementing it.

[luxaritas]

Right - I agree, I'm not sure if there's anything to do (aside from unions for some cases, which is coming)

[luxaritas]

The only other thing that maybe comes to mind is if the RQB was able to support the type field implicitly at runtime without the additional "static" column