help

Coalesce return empty array

Dddanielcruzz

5/19/2023

Hey is there a way to return an empty array rather than null when an array column is empty? I already tried

.select(coalesce("categories",sql`ARRAY[]::"CollectionCategory"[]`).as("categories"))

.select(coalesce("categories",sql`ARRAY[]::"CollectionCategory"[]`).as("categories"))

but it's still returning null and not an empty array

IIgal

5/19/2023

Hey 👋 This should work:

.select(({ fn }) => fn.coalesce(fn<Category[]>('to_json', ['categories']), sql<Category[]>`'[]'`).as('categories'))

.select(({ fn }) => fn.coalesce(fn<Category[]>('to_json', ['categories']), sql<Category[]>`'[]'`).as('categories'))

Dddanielcruzz

5/19/2023

I get malformed array literal: "[]" I think it may be a node-postgres thing? That empty sql arrays are transformed to null ?

IIgal

5/19/2023

edited my snippet not sure, but you can control it via pg-types

Dddanielcruzz

5/19/2023

still null 😦 I guess I'll just handle the null. I'm migrating from Prisma and out of nowhere my app crashed haha, and it's because Prisma returns empty array I tried casting the whole thing to ::text[] and still got null

IIgal

5/19/2023

pg-types is a package https://www.npmjs.com/package/pg-types

Dddanielcruzz

5/19/2023

Oh yeah I had another issue and tried to using it but I have to pass an id for the type, which could be the same or not between dev db and prod

Kkoskimas

5/20/2023

Custom enum types seem to cause all kinds of issues in both pg and in Kysely. If it's possible, you could consider dropping your custom types and use text instead. There's at least two enum patterns that I've found working great: 1. Simple text column with a check constraint that limits the value to the enum values. 2. Text column as a foreign key to an enum table. The enum table only has one primary key column and you insert the enum options there. The foreign key ensures the values but you still get a simple text data type. Unfortunately this means that you need a pivot table each time you have an array of enums in a table.

Looking for more? Join the community!

help

help

Want results from more Discord servers?

Add your server

Recommended Posts

kysely-codegen for multiple databasesI have two MySQL 'databases' within the same instance. From the documentation, it doesn't look like Using MySQL functions in SELECT statementHi! Just trying to migrate over from Knex. Skimmed over the documentation but still unsure of how t Extract OrderBy TS KeysHey I have the following query ```ts const getBaseQuery = ({ offset, pageSize, slug }: GetBaseQuer insert into with mix of static and table valuesHow might I execute an insert into that combines JS-side values with a select, like: ``` INSERT INTO Migration error "TypeError: Cannot read properties of undefined (reading 'getExecutor')"I am trying to run a migration using Kysely, and its returning this error: ``` file:///Users/brunocr where clause with lengthHow do I have a where clause with a length, e.g. `select * from data where length(prodcode) = 3`show generated sqlhow can i see what the generated sql is for a kysely query? e.g. ``` result = await db.selectFrom("Property does not exist on typeHello. I'm trying to run the following query: ```javascript result = await db.selectFrom("data") Correct type definitions for function receiving builderI have something like this and I was wondering if it's the correct way to type my helper function. Transform Postgres array into JS arrayHey, I was wondering if there's a helper or something to transform Postgres arrays `{one, two}` into Noob Question: SQL INSERT that combines static values with a SELECT statementHey Kysely community! I'm just getting into Kysely and I have a question about how I could run the f Is there a way to execute an ExpressionBuilder?Using the expression hasDogNamed example from the docs ``` const eb = expressionBuilder<DB, 'person Argument of type 'string' is not assignable to parameter of type 'DynamicReferenceBuilder<never>'.The following statement, is giving me the error on the title ```ts fn("round", [fn.min("ld.price_btc How do Date columns works?I am learning Kysely from the website, at the getting started section I can see the following line: converting result.insertedId to numberwhen using INT as PK i can safe convert `result.insertedId` from `bigInt` to `Int` using `Number(res Advice on building plugin for working with parametersI am currently working on a driver for YDB https://github.com/Gaspero/kysely-ydb YDB dialect expect Mysql Query execution never endsHello, I have a question regarding my new integration of kysely in trpc. I wanted to port my prisma How to do join with raw sql snippets?How can I do this join with kysely? `left join videos on videos.id = CAST(highlights_content.item AS More elegant way to handle nested relations?Can someone come up with more elegant solution for handling relations than this? I've modified the e Join DATE_TRUNCHey, sorry if this is already answered somewhere. I couldn't find anything 😦 I want to translate