This post is part of an impromptu series about PostgreSQL and things I am learning coming from a SQL Server background. A complete list of posts can be found on this post.
WOW! I honestly didn’t expect to get quite so much notice and feedback on the first iteration of this post. The power of social media at work. π
But I’m so thankful that I did! It allowed me to quickly see that the support for PostgreSQL is stronger than ever, there are many opinions on tooling, and I have a ton to learn. That’s a challenge I’m up for!
I particularly appreciated the post that Rob Conery created in response, gently reminding me that when something starts Open Source and Linux, there’s often a plethora of command line tools ready to help. Much like Git (which is always used as the seminal ‘command line is more powerful’ tool example), something like ‘psql’ is really good at helping me get around to the things I often need to know quickly. I need to remember to consider the roots as I try to grow my understanding deeper.
To that end, I’m all for using command line tools when I can and it makes sense, particularly in database administration. But when I’m knee deep in a new feature and trying to help a young team, I’m still on the lookout for a tool we can use together to better develop and manage the PostgreSQL database day-to-day.
And so, I thought a quick update would be valuable based on the feedback I received here and through Twitter. As always, I’m learning alongside everyone else, trying to be as productive as I can for my employer, my teammates, and the larger community. Hopefully this is helpful part of that larger conversation.
Tooling – Part 2
As you’ll see in the previous post and comments, there are tooling options aplenty – something most of us have come to expect from an open source environment. Here’s where things are at two weeks later for me.
Azure Data Studio
What?? Azure… Microsoft… PostgreSQL?
To be honest, most of this post was already written and focused on DBeaver before I got wind of the pending update to Azure Data Studio. I’ll have a second blog post in the next few days about my experience so far. That said…
As you might have noticed at the end of the previous post I mentioned that Shay Rojansky was hired by Microsoft recently. I didn’t expect that he’d start working on tooling specifically, but it did signal to me that they were putting more investment and energy towards PostgreSQL.
Microsoft announced today as part of the MVP Summit the beta release of a PostgreSQL extension for Azure Data Studio. I was able to get a sneak peak a few weeks ago and I’m excited about the potential. It’s clear that the ultimate goal is to provide access to the same great features they are building into this cross-platform data management application. I’ll have a separate post soon about some of my first impressions, but two highlights that I think hold the most unique opportunity for ADS to become a great tooling option for PostgreSQL.
- Community Extensions: Much like Visual Studio Code, Azure Data Studio is open source and is building out a platform for extensions built by the community. Some initial quick wins could be extensions for better monitoring and tuning… and a graphical query plan visualizer!! (hint, hint…)
- SQL Notebooks: I got to see the first examples of these at PASS Summer last year when Vicki Harp and the team presented and I think it’s an AWESOME idea. Having dabbled in and around data science for a few years now, the power of notebooks to store thought process while also displaying results in-line is so much fun. For the first time, you’ll be able to do this with SQL queries in SQL Server or PostgreSQL. YAY!
DBeaver
After I got past the name, it turns out that DBeaver Community Editionis now my daily driver. I have no idea what the history is (time has been at a premium lately), but the overall layout and functionality comes close to my SSMS “expectations”. I’m not saying SSMS is the standard to reach for, simply that it helps me be more productive quickly because it’s more intuitive… for me.
The navigation layout follows a predictable pattern of feature separation, query editing just feels more intuitive (which is subjective for sure), and most important to me, DBeaver produces accurate DDL when I inspect database objects. As a developer and administrator ramping up on a new project, this kind of detail is especially important for me. Otherwise, I’m left wondering if my latest modifications were applied correctly.
As with most other PostgreSQL tools, DBeaver is cross-platform and able to connect to multiple SQL and NoSQL databases. I can’t currently speak to its performance on other databases, although I will be trying it with some of these platforms in the near future.
DBeaver is built on the Eclipse ecosystem, whatever your feelings on that are. One advantage is that the plugin architecture allows for some great free plugins, like export to Excel. Scoff as you might, it makes sending data to Product Owners, managers and users just a little be easier when non-standard data is involved.
And finally, DBeaver is one of the few options aside from PgAdmin that has a free, open source Community Edition that is very capable, while providing a reasonably priced Enterprise Edition for additional features and support. Oh, I almost forgot! It can be installed with Chocolaty too!
A Few Highlights
Most of the other tools I’ve looked at have variations on these features, so I’m not trying to claim that they are unique to DBeaver at all. But again, for whatever reason, finding and using them is just more intuitive for me (at this point).
Intuitive Server/DB Navigation
It might be a small thing, but being able to navigate objects in the database and refer to them consistently with others on the team is immensely helpful. When I talk about Views, Indexes or Functions, there’s no guessing. Again, remember that I’m often thinking about how I can best help others learn to use the DB effectively and having an easy, common way to get about makes this easy.
Also notice a really small thing within each database in DBeaver, access to two simple queries for Sessions and Locks. Again, coming from the SQL Server world, there are so many community contributors teaching and preaching the benefits of knowing some of these essential pieces of day-to-day work. Other tools certainly have something like this I’m sure, but this gave me a quick way to star to explore performance problems on development databases and elsewhere.
DDL/Data Access
I know it seems like I harp on DDL a ton, but one of my main objectives in this new job is to help bring the database into order. If I can’t easily see the data, the schema definition, and know the state of various objects, that is generally a non-starter for me
Double-clicking on any object (table, view, triggers, etc.) will open up the edit window. As you can see, when it’s a table, properties and data preview are close at hand, and all parts of the table are visible through the tabs on the left.
Honestly, this seems like a good option for something like SSMS to have, rather than dealing with each of these objects separately… but I digress.
My one gripe at this point is adding columns to a table through the UI. I haven’t found a quick method to do it graphically. Generally I resort to straight SQL for these operations, but when I was creating a prototype this week, I was jumping back and forth wishing I could just quickly tab through some options. It’s minor.
Result Table Filtering
To be honest the first time I saw this was with DataGrip. I didn’t think it would be useful at all and mostly just assumed it was added as a checkbox to increase the feature count. But, I’m surprised how often I’m in a database and a co-worker asks if something exists and my first reaction is to double-click the table (display data) and then start typing a column and value to filter. It’s not my main method (I can still crank out “SELECT x FROM table WHERE…” as fast as the next guy…), but it is a useful little time saver now and again.
SQL Formatting and Intellisense
Another small detail that doesn’t work as well in other tools I’ve tried. And, it even bests the default SSMS capabilities (although, Red Gate’s SQL Prompt is a great tool that easily bests what’s built into DBeaver!)
When I’m typing, speed is generally more important to me, which means that I don’t use all CAPS for keywords in the SQL language. Yes, it doesn’t matter. But it does look better when things get complicated and I’m trying to show or teach someone about SQL. DBeaver is the first tool out of the box that allows me to set CAPS for auto-complete on intellisense and it generally just works. In other IDE’s, it will only go CAPS if I start the word with a capital letter. Arg.
When I’m typing, speed is generally more important to me, which means that I don’t use all CAPS for keywords in the SQL language. Yes, it doesn’t matter. But it does look better when things get complicated and I’m trying to show or teach someone about SQL. DBeaver is the first tool out of the box that allows me to set CAPS for auto-complete on intellisense and it generally just works. In other IDE’s, it will only go CAPS if I start the word with a capital letter. Arg.
The Others
A few other suggestions made it into the comments of the previous post(and hopefully this one too!). The short list includes:
- DBVis – https://www.postgrescompare.com/
- PGManage – https://github.com/pgmanage/pgmanage
- Aqua Data Studio (not to be confused with Azure Data Studio) – https://www.aquafold.com/aquadatastudio
- Postgres Compare – https://www.postgrescompare.com/
Feel free to check them out and blog about them if you feel strongly about it. I’m sure someone in the community will be thankful you did!
At this point I don’t plan to look for others. Between DBeaver and Azure Data Studio, I can work more effectively with my team and provide better, faster service to my internal customers with PostgreSQL. I’m sure I’ll take a few minutes from time to time, seeing if any new contenders are making waves.
Stay tuned for more information and feedback about Azure Data Studio.