Drizzle Team•2d ago

Can't upsert with onConflictDoUpdate & generatedAlwaysAsIdentity ID columns?

I've noticed the type system doesn't recognize the ID columns (since they are supposed to be autogenerated?) How would one upsert or update? The ID field is not available

export const folders = pgTable('folders', {
    id: integer().primaryKey().generatedAlwaysAsIdentity({ startWith: 1000 }),
    name: text('name').notNull(),
    createdBy: integer('created_by')
        .notNull()
        .references(() => users.id),
    parentFolderId: integer('parent_folder_id').references((): AnyPgColumn => folders.id),
})

const db = createDb()
// no id is available to pass to values per TS
await db.insert(folders).values({ name: 'Test', createdBy: 1000 }).onConflictDoUpdate({
    // where is ID passed in?
    target: folders.id, // no type error
    set: { name: 'Test2' },
})

export const folders = pgTable('folders', {
    id: integer().primaryKey().generatedAlwaysAsIdentity({ startWith: 1000 }),
    name: text('name').notNull(),
    createdBy: integer('created_by')
        .notNull()
        .references(() => users.id),
    parentFolderId: integer('parent_folder_id').references((): AnyPgColumn => folders.id),
})

const db = createDb()
// no id is available to pass to values per TS
await db.insert(folders).values({ name: 'Test', createdBy: 1000 }).onConflictDoUpdate({
    // where is ID passed in?
    target: folders.id, // no type error
    set: { name: 'Test2' },
})

1 Reply

Mario564•2d ago

@DiamondDragon You can use the new .overrideSystemValues method before .values to insert and update generated identity fields

Gaming

Programming

Can't upsert with onConflictDoUpdate & generatedAlwaysAsIdentity ID columns?