[BUG]: Error on upsert with UNIQUE constraint in SQLite using onConflictDoUpdate

[sejoalfaro]

What version of drizzle-orm are you using?

0.29.5

What version of drizzle-kit are you using?

0.20.18

Description

I am trying to do an upsert operation on a SQLite table with a UNIQUE constraint involving the fields slug and organization_id. However, when a row with the slug and the organization_id already exist, instead of updating the records, a UNIQUE constraint violation error is thrown. I am using turso!

Steps to Reproduce:

1. Table definition in SQLite:

export const categories = sqliteTable('categories', {
    id: text('id').$defaultFn(() => createId()).primaryKey(),
    name: text("name").notNull(),
    slug: text("slug").notNull(),
    description: text('description'),
    color: text('color').default("")
    organization_id: text("organization_id").references(() => organizations.id).notNull(),
}, (table) => ({
    unq: unique("categories_slug_organization").on(table.slug, table.organization_id),
}));

2. Attempted upsert function:

export async function upsert(payload: Omit<ICategories, "id">): Promise<ICategories> {
    const user = await currentUser();
    const [createdCategory] = await db.insert(categories).values({...payload, organization_id: user?.organization || ""})
    .onConflictDoUpdate({target: categories.slug, set: {name: payload.name, state: "", organization_id: user?.organization || ""}})
    .returning();

    return createdCategory as ICategories;
}

3. Error received when executing the function where slug and organization_id already exist:

⨯ LibsqlError: SQLITE_CONSTRAINT: SQLite error: UNIQUE constraint failed: categories.slug, categories.organization_id
    at async Module.post (./src/actions/services/category-service.ts:36:31)
    at async createCategory (./src/actions/category/category-actions.tsx:43:22)
Cause: [ResponseError: SQLite error: UNIQUE constraint failed: categories.slug, categories.organization_id] {
  code: 'SQLITE_CONSTRAINT',
  proto: {
    message: 'SQLite error: UNIQUE constraint failed: categories.slug, categories.organization_id',
    code: 'SQLITE_CONSTRAINT'
  }
}

Expected behavior

I expected the existing record to be updated with the new values provided in the payload.

Actual Result:

The process fails with a UNIQUE constraint error, indicating it cannot insert a record due to the violation of the uniqueness constraint on categories.slug and categories.organization_id.

Environment & setup:

Node.js: 18.18.2
drizzle-orm: 0.29.5
drizzle-kit: 0.20.18