One of the most common questions people ask is Which tools work with PostgreSQL. In a sense the measure of a database's maturity/popularity are the number of vendors willing to produce management and development tools for it. Luckily there are a lot of vendors producing tools for PostgreSQL and the list is growing. One set of tools people are interested in are Database administration, ER diagramming, Query tools, and quickie application generators (RAD).
For this issue of our product showcase, we will not talk about one product, but several that fit in the aforementioned category. All the listed products work with PostgreSQL and can be used for database administration and/or architecting or provide some sort of light reporting/rapid application building suite. By light reporting/application building, we mean a tool with a simple wizard that a novice can use to build somewhat functional applications in minutes or days. This rules out all-purpose development things like raw PHP, .NET, Visual Studio, database drivers etc. Things we consider in this realm are things like OpenOffice Base and MS Access. Most of these tools are either free or have 30-day try before you buy options.
You can't really say one tool is absolutely better than another since each has its own strengths and caters to slightly different audiences and also you may like the way one tool does one important thing really well, though it may be mediocre in other respects. We also left out a lot of products we are not familiar with and may have gotten some things wrong.
If we left out your favorite product and you feel it meets these criteria, or you feel we made any errors, please let us know, and we'll add or correct it. We will be including Free open source as well as proprietary products in this mix. If we left out what you consider an important criteria, please let us know and we'll try to squeeze it in somewhere.
UPDATE: We have added more entries since we initially published this. In playing around with some of these, we discovered that more than we thought sport a drag and drop table draw line join kind of query builder, but some do it better than others. The same holds true with ER modeling relational foreign key gui dialogs within the relational diagram. Since our list is now bigger, we went back to test drive some of these to see how well they do it for PostgreSQL. So in these cases, you'll see more than a Yes/No answer and some of our personal prefences such as JOIN syntax implementation may show more than we would like.
The query builder part is probably something we are more critical of, not because we care that much for them, but that's the first thing new database users need badly. From an expert database user stance, this is only really useful if you can toggle back and forth between design and SQL view without losing your changes.
There were some also added features we noticed such as code completion and plpgsql debugger which we didn't test, but tried to put in the general admin/edit description section if we know it does it or someone commented on it.
We may have also incorrectly tagged some things No if it wasn't intuitively obvious how to do it in our 5-10 minutes of testing, so please correct us in those cases and how you do it as well.
RAD stands for Rapid Application Development - we are only considering tools Yes if they include a wizard to build.| Product | OS Support | Price (license) | Administration / Data Edit /Database Programming | DB Support | WYSIWIG Query Builder | Graphical Explain | RAD | Reporting | Data Import/Export | Relational Designer / ER Modeler |
|---|---|---|---|---|---|---|---|---|---|---|
| Aqua Datastudio 7.5 | Windows, Linux, MacOSX, Solaris | Proprietary ($400) check feature matrix feature matrix | Yes - also has database compare/synchronize, backup and integration with subversion, general admin + edit and procedural debugger | PostgreSQL, MySQL, Oracle, SQL Server, Sybase, Informix, Apache Derby (others with JDBC driver) | Yes | Yes | No | Yes - both meta data and adhoc on screen charts (only viewable in tool?) | Yes - Export (XML, HTML, Excel), Import - Delimited | Yes |
| Database .NET | Windows no install required (aside from .NET) self-standing 5 MB exe | Freeware | Sort Of - ability to script table structures, browse table structure and data (no wysiwig for table design, or editing data) | PostgreSQL, Firebird, MySQL, SQLite, SQL Server, SQL Server CE, Oracle, MS Access, Dbase, OLEDb, ODBC | No | No | No | No | Yes - Export (CSV, TXT,XML) | No |
| Database Master | Windows | Proprietary ($49) | Yes - general user management, ability to edit data including blob, ability to add tables, but datatype options impoverished. | PostgreSQL, MySQL, SQLite, ODBC, OLEDb | No | No | No | Yes - simple query result with ability to export to PDF. In-built reports for server config, process etc. | Yes - Import/Export (XML, HTML, Excel, CSV) | Yes, but doesn't seem to be able to read foreign key relationships of pg (or couldn't get that to work) and can't edit from diagram. |
| DBTools Manager Enterprise 3.4 | Windows | Proprietary ($100-$500) depending if you get add-on packs for non-OS dbs (also Freeware version and standard) - check feature matrix | Yes - include edit, also has database compare/synchronize, backup (in sql format), Task wizard for ETL (interface intuitive) | PostgreSQL, MySQL, Interbase, Firebird, SQLite packaged (extra purchase packs for SQL Server, Oracle, Sybase, ODBC, MS Access) | Yes - you have to launch the sql query designer icon (not intuitively obvious). Also automatically shows defined relationships, but doesn't seem to allow LEFT,RIGHT JOIN or toggle between SQL/Design view. | Yes | Yes but can currently only use within DBTools. A redistributable runtime is expected for later versions. | Yes - both meta data and data reports | Yes (also database migration wizard) - MS Access, Excel / Open Office spread sheet, CSV, DBF,HTML, XML - ADO/DAO/ODBC (Paradox, Foxpro, DBase) data sources | Yes |
| DbVisualizer 6.5 | Windows, Linux, MacOSX | Proprietary ($150) (also a freeware version) check feature matrix feature matrix | Yes - general admin + edit and blob editing show in chart | PostgreSQL, Oracle, Sybase, SQL Server, DB2, Mimer, Neoview, MySQL, Informix, JavaDB/Derby | Yes - supports ANSI LEFT,RIGHT, FULL joins, and toggle between SQL and Design, changes in SQL view can be seen in design by clicking load to query design view. | Yes | No | No | Yes - CSV, HTML, XLS, XML | No? or at least couldn't find it |
| EMS SQL Management Studio for PostgreSQL (2007) include Manager and the whole bundle of export, backup etc. tools. | Windows | Proprietary ($250 for full-version) (also Freeware version of Manager with subset of features of full) - check feature matrix | Yes - eidt, table designer (supports full array of data types including array of types), also has database compare/synchronize, backup | PostgreSQL (similar products for MySQL, SQL Server, FireBird/Interbase, Oracle, DB2) | Yes can do views too. Nice toggle feature between query builder and sql view. Supports inner/left/outer/full joins, however adding in GROUP BY in sql view confuses the designer though can make group by changes in designer. Making simple join changes in SQL view or adding columns can toggle back and forth to design view. | Yes | No | Yes - both meta data and data reports | Yes - MS Access, Excel / Open Office spread sheet, CSV, DBF,HTML, XML - ADO data sources | Yes including ability to edit foreign keys and add columns from diagrammer. |
| MicroOlap Database Designer/SQL for PostgreSQL | Windows (can run in Linux under WINE) | Proprietary ($400) | Yes (Designer) | PostgreSQL (similar products for MySQL and SQL Server) | Yes | No | No | Yes -- just meta data/relational designer/reverse engineer reports. | Import structures from ODBC supported dbs | Yes (Designer) - fairly sophisticated and reverse engineering |
| Navicat 8 for PostgreSQL | Windows, Mac OSX, Linux | Proprietary (~$200 with report viewer) and Free Lite version (only for non-proprietary use) | Yes include job agent, backup, report scheduling, DDL, data synch of data and/or structure compare etc. | PostgreSQL (similar product for MySQL, Oracle) | Yes | No | No | Yes -- focused on end user reports with charts and ability to schedule and email reports. | dbf, access, excel, html, xml and 10 other flat file formats | No |
| MS Access 2000-2007 | Windows | Proprietary ($200 or part of MS Office Professional+) | No DDL but can edit data | Any database with an ODBC/OLEDB driver | Yes (pretty good), can't create views | No | Yes, Desktop App and relies on MS Access full or free runtime download. | Yes - has subreports, charting fairly advanced | Yes - from and to any ODBC data source or excel with wizards and ability to save import specs | Yes but will not make any changes to PostgreSQL. It will just look pretty and can print. Also doesn't read relationships from PostgreSQL aside from primary key, but you can draw them in the relationship diagrammer. |
| Once:Radix | Mac OSX/ Windows / Linux | Free (GPL) | No | PostgreSQL | No | No | Yes - webapps based on Java Servlets (Apache/Tomcat) | Yes - uses Jasper Reports | No | No |
| Open Office Base 3.1 | Windows /Mac / Linux / Unix | Free (LGPL) - (also Star Office proprietary) | Yes (can define tables/etc. but sometimes flaky for DDL, fine for editing data) | Any database with an JDBC/SDBC/ODBC driver | Yes (pretty good) - can also design database views graphically, but can't reedit :( | No | Yes - end app needs OpenOffice base to run | Yes can also do more sophisticated reporting if you download Sun Report builder | Yes - via the Paste Special/Paste commands - supports CSV, HTML Table, OOBase/Excel copy pase. The wizard is nice but tempermental when importing into PostgreSQL. We'll write up another article about tricks of getting around its idiosyncracies. | Yes. Will also read Primary/Foreign key constraints and allow you to edit them. Kind of flaky for creating relationships (sometimes creates duplicate foreign keys if you reedit a relationship) but great for looking at them and setting layout. Still seems to lack a print option in this version. We use PgAdmin to create them and OO to look at them. |
| Pentaho BI Suite Enterprise | Windows, Linux | Commercial Open Source (comes in priced and free community editions). You can also download pieces separately like Report Designer. Pricing for commercial is not stated. Licensing is a mix of (GPL/LGPL/Mozilla PL) see feature matrix pg 3 | This is focused on BI, so BI administration, report design, OLAP, report scheduler, dashboards - web based reporting. built on java/tomcat/jetty/mondrian. Seems like a fairly hefty product (600MB download for full), not designed for beginners. Note we did not test it so can't speak for its merits. | While you can report on PostgreSQL data and any source with a JDBC driver, insists on you installing MySQL to store BI metadata so its evidentally very MySQL centric. | ? | ? | ? | Yes - that's what its for. Web-based reporting in category of MS Reporting Services, Cognos,SAP BI Objects | Appears to have Advanced ETL features to pull data from any data source | No |
| PhpPgAdmin 4+ | Windows, Linux, Mac OSX, Unix any webserver supporting PHP (any client with a web browser) | Free (GPL) | Yes DDL builder, user management, data editing in grid view, database object browser | PostgreSQL | Sort of | No | No | Advanced reporting and dashboards. Web-based | CSV,tab, xml | No |
| PgAdmin III 1.10 | Windows, Linux, Mac OSX, Unix | Free Open Source (BSD) | Yes DDL builder, plpgsql debugger as plugin, user management, data editing in grid view, database object browser, job schedule interface to pgAgent | PostgreSQL, EnterpriseDb, GreenPlum | Yes (not great) | Yes (great) | No | Administration specific - Db objects | Limited to CSV. No direct import (but you can use psql/PostgreSQL native COPY | No |
| PostgreSQL Maestro 9.5 | Windows | Proprietary ($300 for the full PostgreSQL bundle, $200 for just Maestro) | Yes DDL builder,pl/pgsql debugger, user management, data editing in grid view with blob edit, database object browser | PostgreSQL (similar products for MySQL, MS SQL Server,Oracle, SQLite, Firebird, Sybase, DB2) | Yes | No | Yes (packaged separately as a free tool - a PHP page generator for query or table) | Yes, includes quick charts | Yes | Yes |
| Power*Architect 0.9 | Java 1.6 (Windows, Linux, Mac OSX,Unix) | Free open source (GPL) | Sort Of (can browse, but no edit/create except via import/export and model generation scripts), ability to compare data models | PostgreSQL, SQL Server, MySQL, Oracle, Derby, DB2, HSQL, SQLStream | No | No | No | Yes but just modeling reports. Ability to export data model to PDF and XML. | Yes - Export - CSV/HTML, Import -- appears to have ability to import and export between database connections, but had trouble getting it to work. | Yes. This is its main focus. Lots of ER features and ability to generate logical data models and generate scripts to make them physical for desired database platform. |
| SQL Workbench/J | Java 6 (JDK 1.6) based - Windows, Linux, Mac OSX, Unix | Free Open Source (License details) Apache 2.0 license | Geared toward querying, editing, and data import/export. Sports a nice SQL formatter, nice SQL field/table code completion, schema diff/data diff, ability to see DDL of all tables, triggers etc and script, and a really nice data pumper. It is also very light-weight with a very sleek clean interface. | PostgreSQL, EnterpriseDb, Oracle, H2, Firebird, Apache Derby, IBM DB2, Ingres, SQLite, MySQL, MS SQL Server though appears can support most JDBC driver based dbs. Have to download JDBC driver separately and specify its location via the driver template interfacee | No -just query editor with data browser/basic data edit (autogenerate of UPDATE/Insert sql) | No | No | No | Yes -XML/txt but also sports an impressive looking Data Pumper that allows you to map fields between two JDBC datasources and copy data across. | No |
| SQuirreL SQL 3.0 | Java 1.6 (Windows, Linux, Mac OSX,Unix) | Free open source (LGPL) | Yes most of the additional features are available via plugins included as options in the install or for free download | PostgreSQL (plug-in based architecture), specific ones also for Firebird, MySQL, MS SQL Server, H2, Oracle, Informix, Sybase, DB2. Although you can check to install the PostgreSQL plugin, you still need to download the jdbc driver from http://jdbc.postgresql.org/download.html and dump in the lib folder of SQuirrel install for the PostgreSQL plugin to become active. Same for other databases. | No | No | No | No | Yes - Import/Export CSV/Excel | Yes via Graph plugin. Renders relationships but doesn't allow adding/editing relationships. |