Kysely•13mo ago

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•13mo 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•13mo 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•13mo 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?

Gaming

Programming

Limit with a literal value