pg-entity: A pleasant PostgreSQL layer

This is a package candidate release! Here you can preview how this package release will appear once published to the main package index (which can be accomplished via the 'maintain' link below). Please note that once a package has been published to the main package index it cannot be undone! Please consult the package uploading documentation for more information.

[maintain] [Publish]

A PostgreSQL layer to safely expand your SQL queries with a lightweight eDSL. Read the tutorial at https://tchoutri.github.io/pg-entity/Tutorial


[Skip to Readme]

Properties

Versions 0.0.1.0, 0.0.1.0, 0.0.2.0, 0.0.3.0, 0.0.4.0, 0.0.4.1, 0.0.4.2, 0.0.4.3, 0.0.4.4
Change log CHANGELOG.md
Dependencies base (>=4.12 && <=4.16), bytestring (>=0.10 && <0.11), colourista (>=0.1 && <0.2), exceptions (>=0.10 && <0.11), monad-control (>=1.0 && <1.1), parsec (>=3.1.14.0 && <3.2), pg-transact (>=0.3 && <0.4), postgresql-simple (>=0.6 && <0.7), resource-pool (>=0.2 && <0.3), safe-exceptions (>=0.1 && <0.2), template-haskell (>=2.15.0.0 && <=2.17.0.0), text (>=1.2 && <1.3), text-manipulate (>=0.3 && <0.4), time (>=1.9 && <1.10), uuid (>=1.3 && <1.4), vector (>=0.12 && <0.13) [details]
License MIT
Author Théophile Choutri
Maintainer Théophile Choutri
Category Database
Home page https://tchoutri.github.io/pg-entity
Bug tracker https://github.com/tchoutri/pg-entity/issues
Source repo head: git clone https://github.com/tchoutri/pg-entity
Uploaded by hecate at 2021-11-05T21:11:22Z

Modules

[Index] [Quick Jump]

Downloads

Maintainer's Corner

Package maintainers

For package maintainers and hackage trustees


Readme for pg-entity-0.0.1.0

[back to package description]

PG-Entity CI-badge docs simple-haskell

This library is a pleasant layer on top of postgresql-simple to safely expand the fields of a table when writing SQL queries.
It aims to be a convenient middle-ground between rigid ORMs and hand-rolled SQL query strings. Here is its philosophy:

Its dependency footprint is optimised for my own setups, and as such it makes use of text, vector and pg-transact.

Table of Contents

Installation

At present time, pg-entity is published on Hackage but not on Stackage. To use it in your projects, add it in your cabal file like this:

pg-entity ^>= 0.0

or in your stack.yaml file:

extra-deps:
  - pg-entity-0.0.1.0

The following GHC versions are supported:

Documentation

This library aims to be thoroughly tested, by the means of Oleg Grerus' cabal-docspec and more traditional tests for database roundtrips.

I aim to produce and maintain a decent documentation, therefore do not hesitate to raise an issue if you feel that something is badly explained and should be improved.

You will find the Tutorial here, and you will find below a short showcase of the library.

Usage

The idea is to implement the Entity typeclass for the datatypes that represent your PostgreSQL table.

-- Traditional list & string syntax
{-# LANGUAGE OverloadedLists #-}
{-# LANGUAGE OverloadedStrings #-}
-- Quasi-quoter to construct SQL expressions
{-# LANGUAGE QuasiQuotes #-}
-- Deriving machinery
{-# LANGUAGE GeneralizedNewtypeDeriving #-}
{-# LANGUAGE DeriveAnyClass #-}
{-# LANGUAGE DerivingVia #-}

import Data.UUID (UUID)
import Data.Vector (Vector)
import Database.PostgreSQL.Simple.SqlQQ

import Database.PostgreSQL.Entity

-- This is our Primary Key newtype. It is wrapped in a newtype to make
-- it impossible to mitake with a plain `UUID`, but we still want to
-- benefit from the pre-existing typeclass instances that exist for
-- `UUID`. You can read the last two lines as:
-- > We use the definitions posessed by `UUID` for our own newtype.
newtype JobId = JobId { getJobId :: UUID }
  deriving (Eq, Show, FromField, ToField)
    via UUID

-- A straightforward table definition, which lets us use
-- the DerivingVia mechanism to declare the table name
-- in the `deriving` clause, and infer the fields and primary key.
-- The field names will be converted to snake_case.

data Job
  = Job { jobId    :: JobId
        , lockedAt :: UTCTime
        , jobName  :: Text
        }
  deriving stock (Eq, Generic, Show)
  deriving anyclass (FromRow, ToRow)
  deriving Entity
    via (GenericEntity '[TableName "jobs"] Job)

-- In the above deriving clause, we only had to specify the table name in order to pluralise it,
-- leaving the guessing of the primary key and the table names to the library.

-- Below is a richer table definition that needs some type annotations to help PostgreSQL.
-- We will have to write out the full instance by hand

newtype BagId = BagId { getBagId :: UUID }
  deriving (Eq, Show, FromField, ToField)
    via UUID

-- | This is a PostgreSQL Enum, which needs to be marked as such in SQL type annotations.
data Properties = P1 | P2 | P3
  deriving stock (Eq, Generic, Show)
  deriving anyclass (FromRow, ToRow)

data Bag
  = Bag { bagId      :: BagId
        , someField  :: Vector UUID
        , properties :: Vector Properties
        }
  deriving stock (Eq, Generic, Show)
  deriving anyclass (FromRow, ToRow)


instance Entity Bag where
  tableName  = "bags"
  primaryKey = [field| bag_id |]
  fields     = [ [field| bag_id |]
               , [field| some_field :: uuid[] |]
               , [field| properties :: properties[] |]
               ]

-- You can write specialised functions to remove the noise of Type Applications

insertBag :: Bag -> DBT IO ()
insertBag = insert -- `insert` will be specialised to `Bag`

-- And you can insert raw SQL through postgresql-simple

isJobLocked :: Int -> DBT IO (Only Bool)
isJobLocked jobId = queryOne Select q (Only jobId)
  where q = [sql| SELECT
                    CASE WHEN locked_at IS NULL then false
                         ELSE true
                     END
                   FROM jobs WHERE job_id = ?
            |]

For more examples, see the BlogPost module for the data-type that is used throughout the tests and doctests.

Escape hatches

Safe SQL generation is a complex subject, and it is far from being the objective of this library. The main topic it addresses is listing the fields of a table, which is definitely something easier. This is why every level of this wrapper is fully exposed, so that you can drop down a level at your convience.

It is my personal belief, firmly rooted in experience, that we should not aim to produce statically-checked SQL and have it "verified" by the compiler. The techniques that would allow that in Haskell are still far from being optimised and ergonomic. As such, this library makes no effort to produce semantically valid SQL queries, because one would have to encode the semantics of SQL in the type system (or in a rule engine of some sort), and this is clearly not the kind of things I want to spend my youth on.

Each function is tested for its output with doctests, and the ones that cannot (due to database connections) are tested in the more traditional test-suite.

The conclusion is : Test your DB queries. Test the encoding/decoding. Make roundtrip tests for your data-structures.

Acknowledgements

I wish to thank