Drizzle TeamDT
Drizzle Team•2y ago
chico

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(),
  ...


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;
      }
    }
  };


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)


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
Was this page helpful?