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``::
209 >>> :t select c (each projectSchema)
210 select c (each projectSchema) :: MonadIO m => m [Project Result]
212When we ``select`` things containing ``Expr``\s, Rel8 builds a new response
213table with the ``Result`` interpretation. This means you'll get back plain
214Haskell values. Studying ``projectAuthorId`` again, we have::
216 >>> let aProjectResult = undefined :: Project Result
217 >>> :t projectAuthorId aProjectResult
218 projectAuthorId aProjectResult :: AuthorId
220Here ``Column Result AuthorId`` reduces to just ``AuthorId``, with no
221wrappping type at all.
223Putting this all together, we can run our first query::
225 >>> select c (each projectSchema) >>= mapM_ print
226 Project {projectAuthorId = 1, projectName = "rel8"}
227 Project {projectAuthorId = 2, projectName = "aeson"}
228 Project {projectAuthorId = 2, projectName = "text"}
230We now know that ``each`` is the equivalent of a ``SELECT *`` query, but
231sometimes we're only interested in a subset of the columns of a table. To
232restrict the returned columns, we can specify a projection by using ``Query``\s
233``Functor`` instance::
235 >>> select c $ projectName <$> each projectSchema
236 ["rel8","aeson","text"]
241Another common operation in relational databases is to take the ``JOIN`` of
242multiple tables. Rel8 doesn't have a specific join operation, but we can
243recover the functionality of a join by selecting all rows of two tables, and
244then using ``where_`` to filter them.
246To see how this works, first let's look at taking the product of two tables.
247We can do this by simply calling ``each`` twice, and then returning a tuple of
251 mapM_ print =<< select c do
252 author <- each authorSchema
253 project <- each projectSchema
254 return (projectName project, authorName author)
257 ("rel8","Bryan O'Sullivan")
258 ("rel8","Emily Pillmore")
260 ("aeson","Bryan O'Sullivan")
261 ("aeson","Emily Pillmore")
263 ("text","Bryan O'Sullivan")
264 ("text","Emily Pillmore")
266This isn't quite right, though, as we have ended up pairing up the wrong
267projects and authors. To fix this, we can use ``where_`` to restrict the
268returned rows. We could write::
271 author <- each authorSchema
272 project <- each projectSchema
273 where_ $ projectAuthorId project ==. authorId author
274 return (project, author)
276but doing this every time you need a join can obscure the meaning of the
277query you're writing. A good practice is to introduce specialised functions
278for the particular joins in your database. In our case, this would be::
280 projectsForAuthor :: Author Expr -> Query (Project Expr)
281 projectsForAuthor a = each projectSchema >>= filter \p ->
282 projectAuthorId p ==. authorId a
284Our final query is then::
287 mapM_ print =<< select c do
288 author <- each authorSchema
289 project <- projectsForAuthor author
290 return (projectName project, authorName author)
293 ("aeson","Bryan O'Sullivan")
294 ("text","Bryan O'Sullivan")
299Rel8 is also capable of performing ``LEFT JOIN``\s. To perform ``LEFT JOIN``\s,
300we follow the same approach as before, but use the ``optional`` query
301transformer to allow for the possibility of the join to fail.
303In our test database, we can see that there's another author who we haven't
306 >>> select c $ authorName <$> each authorSchema
307 ["Ollie","Bryan O'Sullivan","Emily Pillmore"]
309Emily wasn't returned in our earlier query because - in our database - she
310doesn't have any registered projects. We can account for this partiality in our
311original query by wrapping the ``projectsForAuthor`` call with ``optional``::
314 mapM_ print =<< select c do
315 author <- each authorSchema
316 mproject <- optional $ projectsForAuthor author
317 return (authorName author, projectName <$> mproject)
319 ("Ollie",Just "rel8")
320 ("Bryan O'Sullivan",Just "aeson")
321 ("Bryan O'Sullivan",Just "text")
322 ("Emily Pillmore",Nothing)
328Aggregations are operations like ``sum`` and ``count`` - operations that reduce
329multiple rows to single values. To perform aggregations in Rel8, we can use the
330``aggregate`` function, which takes a ``Query`` of aggregated expressions, runs
331the aggregation, and returns aggregated rows.
333To start, let's look at a simple aggregation that tells us how many projects
340Rel8 is also capable of aggregating multiple rows into a single row by
341concatenating all rows as a list. This aggregation allows us to break free of
342the row-orientated nature of SQL and write queries that return tree-like
343structures. Earlier we saw an example of returning authors with their projects,
344but the query didn't do a great job of describing the one-to-many relationship
345between authors and their projects.
347Let's look again at a query that returns authors and their projects, and
348focus on the /type/ of that query::
350 projectsForAuthor a = each projectSchema >>= filter \p ->
351 projectAuthorId p ==. authorId a
353 let authorsAndProjects = do
354 author <- each authorSchema
355 project <- projectsForAuthor author
356 return (author, project)
359 >>> :t select c authorsAndProjects
360 select c authorsAndProjects
361 :: MonadIO m => m [(Author Result, Project Result)]
364Our query gives us a single list of pairs of authors and projects. However,
365with our domain knowledge of the schema, this isn't a great type - what we'd
366rather have is a list of pairs of authors and /lists/ of projects. That is,
369 [(Author Result, [Project Result])]
371This would be a much better type! Rel8 can produce a query with this type by
372simply wrapping the call to ``projectsForAuthor`` with either ``some`` or
373``many``. Here we'll use ``many``, which allows for the possibility of an
374author to have no projects::
377 mapM_ print =<< select c do
378 author <- each authorSchema
379 projectNames <- many $ projectName <$> projectsForAuthor author
380 return (authorName author, projectNames)
383 ("Bryan O'Sullivan",["aeson","text"])
384 ("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