Which SQL editor should you choose?
That question spikes anxiety and causes night terrors to crawl up the spines of the best of us.
Why not choose based on the tried-and-true method of which mascot would win in a fight? Who would walk out of the ring victorious?
A cat? A beaver? A girl who grows up in the Swiss Alps with her grandfather? A question for the ages (I personally would vote for the Beaver)!
Perhaps reading reviews on each would serve you better? Let’s try that instead. Come along with me as I review Postico, a SQL editor specifically designed for PostgreSQL on Macs.
Quick backstory: many many moons ago I spent most of my days gazing upon SQL Server Management Studio as my SQL editor of choice. I used to dream in SQL in those days, but that’s a story for another day.
When I parachuted out of that company I didn’t want to fork out thousands of dollars for my dev tools, so I took the plunge into the open source world. I dove headfirst into Ruby on Rails and Postgres, usually doing all my work in the Heroku world (I love Heroku!).
Needing a SQL Editor, I checked out a bunch, but eventually landed on Postico. I needed a stable, easy-to-use SQL editor that I could quickly connect to local databases and remote databases (usually Postgres databases on Heroku). I wanted a SQL editor that didn’t crash, that didn’t come stuffed with too much junk, that worked amazing on a Mac, that offered a free version, and that didn’t break the bank.
A few years later, I can happily report that Postico met each of those challenges and more. Read below for my full review.
Things You Should Consider When Choosing a SQL Editor
Before jumping into your research, jot down a few things to consider. Compiling your must-haves and nice-to-haves before jumping into research can save you hours of time. Some questions you’ll want to add to your list:
- What databases do I need to connect to? Postgres? MySQL? SQLServer? Access (gasp! Let’s pretend I didn’t say that)? Oracle? SQLite? Redshift?
- Do I have to connect locally and remotely?
- What dev environment do I use? Mac? Windows? Linux?
- What functionality do I absolutely need? Editing in table results? Query profiling? Advanced database diagramming?
- How much am I willing to pay?
Spending a few minutes up front on your requirements can save you hours of frustration. Take a few minutes and jot down your answers the questions above. This helps make sure you don’t forget a key piece of functionality your SQL editor must offer.
Who Makes It
The wonderful people at Egger Apps put out Postico. This small team created Postgres.app (OMG, thank you!) which hands down I found as the easiest way to run PostgreSQL on my Mac. They also offer TableTool, an open source CSV editor, and jetread, a tool to let you read Access files from the command line.
Three brave souls make up the team, and they call Linz, Austria home. They obviously care deeply about user experience. This quote from their website explains their philosophy well:
“We put the user experience first. Feature checklists take a back seat. We don't care if the competition has more features. We have better features. Our products are designed with a meticulous attention to detail.”
Description of Postico
Postico offers SQL developers a top-notch SQL editor and query interface to Mac users who work with PostgreSQL databases. Postico offers much more than the ability to query Postgres databases, it comes with the ability to interact with data - filtering rows, sorting rows, editing and even batch editing rows. Beyond data, it allows you to create and alter columns, create and drop tables, and create and alter views. When you do query data, Postico’s syntax highlighting, multiple result sets, automatic indenting, and auto-suggest for column names makes writing queries easy and smooth.
Postico delivers the goods if you work with Postgres databases on a Mac. Postico does not claim to do anything else, so trying to shoehorn it into other databases may leave you unsatisfied.
Postico Pros & Cons:
- Specifically built for PostgreSQL and Macs
- Deep focus on usability and making features awesome
- Lovely user interface and key features to make SQL development a joy
- Free version to get started
- No support for other databases
- Only supported on Macs
- Favorites window confuses me a bit
Postico Features & Benefits
Installing and Get Started
Getting started with Postico couldn’t be easier. Just head to https://eggerapps.at/postico/ and click the “download” button.
After you install the app, just pop open the favorites window and enter your database’s connection info:
Once you connect, a screen showing all of your tables greets you. This example shows my development database for BoxScoresAndMore.com, a site I am building with my grade-school-aged kiddos (gotta start them early!) You can see tables for conferences, games, divisions, etc.
Clicking into a table pops open the data for that table. From here you can edit, bulk edit, sort, filter, etc. This ease of data management makes Postico ultra useful when developing a new app.
If you want to query your data, just click the back button, and click into the SQL Query option:
Once the SQL Editor says hello, Postico’s auto-suggest makes writing queries a breeze:
The queries auto-save every so often so you don’t have to worry about losing your queries.
Postico makes query writing a breeze. It offers the following key features that make writing queries fast, efficient, and easy:
- Syntax highlighting
- Multiple results
Let’s say you wanted to get a quick list of teams from the AL East and NL East. Using Postico’s multiple results windows saves us some time here by letting us simply do this:
Switching between the result sets couldn’t be easier.
The helpful auto-suggest and syntax highlighting make complex queries easy to visually process and compose. These may small sound like small features, but they provide real productivity gains when working in SQL for hours at a time.
Designing and Altering Database Structure
Postico makes working with your database structure a breeze. Let’s say you need to alter the column type of a table. Just right click on the table in the UI and select “Open Structure…” and presto, the structure screen appears:
Make your desired changes, and click the “SQL preview” button to preview your changes. If you want to discard them, just click the “Discard changes” button.
Need to add a Column or Index? No problem, just click the “+ Column” or “+ Index” buttons in the table editor screen, see screenshot above.
What if you need to add a table or view? Easy as pie, just right click in the table list and choose the database object you wish to add:
Postico really shines when it comes to writing queries, adding columns, altering tables, adding tables, adding views, and other day-to-day SQL tasks.
Tuning SQL Queries For Performance
Spending so much of my youth in Microsoft SQL Server Management Studio caused me to fall in love with their query analyzer screen. It showed pretty pictures on key query execution pieces that showed you exactly how to tune your SQL query for maximum performance.
For the life of me, I can’t find anything similar in Postico. To get anything close to this I have to run the EXPLAIN ANALYZE command in my query and see the text results dumped out, like this:
It works, but I got so hooked on seeing the pretty pictures in my youth that I’d love it if Postico surfaced this capability more. Making it more prominent would make me pay attention to query performance more.
What do Other Say About Postico?
Searching for online reviews of Postico shows others find it highly valuable. Reviewers in iTunes rate Postico 4.8 stars across 14 reviews of the current version. Read below for some highlights:
POSTICO is Amazing!
“I was using PGAdmin on my Mac and dealing with constant usability issues. With PGAdmin I would have windows randomly resizing on me while trying to administer my local dev environment. I switched to Postico and everything works great! The software’s UI is not only stable but much more responsive than PGAdmin ever was.”
Jul 1, 2018
Postico is wonderful
“I used it view/modify a Postgres db and it consistently exceeded my expectations.
However, wish there was a way to store local connection profiles at a database level instead of having each bookmark open up ‘localhost.'“
Feb 2, 2018
Kpublik writes on his 4-star review on Alternativeto.net:
“I'm a full-time developer, specializing in database work with PostgreSQL. I've used may database management tools for both Mac and Windows, but by far Postico is my all-time favourite.”
Alternatives to Postico
People looking for SQL Editors consider these alternatives when looking at Postico:
That list is too long to dive into each, but if you spend the bulk of your time developing on a Mac working with Postgres databases, then give Postico a try.
Spending all day working with Postgres databases on a Mac can lead to frustration, inefficiency and lost time if you carry the wrong tools in your toolbox.
Finding a SQL Editor designed to make you productive and that makes your coding enjoyable can turn your days from painful slogs into delightful walks in the park.
For those spending hours everyday crafting Postgres goodness on a Mac we recommend giving Postico a try.
Shameless SQLBot Plug
Do you ever write SQL reports that you’d like to share regularly in Slack or over email? Instead of using something like sp_send_dbmail, give SQLBot a try. It gives you a dead-simple way to schedule SQL reports into Slack or email from Postgres, MS SQL Server, MySql, and even Redshift.