Async PostgreSQL with Mojo::Pg
Over the years, I have worked with many variants of the ORM concept in various libraries and languages. However in the end, I find they often get in the way of writing clear and simple SQL powered web applications more than they are helping. After all, SQL is designed to extract sets of data across tables, not to just be mapped to objects representing rows.
However DBI, the standard Perl database library for executing staight SQL, is rather old and archaic. It is not a very inviting interface to use directly. I have experimented with various wrappers, but never really found one I liked...until now.
This fall, the Mojolicious project launched a new sub-project for accessing PostgreSQL databases: Mojo::Pg. It has powerful features like migrations and Async, but first lets look at the simple case:
Simple SQL Simply
With Mojo::Pg database connections are handled with URLs:
This simple format still fully supports the configuration of the underlying DBD::Pg driver:
The URL format has the advantage of being simple to define in an
%ENV variable for PaaS deployment. In fact it is already supported by Heroku addons.
Mojo::Pg also provides a succinct wrapper around the Statement object for getting data out. You can use the DBI iterator, or you can wrap the entire result into a Mojo::Collection object.
The ability to for Mojo::Pg::Result to return the data structure in collections of whatever you want - hashes, arrays, etc - and Mojo::Collection to allow you to access the data either using standard Perl array operations or via expressive method calls makes writing what would otherwise very tricky with plain old DBI simple with Mojo::Pg.
Mojo::Pg also provides a simple scope guard for transactions, so that if your guard variable goes out of scope before commit is called on it the transaction will automatically be rolled back.
This also comes in very handy in async transactions, as you can very easily handle failure by rolling back.
Keeping databases in sync is always a problem when developing databases. Mojo::Pg has a very straightforward solution. You define migrations in pure SQL, either in the DATA section of your file for simple apps, or a separate file, with each level of migrations separated by an SQL comment. Here is a simple illustration:
This retains all the benefits of systems like DBIx::DeploymentHandler and Rails migrations without any of the complexity.
Bringing your database up to the latest schema version is a single straight forward command:
This will cause Mojo::Pg to examine the
mojo_migrations table (creating it if needed) to work out what version the target database is currently running and then to execute all the statements needed to bring it up to the latest version
Finally, Mojo::Pg allows you to perform async/long poll operations against a PostgreSQL database meaning that your code can do other things while waiting for the database to return instead of blocking.
This works just as you would expect, by passing a callback as the last argument to query. Typically we combine this with Mojo::Delay, to allow better callback control:
The async support uses Mojo::IOLoop under the hood, but Mojo::IOLoop can also interact with AnyEvent through the EV compatibility layer.
Mojo::Pg also supports async waiting for notifications. This is a common pattern for web sockets.
The notification can either be triggered manually from another process also connected to the PostgreSQL database:
Or the notification could be fired by a database trigger (meaning, for example, that Perl code can get an async notification whenever a table is updated.)
I hope this has got you excited about the possibilities of Mojo::Pg. It's still early days for this library, but it has already got a rather unique feature set.
If you want to learn more about the Mojolicious stack, stay tuned for an exciting announcement coming soon from the Mojolicious team.