Select by id?
With this code:
```
export const getProductById: RequestHandler = async (req, res, next) => {
const { id } = req.params
try {...
Solution:
That is because Kanel create a "special" type for the id
Why has the json_agg and to_json call functions not been released?
Hi
Recently I have favored Kysely.
I would like to implement a query that calls to_json using kysely....
Solution:
Hey 👋
We don't have a roadmap. It'll be released when we're ready to cut a version....
Why jsonArrayFrom converts a Generated<number> field into string | number?
Look at this pic
Solution:
Since you're not using table specifiers, are you sure some "parent" table doesn't also have those columns and you are actually selecting them?
kysely uses prepared statements like knex?
im planning use rds proxy and prepared statements pin connection
Solution:
Hey 👋🏻
nope. you can use kysely as a standalone query builder and execute the sql using a 3rd party driver's prepared statement execution api....
How to improve performance in large SELECT?
I have a large query with >100 selections, this makes VS Code hangs for type checking every 30s.
I tried
$assertType() with a hard-coded object type at the end of the select query, but it doesn't help much.
Is there a recommended way for large inferred types?...Type inference of queries with expressions in Webstorm
Webstorm reports that the type of
const result = await selectable.select(({ eb }) => eb(eb.fn.count('id'), '>', 0).as('email_exists')).executeTakeFirst()
is {email_exists: SqlBool} | undefined
...How to compose functions?
I have been unable to calculate
select count(id) > 0 from foo. I tried this:
.select(({ eb }) => {
eb(eb.fn.count('id'), '>', 0)
})...Solution:
I needed to alias the return:
.select(({ eb }) => eb(eb.fn.count('id'), '>', 0).as('email_exists'))
I learned this by accidentally stumbling across this note in the documentation:
You can select arbitrary expression including subqueries and raw sql snippets. When you do that, you need to give a name for the selections using the as method....
How do I create a helper to be used on a select array of fields?
I want to create a toBool helper to convert from tinyint, however I'm lost on the type I need to pass to get autocomplete of the possible fields in the select
```ts
import { sql } from 'kysely'
...
Solution:
I think checking equality to 0 would make this safer (and swapping true/false order), as tinyint can be negative.
How do I cast the type of a eb.fn('...') ?
I tried with eb.fn<string>("CONCAT", [....]) but it didn't worked
Solution:
If it doesn't work for some reason, you can use
...
eb.fn('now', []).$castTo<Date>()
eb.fn('now', []).$castTo<Date>()
cant use kysely-codegen
I followed this part https://kysely.dev/docs/getting-started of documentation till CODEGEN part when you can generate types based on actual database. https://kysely.dev/docs/generating-types this one. I installed needed packages:
npm install --save-dev kysely-codegen, and I set database string in .env file. Next step is: kysely-codegen, but I get this error when I type it in the console:
```
kysely-codegen
kysely-codegen : The term 'kysely-codegen' is not recognized as the name of a cmdlet, function, script file, o ...Solution:
oh I feel stupid...
npx kysely-codegen workswithSchema is marked as deprecated in ExpressionBuilder?
why and how to be replaced? How can be the schema defined dynamically (env)?
Insert values from subquery
What am I doing wrong here? https://kyse.link/?p=s&i=cvuB1ICSw0A1wiPUHxgK
Solution:
comments on ...
eb.lit :
To prevent SQL injections, only boolean, number and null values are accepted. If you need string or other literals, use sql.lit instead.
To prevent SQL injections, only boolean, number and null values are accepted. If you need string or other literals, use sql.lit instead.
$if with sql.raw doesn't compile
Hey,
I have updated my kysely version after I deleted node_modules and npm installed and I am getting an error for the following code:
const filterWhereQuery = sql.raw(LOWER("${dimensionName}") LIKE '%${filter?.toLowerCase()}%');
...Solution:
Hey 👋
Try providing
<SqlBool> generic to your sql template tag....ORDER BY in agg function.
Hi, I would like to represent something like
in kysely. How would I go about doing this?...
json_agg(table ORDER BY column) as values
json_agg(table ORDER BY column) as values
Binary fields in mySQL
I have a schema with ids stored as BINARY (sucks I know). Is it possible to write a plugin to convert them to string when receiving?
Solution:
You can use the
typeCast config of mysql2
```ts
typeCast: function (field, next) {
if (field.type === 'TINY' && field.length === 1) {...jsonb_build_object and raw sql possibilities
is possible to do this query without use sql.raw?
``
sql<string>jsonb_build_object(${sql.join([
sql.raw('hair_type',coalesce (dog.hair_type, dog_breed.hair_type)),
sql.raw('ear_type',coalesce (dog.ear_type, dog_breed.ear_type)),...Solution:
Yes, like this https://kyse.link/?p=s&i=uW2g2bEGXjBewlokoR0Y
ts_rank in select
still not possible?
```typescript
.select([
'finance_reimbursement_requests.reimbursement_request_id',...
Solution:
For future readers, when using sql template tag, or ExpressionBuilder within a select, you gotta alias it using the ...
as method.
sql<number>`ts_rank(to_tsvector('english', users.email, websearch_to_tsquery('R2023-185'))`.as('rank'),
sql<number>`ts_rank(to_tsvector('english', users.email, websearch_to_tsquery('R2023-185'))`.as('rank'),
How to convert this raw query to kysely?
```sql
UPDATE finance_reimbursement_approval_matrix
SET has_approved = true
WHERE approval_matrix_id = 'c78f11f0-7014-47cf-82b7-838a43b34b20'
AND reimbursement_request_id IN (...
Solution:
Here you go https://kyse.link/?p=s&i=YUbdjc83WtEz4pjwWvbV
Converting a more complex query to Kysely
I wrote this query for my database, and I've been trying to convert it to use Kysely but can't seem to. I'd like to figure it out (rather than just use string SQL)
The query:
```
SELECT...
Solution:
Here you go https://kyse.link/?p=s&i=3wmZrPkueuX6dxgeZ0Lz
Error "isSelectQueryBuilder" while using doUpdateSet on conflict
Hello, I get an error when dealing with an update object factory for
doUpdateSet expression during an on conflict statement for an insert.
From my experiments, if the select type of the column is different (not assignable to) the insert/update types than something breaks at the type level and gives me an error
Repro:
https://kyse.link/?p=s&i=Wt7lre2AUUH7EdAInOja...