Home

Motivations

The primary client interface for PostgreSQL is psql. It is fairly simple to use, with useful database schema exploration facilities. However, there are some tasks where a GUI can provide a smoother experience. Of course, there are some GUI client applications that work with PostgreSQL too, but few are even satisfactory as a replacement for psql.

So, pqwx aims to be a GUI tool for access PostgreSQL databases, palatable by people who actually quite like psql. To do this for me, it needs a fairly simple interface in which I can write and execute SQL, but also providing GUI facilities to do things better where possible. Although I'm happy on the command line, I've also spent a lot of time recently sitting in front of SQL Server's various client applications, and found them to be actually quite usable. So I have devised some requirements based on what I've found useful and annoying there, and also reviewing some alternative GUI clients for PostgreSQL:

• Script/SQL editor windows immediately accessible, where one window is one database connection.
• Script compatibility with psql (where reasonable). In general, if I can run it with psql, I want to be able to load it into pqwx and run it there too. Specifically:
  • COPY xxx FROM STDIN should work
  • psql backslash commands should be handled (where possible) or ignored (where necessary)
    • Execution of scripts should mix \echo arguments with SQL response tags, similar to psql
    • Commands like \g or \go to send the current query have obvious translations
    • Commands like \o, \i or \copy need consideration to work them into a GUI environment. If outputting to a file, should this pop up a save box? Some way of outputting multiple files to one directory for that "generate a bunch of reports" script?
    • \c foo should DTRT to change to database "foo" on the same server. Similarly \c foo - foohost should connect to "foo" on "foohost" and DTRT about requesting user credentials.
    • \d and friends should probably be ignored, or just jump the user into GUI-implemented search functions
  • Loading and executing pg_dump script output should work
• Loading up a 20Mb script and executing it shouldn't kill the client machine
• Executing a query that returns 10e6 rows shouldn't kill the client machine, nor the server. e.g. select * from pg_class c1, pg_class c2
• Load script file, mark region, hit F5- executed. It should be that simple.
• The current status of the connection (idle, in transaction, in error, waiting for copy data etc.) should be clear at all times
• All database interaction done off the GUI thread- slow servers shouldn't stop the application from repainting (can't emphasise this enough)
• Some sort of schema exploration widget that enables quickly understanding what is present in a database:
  • Somehow balance not overloading overloading the user with the entire contents, but also not requiring 20 clicks just to determine that actually, this database is exactly like template1.
  • Be PostgreSQL-smart about what's important and what's not.
    • Hide things like sequences that are owned by columns by default.
    • Functions returning trigger are distinct from normal functions (although may be shared by multiple triggers)
    • All the stuff in pg_catalog and information_schema is important to people who, for example, write database client applications, but should be shunted out of the way for most people. This goes double for the pg_temp_* and pg_toast_temp_* namespaces.
    • Objects owned by extensions should be separated out
  • Include essentially everything that can be queried using the \d commands
  • Have some interface like Eclipse's "find type" box that allows you to enter partial name matches to jump to tables, e.g. ven_ful_r_t for venue_fulfillment_rule_type (or even VenFulRT).
    • This facility could accept plain old wildcards too (ven*ful*rule*)
• Loading an archive produced by pg_dump should present some reasonable interface for picking items from the TOC and restoring them to a new/existing database. This should actually drive the pg_restore tool to extract the TOC and run the restore.
• Similarly, as far as possible, emulate producing a TOC for a connected database and drive pg_dump to dump the selected items. (This would actually be a nice feature in pg_dump that Istr depesz suggested)
• Present the explain output from the server in some friendly graph display: use the JSON/XML output from servers since 9.0, or write something to make a best-effort parse of the plain text output.
  • Ideally have a way of producing the non-analyze plan for any statement, and maybe pop up a dbox asking for options for the analyze version. Allow for multiple plans to be produced in one execution?
• A good editor.
  • Writing one from scratch would seem to be a really bad idea. At a pinch, some system where the script isn't edited directly in pqwx but rather in "auto-revert" mode so you can window-swap between pqwx and your real editor.
  • SQL syntax highlighting
  • some sort of auto-completion (but not too eager)
  • some sort of reformatter to tidy SQL statements and update them to the user's favourite conventions:
    • indentation
    • casing
    • table aliasing: mandatory, as required
    • quoting: mandatory, as required
• cross-platform (Linux & friends, Windows, Mac)
• licenced as PostgreSQL itself

Inspirations

PostgreSQL tools:

• PgAdmin

Generic tools supporting PostgreSQL:

• squirrel-sql
• DatabaseSpy
• RazorSql
• Navicat

Other database clients:

• SQL Server mgmt studio
• TOAD

Other applications to raid:

• Eclipse