I can't believe how hard it is to find a good database client tool that runs on Linux. I spent hours on Sunday looking for something that worked as well as pgadmin3. I don't prefer these Java tools that force you to manage JDBC drivers and usually share dated and cluttered UIs. Plus, I can't stand having to manage connections to each database instead of to each server. Let me connect to the server and then select my dB.
My notes:
- Lists
- https://wiki.postgresql.org/wiki/PostgreSQL_Clients
- https://wiki.postgresql.org/wiki/Community_Guide_to_PostgreSQL_GUI_Tools
- Clients
- Most clients are Java, which is a pain b/c
- they require you to list the DB in the connection
- JDBC drivers can not use a socket connection
- UI isn’t great
- Valentina Studio
- Has free & pro ($199) versions
- Was the easiest to get connected initially, but then found bug that showed it wasn’t using socket connection as expected.
- Navicat
- Linux version is a Wine app
- RazorSQL ($100 / year)
- Is a Java app
- Not much different from SQL Workbench, just use that instead?
- TeamSQL
- Electron, AppImage
- Wanted me to enter an email address just to use the software, became privacy concerned about how much stuff would get sent to their servers
- DbVisualizer ($197, $69 renewal)
- Java
- Downloaded & tried it, typical java app
- SQL Workbench/J (OSS)
- Java
- Datazenit
- web based but still Java?
- PgManage
- too crude
- JetBrains DataGrip ($199 / $159)
- Java
- PgAdmin3
- has lots of errors when used with PostgreSQL 11
- PgAdmin4
- installed, couldn’t get past the splash screen which said it couldn’t find the application server
- Sqlectron
- Electron
- Couldn’t get a DB connection to save
- dbForge
- Windows only
- dbGlass
- No longer developed
- HeidiSQL
- Windows only
- DBeaver
- OSS and enterprise version
You can flip between postgres databases in dbeaver quite easily (dbeaver calls it changing the "active object"). I used to use pgadmin3 every day, and have found dbeaver to be much better, and about the same as DataGrip. Although some things still feel better in psql or pgcli.
The vast coverage of JDBC is probably a good reason so many tools use Java. I'm sure you wouldn't prefer they all used ODBC or DBI instead.
PopSQL (https://popsql.io) should give you the modern UI you're looking for. Although you will have to create a connection per database, unfortunately.
Won't show more than a white screen because of "[Oracle] NJS-045: cannot load the oracledb add-on binary for Node.js 8.2.1 (linux, x64)" - this does not inspire confidence.
Website says "PostgreSQL, MySQL, BigQuery, SQL Server, Redshift, Snowflake, SQLite, Presto, Cassandra, Oracle, ODBC, and more on the way."
DataGrip is fantastic and not expensive if you use other JetBrains products. It also manages its own JDBC drivers -- you don't need to install at the OS level.
I have subscribed to this issue [1] of azuredatastudio (ex sqlopsstudio) in the hope that one day it will support more than SQL Server. I am not sure Microsoft would have any interest in supporting others SQL engine though. Visually the software looks really similar to VSCode by the way.
As someone that has done lots of Java development you have lost me there, as JDBC drivers obviously do make use of network connections, usually TCP/IP sockets.
- Valentina Studio: support confirmed and fixed the bug in about a day. Waiting for the next release. Not crazy about some of their UI, but hopeful this will be my answer.
Dbeaver is the best FOSS database UI tool hands down. I've done a lot of searching and haven't found anything close. Managed to convert my whole office to it in a matter of months.
Favorite features:
Well integrated ER diagrams that let you quickly see parts of the db related to current table, or the whole thing if you want.
SQL generation from structure or data edits. Great for cranking out DB migrations like a boss.
Ability to maintain many database connections at once, and support for doing stuff before/after connecting which we widely abuse to setup SSH and VPN connections.
Very liberal database support. My original search for a tool like DBeaver was from the need to connect to MySQL, Postgres, and SQL Server and not wanting to use a bunch of different tools. DBeaver supports roughly anything Java supports, which is everything.
Wide OS support. Good luck finding another tool that can connect to MS SQL Server on MacOs that isn't garbage or money.
The icon is also adorable
The only downside is a bit of classic FOSS UI clunk. Not nearly as rediculous as Eclipse or Blender or the like, but it takes a few days to get comfortable.
Maybe I've been jaded by SSMS and Mysql Management Studio heh. DBeaver actually has a better UI than most DB tools I've used, even though that's still not saying a lot.
Mouse over the buttons for an excessive amount of time and the tooltips will tell ya what they do.
Double click a table and go to "data" tab to see contents.
Schemas ->public is just a side effect of supporting a lot of different databases. Many support schemas
Ram is because default java heap size, you could probably change launch params to get it much lower though.
On all counts other DB tools I've used are just as obtuse and burn tons of resources. Most are written in Java or another high-level language because it's nigh impossible to get DB drivers for a ton of different databases working together in C. DBeaver is basically leveraging JDBC for what it was designed for
I have this same smoke test and it's one of the reasons we built "slash commands" into SeekWell (https://seekwell.io/). If you type "/show" (or even just "/s") you get a list of tables that will be filtered down as you type. Once you select a table it will autocomplete a SELECT statement and return the results. It also works for things like adding a WHERE condition. The goal is to make it so you never have to leave your keyboard, but also never need to type out menial SQL. Here's a 30 seconds video showing how it works.
It's about $8 a month and even lower on subsequent years.
But the real power of DataGrip comes by using it in one of their IDE that auto completes table and column names inside code and allows you to shift-click it to get to see the definition and show warning when you specify an unknown field.
The software is great but I would never buy it purely because of their business model.
There is absolutely no reason DataGrip needs to be a subscription service. There aren't any ongoing costs for customers to use it.
It should be a one-off purchase with optional support subscription, maybe requiring re-purchase at major version increments, not this bullshit where you need to maintain a subscription for a license on a binary program you already paid for and installed.
Feels as dodgy as Adobe turning Photoshop into a subscription service
Fuck these business models and the sales idiots who try to apply them to every single product
I used to think like this too, but I've since changed my mind and understand why they do this -- and it can even be beneficial for users. There's a few big problems with the old model:
First, you have to define the difference between "major" and "minor" version. Users expect major versions to have some significant improvements or new features. What this means is as a developer, you have incentive not to release minor features regularly, but instead batch them together so you can do a "major" release.
Second, the sales team now dictates the release cycle. More major releases means more money, but do it too frequently and the user base revolts. In many companies sales dictates the releases, but now if development is late it's messing with the company's cash flow and ability to continue to exist.
Third, the software can never be "done", because that means no more major releases. As a result, unless there is a sustainable stream of new users, you get feature and scope creep.
Subscriptions mean predictable revenue, and make the developer's incentive align with users: keeping users happy. This means keeping the product stable, making incremental improvements, and evolving with features that make sense.
Of course the beauty of the market is you should be able to find products sold both ways, and you can choose.
All jetbrains products have a licence witch grants you the right to use the tools you buy forever(Without updates). If you buy a tool in 2019 you get a licence for 2019.1 and updates for a year. So if you never update you only have to pay once.
Our team just bought a bunch of licenses of DBeaver and I think this is only correct if you mean 'relational' databases. We have a MongoDB and Eleastic and I don't believe that DataGrip supports either of these, which was a real drag.
We're fully on Jetbrains tools otherwise (R#, dotTrace and dotMemory, TeamCity as build server) so it was a no brainer for me to want to continue that trend but it didn't check all the boxes for us, despite its clear polish.
I tend to hate any IDE built on Eclipse (which I think DBeaver is), but I've been very pleasantly surprised with how well it works.
1. It's got very nice ER modeling where other tools (Like Oracle SQL Developer) make you jump through a dozen screens and a wizard to make a simple diagram.
2. It's got nice export tools to get your data to business partners in a more convenient way. Sure you can copy tables in pretty much every tool but there's just an 'excel' button that can pop open your current query in a new sheet.
3. It does have a weird delay when opening up databases sometimes where it 'reads metadata' about the table and it can sometimes take a long time to return even the most basic query. But once it's cached that data I've not noticed problems after that.
4. It does seem like the document database tooling isn't as baked as the relation tooling. I routinely get hangs when querying my Mongo collections, which is sometimes a drag. But it hasn't been a huge issue.
Overall, I'd highly recommend the paid version of this tool as it's helped me consolidate: Robo3T, Sql Server Mgmt Studio, SQL Developer, and MySQL Workbench into a single tool.
My previous workplace used to be locked into the Microsoft ecosystem and the core legacy product was backed onto a Microsoft SQL Server DB.
Over the years we pushed the business to move away from the MS/Windows ecosystem. When this happened, like many others, I looked for a UNIX compatible DB client that supported SQL Server.
First, I tried SQuirreL[1] and it was horrible. I just had to uninstall it and keep looking. I settled on DBeaver for a while as it has some nice features and it did most of the things I needed it to, but it was not particularly polished.
Eventually the business decided to pay for Jetbrain's All Products package which includes DataGrip and from my experience you could say: Eclipse is to IntelliJ IDEA what DBeaver is to DataGrip.
The other product I was looking closely at was Navicat for SQL Server[2], which looks pretty damn good and those who use it seem to swear by it. However, I am not a DBA and for that reason I can't justify the USD$699 personal licence price tag of Navicat.
DataGrip is not perfect, but it's pretty damn close and I think its price tag is well justified.
I've never personally used it, but I'm fairly sure you can use Azure Data Studio[0] for plain MSSQL databases, despite Azure being in the name. I'd be in interested in what people think of it, since I've never seen anyone talk about this in the wild.
I tried to like it and used it for a few months but eventually got back to SequelPro and it just feels breeze to use when every operation is so snappy not to mention an instant launch.
The embedded DataGrip can be a good tool inside one of their IDE though.
Does it provide any linting or other code checks? I'm curious because I'm looking for something that can teach me proper SQL coding style and practices as I write it.
Been a user of DBeaver for years - across all my platforms. Its does most things well enough that it has not been a problem to use across SQLite, MSSQL, MySQL and various file based data. Easier to use than SQuirreL which used to be my fav. All of these are java tools I think primarily because of starting from JDBC for which there seems to be a driver for just about everything on the planet.
So DBeaver scratches my itch of needing same tool across platforms, across databases.
I like to try out new tools likes this, but whilst on my mac, and I generally use Sequel Pro for my databases (I use mysql and mariadb drop-ins). I have my database locally in a docker container, where I forward 3306 to 3300.
Entering host as 127.0.0.1 with correct credentials and port, it gives me this error: "Could not connect: Access denied for user 'root'@'172.18.0.1'".
This does not happen with Sequel, but occurs with Dbeaver. It actually also happened trying TablePlus which is a piece of software that a fellow commenter was mentioning.
I'm sure that it could be solved in my configs by allowing that particular IP, which is the local IP of the virtual machine which is serving docker. However, that is supposed to be completely transparent, thus I should be able to connect through 127.0.0.1.
As I haven't looked at the source code, it seems like the host lookup logic is flawed, as it translates my `127.0.0.1` to `172.18.0.1`. Why? Dunno. But this occurred in both Dbeaver and TablePlus. Works in my terminal and in Sequel however.
This sounds like a docker network problem more than an issue with dbeaver.
Try binding MySQL to 0.0.0.0 (all interfaces) instead of 127.0.0.1 (loopback). That said, 127.0.0.1 should be fine provided you connect through the service name or the docker container IP), connecting via 127.0.0.1 doesn't make sense. Then again, it's docker on Mac, so I don't really know.
Fwiw, also used sequel pro on Mac, moving to Linux dbeaver was the only thing that came close.
Now I don't really mind either but dbeaver is a bit quirky overall.
I know I can solve that by allowing "everytuldhing" to connect, but that is not my point. Hence my ""I'm sure that it could be solved in my configs by allowing that particular IP"" comment.
That it's `Docker on mac` is kinda irrelevant, as it's still just a virtual machine with a somewhat static IP. My point is, that when it comes to transparency on that level, this piece of software does not acknowledge that, but Sequel Pro does. I'm not a particular fan of Sequel Pro, but it just works in these types of case.
I'm still expecting, that software like this, would be able to handle this type of "proxy mechanism", so from outside the docker container, I could still connect through 127.0.0.1 to it, thus making the default config adequate.
Not sure if this is even makes sense, but I feel, that when connecting through a binding docker port, it should still use the origin IP ie. 127.0.0.1
Woah, that ER diagram functionality reminds me of a tool that I tried a long time ago but which I had since forgotten. Back then the tool in question had too many issues and shortcomings, but they may have developed it sufficiently now.
Being told about Dbeaver and reminded of pgModeler right now is great because I am currently defining the tables for a project I am working on for a client, and as the number of tables grow jumping back and forth between the files that define each of them adds a still small but noticeable overhead. With a ER diagram tool I might be able to speed up a little part of the definition work.
Readit News
My notes:
The vast coverage of JDBC is probably a good reason so many tools use Java. I'm sure you wouldn't prefer they all used ODBC or DBI instead.
Won't show more than a white screen because of "[Oracle] NJS-045: cannot load the oracledb add-on binary for Node.js 8.2.1 (linux, x64)" - this does not inspire confidence.
Website says "PostgreSQL, MySQL, BigQuery, SQL Server, Redshift, Snowflake, SQLite, Presto, Cassandra, Oracle, ODBC, and more on the way."
DataGrip is fantastic and not expensive if you use other JetBrains products. It also manages its own JDBC drivers -- you don't need to install at the OS level.
[1] https://github.com/Microsoft/azuredatastudio/issues/56
That was my experience as well. pgAdmin 4 is such a shit show.
As someone that has done lots of Java development you have lost me there, as JDBC drivers obviously do make use of network connections, usually TCP/IP sockets.
- Valentina Studio: support confirmed and fixed the bug in about a day. Waiting for the next release. Not crazy about some of their UI, but hopeful this will be my answer.
- DBeaver: going to give it a try
dpage/pgadmin4
Favorite features:
Well integrated ER diagrams that let you quickly see parts of the db related to current table, or the whole thing if you want.
SQL generation from structure or data edits. Great for cranking out DB migrations like a boss.
Ability to maintain many database connections at once, and support for doing stuff before/after connecting which we widely abuse to setup SSH and VPN connections.
Very liberal database support. My original search for a tool like DBeaver was from the need to connect to MySQL, Postgres, and SQL Server and not wanting to use a bunch of different tools. DBeaver supports roughly anything Java supports, which is everything.
Wide OS support. Good luck finding another tool that can connect to MS SQL Server on MacOs that isn't garbage or money.
The icon is also adorable
The only downside is a bit of classic FOSS UI clunk. Not nearly as rediculous as Eclipse or Blender or the like, but it takes a few days to get comfortable.
- Again I wonder why it doesn't show the tables first, I have to go through Schemas -> Public.
- 480 MB of RES RAM, I'm connected to one smallish Postgres DB.
- I can't even find "just show me the contents of the table, like 'SELECT * FROM'.
I know, maybe it's tailored for Enterprise Database Architects, but this is my smoke test for a DB GUIs:
"How long and how many clicks does it take so that it provides more value than: 'psql; \dt+; SELECT * FROM X;'"
Mouse over the buttons for an excessive amount of time and the tooltips will tell ya what they do.
Double click a table and go to "data" tab to see contents.
Schemas ->public is just a side effect of supporting a lot of different databases. Many support schemas
Ram is because default java heap size, you could probably change launch params to get it much lower though.
On all counts other DB tools I've used are just as obtuse and burn tons of resources. Most are written in Java or another high-level language because it's nigh impossible to get DB drivers for a ton of different databases working together in C. DBeaver is basically leveraging JDBC for what it was designed for
https://www.useloom.com/share/0e0b79b0b8304035af4bb15552abc3...
These guys actually fix their bugs. It works on every database known to humans, and also does nearly everything DBeaver appears to do.
For many people, including me perhaps, something like DBeaver is good enough since it's free.
https://www.jetbrains.com/datagrip/buy/#edition=personal
But you're probably right.
Compared to a six-figure salary? Developers tools are a bargain for what we get out of them.
But the real power of DataGrip comes by using it in one of their IDE that auto completes table and column names inside code and allows you to shift-click it to get to see the definition and show warning when you specify an unknown field.
There is absolutely no reason DataGrip needs to be a subscription service. There aren't any ongoing costs for customers to use it.
It should be a one-off purchase with optional support subscription, maybe requiring re-purchase at major version increments, not this bullshit where you need to maintain a subscription for a license on a binary program you already paid for and installed.
Feels as dodgy as Adobe turning Photoshop into a subscription service
Fuck these business models and the sales idiots who try to apply them to every single product
First, you have to define the difference between "major" and "minor" version. Users expect major versions to have some significant improvements or new features. What this means is as a developer, you have incentive not to release minor features regularly, but instead batch them together so you can do a "major" release.
Second, the sales team now dictates the release cycle. More major releases means more money, but do it too frequently and the user base revolts. In many companies sales dictates the releases, but now if development is late it's messing with the company's cash flow and ability to continue to exist.
Third, the software can never be "done", because that means no more major releases. As a result, unless there is a sustainable stream of new users, you get feature and scope creep.
Subscriptions mean predictable revenue, and make the developer's incentive align with users: keeping users happy. This means keeping the product stable, making incremental improvements, and evolving with features that make sense.
Of course the beauty of the market is you should be able to find products sold both ways, and you can choose.
You can see it on the “buy” page.
It’s still less than ideal, but I pay yearly for their “all pack” product and I feel with the amount I use it I’ve gotten my money’s worth every year.
If you want major updates, it's not weird to be asked to pay for it, which means continued subscription.
We're fully on Jetbrains tools otherwise (R#, dotTrace and dotMemory, TeamCity as build server) so it was a no brainer for me to want to continue that trend but it didn't check all the boxes for us, despite its clear polish.
I tend to hate any IDE built on Eclipse (which I think DBeaver is), but I've been very pleasantly surprised with how well it works.
1. It's got very nice ER modeling where other tools (Like Oracle SQL Developer) make you jump through a dozen screens and a wizard to make a simple diagram.
2. It's got nice export tools to get your data to business partners in a more convenient way. Sure you can copy tables in pretty much every tool but there's just an 'excel' button that can pop open your current query in a new sheet.
3. It does have a weird delay when opening up databases sometimes where it 'reads metadata' about the table and it can sometimes take a long time to return even the most basic query. But once it's cached that data I've not noticed problems after that.
4. It does seem like the document database tooling isn't as baked as the relation tooling. I routinely get hangs when querying my Mongo collections, which is sometimes a drag. But it hasn't been a huge issue.
Overall, I'd highly recommend the paid version of this tool as it's helped me consolidate: Robo3T, Sql Server Mgmt Studio, SQL Developer, and MySQL Workbench into a single tool.
My previous workplace used to be locked into the Microsoft ecosystem and the core legacy product was backed onto a Microsoft SQL Server DB.
Over the years we pushed the business to move away from the MS/Windows ecosystem. When this happened, like many others, I looked for a UNIX compatible DB client that supported SQL Server.
First, I tried SQuirreL[1] and it was horrible. I just had to uninstall it and keep looking. I settled on DBeaver for a while as it has some nice features and it did most of the things I needed it to, but it was not particularly polished.
Eventually the business decided to pay for Jetbrain's All Products package which includes DataGrip and from my experience you could say: Eclipse is to IntelliJ IDEA what DBeaver is to DataGrip.
The other product I was looking closely at was Navicat for SQL Server[2], which looks pretty damn good and those who use it seem to swear by it. However, I am not a DBA and for that reason I can't justify the USD$699 personal licence price tag of Navicat.
DataGrip is not perfect, but it's pretty damn close and I think its price tag is well justified.
1. http://squirrel-sql.sourceforge.net/
2. https://www.navicat.com/en/store/navicat-for-sqlserver/
[0]https://github.com/Microsoft/azuredatastudio
The embedded DataGrip can be a good tool inside one of their IDE though.
Except any non-relational DBs.
MySQL, Postgres, SQLite, etc...all the tools I need in one app.
I generally try to avoid Eclipse-based projects because they can be very bloated, but in this case it's easily justifiable.
So DBeaver scratches my itch of needing same tool across platforms, across databases.
Try binding MySQL to 0.0.0.0 (all interfaces) instead of 127.0.0.1 (loopback). That said, 127.0.0.1 should be fine provided you connect through the service name or the docker container IP), connecting via 127.0.0.1 doesn't make sense. Then again, it's docker on Mac, so I don't really know.
Fwiw, also used sequel pro on Mac, moving to Linux dbeaver was the only thing that came close.
Now I don't really mind either but dbeaver is a bit quirky overall.
https://pgmodeler.io/
Being told about Dbeaver and reminded of pgModeler right now is great because I am currently defining the tables for a project I am working on for a client, and as the number of tables grow jumping back and forth between the files that define each of them adds a still small but noticeable overhead. With a ER diagram tool I might be able to speed up a little part of the definition work.
Edit: Hmm, pgModeler might still not be such a great tool https://news.ycombinator.com/item?id=17693582
Dead Comment