Channels
generating raw sql?
I think I saw somewhere an option that you can build a query using kysely and convert it to raw sql, but I can't find that anywhere. Does such an option exist or did I get something mixed up?
Solution:
Hey π
Use the
.compile() method instead of executing.
For more info, check out the link @53ny posted....Conditionally updating fields
How would i go about doing something like
UPDATE table SET field = IF(condition, trueValue, falseValue) WHERE someCondition;, i can't exactly seem to find that in the docs
Driver used: MySQL...Solution:
Hey π
Something like:
```ts...
How to cast to date. eg : `DATE(created_at)`
How to write this query in kysely
```sql
SELECT DATE(created_at) AS date, SUM(points) AS score
FROM table
WHERE id = '...' ...
Solution:
Hey π
Does this work for you?
```ts...
transaction takes too long
I have a problem with transactions. What might be the reason that db.transaction() adds more than 10ms latency?
```
Query took 2.3298759999997856
Validation took 0.006788000000597094
Update took 0.6013680000005479...
Syntax error when empty array is passed to a WHERE filter (request for more readable errors)
In a query like
someQuery.where("x", "in", xs), if xs is empty, then the error will be something like error: syntax error at or near ")". Given that in complex queries there can be multiple places that could cause such an issue, debugging it is sometimes painful.
I understand that it's SQL that is throwing the error and empty arrays are not possible, but would it be feasible to do a runtime check by the lib and throw a more readable error in case that happens? Or would this be against the philsophy of Kysely or too much overhead?
Also, I think a fallback of 1=0 might make even more sense, since that should be the logical output of the query with an empty array....Derived tables for Postgres
Hi, I am trying to do a bulk update like this:
```
UPDATE your_table
SET someColumn = new_values.new_value
FROM (VALUES...
Complex Query Builder from dynamic input - Typescript issue
Hi, it's me - again π
I'm currently trying to migrate our current query builder.
It seems that everything is working as expected, but I get some type errors and I'm not sure how to fix this - Maybe you have an idea how to fix this / what the rootcause is....
Solution:
Hey π
This is a bit harder to grok.
What's immediate is that
conditionBuilder might return undefined and .where expects something....How to add table/column comments when creating tables?
Hey, we're approaching close to 100 tables in our project. New people will be joining our founding team and we thought documenting the tables using the Postgres table/column commenting feature would make it easier to understand the DB schema.
After searching the docs, discord, and google, I couldn't find any info on how to do this in Kysely. Should we resort to raw SQL? Thanks!...
Solution:
Hey π
Kysely supports column comments in introspectors. This should allow external codegen tools (e.g. kysely-codegen) to generate types with jsdocs.
Builders don't have built-in support for adding comments on tables or columns. You can use
modifyEnd where possible to add comments with sql template tag....Need help improving a custom helper
My dopamine with Kysely is quite high right now, so I am experimenting all sorts of things to create custom helpers for common use-cases.
Here's an example of a working demo https://old.kyse.link/?p=s&i=qkTHjgwIuyb847wjoRd3
Following is a trimmed snippet from the above mentioned demo, for which I need help....
Solution:
Yeah unfortunately typescript doesn't bend that way. We wouldn't need the whole
ExpressionBuilder callback thingy anywhere if it did. We could just use free functions and they'd somehow catch the context through generics in the return value.
another option is month(eb)('created_at')....jsonArrayFrom with `as` not being typed
I have the following query
```ts
await db
.selectFrom('user')
.selectAll()...
Solution:
so Im using deno, and it uses import maps to import deps, I have these 2 import paths set up:
I forgot to bump the helper path version to 0.27.3 as well π€¦ββοΈ...
How to use multiple schema definitions ( e.g. <catalog>.<schema>.<table> )
Hey guys,
I'm currently working on a new version for our graphql wrapper.
Currently we're using knex to generate the query - it works as expected but isn't typesafe with how we use it π
...
Solution:
You could actually get away with:
```ts
const test = db
.selectFrom<"person">(...
Using raw SQL with `or` where
I have been using the raw SQL template tag to work with certain JSON columns and wanted to add a
orWhere clause using it. Here is a simple example of what I am trying to achieve.
```ts
const result = await db
.selectFrom('users')...Solution:
And you're right,
eb.or expects a list of Expression<SqlBool> where SqlBool is boolean | 0 | 1Custom Plugin to transform Alias.* to "Alias.Column1", "Alias.Column2", etc.
Hi everyone, in the previous question, I asked how to transform the following syntax into a new one. I was adviced to create my own plugin serializer (example: https://github.com/subframe7536/kysely-sqlite-tools/tree/master/packages/plugin-serialize). However, I still struggle with how to do it. Can anybody please give me a hint? I'll do it later by myself but need a guide how to start.
Thank you very much!
input syntax:...
can kysely be extended with custom types within the database
hi. i would like to extend my database with custom types (a date type for sqlite) so that it is easier to work with. i would assume that conversion to sql would be easy since its just a stringify operation using sql template tags, but is there a way to automatically convert from the database results, i.e. parse a ISO string into a Date object?
newbie need help with json_build_object
Hi! Just starting with kysely and encountered problem I cant solve. I want to build raw query with kysely builder...
this part gives me trouble. I would be grateful for your help...
JSON_BUILD_OBJECT('id', unit.id, 'name', unit.name) AS unit
JSON_BUILD_OBJECT('id', unit.id, 'name', unit.name) AS unit
Solution:
Here you go https://kyse.link/DqXNv
Note that the properties are nullable in the selected
unit object because you're using left join. The types are correct. If you are sure there's a unit for each sku then use innerJoin instead....Solved: Exporting query builder classes
I saw this PR https://github.com/kysely-org/kysely/pull/763 where you guys have decided not to export classes as they are private.
Is there any specific reason for which you don't want to make these classes public? Because if you change the public API of these classes, then anyways the query builder will be impacted by the change....
building kysely makes the package size very large
hi. i'd be more than happy to figure this out because i like kysely a lot more than drizzle. the thing was that the package size was so much larger with drizzle. i made a repro here. when running
bun build --minify --outfile, i get kysely to be 340.40kB whilst drizzle is 55.35kB, meanwhile the functional code besides the database is identicalKysely setup in monolith API
Hello, we have a basic http server with a basic router and a PostgreSQL database. We're wondering what is the proper setup to use the Kysely client in our routes to make calls to the database. Should there be a singleton client that is used across routes, or do we need to instantiate a kysely client on every request ?
Is it possible to get the total count while fetching rows in a single query?
I have a query which looks like this:
```typescript
const { items, count } = await db
.selectFrom(eb => eb
.selectFrom("ads")...
Separating results of join into objects of each type
Hey guys, I'm curious if anyone knows a way to unmerge the results of a join into objects of both types. For example, if you have a table User and table Bio, and query a single row which joins those two, the result is an a flat object with keys from both tables. What I'd like to do is separate the result into a User object and Bio object.
Solutions that work but I don't love:
1. manually extracting values from the object based on the current schema. (future changes to schema will break this, so I'd like to avoid that)...