[Feature] A withGraph function

A withGraph function that can automatically create join queries using a relations object defined on the Kysely instance. It would be similar to ObjectionJS's withGraphJoined.

This may not be the best fit for the kysely core itself, however, it may be suitable as a plugin?

I was thinking something like this

const db = new Kysely<DB>({
    dialect: <dialect>,
    plugins: [
        new KyselyGraph({
            profiles: { //table name
                followers: {
                    type: 'ManyToManyRelation',
                    from: 'profiles.id',
                    through: {
                        from: 'following.following_id',
                        to: 'following.follower_id'
                    },
                    to: 'profiles.id' //Possibly use the table name here to do a recursive type definition?
                }
            }
        })
    ]
})

You would just feed in the relations you want to traverse.

Eg. This would get the followers of followers.

db.selectFrom('profiles').withGraph({
   followers: {
      followers: true
   }
}).selectAll()

Using LATERAL joins would also allow you to do

db.selectFrom('profiles').withGraph({
   followers: {
      followers: true
   }
})
.modifyGraph('followers', (qb) => qb.limit(10))
.modifyGraph('followers:followers', (qb) => qb.limit(5))

It would work in a similar way to ObjectionJS, where it would alias and prefix the columns on the query to then be able to deep merge the results behind the scenes.

I'm not exactly sure how to do this in a type-safe way while in a KyselyPlugin. From what I am seeing (I may be wrong), plugins do not have access to the current kysely instance and there's no direct API to extend the query builder from them.

This feature would be amazing for people that are migrating from Objection and would simplify these complex join queries.

As you said, this is not something that would be added to the core.

To be able to implement this using a plugin the plugin interface would need the ability to change the type of each query builder. If you install a plugin, it would have a mechanism for intercepting each query builder and wrapping it with additional methods in addition to changing it's type globally.

Unfortunately implementing this is probably too difficult. What would happen with multiple plugins? The subsequent plugins would need to know the type the previous plugins gave to the query builders. I don't think this is doable. If it was, the plugins would complicate the types enormously even when they didn't augment the query builder types.

You are left with the last option: implement a new package on top of kysely.

kysely-org / kysely

[Feature] A withGraph function #309