Frater 219's Journal: Developing a Web app with mod_python and PostgreSQL

Over the past few months I have been developing a Web interface to allow host operators at my workplace to request the opening of ports on the firewall. Requests are stored in a relational database; authorized personnel can mark them approved, or download new firewall configuration files based on the approved requests.

It's not really that complex of a project, but I am very glad that I chose the tools that I did to create it: specifically, the Apache Web server, the Python language, and the PostgreSQL database backend. All three have proven exceedingly pleasant and flexible to work with, and showcase one of the practical strengths of open-source software: these products from different organizations and with widely divergent data models and philosophies integrate seamlessly to allow me to get my work done.

Apache and mod_python

Apache provides a powerful yet fast Web front-end, and supports authentication against my workplace LDAP directory, meaning that my own code does not have to think about authentication at all -- all I do is read a username from the Apache request object. The mod_python interpreter (an Apache module that embeds Python into Apache, with dramatically lower overhead and better integration than CGI offers) seamlessly interfaces my Python application logic to the Apache front-end.

Python itself is fun to work with, flexible, and exceptionally easy to debug. There are three different modules for connecting Python to my database backend, each optimized for a different sort of task. All conform to the same API, though, so I can switch from one to another with minimal code changes. The fact that Python (unlike Perl or PHP) hosts an interactive interpreter allows me to test chunks of my code quickly -- and Python's accurate and informative error messages make my own foul-ups easy to find and quick to fix.

I will admit that Python is not the fastest interpreter around. Speed isn't one of my chief requirements, though, and the tradeoff between programmer time and CPU time becomes easier to make on fast systems. (Terrible, I know.) If I were to set out aggressively optimizing this system now, the first thing I would do would be to push various bits of processing into PL/pgSQL rather than Python, so as to cut down on the amount of time spent converting data formats. (I must admit, though, I haven't profiled it.)

Though I come from a Perl background, and though processing of entered text was a major part of this project, I never missed Perl's regular-expression processing in writing my Python application code. I didn't even use Python's regex capabilities: I found myself designing data formats (mostly in dynamic HTML forms) which would require minimal parsing easily accomplished with basic string operations.

PostgreSQL

I don't have a great deal of background in the world of relational databases, but I knew that I needed a stronger database back-end than Berkeley DB or GNU DBM for this task. Specifically, I needed to ensure that no front-end application session could violate specific data integrity rules for the stored host information, and that erroneous sessions could be rolled back cleanly.

These requirements led me to SQL databases, and soon to PostgreSQL. I'd heard of its more popular counterpart mySQL, of course -- but what I'd heard was from database experts pointing out its wrong behavior, limitations, and its advocates' lies and misstatements with respect to its capabilities. I also knew of several Web sites, such as E2, which in the past had serious database corruption problems with mySQL. These put a bad taste in my mouth regarding the more popular system, and I turned to its less well-known, but evidently more professionally designed, competitor.

PostgreSQL was as pleasant to work with as the other tools I've mentioned. I installed it on a Debian GNU/Linux system, which did the install with no problems. (I'd thought about building it from source, but decided against it on maintainability grounds -- I like to assume that I'm not going to be the last person to work on my project, and so I want to ensure that it is as uncomplicated to upgrade or otherwise maintain as is practical.)

I got some help from a database specialist in defining my tables and data integrity rules. By ensuring in the DBMS that no data could be entered that would violate these rules, I cut out what could have been a major source of ongoing bugs in my application code -- and of user errors. Twice during testing, data integrity exceptions alerted me to errors in my code or logic -- if I'd failed to define foreign key constraints (or used a DBMS that didn't implement them), those errors might have gone into production and allowed malformed data entry. I don't write very buggy code, but I don't believe anyone who says they don't make errors -- so I have great difficulty believing those who say they don't need data integrity checks in the DBMS.

In conclusion ...

I would strongly recommend that anyone developing database-driven Web applications look into these technologies. For me, they have helped make what looked at the start like a complicated project into something relatively painless. More importantly, their solid performance and reliability has given me the confidence that the application I have written will continue to perform reliably and correctly.