postgresql-typed: PostgreSQL interface with compile-time SQL type checking, optional HDBC backend

[ bsd3, database, library ] [ Propose Tags ]

Automatically type-check SQL statements at compile time. Uses Template Haskell and the raw PostgreSQL protocol to describe SQL statements at compile time and provide appropriate type marshalling for both parameters and results. Allows not only syntax verification of your SQL but also full type safety between your SQL and Haskell. Supports many built-in PostgreSQL types already, including arrays and ranges, and can be easily extended in user code to support any other types.

Also includes an optional HDBC backend that, since it uses the raw PostgreSQL protocol, may be more efficient than the normal libpq backend in some cases (though provides no more type safety than HDBC-postgresql when used without templates).

Originally based on Chris Forno's templatepg library.


[Skip to Readme]

Flags

Automatic Flags
NameDescriptionDefault
md5

Enable md5 password authentication method.

Enabled
binary

Use binary protocol encoding via postgresql-binary. This may put additional restrictions on supported PostgreSQL server versions.

Enabled
text

Support Text string values via text (implied by binary).

Enabled
uuid

Support the UUID type via uuid (implied by binary).

Enabled
scientific

Support decoding numeric via scientific (implied by binary).

Enabled
aeson

Support decoding json via aeson.

Enabled
hdbc

Provide an HDBC driver backend using the raw PostgreSQL protocol.

Enabled
tls

Enable TLS (SSL) support in PostgreSQL server connections.

Enabled
crypton

Use crypton rather than cryptonite.

Enabled

Use -f <flag> to enable a flag, or -f -<flag> to disable that flag. More info

Downloads

Maintainer's Corner

Package maintainers

For package maintainers and hackage trustees

Candidates

Versions [RSS] 0.3.0, 0.3.1, 0.3.2, 0.3.3, 0.4.0, 0.4.1, 0.4.2, 0.4.2.1, 0.4.2.2, 0.4.3, 0.4.4, 0.4.5, 0.5.0, 0.5.1, 0.5.2, 0.5.3.0, 0.6, 0.6.0.1, 0.6.1.0, 0.6.1.1, 0.6.1.2, 0.6.2.0, 0.6.2.1, 0.6.2.2, 0.6.2.3, 0.6.2.4, 0.6.2.5
Dependencies aeson (>=0.7 && <2.3), array, attoparsec (>=0.12 && <0.15), base (>=4.8 && <5), binary, bytestring (>=0.10.2), containers, crypton, crypton-x509, crypton-x509-store, crypton-x509-validation, cryptonite (>=0.5), data-default, haskell-src-meta, HDBC (>=2.2), memory (>=0.5), network, old-locale, postgresql-binary (>=0.8), scientific (>=0.3), template-haskell, text (>=1), time, tls, utf8-string, uuid (>=1.3), x509, x509-store, x509-validation [details]
License BSD-3-Clause
Copyright 2010-2013 Chris Forno, 2014-2019 Dylan Simon
Author Dylan Simon
Maintainer Dylan Simon <dylan-pgtyped@dylex.net>
Category Database
Home page https://github.com/dylex/postgresql-typed
Bug tracker https://github.com/dylex/postgresql-typed/issues
Source repo head: git clone git://github.com/dylex/postgresql-typed
Uploaded by DylanSimon at 2023-11-09T23:50:15Z
Distributions LTSHaskell:0.6.2.5, NixOS:0.6.2.5, Stackage:0.6.2.5
Reverse Dependencies 5 direct, 2 indirect [details]
Downloads 15046 total (40 in the last 30 days)
Rating 1.25 (votes: 1) [estimated by Bayesian average]
Your Rating
  • λ
  • λ
  • λ
Status Docs uploaded by user
Build status unknown [no reports yet]

Readme for postgresql-typed-0.6.2.5

[back to package description]

Haskell PostgreSQL-typed

A Haskell PostgreSQL interface that provides type-safety through compile-time (template Haskell) database access. See the Haddock documentation in Database.PostgreSQL.Typed or the test cases for simple examples.

Getting started

Installation

Use your preferred package manager to install or add to your package dependencies:

  • stack install postgresql-typed or
  • cabal install postgresql-typed

You'll also likely need to add network as a dependency.

Enable ghc extensions

Make sure you enable TemplateHaskell, QuasiQuotes, and DataKinds language extensions, either in your cabal default-extensions or in a {-# LANGUAGE TemplateHaskell, QuasiQuotes, DataKinds #-} pragma in your source.

Setup compile-time database connection

Either set the following environment variables:

  • TPG_DB the database name to use (default: same as user)
  • TPG_USER the username to connect as (default: $USER or postgres)
  • TPG_PASS the password to use (default: empty)
  • TPG_HOST the host to connect to (default: localhost)
  • TPG_PORT or TPG_SOCK the port number or local socket path to connect on (default port: 5432)

Or in your code call Database.PostgreSQL.Typed.useTPGDatabase with a database config as a top-level quote in each code file where you have SQL queries. It's often helpful to make your own utility function to do this:

-- |Call this at top-level at the beginning of every file (rather than 'useTPGDatabase')
useMyTPGConfig :: Language.Haskell.TH.DecsQ
useMyTPGConfig = useTPGDatabase PGDatabase{ ... } -- or load config from file

Setup your database schema

Your tables and other schema need to be created in your development (compile-time) database before you compile your code. No queries will actually be executed, so there does not need to be any data, but it will do query parsing with the database (prepare queries) so any referenced objects must exist.

Setup run-time database connection

Use pgConnect to connect to your database using a PGDatabase configuration. The run-time database does not need to be the same as the build-time database (though it can be), but it must have the same schema. It's recommended to use bracket (pgConnect PGDatabase{..}) pgDisconnect. If you need a pool of connections, consider resource-pool (while PGConnections are mostly thread-safe, they can't be used for multiple queries simultaneously).

Complete example

schema.sql:

CREATE TABLE thing (id SERIAL PRIMARY KEY, name TEXT NOT NULL);

DBConfig.hs:

{-# LANGUAGE OverloadedStrings #-}
module DBConfig where

import qualified Database.PostgreSQL.Typed as PG
import           Network.Socket (SockAddr(SockAddrUnix))

myPGDatabase :: PG.PGDatabase
myPGDatabase = PG.defaultPGDatabase
  { PG.pgDBAddr = if tcp then Left ("localhost", "5432") else Right (SockAddrUnix "/run/postgresql/.s.PGSQL.5432")
  , PG.pgDBUser = "user"
  , PG.pgDBName = "db"
  } where tcp = False

Main.hs:

{-# LANGUAGE DataKinds #-}
{-# LANGUAGE QuasiQuotes #-}
{-# LANGUAGE TemplateHaskell #-}

import           Control.Exception (bracket)
import           Control.Monad (void, unless)
import           Data.Int (Int32)
import           Data.Maybe (listToMaybe)
import qualified Database.PostgreSQL.Typed as PG

import DBConfig

PG.useTPGDatabase myPGDatabase

data Thing = Thing Int32 String
  deriving (Eq)

createThing :: PG.PGConnection -> Thing -> IO ()
createThing pg (Thing tid tname) =
  void $ PG.pgExecute pg [PG.pgSQL|INSERT INTO thing (id, name) VALUES (${tid}, ${tname})|]

lookupThing :: PG.PGConnection -> Int32 -> IO (Maybe Thing)
lookupThing pg tid = fmap (uncurry Thing) . listToMaybe <$>
  PG.pgQuery pg [PG.pgSQL|SELECT id, name FROM thing WHERE id = ${tid}|]

main = bracket (PG.pgConnect myPGDatabase) PG.pgDisconnect $ \pg -> do
  let myt = Thing 1 "cat"
  createThing pg myt
  t <- lookupThing pg 1
  unless (t == Just myt) $ fail "wrong thing!"