XML in Postgres – The Game Changer

About the time that Postgres 8.3 came out, Adobe decided to open-source something called Flex. Up to that point, I had a non-committal relationship with Postgres. Meaning that I didn’t have a real strong preference between Postgres vs MySQL, and I didn’t participate in either community. I had heard some really good things about Adobe Flex, but I never had much interest in it until it was open-sourced. But when the two came together… it was like the Reese’s Penut Butter Cup commercials from the 80’s or 90’s. Or the KY his and her’s commercials of late… but maybe not quite that good 🙂

The point is, it was a game changer. Once you have peanut butter with chocolate, you don’t go back to putting peanut butter on celery. So why this watershed moment? Ok, you cheated and looked at the title. With Postgres 8.3 came the ability to read and write XML right from the database. And with Adobe Flex, we (finally) had a web client that could take that XML and make it wonderfully easy to work with.

All web browsers can work with XML on some level. They have to in order to render the html. But try doing client side XSLT that will work on all platforms and all browsers. It is neither fun nor easy. But Flex uses E4X (ECMAScript for XML) which is the best paradigm I’ve seen yet for working with XML. It is pretty similar to the SimpleXML functionality in PHP.

So lets take a round trip with our data. Our data starts off in regular relational tables. (Try to resist the temptation to store your relational data in XML.) For simple stuff, table_to_xml and query_to_xml make it extremely easy to convert your data to XML. If you have more complex needs, you can build pretty much anything using building blocks like XMLELEMENT, XMLAGG, XMLATTRIBUTES, etc. Postgres’ xml documentation can be found here.

We pass that XML back to the middle tier. And the great thing is, there is nothing to do here.  Because both the client and the database now “speak” the same language, there is no need to translate in the middle tier.

When Flex gets the XML, you can bi-directionally bind it to form and datagrid controls. The datagrid control supports bi-di binding out of the box, for other form controls you can extend them to support it or add a <binding/> tag. But whatever the method, the user can directly modify the XML we got from the database; adding, deleting and changing nodes or even reordering using drag and drop. When the user is done making changes, they hit save and the XML is sent back to the middle tier.

The middle tier checks authentication and permissions then passes the XML along to the database. Again, not much work being done here. No translating between form fields and database fields and no building queries out of strings and variables. It just passes along the XML to a stored procedure on the database.

The database does another permissions check. Then it shreds the XML back into relational data. The process, which we’ve taken to calling End-to-End XML, is a great simplification over the traditional web development approach. In a typical LAMP style application, the majority of the code is on the middle tier. It does all the work translating client requests into queries, binding data, iterating over result sets and building HTML forms and tables.  Almost no programming is done on the database.  As proven by the fact that the most popular database for this stack didn’t have views, stored procedures or transactions for a long time. And the mantra I’ve heard from the Ruby on Rails folks is that the database is largely irrelevant. Its just a place for persistence.

But with End-to-End XML, all of your data centric code is right there on the database. Exactly where it should be. (But given that I’m an Oracle developer by trade, I may be a bit biased.) And again, all of our client side code is on the client side.

The loose coupling has also made it easier to port applications between Oracle and Postgres backends. Flex doesn’t care where it gets the XML from only that it has the same structure. And both Oracle and Postgres follow the SQL/XML spec fairly closely. So the code between the two looks almost identical. Well with the inclusion of some Postgres helper functions that I’ll highlight in another post.

So there you have it. The story of how two open-source products combined to made my coding life much easier and more enjoyable.  And as for MySQL, I’m all to glad to leave it in my past, along with iterating over record-sets to create HTML rows and cells; spending all day coding a really cool Javascript function only to find that it is totally busted on Internet Explorer; or trying to get CSS to look the same in one browser as it does in another.

And as for Postgres, not only is it far superior to any other open-source db out there (and most commercial one’s I’ve worked with); but they’ve got a super community. They’ve got quite a few mailing lists. But the general mailing list is where end users like myself can go and get help or advice. And the core developers are never too busy or too high and mighty to stop by and answer any question you may have.

Here are the slides from the End-to-End XML talk I gave at the PostgreSQL conference.