PetaPoco - PostgreSQL Support and More Improvements

This post is about PetaPoco - "a tiny ORMish thing for your POCOs". Read more on the PetaPoco Project Page.

PostgreSQL Support

I've been in conversation with Rob Sullivan of http://datachomp.com who asked about PostgreSQL support in PetaPoco... another database that I've never used. Once again it was understanding the tools that took the longest in getting it to work with PetaPoco.

Once up and running only a few tweaks were needed to PetaPoco itself to get a full set of green lights on the unit tests. The main problem was related to the Insert method - instead of using @@IDENTITY, it needed to use returning <pkcolumn> as NewID. Thanks Rob for tip!

The T4 templates have also been updated for PostgreSQL, mostly this was a straight copy of the SQL Server schema reading with some different data type mapping.

I've also written up some notes on getting the template to work - details like installing providers into the GAC and registering the providers in machine.config. These notes can be found in the top of the PetaPoco.Core.ttinclude file.

Parameterizing the Paged Requests

I realized last night that PetaPoco's paged requests weren't using parameters for the offset and limit counts - rather it was using string.Format to put literal values straight into the SQL. Not a problem for PetaPoco but it means that server side query plans can't be cached and reused. Aparently DBA's call this "plan cache poisoning" - something I learned from Rob's post here.

Simpler Paged Requests

PetaPoco's Page requests return not just the page of records but also do a SELECT COUNT(*) to get the total number of records - so a page count can be calculated and displayed. Sometime though there might be other easier or better ways to calculate the record count, or perhaps it's just not needed.

So I've added an overloaded version of Fetch that returns a list of records without doing the extra query to get the count.

// Fetch all matching records
List<T> Fetch<T>(string sql, params object[] args);

// New fetch to get just a single page of records
List<T> Fetch<T>(long page, long itemsPerPage, string sql, params object[] args);

// Fetch a page of records and the total record count
Page<T> Page<T>(long page, long itemsPerPage, string sql, params object[] args);

Scope_Identity() vs @@Identity

This is another point raised by Rob. On SQL Server @@Identity can return the primary key identity of records inserted by a trigger instead of the actual record being inserted. So, for SQL Server PetaPoco now uses the Scope_Identity() function to get the correct record ID.

Simplification beats Over-Optimization

In yesterday's somewhat over exuberant optimization I made some changes that I wasn't really happy with. The optimized versions of SingleOrDefault and FirstOrDefault in particular just added code with such a tiny (if any) performance gain that I've dropped them and reverted to simpler implementations.

All this is available in GitHub and NuGet now.

Finally, special thanks must go out to Rob Sullivan for all his suggestions and feedback.