Basic Tutorial

personTable :: Table (Column PGText, Column PGInt4, Column PGText)
                     (Column PGText, Column PGInt4, Column PGText)
personTable = table "personTable" (p3 ( tableColumn "name"
                                      , tableColumn "age"
                                      , tableColumn "address" ))

personQuery :: Query (Column PGText, Column PGInt4, Column PGText)
personQuery = queryTable personTable

Define a Table

• First type argument is read columns
• Second type argument is write columns
• A lot of typeclass magic

data Birthday' a b = Birthday { bdName :: a, bdDay :: b }
type Birthday = Birthday' String Day
type BirthdayColumn = Birthday' (Column PGText) (Column PGDate)

makeAdaptorAndInstance "pBirthday" ''Birthday'

birthdayTable :: Table BirthdayColumn BirthdayColumn
birthdayTable = table "birthdayTable"
                       (pBirthday Birthday { bdName = tableColumn "name"
                                           , bdDay  = tableColumn "birthday" })

birthdayQuery :: Query BirthdayColumn
birthdayQuery = queryTable birthdayTable

Create Record Types

• Records should be polymorphic in all fields
• Records should use type synonyms
• Use Template Haskell to get typeclass magic
  • Will automagically convert to/from Opaleye "Column" syntax
  • Conversion semantics not here but will show examples after

-- projection
nameAge :: Query (Column PGText, Column PGInt4)
nameAge = proc () -> do
  (name, age, _) <- personQuery -< ()
  returnA -< (name, age)

-- product
personBirthdayProduct ::
  Query ((Column PGText, Column PGInt4, Column PGText), BirthdayColumn)
personBirthdayProduct = proc () -> do
  personRow   <- personQuery -< ()
  birthdayRow <- birthdayQuery -< ()
  returnA -< (personRow, birthdayRow)

-- restriction
youngPeople :: Query (Column PGText, Column PGInt4, Column PGText)
youngPeople = proc () -> do
  row@(_, age, _) <- personQuery -< ()
  restrict -< age .<= 18
  returnA -< row

Write Queries with Arrows

personAndBirthday ::
  Query (Column PGText, Column PGInt4, Column PGText, Column PGDate)
personAndBirthday = proc () -> do
  (name, age, address) <- personQuery -< ()
  birthday             <- birthdayQuery -< ()
  restrict -< name .== bdName birthday
  returnA -< (name, age, address, bdDay birthday)

Joins

restrictIsTwenties :: QueryArr (Column PGInt4) ()
restrictIsTwenties = proc age -> do
  restrict -< (20 .<= age) .&& (age .< 30)

restrictAddressIs1MyStreet :: QueryArr (Column PGText) ()
restrictAddressIs1MyStreet = proc address -> do
  restrict -< address .== pgString "1 My Street, My Town"

twentiesAtAddress' :: Query (Column PGText, Column PGInt4, Column PGText)
twentiesAtAddress' = proc () -> do
  row@(_, age, address) <- personQuery -< ()
  restrictIsTwenties -< age
  restrictAddressIs1MyStreet -< address
  returnA -< row

Composability

• data QueryArr  a b
• type Query = QueryArr ()
• "QueryArr a b is analogous to a Haskell function a -> [b]"

Database Mutation

Different Write and Read Columns

myTable :: Table
    (Maybe (Column PGInt4), Column PGFloat8, Column PGFloat8, Column P.PGText)
    (Column PGInt4, Column PGFloat8, Column PGFloat8, Column P.PGText)
myTable = table "tablename" (p4 ( tableColumn "id"
                                , tableColumn "x"
                                , tableColumn "y"
                                , tableColumn "s" ))

• The id column is Maybe for autoincrementing insertions
  • Can use Maybe for database triggers

Writing

delete :: Connection -> IO Int64 -- number of rows
delete conn = runDelete conn myTable (\(_, x, y, _) -> x .< y)

-- can also use `runInsertManyReturning` if you want the return value
insertNothing :: Connection -> IO Int64 -- number of rows
insertNothing conn = runInsertMany conn myTable [(Nothing, 2, 3, P.pgString "Hello")]

-- can also use `runUpdateReturning`
updateSomething :: Connection -> IO Int64 -- number of rows
updateSomething conn =
  runUpdate conn myTable functionToRunOnEachSelectedRow functionToSelectRowsToUpdate