search

kysely-org / kysely

A type-safe typescript SQL query builder
https://kysely.dev
MIT License
10.43k stars 266 forks source link

How can I bind a compiled query with it's parameters ? #1107

Closed HendrixString closed 1 month ago

HendrixString commented 1 month ago

Hi,

Given a CompiledQuery object,

How can I bind the parameters into the sql to produce a static and rendered output native query ?

koskimas commented 1 month ago

There's nothing like that in kysely and you should never execute a query built like that. That's like db programming 101. You'll get fired if you do that 😅

igalklebanov commented 1 month ago

Hey 👋

If you must (e.g. some relational database/API doesn't support parameters), and understand the risks, you have two options:

  1. you can implement a custom dialect where you override the appendValue method in your query compiler to call appendImmediateValue instead.
  2. copy the implementation of ImmediateValuePlugin from the source code: https://github.com/kysely-org/kysely/tree/master/src/plugin/immediate-value. Note, this is an internal plugin.

YOU ARE FULLY RESPONSIBLE IF ANYTHING HAPPENS. DON'T DO IT.

HendrixString commented 1 month ago

Hi @igalklebanov , thanks for the tips.

It is mostly for education purposes, because D1 only allows for multiple statements without parameters over http rest API.

I am just loudly rethinking some of the constraints with Kysely, that coincides with some of my favourite cloud services in the realm of query manipulation and execution.

It would have been great for example if:

  1. I had executeBatch in the driver.
  2. I had also the original query builders in the arguments of executeQuery

This will open up diverse scenarios of execution and flexibility, specially with cloud services advocating non interactive transactions, some even don't support it.

I truly believe it is a missing part in Kysely's execution feature.

I am not giving up on Kysely because I do enjoy and appreciate writing a lot of code that is shared between the dialects and then building queries with the same interface and object.

My only custom code are migrations between SQLite, postgres and MySQL and json functions. This is why I chose Kysely.

But I really hope you guys will relax the execution part a bit, so we can target cloud databases without desiring forking Kysely or come up with another original execution pipeline.

I also know more people want this. And, it will help convert more users into Kysely.

Let's create a poll around this

rdsedmundo commented 1 month ago

There's nothing like that in kysely and you should never execute a query built like that. That's like db programming 101. You'll get fired if you do that 😅

I was looking for the same functionality in kysely and legitimately don't understand your answer, can you elaborate?

There are benefits of having the values directly in the query string sent to the server, instead of a separate parameter. As long as the fields are escaped properly, that is not harmful at all, your answer seems greatly exaggerated.

Sequelize for example has this option, alongside the bind parameters: the replacements option allows both unnamed parameters in the form of ? passed through an array sequentially, or :parameterName which reads from an object.