Using Plans

You’ll need a Haskell project named using-plans for this tutorial. The setup is identical to the setup in Getting Started aside from the package name, so we’ll avoid explaining it again here.

This example shows Plans. A Plan contains one or more SQL select statements, and when the first is run, its result is passed into the next.

The goal of plans is to prevent n+1 queries, while still allowing a plan for a single item to be modified to execute against many items.

The code contains a detailed description, so this document will just focus on examples.

This example has a table with students, and a table with classes they can take.

Because multiple students can be enrolled in multiple classes, it is a many-to-many relationship, which can be modelled with a separate table with two foreign keys on it.

These tables are defined in a manner similar to previous tutorials. Note the foreign keys on the student_class table. Begin your src/Main.hs like this:

src/Main.hs : haskell 
{-# LANGUAGE QualifiedDo #-}
module Main
  ( main
  ) where

import qualified Orville.PostgreSQL as O
import qualified Orville.PostgreSQL.AutoMigration as AutoMigration
import qualified Orville.PostgreSQL.Plan as Plan
import qualified Orville.PostgreSQL.Plan.Syntax as PlanSyntax

import           Data.List (sort)
import           Data.List.NonEmpty (NonEmpty((:|)))
import qualified Data.Int as Int
import qualified Data.Text as T

-------------
-- Student --
-------------

type StudentId = Int.Int32
type StudentName = T.Text
type StudentAge = Int.Int32

data Student = Student
  { studentId :: StudentId
  , studentName :: StudentName
  , studentAge :: StudentAge
  }
  deriving Show

studentIdField :: O.FieldDefinition O.NotNull StudentId
studentIdField =
  O.integerField "id"

studentNameField :: O.FieldDefinition O.NotNull StudentName
studentNameField =
  O.unboundedTextField "name"

studentAgeField :: O.FieldDefinition O.NotNull StudentAge
studentAgeField =
  O.integerField "age"

studentMarshaller :: O.SqlMarshaller Student Student
studentMarshaller =
  Student
    <$> O.marshallField studentId studentIdField
    <*> O.marshallField studentName studentNameField
    <*> O.marshallField studentAge studentAgeField

studentTable :: O.TableDefinition (O.HasKey StudentId) Student Student
studentTable =
  O.mkTableDefinition "plan_demo_student" (O.primaryKey studentIdField) studentMarshaller

----------------------------
-- Student to Class Table --
----------------------------

studentClassIdField :: O.FieldDefinition O.NotNull Int.Int32
studentClassIdField =
  O.integerField "id"

studentClassClassIdField :: O.FieldDefinition O.NotNull Int.Int32
studentClassClassIdField =
  O.integerField "class_id"

studentClassStudentIdField :: O.FieldDefinition O.NotNull Int.Int32
studentClassStudentIdField =
  O.integerField "student_id"

data StudentClass = StudentClass
  { studentClassId :: Int.Int32
  , studentClassClassId :: Int.Int32
  , studentClassStudentId :: Int.Int32
  }

studentClassMarshaller :: O.SqlMarshaller StudentClass StudentClass
studentClassMarshaller =
  StudentClass
    <$> O.marshallField studentClassId studentClassIdField
    <*> O.marshallField studentClassClassId studentClassClassIdField
    <*> O.marshallField studentClassStudentId studentClassStudentIdField

studentClassTable :: O.TableDefinition (O.HasKey Int.Int32) StudentClass StudentClass
studentClassTable =
  O.addTableConstraints
    [ O.foreignKeyConstraint (O.tableIdentifier classTable) $
        O.foreignReference (O.fieldName studentClassClassIdField) (O.fieldName classIdField) :| []
    , O.foreignKeyConstraint (O.tableIdentifier studentTable) $
        O.foreignReference (O.fieldName studentClassStudentIdField) (O.fieldName studentIdField) :| []
    ]
  $ O.mkTableDefinition "plan_demo_student_class" (O.primaryKey studentClassIdField) studentClassMarshaller

-----------
-- Class --
-----------

classIdField :: O.FieldDefinition O.NotNull Int.Int32
classIdField =
  O.integerField "id"

classSubjectField :: O.FieldDefinition O.NotNull T.Text
classSubjectField =
  O.unboundedTextField "subject"

data Class = Class
  { classId :: Int.Int32
  , classSubject :: T.Text
  }
  deriving (Show, Eq, Ord)

classMarshaller :: O.SqlMarshaller Class Class
classMarshaller =
  Class
    <$> O.marshallField classId classIdField
    <*> O.marshallField classSubject classSubjectField

classTable :: O.TableDefinition (O.HasKey Int.Int32) Class Class
classTable =
  O.mkTableDefinition "plan_demo_class" (O.primaryKey classIdField) classMarshaller

The three tables are declared, we’ll proceed to the main function, which ensures some sample data is available, and then queries it in different ways.

Since we have foreign key constraints, we have to create and delete the data in an order that doesn’t violate these constraints. Orville will throw a Haskell runtime exception upon execution of an SQL statement that violates a constraint.

src/Main.hs : haskell 
main :: IO ()
main = do
  pool <-
    O.createConnectionPool
        O.ConnectionOptions
          { O.connectionString = "host=localhost user=postgres password=postgres"
          , O.connectionNoticeReporting = O.DisableNoticeReporting
          , O.connectionPoolStripes = O.OneStripePerCapability
          , O.connectionPoolLingerTime = 10
          , O.connectionPoolMaxConnections = O.MaxConnectionsPerStripe 1
          }

  O.runOrville pool $ do
    AutoMigration.autoMigrateSchema AutoMigration.defaultOptions [AutoMigration.SchemaTable studentTable, AutoMigration.SchemaTable classTable, AutoMigration.SchemaTable studentClassTable]
    _ <- O.deleteEntity studentClassTable 0
    _ <- O.deleteEntity studentClassTable 1
    _ <- O.deleteEntity studentClassTable 2
    _ <- O.deleteEntity classTable 0
    _ <- O.deleteEntity classTable 1
    _ <- O.deleteEntity classTable 2
    _ <- O.deleteEntity studentTable 0
    _ <- O.deleteEntity studentTable 1
    _ <- O.insertEntity studentTable Student { studentId = 0, studentName = T.pack "Name", studentAge = 91 }
    _ <- O.insertEntity studentTable Student { studentId = 1, studentName = T.pack "Other Name", studentAge = 42 }
    _ <- O.insertEntity classTable Class { classId = 0, classSubject = T.pack "Painting" }
    _ <- O.insertEntity classTable Class { classId = 1, classSubject = T.pack "Cooking" }
    _ <- O.insertEntity classTable Class { classId = 2, classSubject = T.pack "Swimming" }
    _ <- O.insertEntity studentClassTable $ StudentClass {studentClassId=0, studentClassClassId=0, studentClassStudentId=0}
    _ <- O.insertEntity studentClassTable $ StudentClass {studentClassId=1, studentClassClassId=2, studentClassStudentId=0}
    _ <- O.insertEntity studentClassTable $ StudentClass {studentClassId=2, studentClassClassId=1, studentClassStudentId=1}
    pure ()

Now that the data is available, let’s first show off a simple plan which is already more powerful than findEntity. findMaybeOne takes the table, and the field to filter on, whereas findEntity only works using the primary key.

The plan returned by findMaybeOne is like a parameterized query: it doesn’t yet contain the actual value to filter for.

But upon execution, that value has to be provided, and it has to match the type of the Plan. execute is in MonadOrville just like findEntity, so from here on out, it is familiar territory.

src/Main.hs : haskell 
  print =<< O.runOrville pool (Plan.execute (Plan.findMaybeOne studentTable studentIdField) 0)

Just to demonstrate that you can filter on other fields too, let’s search for a specific student using their name. Note that Orville doesn’t have any type-level checks for whether there is an index on the field.

src/Main.hs : haskell 
  print =<< O.runOrville pool (Plan.execute (Plan.findMaybeOne studentTable studentNameField) (T.pack "Other Name"))

A plan that takes a single argument and returns a single result can be passed into planList to make it work on multiple values. Note how execute now takes a list instead of just a single value.

src/Main.hs : haskell 
  print =<< O.runOrville pool (Plan.execute (Plan.planList (Plan.findMaybeOne studentTable studentNameField)) [T.pack "Other Name", T.pack "Name"])

Remember that a plan is just a list of SQL statements. This can be used in a way similar to how JOIN is used in SQL.

The function chain is used to chain these statements together. After the first statement is executed and its result has been sent to the client, Orville allows for manipulating the value with focusParam before using it in a step.

For simplicity, we’ll use findOne here which is like findMaybeOne, but throws an exception when it fails to find something. In practice, make sure to only use findOne when there are constraints or there is otherwise certainty that a row exists.

The following plan will: 1. find a student, given a name. Since this is the first step of the plan, the name is the input of the plan. 1. using the ID of the student, find a row in student_class that matches on the studentClassStudentIdField. Note that Orville doesn’t check that the fields are actually comparable, or that it makes sense to compare them! 1. using the ID of the StudentClass, find a row in class that matches on the class_id.

src/Main.hs : haskell 
  (print =<<) . O.runOrville pool $ Plan.execute
    (              Plan.findOne studentTable studentNameField
      `Plan.chain` Plan.focusParam studentId (Plan.findOne studentClassTable studentClassStudentIdField)
      `Plan.chain` Plan.focusParam studentClassClassId (Plan.findOne classTable classIdField)
    )
    (T.pack "Other Name")

Expanding on the previous example, let’s find all the names of the classes they attend, instead of just one. This also lets us avoid unsafe uses of findOne. Unlike the previous version, whose middle findOne fails when a student doesn’t attend any classes, this new version doesn’t throw an exception in that case. Because of the foreign key constraint, the final findOne can’t fail, so we keep it.

Let’s extract the Student -> [Class] part to its own Plan, which we’ll then use for the final expression. Note how the following definition is similar to the last two lines of the plan above.

src/Main.hs : haskell 
  let
    studentToClassesPlan :: Plan.Plan scope Student [Class]
    studentToClassesPlan =
                   Plan.focusParam studentId (Plan.findAll studentClassTable studentClassStudentIdField)
      `Plan.chain` Plan.planList (Plan.focusParam studentClassClassId $ Plan.findOne classTable classIdField)

Remember that plans solve the n+1 problem by scaling easily from a single item to multiple items. This means that while studentToClassesPlan executes as two SQL statements, planList studentToClassesPlan, a version that works on multiple elements, also executes as two SQL statements.

You can verify this using explain, which shows the generated SQL. Note how both of these lists have two SQL statements in them:

src/Main.hs : haskell 
  print $ Plan.explain studentToClassesPlan
  print $ Plan.explain (Plan.planList studentToClassesPlan)

With that in mind, let’s use studentToClassesPlan with findAll and planList to get a list of classes for each matching student.

(We sort the inner lists below so that the result is deterministic.)

src/Main.hs : haskell 
  (print =<<) . fmap (fmap sort) . O.runOrville pool $ Plan.execute
    ( Plan.findAll studentTable studentNameField
      `Plan.chain` Plan.planList studentToClassesPlan
    )
    (T.pack "Name")

Running a sub-plan only once

A plan doesn’t notice when the same sub-plan appears in it more than once. Every occurrence runs its own query. For example, this plan fetches a student together with their age, but findStudentByName appears twice, once for each side of <*>:

src/Main.hs : haskell 
  let
    findStudentByName :: Plan.Plan scope StudentName Student
    findStudentByName =
      Plan.findOne studentTable studentNameField

    studentAndAgeRepeated :: Plan.Plan scope StudentName (Student, StudentAge)
    studentAndAgeRepeated =
      (,)
        <$> findStudentByName
        <*> (studentAge <$> findStudentByName)
  print ("repeated", Plan.explain studentAndAgeRepeated)

explain lists the student query twice, so the plan runs two SQL statements where one would do. There are several ways to run the student query only once.

When the rest of the plan only computes values from the shared result, fmap over the plan once:

src/Main.hs : haskell 
  let
    studentAndAgeMapped :: Plan.Plan scope StudentName (Student, StudentAge)
    studentAndAgeMapped =
      (\student -> (student, studentAge student)) <$> findStudentByName
  print ("mapped", Plan.explain studentAndAgeMapped)

When the rest of the plan runs further queries, or needs several intermediate results, use bind. It runs a plan once and hands its result to a function as a Planned value. use turns a Planned value back into a plan that returns it, and using feeds it as input to another plan, such as Plan.using student studentToClassesPlan. Neither runs the bound plan again, however many times they are used:

src/Main.hs : haskell 
  let
    studentAndAgeBound :: Plan.Plan scope StudentName (Student, StudentAge)
    studentAndAgeBound =
      Plan.bind findStudentByName $ \student ->
        (,)
          <$> Plan.use student
          <*> Plan.use (studentAge <$> student)
  print ("bound", Plan.explain studentAndAgeBound)

Orville.PostgreSQL.Plan.Syntax exports bind as >>=, so with the QualifiedDo extension (enabled at the top of Main.hs) the same plan can be written with do notation. Each <- binds a Planned value:

src/Main.hs : haskell 
  let
    studentAndAgeDo :: Plan.Plan scope StudentName (Student, StudentAge)
    studentAndAgeDo = PlanSyntax.do
      student <- findStudentByName
      (,) <$> Plan.use student <*> Plan.use (studentAge <$> student)
  print ("do", Plan.explain studentAndAgeDo)

All three alternatives run one SQL statement. Note that using doesn’t share the plan it is given: writing Plan.using student studentToClassesPlan twice would run the class queries twice. Bind its result once and use it instead.

You can build and execute this as usual:

shell 
stack build
stack exec using-plans

And you should see the following output:

output : plaintext 
Just (Student {studentId = 0, studentName = "Name", studentAge = 91})
Just (Student {studentId = 1, studentName = "Other Name", studentAge = 42})
[Just (Student {studentId = 1, studentName = "Other Name", studentAge = 42}),Just (Student {studentId = 0, studentName = "Name", studentAge = 91})]
Class {classId = 1, classSubject = "Cooking"}
["SELECT \"student_id\",\"id\",\"class_id\",\"student_id\" FROM \"plan_demo_student_class\" WHERE (\"student_id\") = ($1)","SELECT \"id\",\"id\",\"subject\" FROM \"plan_demo_class\" WHERE (\"id\") IN ($1, $2)"]
["SELECT \"student_id\",\"id\",\"class_id\",\"student_id\" FROM \"plan_demo_student_class\" WHERE (\"student_id\") IN ($1, $2)","SELECT \"id\",\"id\",\"subject\" FROM \"plan_demo_class\" WHERE (\"id\") IN ($1, $2)"]
[[Class {classId = 0, classSubject = "Painting"},Class {classId = 2, classSubject = "Swimming"}]]
("repeated",["SELECT \"name\",\"id\",\"name\",\"age\" FROM \"plan_demo_student\" WHERE (\"name\") = ($1)","SELECT \"name\",\"id\",\"name\",\"age\" FROM \"plan_demo_student\" WHERE (\"name\") = ($1)"])
("mapped",["SELECT \"name\",\"id\",\"name\",\"age\" FROM \"plan_demo_student\" WHERE (\"name\") = ($1)"])
("bound",["SELECT \"name\",\"id\",\"name\",\"age\" FROM \"plan_demo_student\" WHERE (\"name\") = ($1)"])
("do",["SELECT \"name\",\"id\",\"name\",\"age\" FROM \"plan_demo_student\" WHERE (\"name\") = ($1)"])