full 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....Arbitrary insert statement
I have a bit of an edge case where I want to build a query that is dynamic based on user input (it's part of an interactive tutorial). Is there a way to build an insert statement where the object name is an arbitrary string? Alternatively, is there some way to do this with sql framemgents, but still make the value a parameter? I'm imagining a
sql.parameter function, but I don't see anything like that in the docs.Solution:
Everything is a parameter by default when you use the ...
sql tag.
sql`insert into foo (id) values (${id})`.execute(db)
sql`insert into foo (id) values (${id})`.execute(db)
How to handle versioning of database objects.
Hello, i want to add versioning for my QuizTemplates.
I have an idea with revision field.
I want to add new field called "revision" in quizTemplate and quizInstance somehow create join from QuizInstance.
How can i create join by two fields instead of one?
How do you handle join by more than 1 column....
Solution:
Sqlite JSON
Hello,
I'm a new user to Kysely and I'm having trouble using the JSON interface. I would normally just
stringify and parse JSON myself when using Kysely, but it looks like I can addColumn("myColumn", "json"). However, when I try to insert values as JSON, I get TypeError: SQLite3 can only bind numbers, strings, bigints, buffers, and null.
If I strigify my JSON first, it adds to the database properly, but the Kysely gives me type errors. Should I have my Database table column of type string when I am passing type "json" to Kysely?...Solution:
Hey 👋🏻
Try using:
```ts...
case() return boolean (castTo doesn't work?)
Hiya! I'm trying to determine if what I'm doing is currently possible
I have the following expression
```...
TINYINT convert to bool
hello, i am using kysely with a mysql dialect and i find myself in a situation where some fields in JSON are just 1 instead of being true is there any way to change this?
Solution:
```
typeCast(field, next) {
if (field.type === 'TINY' && field.length === 1) {
return field.string() === '1'
} else {...
CASE with subquery clause
How can I return the column value from the subquery in a CASE? With this example, Kysely thinks the second condition is
{ name: string }:
```ts
eb.case()
.when('re.type', '=', 'company')...