Webstorm warning with multiple joins
```ts
const organizationsWithAdminUser = await db
.selectFrom("organizations")
.innerJoin("groups", "groups.organization_id", "organizations.id")
.innerJoin("users_groups", "users_groups.group_id", "groups.id")...
How do I use `WHERE NOT EXISTS`?
I am trying to write the followng where condition:
```sql
INSERT INTO Filing (location_id, filing_date, other_columns)
SELECT L.location_id, '2023-01-01'::date, default_values_for_other_columns
FROM Location L...
Solution:
You can call exists inside not to use not exists, something like this
```ts
db.insertInto("Filing")
.columns(["location_id", "filing_date"])...
Message Not Public
Sign In & Join Server To View
transforming database values to JS values (dates in sqlite)
hi. is it possible to transform database values with any driver into any JS value? such as integers to timestamps on specified columns? i want to do this for sqlite and other databases but idk how
How to enable pragmas with SqliteDialect
Hello, I am using SqliteDialect and want to enable foreign_keys pragma.
From SQLite Docs:
Foreign key constraints are disabled by default (for backwards compatibility), so must be enabled separately for each database connection....
Solution:
Message Not Public
Sign In & Join Server To View
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....