With 19 new releases, postgresql-simple has seen substantial development since my announcement of version 0.3.1 nearly a year ago. Compared to my last update, the changes are almost exclusively bugfixes and new features. Little code should break as a result of these changes, and most if not all of the code that does break should fail at compile time.
As it stands today, postgresql-simple certainly has the best support for postgres-specific functionality of anything on hackage. So, for a few highlights:
Thanks to contributions from Tobias Florek, perhaps the most exciting change for many is that postgresql-simple now properly supports parametrized identifiers, including column, table, and type names. These are properly escaped via libpq. So for example, you can now write:
execute conn "DROP TABLE IF EXISTS ? CASCADE" (Only ("schema.table" :: QualifiedIdentifier))
Of course, this particular example is still potentially very insecure, but it shouldn’t be possible to create a SQL injection vulnerability this way.
The downside of this change is that postgresql-simple now requires libpq version 9.0 or later. However, community support for 8.4 is coming to and end this July. Also, it is possible to use newer versions of libpq to connect to older versions of postgres, so you don’t have to upgrade your server. (In fact, in one particular situation I’m still using postgresql-simple to connect to postgresql 8.1, although some features such as non-builtin types don’t work.)
Copy In and Copy Out support
While the postgresql-libpq binding has supported
COPY FROM STDIN and
COPY TO STDOUT for some time, postgresql-simple now supports these directly without having to muck around with postgresql-libpq calls via the Internal module.
If you are interested in streaming data to and from postgres, you may also be interested in higher-level COPY bindings for pipes and io-streams. This is available in Oliver Charles’s pipes-postgresql-simple, which also supports cursors, and my own unreleased postgresql-simple-streams, which also supports cursors and large objects.
Out-of-box support for JSON, UUID, and Scientific
Savepoints and nestable folds
Thanks to contributions from Joey Adams, postgresql-simple now has higher-level support for savepoints in the Transaction module, as well as the ability to nest the fold operator. Previously, postgresql-simple had assigned a static name to the cursor that underlies
fold, and now every connection has a counter used to generate temporary names.
Parametrized VALUES expressions
For example, these situations commonly arise when dealing with writable common table expressions. Let’s say we have a table of things with an associated table of attributes:
CREATE TABLE things ( id SERIAL NOT NULL, name TEXT NOT NULL, PRIMARY KEY (id) ); CREATE TABLE attributes ( id INT NOT NULL REFERENCES things, key TEXT NOT NULL, value BIGINT NOT NULL );
Then we can populate both a thing and its attributes with a single query, returning the
id generated by the database:
query conn [sql| WITH thing AS ( INSERT INTO things (name) VALUES (?) RETURNING id ), newattrs AS ( INSERT INTO attributes SELECT thing.id, a.* FROM thing JOIN ? a ) SELECT id FROM thing; |] ("bob", Values [ "text" ,"int8" ] [ ( "foo" , 42 ) , ( "bar" , 60 ) ])
The empty case is also dealt with correctly; see the documentation for details.
Improved support for outer joins
A long standing challenge with postgresql-simple is dealing with outer joins in a sane way. For example, with the schema above, let’s say we want to fetch all the things along with their attributes, whether or not any given thing has any attributes at all. Previously, we could write:
getAllTheThings :: Connection -> IO [(Text, Maybe Text, Maybe Int64)] getAllTheThings conn = do query conn [sql| SELECT name, key, value FROM things LEFT OUTER JOIN attributes ON things.id = attributes.id |]
Now, the columns from the attributes table are not nullable, so normally we could avoid the
Maybe constructors, however the outer join changes that. Since both of these columns are not nullable, they are always both null or both not null, which is an invariant not captured by the type. And a separate Maybe for each column gets awkward to deal with, especially when more columns are involved.
What we would really like to do is change the type signature to:
getAllTheThings :: Connection -> IO [Only Text :. Maybe (Text, Int64)]
And now we can, and it will just work! Well, almost. The caveat is that there is a separate
instance FromRow (Maybe ...) for most of the provided FromRow instances. This won’t work with your own
FromRow instances unless you also declare a second instance. What’s really desired is a generic instance:
instance FromRow a => FromRow (Maybe a)
This instance would return
Nothing if all the columns that would normally be consumed are null, and attempt a full conversion otherwise. This would reduce code bloat and repetition, and improve polymorphism and compositionality.
But alas, it’s not possible to define such an instance without changing the
FromRow interface, and quite probably breaking everybody’s
FromRow instances. Which I’m totally willing to do, once somebody comes up with a way to do it.