DT
Drizzle Team•2mo ago
DiamondDragon

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
Mario564•2mo ago
@DiamondDragon You can use the new .overrideSystemValues method before .values to insert and update generated identity fields

Did you find this page helpful?