chico Posts - Answer Overflow

chico

Explore posts from servers

TtRPC DTDrizzle Team Mmfad

Posts Comments

DTDrizzle Team

•Created by chico on 9/21/2024 in #help

SQLite onConflictDoUpdate does not work as expected on composite primary key or unique constraint

https://github.com/drizzle-team/drizzle-orm/issues/2998 thanks for feedback

1 replies

DTDrizzle Team

•Created by chico on 7/31/2024 in #help

findMany not including right table details in query

the question details exceeded the post word limit github discussion link
https://github.com/drizzle-team/drizzle-orm/discussions/2702

4 replies

DTDrizzle Team

•Created by chico on 5/29/2024 in #help

returning() doesnt work with onConflictDoNothing

Asking for drizzle sqlite. in an insert many scenario, returns an empty array on conflict if that is intended, what is the best way to get return previously inserted ids & information, with/without conflict?

1 replies

DTDrizzle Team

•Created by chico on 5/29/2024 in #help

Drizzle SQLite not implicitly handling autoincrement primary key id

schema

import { sqliteTable, integer, text, real, blob } from 'drizzle-orm/sqlite-core';
import { relations, sql } from 'drizzle-orm';

var itemsTable = sqliteTable('items', {
  id: integer('id', { mode: 'number' }).default(1).primaryKey({ autoIncrement: true }),
  anotherId: integer('another_id').notNull().unique(),
  ...

import { sqliteTable, integer, text, real, blob } from 'drizzle-orm/sqlite-core';
import { relations, sql } from 'drizzle-orm';

var itemsTable = sqliteTable('items', {
  id: integer('id', { mode: 'number' }).default(1).primaryKey({ autoIncrement: true }),
  anotherId: integer('another_id').notNull().unique(),
  ...

code

for (let item of items) {
      try {
        let item = await getItemData(item);
        // item is a javascript object without id field
        if (item.error) {
               break;
        }
        
        await db
        .insert(itemsTable)
        .values(item)
        .onConflictDoUpdate({
          target: itemsTable.anotherId, 
          set: item,

        });
      } catch (e) {
        console.log('There was a problem upserting this item', e);
        continue;
      }
    }
  };

for (let item of items) {
      try {
        let item = await getItemData(item);
        // item is a javascript object without id field
        if (item.error) {
               break;
        }
        
        await db
        .insert(itemsTable)
        .values(item)
        .onConflictDoUpdate({
          target: itemsTable.anotherId, 
          set: item,

        });
      } catch (e) {
        console.log('There was a problem upserting this item', e);
        continue;
      }
    }
  };

hi in the above code, I am getting an error

There was a problem upserting this item xe [LibsqlError]: SQLITE_CONSTRAINT_PRIMARYKEY: UNIQUE constraint failed: item.id
    at q0 (/app/dist/index.cjs:11:304131)

There was a problem upserting this item xe [LibsqlError]: SQLITE_CONSTRAINT_PRIMARYKEY: UNIQUE constraint failed: item.id
    at q0 (/app/dist/index.cjs:11:304131)

I am iterating a collection of items that I am trying to upsert to the drizzle table I am letting Drizzle automatically assign an autoincrementing integer as the primary key, starting at 1. That way I dont use the returning() accessor to get the resulting inserted id however, it is not working like i expect since it is showing the SQLITE_CONSTRAINT_PRIMARYKEY: UNIQUE constraint failed error Feedback appreciated thanks

2 replies

Gaming

Programming