Dynamic query parts based on user input
Based on certain user input (REST API), I need to filter and/or sort by provided fields.
This could mean that some joins need to be added as well.
The fields to filter and sort are not always a 1-1 mapping to a database column, the names can deviate.
...
Unable to use table name with 3 dots in selectFrom
Hey
I am using the bigquery dialect adapter and I am trying to query the information schema.
it has the following structure
<DATASET>.INFORMATION_SCHEMA.COLUMNS
but when I pass a string with this structure to the "selectFrom" it just takes the first two...
Updateable primary key question & .set() type safety
Do you all wrap your Updateable versions of tables to omit id? Or am I doing something unconventional here.
Also how do you get typesafety on set() calls to prevent it accepting an item with id (or whatever primary key) from being updated?...
Solution:
Hey 👋
To define a column that's never updated, you can use
ColumnType with never in the 3rd generic value.
```ts...Error: don't await SelectQueryBuilder instances directly.
To execute the query you need to call
execute or executeTakeFirst.
I'm trying to create a function that enhances the query (SelectQueryBuilder) with pagination, sorting and filtering options that are derived from a HTTP request.
This function looks like:...Solution:
If you return a thenable object from an async function its ...
then method gets called. Kysely query builders all have a then method that throws that exception. It's there because many other query builders and ORMs allow you to do stuff like
const result = await db.selectFrom('person').selectAll()
const result = await db.selectFrom('person').selectAll()
JSON Object Traversal
Hi everyone, new user of Kysely.
I have the following json object inside a
sess column:
```json
{
"cookie": {...Solution:
Just use
->> for all of them?express-session store
Hi, I'm a new user of Kysely.
I'm wondering if there is a way to connect kysely with
express-session. For example, to use in combination with passport.
For example, I was wondering if Kysely somehow exposes a pg compatible pg.Pool, which would allow me to use something like connect-pg-simple to have an adapter from the Kysely world to the pg world....Solution:
I just realized that I can reuse the
pg.Pool that Kysely uses when I added the Postgres dialect, so that solves that problem 😄Synchronous queries to SQLite?
Is there any way to make synchronous queries to SQLite? As I understand it,
better-sqlite3 is a synchronous library and Kysely's SQLite dialect uses better-sqlite3, so is there a way to make synchronous queries?Solution:
There's no way to do that. The only dialect where a synchronous API would make sense is sqlite and we'd need to add a synchronous alternative of EVERYTHING for that tho work.
If I'm not mistaken, the reason why better-sqlite3 is faster is that it's synchronous internally greatly reducing mutex thrashing. We're just wrapping that same API to a couple of layers of async javascript code. I haven't run any performance benchmarks, but the performance might not be affected too much....
Return type when fields are conditional
Probably more of a TypeScript question than a Kysely question, but how would I type the output of a wrapper function that allows the conditional specification of fields. E.g.
```ts
export function selectActivitiesByActivityId(activityIds: number[], fields: Array<keyof ActivityHistoryTable> = []) {
const db = createKysely();...
What's the best way to use Pick with Selectable/Insertable/Updatable?
I am newer to typescript so fully possible missing something obvious here. But if I try to Pick from a table schema wrapped in Selectable/Insertable/Updateable it doesn't seem to infer the actual type of the column.
Example (first screenshot): x has no typescript issue here (just warns its unused in this fake code). "test" type shows resolving as:
```
type test = {...
Solution:
Actually I think this is environmental - I found there is a kysely sandbox and I couldn't repro there - it works perfect there in how I showed/you showed in the "better yet". I am going to close this and thanks again @koskimas
Issue with select
How would I handle doing what I'm doing in the raw part during the select?
```
const result = await db
.with("townhallDistribution", (qb) =>...
Issue with migrating from knex
I've recently learned about Kysely and how it is very compatible with TS compared to Knex.
I'm also very new to coding in general and have been following the structure I was taught at school earlier this year but I'm struggling right now to migrate the file that initializes data and shuts it down.
If anyone would be so kind to help me figure out how to move this from Knex to Kysely!...
how to decode a point to `{x: number, y: number}` when selected inside `jsonBuildObject`?
when i select a column of type point the basic way i get it decoded to
{x: number, y: number}, but when i wrap it in jsonBuildObject i get it as the raw string.
example:
```ts...Solution:
The only way to detect those columns inside JSON is regex or similar. There's no type info. In JSON, that's just a string.
need advice how to configure driver to return prefixed or drizzle style selections
Ideally I would like to get shape with a query like drizzle offers, meaning each table has its own object (eg. products joined with tax would return multiple rows as {product:{}, tax:{}}) or at least add a prefix to the columns. From what I read yesterday, this is beyond the scope of kysely, and more of a db driver task. Has anyone done something similar in combination with kysely and could give me a hint, because I don't really know how to go about it. Thanks!
Solution:
That is an ORM task. You can't get something like that in the general case without thousands of lines of code. That code includes modifying the SQL to get foreign keys selected if you don't manually select them, figuring out which row goes with which row, renaming selections and all kinds of crap.
All of which will fuck up the rest of the query. You can't build the rest of the query using arbitary SQL anymore since the query has been mangled into unrecognizable abomination. The selection you try to reference no longer exists, you can't use group by since the query structure is completely differenet from what you think etc.
At that point, you need more higher level ORM concepts that are able to deal with the mangled query....
problem with nested jsonArrayFrom and jsonObjectFrom
Typescript raise error when attempt to reference to relations in nested json helpers is made. Is this syntax error or kysely limitation?
https://kyse.link/SL7ct...
Solution:
Hey 👋
You can't just use the same
eb for all depths. The one you've used doesn't have line nor subline in its query context.
This works:...Is there way to select every field from table using `select` instead of `selectAll`?
I have very wide table and writing every field by hand is time consuming.
selectAll does the job, but I also need to populate some relations and build json_aggs. Is there way to either select every column from table (and then build jsonObject) or extend selectAll with some extra fields?
```
db
.selectFrom('document')
.select((eb)=> [...Solution:...
.selectAll('document')
.select(eb => ...)
.selectAll('document')
.select(eb => ...)
select / groupBy combined helper
I realize this usecase looks awful, but our API takes an input of a list of fields to group by, which also means it will return those fields in the result. What I have works, but there's a lot of duplication:
```javascript
.$if(isGroupedBy(STOP_ID), (qb) =>
qb.select('stopid as stopId').groupBy('stopid'),...
Solution:
But here's how you'd do it if you really really want to https://kyse.link/4PswR
How to delete and insert in one query
Hey! I wonder how with kysely make for example delete and insert in one query to reduce db roundtrips.
```
db.deleteFrom('a')
.where('smthId', '=', a.id)...
Solution:
.filterWhere inference
I have this in a select
```fn
.coalesce(
fn
.jsonAgg(...
Solution:
You can use
.$narrowType for this.json object relation null problem
I am trying to make https://kysely.dev/docs/recipes/relations this helper work, but get error
```
Argument of type 'ExpressionWrapper<DB & { pozycja: DokumentPozycja; } & { rozbicie: DokumentPozycjaRozbicie; }, "dokument" | "pozycja" | "rozbicie", number | null>' is not assignable to parameter of type 'Expression<number>'.
Types of property 'expressionType' are incompatible.
Type 'number | null | undefined' is not assignable to type 'number | undefined'....
Solution:
Never use
undefined or ? in your type interfaces.How to narrow type from jsonArrayFrom
Hey! I need to narrow some types from table that works as
Single Table Inheritance table. I believe this is possible, but after some fighting with api and syntax I am not sure how to make it.
Here is my query:
(sorry for polish, Ubiquitous Language with domain masters)
```...Solution:
oh okey got it,
.$narrowType<PrzyjecieZewnetrznePozycjaRozbicieSelect>() has to be straight after jsonArrayFrom not at the top level