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 -XBlockArguments #-}
14 {-# language -XDeriveAnyClass #-}
15 {-# language -XDeriveGeneric #-}
16 {-# language -XDerivingStrategies #-}
17 {-# language -XDerivingVia #-}
18 {-# language -XDuplicateRecordFields #-}
19 {-# language -XGeneralizedNewtypeDeriving #-}
20 {-# language -XOverloadedStrings #-}
21 {-# language -XStandaloneDeriving #-}
22 {-# language -XTypeApplications #-}
23 {-# language -XTypeFamilies #-}
30Before we start writing any Haskell, let's take a look at the schema we'll work
31with. The `author` table has three columns:
33+-----------------+-------------+----------+
34| Column Name | Type | Nullable |
35+=================+=============+==========+
36| ``author_id`` | ``integer`` | not null |
37+-----------------+-------------+----------+
38| ``name`` | ``text`` | not null |
39+-----------------+-------------+----------+
40| ``url`` | ``text`` | |
41+-----------------+-------------+----------+
43and the `project` table has two:
45+-----------------+-------------+----------+
46| Column Name | Type | Nullable |
47+=================+=============+==========+
48| ``author_id`` | ``integer`` | not null |
49+-----------------+-------------+----------+
50| ``name`` | ``text`` | not null |
51+-----------------+-------------+----------+
53A ``project`` always has an ``author``, but not all ``author``\s have projects.
54Each ``author`` has a name and (maybe) an associated website, and each project
57Mapping Schemas to Haskell
58--------------------------
60Now that we've seen our schema, we can begin writing a mapping in Rel8. The
61idiomatic way to map a table is to use a record that is parameterised what Rel8
62calls an *interpretation functor*, and to define each field with ``Column``.
63For this type to be usable with Rel8 we need it to be an instance of
64``Rel8able``, which can be derived with a combination of ``DeriveAnyClass`` and
65``DeriveGeneric`` language extensions.
67Following these steps for ``author``, we have::
69 data Author f = Author
70 { authorId :: Column f Int64
71 , name :: Column f Text
72 , url :: Column f (Maybe Text)
74 deriving stock (Generic)
75 deriving anyclass (Rel8able)
77This is a perfectly reasonable definition, but cautious readers might notice a
78problem - in particular, with the type of the ``authorId`` field. While
79``Int64`` is correct, it's not the best type. If we had other identifier types
80in our project, it would be too easy to accidentally mix them up and create
81nonsensical joins. As Haskell programmers, we often solve this problem by
82creating ``newtype`` wrappers, and we can also use this technique with Rel8::
84 newtype AuthorId = AuthorId { toInt64 :: Int64 }
85 deriving newtype (DBEq, DBType, Eq, Show)
87Now we can write our final schema mapping. First, the ``author`` table::
89 data Author f = Author
90 { authorId :: Column f AuthorId
91 , authorName :: Column f Text
92 , authorUrl :: Column f (Maybe Text)
94 deriving stock (Generic)
95 deriving anyclass (Rel8able)
97And similarly, the ``project`` table::
99 data Project f = Project
100 { projectAuthorId :: Column f AuthorId
101 , projectName :: Column f Text
103 deriving stock (Generic)
104 deriving anyclass (Rel8able
106To show query results in this documentation, we'll also need ``Show`` instances:
107Unfortunately these definitions look a bit scary, but they are essentially just
110 deriving stock instance f ~ Identity => Show (Author f)
111 deriving stock instance f ~ Identity => Show (Project f)
113These data types describe the structural mapping of the tables, but we also
114need to specify a ``TableSchema`` for each table. A ``TableSchema`` contains
115the name of the table and the name of all columns in the table, which will
116ultimately allow us to ``SELECT`` and ``INSERT`` rows for these tables.
118To define a ``TableSchema``, we just need to fill construct appropriate
119``TableSchema`` values. When it comes to the ``tableColumns`` field, we
120construct values of our data types above, and set each field to the name of the
121column that it maps to.
123First, ``authorSchema`` describes the column names of the ``author`` table when
124associated with the ``Author`` type::
126 authorSchema :: TableSchema (Author Name)
127 authorSchema = TableSchema
128 { tableName = "author"
129 , tableSchema = Nothing
130 , tableColumns = Author
131 { authorId = "author_id"
132 , authorName = "name"
137And likewise for ``project`` and ``Project``::
139 projectSchema :: TableSchema (Project Name)
140 projectSchema = TableSchema
141 { tableName = "project"
142 , tableSchema = Nothing
143 , tableColumns = Project
144 { projectAuthorId = "author_id"
145 , projectName = "name"
152 You might be wondering why this information isn't in the definitions of
153 ``Author`` and ``Project`` above. Rel8 decouples ``TableSchema`` from the data
154 types themselves, as not all tables you define will necessarily have a schema.
155 For example, Rel8 allows you to define helper types to simplify the types of
156 queries - these tables only exist at query time, but there is no corresponding
157 base table. We'll see more on this idea later!
159With these table definitions, we can now start writing some queries!
167First, we'll take a look at ``SELECT`` statements - usually the bulk of most
168database heavy applications.
170In Rel8, ``SELECT`` statements are built using the ``Query`` monad. You can
171think of this monad like the ordinary ``[]`` (List) monad - but this isn't
174To start, we'll look at one of the simplest queries possible - a basic ``SELECT
175* FROM`` statement. To select all rows from a table, we use ``each``, and
176supply a ``TableSchema``. So to select all ``project`` rows, we can write::
178 >>> :t each projectSchema
179 each projectSchema :: Query (Project Expr)
181Notice that ``each`` gives us a ``Query`` that yields ``Project Expr`` rows. To
182see what this means, let's have a look at a single field of a ``Project Expr``::
184 >>> let aProjectExpr = undefined :: Project Expr
185 >>> :t projectAuthorId aProjectExpr
186 projectAuthorId aProjectExpr :: Expr AuthorId
188Recall we defined ``projectAuthorId`` as ``Column f AuthorId``. Now we have
189``f`` is ``Expr``, and ``Column Expr AuthorId`` reduces to ``Expr AuthorId``.
190We'll see more about ``Expr`` soon, but you can think of ``Expr a`` as "SQL
191expressions of type ``a``\".
193To execute this ``Query``, we pass it to ``select``::
195 >>> :t select c (each projectSchema)
196 select c (each projectSchema) :: MonadIO m => m [Project Identity]
198When we ``select`` things containing ``Expr``s, Rel8 builds a new response
199table with the ``Identity`` interpretation. This means you'll get back plain
200Haskell values. Studying ``projectAuthorId`` again, we have::
202 >>> let aProjectIdentity = undefined :: Project Identity
203 >>> :t projectAuthorId aProjectIdentity
204 projectAuthorId aProjectIdentity :: AuthorId
206Here ``Column Identity AuthorId`` reduces to just ``AuthorId``, with no
207wrappping type at all.
209Putting this all together, we can run our first query::
211 >>> select c (each projectSchema) >>= mapM_ print
212 Project {projectAuthorId = 1, projectName = "rel8"}
213 Project {projectAuthorId = 2, projectName = "aeson"}
214 Project {projectAuthorId = 2, projectName = "text"}
216We now know that ``each`` is the equivalent of a ``SELECT *`` query, but
217sometimes we're only interested in a subset of the columns of a table. To
218restrict the returned columns, we can specify a projection by using ``Query``\s
219``Functor`` instance::
221 >>> select c $ projectName <$> each projectSchema
222 ["rel8","aeson","text"]
227Another common operation in relational databases is to take the ``JOIN`` of
228multiple tables. Rel8 doesn't have a specific join operation, but we can
229recover the functionality of a join by selecting all rows of two tables, and
230then using ``where_`` to filter them.
232To see how this works, first let's look at taking the product of two tables.
233We can do this by simply calling ``each`` twice, and then returning a tuple of
237 mapM_ print =<< select c do
238 author <- each authorSchema
239 project <- each projectSchema
240 return (projectName project, authorName author)
243 ("rel8","Bryan O'Sullivan")
244 ("rel8","Emily Pillmore")
246 ("aeson","Bryan O'Sullivan")
247 ("aeson","Emily Pillmore")
249 ("text","Bryan O'Sullivan")
250 ("text","Emily Pillmore")
252This isn't quite right, though, as we have ended up pairing up the wrong
253projects and authors. To fix this, we can use ``where_`` to restrict the
254returned rows. We could write::
257 author <- each authorSchema
258 project <- each projectSchema
259 where_ $ projectAuthorId project ==. authorId author
260 return (project, author)
262but doing this every time you need a join can obscure the meaning of the
263query you're writing. A good practice is to introduce specialised functions
264for the particular joins in your database. In our case, this would be::
266 projectsForAuthor :: Author Expr -> Query (Project Expr)
267 projectsForAuthor a = each projectSchema >>= filter \p ->
268 projectAuthorId p ==. authorId a
270Our final query is then::
273 mapM_ print =<< select c do
274 author <- each authorSchema
275 project <- projectsForAuthor author
276 return (projectName project, authorName author)
279 ("aeson","Bryan O'Sullivan")
280 ("text","Bryan O'Sullivan")
285Rel8 is also capable of performing ``LEFT JOIN``\s. To perform ``LEFT JOIN``\s,
286we follow the same approach as before, but use the ``optional`` query
287transformer to allow for the possibility of the join to fail.
289In our test database, we can see that there's another author who we haven't
292 >>> select c $ authorName <$> each authorSchema
293 ["Ollie","Bryan O'Sullivan","Emily Pillmore"]
295Emily wasn't returned in our earlier query because - in our database - she
296doesn't have any registered projects. We can account for this partiality in our
297original query by wrapping the ``projectsForAuthor`` call with ``optional``::
300 mapM_ print =<< select c do
301 author <- each authorSchema
302 mproject <- optional $ projectsForAuthor author
303 return (authorName author, projectName <$> mproject)
305 ("Ollie",Just "rel8")
306 ("Bryan O'Sullivan",Just "aeson")
307 ("Bryan O'Sullivan",Just "text")
308 ("Emily Pillmore",Nothing)
314Aggregations are operations like ``sum`` and ``count`` - operations that reduce
315multiple rows to single values. To perform aggregations in Rel8, we can use the
316``aggregate`` function, which takes a ``Query`` of aggregated expressions, runs
317the aggregation, and returns aggregated rows.
319To start, let's look at a simple aggregation that tells us how many projects
326Rel8 is also capable of aggregating multiple rows into a single row by
327concatenating all rows as a list. This aggregation allows us to break free of
328the row-orientated nature of SQL and write queries that return tree-like
329structures. Earlier we saw an example of returning authors with their projects,
330but the query didn't do a great job of describing the one-to-many relationship
331between authors and their projects.
333Let's look again at a query that returns authors and their projects, and
334focus on the /type/ of that query::
336 projectsForAuthor a = each projectSchema >>= filter \p ->
337 projectAuthorId p ==. authorId a
339 let authorsAndProjects = do
340 author <- each authorSchema
341 project <- projectsForAuthor author
342 return (author, project)
345 >>> :t select c authorsAndProjects
346 select c authorsAndProjects
347 :: MonadIO m => m [(Author Identity, Project Identity)]
350Our query gives us a single list of pairs of authors and projects. However,
351with our domain knowledge of the schema, this isn't a great type - what we'd
352rather have is a list of pairs of authors and /lists/ of projects. That is,
355 [(Author Identity, [Project Identity])]
357This would be a much better type! Rel8 can produce a query with this type by
358simply wrapping the call to ``projectsForAuthor`` with either ``some`` or
359``many``. Here we'll use ``many``, which allows for the possibility of an
360author to have no projects::
363 mapM_ print =<< select c do
364 author <- each authorSchema
365 projectNames <- many $ projectName <$> projectsForAuthor author
366 return (authorName author, projectNames)
369 ("Bryan O'Sullivan",["aeson","text"])
370 ("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