tzezar Posts - Answer Overflow

tzezar

Explore posts from servers

CC#NNuxt KKysely PPrisma DTDrizzle Team

Posts Comments

NNuxt

•Created by tzezar on 8/29/2024 in #❓・help

no intellisense using path alias

1 replies

DTDrizzle Team

•Created by tzezar on 8/4/2024 in #help

Is using zod for type narrowing fine solution?

Is using parse for type narrowing good idea with decent performance or should I make validation functions for objects?

let a = await dbDrizzle.query.document.findFirst({
    with: {
        lines: true
    },
}).then(r => {
    if (!r) throw new Error('Document not found')
    if (r.typ === 'order') {
        const lineSchema = orderLineSchema
        return orderSchema.extend({lines: z.array(lineSchema)}).parse(r)
    } else if (r.typ === 'receipt') {
        const lineSchema = receiptLineSchema
        return receiptSchema.extend({lines: z.array(lineSchema)}).parse(r)
    }
})

let a = await dbDrizzle.query.document.findFirst({
    with: {
        lines: true
    },
}).then(r => {
    if (!r) throw new Error('Document not found')
    if (r.typ === 'order') {
        const lineSchema = orderLineSchema
        return orderSchema.extend({lines: z.array(lineSchema)}).parse(r)
    } else if (r.typ === 'receipt') {
        const lineSchema = receiptLineSchema
        return receiptSchema.extend({lines: z.array(lineSchema)}).parse(r)
    }
})

2 replies

KKysely

•Created by tzezar on 8/3/2024 in #help

need advice how to configure driver to return prefixed or drizzle style selections

Ideally I would like to get shape with a query like drizzle offers, meaning each table has its own object (eg. products joined with tax would return multiple rows as {product:{}, tax:{}}) or at least add a prefix to the columns. From what I read yesterday, this is beyond the scope of kysely, and more of a db driver task. Has anyone done something similar in combination with kysely and could give me a hint, because I don't really know how to go about it. Thanks!

5 replies

KKysely

•Created by tzezar on 8/2/2024 in #help

problem with nested jsonArrayFrom and jsonObjectFrom

Typescript raise error when attempt to reference to relations in nested json helpers is made. Is this syntax error or kysely limitation? https://kyse.link/SL7ct

4 replies

KKysely

•Created by tzezar on 7/31/2024 in #help

Is there way to select every field from table using `select` instead of `selectAll`?

I have very wide table and writing every field by hand is time consuming. selectAll does the job, but I also need to populate some relations and build json_aggs. Is there way to either select every column from table (and then build jsonObject) or extend selectAll with some extra fields?

db
  .selectFrom('document')
  .select((eb)=> [
      'dokument.*',
      // some json_agg for example
  ])
  .executeTakeFirstOrThrow()

db
  .selectFrom('document')
  .select((eb)=> [
      'dokument.*',
      // some json_agg for example
  ])
  .executeTakeFirstOrThrow()

Using raw sql it could be done with

SELECT d*, json_obj(w*) as "warehouse"
FROM document d
LEFT JOIN warehouse w ON w.id = d.warehouse_id

SELECT d*, json_obj(w*) as "warehouse"
FROM document d
LEFT JOIN warehouse w ON w.id = d.warehouse_id

please dont waste your time on building this query, just a little hint will be super helpfull! 😉

4 replies

KKysely

•Created by tzezar on 7/30/2024 in #help

How to delete and insert in one query

Hey! I wonder how with kysely make for example delete and insert in one query to reduce db roundtrips.

db.deleteFrom('a')
    .where('smthId', '=', a.id)
    .execute()
db.insertInto('b')
    .values(dto)
    .execute()

db.deleteFrom('a')
    .where('smthId', '=', a.id)
    .execute()
db.insertInto('b')
    .values(dto)
    .execute()

I believe Using WITH Clause (Common Table Expressions - CTEs) might help, but how kysely syntax would looks like? Should I simply use raw sql here?

BEGIN;

-- Perform the DELETE operation
DELETE FROM your_table
WHERE condition;

-- Perform the INSERT operation
INSERT INTO your_table (column1, column2)
VALUES (value1, value2);

COMMIT;

BEGIN;

-- Perform the DELETE operation
DELETE FROM your_table
WHERE condition;

-- Perform the INSERT operation
INSERT INTO your_table (column1, column2)
VALUES (value1, value2);

COMMIT;

6 replies

KKysely

•Created by tzezar on 7/28/2024 in #help

json object relation null problem

I am trying to make https://kysely.dev/docs/recipes/relations this helper work, but get error

Argument of type 'ExpressionWrapper<DB & { pozycja: DokumentPozycja; } & { rozbicie: DokumentPozycjaRozbicie; }, "dokument" | "pozycja" | "rozbicie", number | null>' is not assignable to parameter of type 'Expression<number>'.
  Types of property 'expressionType' are incompatible.
    Type 'number | null | undefined' is not assignable to type 'number | undefined'.
      Type 'null' is not assignable to type 'number | undefined'.ts(2345)

Argument of type 'ExpressionWrapper<DB & { pozycja: DokumentPozycja; } & { rozbicie: DokumentPozycjaRozbicie; }, "dokument" | "pozycja" | "rozbicie", number | null>' is not assignable to parameter of type 'Expression<number>'.
  Types of property 'expressionType' are incompatible.
    Type 'number | null | undefined' is not assignable to type 'number | undefined'.
      Type 'null' is not assignable to type 'number | undefined'.ts(2345)

I wrote code following docs example: usage:

stawka(ref('rozbicie.stawkaVatZakupuId')).$notNull().as('stawkaVatZakupu'),

stawka(ref('rozbicie.stawkaVatZakupuId')).$notNull().as('stawkaVatZakupu'),

and helper expression?:

function stawka(stawkaVatId: Expression<number >) {
    return jsonObjectFrom(
        db.selectFrom('stawkaVat')
            .select([
                'stawkaVat.id',
                'stawkaVat.nazwa',
                'stawkaVat.wartosc'
            ])
            .where('stawkaVat.id', '=', stawkaVatId),
    )
}

function stawka(stawkaVatId: Expression<number >) {
    return jsonObjectFrom(
        db.selectFrom('stawkaVat')
            .select([
                'stawkaVat.id',
                'stawkaVat.nazwa',
                'stawkaVat.wartosc'
            ])
            .where('stawkaVat.id', '=', stawkaVatId),
    )
}

adding union with null here:

Expression<number | null >

Expression<number | null >

fixes problem, but it looks like it should work without null after adding .$notNull() as above and later after select

   .$narrowType<PrzyjecieZewnetrznePozycjaRozbicieSelect & {
                                        stawkaVatZakupu: NotNull
                                    }>()

   .$narrowType<PrzyjecieZewnetrznePozycjaRozbicieSelect & {
                                        stawkaVatZakupu: NotNull
                                    }>()

any clue?

6 replies

KKysely

•Created by tzezar on 7/28/2024 in #help

How to narrow type from jsonArrayFrom

Hey! I need to narrow some types from table that works as Single Table Inheritance table. I believe this is possible, but after some fighting with api and syntax I am not sure how to make it. Here is my query: (sorry for polish, Ubiquitous Language with domain masters)

let dokument = await db
.selectFrom('dokument')
.where('id', '=', dokumentId)
.select((eb) => [
"dokument.id",
"dokument.typ",
"dokument.status",
"dokument.wartoscNetto",
"dokument.wartoscBrutto",
"dokument.wartoscVat",
"dokument.zyskMagazynowyNetto",
"dokument.cennikId",
"dokument.magazynId",
"dokument.dostawcaId",
"dokument.dataPrzyjecia",
jsonArrayFrom(
eb.selectFrom('dokumentPozycja as pozycja')
    .select((eb) => [
        "pozycja.dokumentId",
        "pozycja.asortymentId",
        "pozycja.ilosc",
        "pozycja.nazwaAsortymentu",
        jsonArrayFrom(
            eb.selectFrom('dokumentPozycjaRozbicie as rozbicie')
                .select((eb) => [
                    "rozbicie.id",
                    "rozbicie.typ",
                    "rozbicie.dokumentPozycjaId",
                    "rozbicie.partiaProduktuId",
                    "rozbicie.magazynProduktId",
                    "rozbicie.magazynPartiaProduktuId",
                    "rozbicie.ruchMagazynowyId",
                    "rozbicie.cenaNetto",
                    "rozbicie.cenaBrutto",
                    "rozbicie.wartoscNetto",
                    "rozbicie.wartoscBrutto",
                    "rozbicie.wartoscVat",
                    "rozbicie.zyskMagazynowyNetto",
                    "rozbicie.ilosc",
                    "rozbicie.stawkaVatZakupuId",
                    "rozbicie.jednostkaMiaryId",
                ])
                .whereRef('rozbicie.dokumentPozycjaId', '=', 'pozycja.id')
                .orderBy('rozbicie.id')
        ).as('rozbicia')
    ])
    .whereRef('pozycja.dokumentId', '=', 'dokument.id')
    .orderBy('pozycja.id')
).as('pozycje')
])
.executeTakeFirstOrThrow()

let dokument = await db
.selectFrom('dokument')
.where('id', '=', dokumentId)
.select((eb) => [
"dokument.id",
"dokument.typ",
"dokument.status",
"dokument.wartoscNetto",
"dokument.wartoscBrutto",
"dokument.wartoscVat",
"dokument.zyskMagazynowyNetto",
"dokument.cennikId",
"dokument.magazynId",
"dokument.dostawcaId",
"dokument.dataPrzyjecia",
jsonArrayFrom(
eb.selectFrom('dokumentPozycja as pozycja')
    .select((eb) => [
        "pozycja.dokumentId",
        "pozycja.asortymentId",
        "pozycja.ilosc",
        "pozycja.nazwaAsortymentu",
        jsonArrayFrom(
            eb.selectFrom('dokumentPozycjaRozbicie as rozbicie')
                .select((eb) => [
                    "rozbicie.id",
                    "rozbicie.typ",
                    "rozbicie.dokumentPozycjaId",
                    "rozbicie.partiaProduktuId",
                    "rozbicie.magazynProduktId",
                    "rozbicie.magazynPartiaProduktuId",
                    "rozbicie.ruchMagazynowyId",
                    "rozbicie.cenaNetto",
                    "rozbicie.cenaBrutto",
                    "rozbicie.wartoscNetto",
                    "rozbicie.wartoscBrutto",
                    "rozbicie.wartoscVat",
                    "rozbicie.zyskMagazynowyNetto",
                    "rozbicie.ilosc",
                    "rozbicie.stawkaVatZakupuId",
                    "rozbicie.jednostkaMiaryId",
                ])
                .whereRef('rozbicie.dokumentPozycjaId', '=', 'pozycja.id')
                .orderBy('rozbicie.id')
        ).as('rozbicia')
    ])
    .whereRef('pozycja.dokumentId', '=', 'dokument.id')
    .orderBy('pozycja.id')
).as('pozycje')
])
.executeTakeFirstOrThrow()

6 replies

KKysely

•Created by tzezar on 7/24/2024 in #help

How to narrow type in select

Is it possible to narrow type from enum company, person to just person in select query?

const results = await db
    .selectFrom('receiver')
    .where('type', '=', 'person')
    .select(eb => [
        'receiver.type',
    ])
    .execute()

const results = await db
    .selectFrom('receiver')
    .where('type', '=', 'person')
    .select(eb => [
        'receiver.type',
    ])
    .execute()

5 replies

KKysely

•Created by tzezar on 7/20/2024 in #help

are nested joins supported?

Like this one:

knex .select('*') .from('users') .join('accounts', function () { this.on(function () { this.on('accounts.id', '=', 'users.account_id'); this.orOn('accounts.owner_id', '=', 'users.id'); }); });

knex .select('*') .from('users') .join('accounts', function () { this.on(function () { this.on('accounts.id', '=', 'users.account_id'); this.orOn('accounts.owner_id', '=', 'users.id'); }); });

4 replies

KKysely

•Created by tzezar on 7/13/2024 in #help

is there any helper to put all columns in json_build_object?

to avoid manuał typing all the columns?

4 replies

DTDrizzle Team

•Created by tzezar on 7/9/2024 in #help

How to type connection as conn or transaction?

My attempt was like below:

export type DBConn = PgTransaction<NodePgQueryResultHKT, Record<string, never>, ExtractTablesWithRelations<Record<string, never>>> | NodePgDatabase<Record<string, never>>

export type DBConn = PgTransaction<NodePgQueryResultHKT, Record<string, never>, ExtractTablesWithRelations<Record<string, never>>> | NodePgDatabase<Record<string, never>>

but passing transaction to function using it raise error. Any idea?

2 replies

DTDrizzle Team

•Created by tzezar on 7/9/2024 in #help

Does 'query' support subqueries with filters?

Is it possible to define subquery with filters for many to one relation?

2 replies

DTDrizzle Team

•Created by tzezar on 7/8/2024 in #help

applying migrations...error: unsafe use of new value "zewnetrzny"

adding model with enum like below (sorry for polish, ubiquitous language with domain masters)

export const typUzytkownikaEnum = pgEnum('typ_uzytkownika',
    ['zewnetrzny', 'wewnetrzny']
);

export const typUzytkownikaEnum = pgEnum('typ_uzytkownika',
    ['zewnetrzny', 'wewnetrzny']
);

raise error:

applying migrations...error: unsafe use of new value "zewnetrzny"

applying migrations...error: unsafe use of new value "zewnetrzny"

"zewnetrzny" is not listed as forbidden word in pg, why error then?

3 replies

DTDrizzle Team

•Created by tzezar on 7/7/2024 in #help

How to narrow type in select from enum?

During select I need to narrow enum type from multiple choices to one choice eg just 'product' schema

export const typAsortymentuEnum = pgEnum('typ_asortymentu', ['produkt', 'usluga']);
export const asortyment = pgTable('asortyment', {
    id: serial('id').primaryKey().notNull(),
    typ: typAsortymentuEnum('typ').notNull(),
   
})

export const typAsortymentuEnum = pgEnum('typ_asortymentu', ['produkt', 'usluga']);
export const asortyment = pgTable('asortyment', {
    id: serial('id').primaryKey().notNull(),
    typ: typAsortymentuEnum('typ').notNull(),
   
})

query

    const produkt = alias(asortyment, "produkt")
 const results = await db
        .select({
            id: produkt.id,
            typ: produkt.typ,
        })
        .from(produkt)
        .where(eq(produkt.typ, 'produkt'))
        .execute()

    const produkt = alias(asortyment, "produkt")
 const results = await db
        .select({
            id: produkt.id,
            typ: produkt.typ,
        })
        .from(produkt)
        .where(eq(produkt.typ, 'produkt'))
        .execute()

result typ value is typed as typ: "produkt" | "usluga"; and I need to narrow it to just product I would be glad for any tip how to accomplish this. Thanks!

2 replies

KKysely

•Created by tzezar on 7/3/2024 in #help

Is it possible to change type of returned value with jsonBuildObject?

I struggle typing reponse schema in fastify cuz query returns 'wartosc' as string but actually it returns number. Can't simply change zod reponse schema, cuz kysely types wartosc in query is as string, and there is type mismatch anyway. (sorry for polish, ubiquitous language with polish domain masters)

  jsonBuildObject({
      id: eb.ref('domyslnaStawkaVatSprzedazy.id'),
      nazwa: eb.ref('domyslnaStawkaVatSprzedazy.nazwa'),
      wartosc: eb.ref('domyslnaStawkaVatSprzedazy.wartosc'),
  }).as('domyslnaStawkaVatSprzedazy'),

  jsonBuildObject({
      id: eb.ref('domyslnaStawkaVatSprzedazy.id'),
      nazwa: eb.ref('domyslnaStawkaVatSprzedazy.nazwa'),
      wartosc: eb.ref('domyslnaStawkaVatSprzedazy.wartosc'),
  }).as('domyslnaStawkaVatSprzedazy'),

is something like this possible (simmillar to type casting with raw sql sql<number>? if not any tip how to handle this?

  jsonBuildObject({
      id: eb.ref('domyslnaStawkaVatSprzedazy.id'),
      nazwa: eb.ref('domyslnaStawkaVatSprzedazy.nazwa'),
      wartosc: eb.ref<number>('domyslnaStawkaVatSprzedazy.wartosc'),
  }).as('domyslnaStawkaVatSprzedazy'),

  jsonBuildObject({
      id: eb.ref('domyslnaStawkaVatSprzedazy.id'),
      nazwa: eb.ref('domyslnaStawkaVatSprzedazy.nazwa'),
      wartosc: eb.ref<number>('domyslnaStawkaVatSprzedazy.wartosc'),
  }).as('domyslnaStawkaVatSprzedazy'),

I would really like to put data in proper shape in sql to avoid extra mapping on backend code

16 replies

KKysely

•Created by tzezar on 7/1/2024 in #help

Is it possible to configure to which types database columns are introspected?

We use kysely-codegen and we would like to use those generated types, but they ofter are translated to diffrent types we want. Lets say int id is introspected to Generated<number> (we would like number type) or decimals are introspected to Numeric (we would like just number)

6 replies

CC#

•Created by tzezar on 6/1/2024 in #help

ASP.NET Core Identity - How to manage roles?

HEY! I'm starting with asp.net and can't figure out a certain thing. I want to implement authentication and authorization (RBAC) to rest api. From what I can see this is what 'Identity' is used for, right? While authentication seems straight forward, I don't completely understand how roles should be managed. Do you create CRUD endpoints yourself to manage them?

2 replies

PPrisma

•Created by tzezar on 5/15/2024 in #help-and-questions

Does prisma allow use of Shared Primary Key?

if so, what should the model look like? Sorry if this has already been asked somewhere, I can't find anything.

2 replies

KKysely

•Created by tzezar on 5/6/2024 in #help

concat in postgres

Hey! I want to concat few columns to get address, however query is built in wrong way cuz postgres uses || to concat. What am I doing wrong?

let warehouses = await db
      .selectFrom('warehouse as w')
      .leftJoin('address as a', 'w.addressId', 'a.id')
      .select((eb) => [
          'w.id',
          'w.name',
          'w.description',
          eb.fn<string>('concat', [
              'a.city',
              eb.val(' '),
              'a.district',
              eb.val(' '),
              'a.street',
          ]).as('address')
      ])
      .execute()

let warehouses = await db
      .selectFrom('warehouse as w')
      .leftJoin('address as a', 'w.addressId', 'a.id')
      .select((eb) => [
          'w.id',
          'w.name',
          'w.description',
          eb.fn<string>('concat', [
              'a.city',
              eb.val(' '),
              'a.district',
              eb.val(' '),
              'a.street',
          ]).as('address')
      ])
      .execute()

select
    "w"."id",
    "w"."name",
    "w"."description",
    concat("a"."city",
    $1,
    "a"."district",
    $2,
    "a"."street") as "address"
from
    "warehouse" as "w"
left join "address" as "a" on
    "w"."address_id" = "a"."id"
',
  parameters: [ ' ', ' ' ]

select
    "w"."id",
    "w"."name",
    "w"."description",
    concat("a"."city",
    $1,
    "a"."district",
    $2,
    "a"."street") as "address"
from
    "warehouse" as "w"
left join "address" as "a" on
    "w"."address_id" = "a"."id"
',
  parameters: [ ' ', ' ' ]

I made it with raw sql for now

sql<string>`concat(a.city || ' ' || a.street)`.as('address')

sql<string>`concat(a.city || ' ' || a.street)`.as('address')

but I wonder what is proper way

7 replies

Gaming

Programming