[BUG]: onConflictDoUpdate with targetWhere broken when using bun sql

[palmithor]

Report hasn't been filed before.

• [x] I have verified that the bug I'm about to report hasn't been filed before.

What version of drizzle-orm are you using?

0.40.0

What version of drizzle-kit are you using?

0.30.5

Other packages

No response

Describe the Bug

What is the undesired behavior I am currently using npm module "postgres": "^3.4.5", and all tests run. I wanted to try out bun and one test fails. I'm not sure if this should be reported here or bun though.

onConflictDoUpdate does not work with target and targetWhere when using bun sql. It fails both on inserts and on conflicts.

PostgresError: there is no unique or exclusion constraint matching the ON CONFLICT specification
 errno: "42P10",
  code: "ERR_POSTGRES_SERVER_ERROR"

Steps to reproduce it

export const App = pgTable(
  'app',
  {
    id: serial('id').primaryKey(),
    organizationId: integer('organization_id').notNull()
    module: text('module', { enum: ["local", "saas" ] }).notNull(),
    externalId: text('external_id').notNull(),
    createdAt: timestamp('created_at').notNull().defaultNow(),
    updatedAt: timestamp('updated_at').notNull().defaultNow(),
  },
  (t) => [
    uniqueIndex('app_module_external_id_uq_idx')
      .on(t.externalId, t.module)
      .where(inArray(t.module, ['local', 'saas']))
  ],
);

Migration had to be manipulated manually, it's like this now

CREATE UNIQUE INDEX "app_module_external_id_uq_idx" ON "app" USING btree ("external_id","module") WHERE "todo"."module" in ('saas', 'local');

  const [app] = await db
    .insert(App)
    .values({
      organizationId,
      externalId,
      module
    })
    .onConflictDoUpdate({
      target: [App.externalId, App.module],
      targetWhere: sql`module in (${'saas'}, ${'local'}) and organization_id = ${organizationId}`,
      set: {
        updatedAt: new Date(),
      },
    })
    .returning();

db is like this

import { readFileSync } from 'node:fs';
import { type SQL, postgres } from 'bun';
import type { DrizzleConfig } from 'drizzle-orm';
import { type BunSQLDatabase, drizzle } from 'drizzle-orm/bun-sql';

export type ConnectionParams = {
  username: string;
  password: string;
  host: string;
  port: number;
  database: string;
  sslRootCert?: string;
};

let _dbMigrator: BunSQLDatabase<Record<string, never>>;
let _migratorClient: SQL;

const getSSL = ({ sslRootCert }: { sslRootCert?: string }) => {
  return sslRootCert
    ? {
        rejectUnauthorized: false,
        ca: readFileSync(sslRootCert).toString(),
      }
    : undefined;
};

const getConnectionString = ({
  username,
  password,
  host,
  port,
  database,
  sslRootCert,
}: ConnectionParams) => {
  return `postgres://${username}:${password}@${host}:${port}/${database}${sslRootCert ? '?sslmode=verify-full' : ''}`;
};

export const getMigrator = (connectionParams: ConnectionParams) => {
  if (!_dbMigrator) {
    _migratorClient = new postgres(getConnectionString(connectionParams), {
      ssl: getSSL({ sslRootCert: connectionParams.sslRootCert }),
      max: 1,
    });
    _dbMigrator = drizzle(_migratorClient);
  }
  return { migrator: _dbMigrator, pg: _migratorClient };
};

export const createDb = <T extends Record<string, unknown>>(
  connectionParams: ConnectionParams,
  drizzleConfig: DrizzleConfig<T>,
) =>
  drizzle(
    new postgres(getConnectionString(connectionParams), {
      ssl: getSSL({ sslRootCert: connectionParams.sslRootCert }),
    }),
    drizzleConfig,
  );
import { type ConnectionParams, createDb, getMigrator } from '@tempo-io/postgresql';
import { env } from '../libs/env';
import { schema } from './schema';
import type { Db } from './types';

let _db: Db | undefined;

const getConnectionParams = (): ConnectionParams => ({
  username: env.POSTGRES_USERNAME,
  password: env.POSTGRES_PASSWORD,
  host: env.POSTGRES_HOST,
  port: env.POSTGRES_PORT,
  database: env.POSTGRES_DATABASE,
  sslRootCert: env.POSTGRES_SSLROOTCERT,
});

export const dbMigrator = () => getMigrator(getConnectionParams());

export const db = () => {
  if (_db) {
    return _db;
  }
  _db = createDb(getConnectionParams(), { schema });
  return _db;
};

What is the desired result? It doesn't fail and inserts / updates accordingly.þ

Do you think this bug pertains to a specific database driver? Which one? Bun for sure as it works with postgres.

Are you working in a monorepo? Yes, but I find it very unlikely that it affects this.

If you're using a runtime that isn't Node.js: Which one? What version? Have you verified that this isn't an issue with the runtime itself? Yes bun, and obviously I can't use bun postgres driver with node.

[OleksiiKH0240]

Hi @palmithor. I tried running drizzle-kit push and drizzle-kit migrate on the App table you specified, using [email protected], [email protected], and [email protected] (installed via bun install). All of them failed with errors, but they were different from the ones you’re seeing.

On the alternation-engine branch, however, all of the above commands work correctly, and that branch is planned to be merged into main in the near future.

Also, as far as I know, in your case drizzle-kit will use the postgres driver, not bun-sql, to execute queries against the database.