6In this section, we'll take a look at using Rel8 to work with a small database
7for Haskell packages. We'll take a look at idiomatic usage of Rel8, mapping
8tables to Haskell, and then look at writing some simple queries.
10Before we get started, we'll be using the following language extensions and
11imports throughout this guide::
13 {-# language BlockArguments #-}
14 {-# language DeriveAnyClass #-}
15 {-# language DeriveGeneric #-}
16 {-# language DerivingStrategies #-}
17 {-# language DerivingVia #-}
18 {-# language DuplicateRecordFields #-}
19 {-# language GeneralizedNewtypeDeriving #-}
20 {-# language OverloadedStrings #-}
21 {-# language StandaloneDeriving #-}
22 {-# language TypeApplications #-}
23 {-# language TypeFamilies #-}
31Before we start writing any Haskell, let's take a look at the schema we'll work
32with. The `author` table has three columns:
34+-----------------+-------------+----------+
35| Column Name | Type | Nullable |
36+=================+=============+==========+
37| ``author_id`` | ``integer`` | not null |
38+-----------------+-------------+----------+
39| ``name`` | ``text`` | not null |
40+-----------------+-------------+----------+
41| ``url`` | ``text`` | |
42+-----------------+-------------+----------+
44and the `project` table has two:
46+-----------------+-------------+----------+
47| Column Name | Type | Nullable |
48+=================+=============+==========+
49| ``author_id`` | ``integer`` | not null |
50+-----------------+-------------+----------+
51| ``name`` | ``text`` | not null |
52+-----------------+-------------+----------+
54A ``project`` always has an ``author``, but not all ``author``\s have projects.
55Each ``author`` has a name and (maybe) an associated website, and each project
58Mapping Schemas to Haskell
59--------------------------
61Now that we've seen our schema, we can begin writing a mapping in Rel8. The
62idiomatic way to map a table is to use a record that is parameterised what Rel8
63calls an *interpretation functor*, and to define each field with ``Column``.
64For this type to be usable with Rel8 we need it to be an instance of
65``Rel8able``, which can be derived with a combination of ``DeriveAnyClass`` and
66``DeriveGeneric`` language extensions.
68Following these steps for ``author``, we have::
70 data Author f = Author
71 { authorId :: Column f Int64
72 , name :: Column f Text
73 , url :: Column f (Maybe Text)
75 deriving stock (Generic)
76 deriving anyclass (Rel8able)
78This is a perfectly reasonable definition, but cautious readers might notice a
79problem - in particular, with the type of the ``authorId`` field. While
80``Int64`` is correct, it's not the best type. If we had other identifier types
81in our project, it would be too easy to accidentally mix them up and create
82nonsensical joins. As Haskell programmers, we often solve this problem by
83creating ``newtype`` wrappers, and we can also use this technique with Rel8::
85 newtype AuthorId = AuthorId { toInt64 :: Int64 }
86 deriving newtype (DBEq, DBType, Eq, Show)
88Now we can write our final schema mapping. First, the ``author`` table::
90 data Author f = Author
91 { authorId :: Column f AuthorId
92 , authorName :: Column f Text
93 , authorUrl :: Column f (Maybe Text)
95 deriving stock (Generic)
96 deriving anyclass (Rel8able)
98And similarly, the ``project`` table::
100 data Project f = Project
101 { projectAuthorId :: Column f AuthorId
102 , projectName :: Column f Text
104 deriving stock (Generic)
105 deriving anyclass (Rel8able)
107To show query results in this documentation, we'll also need ``Show`` instances:
108Unfortunately these definitions look a bit scary, but they are essentially just
111 deriving stock instance f ~ Result => Show (Author f)
112 deriving stock instance f ~ Result => Show (Project f)
114These data types describe the structural mapping of the tables, but we also
115need to specify a ``TableSchema`` for each table. A ``TableSchema`` contains
116the name of the table and the name of all columns in the table, which will
117ultimately allow us to ``SELECT`` and ``INSERT`` rows for these tables.
119To define a ``TableSchema``, we just need to fill construct appropriate
120``TableSchema`` values. When it comes to the ``tableColumns`` field, we
121construct values of our data types above, and set each field to the name of the
122column that it maps to.
124First, ``authorSchema`` describes the column names of the ``author`` table when
125associated with the ``Author`` type::
127 authorSchema :: TableSchema (Author Name)
128 authorSchema = TableSchema
132 { authorId = "author_id"
133 , authorName = "name"
138And likewise for ``project`` and ``Project``::
140 projectSchema :: TableSchema (Project Name)
141 projectSchema = TableSchema
145 { projectAuthorId = "author_id"
146 , projectName = "name"
150There is also some generics machinery available if you want to grab the field
151information from your ``Rel8able`` type::
153 projectSchema :: TableSchema (Project Name)
154 projectSchema = TableSchema
157 , columns = namesFromLabels @(Project Name)
160This will create a ``TableSchema`` for ``Project`` where every column name
161corresponds exactly to the name of the field. If you need more flexibility, you
162can use ``namesFromLabelsWith``, which takes a transformation function.
166 You might be wondering why this information isn't in the definitions of
167 ``Author`` and ``Project`` above. Rel8 decouples ``TableSchema`` from the data
168 types themselves, as not all tables you define will necessarily have a schema.
169 For example, Rel8 allows you to define helper types to simplify the types of
170 queries - these tables only exist at query time, but there is no corresponding
171 base table. We'll see more on this idea later!
173With these table definitions, we can now start writing some queries!
181First, we'll take a look at ``SELECT`` statements - usually the bulk of most
182database heavy applications.
184In Rel8, ``SELECT`` statements are built using the ``Query`` monad. You can
185think of this monad like the ordinary ``[]`` (List) monad - but this isn't
188To start, we'll look at one of the simplest queries possible - a basic ``SELECT
189* FROM`` statement. To select all rows from a table, we use ``each``, and
190supply a ``TableSchema``. So to select all ``project`` rows, we can write::
192 >>> :t each projectSchema
193 each projectSchema :: Query (Project Expr)
195Notice that ``each`` gives us a ``Query`` that yields ``Project Expr`` rows. To
196see what this means, let's have a look at a single field of a ``Project Expr``::
198 >>> let aProjectExpr = undefined :: Project Expr
199 >>> :t projectAuthorId aProjectExpr
200 projectAuthorId aProjectExpr :: Expr AuthorId
202Recall we defined ``projectAuthorId`` as ``Column f AuthorId``. Now we have
203``f`` is ``Expr``, and ``Column Expr AuthorId`` reduces to ``Expr AuthorId``.
204We'll see more about ``Expr`` soon, but you can think of ``Expr a`` as "SQL
205expressions of type ``a``\".
207To execute this ``Query``, we pass it to ``select``. This function takes both a
208database connection (which can be obtained using hasql's ``acquire`` function),
209and the ``Query`` to run::
211 >>> Right conn <- acquire "user=postgres"
212 >>> :t select conn (each projectSchema)
213 select conn (each projectSchema) :: MonadIO m => m [Project Result]
215When we ``select`` things containing ``Expr``\s, Rel8 builds a new response
216table with the ``Result`` interpretation. This means you'll get back plain
217Haskell values. Studying ``projectAuthorId`` again, we have::
219 >>> let aProjectResult = undefined :: Project Result
220 >>> :t projectAuthorId aProjectResult
221 projectAuthorId aProjectResult :: AuthorId
223Here ``Column Result AuthorId`` reduces to just ``AuthorId``, with no
224wrappping type at all.
226Putting this all together, we can run our first query::
228 >>> select conn (each projectSchema) >>= mapM_ print
229 Project {projectAuthorId = 1, projectName = "rel8"}
230 Project {projectAuthorId = 2, projectName = "aeson"}
231 Project {projectAuthorId = 2, projectName = "text"}
233We now know that ``each`` is the equivalent of a ``SELECT *`` query, but
234sometimes we're only interested in a subset of the columns of a table. To
235restrict the returned columns, we can specify a projection by using ``Query``\s
236``Functor`` instance::
238 >>> select conn $ projectName <$> each projectSchema
239 ["rel8","aeson","text"]
244Another common operation in relational databases is to take the ``JOIN`` of
245multiple tables. Rel8 doesn't have a specific join operation, but we can
246recover the functionality of a join by selecting all rows of two tables, and
247then using ``where_`` to filter them.
249To see how this works, first let's look at taking the product of two tables.
250We can do this by simply calling ``each`` twice, and then returning a tuple of
254 mapM_ print =<< select conn do
255 author <- each authorSchema
256 project <- each projectSchema
257 return (projectName project, authorName author)
260 ("rel8","Bryan O'Sullivan")
261 ("rel8","Emily Pillmore")
263 ("aeson","Bryan O'Sullivan")
264 ("aeson","Emily Pillmore")
266 ("text","Bryan O'Sullivan")
267 ("text","Emily Pillmore")
269This isn't quite right, though, as we have ended up pairing up the wrong
270projects and authors. To fix this, we can use ``where_`` to restrict the
271returned rows. We could write::
274 author <- each authorSchema
275 project <- each projectSchema
276 where_ $ projectAuthorId project ==. authorId author
277 return (project, author)
279but doing this every time you need a join can obscure the meaning of the
280query you're writing. A good practice is to introduce specialised functions
281for the particular joins in your database. In our case, this would be::
283 projectsForAuthor :: Author Expr -> Query (Project Expr)
284 projectsForAuthor a = each projectSchema >>= filter \p ->
285 projectAuthorId p ==. authorId a
287Our final query is then::
290 mapM_ print =<< select conn do
291 author <- each authorSchema
292 project <- projectsForAuthor author
293 return (projectName project, authorName author)
296 ("aeson","Bryan O'Sullivan")
297 ("text","Bryan O'Sullivan")
302Rel8 is also capable of performing ``LEFT JOIN``\s. To perform ``LEFT JOIN``\s,
303we follow the same approach as before, but use the ``optional`` query
304transformer to allow for the possibility of the join to fail.
306In our test database, we can see that there's another author who we haven't
309 >>> select conn $ authorName <$> each authorSchema
310 ["Ollie","Bryan O'Sullivan","Emily Pillmore"]
312Emily wasn't returned in our earlier query because - in our database - she
313doesn't have any registered projects. We can account for this partiality in our
314original query by wrapping the ``projectsForAuthor`` call with ``optional``::
317 mapM_ print =<< select conn do
318 author <- each authorSchema
319 mproject <- optional $ projectsForAuthor author
320 return (authorName author, projectName <$> mproject)
322 ("Ollie",Just "rel8")
323 ("Bryan O'Sullivan",Just "aeson")
324 ("Bryan O'Sullivan",Just "text")
325 ("Emily Pillmore",Nothing)
331Aggregations are operations like ``sum`` and ``count`` - operations that reduce
332multiple rows to single values. To perform aggregations in Rel8, we can use the
333``aggregate`` function, which takes a ``Query`` of aggregated expressions, runs
334the aggregation, and returns aggregated rows.
336To start, let's look at a simple aggregation that tells us how many projects
343Rel8 is also capable of aggregating multiple rows into a single row by
344concatenating all rows as a list. This aggregation allows us to break free of
345the row-orientated nature of SQL and write queries that return tree-like
346structures. Earlier we saw an example of returning authors with their projects,
347but the query didn't do a great job of describing the one-to-many relationship
348between authors and their projects.
350Let's look again at a query that returns authors and their projects, and
351focus on the /type/ of that query::
353 projectsForAuthor a = each projectSchema >>= filter \p ->
354 projectAuthorId p ==. authorId a
356 let authorsAndProjects = do
357 author <- each authorSchema
358 project <- projectsForAuthor author
359 return (author, project)
362 >>> :t select conn authorsAndProjects
363 select conn authorsAndProjects
364 :: MonadIO m => m [(Author Result, Project Result)]
367Our query gives us a single list of pairs of authors and projects. However,
368with our domain knowledge of the schema, this isn't a great type - what we'd
369rather have is a list of pairs of authors and /lists/ of projects. That is,
372 [(Author Result, [Project Result])]
374This would be a much better type! Rel8 can produce a query with this type by
375simply wrapping the call to ``projectsForAuthor`` with either ``some`` or
376``many``. Here we'll use ``many``, which allows for the possibility of an
377author to have no projects::
380 mapM_ print =<< select conn do
381 author <- each authorSchema
382 projectNames <- many $ projectName <$> projectsForAuthor author
383 return (authorName author, projectNames)
386 ("Bryan O'Sullivan",["aeson","text"])
387 ("Emily Pillmore",[])
keysesc to close
- j k
- move down, up
- enter
- open
- esc
- back, or up a level
- 1 2 3 4
- readme, files, commits, refs
- /
- filter
- y
- copy the clone command
- c
- compare the chosen commit with another (or shift-click both)
- ?
- show or hide this list