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 information from your Rel8able type::
152 projectSchema :: TableSchema (Project Name)
153 projectSchema = TableSchema
156 , columns = namesFromLabels @(Project Name)
162 You might be wondering why this information isn't in the definitions of
163 ``Author`` and ``Project`` above. Rel8 decouples ``TableSchema`` from the data
164 types themselves, as not all tables you define will necessarily have a schema.
165 For example, Rel8 allows you to define helper types to simplify the types of
166 queries - these tables only exist at query time, but there is no corresponding
167 base table. We'll see more on this idea later!
169With these table definitions, we can now start writing some queries!
177First, we'll take a look at ``SELECT`` statements - usually the bulk of most
178database heavy applications.
180In Rel8, ``SELECT`` statements are built using the ``Query`` monad. You can
181think of this monad like the ordinary ``[]`` (List) monad - but this isn't
184To start, we'll look at one of the simplest queries possible - a basic ``SELECT
185* FROM`` statement. To select all rows from a table, we use ``each``, and
186supply a ``TableSchema``. So to select all ``project`` rows, we can write::
188 >>> :t each projectSchema
189 each projectSchema :: Query (Project Expr)
191Notice that ``each`` gives us a ``Query`` that yields ``Project Expr`` rows. To
192see what this means, let's have a look at a single field of a ``Project Expr``::
194 >>> let aProjectExpr = undefined :: Project Expr
195 >>> :t projectAuthorId aProjectExpr
196 projectAuthorId aProjectExpr :: Expr AuthorId
198Recall we defined ``projectAuthorId`` as ``Column f AuthorId``. Now we have
199``f`` is ``Expr``, and ``Column Expr AuthorId`` reduces to ``Expr AuthorId``.
200We'll see more about ``Expr`` soon, but you can think of ``Expr a`` as "SQL
201expressions of type ``a``\".
203To execute this ``Query``, we pass it to ``select``::
205 >>> :t select c (each projectSchema)
206 select c (each projectSchema) :: MonadIO m => m [Project Result]
208When we ``select`` things containing ``Expr``\s, Rel8 builds a new response
209table with the ``Result`` interpretation. This means you'll get back plain
210Haskell values. Studying ``projectAuthorId`` again, we have::
212 >>> let aProjectResult = undefined :: Project Result
213 >>> :t projectAuthorId aProjectResult
214 projectAuthorId aProjectResult :: AuthorId
216Here ``Column Result AuthorId`` reduces to just ``AuthorId``, with no
217wrappping type at all.
219Putting this all together, we can run our first query::
221 >>> select c (each projectSchema) >>= mapM_ print
222 Project {projectAuthorId = 1, projectName = "rel8"}
223 Project {projectAuthorId = 2, projectName = "aeson"}
224 Project {projectAuthorId = 2, projectName = "text"}
226We now know that ``each`` is the equivalent of a ``SELECT *`` query, but
227sometimes we're only interested in a subset of the columns of a table. To
228restrict the returned columns, we can specify a projection by using ``Query``\s
229``Functor`` instance::
231 >>> select c $ projectName <$> each projectSchema
232 ["rel8","aeson","text"]
237Another common operation in relational databases is to take the ``JOIN`` of
238multiple tables. Rel8 doesn't have a specific join operation, but we can
239recover the functionality of a join by selecting all rows of two tables, and
240then using ``where_`` to filter them.
242To see how this works, first let's look at taking the product of two tables.
243We can do this by simply calling ``each`` twice, and then returning a tuple of
247 mapM_ print =<< select c do
248 author <- each authorSchema
249 project <- each projectSchema
250 return (projectName project, authorName author)
253 ("rel8","Bryan O'Sullivan")
254 ("rel8","Emily Pillmore")
256 ("aeson","Bryan O'Sullivan")
257 ("aeson","Emily Pillmore")
259 ("text","Bryan O'Sullivan")
260 ("text","Emily Pillmore")
262This isn't quite right, though, as we have ended up pairing up the wrong
263projects and authors. To fix this, we can use ``where_`` to restrict the
264returned rows. We could write::
267 author <- each authorSchema
268 project <- each projectSchema
269 where_ $ projectAuthorId project ==. authorId author
270 return (project, author)
272but doing this every time you need a join can obscure the meaning of the
273query you're writing. A good practice is to introduce specialised functions
274for the particular joins in your database. In our case, this would be::
276 projectsForAuthor :: Author Expr -> Query (Project Expr)
277 projectsForAuthor a = each projectSchema >>= filter \p ->
278 projectAuthorId p ==. authorId a
280Our final query is then::
283 mapM_ print =<< select c do
284 author <- each authorSchema
285 project <- projectsForAuthor author
286 return (projectName project, authorName author)
289 ("aeson","Bryan O'Sullivan")
290 ("text","Bryan O'Sullivan")
295Rel8 is also capable of performing ``LEFT JOIN``\s. To perform ``LEFT JOIN``\s,
296we follow the same approach as before, but use the ``optional`` query
297transformer to allow for the possibility of the join to fail.
299In our test database, we can see that there's another author who we haven't
302 >>> select c $ authorName <$> each authorSchema
303 ["Ollie","Bryan O'Sullivan","Emily Pillmore"]
305Emily wasn't returned in our earlier query because - in our database - she
306doesn't have any registered projects. We can account for this partiality in our
307original query by wrapping the ``projectsForAuthor`` call with ``optional``::
310 mapM_ print =<< select c do
311 author <- each authorSchema
312 mproject <- optional $ projectsForAuthor author
313 return (authorName author, projectName <$> mproject)
315 ("Ollie",Just "rel8")
316 ("Bryan O'Sullivan",Just "aeson")
317 ("Bryan O'Sullivan",Just "text")
318 ("Emily Pillmore",Nothing)
324Aggregations are operations like ``sum`` and ``count`` - operations that reduce
325multiple rows to single values. To perform aggregations in Rel8, we can use the
326``aggregate`` function, which takes a ``Query`` of aggregated expressions, runs
327the aggregation, and returns aggregated rows.
329To start, let's look at a simple aggregation that tells us how many projects
336Rel8 is also capable of aggregating multiple rows into a single row by
337concatenating all rows as a list. This aggregation allows us to break free of
338the row-orientated nature of SQL and write queries that return tree-like
339structures. Earlier we saw an example of returning authors with their projects,
340but the query didn't do a great job of describing the one-to-many relationship
341between authors and their projects.
343Let's look again at a query that returns authors and their projects, and
344focus on the /type/ of that query::
346 projectsForAuthor a = each projectSchema >>= filter \p ->
347 projectAuthorId p ==. authorId a
349 let authorsAndProjects = do
350 author <- each authorSchema
351 project <- projectsForAuthor author
352 return (author, project)
355 >>> :t select c authorsAndProjects
356 select c authorsAndProjects
357 :: MonadIO m => m [(Author Result, Project Result)]
360Our query gives us a single list of pairs of authors and projects. However,
361with our domain knowledge of the schema, this isn't a great type - what we'd
362rather have is a list of pairs of authors and /lists/ of projects. That is,
365 [(Author Result, [Project Result])]
367This would be a much better type! Rel8 can produce a query with this type by
368simply wrapping the call to ``projectsForAuthor`` with either ``some`` or
369``many``. Here we'll use ``many``, which allows for the possibility of an
370author to have no projects::
373 mapM_ print =<< select c do
374 author <- each authorSchema
375 projectNames <- many $ projectName <$> projectsForAuthor author
376 return (authorName author, projectNames)
379 ("Bryan O'Sullivan",["aeson","text"])
380 ("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