Error when destructuring QueryCreator in withRecursive
I'm new to Kysely and attempting to migrate a Next app from prisma to kysely. I've generated types, successfully run some simple queries, and now I'm experiencing an error (in the title) that I'm unsure how to debug with a more complex recursive query.
Here's a playground link to demonstrate: https://kyse.link/ZD1EV
Can anyone help point me in the right direction? I cobbled this query together based on other examples I found but I haven't found anyone else with this error....
Is `sql.join` the best way to concatenate sql templates?
I have some business logic that incrementally builds up a sql WHERE query as a string. It looks like I can't do the dumb thing and just string concat the pieces together like so:
...
let q = sql.raw` noteFtsFv.rowid ` + sql.raw` in `
let q = sql.raw` noteFtsFv.rowid ` + sql.raw` in `
Multiple calls to values in insert statement?
I just had a production bug where I was calling
.values multiple times on a query object. I've fixed it by passing an array to a single values call instead, but this seems like a bug no? Either the types should reflect that values can no longer be called after it has been, or it should collect the values calls into one array?using pgvector with Kysely
Following the example here: https://github.com/pgvector/pgvector-node?darkschemeovr=1#kysely
But I get the error
ERROR: column "embedding" is of type vector but expression is of type character varying
Happy to provide code if necessary but there aren’t any differences from the tutorial ...Solution:...
// Replace `string` with the type you have in the table interface.
sql<string>`${sql.ref(column)} <=> ${toSql(value)}::vector(3)`
// Replace `string` with the type you have in the table interface.
sql<string>`${sql.ref(column)} <=> ${toSql(value)}::vector(3)`
Static/reusable custom window functions
Hello, I'm trying to buld a properly typed
array_agg window function and would like to reuse the existing FunctionModule in a reusable way, so to have orderby, over, and so on.
Is there a way to get the function module you get from fn.agg outside of an expression builder and without creating a puppet kysely instance, so to create an array_agg function and return what fn.agg return but properly typed?
As an example:...
How to loop an array and based on this create a dynamic CTE but preserve type ?
``ts
for (const [references, columns] of referencesArray) {
const newQb = qb.with(cte_${references}`, (wb) => {
wb = wb.selectFrom(references).distinct().selectAll(references);
for (const column of columns) {...Solution:
It's impossible to do this while keeping the types. Not only because of Kysely but also because of typescript.
- You can't change the type of a variable in typescript.
- You can't turn a
string into a string literal.
- Even if your strings had string literal types, you couldn't loop over them...On Conflict do update set ALL to be inserted columns
Here I am once again with a question for the on conflict bit in postgres. Would there be a way to automatically compose an update set statement for the on conflict do update bit with the information about all the columns we're going to insert, and automatically do an update with the
excluded for those?
Example here...How to use kysely in edge runtime (like NextJS 14 middleware)?
Error: The edge runtime does not support Node.js 'crypto' module.
Learn More: https://nextjs.org/docs/messages/node-module-in-edge-runtime
Full log: https://pastebin.com/JSXBNRxv
...
how to write not (array1 && array2)
Hi again,
how to write following code? I would love to use not(expression)
Expected result:...
Partial compile
Hello everyone, is it possible (somehow) to generate only part of the compiled query? I have an edge-case when want to use only part ("where" statement in my case) of the compiled query from kysely. Is there any short-way to do it? I definitely don't want to parse the result. Thanks Mike
Querying on jsonArrayFrom
Hi. So i need to fetch posts with comments from a database that are written by a set group of authors and have comments that are written by a set group of people. How would I accomplish this with sql? The best thing I can think of is fetch all posts and comments where the posts are written by the authors, and then do further clientside filtering on the comments - but is there a way to do this with the sql itself?
Why does `.stream()` return a single result when I pass in a `chunkSize` option ?
I'm guessing it returns
chunkSize mount of rows for each iteration, but shouldn't it be an array instead?Is there a way to keep full date precision?
It appears that selecting dates from Postgresql loses some precision, which wreaks havoc with my paging logic. Is there a way to get dates to have full precision on the JS side?
How can I get total row count without blowing up types?
I have a simple query function that is in charge of generating a list of ids so that it can be passed into a function which does some other fancy stuff with those ids as a CTE. Like so:
```
export async function getByExternalId(
db: Kysely<DB>,...
Solution:
I guess maybe this works?
...
.select(['some_id', (eb) => eb.fn.count('some_id').over().as('total')])
.select(['some_id', (eb) => eb.fn.count('some_id').over().as('total')])
Is there a way to handle migrations with .sql files?
I enjoy being able to control the specific queries in the migration files. To do this, I use postgrator (https://www.npmjs.com/package/postgrator) along with a custom script that also manages which environment should run each file. Does Kysely migrations also support this feature (migrations with only .sql files)?
```sql
-- context: dev,stage,prod
CREATE TABLE IF NOT EXISTS postgrator_migrations (
version BIGINT NOT NULL,...
Solution:
But nothing prevents you from using a separate tool for migrations. There's no benefit from using Kysely's migrations. And to be honest, the migration module has received very little love. It's quite bare-bones.
Postgres: Transaction not honouring previous queries
Hi, I have a table with unique constraint on some columns.
In a transaction, I am deleting all the entries from the table and then I am inserting new entries. However, I am getting
error: duplicate key value violates unique constraint "xxxxx_key"
I only want the deletion to happen if the insertion is successful, and that's why I tried using transaction. However, the transaction seems to work in a different way than I thought.
...Solution:
If you insert before you delete, that's what's going to happen. You insert something and only after that delete the duplicates. The constraint is checked immediately when you insert.
How do I specify a MySQL index hint?
Hi, I am trying to build the following query (as a CTE, as part of a larger query), but I can not work out how I can pass the index hint (
USE INDEX ...):
```sql
SELECT
sc.synopsis_fk,
'characters',...question about transaction isolation level
I am not sure if the way I do queries with transaction with kysely is the right way. I would be glad if someone could take a look:
``
try {
await sqlBEGIN;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;`...case when column = another column
I have a semi-complex query, and I'm trying to use a case() that includes a when() where the two values are columns (aka the two columns have the same value).
It doesn't seem to be supported, however in theory this should work. Any idea how I can make it work with Kysely?
The
.when("shahz_response", "=", "rank") is where I'm attempting this, but this isn't working...Solution:
Welp, looks like I figured it out!
.when("shahz_response", "=", e.ref("rank"))...