Async PostgreSQL with Mojo::Pg

Mojo::Pg - 2014-12-09

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:

1:

my$pg=Mojo::Pg->new('postgresql://partridge')

This simple format still fully supports the configuration of the underlying DBD::Pg driver:

1: 2: 3:

# connect as user 'two' with password 'turtle' to the 'partridge'# database running on port 5432 on the 'dove' servermy$pg2=Mojo::Pg->new('postgresql://two:turtle@doves:5432/partridge?RaiseError=0&PrintError=1');

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.

1: 2: 3: 4: 5: 6: 7: 8: 9: 10: 11: 12: 13: 14: 15: 16:

# Get the dbmy$db=$pg->db;

# Using results as an iteratormy$res=$db->query('SELECT day, gift FROM twelvedays ORDER BY day');while(my$row=$res->hash){say"$row->{day}: $row->{gift}"}

# Using a collection as an array of hashesmy$res=$db->query('SELECT day, gift FROM twelvedays ORDER BY day');foreachmy$row($res->hashes){say"$row->{day}: $row->{gift}"}

# Using a collection as an objectmy$res=$db->query('SELECT day, gift FROM twelvedays ORDER BY day');my$hashes=$res->hashes;say"First gift was: ".$hashes->first->{gift};say"Last gift was: ".$hashes->last->{gift};

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.

Transactions

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.

# return if invoidce_true_love_for_calling_birds returned false, which # means the transaction will automatically be rolled backinvoice_true_love_for_calling_birds()orreturn;

$tx->commit;}

This also comes in very handy in async transactions, as you can very easily handle failure by rolling back.

Migrations

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:

1:

$db->migrations->from_file('migrations.sql')->migrate;

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

ASync

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: