June 8, 2006

Glom, the point-and-click database tool

Author: Nathan Willis

I am by no means a database wizard. I know a bit of SQL, but primarily my interaction with databases is secondhand, through either PHP or desktop applications that work on top of MySQL or PostgreSQL. But like a lot of you, I occasionally need to build a small database for my own private record-keeping -- and for that, I recommend Glom.

Glom is a GNOME application that provides a graphical front end to PostgreSQL database creation and maintenance. The developers claim the interface is inspired loosely by Filemaker, for what that may be worth to veterans of proprietary operating systems. I have no experience with Filemaker, but Glom still seems intuitive to use.

You can download both binary and source packages from the Glom Web site. The dependencies are listed on the Download page; primarily you need Python and a working, running PostgreSQL installation. Once you've installed Glom, however, you still need to follow the instructions on the Initial Postgres Configuration page so that Glom can connect. The steps you need to take include both creating one or more database users and making sure that PostgreSQL is set up to properly allow username/password connections via TCP/IP. Whether you need to take the latter step may vary depending on your setup; my Ubuntu Breezy machine needed to be tweaked from the default configuration. The project provides instructions for both Postgres 7.4 and Postgres 8.1.

Getting started

The first time you launch Glom, a dialog box will ask you if you want to create a new database, open an existing one, or use a built-in example. I recommend trying out the example database first, to get a feel for what Glom can do. The example is a "small business" database, with tables for customer contacts, employees, inventory, and sales invoices.

You can populate any or all of the tables with your own data. Once you've added some records, go up to the Mode menu and switch from "Data" to "Find." Here you can enter information in any field, click the Find button, and Glom will pull together a list of matching records. Like everything else about Glom, it is straightforward and it works.

If you populate the Contacts and Invoices tables, you can make use of one of Glom's other built-in features: reports. The Reports menu holds a list of defined reports for the active table. The examples are relatively simple -- sorting the Contacts table by country and town, for example.

Selecting a report from the menu generates an HTML document opened in your default Web browser. The fields included in the report and their order are defined inside Glom; you can alter them by switching from "Operator" to "Developer" in the User Level menu. When you make this switch, the Edit Reports item in the Reports menu becomes selectable. Use it to open up one of the example reports and re-order the fields. You can also add aggregate statistics for the report as a whole.

Notice that you can re-order elements from within the Edit Reports window, but you do not have absolute control over how items are placed; this is because Glom handles the layout for you automatically, in the interest of simplicity. The same is true of the layout and grouping of fields in the individual records. You can re-order them with the Layout item under the Developer menu, but Glom handles auto-alignment and spacing for you. Some power users may find this irritating (after all, "best fit" is a subjective idea); I take it as a reminder that Glom is not intended to be a full-fledged database power tool, but rather an easy-to-use, point-and-click data manager.

Beyond examples

To make use of Glom for your own data instead of the examples, start a new database from the File menu. Setting up tables and fields is a simple matter of switching to Developer mode and opening the Edit Tables window.

Glom does not implement every data type PostgreSQL is capable of handling; it is limited to text, numeric, boolean, date, time, and images. Similarly, although it relies on PostgreSQL, Glom does not implement all of the relational features that the database back end supports. The result is that other, more fully-featured database tools can open databases Glom creates, but Glom cannot open or edit databases created by other applications.

Glom is not all good news. For one thing, Glom is still buggy at some important tasks. Periodically it crashes when creating a new database or editing a report. That alone is not good, but more troubling is that in the event of a crash, sometimes it succeeds at the create or edit task, and sometimes it does not. Were it a completely self-contained tool that behavior would be excusable, but one advantage of connecting to a real transactional database server is that you stand to gain from features like atomicity. I would like to see more work in that area; you cannot write a program without bugs, but predictability is a step in the right direction.

My only other real complaint is that Glom's interface tends toward the whack-a-mole school of usability design; almost every menu item pops up in a new window of its own, sometimes creating a chain of intermediary parent windows to get to the one you need. That creates two problems: visual clutter on the desktop, and confusion in the mind of the user. It is easy to forget which window is the child of which other window, leading to accidentally closing the wrong one or having to search through open windows to see if the one you want is already on screen.

In most respects, thought, Glom succeeds admirably at being a simple, point-and-click database app. Database design itself is a complex problem -- as Philip Greenspun put it, the hard part is choosing the right data model; the easy part is implementing it. As such, when you've moved beyond what a simple spreadsheet can do for you, you are probably ready to tackle a desktop database application. Glom is a good one with which to start.

Click Here!