Dynamic return type based on provided select array
So I have this function:
```
async getBillableUsers(select: SelectExpression<DB, 'user_data'>[]) {
return this.db
.selectFrom('user_data')...
Solution:
Took 2 hours but I think I figured it out.
Here's my solution for anyone else who might stumble upon this problem.
Feel free to leave better/smarter/faster/shorter alternatives.
```...
How to work with json columns?
There are so many different sources in the documentation as those made me confused completely. I used documented way but still get errors:
https://kyse.link/H3MKN...
Does the TypeScript performance scale with larger projects?
I just started migrating to kysely, love it so far! I was wondering how well the TypeScript performance scales for medium to large projects. For example, I'm working on a project with 500 db tables and 600k lines of code with much of it composed of db queries. As I'm rewriting the first few queries, I'm noticing slight delays with type inference and IDE code completion. Nothing major, but I'm worried it might get worse as I migrate more of the project and more type checking needs to happen.
Is this an actual concern or am I overthinking it? Can anyone who is using kysely on a larger project attest to whether they're running into type performance issues?...
Solution:
I'd strongly consider splitting into domains/services and exposing kysely clients with only what's known/used by each domain/service.
SQLite__namespace is not a constructor
Hi Im building a Electron-Vite App and running into the following error:
```
TypeError: SQLite__namespace is not a constructor
at Object.<anonymous> (C:\Users\USER\OneDrive\Dokumente\Programmieren\dim_wishlist_2\out\main\index.js:26:13)
at Module._compile (node:internal/modules/cjs/loader:1271:14)...
Solution:
This is caused by your module interop config somehow. Does it work if you use
...
import SQLite from 'better-sqlite3'
import SQLite from 'better-sqlite3'
Raw SQL in where clause
I have the following code and I'm getting a type error. This is strange as the raw sql statement works for insert statements but doesn't seem to play nice with the where clause.
In addition, the code below seems to work before
0.27.0.
```...Solution:
Give a type for the raw expression
...
.where('disabled_at', '>', sql<Date>`now()`)
.where('disabled_at', '>', sql<Date>`now()`)
smallint support (Postgres)
Type dataType does not contain smallint?
```TS...
addColumn<CN extends string>(columnName: CN, dataType: DataTypeExpression, build?: ColumnBuilderCallback): CreateTableBuilder<TB, C | CN>;
addColumn<CN extends string>(columnName: CN, dataType: DataTypeExpression, build?: ColumnBuilderCallback): CreateTableBuilder<TB, C | CN>;
Solution:
Yep, seems to be missing. You can use
...
addColumn("foo", sql`smallint`)
addColumn("foo", sql`smallint`)
Examples inner join using OR?
Hello, we are having trouble trying to have a inner join using OR, as in
ON a.id = b.id OR a.sub_id = b.sub_idfull join "using" list of columns
Hey,
How can I create a full join which uses "using"
I want to use "using" in the join but I only see "on", maybe I can acheive this using sql
using , what is the correct way ?
example:
```...Solution:
```ts
.innerJoin('b', join => join
.onRef('a.foo', '=', 'b.foo')
.onRef('a.bar', '=', 'b.bar')
)...
Is kysely sanitizing sql injection when using raw sql ?
Suppose I have a piece of code that does
sql
${myString}
where myString contains sql injection, will kysely sanitize it or is it something that I need to take care of ?...Solution:
Of course it is.
Support for D1 batching
Hi. I'm wondering about support for batching statements with Cloudflare's D1 batching, similar to libsql's batching - i.e. multiple statements are sent and processed at once similar to a transaction, except it all occurs within the database driver itself. Can I somehow go about extending kysely or my dialect to add support for this? I.e. some dialect-specific functions i guess?
Omit/filter out columns from query
Hey, is there an easy or convenient way with Kysely to filter out data from a query? Lets say I have columns 1,2,3,4,..., 10 and I want to omit column 9, do I have to really list all my columns like this:
?...
select(['1','2','3','4','5','6','7','8','10']
select(['1','2','3','4','5','6','7','8','10']
Solution:
Updated version with autocompletion and a list of excludes https://kyse.link/?p=s&i=Z9WWYXTjVPvB901Q1N9M
How to create a typed array of columns for select?
Hello, I am trying to create some helpers that contain the list of columns used by multiple queries, and I want to have them typed to the table they belong to to have it suggest errors if a table changes or someone uses a non-valid column. How is this achievable with the kysely helper types? The array may also include expression builder or the
jsonObjectFrom helpers since they are valid Select optionsReturn `null` by default if no record found
Hey, I am looking to have my queries return
null instead of undefined if no records match my query when using executeTakeFirst. Is there a way to set this in the Kysely client?Solution:
There's no way to do modify Kysely to return null in those cases.
cmpr as eb call SqlBool return type
Hello, missed a couple of updates and now I'm back updating to latest. Found out
eb.cmpr has been removed and I now need to use eb() as a function. Is there an easy way to get back a boolean instead of a SqlBool ?
Before I just needed to call it like eb.cmpr<boolean>. Thanks!
(I mean, other than a plain type assertion)...Solution:
Yep,
eb(...).$castTo<boolean>()Argument of type 'RawBuilder<unknown>' is not assignable to...
I have a query that looks like this:
countQuery.where(
'activityId',
'in',...
Solution:
What you actually want is something like this
...
sql<string>`(SELECT activityId FROM activities WHERE MATCH(EL_EQ_PRODUCT_DESCRIPTION) AGAINST (${'+' + value} IN BOOLEAN MODE))`
sql<string>`(SELECT activityId FROM activities WHERE MATCH(EL_EQ_PRODUCT_DESCRIPTION) AGAINST (${'+' + value} IN BOOLEAN MODE))`
Generic function to stream a table
I am trying to write a generic function that will take a kysely connection, a table name, a column name and a value for that column and return a streaming select. I can't figure out the "foward type declarations" to make it all work, and I think it's because of some of the fancy bits that Kysely does for selectFrom("table as alias") that I can't untangle. Playground link here: https://kyse.link/?p=s&i=n4OdlKPOjCkNULp6niLU . As you can see, it does WORK, but the red squigglies mean TS thinks it's...
Solution:
You can't really get something like this to work without getting rid of types inside the function. For example like this https://kyse.link/?p=s&i=pMUz4CfVVRHR6Sc2wgcN
The issue is that, inside the function, the types
Schema, T etc. are really wide (in this case completely unbounded). There's no information for Kysely to work with. The types don't work unless you give it an actual bounded schema.
You should avoid generic functions like this when using Kysely. Super strict and generic just don't work well together....Type errors after 0.27.1 upgrade
After upgrading to the lastest verision, I've got a couple of type errors that I'm not sure how to resolve. They are both the same kind of error, I have a custom WHERE IN expression:
```typescript
eb(
"objects.type",
"in",...
Solution:
yeah, I just realized that, I don't need sql.raw at all
"Correct" way to create a unique index with some raw SQL
I'm attempting to create a new unique index on a
username column, but I want it to be unique on LOWER(username). I have the following, and it works, but I was wondering if there is an alternative way to do this without having it all as a raw query?
``
await sqlCREATE UNIQUE INDEX users_unique_username_idx ON users (LOWER(username));`.execute(
db,...Solution:
TS2345: Argument of type "id" is not assignable to parameter of type ReferenceExpression<Database
Code:
```ts
const newToken = await db
.selectFrom("tokens")
.selectAll()...
Solution:
So all in all
```ts
import { ColumnType, Generated, Selectable } from "kysely";
import { TokenType } from "./lib/token";...
Join + Nested Object
I everyone 👋 . Before I ask my question, I know that there's already another question with the exact same title. I went through it hoping to find a solution there but to no avail. I'm trying to do the exact same thing as him (join two tables but join one as a property under the other table.
The two tables in question here are
lodges and lodge_conditions. I just wanna make a query such that I'll get back something of the type
```typescript
Selectable<LodgesTable> & { conditions: Selectable<LodgeConditionsTable> }...Solution:
Hey 👋
This is not supported in MariaDB, or old MySQL versions.
Look into the source code of the helper for inspiration. Try a CTE or subquery that creates a record set of
conditions_id and JSON object as columns and join with it on conditions_id and select just the JSON object column....