SequelPro is the gold standard. I've used it for years, and I love it. Unfortunately, like many developers, I'm slowly moving away from MySQL to PostgreSQL, and none GUI tools measure up. Instead of making YAPC (Yet Another Postgres Client), I wish a developer would just write a PostgreSQL adapter for SequelPro[1]. I'd happily contribute $50 or $100 towards a Bountysource.
Writing a PostgreSQL adapter for Sequel Pro is a good idea. I don't know how an adapter can fit into Sequel Pro's architecture though. I'll take a look at Sequel Pro's codebase and consider this approach. Any help would be appreciated!
Try Navicat (http://navicat.com – looks like they even have a Cyber Monday sale right now). Been using it for a couple of years now, and it's great (also "feels native", which is a must for me). There are many editions (I have "Premium Essentials", which was affordable and does all that I need).
Can't emphasise this enough. SequelPro is absolutely the best part of using MySQL. I'd to would love to pitch in for a SequelPro-esque GUI tool for PostgreSQL
Another person here willing to contribute.
SequelPro is really the best database client I've used, even though it might be considered 'spartan'/simple.
I had a look at doing this a while ago and ran away screaming, it's quite eye watering in terms of being agnostic. I started one myself but haven't had enough time to make the UI perfect at the moment. But I did make progress on a way to use many different types of DB's..for example
Sadly it looks that SequelPro was never built as a platform for cross-database support. After glancing through the source the integration with MySQL seems really tight. Also SequelPro is OSX only which is a shame because I think that Sequel is better than most of the alternatives.
Hopefully Datazenit[0] will solve this and provide great cross-database, cross-platform support. Currently it is beta stage and supports MySQL/PostgreSQL, but other database systems are on the way.
What I wish some of these projects would focus on are DBA features that only PgAdmin performs today. The market (both OSS and commercial) is filled with data editors, but none that focus on schema, user, and object management.
PgAdmin does handle these functions, but the UI can be buggy, and is subject to problems with slow network connections - forced restarts can be common.
The unfortunate reason for that is a data editor can be whacked out in a couple of weeks (well, at least an MVP).
Making object editors takes a lot more effort, especially considering all the different objects PostgreSQL has: schemas, tables view, materialized views, indices, functions, sequences, etc.
I'm struggling with this myself. I'm currently trying to add object editors to PG Commander, and it's months and months of work. I wanted to have a table structure editor in PG Commander 1.0, and now, more than a year later, I'll finally be able to deliver a basic table & view editor in version 2.0 -- you can try a prerelease here: https://eggerapps.at/pgcommander-prerelease/
In my case, I almost never edit an object in a UI, but I often view it. Sometimes in a grid (ex. table columns in a grid of type, nullability, default, etc.) or as a single view of all the DDL for the object. I don't trust UIs to edit objects, but they can make the work of creating ALTER scripts much easier.
The latter is where PgAdmin is most useful for DB objects. The DDL view not only shows me things like CREATE TABLE/VIEW/FUNCTION etc., but also shows dependent objects such as constraints, indexes, permissions in the same window. Most of the JDBC based tools do not.
Graphical explain plans can be very nice to have, along with status views that make things like pg_stat_activity easy to consume in an actively refreshing compact view.
Awesome to hear, Jakob-- I recently purchased PG Commander after a couple of months of using it, so a "free" update to 2.0 is awesome. Thanks for your hard work on the app-- it's my default PostgreSQL client.
I've always been a big fan of Sequel Pro [1] which offers all of that, but only for MySQL. I read other databases are in the works. It's also open source [2].
Thanks for your suggestions. My plan is to support most features in Sequel Pro (but for PostgreSQL of course) which do include schema and user management. This release is just a start.
I just wish PgAdmin would respond to typical OSX keyboard shortcuts when editing SQL. Pretty much only control-shift-arrow for word selecting and fn-shift-arrow for sentences is supported. If it would just support a few other basic commands (and make them the same keys as standard OSX shortcuts, or user defined) it would be a huge improvement in usability.
Because it's not a good use of time for one-off modifications. Plus, the GUI is nice for visualizing, so there's a decent chance you're spending a lot of time in there anyway.
It's possible to run a query to modify the contents of a database without changing the schema. You may already be familiar with the UPDATE statement, for instance.
And not every database in existence is part of a larger stack. I don't care much about automating a task I'll only do once in a throwaway DB that exists only on my computer.
1. When I first make a connection, the application window jumps to full screen size. This is very annoying. I running on a 4k screen with 1:1 pixels, so that is far more real estate than the application needs. Even on smaller screens, I would immediately have to resize the window after I connect.
2. I would like to be able to not provide the database name and allow me to switch from one DB to another using the same connection. (also, currently if you leave DB blank and attempt to connect, it crashes.)
3. 80% of the time I directly connect to my databases with a tool like this, it is not to look at the data, it is to create/drop/modify tables and other design and admin stuff. I'll occasionally use it to test complex queries before putting them in my code, but most of my queries are simple enough that they don't need testing prior to coding. So I'd see this as more useful if it had the ability to simplify the admin stuff. As it is, since I'd need to use pgAdmin 80% of the time, its not compelling to us something else for the other 20%.
2. When you switch from one DB to another in postgres, you are essentially making a new connection instead of reusing the same connection[1]. In PSequel, you could create different connection bookmarks for different databases and open multiple connections at the same time in multiple windows.
3. In my company, we write migration scripts (activerecord migration) to modify table structures. We usually don't modify them directly/manually. 80% of the time we use GUI tools just for viewing data and testing out complicated SELECT queries. I didn't realize that many other people's usage patterns are different. Anyway, the feature of modifying table structures is in PSequel's roadmap.
This looks really neat! Another Sequel Pro inspired client that was posted to HN recently was pgweb [0] which is open source that you all might want to check out as well.
I always end up searching for something like this every few months. Congratulations on the launch!
Some feedback for you, though:
"I don't want to pay Apple for delivering a free app."
I can understand this out of context, but I don't think it's a reasonable answer to the question of why the app isn't code-signed (i.e. that has nothing to do with distribution). It's quite the favor to ask of your users for a closed-source app.
I've used SQLWorkbenchJ on the daily for the last year and a half for postgres (on OSX). It gets a lot of flak because it's java based, can be a bit buggy, and doesn't have the nicest interface. However, if you look past those items, it has some really great features:
- DB Explorer (tab for exploring schema, allows you to search through large sets of tables and other items very easily - primo)
- SQL Formatting
- Well thought out user shortcuts for executing queries
- Intelligent auto-completion
Really, they essentially loaded it with every feature imaginable, which may make it feel clunky, but it's a total Clydesdale in terms of functionality.
Agreed. It's not my main tool (pgAdmin usually works well enough for my purposes), but I fire up SQLWorkbenchJ once in a while when I need something that pgAdmin won't do.
Also it's scriptable data import function works nicely - we have it running here every 15 minutes, loading data from other systems into postgres.
Thank god someone has done this. I have spent so much time looking for a halfway decent psql tool, I currently use 0xDBE, and still not happy. My favorite was Sequel Pro (currently only mysql) and I think its not supported any more.
Navicat provides a really nice tool for Postgres. It's paid, but the OS X 'starter' (or something like that) version can be bought for $15 or so. It's by far the best client I've used on OS X.
Seconded. It took me a while to bite the bullet and buy it, but if you're needing any kind of reliability from a PG GUI, Navicat is by far the best option.
I don't find the lag too noticeable; certainly not when you're in query mode. I do find the lack of Materialized View support lacking, though! They don't materialise anywhere..
This looks nice - Sequel Pro is great for MySQL but for working with PostgreSQL the only GUI I could find for the Mac, last I checked, was Valentina Studio, which, whilst it does pretty much everything you would probably want of a DB GUI, also satisfies every stereotype of cross-platform applications (ie. it doesn't offer a very Mac-like experience).
I'm still on the hunt for an OSX PostgreSQL client with a table editor as awesome as aqua data studio's [0] (their java client doesn't work great on mac). With ADS you can edit the results of any query as if it was a spreadsheet, hit "save", and ADS will generate the correct insert/update statements for you and run them. It handles copy/paste across multiple rows pretty well, even with thousands of update/inserts, and I haven't found any other editors (I've tried pgadmin, jetbrains thing, navicat, this PSequel, and some others) that do this.
DbVisualizer has a similar feature, and works as well on a Mac as the Win and Linux versions - subject to occasional issues common to the JRE on your machine.
The latest PyCharm 4.0 IDE from JetBrains support just this feature, and it's great. I am guessing that the upcoming 0xDBE will support this in a standalone app if you don't need a full blown IDE for another language outside of SQL.
Thanks for your suggestion! The feature of editing query results is actually the second highest priority item in my todo list. (The first one is Query Bookmarks.)
I'm new to PostgreSQL and get nervous twitches from using pgAdmin. At the moment I run my queries in Toad.
Tried PSequel, connected but it say (no schema) in the dropdown top left. Hope It will move forward, would be great if it came close to Sequel Pro. Think the uptake of Postgre among novices and webdesigners would increase if there at least was an easy way to connect and backup restore a db.
One lesser known tool is PGStudio, which can be run as a web tool using jetty on your machine.
as a side note, everyone seems to be on a Mac... And nobody on desktop Linux. Didn't really think that possible that from a postgres (or a mysql) crowd.
There are exactly two things that always dissuade me from using desktop linux:
1. The font rendering is bad on linux. Especially non-anti-aliased monospace fonts, for some reason, which is what I spend 90% of my day looking at.
2. Multimonitor support is a real pain to configure, whereas on OS X you just plug and go. I have cinema displays at home and at work, and I move around between them a lot. That's really painful on linux.
> 2. Multimonitor support is a real pain to configure...
Are you using a somewhat recent KDE? At least on the three machines that I regularly use, the management UI looks like [0], and works at least as well as the one in Windows 7.
Likely my problem is that I really like tiling WMs, so end up trying to make my world work with something like xmonad or even dwm. It's entirely possible multimonitor is satisfactory with KDE, and maybe I should give that another shot.
I do, however, know that OS X does font rendering so much better that I have trouble with pretty much any alternative.
Well, if by "better" you mean "much worse". OSX font rendering is basically inconsistent, blurry blobs (yes, I've used OSX for years as well as various Linux distros).
That's fine if that's what you like, but don't try and pretend that it's in any way objectively "better".
Looks like a good start. Though in my experience the best PostgreSQL GUI is EMS SQL Manager (http://www.sqlmanager.net/en/products/postgresql/manager), bar none. As someone who's been using PostgreSQL for over 10 years now, nothing comes close. Honestly, every other GUI/manager feels pretty basic in comparison, even Navicat. If you're on a Mac, it's most definitely worth using it in VMWare Fusion, or Parallels.
An aside: I don't work for them, and yes, I do buy their licenses.
Most Postgres clients have issues connecting to Amazon Redshift. The wire protocol is the same so basic interactions (ex: connect via psql and run a SELECT) usually works but things get hairy when you start doing more complicated things or even just querying the data dictionary (the basic information_schema is there but Redshift has it's own tables for it's specific features).
My company[1] makes a database client that runs in your web browser that has explicit support for Redshift (as well as Postgres, MySQL, and more). I encourage you to check it out.
Thanks for your reply! Never used Amazon Redshift. I'll try to find out what is so special about it.
Edit:
Amazon Redshift is based on PostgreSQL 8.0.2 but PSequel uses libpq 9.3. I'll work on supporting Redshift if it gets enough demands. Anyone who wants a specific feature could fire a request at http://www.psequel.com/report_bugs
Supporting Redshift is actually pretty simple. The only problem is that you need to pay for a cluster for testing. However, since it's pay-by-the-hour you can keep charges minimal (don't forget to delete the cluster when you are done testing!)
Here are a few gotchas with redshift:
1) OID and CTID columns show up in the catalog, but you can't access them.
2) Primary Keys and unique indices aren't actually enforced!! You can insert two rows with the same primary key.
3) Make sure you only access catalogs that exist in 8.0, eg. there is no pg_matviews before 9.3, there's no typcategory before 8.4 etc. (fortunately the PostgreSQL docs are very helpful)
4) When setting the "application_name" connection parameter, PostgreSQL will have to connect twice to a Redshift server. Recent versions of libpq do so automatically, but you should keep this in mind (eg. when using SSH tunnels, or when validating SSL certs)
However, all in all, it's pretty easy to add support for Redshift. (I know because I'm the developer of PG Commander)
Is there a technical reason why it's only for Yosemite or greater? Programs compiled from Swift are supported on OS 10.9 (I'm on Mountain Lion) and it would be great to try this.
Performance for everything under Yosemite is terrible for me. I won't upgrade my main work-mac until I'm convinced they've gotten Yosemite more stable and better performing.
This looks like a promising tool, but its limit platform support kills it for me, for now.
I myself would either like to see the source or have the developer pay the $99 to get Apple to review the code and make it more trustworthy. As it is, I'm a little nervous running the app and letting it auto-update daily when I have no idea who the dev is or what's in the app.
You do realize that Apple doesn't review code, right? An application in the App Store can issue malicious or accidental "DROP TABLE" statements just as easily as an application you downloaded from GitHub. Apple only reviews apps (in binary format) to make sure they mostly do what they say they do, and that they don't violate Apple's distribution guidelines (porn, trademarks, etc.)
They do at least in theory establish a legal entity that is responsible. It also provides a mechanism for removing the app if malicious behaviour is detected and reported.
You are right about "DROP TABLE" risk in this case but some other threats are mitigated by the sand boxing of apps.
The code signing mechanisms that Apple employs in the App Store removes the risk that a third-party software update server will be compromised and distribute malicious code to an otherwise trustworthy application.
(Probably not a high risk in this case, but on principle...)
Currently the auto-updating feature only reminds you to download an update. It doesn't update itself silently. And you shouldn't edit your production data directly anyway (or at least you should try on a dev server first), right?
I wouldn't be installing apps from an unknown author, but if it was on github and I could review and compile it myself I'd be a bit more inclined to try it.
I see "Query bookmark" in the TODO list. I've been looking a simple, clean PostgreSQL "bookmark manager" that will just hold and execute my saved queries. Would really like to see this feature implemented.
If you're looking for a Postgres (and general) database frontend, check out DBeaver. Not exactly a thin app, but comes with all the batteries included. It's been indispensable in my day-to-day work.
The slowness I find I think its related to the continual view. What happens is I ran I ton of large queries, and then the window lags when I scroll. I should be better about limiting my queries when I only want to see a few records, however I am so use to sequel pro where it shows you the first 1000 by default.
If you get too many queries you can hit Cmd-L to clear the display in Terminal. I recently also added a cancel command to stop queries when you forgot a limit clause.
I know the Terminal in PG Commander isn't for everybody; maybe I'll change it in a future version.
Looks neat!
Won't display metadata correctly on PosgreSQL 8.3 or earlier versions though. This is the error I'm getting: "column "typcategory" does not exist".
Thanks for building this, looks very clean. I'm sorely missing a PostgreSQL client similar to Sequel Pro. Valentina Studio is okay but the UI is too clumsy.
https://github.com/sequelpro/sequelpro/issues/362