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"
153 You might be wondering why this information isn't in the definitions of
154 ``Author`` and ``Project`` above. Rel8 decouples ``TableSchema`` from the data
155 types themselves, as not all tables you define will necessarily have a schema.
156 For example, Rel8 allows you to define helper types to simplify the types of
157 queries - these tables only exist at query time, but there is no corresponding
158 base table. We'll see more on this idea later!
160With these table definitions, we can now start writing some queries!
168First, we'll take a look at ``SELECT`` statements - usually the bulk of most
169database heavy applications.
171In Rel8, ``SELECT`` statements are built using the ``Query`` monad. You can
172think of this monad like the ordinary ``[]`` (List) monad - but this isn't
175To start, we'll look at one of the simplest queries possible - a basic ``SELECT
176* FROM`` statement. To select all rows from a table, we use ``each``, and
177supply a ``TableSchema``. So to select all ``project`` rows, we can write::
179 >>> :t each projectSchema
180 each projectSchema :: Query (Project Expr)
182Notice that ``each`` gives us a ``Query`` that yields ``Project Expr`` rows. To
183see what this means, let's have a look at a single field of a ``Project Expr``::
185 >>> let aProjectExpr = undefined :: Project Expr
186 >>> :t projectAuthorId aProjectExpr
187 projectAuthorId aProjectExpr :: Expr AuthorId
189Recall we defined ``projectAuthorId`` as ``Column f AuthorId``. Now we have
190``f`` is ``Expr``, and ``Column Expr AuthorId`` reduces to ``Expr AuthorId``.
191We'll see more about ``Expr`` soon, but you can think of ``Expr a`` as "SQL
192expressions of type ``a``\".
194To execute this ``Query``, we pass it to ``select``::
196 >>> :t select c (each projectSchema)
197 select c (each projectSchema) :: MonadIO m => m [Project Result]
199When we ``select`` things containing ``Expr``\s, Rel8 builds a new response
200table with the ``Result`` interpretation. This means you'll get back plain
201Haskell values. Studying ``projectAuthorId`` again, we have::
203 >>> let aProjectResult = undefined :: Project Result
204 >>> :t projectAuthorId aProjectResult
205 projectAuthorId aProjectResult :: AuthorId
207Here ``Column Result AuthorId`` reduces to just ``AuthorId``, with no
208wrappping type at all.
210Putting this all together, we can run our first query::
212 >>> select c (each projectSchema) >>= mapM_ print
213 Project {projectAuthorId = 1, projectName = "rel8"}
214 Project {projectAuthorId = 2, projectName = "aeson"}
215 Project {projectAuthorId = 2, projectName = "text"}
217We now know that ``each`` is the equivalent of a ``SELECT *`` query, but
218sometimes we're only interested in a subset of the columns of a table. To
219restrict the returned columns, we can specify a projection by using ``Query``\s
220``Functor`` instance::
222 >>> select c $ projectName <$> each projectSchema
223 ["rel8","aeson","text"]
228Another common operation in relational databases is to take the ``JOIN`` of
229multiple tables. Rel8 doesn't have a specific join operation, but we can
230recover the functionality of a join by selecting all rows of two tables, and
231then using ``where_`` to filter them.
233To see how this works, first let's look at taking the product of two tables.
234We can do this by simply calling ``each`` twice, and then returning a tuple of
238 mapM_ print =<< select c do
239 author <- each authorSchema
240 project <- each projectSchema
241 return (projectName project, authorName author)
244 ("rel8","Bryan O'Sullivan")
245 ("rel8","Emily Pillmore")
247 ("aeson","Bryan O'Sullivan")
248 ("aeson","Emily Pillmore")
250 ("text","Bryan O'Sullivan")
251 ("text","Emily Pillmore")
253This isn't quite right, though, as we have ended up pairing up the wrong
254projects and authors. To fix this, we can use ``where_`` to restrict the
255returned rows. We could write::
258 author <- each authorSchema
259 project <- each projectSchema
260 where_ $ projectAuthorId project ==. authorId author
261 return (project, author)
263but doing this every time you need a join can obscure the meaning of the
264query you're writing. A good practice is to introduce specialised functions
265for the particular joins in your database. In our case, this would be::
267 projectsForAuthor :: Author Expr -> Query (Project Expr)
268 projectsForAuthor a = each projectSchema >>= filter \p ->
269 projectAuthorId p ==. authorId a
271Our final query is then::
274 mapM_ print =<< select c do
275 author <- each authorSchema
276 project <- projectsForAuthor author
277 return (projectName project, authorName author)
280 ("aeson","Bryan O'Sullivan")
281 ("text","Bryan O'Sullivan")
286Rel8 is also capable of performing ``LEFT JOIN``\s. To perform ``LEFT JOIN``\s,
287we follow the same approach as before, but use the ``optional`` query
288transformer to allow for the possibility of the join to fail.
290In our test database, we can see that there's another author who we haven't
293 >>> select c $ authorName <$> each authorSchema
294 ["Ollie","Bryan O'Sullivan","Emily Pillmore"]
296Emily wasn't returned in our earlier query because - in our database - she
297doesn't have any registered projects. We can account for this partiality in our
298original query by wrapping the ``projectsForAuthor`` call with ``optional``::
301 mapM_ print =<< select c do
302 author <- each authorSchema
303 mproject <- optional $ projectsForAuthor author
304 return (authorName author, projectName <$> mproject)
306 ("Ollie",Just "rel8")
307 ("Bryan O'Sullivan",Just "aeson")
308 ("Bryan O'Sullivan",Just "text")
309 ("Emily Pillmore",Nothing)
315Aggregations are operations like ``sum`` and ``count`` - operations that reduce
316multiple rows to single values. To perform aggregations in Rel8, we can use the
317``aggregate`` function, which takes a ``Query`` of aggregated expressions, runs
318the aggregation, and returns aggregated rows.
320To start, let's look at a simple aggregation that tells us how many projects
327Rel8 is also capable of aggregating multiple rows into a single row by
328concatenating all rows as a list. This aggregation allows us to break free of
329the row-orientated nature of SQL and write queries that return tree-like
330structures. Earlier we saw an example of returning authors with their projects,
331but the query didn't do a great job of describing the one-to-many relationship
332between authors and their projects.
334Let's look again at a query that returns authors and their projects, and
335focus on the /type/ of that query::
337 projectsForAuthor a = each projectSchema >>= filter \p ->
338 projectAuthorId p ==. authorId a
340 let authorsAndProjects = do
341 author <- each authorSchema
342 project <- projectsForAuthor author
343 return (author, project)
346 >>> :t select c authorsAndProjects
347 select c authorsAndProjects
348 :: MonadIO m => m [(Author Result, Project Result)]
351Our query gives us a single list of pairs of authors and projects. However,
352with our domain knowledge of the schema, this isn't a great type - what we'd
353rather have is a list of pairs of authors and /lists/ of projects. That is,
356 [(Author Result, [Project Result])]
358This would be a much better type! Rel8 can produce a query with this type by
359simply wrapping the call to ``projectsForAuthor`` with either ``some`` or
360``many``. Here we'll use ``many``, which allows for the possibility of an
361author to have no projects::
364 mapM_ print =<< select c do
365 author <- each authorSchema
366 projectNames <- many $ projectName <$> projectsForAuthor author
367 return (authorName author, projectNames)
370 ("Bryan O'Sullivan",["aeson","text"])
371 ("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