© 2026 Hedgehog Software, LLC

TwitterGitHubDiscord
More
CommunitiesDocsAboutTermsPrivacy
Search
Star
Setup for Free
Drizzle TeamDT
Drizzle Team•2y ago•
1 reply
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(),
  ...
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
Drizzle TeamJoin
The official Discord for all Drizzle related projects, such as Drizzle ORM, Drizzle Kit, Drizzle Studio and more!
11,879Members
Resources

Similar Threads

Was this page helpful?
Recent Announcements

Similar Threads

SQLite composite primary key error.
Drizzle TeamDTDrizzle Team / help
3y ago
drizzle push and primary key order
Drizzle TeamDTDrizzle Team / help
3y ago
Enum as primary key for roles (sqlite)
Drizzle TeamDTDrizzle Team / help
2y ago
Drizzle Studio Showing NaN for primary key
Drizzle TeamDTDrizzle Team / help
2y ago