sql: compatibility between Datagrip and CockroachDB

[younisshah]

This is a noob question.

How do I connect CockroachDB to JetBrains DataGrip? I know the API is Postgres compatible which means any client that can connect to Postgres, can connect to CockRoachDB. I tried. I failed.

I created a user and DB using Cockroach SQL CLI. Then I tried to connect to it using DataGrip but I get this error

Error encountered when performing Introspect schema demo_db: ERROR: column name "xmin" not found. ERROR: column name "xmin" not found

Appreciated!

[jordanlewis]

Hi @younisshah,

CockroachDB is not supported by DataGrip at this time. Its Postgres adapter uses several Postgres features we don't support, such as xmin.

We would very much like to support DataGrip, but we'll never support a feature like xmin which is tied to Postgres's transaction implementation details. In my own testing it does appear that xmin is used to sort by most-recently-modified for schema elements, which seems like something we could fake by artificially returning 0, but I'm not sure what else that might break.

As DataGrip is not open source I can't comment on how difficult it might be to write a CockroachDB specific adapter.

[jordanlewis]

I tested always returning 0 for xmin, which seems to superficially work. After adding several more missing virtual tables (#21065), merging in CTEs (#20374), and a sequences function (#21069), I discovered that DataGrip also emits a correlated subquery which we still don't support (#3288).

[bw-smarkwell]

Requested explicit support for CockroachDB at https://youtrack.jetbrains.com/issue/DBE-5835 from the DataGrip team.

[zvozin]

:bump: Here's what the Datagrip team states they need to support a new database: https://www.jetbrains.com/datagrip/new_dbms.

[bw-smarkwell]

Ok this is a bit weird to me, as a customer of DataGrip, they seem to put a lot of onus on the DB developer to do a significant chunk of integration work.

[kassak]

Issue with xmins 've been fixed in DataGrip. Please report exact problems, because we fix connectivity/database detection problems for unsupported databases immediately, and "explicit support" is a feature request, which goes under low priority (that's why it took us so long to notice).
Feel free to open new issues if there are more problems

[jordanlewis]

Thanks @kassak. DataGrip seems to be working pretty well right now, so I'm closing this. @younisshah please let us know if you have more issues and we'll do our best to resolve. Thanks!