Gabriel Gigante Posts - Answer Overflow

Gabriel Gigante

•Created by Gabriel Gigante on 4/26/2025 in #help

Can I set a UNIQUE constraint with a conditional?

I need to ensure uniqueness in an attribute but this attribute can be NULL. A simple .unique in my schema does not work in a sqlite database. My goal is to achieve the equivalent of CREATE UNIQUE INDEX unique_product_barcode_not_null ON products(barCode) WHERE barCode IS NOT NULL; using the Drizzle schema api.

1 replies

DTDrizzle Team

•Created by Gabriel Gigante on 4/25/2025 in #help

LibsqlError: SERVER_ERROR: Server returned HTTP status 404

Hi guys, anyone else getting this error on running large migrations?

try {
      const response = await db.transaction(async (tx) => {
        const [{ insertedOrderId }] = await tx
          .insert(orders)
          .values({ id, totalPrice, customerId, obs })
          .returning({ insertedOrderId: orders.id })

        for (const { id, quantity, customProductPrice, obs } of products) {
          const product = await tx.select().from(productsSchema).where(eq(productsSchema.id, id)).get()

          if (!product) {
            throw new Error(`Product with ID ${id} not found`)
          }

          const updatedProductAvailableQuantity = (product?.availableQuantity ?? 0) - quantity

          await tx
            .update(productsSchema)
            .set({ availableQuantity: updatedProductAvailableQuantity })
            .where(eq(productsSchema.id, product.id))

          await tx.insert(ordersProducts).values({
            orderId: insertedOrderId,
            productId: product.id,
            productCostPrice: product.costPrice,
            productPrice: product.price,
            customProductPrice,
            quantity,
            obs,
          })
        }

        const response = await tx.select().from(orders).where(eq(orders.id, insertedOrderId)).get()

        if (!response) {
          throw new Error('Failed to create order')
        }

        return response
      })

      if (!response) {
        throw new Error('Transaction failed')
      }

      return response
    } catch (error) {
      console.error('Transaction error:', error)
      throw error // Re-throw the error to be handled by the caller
    }

try {
      const response = await db.transaction(async (tx) => {
        const [{ insertedOrderId }] = await tx
          .insert(orders)
          .values({ id, totalPrice, customerId, obs })
          .returning({ insertedOrderId: orders.id })

        for (const { id, quantity, customProductPrice, obs } of products) {
          const product = await tx.select().from(productsSchema).where(eq(productsSchema.id, id)).get()

          if (!product) {
            throw new Error(`Product with ID ${id} not found`)
          }

          const updatedProductAvailableQuantity = (product?.availableQuantity ?? 0) - quantity

          await tx
            .update(productsSchema)
            .set({ availableQuantity: updatedProductAvailableQuantity })
            .where(eq(productsSchema.id, product.id))

          await tx.insert(ordersProducts).values({
            orderId: insertedOrderId,
            productId: product.id,
            productCostPrice: product.costPrice,
            productPrice: product.price,
            customProductPrice,
            quantity,
            obs,
          })
        }

        const response = await tx.select().from(orders).where(eq(orders.id, insertedOrderId)).get()

        if (!response) {
          throw new Error('Failed to create order')
        }

        return response
      })

      if (!response) {
        throw new Error('Transaction failed')
      }

      return response
    } catch (error) {
      console.error('Transaction error:', error)
      throw error // Re-throw the error to be handled by the caller
    }

this transaction fails if products has length greater than 15. These queries works if running without a transaction.

1 replies

Gaming

Programming