ui tools: support open source database UI tools

[ansarizafar]

CockroachDB is not 100% compatible with postgresql that is why available postgresql admin ui tools don't work properly. Admin UI should support creation/modification of database/table/query/records with version 1.0. These futures are inline with CockroachDB's goal of making DBA/Developers life easy.

[tamird]

@ansarizafar this issue is extremely vague. Can you point out a specific incompatibility that you've encountered? Apologies, I misread.

[ansarizafar]

I have tried DBeaver, vstudio and pgAdmin on Ubuntu, Not all features are supported and frequent crashes. Most modern databases like Mongodb, Rethindb, Arangodb, TiDB, FaunaDB and others provide support for these features with their Admin UI. Cockroachdb is a revolutionary product, It would be great If Cockroachdb also provide these features.

[tamird]

You might try postico, which I believe supports CRDB. See https://github.com/jakob/Postico/issues/224.

On Thu, Apr 27, 2017 at 10:08 PM, Zafar Ansari notifications@github.com wrote:

I have tried DBeaver, vstudio and phpadmin on Ubuntu, Not all features are supported and frequent crashes. Most modern databases like Mongodb, Rethindb, Arangodb, TiDB and others provide support for these features with their Admin UI. Cockroachdb is a revolutionary product, It would be great If Cockroachdb also provide these features.

— You are receiving this because you commented. Reply to this email directly, view it on GitHub https://github.com/cockroachdb/cockroach/issues/15441#issuecomment-297888017, or mute the thread https://github.com/notifications/unsubscribe-auth/ABdsPAKisZQu3zlS4K0Gui7AEnvL1it9ks5r0UoPgaJpZM4NK9vW .

[ansarizafar]

Postico is only for Mac and not available for Windows/Ubuntu. If basic support for these features is provide in admin ui then we don't need an extra tool. Admin ui of Rethinkdb/Arangodb is a good example.

[dianasaur323]

@ansarizafar Thanks for bringing this up! It's on our roadmap, but given that our 1.0 is going out soon, it won't make it into the 1.0. I'm adding a community-request flag so that we can continue tracking this as a feature request.

[tamird]

@ansarizafar FYI https://github.com/sosedoff/pgweb/issues/235.

[ansarizafar]

@tamird I had tried PGWeb with CRDB few days back, It was not working. I am really excited to see you working on this project. It would be great, if we can also make PGWeb available as a part CRDB admin UI.

[bladefist]

Would love to see this bumped in priority. Building and maintaining a large db via CLI is tough.

[dianasaur323]

@bladefist thanks for the additional vote for this feature. I'm thinking through how we could support this feature, but there are several dependencies that need to go into the UI first, including login / authorization, before we add in these administrative features. That being said, perhaps we could test out some existing open source tools to see what the gaps are. If you stumble on any bugs, please help us by filing them so that we can fill in those gaps.

[jake2009]

I expect to see CockroachDB will compatible to a powerful ui tool . I hope it is easy to learn to use CockroachDB . If CockroachDB have a powerful ui tool , then it will be easy to learn ,to use . Many people create a table by ui not by command . Command is hard to remember，hard to use.

[bladefist]

@dianasaur323 I don't think this should be apart of your UI build. The tools are pgAdmin and DBeaver. Neither of them work w/ cockroach.

You can get DBeaver to halfway work by using the generic postgres driver, but functionality is minimal and you still get lots of random errors.

I'm not asking cockroach to build their own database manager, I'm asking for you to make it compatible w/ existing postgres tools, or at least, the popular ones. I promise you this will expand the usage of the product greatly. How many people are going to manage and maintain a production database without any tooling other than cli?

CLI is great for people who know how to make their own database software (you guys). For developers, project managers, etc knowing and executing raw sql for every task is not scalable. I know how to do basic things like creating a basic table and adding a new column. But I still go do those tasks in pgAdmin. I don't type it out. I don't want to deal w/ typos, syntax issues, etc.

Supporting these tools should be priority number one, it will greatly increase your adoption rates. /rant.

Thanks, love what you're doing. I just think supporting good tools is a far higher priority than pretty much everything on the 1.1 roadmap. Faster table drops is not going to increase adoption rates nor make this product easier to use.

[lwansbrough]

I have to say I wholeheartedly agree with the sentiments here. There are virtually no compatible management UIs for CockroachDB. Is this by design? I thought CockroachDB was trying to be compatible with Postgres? Even if it isn't (with regards to supporting UI based tools), it seems to me like a huge oversight to ignore such a basic requirement for many devs (I can understand that database designers may not need a UI - but for us mere mortals it's nigh a requirement).

Based on the way CockroachDB has been developing the system, I would say creating a UI based authentication system is definitely putting the cart before the horse. We need a scooter, not the chassis of a car! :)

Perhaps I could recommend you allow an unauthenticated UI as a first pass. Flag enabled, nothing complicated. A query box + a result box. Something that keeps me out of the CLI.

As @bladefist mentioned - this is a huge obstacle for what I imagine is a considerable number of developers who want to get started with CockroachDB. If the goal is to build a DB that promises to be developer friendly, this needs to be not only on your roadmap, but a priority.

[dianasaur323]

I'm glad we have such an opinionated open source crowd, and I agree with you that we perhaps over-index on the CLI right now. I hear you loud and clear, we'll find a better solution for you. I do need to do some exploring and research on my part though, since as you know, this hasn't been something on our immediate roadmap. Rest assured, I'll update this issue once we decide on an approach.

My basic takeaway from everyone's feedback is that regardless of whether we support this internally in our UI or externally with another tool, you want a UI with which to interact with your data for making DDL statements or running queries. Will be back with an approach soon.

[lwansbrough]

Thank you!

[bladefist]

@dianasaur323 thanks!! I'm opinionated because this is really the only missing piece to cockroach being my go-to database, it solves every other problem. Thanks again.

[dianasaur323]

@bladefist, I can't tell you enough how helpful it is to get such great feedback about what to work on in our product. Keep it coming!

[vivekmenezes]

After looking at DBeaver and PGadmin I'm tempted to vote for DBeaver as the first one we should aim to support because it is newer and is a more general tool to support all the different kinds of database types.

[couchand]

We should use this issue specifically to track support for third-party administrative tools, since we already have one for building query/DDL features into the admin UI - #9467. (I'm guessing that's what you were thinking with the name change @vivekmenezes?)

[ansarizafar]

Valentina studio is a free fast ui tool which work great with Postgresql but no CockroachDB support.

[awoods187]

We are looking seriously at this for 1.3. Would love to hear more about what's necessary from a CRDB support perspective

[bladefist]

@awoods187 punting this to 1.3 is extremely disappointing. Major mistake in my opinion.

https://forum.cockroachlabs.com/t/cockroachdb-feature-request-gui-for-rdbms/266/26

Lots of people are having issues w/ this. I don't know if you manage a production database daily or not, but for those of us who do, working in CLI to manage tables, queries, etc is not ideal. Not to mention cockroach aims to be compatible w/ postgres drivers.

[leomkkwan]

Totally agree with @bladefist. GUI is very important, which only stability and client driver could be above of it. Putting this into 1.3 is disappointed as that put GUI support almost a year away from now.

From the support perspective stand point, if CRDB could pick one GUI, for example DBeaver, out there and make a error free environment that could be very helpful, just like how MariaDB bundle HeidiSQL in their windows version. My guts tell me that is not a really huge project as it is working partially already.

[valentin2105]

👍 🥇 Need CockRoach on PGAdmin.

For now, got this error :

ERROR: unknown variable: "datestyle"

It would be awesome if Cockroach can be "really" compatible w/ Postgres. For Example, I can't use Netbox or Redash (Web Apps) with CockRoach..

Thanks a lot otherwise for your awesome work.

[jordanlewis]

Hi @valentin2105, CockroachDB has support for the datestyle var, actually. Make sure you're on a recent version!

[valentin2105]

@jordanlewis True, after updating Cockroach to v1.1.3, Got this one : ERROR: unknown variable: "bytea_output"

[jordanlewis]

@valentin2105 there are a lot of issues preventing pgadmin compatibility at the moment, including #3288. Please consider using a different tool for now.

[valentin2105]

Thanks @jordanlewis :)

[jordanlewis]

Did a quick review of these tools today. PGWeb should be supported soon after #20788.

pgadmin, DBeaver, DBVisualizer, PSequel, and Valentina Studio are all blocked on either #3288 (Correlated subqueries) or #16971 (_pg_expandarray).

Datagrip has its own issue here: #20641.

DBGlass is blocked on #13319 and related issues (namespace vs schema vs database).

Postico works well ✅

[lexesv]

Hello! PGWeb is no supported yet?

./cockroach version
Build Tag:    v2.0-alpha.20180129-161-gdbf321394
Build Time:   2018/02/01 11:18:42
Distribution: CCL
Platform:     linux amd64
Go Version:   go1.9
C Compiler:   gcc 6.3.0 20170516
Build SHA-1:  dbf3213949eeee95eef3d9b894c5efcb10cd4d28
Build Type:   development

Pgweb v0.9.11

[jordanlewis]

@lexesv, we're still missing has_schema_privileges which fell through the cracks. Thanks for pointing it out. It should be available in an upcoming alpha.

[BransonGitomeh]

@jordanlewis waiting on the pgweb support! thanks

[viniciusmiguel]

To increase CockroachDb popularity you need to support at least one administration tool guys, all databases in the market have at least one administration tool. I understand that your priority is to make the database great and stable but the end user experience regarding DBA is a must!

[jordanlewis]

pgweb will be available when 2.0 is released. Others will become available over time. Thanks for chiming in on this everyone - we understand that these tools are important.

[awoods187]

Note--PGWEB should work with the latest 2.0 beta

[thetooth]

@awoods187 which version of pgweb are you using? just tried latest release and built master to no avail, getting: Error: pq: unknown function: has_schema_privilege(), def have schema privilege despite search results suggesting it is a permissions issue.

Also I have found SQL workbench/J works enough to connect and run querys once you enable auto commit. Obviously no support for the new JSONB type :P

[lexesv]

@thetooth You need to build this version of the cockroachdb https://github.com/cockroachdb/cockroach/releases/tag/v2.0-beta.20180319

[thetooth]

@lexesv thx, just realised I am running a stale binary, will play with this tomorrow

[borisnaydis]

DBeaver seems to work with 2.0.

[drzero42]

For future readers - if you are attempting to connect to CRDB 2.0 with DBeaver, ensure you use the PG JDBC 42.2.2 or newer release, or you will get an error about DateStyle parameter that was changed to ISO.

[pradt2]

DBeaver 5.0.2 can connect now with the generic Postgres driver, but in my experience, the functionality is still rather limited. I am getting SQL errors all over the place (i.e. while viewing unique keys/indexes/triggers etc.). Seems like the compatibility is not there yet.

[Tungtono]

Hey folks, TablePlus released its official support for CockroachDB the same time with CockroachDB 2.0. Go have a look: https://tableplus.io Github issue: https://github.com/TablePlus/TablePlus/issues/374

[pradt2]

Are there any updates on this? I believe cockroach would gain a significant amout of traction if it supported some form of GUI-based data management.

[vivekmenezes]

@fygesser we're looking to support pgadmin.

[BramGruneir]

I'm closing this issue in favour of 4 new meta-issues that should help with identifying which tools the community would like supported and help track all of these requests. I went through this and a lot of our open issues to see what was listed.

If anyone has any requests, please post them to the appropriate issue.

• Drivers and ORMS #25468
• DB Visualizers #25467
• Schema Change Tools #25469
• Misc Other Tools #25470

[BramGruneir]

I also just added #25527 for Monitoring and Alerting tools.