K
Kysely•2y ago
Robin

Limit with a literal value

Hi folks, thanks for all your work on Kysely, it's great! I'm wondering how I can add a limit clause with a literal value, rather than a parameter. I have a strange use case, I'm querying a https://materialize.com/ database, which presents as postgres, but has different features. It also has some limitations, one of which is limit must be a literal value, not an expression or parameter. I figured out I can do this 
query.modifyEnd(sql`limit ${sql.raw(limit.toString())}`);
query.modifyEnd(sql`limit ${sql.raw(limit.toString())}`);
Which works, but it's a bit unwieldy. Is there a nice way to add extension methods to the query builder? The docs seem to discourage this, but I'm curious if there are any other ideas to make this nicer. I would love to have an api like this: 
query.rawLimit(limit)
query.rawLimit(limit)
The Streaming Database | Materialize
Materialize is a streaming SQL database for real-time applications, live dashboards, and streaming data pipelines. It provides the simplicity of SQL queries, but with millisecond-level latency for real-time data.
3 Replies
Robin
RobinOP•2y ago
I came up with this helper function, which works but doesn't follow the builder pattern the build in methods use. 
export function rawLimit<DB, TB extends keyof DB, O>(
  query: SelectQueryBuilder<DB, TB, O>,
  value: number
) {
  return query.modifyEnd(sql`limit ${sql.raw(value.toString())}`);
}
export function rawLimit<DB, TB extends keyof DB, O>(
  query: SelectQueryBuilder<DB, TB, O>,
  value: number
) {
  return query.modifyEnd(sql`limit ${sql.raw(value.toString())}`);
}
Igal
Igal•2y ago
Hey 👋 I think we should add an overload such as: 
limit(limit: number)
limit(expression: Expression<number>)
limit(limit: number)
limit(expression: Expression<number>)
Wherever limit exists. (e.g. SelectQueryBuilder). Do you want to submit an issue at https://github.com/kysely-org/kysely/issues ?
Robin
RobinOP•2y ago
That's a great idea, thanks! https://github.com/kysely-org/kysely/issues/534
GitHub
Support custom limit expressions · Issue #534 · kysely-org/kysely
I'm working with a database that doesn't support parameters in the limit clause, would it be possible to add an overload that takes an expression, so I can set the limit to a raw value?

Did you find this page helpful?