LiquiFruit Posts - Answer Overflow

LiquiFruit

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

How to type a Table

I want my data access layer to depend on my entities layer. I want to define a ts type for an entity, say a Student type, and then implement a drizzle table that's based off that type. Something like const studentTable = sqliteTable<Student>(...) would be perfect the syntax imo. I would expect this to throw errors if it was missing a field or incorrectly typed field. Is there a way to do this atm? Maybe a hack with drizzle-zod?

11 replies

DTDrizzle Team

•Created by LiquiFruit on 12/5/2023 in #help

Refactor transaction to batched expression

Im trying to refactor a transaction to a batched expression. I need help with basic sql concepts involving updating tables based on sub queries, and also drizzle syntax such as

sql`...`.as(...)`

sql`...`.as(...)`

. This is my current transaction to expire orders and release related stock:

await db.transaction(async (tx) => {
      // Get orders than are expired
      const expiredOrders = await tx
        .select({ id: orders.id })
        .from(orders)
        .where(
          and(
            // Only orders that are unpaid can be expired
            eq(orders.status, "UNPAID"),

            // Dates are stored as numbers and can be compared as such
            lt(orders.paymentDeadline, new Date()),
          ),
        )

      // Return early if there are no expired orders
      if (expiredOrders.length === 0) return "No expired orders" as const

      // Get a list of expired order IDs
      const expiredOrderIds = expiredOrders.map((eo) => eo.id)

      // Mark these orders as expired
      // const expireOrdersRes =
      await tx
        .update(orders)
        .set({
          status: "EXPIRED",
        })
        .where(inArray(orders.id, expiredOrderIds))

      // Get a list of products from expired orders, and the respective amount of stock
      const expiredProducts = await tx
        .select({
          id: orderProducts.productId,
          amount: sql<number>`count(*)`.as("amount"),
        })
        .from(orderProducts)
        .where(inArray(orderProducts.orderId, expiredOrderIds))
        .groupBy(orderProducts.productId)

      // Update these products' stock by the respective amounts
      // TODO: Batch these updates somehow
      expiredProducts.forEach(async (ep) => {
        await tx
          .update(products)
          .set({ stock: sql`${products.stock} + ${ep.amount}` })
          .where(eq(products.id, ep.id))
      })
    })

await db.transaction(async (tx) => {
      // Get orders than are expired
      const expiredOrders = await tx
        .select({ id: orders.id })
        .from(orders)
        .where(
          and(
            // Only orders that are unpaid can be expired
            eq(orders.status, "UNPAID"),

            // Dates are stored as numbers and can be compared as such
            lt(orders.paymentDeadline, new Date()),
          ),
        )

      // Return early if there are no expired orders
      if (expiredOrders.length === 0) return "No expired orders" as const

      // Get a list of expired order IDs
      const expiredOrderIds = expiredOrders.map((eo) => eo.id)

      // Mark these orders as expired
      // const expireOrdersRes =
      await tx
        .update(orders)
        .set({
          status: "EXPIRED",
        })
        .where(inArray(orders.id, expiredOrderIds))

      // Get a list of products from expired orders, and the respective amount of stock
      const expiredProducts = await tx
        .select({
          id: orderProducts.productId,
          amount: sql<number>`count(*)`.as("amount"),
        })
        .from(orderProducts)
        .where(inArray(orderProducts.orderId, expiredOrderIds))
        .groupBy(orderProducts.productId)

      // Update these products' stock by the respective amounts
      // TODO: Batch these updates somehow
      expiredProducts.forEach(async (ep) => {
        await tx
          .update(products)
          .set({ stock: sql`${products.stock} + ${ep.amount}` })
          .where(eq(products.id, ep.id))
      })
    })

19 replies

Gaming

Programming