Database.TemplatePG

Introduction

TemplatePG is designed with 2 goals in mind: safety and performance. The primary focus is on safety.

To help ensure safety, it uses the PostgreSQL server to parse every query and statement in your code to infer types at compile-time. This means that in theory you cannot get a syntax error at runtime. Getting proper types at compile time has the nice side-effect that it eliminates run-time type casting and usually results in less code. This approach was inspired by MetaHDBC (http://haskell.org/haskellwiki/MetaHDBC) and PG'OCaml (http://pgocaml.berlios.de/).

While compile-time query analysis eliminates many errors, it doesn't eliminate all of them. If you modify the database without recompilation or have an error in a trigger or function, for example, you can still trigger a PGException.

With that in mind, TemplatePG currently does a number of unsafe things. It doesn't properly close the connection with the PostgreSQL server. It doesn't handle unexpected messages from the server very gracefully, and it's not entirely safe when working with nullable result fields. I hope to fix all of these at some point in the future. In the meantime, use the software at your own risk. Note however that TemplatePG is currently powering http://www.vocabulink.com/ with no problems yet. (For usage examples, you can see the Vocabulink source code at http://jekor.com/vocabulink/vocabulink.tar.gz).

To improve performance, TemplatePG does not use prepared statements. In theory, this saves bandwidth (and a potential round-trip) and time for the extra step of binding parameters. Again in theory, this is also safe because we know the types of parameters at compile time. However, it still feels risky (and I would appreciate any audit of the code doing this, especially escapeString).

Usage

queryTuples does all the work (queryTuple and execute are convenience functions).

It's a Template Haskell function, so you need to splice it into your program with $(). It requires a Handle to a PostgreSQL server, but can't be given one at compile-time, so you need to pass it after the splice:

h <- pgConnect ...

tuples <- $(queryTuples "SELECT * FROM pg_database") h

To pass parameters to a query, include them in the string with {}. Most Haskell expressions should work. For example:

let owner = 33

tuples <- $(queryTuples "SELECT * FROM pg_database WHERE datdba = {owner} LIMIT {2 * 3}") h

Note that parameters may only be used where PostgreSQL will allow them. This will not work:

tuples <- $(queryTuples "SELECT * FROM {tableName}") h

And in general, you cannot construct queries at run-time, since they wouldn't be available to be analyzed at compile time.

Compile-Time Parameters

TemplatePG needs information about the database to connect to at compile time (in the form of environment variables).

You must set at least TPG_DB:

TPG_DB

the database name to use

TPG_USER

the username to connect as (default: postgres)

TPG_PASS

the password to use (default: empty)

TPG_HOST

the host to connect to (default: localhost)

TPG_PORT

the port number to connect on (default: 5432)

You can set TPG_DEBUG to get a rough protocol-level trace (pipe to hexdump).

Caveats

TemplatePG assumes that it has a UTF-8 connection to a UTF-8 database.

TemplatePG does not bind parameters with prepared statements (at run-time), instead it relies on its own type conversion and string escaping. The technique might have a security vulnerability. You should also set standard_conforming_strings = on in your postgresql.conf.

I've included withTransaction, rollback, and insertIgnore, but they've not been thoroughly tested, so use them at your own risk.

Limitations and Workarounds

A Note About NULL

Sometimes TemplatePG cannot determine whether or not a result field can potentially be NULL. In those cases it will assume that it can. Basically, any time a result field is not immediately tracable to an originating table and column (such as when a function is applied to a result column), it's assumed to be nullable and will be returned as a Maybe value.

Additionally, you cannot directly use NULL values in parameters. As a workaround, you might have to use 2 or more separate queries (and DEFAULT NULL) to INSERT rows with NULLs.

Nullability is indicated incorrectly in the case of outer joins. TemplatePG incorrectly infers that a field cannot be NULL when it's able to trace the result field back to a non-NULL table column. As a workround, you can wrap columns with COALESCE() to force them to be returned as Maybe values.

Other Workarounds

There's no support for reading time intervals yet. As a workaround, you can use extract(epoch from ...)::int to get the interval as a number of seconds.

data PGException

PGException is thrown upon encountering an ErrorResponse with severity of ERROR, FATAL, or PANIC. It holds the SQLSTATE and message of the error.

Constructors

PGException String String

Instances

Show PGException

Typeable PGException

Exception PGException

pgConnect

Arguments

:: HostName

the host to connect to

-> PortID

the port to connect on

-> String

the database to connect to

-> String

the username to connect as

-> String

the password to connect with

-> IO Handle

a handle to communicate with the PostgreSQL server on

Connect to a PostgreSQL server.

pgDisconnect

Arguments

:: Handle

a handle from pgConnect

-> IO ()

Disconnect from a PostgreSQL server. Note that this currently doesn't send a close message.

queryTuples :: String -> Q Exp

queryTuples :: String -> (Handle -> IO [(column1, column2, ...)])

Query a PostgreSQL server and return the results as a list of tuples.

Example (where h is a handle from pgConnect):

$(queryTuples "SELECT usesysid, usename FROM pg_user") h

=> IO [(Maybe String, Maybe Integer)]

queryTuple :: String -> Q Exp

queryTuple :: String -> (Handle -> IO (Maybe (column1, column2, ...)))

Convenience function to query a PostgreSQL server and return the first result as a tuple. If the query produces no results, return Nothing.

Example (where h is a handle from pgConnect):

let sysid = 10::Integer;

$(queryTuple "SELECT usesysid, usename FROM pg_user WHERE usesysid = {sysid}") h

=> IO (Maybe (Maybe String, Maybe Integer))

execute :: String -> Q Exp

execute :: String -> (Handle -> IO ())

Convenience function to execute a statement on the PostgreSQL server.

Example (where h is a handle from pgConnect):

let rolename = "BOfH"

$(execute "CREATE ROLE {rolename}") h

=> IO ()

withTransaction :: Handle -> IO a -> IO a

Run a sequence of IO actions (presumably SQL statements) wrapped in a transaction. Unfortunately you're restricted to using this in the IO Monad for now due to the use of onException. I'm debating adding a MonadPeelIO version. Untested.

rollback :: Handle -> IO ()

Roll back a transaction. Untested.

insertIgnore :: IO () -> IO ()

Run an INSERT statement, ignoring duplicate key errors. This is also limited to the IO Monad. Untested.