github-starred/better-auth
2
0
Fork 0
mirror of https://github.com/better-auth/better-auth.git synced 2026-05-23 23:52:05 -05:00
Code Issues 2.5k Packages Projects Releases 112 Wiki Activity

Composite unique constraint #2845

New Issue
Closed
opened 2026-03-13 10:23:14 -05:00 by GiteaMirror · 7 comments
GiteaMirror commented 2026-03-13 10:23:14 -05:00
Owner
Copy Link

Originally created by @rbalet on GitHub (Feb 8, 2026).

Is this suited for github?

  • Yes, this is suited for github

Is your feature request related to a problem? Please describe.

Let say, I have the following code

My Plugin

export const fooBarPlugin = () => schema: {
fooBar: {
  fields: {
    fooId: {
      type: "string",
      required: true,
      references: {
        model: "foo",
        field: "id",
        onDelete: "cascade",
      },
    },
    barId: {
      type: "string",
      required: true,
      references: {
        model: "bar",
        field: "id",
        onDelete: "cascade",
      },
    },
    isActive: {
      type: "boolean",
      required: true,
    },
    isSpecial: {
      type: "boolean",
      required: false,
    },
  },
  // Composite unique constraint for upsert support
  unique: [["fooId", "barId"]],
},
}

And I want to get to insert a relation, but update the info if the later already exists

const [result] = await db
  .insert(schema.fooBar)
  .values({
    id: uuidv7(),
    fooId: fooId,
    barId: barId,
    isActive: true,
    isSpecial: true,
  })
  .onConflictDoUpdate({
    target: [schema.fooBar.fooId, schema.fooBar.barId], // composite unique constraint
    set: {
      isActive: true,
      isSpecial: true,
    },
  })
  .returning();

I do get the following error

 cause: error: there is no unique or exclusion constraint matching the ON CONFLICT specification

I don't understand, as fooId and barId are both unique

Anyone know how to fix it, or if this is even possible ?

Describe the solution you'd like

 .onConflictDoUpdate({
    target: [schema.fooBar.fooId, schema.fooBar.barId], // <-- composite unique constraint
    set: {
      isActive: true,
      isSpecial: true,
    },
  })

Describe alternatives you've considered

Workaround: Manually add in the drizzle migration folder

CREATE UNIQUE INDEX foo_bar_unique_idx ON fooBar (fooId, barId);

Additional context

Already exchanged with Dosu here https://github.com/better-auth/better-auth/discussions/7861.

Seems related to https://github.com/better-auth/better-auth/issues/1248, but I'm note sure

Originally created by @rbalet on GitHub (Feb 8, 2026). ### Is this suited for github? - [x] Yes, this is suited for github ### Is your feature request related to a problem? Please describe. Let say, I have the following code My Plugin ```typescript export const fooBarPlugin = () => schema: { fooBar: { fields: { fooId: { type: "string", required: true, references: { model: "foo", field: "id", onDelete: "cascade", }, }, barId: { type: "string", required: true, references: { model: "bar", field: "id", onDelete: "cascade", }, }, isActive: { type: "boolean", required: true, }, isSpecial: { type: "boolean", required: false, }, }, // Composite unique constraint for upsert support unique: [["fooId", "barId"]], }, } ``` And I want to get to insert a relation, but update the info if the later already exists ```typescript const [result] = await db .insert(schema.fooBar) .values({ id: uuidv7(), fooId: fooId, barId: barId, isActive: true, isSpecial: true, }) .onConflictDoUpdate({ target: [schema.fooBar.fooId, schema.fooBar.barId], // composite unique constraint set: { isActive: true, isSpecial: true, }, }) .returning(); ``` I do get the following error ```bash cause: error: there is no unique or exclusion constraint matching the ON CONFLICT specification ``` I don't understand, as fooId and barId are both unique Anyone know how to fix it, or if this is even possible ? ### Describe the solution you'd like ```typescript .onConflictDoUpdate({ target: [schema.fooBar.fooId, schema.fooBar.barId], // <-- composite unique constraint set: { isActive: true, isSpecial: true, }, }) ``` ### Describe alternatives you've considered Workaround: Manually add in the drizzle migration folder ```sql CREATE UNIQUE INDEX foo_bar_unique_idx ON fooBar (fooId, barId); ``` ### Additional context Already exchanged with Dosu here https://github.com/better-auth/better-auth/discussions/7861. Seems related to https://github.com/better-auth/better-auth/issues/1248, but I'm note sure
GiteaMirror added the enhancement label 2026-03-13 10:23:14 -05:00
GiteaMirror commented 2026-03-13 10:23:15 -05:00
Author
Owner
Copy Link

@better-auth-agent[bot] commented on GitHub (Feb 8, 2026):

The error you're encountering (there is no unique or exclusion constraint matching the ON CONFLICT specification) typically arises when the database cannot recognize a composite unique constraint on the specified fields. In your schema for fooBar, you defined a composite unique constraint on the fields fooId and barId, which should allow you to use them in the onConflictDoUpdate clause.

To resolve this issue, you should ensure that the composite unique index is actually created in your database schema. You can manually add the index through a migration, just like you suggested in your workarounds:

CREATE UNIQUE INDEX foo_bar_unique_idx ON fooBar (fooId, barId);

After executing this migration, try running your insert operation again. If it still doesn’t work, consider checking if there are any existing duplicate combinations of fooId and barId in your fooBar table, as this could also trigger the conflict.

If issues persist, or if you would like more specific guidance, feel free to tag @bekacru or @ping__ for help from those who are more familiar with Better Auth specifics!

Have you already tried checking for existing duplicate entries, or do you need assistance with migrations?

If you need more help, tag @better-auth-agent in a comment so I can respond.

Diagram • Discord • GitHub

Diagram Join Star

@better-auth-agent[bot] commented on GitHub (Feb 8, 2026): The error you're encountering (`there is no unique or exclusion constraint matching the ON CONFLICT specification`) typically arises when the database cannot recognize a composite unique constraint on the specified fields. In your schema for `fooBar`, you defined a composite unique constraint on the fields `fooId` and `barId`, which should allow you to use them in the `onConflictDoUpdate` clause. To resolve this issue, you should ensure that the composite unique index is actually created in your database schema. You can manually add the index through a migration, just like you suggested in your workarounds: ```sql CREATE UNIQUE INDEX foo_bar_unique_idx ON fooBar (fooId, barId); ``` After executing this migration, try running your insert operation again. If it still doesn’t work, consider checking if there are any existing duplicate combinations of `fooId` and `barId` in your `fooBar` table, as this could also trigger the conflict. If issues persist, or if you would like more specific guidance, feel free to tag @bekacru or @ping__ for help from those who are more familiar with Better Auth specifics! Have you already tried checking for existing duplicate entries, or do you need assistance with migrations? _If you need more help, tag @better-auth-agent in a comment so I can respond._ <!-- bot:webhook reply v1 --> [Diagram](https://repodiagrams.s3.eu-north-1.amazonaws.com/better-auth_ultra_detailed_interactive.html) • [Discord](https://discord.gg/better-auth) • [GitHub](https://github.com/better-auth/better-auth) [![Diagram](https://img.shields.io/badge/Diagram-2b3137?style=flat-square)](https://repodiagrams.s3.eu-north-1.amazonaws.com/better-auth_ultra_detailed_interactive.html) [![Join](https://img.shields.io/badge/join-5865F2?logo=discord&logoColor=white&style=flat-square)](https://discord.gg/better-auth) [![Star](https://img.shields.io/badge/star-181717?logo=github&logoColor=white&style=flat-square)](https://github.com/better-auth/better-auth)
GiteaMirror commented 2026-03-13 10:23:15 -05:00
Author
Owner
Copy Link

@rbalet commented on GitHub (Feb 8, 2026):

@Bekacru This seems like a legit issue to me, but I'll let you know if it needs to be closed

@rbalet commented on GitHub (Feb 8, 2026): @Bekacru This seems like a legit issue to me, but I'll let you know if it needs to be closed
GiteaMirror commented 2026-03-13 10:23:16 -05:00
Author
Owner
Copy Link

@ping-maxwell commented on GitHub (Feb 11, 2026):

The main problem is that not all database support this, our adapter system is meant to be as general as possible, with few exceptions that are non-major that are allowed to be optionally supported. If a given DB or adapter doesn't support composite unique constraints (Eg Convex adapter couldn't do this ) then everything can easily fall apart

@ping-maxwell commented on GitHub (Feb 11, 2026): The main problem is that not all database support this, our adapter system is meant to be as general as possible, with few exceptions that are non-major that are allowed to be optionally supported. If a given DB or adapter doesn't support composite unique constraints (Eg Convex adapter couldn't do this ) then everything can easily fall apart
GiteaMirror commented 2026-03-13 10:23:16 -05:00
Author
Owner
Copy Link

@rbalet commented on GitHub (Feb 11, 2026):

I understand :/

For the one who may need it then, this is the work around

  1. Into your /drizzle folder, add a new file, after the better-auth initial .sql file, I named it 0001_init_composite.sql
  2. Inside this file, adds the contraint
--- Remove individual unique constraints if they exist
ALTER TABLE foo_bar DROP CONTRAINT IF EXISTS foo_bar_foo_id_unique;
ALTER TABLE foo_bar DROP CONTRAINT IF EXISTS foo_bar_foo_id_unique;

--- Add composite unique indexes
CREATE UNIQUE INDEX IF NOT EXISTS foo_bar_unique_idx ON foo_bar (foo_id, bar_id);
@rbalet commented on GitHub (Feb 11, 2026): I understand :/ For the one who may need it then, this is the work around 1. Into your `/drizzle` folder, add a new file, *after* the better-auth initial `.sql` file, I named it `0001_init_composite.sql` 2. Inside this file, adds the contraint ```sql --- Remove individual unique constraints if they exist ALTER TABLE foo_bar DROP CONTRAINT IF EXISTS foo_bar_foo_id_unique; ALTER TABLE foo_bar DROP CONTRAINT IF EXISTS foo_bar_foo_id_unique; --- Add composite unique indexes CREATE UNIQUE INDEX IF NOT EXISTS foo_bar_unique_idx ON foo_bar (foo_id, bar_id); ```
GiteaMirror commented 2026-03-13 10:23:16 -05:00
Author
Owner
Copy Link

@rbalet commented on GitHub (Feb 11, 2026):

@ping-maxwell I have to wonder though, if I'm adding two unique fields inside my plugin, does other adapter, like Convex, handle it?

I'm not an expert on DB, so this is a genuine question,
but doesn't this always mean that the user is attempting to have a composite unique constraint ?

fields: {
  fooId: {
    type: "string",
    required: true,
    unique: true, // <-- here
    references: {
      model: "foo",
      field: "id",
      onDelete: "cascade",
    },
  },
  barId: {
    type: "string",
    required: true,
    unique: true, // <-- here
    references: {
      model: "bar",
      field: "id",
      onDelete: "cascade",
    },
  }
}

Thanks

@rbalet commented on GitHub (Feb 11, 2026): @ping-maxwell I have to wonder though, if I'm adding two unique fields inside my plugin, does other adapter, like Convex, handle it? I'm not an expert on DB, so this is a genuine question, but doesn't this always mean that the user is attempting to have a composite unique constraint ? ```typescript fields: { fooId: { type: "string", required: true, unique: true, // <-- here references: { model: "foo", field: "id", onDelete: "cascade", }, }, barId: { type: "string", required: true, unique: true, // <-- here references: { model: "bar", field: "id", onDelete: "cascade", }, } } ``` Thanks
GiteaMirror commented 2026-03-13 10:23:17 -05:00
Author
Owner
Copy Link

@ping-maxwell commented on GitHub (Feb 11, 2026):

No, unique constraints per field works differently to composite unique constraints.

The primary difference lies in the scope of the uniqueness check. A standard unique constraint applies to a single column, ensuring every entry in that column is distinct. A composite unique constraint applies to a combination of two or more columns, ensuring that the total pair or group of values is unique, even if individual columns within that group contain duplicates

@ping-maxwell commented on GitHub (Feb 11, 2026): No, unique constraints per field works differently to composite unique constraints. The primary difference lies in the scope of the uniqueness check. A standard unique constraint applies to a single column, ensuring every entry in that column is distinct. A composite unique constraint applies to a combination of two or more columns, ensuring that the total pair or group of values is unique, even if individual columns within that group contain duplicates
GiteaMirror commented 2026-03-13 10:23:17 -05:00
Author
Owner
Copy Link

@rbalet commented on GitHub (Feb 11, 2026):

Thanks for the clarification

@rbalet commented on GitHub (Feb 11, 2026): Thanks for the clarification
Sign in to join this conversation.
No Branch/Tag Specified
Branches Tags
Labels
Clear labels
adapter
astro
awaiting external contributor
blocked
breaking
breaking change
bug
c-devops
core
credentials
database
dependencies
devops
devtools
docs
documentation
duplicate
elysia
enhancement
enterprise
expo
express
fastify
good first issue
help wanted
hono
identity
infra
integration
invalid
javascript
locked
maintenance
need-more-information
needs: info
needs: repro
nextjs
nuxt
oauth
organization
P0
payments
perf
platform
plugin
pull-request
Mirrored from GitHub Pull Request
 question
ready
regression
remix
security
social-provider
solid
stale
svelte
tanstack-start
tracking
version-bump
vue
wontfix
No Label enhancement
Milestone
No items
No Milestone
Projects
Clear projects
No project
Assignees
Clear assignees
GiteaMirror ninjasurge
No Assignees
1 Participants
Notifications
Due Date
No due date set.
Dependencies

No dependencies set.

Reference: github-starred/better-auth#2845
Delete Branch "%!s()"

Deleting a branch is permanent. Although the deleted branch may continue to exist for a short time before it actually gets removed, it CANNOT be undone in most cases. Continue?