[BUG]: SQLite onConflictDoUpdate does not work as expected on composite primary key or unique constraint

ridhwaans commented 1 week ago

What version of `drizzle-orm` are you using?

^0.30.10

What version of `drizzle-kit` are you using?

^0.21.4

Describe the Bug

"@libsql/client": "0.5.3",

The following is the related table and join table The primary key is a composite key. genreId is not null and the rest are nullable.

export const genresJoinTable = sqliteTable(
  'genres_join',
  {
    movieId: integer('movie_id').references(() => moviesTable.id),
    tvShowId: integer('tv_show_id').references(() => tvShowsTable.id),
    artistId: integer('artist_id').references(() => artistsTable.id),
    albumId: integer('album_id').references(() => albumsTable.id),
    genreId: integer('genre_id')
      .notNull()
      .references(() => genresTable.id),
  },
  (t) => ({
    pk: primaryKey({
      columns: [t.movieId, t.tvShowId, t.artistId, t.albumId, t.genreId],
    }),
  }),
);

export const genresJoinTableRelations = relations(
  genresJoinTable,
  ({ one }) => ({
    movie: one(moviesTable, {
      fields: [genresJoinTable.movieId],
      references: [moviesTable.id],
    }),
    tvShow: one(tvShowsTable, {
      fields: [genresJoinTable.tvShowId],
      references: [tvShowsTable.id],
    }),
    artist: one(artistsTable, {
      fields: [genresJoinTable.artistId],
      references: [artistsTable.id],
    }),
    album: one(albumsTable, {
      fields: [genresJoinTable.albumId],
      references: [albumsTable.id],
    }),
    genre: one(genresTable, {
      fields: [genresJoinTable.genreId],
      references: [genresTable.id],
    }),
  }),
);

This is the write operation and drizzle ORM method used

const insertedGenres = await tx
            .insert(genresTable)
            .values(genres)
            .onConflictDoUpdate({
              target: genresTable.name,
              set: { name: sql`excluded.name` },
            })
            .returning({ id: genresTable.id });

   insertedGenres.map(
            async (genre) =>
              await tx
                .insert(genresJoinTable)
                .values({ genreId: genre.id, tvShowId: insertedTvShow[0].id })
                .onConflictDoUpdate({
                  target: [genresJoinTable.genreId, genresJoinTable.tvShowId],
                  set: {
                    genreId: sql`excluded.genre_id`,
                    tvShowId: sql`excluded.tv_show_id`,
                  },
                }),
          );

I have a scenario where I sometimes have the same write value of genre and tvShow However, the onConflictDoUpdate does not work and I end up with multiple records of different ids but same genreId and tvShowId

.onConflictDoUpdate({
                  target: [genresJoinTable.genreId, genresJoinTable.tvShowId],

Is my target wrong? Do I need to continue with it, or declare a list with four fields because the primary composite key has four fields, or use unique alternatively?

unq: unique().on(t.movieId, t.tvShowId, t.artistId, t.albumId, t.genreId),

It does not seem to work currently

References:
https://orm.drizzle.team/docs/indexes-constraints#composite-primary-key https://orm.drizzle.team/docs/insert#on-conflict-do-update https://orm.drizzle.team/docs/indexes-constraints#unique

Expected behavior

When given a list of records to insert to genresJoinTable that have the same values for genreId and tvShowId, I expect a single row with a primary key to be created and updated repeatedly

Environment & setup

Mac Sonoma "@libsql/client": "0.5.3" "next": "14.2.3" bun 1.1.27

buzinas commented 6 days ago

This is not how onConflictDoUpdate works. In your case, genreId and tvShowId need to be a primary key or a unique key in your table, but right now they aren't (they're only part of a larger set of primary keys).

You need to either create a unique composite key for only genreId and tvShowId (if this is not a problem for your schema), or you'll need to manually build two queries.

ridhwaans commented 6 days ago

thanks @buzinas a primary key with only genreId and tvShowId is not an option for this table What do you mean by manually build two queries?
Is it possible to have a target on unique sets of columns?
I tried this:

// schema 
export const genresJoinTable = sqliteTable(
  'genres_join',
  {
    movieId: integer('movie_id').references(() => moviesTable.id),
    tvShowId: integer('tv_show_id').references(() => tvShowsTable.id),
    artistId: integer('artist_id').references(() => artistsTable.id),
    albumId: integer('album_id').references(() => albumsTable.id),
    genreId: integer('genre_id')
      .notNull()
      .references(() => genresTable.id),
  },
  (t) => ({
    pk: primaryKey({
      columns: [t.movieId, t.tvShowId, t.artistId, t.albumId, t.genreId],
    }),
    unq: unique().on(t.genreId, t.tvShowId), // introduced a new unique constraint
}),
);

  // upsert
    insertedGenres.map(
            async (genre) =>
              await tx
                .insert(genresJoinTable)
                .values({ genreId: genre.id, tvShowId: insertedTvShow[0].id })
                .onConflictDoUpdate({
                  target: genresJoinTable.genreId,
                  set: { genreId: sql`excluded.genre_id` },
                }),
          );

it would not work

LibsqlError: SQLITE_CONSTRAINT_UNIQUE: UNIQUE constraint failed: genres_join.genre_id, genres_join.tv_show_id
 cause: {
  "stack": "Error: UNIQUE constraint failed: genres_join.genre_id, genres_join.tv_show_id\n    at zi 
  ...
  "code": "SQLITE_CONSTRAINT_UNIQUE",
  "rawCode": 2067
}

buzinas commented 5 days ago

Your target must be [genresJoinTable.genreId, genresJoinTable.tvShowId].

Also, it looks like by your original query you're just updating the columns to their current values, so you could just use onConflictDoNothing instead?

ridhwaans commented 5 days ago

I tried the suggested target and onConflictDoNothing, however it ends with an error

LibsqlError: SQLITE_CONSTRAINT_UNIQUE: UNIQUE constraint failed: genres_join.genre_id, genres_join.tv_show_id
 cause: {
  "stack": "Error: UNIQUE constraint failed: genres_join.genre_id, genres_join.tv_show_id\n    at zi 
  ...
  "code": "SQLITE_CONSTRAINT_UNIQUE",
  "rawCode": 2067
}

I tried after removing the unique constraint from the table definition, however it seems to create new rows rather than cancel the insert

For example, I need genre ids to be unique (14, 18, 19) for tv show id 246 above, but there are duplicates I am probably doing something wrong. Thanks @buzinas

drizzle-team / drizzle-orm