"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')...Ad hoc custom column type
Hello, we have a table that on the database level, the column is a string type, but on the typescript level, the type is a Type literal union.
Is there any way we can ad hoc type a column to a type we know during select queries and insert statements?...
Solution:
Just give that type for the column in the table interface.
Creating a table within a schema using a column from a table in another schema
I'm using two schemas and I want to create a table in one of them with a column referencing a column that comes from another schema, how would I do this in migrations
i'm using Postgresql
````
await db.schema...
Guidance to create a generic wrapper over Kysely
I am trying to create a generic authentication wrapper over Kysely. The idea is
- You can pass some config to this function, which includes a reference to
Kysely instance, a database table name and an array of column names.
- The function will return an authenticator instance you can use to perform user lookups during Login. The internals of this function are not important for this discussion.
...Any way to docutype a Column as `@deprecated`?
Hello, We'd like to have columns we specify in out types with the
@deprecated type identifier in the Table type definition comments to be used in queries, is there a way to make it work with Kysely?Solution:
Not in any way that'd highlight or otherwise indicate the deprecated columns in queries. There's no typescript or IDE feature that'd allow string literals to be marked as deprecated.
Help with CTE query
Solution:
Something along these lines https://kyse.link/?p=s&i=pQgNfRevgy13EEWdiALI
Error this query cannot be compiled to SQL
I am trying to do
``
await trx.executeQuery(
sql<any>ALTER SCHEMA ${current.subdomain} RENAME TO ${subdomain};`...Solution:
I the end I went with
``ts
await sql
.raw(ALTER SCHEMA "${current.subdomain}" RENAME TO "${subdomain}"`)
.execute(trx)...Object literal may only specify known properties, and 'clientId' does not exist in type 'InsertObjec
I'm getting this issue with an insert statement using Kysely. But I'm not sure what I'm doing wrong.
Here's the Report interface generated via Kysely Codegen:
```js...
Solution:
ah, seems like LID/RID was a string but should be a number. Hard to debug these error messages.
Reuse subquery selects
Hello, I am trying to correctly type a select subquery to use in different other queries:
```ts
export const myUnion =
() => (eb: ExpressionBuilder<KyselyDB, keyof KyselyDB>) => {
return eb...
Solution:
You need to give an alias for the table.
myUnion().as('u')Use JSON key as text
Hello, I am using the following syntax to fetch data in a JSONB column:
```ts
.select((eb) => [
'id',
eb...
Solution:
aah, i had to change the first
-> to ->>, doh!Typing issue when working with onConflict
Hi, so i was trying to use some of examples https://kysely-org.github.io/kysely-apidoc/classes/InsertQueryBuilder.html#onConflict
I want to update column of json type in case of conflict
```typescript
.onConflict((co) =>
co...
Solution:
The issue is a mismatch between the insert and select types. That's essentially trying to assing
Json to string. This is something Kysely should handle, but it's really difficult in that case. What I'd do is this
```ts
export type Json = ColumnType<
JsonValue,...