Modifiers

[dexx086]

First of all: thank you for this library. It seems to be a fresh air among trending overcomplicated query builders/orm libraries out there. So far, I really like the namings, the powerful typescript bindings, and the overall code quality (including so many tests) looks promising to me.

Just started to use, but what I noticed: I found no way to add specific modifiers to the builtin queries. For eg.: I would need to add a STRAIGHT_JOIN to a select statement. So far I've seen only the 'bottom' modifiers related to locking (forUpdate, etc).

Is there any option to add 'top' modifiers (like STRAIGHT_JOIN, SQL_NO_CACHE, etc), maybe even dialect-specific ones? Doesn't matter if could add only as raw SQL and not though dedicated methods, just the option is missing to add custom ones (and all dialects has quite a few specific ones which could be required in some corner cases).

[koskimas]

Thank you 🍻

Yes, we should add some way to add modifiers at the beginning of a select statement. Currently forUpdate and friends are methods. It would be logical to add these as methods too, but we'll quickly get a large number of methods. Maybe we should add a method like selectModifier(modifier) where modifier would be 'for update' | 'for share' | ... 'straight_join' | 'sql_no_cache' allowing all top and bottom modifiers. Then we could drop the forUpdate etc. methods.

[koskimas]

🤔 but then we couldn't have custom modifiers. Kysely wouldn't know where to put them. Maybe the method could accept a second argument when raw sql is given that specifies top or bottom.

Another problem with the selectModifier or modifySelect method is that it's not easy to find.

[koskimas]

An extremely hacky way to add these modifiers with the current version is to use raw sql:

db.selectFrom('person')
  .select([sql`SQL_NO_CACHE first_name`, 'last_name', 'age'])

[dexx086]

Thanks for your quick response. Typescript complains about the hacky way, I could only solve it by casting to 'any': .select([sql`SQL_NO_CACHE` as any, 'first_name', ....] Of course not the best way, but at least works!

The best would be to have better control over these modifiers. My first thoughts were about a modifier(type: T) method, where:

• T: 'for update' | 'for share' | ... as you mentioned too

And could have an overload method with modifier(type: 'custom', name: string, position: P), where:

• name (or maybe sql as we're talking about raw string?): name of the custom, specific modifier
• P: 'top' | 'bottom' => where to place it

Btw: removing all modifier-related methods maybe not necessary, the frequently used ones (distinct, forUpdate) could deserve a comfortable access with 'shortcut' methods, imho.

This way, the most common modifiers (that most of the SQL servers support) could be 'built in', but with the 'custom' option we could add any dialect specific ones too. And of course select/insert/update/delete commands have their own modifiers, but those should not be a problem as those are different query builders, could have different T options. Any thoughts about it, could work?

[koskimas]

Typescript complains about the hacky way, I could only solve it by casting to 'any':

Oh, that's right. Kysely needs to know the name of the selection. You need to use something like this

// Notice the first selection is inside the raw expression
.select([sql<string>`SQL_NO_CACHE first_name`.as('first_name'), 'last_name', ....]

[koskimas]

Whenever you pass raw SQL somewhere in kysely, it has to be done using the sql template tag. That way we don't concatenate input strings to the SQL without an explicit permission from the user. The method could look something like this

modifier(modifier: 'for update' | 'for share' | 'sql_no_cache')
modifier(modifier: RawBuilder<any>, position: 'top' | 'bottom')

You'd use it like this:

.modifier('for update')

Or if you pass in a raw snippet, it will require the second argument

.modifier(sql`some_unknown_modifier`, 'top')

[dexx086]

Thanks for thinking about it. It looks very usable to me :)

[koskimas]

Sorry for not working on this. I've been trying to prioritise things that benefit the most people. We need some way to do this, so I'll reopen this.

[koskimas]

https://github.com/koskimas/kysely/releases/tag/0.19.9