.withSchema() and raw SQL
when doing
sql <ReturnType> SELECT some_function(param);.execute(db) the function is not found because it doesn't exist in the public schema, although db was created with .withSchema('myschema').
If I change the call to SELECT myschema.some_function(param);.execute(db) it still doesn't work, because the function references tables in myschema...JSON in columns (postgresql)
Hey <a:YA_Wave:743393941369651201>
I am completely new to using SQL databases and have a (hopefully) simple question. I want to store JSON inside a column and want to make certain keys not null and/or set a default value, how would I do that?
In this case, I have a
language column and want to set a default value for the locale key...Escaped paramaters in raw sql
For a few queries I made postgreSQL functions, this is the current way of how I call this function:
``ts
export async function getRandomAnswerByQuestionId({ questionId, answerLimit }: { questionId: string, answerLimit: number }) {
return sqlSELECT * FROM get_random_answers(${sql.val(questionId)}, ${sql.val(answerLimit)})`.execute(databaseClient);
}...Solution:
That's fine, but you don't even need to use
sql.val in this case
https://kyse.link/?p=s&i=MEBVYcJHvb7Uj4c6chaI...Should database tables mimic form fields?
Hey everyone, this question is not Kysely specific just wanna point that out don't know where to ask but here. I have a form for business registration that looks like this
Best practice around building query functions
Hey, I've looked around the docs somewhat but still not confident if I should do this: ```ts
export async function createUser(user: NewUser) {
return await db
.insertInto('User')...
Declaring types under 'kysely-codegen' module
Hey everyone, because I'm using Prisma to push schemas to my database (it's because my Zod Schemas) also getting generated from my Prisma schemas -- it's why I'm still using it incase you're wondering why I'm in this situation:
I have a
./types/kysely-codegen.d.ts file which contains ```ts
import { Insertable, Selectable, Updateable } from 'kysely';
import { User } from 'kysely-codegen';...From Prisma Model to Kysely Schema
Hey everyone, I'm using this package https://github.com/valtyr/prisma-kysely, the output of this Prisma Model ```ts
model Business {
id String @id @default(dbgenerated("gen_random_uuid()")) @db.Uuid
email String
name String @db.VarChar(100)...
[ERR_UNKNOWN_FILE_EXTENSION] when running migrations
I have a project with Sveltekit, TypeScript, and Kysely with SQLite. When I run my migrations, using the FileMigrationProvider, I get the following error:
```
TypeError [ERR_UNKNOWN_FILE_EXTENSION]: Unknown file extension ".ts" for /Users/crummy/code/finances/src/db/migrations/2023-06-22-initial.ts
at new NodeError (node:internal/errors:399:5)
at Object.getFileProtocolModuleFormat [as file:] (node:internal/modules/esm/get_format:79:11)...
Solution:
I got it working by avoiding FileMigrationProvider. Kind of hacky, not sure if there's a better way to do these imports:
https://github.com/crummy/finances/commit/3b4ff5ce86a7767baa8ef3da2f279bbfc71d1ae0...
Figuring out where in codebase an exception originated from
Hi all,
This might be a stupid question, but what is the best way to figure out which
.execute() call threw a SQL exception? I know ideally I should be doing error handling on each call, but I have a large codebase and I'm not sure where this error is coming from. Is there any easy way of figuring this out? Maybe a way to have a wrapper that prints out the query if malformed, or to print out the stack trace where the original call was executed?
```
You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ')' at line 1. Trace: Error: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ')' at line 1...How to insert into all values (and columns) from another table / view?
I realize I could write the raw sql, however I was curious how I would go about creating the following insert statement using kysely.
insert into <table>
select * from <view_or_table>
...
Triggers vs stored procedures
Hey everyone, I don't think this is related to Kysely directly, so excuse me but this is the only SQL type of server I am in. I am wondering what is the best way to automatically update a table attribute based on when another attribute changes (in the same table or another table). For example, if I want to have the attributes
updatedAt and lastLogin updated on their own from the Model below ```ts
model User {
id Int @id @default(autoincrement())
createdAt DateTime @default(now()) @db.Date
updatedAt DateTime @db.Timestamp() ...`Selectable`, `Insertable` and `Updateable` wrappers
Hey everyone, I am using this package: https://github.com/valtyr/prisma-kysely and I noticed that the output file does not include
...
export type Person = Selectable<PersonTable>
export type NewPerson = Insertable<PersonTable>
export type EditedPerson = Updateable<PersonTable>
export type Person = Selectable<PersonTable>
export type NewPerson = Insertable<PersonTable>
export type EditedPerson = Updateable<PersonTable>
How to sort by random?
Imagine this query:
```ts
export default function findUsers() {
return db...
Solution:
Should do it...
orderBy(sql`random()`)
orderBy(sql`random()`)
Using jsonArrayFrom with mariadb.
Hello,
Madiadb does not have cast to JSON, and cannot reference nested subqueries.
I'm a typescript newbie. 🙂
How can I rewrite this function to achieve a similar result with Mariadb?...
insert data into a table that has id of type bigserial
I created a table that has id(bigserial), cognitoId(varchar), subaccountInfo(json), I'm trying like this:
```
const obj = [{
nome: "nil", ...
Unable to connect to DB
Thank God there is a Discord server xD
I been trying to make this work for like 2 hours and I can't get it to work ```ts
import { Pool } from 'pg';
import {...
Solution:
There are a few approaches:
https://kysely.dev/docs/recipes/schemas
1. WithSchemaPlugin to apply schema globally to all queries.
2. withSchema method to apply to specific queries on the fly....
Limit with a literal value
Hi folks, thanks for all your work on Kysely, it's great!
I'm wondering how I can add a limit clause with a literal value, rather than a parameter.
I have a strange use case, I'm querying a https://materialize.com/ database, which presents as postgres, but has different features. It also has some limitations, one of which is limit must be a literal value, not an expression or parameter. ...
Prisma-kysely generator converts boolean to a number
Hi, so using
prisma-kysely, the generated type for a prisma Bool appears to be a number for some reason. Is this correct?Solution:
Oh my mistake. mysql does in fact not have a bool type lol
Is there a way to not have as many Selectable<> generics?
Hi. I'm using
prisma-kysely to generate my Kysely types, and it generates them with the Generated generic. I therefore need to use the Selectable generic to change it to the return type of what is selected when I want to use the type. Is there a way to avoid using it so often?