mw10013 Posts - Answer Overflow

mw10013

Explore posts from servers

•Created by mw10013 on 5/8/2024 in #help

`sql` in D1 batch causes "TypeError: Cannot read properties of undefined (reading 'bind')"

Using sql in D1 batch causes "TypeError: Cannot read properties of undefined (reading 'bind')"

    const upsertSql = sql`insert into ${schema.subscriptions} 
(id, status, plan, cycle_started_at, customer_id, created_at, updated_at)
values (${subscriptionData.id}, ${subscriptionData.status}, ${plan}, ${cycleStartedAt.getTime()}, ${subscriptionData.customerId}, ${createdAt.getTime()}, ${updatedAt.getTime()})
on conflict (${schema.subscriptions.id}) do update set status = ${subscriptionData.status}, plan = ${plan}, cycle_started_at = ${cycleStartedAt.getTime()}, updated_at = ${updatedAt.getTime()}
where ${updatedAt.getTime()} > ${schema.subscriptions.updatedAt}
on conflict (${schema.subscriptions.customerId}) do update set id = ${subscriptionData.id}, status = ${subscriptionData.status}, plan = ${plan}, cycle_started_at = ${cycleStartedAt.getTime()}, created_at = ${createdAt.getTime()}, updated_at = ${updatedAt.getTime()}
where ${createdAt.getTime()} > ${schema.subscriptions.createdAt}
returning *`;

    const [[batchSubscription]] = await db.batch([
      db.all<schema.Subscription>(upsertSql),
    ]);

    const upsertSql = sql`insert into ${schema.subscriptions} 
(id, status, plan, cycle_started_at, customer_id, created_at, updated_at)
values (${subscriptionData.id}, ${subscriptionData.status}, ${plan}, ${cycleStartedAt.getTime()}, ${subscriptionData.customerId}, ${createdAt.getTime()}, ${updatedAt.getTime()})
on conflict (${schema.subscriptions.id}) do update set status = ${subscriptionData.status}, plan = ${plan}, cycle_started_at = ${cycleStartedAt.getTime()}, updated_at = ${updatedAt.getTime()}
where ${updatedAt.getTime()} > ${schema.subscriptions.updatedAt}
on conflict (${schema.subscriptions.customerId}) do update set id = ${subscriptionData.id}, status = ${subscriptionData.status}, plan = ${plan}, cycle_started_at = ${cycleStartedAt.getTime()}, created_at = ${createdAt.getTime()}, updated_at = ${updatedAt.getTime()}
where ${createdAt.getTime()} > ${schema.subscriptions.createdAt}
returning *`;

    const [[batchSubscription]] = await db.batch([
      db.all<schema.Subscription>(upsertSql),
    ]);

Running the sql outside of batch works fine.

    const [subscription] = await db.all<schema.Subscription>(upsertSql);

    const [subscription] = await db.all<schema.Subscription>(upsertSql);

Has anybody gotten sql to work inside of D1 batch?

1 replies

DTDrizzle Team

•Created by mw10013 on 5/8/2024 in #help

Multiple onConflictDoUpdate's supported?

I'm using two onConflictDoUpdate() and seems only the last one is picked up when run. Are multiple onConflictDoUpdate supported?

    const [subscription] = await db
      .insert(schema.subscriptions)
      .values({
        id: subscriptionData.id,
        status: subscriptionData.status,
        plan,
        cycleStartedAt,
        customerId: subscriptionData.customerId,
        createdAt,
        updatedAt,
      })
      .onConflictDoUpdate({
        target: schema.subscriptions.id,
        set: {
          status: subscriptionData.status,
          plan,
          cycleStartedAt,
          updatedAt,
        },
        setWhere: sql`${updatedAt.getTime()} > ${schema.subscriptions.updatedAt}`,
      })
      .onConflictDoUpdate({
        target: schema.subscriptions.customerId,
        set: {
          id: subscriptionData.id,
          status: subscriptionData.status,
          plan,
          cycleStartedAt,
          createdAt,
          updatedAt,
        },
        setWhere: sql`${createdAt.getTime()} > ${schema.subscriptions.createdAt}`,
      })
      .returning();

    const [subscription] = await db
      .insert(schema.subscriptions)
      .values({
        id: subscriptionData.id,
        status: subscriptionData.status,
        plan,
        cycleStartedAt,
        customerId: subscriptionData.customerId,
        createdAt,
        updatedAt,
      })
      .onConflictDoUpdate({
        target: schema.subscriptions.id,
        set: {
          status: subscriptionData.status,
          plan,
          cycleStartedAt,
          updatedAt,
        },
        setWhere: sql`${updatedAt.getTime()} > ${schema.subscriptions.updatedAt}`,
      })
      .onConflictDoUpdate({
        target: schema.subscriptions.customerId,
        set: {
          id: subscriptionData.id,
          status: subscriptionData.status,
          plan,
          cycleStartedAt,
          createdAt,
          updatedAt,
        },
        setWhere: sql`${createdAt.getTime()} > ${schema.subscriptions.createdAt}`,
      })
      .returning();

insert into "subscriptions" ("id", "status", "plan", "cycle_started_at", "customer_id", "created_at", "updated_at") 
values (?, ?, ?, ?, ?, ?, ?) 
on conflict ("subscriptions"."customer_id") 
do update set "id" = ?, "status" = ?, "plan" = ?, "cycle_started_at" = ?, "created_at" = ?, "updated_at" = ? 
where ? > "subscriptions"."created_at" returning "id", "status", "plan", "cycle_started_at", "customer_id", "created_at", "updated_at"

insert into "subscriptions" ("id", "status", "plan", "cycle_started_at", "customer_id", "created_at", "updated_at") 
values (?, ?, ?, ?, ?, ?, ?) 
on conflict ("subscriptions"."customer_id") 
do update set "id" = ?, "status" = ?, "plan" = ?, "cycle_started_at" = ?, "created_at" = ?, "updated_at" = ? 
where ? > "subscriptions"."created_at" returning "id", "status", "plan", "cycle_started_at", "customer_id", "created_at", "updated_at"

5 replies

Gaming

Programming