{"record":{"id":"aa30b2cfe353eb7e","repo":"remix-run/remix","slug":"database-does-not-support-upsert","errorCode":null,"errorMessage":"Database does not support upsert","messagePattern":"Database does not support upsert","errorType":"exception","errorClass":"DataTableQueryError","httpStatus":null,"severity":"error","filePath":"packages/data-table/src/lib/database/query-execution.ts","lineNumber":535,"sourceCode":"    affectedRows,\n    insertId: result.insertId,\n    rows: applyAfterReadHooksToRows(table, normalizeRows(result.rows)),\n  }\n}\n\nasync function executeUpsert(\n  database: QueryExecutionContext,\n  table: AnyTable,\n  values: Record<string, unknown>,\n  options?: {\n    returning?: ReturningInput<Record<string, unknown>>\n    touch?: boolean\n    conflictTarget?: string[]\n    update?: Record<string, unknown>\n  },\n): Promise<WriteResult | WriteRowResult<Record<string, unknown>>> {\n  if (!database.capabilities.upsert) {\n    throw new DataTableQueryError('Database does not support upsert')\n  }\n\n  let preparedValues = prepareInsertValues(\n    table,\n    values as never,\n    database.now(),\n    options?.touch ?? true,\n  )\n  let updateChanges = options?.update\n    ? prepareUpdateValues(\n        table,\n        options.update as never,\n        database.now(),\n        options?.touch ?? true,\n        'create',\n      )\n    : undefined\n  let returning = options?.returning","sourceCodeStart":517,"sourceCodeEnd":553,"githubUrl":"https://github.com/remix-run/remix/blob/9696913134be3a4423513d2775f7b31d6917c049/packages/data-table/src/lib/database/query-execution.ts#L517-L553","documentation":"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.","triggerScenarios":"await table.upsert({ values, conflictTarget: ['id'], update: {...} }) against a database whose adapter reports capabilities.upsert = false.","commonSituations":"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.","solutions":["Replace upsert() with an explicit select-then-insert-or-update sequence","Use a database/adapter that supports upsert (Postgres, SQLite 3.24+) with a driver that declares the capability","If writing a custom adapter, implement the upsert execution path and set capabilities.upsert = true"],"exampleFix":"// before\nawait userTable.upsert({ values, conflictTarget: ['id'], update: { email } })\n\n// after\nlet existing = await userTable.row({ where: { id } })\nif (existing) {\n  await userTable.update({ where: { id }, changes: { email } })\n} else {\n  await userTable.create({ values })\n}","handlingStrategy":"type-guard","validationCode":"if (!database.capabilities.upsert) {\n  let existing = await table.row({ where: { id: values.id } })\n  if (existing) await table.update({ where: { id: values.id }, changes: update })\n  else await table.create({ values })\n} else {\n  await table.upsert({ values, conflictTarget, update })\n}","typeGuard":"function supportsUpsert(database: { capabilities: { upsert: boolean } }): boolean {\n  return database.capabilities.upsert === true\n}","tryCatchPattern":"try {\n  await table.upsert({ values, conflictTarget, update })\n} catch (error) {\n  if (error instanceof DataTableQueryError && /does not support upsert/.test(error.message)) {\n    // fall back to select-then-insert-or-update\n  } else throw error\n}","preventionTips":["Check database.capabilities.upsert at startup and disable upsert-based flows accordingly","Keep a manual upsert fallback helper for capability-limited databases","Run the test suite against every supported database dialect"],"tags":["data-table","upsert","database-capabilities"],"backgroundTag":"unsupported-database-feature","analyzedSha":"9696913134be3a4423513d2775f7b31d6917c049","analyzedAt":"2026-08-27T19:55:01.024Z","schemaVersion":2},"datasetVersion":"2026-08-28T00:17:15.603Z"}