Support for CockroachDB database

Hey folks,

We're currently migrating to CockroachDB and would like to update our Retool apps to handle the new DB. I can't seem to make it work with the existing Postgres resource type - reads work, but on inserts we get error like:

{
  "error": {
    "message": "Cannot read properties of null (reading '1')",
    "name": "TypeError",
    "stack": "TypeError: Cannot read properties of null (reading '1')\n    at Query.<anonymous> (/node_modules/knex/lib/dialects/postgres/index.js:148:69)\n    at Query.handleReadyForQuery (/node_modules/
pg/lib/query.js:138:12)\n    at Client._handleReadyForQuery (/node_modules/pg/lib/client.js:290:19)\n    at Connection.emit (node:events:517:28)\n    at Connection.emit (node:domain:489:12)\n    at /node_
modules/pg/lib/connection.js:114:12\n    at Parser.parse (/node_modules/pg-protocol/dist/parser.js:40:17)\n    at Socket.<anonymous> (/node_modules/pg-protocol/dist/index.js:11:42)\n    at Socket.emit (no
de:events:517:28)\n    at Socket.emit (node:domain:489:12)\n    at addChunk (node:internal/streams/readable:335:12)\n    at readableAddChunk (node:internal/streams/readable:308:9)\n    at Readable.push (n
ode:internal/streams/readable:245:10)\n    at TCP.onStreamRead (node:internal/stream_base_commons:190:23)\n    at TCP.callbackTrampoline (node:internal/async_hooks:130:17)"
  },
  "level": "fatal",
  "message": "There was an uncaught error",
  "origin": "uncaughtException",
  "pid": 50,
  "requestId": "a1e5272fded38b1a73e937c5c522ea03",
  "timestamp": "2024-07-01T07:59:43.117Z"

from the on-prem instance logs. It seems like knex has already added native support for CockroachDB (CockroachDB Support · Issue #2002 · knex/knex · GitHub), so it shouldn't be a hard one to add I hope?

If anybody made the inserts/upserts work in existing setup, I'd love to hear how to do it!

Thanks,
Wojtek

Managed to get this working with help from Cockroach folks. The workaround is to use raw SQL mode (not GUI mode). To enable it, make sure you have the option to "Show write GUI mode only" disabled for all the environements.

Rewriting the query to INSERT INTO ... ON CONFLICT works!

1 Like