remix-run/remix · error · DataTableQueryError

Database does not support upsert

Error message

Database does not support upsert

What it means

upsert() is only implemented for databases whose dialect supports native upsert semantics (e.g. Postgres ON CONFLICT, SQLite ON CONFLICT, MySQL ON DUPLICATE KEY). The adapter's capabilities.upsert flag is false (notably MySQL in some configurations or unsupported drivers), so a DataTableQueryError is thrown before any SQL runs.

Source

Thrown at packages/data-table/src/lib/database/query-execution.ts:535

    affectedRows,
    insertId: result.insertId,
    rows: applyAfterReadHooksToRows(table, normalizeRows(result.rows)),
  }
}

async function executeUpsert(
  database: QueryExecutionContext,
  table: AnyTable,
  values: Record<string, unknown>,
  options?: {
    returning?: ReturningInput<Record<string, unknown>>
    touch?: boolean
    conflictTarget?: string[]
    update?: Record<string, unknown>
  },
): Promise<WriteResult | WriteRowResult<Record<string, unknown>>> {
  if (!database.capabilities.upsert) {
    throw new DataTableQueryError('Database does not support upsert')
  }

  let preparedValues = prepareInsertValues(
    table,
    values as never,
    database.now(),
    options?.touch ?? true,
  )
  let updateChanges = options?.update
    ? prepareUpdateValues(
        table,
        options.update as never,
        database.now(),
        options?.touch ?? true,
        'create',
      )
    : undefined
  let returning = options?.returning

View on GitHub (pinned to 9696913134)

Solutions

1. Replace upsert() with an explicit select-then-insert-or-update sequence
2. Use a database/adapter that supports upsert (Postgres, SQLite 3.24+) with a driver that declares the capability
3. If writing a custom adapter, implement the upsert execution path and set capabilities.upsert = true

Example fix

// before
await userTable.upsert({ values, conflictTarget: ['id'], update: { email } })

// after
let existing = await userTable.row({ where: { id } })
if (existing) {
  await userTable.update({ where: { id }, changes: { email } })
} else {
  await userTable.create({ values })
}

if (!database.capabilities.upsert) {
  let existing = await table.row({ where: { id: values.id } })
  if (existing) await table.update({ where: { id: values.id }, changes: update })
  else await table.create({ values })
} else {
  await table.upsert({ values, conflictTarget, update })
}

function supportsUpsert(database: { capabilities: { upsert: boolean } }): boolean {
  return database.capabilities.upsert === true
}

try {
  await table.upsert({ values, conflictTarget, update })
} catch (error) {
  if (error instanceof DataTableQueryError && /does not support upsert/.test(error.message)) {
    // fall back to select-then-insert-or-update
  } else throw error
}

When it happens

Trigger: await table.upsert({ values, conflictTarget: ['id'], update: {...} }) against a database whose adapter reports capabilities.upsert = false.

Common situations: Developing locally against Postgres/SQLite and deploying to a database/driver without upsert support; using a custom or community adapter that hasn't declared the upsert capability; version changes that narrowed capability detection.

Related errors

• upsert requires at least one value
• create({ returnRow: true }) requires primary key values for
• ' + operation + '() returning is not supported by this datab
• Unknown transaction token: + token.id
• MySQL migration lock is already held by this database

AI-assisted analysis of remix-run/remix@9696913134 (2026-08-27). Data as JSON: /api/errors/aa30b2cfe353eb7e. Report an issue: GitHub.