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

1 Reply

chicoOP•7mo ago

resolved. I needed to define it as id: integer("id").primaryKey(), and didn't need the rest of the constraint

Gaming

Programming

Drizzle SQLite not implicitly handling autoincrement primary key id