Unioning an array of sql`{SQL_QUERY}`
Hey chaps, I'm doing some really heinous shit with triggers etc (hence why I'm not using Kysely's default query builder syntax). I have an array of SQL queries that I'd like to
UNION ALL together - any idea how best to do that? Note - the length of the query array is dynamic - Thanks!!Identically named columns in multiple tables - How to use innerJoin.selectAll()?
Hey, I am new to using kysely, and also relatively new to using PostgreSQL.
I have trouble with the following query:
```js...
Solution:
Hey 👋🏻
You have 3 options:
1. use table scoped
selectAll on 1 table (selectAll('tables')) and then select the other table/s columns while aliasing columns that exist multiple "tables"....Type safe discriminated union query
I am looking for a way to make this query more type safe.
The generated SQL gives me the result I want, which adheres to the specified union type, but typescript needs some convincing and the
$castTo assertions I have added are hurting type safety.
If I remove .$castTo<unknown>() in the task query I get this error in the event query Property 'description' is missing in type '{ event_name: string; }' but required in type '{ description: string; }', and similar in the note query....Improving TS compile speed with multiple conditional selects?
Hi, I have API endpoints that query Postgres via Kysely. The API endpoints accept an
include object that determines whether to include various optional fields. Some endpoints have grown to accept 5-10 optional fields.
Each of these optional fields results in an additional $if branch in the Kysely query. And this seems to harm TS compile speed -- I'm now waiting 1-3min for each compilation.
What are the best ways to mitigate the compile speed downsides of using many $if calls?...Type safety in checkExpression in addCheckConstraint
Hi, is it possible somehow utilise kysely to create type-safe checkExpression and avoid writing stuff like this:
```typescript
.addCheckConstraint(
'constraint-name',...
Trouble with dynamically allocating table name via string
Hello!
I am having some trouble trying to allocate a table name dynamically via a string. I used the sql.table command in my SelectFrom statement but no such luck. Passing in a direct string also does not work as hoped.
I have read a few posts which indicate this is not possible, if not I can work around it, if it is however how might I achieve this?...
Dynamic expression builder using raw SQL for WHERE column LIKE string% OR
Hello!
We switched over to using Kysely in our project and it's going great so far! But I found a bit of a hitch with writing an old dynamic WHERE LIKE query.
I need to get the results of file paths stored in our system that start with a directory path. Previously in pure SQL we would generate an expression like this:...
Solution:
@Igal that works great. Leaving the full solution here for others who come looking
...
const condition = dirPaths.map((dir) =>
sql<boolean>`file_path LIKE ${dir + '%'}`)
.reduce((acc, condition) => sql<boolean>`${acc} OR ${condition}`);
const condition = dirPaths.map((dir) =>
sql<boolean>`file_path LIKE ${dir + '%'}`)
.reduce((acc, condition) => sql<boolean>`${acc} OR ${condition}`);
How to define multiple configs in a NX monorepo?
Hi, we have multiple applications in a NX monorepo and I need separate configs for migrations and seeders. How would you recommend to do it? When using
kysely init it creates the kysely.config.ts in the root ./config. Should I use conditional app selection by env variables?Making Updateable<X> fields required
Excuse me if this has already been posted, but I recently turned on strict mode after completing my migration to TS and I'm struggling to make certain fields required when updating an entity.
When I hover over
UpdateableActivity it shows:
```ts...Solution:
You can't make them required by kysely. In SQL update statements, every column is optional.
You can have your own interface that makes some fields required and use that as an input for your own update functions that use kysely....
ilike with reference
Hey there, I'm looking to perform the following SQL operation:
but unfortunately I'm having trouble doing this in Kysely. Here is my current attempt:...
WHERE groups.group_name ILIKE '%' || users.first_name || '%'
WHERE groups.group_name ILIKE '%' || users.first_name || '%'
Solution:
Using
Seemed to do the trick, instead of
sql`'%'`,
sql`'%'`,
eb.val("%"). Any other ways to make this more ergonomic?...Issue with how timestamptz is returned depending on main or subquery
I'm running into the following issue where my date is returned in two different ways. Date is a
timestamptz type column
When I grab the lesson as followed:
```ts...Solution:
Pretty sure
pg just parses the JSON object without checking strings if they represent timestamps and it doesn't get any metadata related to the contents of the JSON. That'll be quite slow as a default, to check those strings.select expression helper
I got this selectExpression that i use in many file
.select([
'e.id',
eventStartDate('e'),...
Solution:
tuple() and refTuple() questions
Hello, i would like to generate this query
DELETE FROM table WHERE (col1,col2,col3) IN (...insert values);
i found out from my code editor intellisense that tuple() and refTuple() exist, but to my surprise, i can only insert 2 values in a single tuple.
is there a way for me to achieve that in kysely? or do i stick to raw sql for that?
...Solution:
There's no such limitation
Casting part of a return type
Hi there,
Some of my queries return lots of fields. I want to be able to cast the return value of some of these fields. For example, I have a
SELECT statement on a numeric field, but pg will parse this to a string. I want to be able to tell this in my output.
- I can't use $.castTo because this destroys the other values in my SELECT clause....Solution:
Execute RAW Sql Queries
hello, is it possible to execute any raw query supported by my underlying sql driver with
kysely? i could not find any such function in the docs or the guide book
for example, describe table_name errors because kysely itself cannot compile it. i am using mysql2 as my driver....Solution:...
await sql`RAW QUERY HERE;`.execute(db);
await sql`RAW QUERY HERE;`.execute(db);
Message Not Public
Sign In & Join Server To View
Selecting result of subtraction with summation
Hey, I'm having trouble translating the effective following Postgresql code to Kysely code:
```sql
SELECT
reagent_inventory.initial_quantity -
COALESCE(SUM(reagent_inventory_usage.quantity_consumed), 0) AS remaining_quantity...
Solution:
oh oops, I think I just meant to use
"inventory.initial_quantity", rather than wrapping it in eb.val 🤦♂️Migrator transaction (postgresql dialect)
Hello everyone,
will the code below ensure all migrations are in a single transaction? if kysely migrations don't support AsyncLocalStorage, does it make sense to use unmanaged transaction and put
BEGIN before and COMMIT at the end?
```...Solution:
When you run
migrateToLatest() all migrations are run in a single transaction. They either all succeed or none of them succeed.
Also when you run migrateTo the same happens. The only case where you get individual transactions is when you run the migrations in multiple calls.
You can't use migrateTo or migrateToLatest()?...Kysely client usage and pooling question
Hello,
We have a long running server that at the moment creates a single Kysely instance. It is setup to use pg pooling. We have a util function called getKysely which returns this instance and is used throught our backend , like on every request handler that calls the DB. So in theory multiple concurrent requests call this function to get the Kysely client and make queries.
We are noticing that even within the same API endpoint handler, executing queries with a fetched client seems to be running on different connections. We think this because we sometimes execute raw sql queries which change the role of the connection, but later on using the same client we execute a query to check the current role and it is not the one set up previously. ...
Solution:
Kysely takes a connection from the pool for every query. It might be a brand new one or an existing connection in the pool. The pool is in charge of that. Kysely just requests a connection from the pool.
There is no way the pool could know about your requests and return the same connection. How do you imagine that could ever work? A request is not a fundamental ecmascript concept. There's no "request local storage" that could be used to store that info.
If you're coming from the java world, there you have a thread per request (at least with ancient frameworks) and the thread-local storage can be used to get the same connection/transaction automatically....
Implementing D1 batching (getting typed queries as input and returning typed InferResult)
Hi. I've wanted to get D1 batching to work, so I've tried doing as below. Functionally the code works, however I would like to get the proper type safety for this. Essentially I extend Kysely to add the
executeBatch method and I want to infer the results for each of the individual queries. Is this possible?