tzezar Posts - Answer Overflow

Topics

tzezar

Explore posts from servers

CC#NNuxt KKysely PPrisma DTDrizzle Team

•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

•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

•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

•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

•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

•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

•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

•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

•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

•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

•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

•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

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

generating raw sql?

I think I saw somewhere an option that you can build a query using kysely and convert it to raw sql, but I can't find that anywhere. Does such an option exist or did I get something mixed up?

5 replies

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

transaction takes too long

I have a problem with transactions. What might be the reason that db.transaction() adds more than 10ms latency?

Query took 2.3298759999997856
Validation took 0.006788000000597094
Update took 0.6013680000005479
Refresh took 0.9085340000001452
Transaction took 13.280770999999731

Query took 2.3298759999997856
Validation took 0.006788000000597094
Update took 0.6013680000005479
Refresh took 0.9085340000001452
Transaction took 13.280770999999731

   "kysely": "^0.27.3",
   "pg": "^8.11.5",

   "kysely": "^0.27.3",
   "pg": "^8.11.5",

+ postgresql Below I will attach code that generated those results. Transaction overhead is present in every function call. What might be the reason for the more than 10ms spent on a transaction?

7 replies

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

newbie need help with json_build_object

Hi! Just starting with kysely and encountered problem I cant solve. I want to build raw query with kysely builder...

  JSON_BUILD_OBJECT('id', unit.id, 'name', unit.name) AS unit

  JSON_BUILD_OBJECT('id', unit.id, 'name', unit.name) AS unit

this part gives me trouble. I would be grateful for your help

        sql`
        SELECT 
            sku.id,
            JSON_BUILD_OBJECT('id', unit.id, 'name', unit.name) AS unit
        FROM sku
        LEFT JOIN unit ON unit.id = sku.stock_unit_id
        LIMIT 100;
    `


    let results = await kyselydb
        .selectFrom('sku')
        .leftJoin('unit as u', 'u.id', 'sku.stock_unit_id')
        .selectAll('sku')
        .select((eb) => [
            jsonObjectFrom(

            ).as('unit')
        ])
        .limit(10)
        .execute()

        sql`
        SELECT 
            sku.id,
            JSON_BUILD_OBJECT('id', unit.id, 'name', unit.name) AS unit
        FROM sku
        LEFT JOIN unit ON unit.id = sku.stock_unit_id
        LIMIT 100;
    `


    let results = await kyselydb
        .selectFrom('sku')
        .leftJoin('unit as u', 'u.id', 'sku.stock_unit_id')
        .selectAll('sku')
        .select((eb) => [
            jsonObjectFrom(

            ).as('unit')
        ])
        .limit(10)
        .execute()

6 replies

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

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 sql`BEGIN;
    SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;`
    .execute(db)

    // some kysely queries

    await sql`COMMIT;`
    .execute(db)

    res.send('ok')
} catch (error) {
    await sql`ROLLBACK;`
    .execute(db)

    next(error)
}

try {
    await sql`BEGIN;
    SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;`
    .execute(db)

    // some kysely queries

    await sql`COMMIT;`
    .execute(db)

    res.send('ok')
} catch (error) {
    await sql`ROLLBACK;`
    .execute(db)

    next(error)
}

8 replies

•Created by tzezar on 11/20/2023 in #help

What is easiest way to get count alongside data for pagination?

I am running second query with raw sql SELECT COUNT(id) from table_name. is this legit?

6 replies

•Created by tzezar on 11/11/2023 in #help

Can you mix kysely with pg transactions?

For example:

try {
  await client.query('BEGIN')
   
  <KYSELY CODE HERE>

  await client.query('COMMIT')
} catch (e) {
  await client.query('ROLLBACK')
  throw e
} finally {
  client.release()
}

try {
  await client.query('BEGIN')
   
  <KYSELY CODE HERE>

  await client.query('COMMIT')
} catch (e) {
  await client.query('ROLLBACK')
  throw e
} finally {
  client.release()
}

6 replies

•Created by tzezar on 11/7/2023 in #help

How to retrieve field name raising error in database?

I am using kysely, zod, expressjs. I need to return field error and display it to the end user in frontend form. However I am not sure how to do it with kysely. Code for backend is pretty simple:

const FormData = z.object({
  name: z.string({ required_error: "name is required" }),
});

export const createUnitOfMeasurement: RequestHandler = async (
  req,
  res,
  next
) => {
  try {
    const { name } = FormData.parse(req.body);
    const r = await db
      .insertInto("unit_of_measurement")
      .values({
        name: name,
      })
      .returningAll()
      .executeTakeFirstOrThrow();
    res.send(r);
  } catch (err) {
    next(err);
  }
};

const FormData = z.object({
  name: z.string({ required_error: "name is required" }),
});

export const createUnitOfMeasurement: RequestHandler = async (
  req,
  res,
  next
) => {
  try {
    const { name } = FormData.parse(req.body);
    const r = await db
      .insertInto("unit_of_measurement")
      .values({
        name: name,
      })
      .returningAll()
      .executeTakeFirstOrThrow();
    res.send(r);
  } catch (err) {
    next(err);
  }
};

and next(err) is catching error and sending it in response with valid status code. However this is error generated by Kysely: <Error in second message> I would be super gratefull if you give me any suggestion how to extract field name raising error.

6 replies

•Created by tzezar on 10/31/2023 in #help

Select by id?

With this code:

export const getProductById: RequestHandler = async (req, res, next) => {
  const { id } = req.params
  try {
    const result = await db.selectFrom("product").where('id', '=', id).selectAll().execute();
    res.send(result);
  } catch (err) {
    next(err);
  }
};

export const getProductById: RequestHandler = async (req, res, next) => {
  const { id } = req.params
  try {
    const result = await db.selectFrom("product").where('id', '=', id).selectAll().execute();
    res.send(result);
  } catch (err) {
    next(err);
  }
};

I get type script error on in in query:

Argument of type 'string' is not assignable to parameter of type 'OperandValueExpressionOrList<PublicSchema, "product", "id">'.ts(2345)
const id: string

Argument of type 'string' is not assignable to parameter of type 'OperandValueExpressionOrList<PublicSchema, "product", "id">'.ts(2345)
const id: string

any clue how to fix it?

11 replies