tzezar Posts - Answer Overflow

tzezar

Explore posts from servers

•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

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

DTDrizzle Team

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

Default isolation level for transaction

Is it possible to globally set default isolation level?

1 replies

DTDrizzle Team

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

coalesce

I wonder how to make COALESCE(smth, 0) with drizzle? Postgresql

            const sq = tx.select({
                paymentId: settlementTransaction.paymentId,
                alreadyReleased: sum(settlementTransaction.amount).as('alreadyReleased')
            })
                .from(settlementTransaction)
                .groupBy(settlementTransaction.paymentId)
                .as('sq');
                

            let r = await tx
                .select()
                .from(payment)
                .leftJoin(sq, eq(sq.paymentId, payment.id))
                .where(and(eq(payment.fromCustomerId, s.fromCustomerId), eq(payment.type, 'PREPAID')))

            const sq = tx.select({
                paymentId: settlementTransaction.paymentId,
                alreadyReleased: sum(settlementTransaction.amount).as('alreadyReleased')
            })
                .from(settlementTransaction)
                .groupBy(settlementTransaction.paymentId)
                .as('sq');
                

            let r = await tx
                .select()
                .from(payment)
                .leftJoin(sq, eq(sq.paymentId, payment.id))
                .where(and(eq(payment.fromCustomerId, s.fromCustomerId), eq(payment.type, 'PREPAID')))

I need COALESCE(alreadyReleased, 0)

1 replies

DTDrizzle Team

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

Conditional join

Hey! I am looking for a way of making conditional joins based on some statement. I found only one post in this topic https://discord.com/channels/1043890932593987624/1128636955270324275 that does not help me much. lets say this is my query:

let query = await db
            .select()
            .from(documentLine)
            .leftJoin(document, eq(document.id, documentLine.documentId))
            .leftJoin(user, eq(user.id, documentLine.userId))

let query = await db
            .select()
            .from(documentLine)
            .leftJoin(document, eq(document.id, documentLine.documentId))
            .leftJoin(user, eq(user.id, documentLine.userId))

I need to make joins conditional, because some filters require joins and in other cases I do not need join so its like wasted computation time. So example showing my need:

if (filters.includes(documentId)){
query.add(.leftJoin(document, eq(document.id, documentLine.documentId)))
}

if (filters.includes(documentId)){
query.add(.leftJoin(document, eq(document.id, documentLine.documentId)))
}

I would be glad for any tip how to do this.

6 replies

DTDrizzle Team

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

will joins in delete queries be supported?

example:

   await db
            .delete(orderSubline)
            .leftJoin(orderLine, eq(orderLine.id, orderSubline.orderLineId))
            .where(eq(orderLine.orderId, orderId))

   await db
            .delete(orderSubline)
            .leftJoin(orderLine, eq(orderLine.id, orderSubline.orderLineId))
            .where(eq(orderLine.orderId, orderId))

2 replies

DTDrizzle Team

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

How should locks be done with drizzle?

Table or row locks in postgres. How can select for update be done in drizzle?

5 replies

DTDrizzle Team

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

Inserting into columns of type Enum

Hi! I am not sure how to insert data into enum column type...

export const batchTransactionTypeEnum = pgEnum("type", [
    "IN",
    "OUT"
]);

export const batchTransaction = pgTable('batch_transaction', {
    id: serial('id').primaryKey(),
    type: batchTransactionTypeEnum("type"),
})

export const batchTransactionTypeEnum = pgEnum("type", [
    "IN",
    "OUT"
]);

export const batchTransaction = pgTable('batch_transaction', {
    id: serial('id').primaryKey(),
    type: batchTransactionTypeEnum("type"),
})

and query:

        let bt = await tx.insert(batchTransaction).values({
          batchId: b.id,
          type: "IN",
          quantityInBaseUnit: ds.document_subline.quantityInBaseUnit,
        });

        let bt = await tx.insert(batchTransaction).values({
          batchId: b.id,
          type: "IN",
          quantityInBaseUnit: ds.document_subline.quantityInBaseUnit,
        });

above code results in ungly error:

Query: insert into "batch_transaction" ("id", "quantity_in_base_unit", "type", "created_at", "batch_id") values (default, $1, $2, default, $3) -- params: ["25.00", "IN", 9]
H:\projects\wms\backend-express\node_modules\postgres\cjs\src\connection.js:790
    const error = Errors.postgres(parseError(x))
                         ^
PostgresError: nieprawidłowa wartość wejścia dla enumeracji type: "IN"
    at ErrorResponse (H:\projects\wms\backend-express\node_modules\postgres\cjs\src\connection.js:790:26)
    at handle (H:\projects\wms\backend-express\node_modules\postgres\cjs\src\connection.js:476:6)
    at Socket.data (H:\projects\wms\backend-express\node_modules\postgres\cjs\src\connection.js:315:9)
    at Socket.emit (node:events:514:28)
    at Socket.emit (node:domain:489:12)
    at addChunk (node:internal/streams/readable:324:12)
    at readableAddChunk (node:internal/streams/readable:297:9)
    at Socket.Readable.push (node:internal/streams/readable:234:10)
    at TCP.onStreamRead (node:internal/stream_base_commons:190:23) {
  severity_local: 'BŁĄD',
  severity: 'ERROR',
  code: '22P02',
  where: "unnamed portal parameter $2 = '...'",
  file: 'enum.c',
  line: '133',
  routine: 'enum_in'
}

Query: insert into "batch_transaction" ("id", "quantity_in_base_unit", "type", "created_at", "batch_id") values (default, $1, $2, default, $3) -- params: ["25.00", "IN", 9]
H:\projects\wms\backend-express\node_modules\postgres\cjs\src\connection.js:790
    const error = Errors.postgres(parseError(x))
                         ^
PostgresError: nieprawidłowa wartość wejścia dla enumeracji type: "IN"
    at ErrorResponse (H:\projects\wms\backend-express\node_modules\postgres\cjs\src\connection.js:790:26)
    at handle (H:\projects\wms\backend-express\node_modules\postgres\cjs\src\connection.js:476:6)
    at Socket.data (H:\projects\wms\backend-express\node_modules\postgres\cjs\src\connection.js:315:9)
    at Socket.emit (node:events:514:28)
    at Socket.emit (node:domain:489:12)
    at addChunk (node:internal/streams/readable:324:12)
    at readableAddChunk (node:internal/streams/readable:297:9)
    at Socket.Readable.push (node:internal/streams/readable:234:10)
    at TCP.onStreamRead (node:internal/stream_base_commons:190:23) {
  severity_local: 'BŁĄD',
  severity: 'ERROR',
  code: '22P02',
  where: "unnamed portal parameter $2 = '...'",
  file: 'enum.c',
  line: '133',
  routine: 'enum_in'
}

Found simmilar topic, but solution with adding cosnt to enum value does not work. https://discord.com/channels/1043890932593987624/1120919676457848946

7 replies

DTDrizzle Team

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

Modified select() on an existing query example

Hey, I would like to reuse first query with some left joins etc. to obtain results count with second query I found some posts, and it seems its possible, but cant get it working. I just need to alternate select() to other fields, more precisely to a simple sql count expression.

// random query with left join
    const query = db
      .select({
        id: documentLine.id,
        documentId: documentLine.documentId,
        productVariant: {
          id: productVariant.id,
          fullName: productVariant.fullName,
        },
      })
      .from(documentLine)
      .leftJoin(
        productVariant,
        eq(productVariant.id, documentLine.productVariantId)
      )
// it applies fileters, ordering, pagination
    let paginatedResults = await getPaginatedResponse(query, filters, ordering, limit, offset)

// need to copy query with all filters and more important with joins
    let countQuery = query.$dynamic().where(and(...filters))

// sample attempt to alternate select
    let count = countQuery._.selectedFields({ count: sql<string>`count(*)` })

// random query with left join
    const query = db
      .select({
        id: documentLine.id,
        documentId: documentLine.documentId,
        productVariant: {
          id: productVariant.id,
          fullName: productVariant.fullName,
        },
      })
      .from(documentLine)
      .leftJoin(
        productVariant,
        eq(productVariant.id, documentLine.productVariantId)
      )
// it applies fileters, ordering, pagination
    let paginatedResults = await getPaginatedResponse(query, filters, ordering, limit, offset)

// need to copy query with all filters and more important with joins
    let countQuery = query.$dynamic().where(and(...filters))

// sample attempt to alternate select
    let count = countQuery._.selectedFields({ count: sql<string>`count(*)` })

https://discord.com/channels/1043890932593987624/1104858040936706140 https://github.com/drizzle-team/drizzle-orm/issues/561

2 replies

DTDrizzle Team

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

all table definition type

Hi everyone! Is there possibility to obtain type of all defined models? I created helper function that takes one particullar table as parameter and would love to have it typed.

2 replies

DTDrizzle Team

•Created by tzezar on 12/29/2023 in #help

dynamic subquery?

Is it possible to make dynamic subquery? I need to filter subquery by some value and cant figure out how to make it.

    let sq = db
      .select({
        lineId: documentLine.id,
        name: productVariant.fullName,
      })
      .from(documentLine)
      .leftJoin(
        productVariant,
        eq(productVariant.id, documentLine.productVariantId)
      )
      .limit(limit)
      .offset(offset)
      .where(eq(documentLine.documentId, 1))
      .as("line");

    const dynamicQuery = sq.$dynamic();
    if (documentId) {
      dynamicQuery.where(eq(documentLine.documentId, +documentId));
    }

    let sq = db
      .select({
        lineId: documentLine.id,
        name: productVariant.fullName,
      })
      .from(documentLine)
      .leftJoin(
        productVariant,
        eq(productVariant.id, documentLine.productVariantId)
      )
      .limit(limit)
      .offset(offset)
      .where(eq(documentLine.documentId, 1))
      .as("line");

    const dynamicQuery = sq.$dynamic();
    if (documentId) {
      dynamicQuery.where(eq(documentLine.documentId, +documentId));
    }

13 replies

DTDrizzle Team

•Created by tzezar on 12/21/2023 in #help

how to use types on frontend

Hey guyz, according to: https://discord.com/channels/1043890932593987624/1179496917420490782 it is possible to get types for inserts or reads; thats super dope, but I have a question, I am not sure how to use those types on fronted. Got two folders, one for backend with expressjs + drizzle and second one for frontend (svelte + sveltekit). How can I import those types? backend

export type ProductVariant= typeof productVariant.$inferInsert;

export type ProductVariant= typeof productVariant.$inferInsert;

fronted

???

???

8 replies

DTDrizzle Team

•Created by tzezar on 12/3/2023 in #help

circular foreign keys

I have separate files for each table:

export const productVariant = pgTable("product_variant", {
  id: serial("id").primaryKey(),
  defaultPackageSizeId: integer("default_package_size_id")
    .notNull()
    .references(() => packageSize.id),
});


export const packageSize = pgTable("package_size", {
  id: serial("id").primaryKey(),
  productVariantId: integer("product_variant_id").references(
    () => productVariant.id
  ),
});

export const productVariant = pgTable("product_variant", {
  id: serial("id").primaryKey(),
  defaultPackageSizeId: integer("default_package_size_id")
    .notNull()
    .references(() => packageSize.id),
});


export const packageSize = pgTable("package_size", {
  id: serial("id").primaryKey(),
  productVariantId: integer("product_variant_id").references(
    () => productVariant.id
  ),
});

but this does not work, error:

packageSize implicitly has type 
 because it does not have a type annotation and is referenced directly or indirectly in its own initializer.

packageSize implicitly has type 
 because it does not have a type annotation and is referenced directly or indirectly in its own initializer.

How to fix it?

4 replies

DTDrizzle Team

•Created by tzezar on 12/3/2023 in #help

select and alter columns with drizzle-zod

Hi, I want to create validation schema for update patch request. Not sure how it should be done. I want to (based on drizzle schema) select which field is updatable and mark everyfield as optional. I tried:

  
const requestSchema = createDocumentSublineSchema.pick({
  quantityInBaseUnit: true,
});

const updateDocumentSublineSchema = createInsertSchema(documentSubline, {
  quantityInBaseUnit: (schema) => schema.quantityInBaseUnit.optional(),
});

  
const requestSchema = createDocumentSublineSchema.pick({
  quantityInBaseUnit: true,
});

const updateDocumentSublineSchema = createInsertSchema(documentSubline, {
  quantityInBaseUnit: (schema) => schema.quantityInBaseUnit.optional(),
});

this does not work, I must be missing something

4 replies

DTDrizzle Team

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

Do I have to import table name type for select query?

Doing this simple query:

dbDrizzle.select({
      id: user.id
    }).from(user)

dbDrizzle.select({
      id: user.id
    }).from(user)

I get no intelisense with ts etc. after importing import { user } from "../../../drizzle/migrations/schema"; it works. This is the way or I messed up configuration?

4 replies

Gaming

Programming