A Pythonic, TTM-inspired interface to PostgreSQL – Requirements

Several moons ago, I started a series of posts about “designing and implementing a generic end user interface for PostgreSQL.” After a while, the series got sidetracked by other issues.

More recently, I have returned to the original endeavor. Partly from reading Database Explorations: Essays on The Third Manifesto and related topics by C.J. Date and Hugh Darwen, I decided to use relational concepts as presented in The Third Manifesto (TTM) in my implementation. This post provides an overview of the requirements.

Limited Scope

The interface is not a full-blown replacement for an object-relational mapper (ORM) (although in theory it could eventually grow in that direction). The interface is intended to assist with two typical needs of a database “admin” application: browsing and CRUD.

Browsing refers to presenting a subset of rows (tuples) of a table (relation variable or relvar) for subsequent editing. The relvar will typically be normalized so it may be necessary to join it to other relvars. Browsing will usually display a limited number of columns (attributes) so relational projection will be needed.

CRUD refers to the ability to create, read, update and delete single tuples in a relvar. The interface should only support relvars with a properly defined, possibly composite primary key.


The user (developer) should have to define only the attributes of each relvar together with the key, and for browsing, the projected attributes plus a JOIN specification if multiple relvars are involved. The definitions should be simple enough so that most of them could be (at a later date) derived automatically from the database catalogs.

From the definitions, the interface should generate all necessary SQL commands to INSERT a single tuple (possibly returning a generated key value), retrieve, UPDATE or DELETE a single tuple using the key, and fetch subsets of projected/joined tuples in a given order.

Optimistic Concurrency Control

The interface should take advantage of PostgreSQL features to implement optimistic locking when handling updates or deletes, as described in a previous series of posts.

Query by Example Support

The interface should facilitate querying of the browsed tuples using something similar to Query-By-Example. For example, when browsing movies if the argument release_year is passed as “>= 1969“, the results should only include films released on that year or later. This feature was not discussed in a post but had been committed to the tutorial repository.


The interface should follow the TTM guidelines when possible. For example, although implemented in Python, assignment to a relvar attribute defined as int should not be allowed if the value is of type str, and duplicate attribute names in a join expression should not be permitted. However, since the interface ought to be usable against existing SQL databases, allowance should be made for certain SQL features such as nullable attributes.

The implementation has been committed to the Pyrseas repository and changes were made to the DBUI tutorial to use the new interface. Subsequent posts will cover the interface in more detail.

9 thoughts on “A Pythonic, TTM-inspired interface to PostgreSQL – Requirements

  1. Looks very much like reinventing the wheel. SQLAlchemy already provides most of this. Eyeballing the implementation I don’t see anything that isn’t already done better in SA, or wouldn’t be trivial to add on with a simple facade. If you don’t want an ORM (sql result to object mapping, change tracking, update statement generation, identity mapping, topological sorting of a graph of objects to insert, etc.), then you can just use the lower layers of connection pooling, schema metadata objects and sql expression building ignoring the ORM completely. From your list of requirements, I see that optimistic concurrency control is not there (though available at the ORM level), and query by example. Both implementations would be around a one or two dozen lines of code. To reuse the YAML definition you would just need to do a simple mapping from pyrseas metadata to SQLAlchemy metadata.

    It really is a great toolkit and a huge amount of work has gone into it. It would be a shame to not build upon that effort.

    • I don’t deny that a lot of work has gone into SA, but I do not use ORMs because I tend to agree with most of the points made by Ken Downs (creator of Andromeda and inspirer of Pyrseas) in his Why I Do Not Use ORM and subsequent posts.

      This interface arose from not wanting to repeat myself when writing CRUD and “admin” type applications. It’s a stepping stone on the way to automatic generation of those kinds of database applications. Even if I was agreeable to ORMs, I don’t need the bells and whistles of an ORM to write the generally simple (and repetitive) SQL needed for such applications.

      • SQLAlchemy architecture does not require you to use it as an ORM. In particular, the ORM is cleanly layered on top of the SQL toolkit layer, allowing you to completely ignore all of the ORM features (or even mix and match if you like). In fact what you have built is in its concepts remarkably similar to SQLAlchemy core. (although obviously a lot simpler) Check the SQLAlchemy core documentation to get an overview of how the toolkit part looks like: http://docs.sqlalchemy.org/en/rel_0_8/core/tutorial.html
        Or this is a great article detailing the reasoning and history behind the architecture: http://www.aosabook.org/en/sqlalchemy.html

        I humbly disagree with your assessment of ORMs, as I have found SQLAlchemy’s variant to boost productivity, result in more readable notation, get out of the way when needed and be helpful of creating abstractions that actually result in better database design. But I have found that these discussions don’t tend to be too productive so I will leave it at a recommendation to just give it a try if you haven’t yet.

      • But why would I bother with SA’s core? For example, what is the advantage of

        users.insert().values(name=’jack’, fullname=’Jack Jones’)

        over the comparable SQL INSERT command delivered directly to Psycopg2 and tailored exactly to my needs?

  2. The point is that you have built the equivalent functionality in Relvar.insert_one(). Except that you don’t support using function calls as values to be inserted (e.g. nextval(), uuid_generate_v4(), do_something_special(‘with value passed in’)), you don’t have insert_many, you don’t support INSERT DEFAULT VALUES. And while SQLAlchemy doesn’t have INSERT SELECT built in, implementing it is literally a dozen lines of code: http://stackoverflow.com/questions/1849375/how-do-i-insert-into-t1-select-from-t2-in-sqlalchemy

    Now insert is just about the simplest example you can have. When you get to querying you may want to worry about sub-queries, group by and order by, possibly over arbitrary expression, unions and intersecting, distinct, selecting for update, custom operators, window functions, using server side cursor to incrementally process a result set and so on and so on. This is all out of the box available with SQLAlchemy. I don’t want to put down your effort, but I just don’t see what do you hope to achieve by essentially reimplementing SQLAlchemy besides the fun of doing it (a noble goal in itself).

    • It does support nextval and default values: that’s what the sysdefault parameter of class Attribute is for. In any case, for my purposes I don’t need subqueries, group by, unions, etc. As I said in the post, this is not intended as an ORM-replacement. And if I needed more complex query features, I would never want to use an intermediary: it would be like learning German to speak through an interpreter to a Spanish person, when I can speak Spanish perfectly well.

  3. Pingback: Attributes in the Pythonic, TTM-inspired interface to PostgreSQL | Taming Serpents and Pachyderms

  4. Pingback: Tuples in the Pythonic, TTM-inspired interface to PostgreSQL | Taming Serpents and Pachyderms

  5. Pingback: ANFSCD: Revisiting the Web Server | Taming Serpents and Pachyderms

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.