[BUG]: using `returning` with `onConflictDoNothing` does not return anything

[jamiehaywood]

What version of drizzle-orm are you using?

0.31.2

What version of drizzle-kit are you using?

0.22.5

Describe the Bug

import { drizzle } from "drizzle-orm/bun-sqlite";
import { Database } from "bun:sqlite";
import * as schema from "./schema";

const sqlite = new Database("sqlite.db");
const db = drizzle(sqlite);

const book = {
  id: "1",
  title: "The Great Gatsby",
  description: "A book about a rich guy",
};

const firstRes = await db.insert(schema.books).values(book).returning().onConflictDoNothing();
// [{id: "1",title: "The Great Gatsby",description: "A book about a rich guy"}]

const secondRes = await db.insert(schema.books).values(book).returning().onConflictDoNothing();
// []

Expected behavior

I would expect returning to always return from the insert statement even if there's a conflict

Environment & setup

No response

[Markyiptw]

Seems like a bug with sqlite: https://sqlite.org/forum/info/a490a6b058261365

Even if it's impossible to fix, I hope the type can be fixed to | undefined

[Acorn221]

It looks like I'm having a similar issue with postgres. I've had to just do:

  .onConflictDoUpdate({
    target: entity.email,
    set: {
      updatedAt: new Date(),
    }
  }).returning({...});

to just get around the issue for now

[dustinboss]

@jamiehaywood are you actually getting an undefined response from that query?

I had a similar issue, but the query was actually throwing an error because there were no rows to insert (in PostgreSQL). In other words, If I was inserting 100 rows, and all of them were conflicts, the query would fail because there were 0 rows to insert. However, if even one of the queries was not a conflict, then it would not throw an error.

I think that's also how @Acorn221 solved the problem. He converted to "doUpdate" action and created a trivial update, so the database actually had some query to execute.

The error that is thrown doesn't have a specific error type, so it's not easy to check. I had to rely on the text of the error message, which felt very hacky.

Using your example, I did something like this:

try {
      await db.insert(schema.books).values(book).returning().onConflictDoNothing();
    } catch (err) {
      // Catch the known error
      const areAllRowsConflicts =
        err instanceof Error &&
        err.message.includes("values() must be called with at least one value"); // This was part of the full error message
      if (!areAllConflicts) {
        throw err;
      }
 }

[L-Mario564]

This is expected behavior. This happens at the DB level so there's nothing Drizzle can do about it.

As for types, the return type is an array, meaning that the type is technically correct, since it is an array of objects, but you must handle the case when that array is empty.

[ManuLpz4]

Makes sense to maintain the Array return type while always will be empty instead of void? I understand that is something that happens at database level but is something that could push errors to production.

[MatthewAry]

This should be removed from the Roadmap https://orm.drizzle.team/roadmap