Cockroach: sql: support for IntelliJ IDEA

[cristhiank]

FEATURE REQUEST

We are exploring CockroachDB as an alternative to migrate our SaaS product from MySQL, we use IntelliJ IDEA as our IDE and when we connect to the DB with the normal PostgreSQL driver we are getting the following message:
ERROR: column name "xmin" not found.

We can execute normal SQL in the DB but we lost all the designers functionality.

1. Please describe the feature you are requesting, as well as your proposed use case for this feature.

It would be nice to be able to explore and manipulate CockroachDB with IntelliJ IDEA using the editor designers and tools.

1. Indicate the importance of this issue to you (blocker, must-have, should-have, nice-to-have). Are you currently using any workarounds to address this issue?

nice-to-have

Bonus:
FlywayDB is going to support CockroachDB in the next release this month, we are using this too.
https://github.com/flyway/flyway/issues/1760

[a-robinson]

Hi @cristhiank, thanks for the information on what you're using and what you're looking for!

Unfortunately, IntelliJ IDEA's postgres support relies on some pretty hairy internals of postgres (like xmin) that we haven't yet tried to emulate. This exact topic was actually discussed earlier this year: https://github.com/cockroachdb/cockroach/issues/13998#issuecomment-285407565

Our goal is to support as many of these tools as we can, but in some cases it's easier to create custom support for cockroach (as in the flyway issue you linked to) than to support all of the exposed internals of postgres. I'll leave this open as a feature request, but unfortunately IntelliJ IDEA isn't at the top of our roadmap right now.

cc @dianasaur323 for awareness

[cristhiank]

Thanks @a-robinson I understand, we can work with the current level of support for IntelliJ IDEA.
Actually I found a workaround for at least be able to see and do some checks on the db schema by enabling the "Instrospect using JDBC metadata"

IMO the priority for Java environments would be building an own JDBC driver. I've never done that, but will check it to see if I can help and contribute.

[a-robinson]

What would you want to be custom about the JDBC driver? As far as I'm aware we work with the JDBC driver for postgres, as recommended in our docs. Have you had any particular issues with it?

[cristhiank]

Sorry, mybad, you are right. The postgres driver works fine. Is a tooling problem, nothing to do with the driver. I was thinking on Hibernate Dialects and that sort of things.

[serkangunes]

For me most of the time it works however I keep getting this error.
[42P01] org.postgresql.util.PSQLException: ERROR: relation "pg_catalog.pg_shdescription" does not exist. ERROR: relation "pg_catalog.pg_shdescription" does not exist

[jordanlewis]

These limitations have been lifted for 19.2, and IntelliJ IDEA's db explorer now works well with CockroachDB using its PostgreSQL driver.

[kenliu]

We are getting close to releasing 19.2 (in beta now) so we would appreciate any feedback on compatibility issues with IDEA before GA in October.

[awoods187]

@jordanlewis should we close this issue then?

[jordanlewis]

Yes, I think this issue has outlived its usefulness.