Database Administration, Reporting, and Light application development

Monday, September 07. 2009

Database Administration, Reporting, and Light application development

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.

Posted by Leo Hsu and Regina Obe in beginner, db2, Dbase, firebird, informix, ms access, mysql, oobase, oracle, other dbms, pgadmin, product showcase, sql server, sqlite at 01:54 | Comments (21) | Trackback (1)

Trackbacks

Trackback specific URI for this entry

Importing data into PostgreSQL using Open Office Base 3.2
A while ago we demonstrated how to use Open Office Base to connect to a PostgreSQL server using both the native PostgreSQL SBC and the PostgreSQL JDBC driver. The routine for doing the same in Open Office Base 3.2 is pretty much the same as it was in the

Weblog: Postgres OnLine Journal
Tracked: Jun 28, 02:31

Comments

Display comments as (Linear | Threaded)

One of my favorite tools is Aqua Datastudio. It supports all of the databases I use on a daily basis; runs on Mac, Linux and Window; logs all of your queries; and is fairly feature complete. If it did code completion for Oracle packages, it would be the only tool I'd use.

http://www.aquafold.com/

#1 Scott Bailey on 2009-09-02 21:01

Database Master is my favorit. It is a awesome tool and awesome user friendly. I can visualize relations, import csv,sql files. But most important is editing and reporting data (inluding blob fields).

Great Tool and thank you for this great product ;-)
http://www.nucleonsoftware.com

#1.1 John on 2009-09-04 17:09

Nice writeup!

One tool that I use a lot is DbVisualizer. It supports several database engines using JDBC and runs on Linux, Mac, Unix and Windows. There is a free version and the paid for version is reasonably priced.

http://www.dbvis.com

#2 Greg Siems on 2009-09-02 22:51

Don't forget the huge Java ecosystem of Open Source database and Business Intelligence tools that work over JDBC so database compatibility is excellent. Just a few examples:

SquirrelSQL, a SQL browser and frontend:
http://squirrel-sql.sourceforge.net/

Power*Architect for database design (includes forward and reverse engineering), with special support for special database dialects including Postgres:
http://www.sqlpower.ca/page/architect

Everything from Pentaho (e.g. data integration, reporting, OLAP):
http://community.pentaho.com/

Those are the ones I use on a daily basis. I seldom need other tools.

#3 Balázs Bárány on 2009-09-03 01:00

Thanks all for the feedback. We'll add these to the list.

#4 Leo on 2009-09-03 01:31

FYI - pgAdmin III includes an excellent PL/pgSQL debugger.

#5 Anonymous on 2009-09-03 08:13

I just linked your great guide into the less well maintained page covering this on the PG wiki: http://wiki.postgresql.org/wiki/Community_Guide_to_PostgreSQL_GUI_Tools

#6 Greg Smith on 2009-09-03 17:36

Thanks Greg

#6.1 Leo on 2009-09-03 22:48

You might want to consider SQL Workbench/J as well, which has strong export/import capabilities, schema diff, data diff and can be used in batch files as well.

#7 Thomas (Homepage) on 2009-09-03 17:51

Thomas,

Thanks for the heads up. We've added to our list. I really love the light-weight slick interface, sql formatter and the data pump. I'll have to play with the data pump some more since its something I can forsee using a lot and I really love the way you have implemented the screen. So far been impressed.

#7.1 Regina on 2009-09-07 01:59

Any chance you could change "commercial," to "proprietary" where appropriate? FLOSS has been used in commerce for some decades now.

#8 David Fetter (Homepage) on 2009-09-03 19:08

Done

#8.1 Regina on 2009-09-03 22:03

You missed out Servoy. http://www.servoy.com - (It's proprietary, but not sure on latest prices - should be on the website somewhere.)

I use it regularly as a RAD environment (it's simple enough that I can use it) and it works well with Postgres.

#9 John on 2009-11-13 04:46

Many IDE's also provide full service SQL connections using driver as well. Currently I am using NetBeans for development and NetBeans to connect to PostGres. Provides nice graphical output, and editing. The kicker for me- code completion! (the thing I've been missing most from my Toad days). For serious DBA stuff you will still need something else, but for casual edits, minor schema changes and queries, it's nice to have.

#10 Danny on 2010-01-10 04:48

For table structure/data comparison Zidsoft CompareData http://www.zidsoft.com is a performance, ODBC-based database comparison tools that supports PostgreSQL (free for comparing table/view data).

#11 faridz (Homepage) on 2010-09-16 18:43

For diffing two different schemas and generating upgrade SQL statements, there is application called Another PostgreSQL Diff Tool.

It is already quite advanced, all the details about the application can be found at its homepage at http://apgdiff.startnet.biz/

I think it might be good addition to the list of applications.

#12 Miroslav (Homepage) on 2010-10-04 05:42

I think Another PostgreSQL Diff Tool (also known as apgdiff, homepage http://apgdiff.startnet.biz/) could be added on the list too. It makes PostgreSQL database schema upgrades much easier.

#13 Miroslav (Homepage) on 2010-10-06 19:59

Im use WaveaMaker with PosgreSQL and have nice web app and complex too...tryit www.wavemaker.com a nice RAD also can build your own datamodel like Posgres pgadmin client,...

#14 Manuel Rodriguez Coria (Homepage) on 2010-10-09 01:17

dbSchema is the best!

Basically has everything I need and the best ER modeler in my opinion.

#15 prince on 2011-07-03 12:58

Also would like to mention Datanamic's postgresql data and schema comparer. More info at: http://www.datanamic.com/schemadiff-for-postgresql/index.html

#16 rick (Homepage) on 2011-09-23 11:00

DtSQL is also a good universal database tool to support a lot of databases including PostgreSQL. It could be found at http://www.dtsql.com

#17 John Smith (Homepage) on 2012-08-03 14:19

Add Comment

Name
Email
Homepage
In reply to
Comment	E-Mail addresses will not be displayed and will only be used for E-Mail notifications. To prevent automated Bots from commentspamming, please enter the string you see in the image below in the appropriate input box. Your comment will only be submitted if the strings match. Please ensure that your browser supports and accepts cookies, or your comment cannot be verified correctly. Enter the string from the spam-prevention image above:
	Remember Information? Subscribe to this entry

Database Administration, Reporting, and Light application development

Postgres OnLine Journal

PostGIS in Action About the Authors Consulting

Monday, September 07. 2009

Database Administration, Reporting, and Light application development

Entry's Links

Quicksearch

Calendar

Categories

Archives

Subscribe

Blog Administration

Database Administration, Reporting, and Light application development

Postgres OnLine Journal PostGIS in Action About the Authors Consulting

Monday, September 07. 2009

Database Administration, Reporting, and Light application development

Entry's Links

Quicksearch

Calendar

Categories

Archives

Subscribe

Blog Administration

Postgres OnLine Journal

PostGIS in Action About the Authors Consulting