User Interfaces for Databases

Pyrseas was started to improve on Andromeda. In addition to the schema version control I’ve been discussing in these posts, Andromeda provides two other capabilities: automations and web application programming.

Andromeda’s web programming allows you to generate a database application with very little actual programming, since Andromeda takes into account the primary key, foreign key and other information present in the YAML specification to generate much of the application code for you. This is another area that I’d like to explore for implementation in Pyrseas.

Administrative Applications

Nearly any database needs a user interface to add, retrieve, update and delete records, known by the unattractive acronym CRUD. There are a number of admin apps that provide such generic facilties. For example, for PostgreSQL we have pgAdmin which offers a GUI, and phpPgAdmin and Adminer which offer PHP web interfaces. These have a couple of limitations.

Admin apps almost always operate on a single table at a time. Interfaces for end users often require interacting with or viewing two or more tables on a single screen or page, e.g., customers with their invoices, accounts with their transactions.

By definition, admin apps are “raw” and cater to the needs of DBAs and programmers. After retrieving a customer record searching by name, the admin user can update any column, including the primary key and other columns that end users would normally be prevented from updating.

Django Admin

Django includes an appealing admin site. With a moderate dose of programming, you can develop an application suitable for end users. However, you’re at the mercy of Django’s constraints on “modeling” a database. Take for example the film_category table I used in my second post on version control:

CREATE TABLE film_category (
    film_id INTEGER NOT NULL REFERENCES film (id),
    category_id INTEGER NOT NULL
        REFERENCES category (category_id),
    PRIMARY KEY (film_id, category_id)

Django cannot use this table “as is” because it insists in having a single column as the primary key. So you end up adding an unnecessary “id” column together with a SEQUENCE (and needless to say, you incur the wrath of the SSSKA).

We could turn to SQLAlchemy, a Python-based ORM that understands that a primary key –albeit singular– can encompass more than one column, and some other web framework or even Django itself, and attempt to recreate the nice Django admin app. However, aside from the additional effort that would represent, we wouldn’t be taking advantage of the YAML database specification we can now store in our VCS.

This is a very broad, and some may say, ambitious topic, but as I did with version control, in future posts I’ll survey existing “art” and progress towards designing and implementing a generic end user interface for PostgreSQL. Reader feedback will be much appreciated.


Interesting database tool: Adminer

No, it’s not a tool to extract extra revenue from Google Ads. Adminer (which I guess is pronounced admin-er, rather than ad-minor) is a nifty little tool that competes with phpMyAdmin, the web-browser-based MySQL administration tool. What is interesting from my perspective is that it’s been so successful that it now also supports PostgreSQL –as well as SQLite, Oracle and SQL Server (so it also competes with phpPgAdmin).

Nifty, in the sense of “quick, agile,” is an apt description for Adminer: adminer-3.1.0.php weighs in at only 292kB and that single file is all you have to download and install. If you prefer an English-only version it’s just 188kB (there are also MySQL-only versions that are smaller yet).  It installs quickly, provided you have Apache and PHP already installed, and its responsiveness is admirable.

But perhaps what I found most impressive was its ability to display the database schema.  Here is Adminer showing the schema for the “movies” database I’ve been using in recent posts:

Its MySQL roots are visible in some places, e.g., in the table display below, the SERIAL column language_id is shown as Auto increment:

Overall, a very responsive, easy-to-use tool for administering a database.

Hat tip: Selena Marie.