[FEATURE]: Customizable many relations

[arpadgabor]

Describe want to want

Relation support is cool. The fact that it only uses 1 single query is even better. However, right now, it's pretty limited in scope and the rules for how many is composed are not explained in the documentation (i.e., many relies on relationName, if you have more complex relations, or rather, not basic, you quickly hit limitations).

Example use case

Let's take an example. I have a table that contains features. Each feature can:

1. have subfeatures (using a parent_id column)
2. be related with other features using different rules:
   • compatibility
   • recommendation

2 is achieved with a junction table feature_metadata that has a source_id and target_id to relate 2 features, but also a few other extra columns is_compatible, is_recommending, etc. to define the type of relationship.

So each row is related like this:

feature.id  -> source_id [feature_metadata] target_id -> feature.id

or, for parent-child relationships:

feature.parent_id ---> feature.id

Desired query API

So now that I have my hypothetical tables, I want to query my data:

db.features.findMany({
  with: {
    parent: true,
    children: true,
    compatibleFeatures: true,
    recommendedFeatures: true
  }
})

Overall, this looks good. However there are a few problems.

The problems

While the parent relationship is easy to model with a one(), the others aren't so trivial.

At the moment, many "magically" finds the one relationship defined in the related table using relationshipName as key. However this is extremely limited.

Parent-child relationships

Edit: This has been fixed but not in a public release yet. Thanks!

First of all, the children. From my testing and inspection of the source, there is no way to model that relationship at the moment. Why? As many depends on one to exist in the related table, and the related table in this case is the same as the source table, any time you try to query for the many relation, you'll get either:

There are multiple relations with name "name" in table "features"

Or, if you specify a separate relationship name for one of the relations:

There is not enough information to infer relation "features.children"

This would easily be fixed if many relations were able to have their own references and fields options, but at this time, it doesn't.

Many-to-many with the same table

This is fortunately achievable, however it's not pretty and introduces extra unnecessary relations.

Basically the idea is that for each compatibleFeatures and recommendedFeatures relation, there needs to be an accompanying one inside the source table (i.e. features) and additionally a pair of source/target relations in the junction table (again, for each relation). This is how it would look:

features           junction
one(R_1)              one(R_1)
many(R_2)             one(R_2)

Where R_* is the relation name. Again, this needs to be done for each relation you need. This results in many additional relations (as you need +1 in features and +2 in junction for every relation).

A much better implementation of this would also be related to #607. If we could define through relations with customizable fields and references, we could enable much more sophisticated setups.

Other stuff

There's a lot of potential for the relations, and I think a more explicit way of defining them instead of relying on hidden implementations would open up more possibilities, for example, allowing users to create relations that also define additional filters directly there.

Objection.js has a pretty cool implementation for many-to-many using through and it also gives the ability to define extra properties that reside in the junction table, so extra properties will be included.

[dankochetov]

Thanks a lot for the thorough review! Let me answer from top to bottom:

1. Parent-child relationships are now possible, and can be tested using drizzle-orm@beta. Will be soon released to latest.
2. Allowing to specify the columns on the many relation side sounds useful, but it introduces a new level of complexity, because then you could define the other relation side with different set columns. We might think about it, and if you have ideas on how to implement it properly, please share.
3. Specifying many-to-many relations with through also provides good DX, but currently it's too much work to implement, so not a priority. Might get back to it in the future.
4. We've considered allowing to specify conditions on the relations, so it will probably be implemented at some point. Not a priority at the moment.

[arpadgabor]

Allowing to specify the columns on the many relation side sounds useful, but it introduces a new level of complexity, because then you could define the other relation side with different set columns. We might think about it, and if you have ideas on how to implement it properly, please share.

Yes I think there's a bit of a risk with possibly defining the relationships wrong, but I think this can be mitigated to some degree with the right documentation + good visual representations so even people with less SQL experience can get the gist of it.

I guess you probably decided to only use one and many for simplicity, instead of doing hasMany, belongsToMany, etc. type of relations. Though, I think having explicit naming for each type of relation might be better (even if they sometimes represent the same thing), especially since your access paterns may not require both-way querying all the time. For example, you could have a books table, where every book has many views. You won't be interested in fetching a single view and get it's associated book, but you probably will be interested in getting a book and all of it's views (this is just a simple example).

So I think having separately named relation types will make designing (and documenting through code) much easier, like:

const featureRelations = relation(features, ({ hasMany, belongsTo }) => ({
  dependencies: hasMany(junction, {
    fields: [features.id],
    references: [junction.foreignKey]
  }),

  parent: belongsTo(feature, {
    fields: [features.parent_id],
    references: [features.id]
  })
}))

Anyways, I was looking for possibly real-world examples that could guide future development. I think a good benchmark that could possibly be used is the GTFS specification. Specifically trying to model stops, routes, trips and stop_times. Why? Because if you want to get all the stops for a specific route, you need to go through stop_times and through trips. Personally I was only able to do this with multiple CTEs in a single query. This is a very specific use-case, I know, but I think it can be a good benchmark for people looking to model relations in a non-standard way, even if it's not possible to do the whole thing out of the box (it most likely isn't), having ways to model parts of it reliably is nice.

[MkrierPharmanity]

Allowing to specify the columns on the many relation side sounds useful, but it introduces a new level of complexity, because then you could define the other relation side with different set columns. We might think about it, and if you have ideas on how to implement it properly, please share.

Yes I think there's a bit of a risk with possibly defining the relationships wrong, but I think this can be mitigated to some degree with the right documentation + good visual representations so even people with less SQL experience can get the gist of it.

I guess you probably decided to only use one and many for simplicity, instead of doing hasMany, belongsToMany, etc. type of relations. Though, I think having explicit naming for each type of relation might be better (even if they sometimes represent the same thing), especially since your access paterns may not require both-way querying all the time. For example, you could have a books table, where every book has many views. You won't be interested in fetching a single view and get it's associated book, but you probably will be interested in getting a book and all of it's views (this is just a simple example).

So I think having separately named relation types will make designing (and documenting through code) much easier, like:

const featureRelations = relation(features, ({ hasMany, belongsTo }) => ({
  dependencies: hasMany(junction, {
    fields: [features.id],
    references: [junction.foreignKey]
  }),

  parent: belongsTo(feature, {
    fields: [features.parent_id],
    references: [features.id]
  })
}))

Anyways, I was looking for possibly real-world examples that could guide future development. I think a good benchmark that could possibly be used is the GTFS specification. Specifically trying to model stops, routes, trips and stop_times. Why? Because if you want to get all the stops for a specific route, you need to go through stop_times and through trips. Personally I was only able to do this with multiple CTEs in a single query. This is a very specific use-case, I know, but I think it can be a good benchmark for people looking to model relations in a non-standard way, even if it's not possible to do the whole thing out of the box (it most likely isn't), having ways to model parts of it reliably is nice.

any idea on how to do the book and view example right now with drizzle and relations ? its a very common use case and yet it seem impossible to do this in drizzle right now

[hirvesh]

Would love to have the many-to-many self referencing table via junction table implemented as well. Is it on the roadmap @dankochetov 🤔

[zivtamary]

Thanks a lot for the thorough review! Let me answer from top to bottom:

1. Parent-child relationships are now possible, and can be tested using drizzle-orm@beta. Will be soon released to latest.
2. Allowing to specify the columns on the many relation side sounds useful, but it introduces a new level of complexity, because then you could define the other relation side with different set columns. We might think about it, and if you have ideas on how to implement it properly, please share.
3. Specifying many-to-many relations with through also provides good DX, but currently it's too much work to implement, so not a priority. Might get back to it in the future.
4. We've considered allowing to specify conditions on the relations, so it will probably be implemented at some point. Not a priority at the moment.

is (1.) possible in the stable version now?

[benkraus]

I don't seem to see anything for 1 in stable or in beta? Am I missing something?

[arpadgabor]

@benkraus @zivtamary on my phone atm but this works in my project:

parent: one(features, {
    fields: [features.parentId],
    references: [features.id],
    relationName: 'subfeatures',
  }),
subfeatures: many(features, {
    relationName: 'subfeatures',
})

These are defined in the same relation declaration, for the same table.

[statusunknown418]

trying to add a self one-to-many relation but it's currently failing not enough data to infer relation I'm trying to work on one-to-many relations for the same table, any idea how to implement it? saw this is still open

// schema

export const users = mysqlTable("user", {
  id: varchar("id", { length: 255 }).notNull().primaryKey(),
  name: varchar("name", { length: 255 }),
  email: varchar("email", { length: 255 }).notNull(),
  emailVerified: timestamp("emailVerified", {
    mode: "date",
    fsp: 3,
  }).defaultNow(),
  image: varchar("image", { length: 255 }),
});

export const usersRelations = relations(users, ({ many }) => ({
  friends: many(users, { relationName: "friends" }), // error here
}));

[Mehdi-YC]

hi @statusunknown418 , did you find any slution , i kinda have the same problem even without self reference

export const user = pgTable('auth_user', {
    id:             varchar('id', {length: 15 }).primaryKey(),// change this when using custom user ids
    username:       varchar('username', {length: 55}),
    names:          varchar('names', { length: 255 }),
    lastNames:      varchar('last_names', { length: 255 })
});

export const userRelations = relations(user, ({ many }) => ({
    blog: many(blog),
  }));

Error: There is not enough information to infer relation "user.blog"

[Angelelz]

I wrote an example of a many-to-many self relation in this discord thread.

[henritouchard]

trying to add a self one-to-many relation but it's currently failing not enough data to infer relation I'm trying to work on one-to-many relations for the same table, any idea how to implement it? saw this is still open

// schema

export const users = mysqlTable("user", {
  id: varchar("id", { length: 255 }).notNull().primaryKey(),
  name: varchar("name", { length: 255 }),
  email: varchar("email", { length: 255 }).notNull(),
  emailVerified: timestamp("emailVerified", {
    mode: "date",
    fsp: 3,
  }).defaultNow(),
  image: varchar("image", { length: 255 }),
});

export const usersRelations = relations(users, ({ many }) => ({
  friends: many(users, { relationName: "friends" }), // error here
}));

Hey! I was stuck on this before. the problem is that you must create both many and one relation and both must have the same relationName

export const users = mysqlTable("user", {
  id: varchar("id", { length: 255 }).notNull().primaryKey(),
  name: varchar("name", { length: 255 }),
  email: varchar("email", { length: 255 }).notNull(),
  emailVerified: timestamp("emailVerified", {
    mode: "date",
    fsp: 3,
  }).defaultNow(),
  image: varchar("image", { length: 255 }),
  friendId: interger(friendId).references(():AnyPgColumn=> users.id) // <=== notice this key creation
});

export const usersRelations = relations(users, ({ many, one }) => ({
  friends: many(users, { relationName: "friends" }),
  friend: one(users, {   // Notice this update key
    fields: [users.friendId],
    references: [users.id],
    relationName: 'friends', // this name must be the same than the friends relation
  }
}));

Hope it helps