Ability to set comments and sql hints on queries

[ardabeyazoglu]

Currently, there is no way of adding block comments using query builder. A comment method would be very useful for data intensive, saas type of apps.

My Proposal

await db
     .selectFrom("my_table")
     .select(["col1", "col2"])
     .comment("a very nice query")
     .execute();

should compile as: select /* a very nice query */ col1, col2 from my_table or /* a very nice query */ insert into ... in case of non-readonly queries.

Why

• Arbitrary comments can be very useful for debugging. Setting user ids, shard numbers, or any app specific info can be used for analysis in query monitoring apps.
• Load balancers/proxies can make decisions based on pattern matching (proxysql, maxscale etc.). Sometimes information not included in the query itself is required for that.
• MySQL also provides a way to modify per-query settings and optimizer hints using special comments. For example SELECT /*+MAX_EXECUTION_TIME(1000) */ * FROM table would force query finish in 1 second or time out with error.

I have used all of these using comments in my older projects writing raw queries. It would be very efficient to integrate it to query builder.

---

If you could point me to the correct files/locations to add this feature, I could also work on it.

[koskimas]

.comment("*/ DELETE FROM users; /*")

Well I guess we could do our best to sanitize the comment and document it as a dangerous method.

[ardabeyazoglu]

Well, we could only allow certain characters. Alphanumeric plus a few special unharmful chars.. We could also name the method sth like "dangerouslyComment". What do you think?

Edit: A whitelist pattern like this [0-9a-zA-Z\s#_+,.;:()] would be safe and enough for most needs I think.

[igalklebanov]

Hey 👋

Have you tried modifyFront and modifyEnd?

This is very niche (community, prove me wrong with reactions to this issue), can (should) be done through an escape hatch, and built-in support doesn't seem to provide any real value.

[ardabeyazoglu]

@igalklebanov I didn't know about them. This would provide a solution, very similar to what i proposed. Thanks.