TutorialBasic.lhs

> {-# LANGUAGE FlexibleContexts #-}
> {-# LANGUAGE FlexibleInstances #-}
> {-# LANGUAGE MultiParamTypeClasses #-}
> {-# LANGUAGE TemplateHaskell #-}
> {-# LANGUAGE TypeFamilies #-}
> {-# LANGUAGE TypeOperators #-}
> {-# LANGUAGE UndecidableInstances #-}
>
> module TutorialBasic where
>
> import           Prelude hiding (sum)
>
> import           Opaleye (Field, FieldNullable, matchNullable, isNull,
>                          MaybeFields,
>                          Table, table, tableField, selectTable,
>                          Select, (.==), (.<=), (.&&), (.<),
>                          (.===),
>                          (.++), ifThenElse, sqlString, aggregate, groupBy,
>                          count, avg, sum, optional, runSelect,
>                          showSql, where_, Unpackspec,
>                          SqlInt4, SqlInt8, SqlText, SqlDate, SqlFloat8, SqlBool)
>
> import           Data.Profunctor.Product (p2, p3)
> import           Data.Profunctor.Product.Default (Default)
> import           Data.Profunctor.Product.TH (makeAdaptorAndInstanceInferrable)
> import           Data.Time.Calendar (Day)
>
> import qualified Database.PostgreSQL.Simple as PGS

Introduction
============

In this example file I'll give you a brief introduction to the Opaleye
relational query EDSL.  I'll show you how to define tables in Opaleye;
use them to generate selects, joins and filters; use the API of
Opaleye to make your queries more composable; and finally run the
queries on Postgres.

Schema
======

Opaleye assumes that a Postgres database already exists.  Currently
there is no support for creating databases or tables, though these
features may be added later according to demand.

A table is defined with the `table` function.  The syntax is
simple.  You specify the types of the fields, the name of the table
and the names of the fields in the underlying database, and whether
the fields are required or optional.

(Note: This simple syntax is supported by an extra combinator that
describes the shape of the container that you are storing the fields
in.  In the first example we are using a tuple of size 3 and the
combinator is called `p3`.  We'll see examples of others later.)

The `Table` type constructor has two arguments.  The first one tells
us what fields we can write to the table and the second what fields
we can read from the table.  In this document we will always make all
fields required, so the write and read types will be the same.  All
`Table` types will have the same type argument repeated twice.  In the
manipulation tutorial you can see an example of when they might differ.

> personTable :: Table (Field SqlText, Field SqlInt4, Field SqlText)
>                      (Field SqlText, Field SqlInt4, Field SqlText)
> personTable = table "personTable" (p3 ( tableField "name"
>                                       , tableField "age"
>                                       , tableField "address" ))

By default, the table `"personTable"` is looked up in PostgreSQL's
default `"public"` schema. If we wanted to specify a different schema we
could have used the `tableWithSchema` function instead of `table`.

To select all rows of a table we use `selectTable`.

(Here and in a few other places in Opaleye there is some typeclass
magic going on behind the scenes to reduce boilerplate.  However, you
never *have* to use typeclasses.  All the magic that typeclasses do is
also available by explicitly passing in the "typeclass dictionary".
For this example file we will always use the typeclass versions
because they are simpler to read and the typeclass magic is
essentially invisible.)

> personSelect :: Select (Field SqlText, Field SqlInt4, Field SqlText)
> personSelect = selectTable personTable

A `Select` corresponds to an SQL SELECT that we can run.  Here is the
SQL generated for `personSelect`.

ghci> printSql personSelect
SELECT name0_1 as result1,
       age1_1 as result2,
       address2_1 as result3
FROM (SELECT *
      FROM (SELECT name as name0_1,
                   age as age1_1,
                   address as address2_1
            FROM personTable as T1) as T1) as T1

This SQL is functionally equivalent to the following "idealized" SQL.
In this document every example of SQL generated by Opaleye will be
followed by an "idealized" equivalent version.  This will give you
some idea of how readable the SQL generated by Opaleye is.  Eventually
Opaleye should generate SQL closer to the "idealized" version, but
that is an ongoing project.  Since Postgres has a sensible query
optimization engine there should be little difference in performance
between Opaleye's version and the ideal.  Please submit any
differences encountered in practice as an Opaleye bug.

SELECT name,
       age,
       address
FROM personTable

(`printSQL` is just a convenient utility function for the purposes of
this example file.  See below for its definition.)


Record types
------------

Opaleye can use user defined types such as record types in queries.

It will save you a lot of headaches if you define your data types to
be polymorphic in all their fields.  If you want to use concrete types
in particular places, as you almost always will, you can use type
synonyms.  For example:

> data Birthday' a b = Birthday { bdName :: a, bdDay :: b }
> type Birthday = Birthday' String Day
> type BirthdayField = Birthday' (Field SqlText) (Field SqlDate)

To get user defined types to work with the typeclass magic they must
have instances defined for them.  The instances are derivable with
Template Haskell.

> $(makeAdaptorAndInstanceInferrable "pBirthday" ''Birthday')

You don't have to use Template Haskell, but it just saves us writing
things out by hand here.  If you want to avoid Template Haskell see
[Data.Profunctor.Product.TH](https://hackage.haskell.org/package/product-profunctors/docs/Data-Profunctor-Product-TH.html).

Then we can use 'table' to make a table on our record type in exactly
the same way as before.

> birthdayTable :: Table BirthdayField BirthdayField
> birthdayTable = table "birthdayTable"
>                        (pBirthday Birthday { bdName = tableField "name"
>                                            , bdDay  = tableField "birthday" })
>
> birthdaySelect :: Select BirthdayField
> birthdaySelect = selectTable birthdayTable

ghci> printSql birthdaySelect
SELECT name0_1 as result1,
       birthday1_1 as result2
FROM (SELECT *
      FROM (SELECT name as name0_1,
                   birthday as birthday1_1
            FROM birthdayTable as T1) as T1) as T1

Idealized SQL:

SELECT name,
       birthday
FROM birthdayTable


Projection
==========

"Projection" means discarding some of the fields of our select, for
example we might want to discard the "address" field of our
`personSelect`.

Projection gives us our first example of using "do notation" to
write Opaleye queries.

Here we run the `personSelect`, pattern match on the results and return only the
fields we are interested in.

> nameAge :: Select (Field SqlText, Field SqlInt4)
> nameAge = do
>   (name, age, _) <- personSelect
>   pure (name, age)

ghci> printSql nameAge
SELECT name0_1 as result1,
       age1_1 as result2
FROM (SELECT *
      FROM (SELECT name as name0_1,
                   age as age1_1,
                   address as address2_1
            FROM personTable as T1) as T1) as T1

Idealized SQL:

SELECT name,
       age
FROM personTable

Product
=======

"Product" means taking the Cartesian product of two queries.  This is
simple in do notation.  Here we take the product of `personSelect`
and `birthdaySelect`.

> personBirthdayProduct ::
>   Select ((Field SqlText, Field SqlInt4, Field SqlText), BirthdayField)
> personBirthdayProduct = do
>   personRow   <- personSelect
>   birthdayRow <- birthdaySelect
>
>   pure (personRow, birthdayRow)

ghci> printSql personBirthdayProduct
SELECT name0_1 as result1,
       age1_1 as result2,
       address2_1 as result3,
       name0_2 as result4,
       birthday1_2 as result5
FROM (SELECT *
      FROM (SELECT name as name0_1,
                   age as age1_1,
                   address as address2_1
            FROM personTable as T1) as T1,
           (SELECT name as name0_2,
                   birthday as birthday1_2
            FROM birthdayTable as T1) as T2) as T1

Idealized SQL:

SELECT name0,
       age0,
       address0,
       name1,
       birthday1
FROM (SELECT name as name0,
             age as age0,
             address as address0
      FROM personTable as T1),
     (SELECT name as name1,
             birthday as birthday1
      FROM birthdayTable as T1)


Restriction
===========

"Restriction" means restricting the rows of the result of a select to
only those where some condition holds.

We can restrict `personSelect` to the rows where the person is up to 18
years old.

> youngPeople :: Select (Field SqlText, Field SqlInt4, Field SqlText)
> youngPeople = do
>   row@(_, age, _) <- personSelect
>   where_ (age .<= 18)
>
>   pure row

ghci> printSql youngPeople
SELECT name0_1 as result1,
       age1_1 as result2,
       address2_1 as result3
FROM (SELECT *
      FROM (SELECT name as name0_1,
                   age as age1_1,
                   address as address2_1
            FROM personTable as T1) as T1
      WHERE ((age1_1) <= 18)) as T1

Idealized SQL:

SELECT name,
       age,
       address
FROM personTable
WHERE age <= 18


We can use a variety of operators to form more complex restriction
conditions.

> twentiesAtAddress :: Select (Field SqlText, Field SqlInt4, Field SqlText)
> twentiesAtAddress = do
>   row@(_, age, address) <- personSelect
>
>   where_ $ (20 .<= age) .&& (age .< 30)
>   where_ $ address .== sqlString "1 My Street, My Town"
>
>   pure row

ghci> printSql twentiesAtAddress

SELECT name0_1 as result1,
       age1_1 as result2,
       address2_1 as result3
FROM (SELECT *
      FROM (SELECT name as name0_1,
                   age as age1_1,
                   address as address2_1
            FROM personTable as T1) as T1
      WHERE ((address2_1) = '1 My Street, My Town') AND ((20 <= (age1_1))
             AND ((age1_1) < 30))) as T1

Idealized SQL:

SELECT name,
       age,
       address
FROM personTable
WHERE address = '1 My Street, My Town'
AND   20 <= age
AND   age < 30


Inner join
----------

A Product followed by a restriction is sometimes called a "join" or
"inner join" in SQL terminology.  The following select is an example of
such.

> personAndBirthday ::
>   Select (Field SqlText, Field SqlInt4, Field SqlText, Field SqlDate)
> personAndBirthday = do
>   (name, age, address) <- personSelect
>   birthday             <- birthdaySelect
>
>   where_ $ name .== bdName birthday
>
>   pure (name, age, address, bdDay birthday)


ghci> printSql personAndBirthday
SELECT name0_1 as result1,
       age1_1 as result2,
       address2_1 as result3,
       birthday1_2 as result4
FROM (SELECT *
      FROM (SELECT name as name0_1,
                   age as age1_1,
                   address as address2_1
            FROM personTable as T1) as T1,
           (SELECT name as name0_2,
                   birthday as birthday1_2
            FROM birthdayTable as T1) as T2
      WHERE ((name0_1) = (name0_2))) as T1

Idealized SQL:

SELECT name0,
       age0,
       address0,
       birthday1
FROM (SELECT name as name0,
             age as age0,
             address as address0
      FROM personTable as T1),
     (SELECT name as name1,
             birthday as birthday1
      FROM birthdayTable as T1)
WHERE name0 == name1


Nullability
===========

NULLs in SQL have been the source of a lot of complaints, but as
Haskell programmers we know that there is nothing wrong with
nullability as long is it is reflected in the type system.  Nullable
fields are indicated with the `FieldNullable` type constructor.

For example, suppose we have an employee table which records the name
of each employee and the name of their boss.  If their boss is
recorded as NULL then that means they have no boss!

> employeeTable :: Table (Field SqlText, FieldNullable SqlText)
>                        (Field SqlText, FieldNullable SqlText)
> employeeTable = table "employeeTable" (p2 ( tableField "name"
>                                           , tableField "boss" ))

We can write a select that returns as string indicating for each
employee whether they have a boss.

> hasBoss :: Select (Field SqlText)
> hasBoss = do
>   (name, nullableBoss) <- selectTable employeeTable
>
>   let aOrNo = ifThenElse (isNull nullableBoss) (sqlString "no") (sqlString "a")
>
>   pure $ name .++ sqlString " has " .++ aOrNo .++ sqlString " boss"

ghci> printSql hasBoss

SELECT (((name0_1) || ' has ')
       || (CASE WHEN boss1_1 IS NULL THEN 'no' ELSE 'a' END))
       || ' boss' as result1
FROM (SELECT *
      FROM (SELECT name as name0_1,
                   boss as boss1_1
            FROM employeeTable as T1) as T1) as T1

Idealized SQL:

SELECT name || ' has '
            || CASE WHEN boss IS NULL THEN 'no' ELSE 'a' END || ' boss'
FROM employeeTable

But we can do much more than just check for NULL of course.  We can
write a select to produce a string describing each employee's
status along with the name of their boss, if any.  The combinator
`matchNullable` checks whether `nullableBoss` is NULL.  If so it
returns its first argument.  If not it passes the non-NULL value to
the function that is the second argument.

> bossSelect :: (Field SqlText, FieldNullable SqlText) -> Select (Field SqlText)
> bossSelect (name, nullableBoss) = do
>   pure $ matchNullable (name .++ sqlString " has no boss")
>                        (\boss -> sqlString "The boss of " .++ name
>                                  .++ sqlString " is " .++ boss)
>                        nullableBoss

Note that `matchNullable` corresponds to Haskell's

    maybe :: b -> (a -> b) -> Maybe a -> b

and in pure Haskell the same computation could be expressed as

> bossHaskell :: (String, Maybe String) -> String
> bossHaskell (name, nullableBoss) = maybe (name ++ " has no boss")
>                                          (\boss -> "The boss of " ++ name
>                                                    ++ " is " ++ boss)
>                                          nullableBoss

Then we get the following SQL.

ghci> printSql (bossSelect <<< selectTable employeeTable)

SELECT CASE WHEN boss1_1 IS NULL THEN (name0_1) || ' has no boss'
     ELSE (('The boss of ' || (name0_1)) || ' is ') || (boss1_1) END as result1
FROM (SELECT *
      FROM (SELECT name as name0_1,
                   boss as boss1_1
            FROM employeeTable as T1) as T1) as T1

Idealized SQL:

SELECT CASE WHEN boss IS NULL
            THEN name0_1 || ' has no boss'
            ELSE 'The boss of ' || name || ' is ' || boss
            END
FROM employeeTable


Composability
=============

Rewriting `twentiesAtAddress` will allow us to get our first glimpse
of the enormous composability that Opaleye offers.

We can factor out some parts of the 'twentiesAtAddress' select.  For
example we can pull out the restriction to one's age being "in the
twenties" and the restriction to the one's address being "1 My Street,
My Town".

The types are of the form `SelectArr a ()`.  This means that they read
fields of type `a` but do not return any fields.  (Note: `Select` is
just a synonym for `SelectArr ()` which means that it is a `SelectArr`
that does not read any fields.)

> restrictIsTwenties :: Field SqlInt4 -> Select ()
> restrictIsTwenties age = do
>   where_ $ (20 .<= age) .&& (age .< 30)
>
> restrictAddressIs1MyStreet :: Field SqlText -> Select ()
> restrictAddressIs1MyStreet address = do
>   where_ $ address .== sqlString "1 My Street, My Town"

We can't generate "the SQL of" these combinators.  They are not
`Select`s so they don't have any SQL!  (This corresponds to the
observation that in Haskell typically values can be "shown", but
functions cannot be "shown".) Instead we use them to reimplement
`twentiesAtAddress` in a more neatly-factored way.

> twentiesAtAddress' :: Select (Field SqlText, Field SqlInt4, Field SqlText)
> twentiesAtAddress' = do
>   row@(_, age, address) <- personSelect
>
>   restrictIsTwenties age
>   restrictAddressIs1MyStreet address
>
>   pure row

The SQL generated is exactly the same as before

ghci> printSql twentiesAtAddress'
SELECT name0_1 as result1,
       age1_1 as result2,
       address2_1 as result3
FROM (SELECT *
      FROM (SELECT name as name0_1,
                   age as age1_1,
                   address as address2_1
            FROM personTable as T1) as T1
      WHERE ((address2_1) = '1 My Street, My Town') AND ((20 <= (age1_1))
             AND ((age1_1) < 30))) as T1


Composability of joins
----------------------

We can perform a similar transformation for `personAndBirthday` by
pulling out a `SelectArr` which perform the mapping of a person's name
to their date of birth by looking up in `birthdaySelect`.

> birthdayOfPerson :: Field SqlText -> Select (Field SqlDate)
> birthdayOfPerson name = do
>   birthday <- birthdaySelect
>
>   where_ $ name .== bdName birthday
>
>   pure (bdDay birthday)

We can then reimplement `personAndBirthday` as follows

> personAndBirthday' ::
>   Select (Field SqlText, Field SqlInt4, Field SqlText, Field SqlDate)
> personAndBirthday' = do
>   (name, age, address) <- personSelect
>   birthday <- birthdayOfPerson name
>
>   pure (name, age, address, birthday)

and it yields the same SQL as before.

ghci> printSql personAndBirthday'
SELECT name0_1 as result1,
       age1_1 as result2,
       address2_1 as result3,
       birthday1_2 as result4
FROM (SELECT *
      FROM (SELECT name as name0_1,
                   age as age1_1,
                   address as address2_1
            FROM personTable as T1) as T1,
           (SELECT name as name0_2,
                   birthday as birthday1_2
            FROM birthdayTable as T1) as T2
      WHERE ((name0_1) = (name0_2))) as T1



Aggregation
===========

Type safe aggregation is the jewel in the crown of Opaleye.  Even SQL
generating APIs which are otherwise type safe often fall down when it
comes to aggregation.  If you want to find holes in the type system of
an SQL generating language, aggregation is the best place to look!  By
contrast, Opaleye aggregations always generate meaningful SQL.

By way of example, suppose we have a widget table which contains the
style, color, location, quantity and radius of widgets.  We can model
this information with the following datatype.

> data Widget a b c d e = Widget { style    :: a
>                                , color    :: b
>                                , location :: c
>                                , quantity :: d
>                                , radius   :: e }
>
> $(makeAdaptorAndInstanceInferrable "pWidget" ''Widget)

For the purposes of this example the style, color and location will be
strings, but in practice they might have been a different data type.

> widgetTable :: Table (Widget (Field SqlText) (Field SqlText) (Field SqlText)
>                              (Field SqlInt4) (Field SqlFloat8))
>                      (Widget (Field SqlText) (Field SqlText) (Field SqlText)
>                              (Field SqlInt4) (Field SqlFloat8))
> widgetTable = table "widgetTable"
>                      (pWidget Widget { style    = tableField "style"
>                                      , color    = tableField "color"
>                                      , location = tableField "location"
>                                      , quantity = tableField "quantity"
>                                      , radius   = tableField "radius" })


Say we want to group by the style and color of widgets, calculating
how many (possibly duplicated) locations there are, the total number
of such widgets and their average radius.  `aggregateWidgets` shows us
how to do this.

> aggregateWidgets :: Select (Widget (Field SqlText) (Field SqlText) (Field SqlInt8)
>                                   (Field SqlInt4) (Field SqlFloat8))
> aggregateWidgets = aggregate (pWidget Widget { style    = groupBy
>                                              , color    = groupBy
>                                              , location = count
>                                              , quantity = sum
>                                              , radius   = avg })
>                              (selectTable widgetTable)

The generated SQL is

ghci> printSql aggregateWidgets
SELECT result0_2 as result1,
       result1_2 as result2,
       result2_2 as result3,
       result3_2 as result4,
       result4_2 as result5
FROM (SELECT *
      FROM (SELECT style0_1 as result0_2,
                   color1_1 as result1_2,
                   COUNT(location2_1) as result2_2,
                   SUM(quantity3_1) as result3_2,
                   AVG(radius4_1) as result4_2
            FROM (SELECT *
                  FROM (SELECT style as style0_1,
                               color as color1_1,
                               location as location2_1,
                               quantity as quantity3_1,
                               radius as radius4_1
                        FROM widgetTable as T1) as T1) as T1
            GROUP BY style0_1,
                     color1_1) as T1) as T1

Idealized SQL:

SELECT style,
       color,
       COUNT(location),
       SUM(quantity),
       AVG(radius)
FROM widgetTable
GROUP BY style, color

Note: In `widgetTable` and `aggregateWidgets` we see more explicit
uses of our Template Haskell derived code.  We use the 'pWidget'
"adaptor" to specify how fields are aggregated.  Note that this is
yet another example of avoiding a headache by keeping your datatype
fully polymorphic, because the 'count' aggregator changes a 'Field
String' into a 'Field Int64'.

Outer join
==========

Opaleye supports left/right and full outer joins.  A left or right
join is expressed by using `optional`.  For full outer joins see
`Opaleye.Join`.

> personBirthdayLeftJoin :: Select ((Field SqlText, Field SqlInt4, Field SqlText),
>                                  MaybeFields BirthdayField)
> personBirthdayLeftJoin = do
>   personRow@(name, _, _) <- personSelect
>   mBirthdayRow <- optional $ do
>     birthdayRow <- birthdaySelect
>     where_ (name .== bdName birthdayRow)
>     pure birthdayRow
>   pure (personRow, mBirthdayRow)

The generated SQL is

ghci> printSql personBirthdayLeftJoin
SELECT result1_0_3 as result1,
       result1_1_3 as result2,
       result1_2_3 as result3,
       result2_0_3 as result4,
       result2_1_3 as result5
FROM (SELECT *
      FROM (SELECT name0_1 as result1_0_3,
                   age1_1 as result1_1_3,
                   address2_1 as result1_2_3,
                   name0_2 as result2_0_3,
                   birthday1_2 as result2_1_3
            FROM
            (SELECT *
             FROM (SELECT name as name0_1,
                          age as age1_1,
                          address as address2_1
                   FROM personTable as T1) as T1) as T1
            LEFT OUTER JOIN
            (SELECT *
             FROM (SELECT name as name0_2,
                          birthday as birthday1_2
                   FROM birthdayTable as T1) as T1) as T2
            ON
            (name0_1) = (name0_2)) as T1) as T1

Idealized SQL:

SELECT name0,
       age0,
       address0,
       name1,
       birthday1
FROM (SELECT name as name0,
             age as age0,
             address as address0
      FROM personTable) as T1
     LEFT OUTER JOIN
     (SELECT name as name1,
             birthday as birthday1
      FROM birthdayTable) as T1
ON name0 = name1


A comment about type signatures
-------------------------------

We mentioned that Opaleye uses typeclass magic behind the scenes to
avoid boilerplate.  One consequence of this is that the compiler
cannot infer types in some cases. Use of `leftJoin` is one of those
cases.  You will generally need to provide a type signature yourself.
If you see the compiler complain that it cannot determine a `Default`
instance then specify more types.


Newtypes
========

In Haskell, newtypes are a great way of getting additional typesafety.
For example, the ID of a warehouse may be an integer, but instead of
representing it as a naked `Int` we wrap it in a `WarehouseId` newtype
to guard against meaninglessly mixing it with other `Int`s.  We can do
something similar in Opaleye.

For this example, a warehouse has an integer ID, a location, and holds
and integer quantity of goods.

> data Warehouse' a b c = Warehouse { wId       :: a
>                                   , wLocation :: b
>                                   , wNumGoods :: c }
>
> $(makeAdaptorAndInstanceInferrable "pWarehouse" ''Warehouse')

We could represent the integer ID in Opaleye as a `SqlInt4`

> type BadWarehouseField = Warehouse' (Field SqlInt4)
>                                      (Field SqlText)
>                                      (Field SqlInt4)
>
> badWarehouseTable :: Table BadWarehouseField BadWarehouseField
> badWarehouseTable = table "warehouse_table"
>         (pWarehouse Warehouse { wId       = tableField "id"
>                               , wLocation = tableField "location"
>                               , wNumGoods = tableField "num_goods" })

but that would expose us to the following sorts of errors, where we
can meaninglessly relate the warehouse ID with the quantity of goods
it holds.

> badComparison :: BadWarehouseField -> Field SqlBool
> badComparison w = wId w .== wNumGoods w

On the other hand we can make a newtype for the warehouse ID

> newtype WarehouseId' a = WarehouseId a
> $(makeAdaptorAndInstanceInferrable "pWarehouseId" ''WarehouseId')
>
> type WarehouseIdField = WarehouseId' (Field SqlInt4)
>
> type GoodWarehouseField = Warehouse' WarehouseIdField
>                                       (Field SqlText)
>                                       (Field SqlInt4)
>
> goodWarehouseTable :: Table GoodWarehouseField GoodWarehouseField
> goodWarehouseTable = table "warehouse_table"
>         (pWarehouse Warehouse { wId       = pWarehouseId (WarehouseId (tableField "id"))
>                               , wLocation = tableField "location"
>                               , wNumGoods = tableField "num_goods" })

Now the comparison will not pass the type checker

> -- forbiddenComparison :: GoodWarehouseField -> Field SqlBool
> -- forbiddenComparison w = wId w .== wNumGoods w
> --
> -- => Couldn't match type `WarehouseId' (Field SqlInt4)' with `Field SqlInt4'

but we can compare two `WarehouseIdField`s.

> permittedComparison :: GoodWarehouseField
>                     -> GoodWarehouseField
>                     -> Field SqlBool
> permittedComparison w1 w2 = wId w1 .=== wId w2

(Currently we use `.===`, a more polymorphic version of `.==`, but
`.==` may be generalised in the future.)

Running queries on Postgres
===========================


Opaleye provides simple facilities for running queries on Postgres.
`runSelect` is a typeclass polymorphic function that effectively has
the following type

> -- runSelect :: Database.PostgreSQL.Simple.Connection
> --          -> Select fields -> IO [haskells]

It converts a "record" of Opaleye fields to a list of "records" of
Haskell values.  Like `leftJoin` this particular formulation uses
typeclasses so please put type signatures on everything in sight to
minimize the number of confusing error messages!

For example, for the 'twentiesAtAddress' select `runSelect` would have
the following type:

> runTwentiesSelect :: PGS.Connection
>                  -> Select (Field SqlText, Field SqlInt4, Field SqlText)
>                  -> IO [(String, Int, String)]
> runTwentiesSelect = runSelect

Note that nullable fields are indicated with the FieldNullable type
constructor, and these are converted to Maybe when executed.  If we
have a table with a nullable field then FieldNullables turn into
Maybes.  We could run the select `selectTable employeeTable` like this.

> runEmployeesSelect :: PGS.Connection
>                   -> Select (Field SqlText, FieldNullable SqlText)
>                   -> IO [(String, Maybe String)]
> runEmployeesSelect = runSelect

Newtypes are taken care of automatically by the typeclass instance
that was generated by `makeAdaptorAndInstanceInferrable`.  A `WarehouseId'
(Field SqlInt4)` becomes a `WarehouseId' Int` when the select is run.
We could run the select `selectTable goodWarehouseTable` like this.

> type WarehouseId = WarehouseId' Int
> type GoodWarehouse = Warehouse' WarehouseId String Int
>
> runWarehouseSelect :: PGS.Connection
>                   -> Select GoodWarehouseField
>                   -> IO [GoodWarehouse]
> runWarehouseSelect = runSelect


Conclusion
==========

There ends the Opaleye introductions module.  Please send me your questions!

Utilities
=========

This is a little utility function to help with printing generated SQL.

> printSql :: Default Unpackspec a a => Select a -> IO ()
> printSql = putStrLn . maybe "Empty select" id . showSql