1{-# language DuplicateRecordFields #-}
20 -- *** Deriving-via helpers
28 -- *** @TypeInformation@
31 , parseTypeInformation
33 -- ** The @DBType@ hierarchy
40 -- * Tables and higher-kinded tables
42 , Column, Field, Necessity( Required, Optional )
51 , EqTable, (==:), (/=:)
52 , OrdTable, ascTable, descTable
59 , maybeTable, ($?), nothingTable, justTable
60 , isNothingTable, isJustTable
68 , eitherTable, leftTable, rightTable
69 , isLeftTable, isRightTable
73 , bitraverseEitherTable
77 , theseTable, thisTable, thatTable, thoseTable
78 , isThisTable, isThatTable, isThoseTable
79 , hasHereTable, hasThereTable
80 , justHereTable, justThereTable
82 , keepHereTable, loseHereTable
83 , keepThereTable, loseThereTable
84 , keepThisTable, loseThisTable
85 , keepThatTable, loseThatTable
86 , keepThoseTable, loseThoseTable
88 , bitraverseTheseTable
102 , namesFromLabelsWith
121 -- ** Boolean operations
126 , (==.), (/=.), (==?), (/=?)
132 , (<.), (<=.), (>.), (>=.)
133 , (<?), (<=?), (>?), (>=?)
134 , leastExpr, greatestExpr
160 -- ** @LIMIT@/@OFFSET@
187 , listAgg, listAggExpr
188 , nonEmptyAgg, nonEmptyAggExpr
192 , DBSum, sum, sumWhere
193 , DBString, stringAgg
203 , distributeAggregate
217 -- * Running statements
244 -- These need organizing, but are reachable from Rel8's documentation so we
245 -- do need to export and document them.
259import Rel8.Expr.Aggregate
262import Rel8.Expr.Function
265import Rel8.Expr.Order
266import Rel8.Expr.Serialize
269import Rel8.Query.Aggregate
270import Rel8.Query.Distinct
271import Rel8.Query.Each
272import Rel8.Query.Either
273import Rel8.Query.Exists
274import Rel8.Query.Filter
275import Rel8.Query.Limit
276import Rel8.Query.List
277import Rel8.Query.Maybe
278import Rel8.Query.Null
279import Rel8.Query.Order
280import Rel8.Query.SQL (showQuery)
282import Rel8.Query.These
283import Rel8.Query.Values
284import Rel8.Schema.Column
285import Rel8.Schema.Context.Label
286import Rel8.Schema.Generic
287import Rel8.Schema.HTable
288import Rel8.Schema.Name
289import Rel8.Schema.Nullability
290import Rel8.Schema.Table
291import Rel8.Statement.Delete
292import Rel8.Statement.Insert
293import Rel8.Statement.Returning
294import Rel8.Statement.Select
295import Rel8.Statement.Update
296import Rel8.Statement.View
298import Rel8.Table.Aggregate
299import Rel8.Table.Alternative
300import Rel8.Table.Bool
301import Rel8.Table.Either
303import Rel8.Table.Insert
304import Rel8.Table.List
305import Rel8.Table.Maybe
306import Rel8.Table.Name
307import Rel8.Table.NonEmpty
309import Rel8.Table.Order
310import Rel8.Table.Serialize
311import Rel8.Table.These
314import Rel8.Type.Information
315import Rel8.Type.JSONEncoded
316import Rel8.Type.JSONBEncoded
317import Rel8.Type.Monoid
320import Rel8.Type.ReadShow
321import Rel8.Type.Semigroup
322import Rel8.Type.String
324import Rel8.Expr.Opaleye (unsafeCastExpr)
325import Rel8.Kind.Necessity
326import Rel8.Schema.Field (Field)
331-- In this section, we'll take a look at using Rel8 to work with a small
332-- database for Haskell packages. We'll take a look at idiomatic usage of Rel8,
333-- mapping tables to Haskell, and then look at writing some simple queries.
335-- The documentation in this library uses the following language extensions:
337-- >>> :set -XBlockArguments
338-- >>> :set -XDeriveAnyClass
339-- >>> :set -XDeriveGeneric
340-- >>> :set -XDerivingStrategies
341-- >>> :set -XDerivingVia
342-- >>> :set -XDuplicateRecordFields
343-- >>> :set -XGeneralizedNewtypeDeriving
344-- >>> :set -XOverloadedStrings
345-- >>> :set -XStandaloneDeriving
346-- >>> :set -XTypeApplications
347-- >>> :set -XTypeFamilies
349-- Before we start writing any Haskell, let's take a look at the schema we'll
354-- > Column │ Type │ Nullable Column │ Type │ Nullable
355-- > ═══════════╪═════════╪══════════ ═══════════╪═════════╪══════════
356-- > author_id │ integer │ not null author_id │ integer │ not null
357-- > name │ text │ not null name │ text │ not null
360-- Our schema consists of two tables - @author@ and @project@. A @project@
361-- always has an @author@, but not all @author@s have projects. Each @author@
362-- has a name and (maybe) an associated website, and each project has a name.
364-- Now that we've seen our schema, we can begin writing a mapping in Rel8. The
365-- idiomatic way to map a table is to use a record that is parameterised what
366-- Rel8 calls an /interpretation functor/, and to define each field with
367-- 'Column'. For this type to be usable with Rel8 we need it to be an instance
368-- of 'HigherKindedTable', which can be derived with a combination of
369-- @DeriveAnyClass@ and @DeriveGeneric@.
371-- Following these steps for @author@, we have:
373-- > data Author f = Author
374-- > { authorId :: Column f Int64
375-- > , name :: Column f Text
376-- > , url :: Column f (Maybe Text)
377-- > } deriving (Generic, HigherKindedTable)
379-- This is a perfectly reasonable definition, but cautious readers might notice
380-- a problem - in particular, with the type of the @authorId@ field. While
381-- @Int64@ is correct, it's not the best type. If we had other identifier types
382-- in our project, it would be too easy to accidentally mix them up and create
383-- nonsensical joins. As Haskell programmers, we often solve this problem by
384-- creating @newtype@ wrappers, and we can also use this technique with Rel8:
387-- newtype AuthorId = AuthorId { toInt64 :: Int64 }
388-- deriving newtype (DBEq, DBType, Eq, Show)
391-- Now we can write our final schema mapping. First, the @author@ table:
394-- data Author f = Author
395-- { authorId :: Column f AuthorId
396-- , authorName :: Column f Text
397-- , authorUrl :: Column f (Maybe Text)
399-- deriving stock Generic
400-- deriving anyclass HigherKindedTable
403-- And similarly, the @project@ table:
406-- data Project f = Project
407-- { projectAuthorId :: Column f AuthorId
408-- , projectName :: Column f Text
410-- deriving stock Generic
411-- deriving anyclass HigherKindedTable
414-- To show query results in this documentation, we'll also need @Show@
415-- instances: Unfortunately these definitions look a bit scary, but they are
416-- essentially just @deriving (Show)@:
418-- >>> deriving stock instance f ~ Identity => Show (Author f)
419-- >>> deriving stock instance f ~ Identity => Show (Project f)
421-- These data types describe the structural mapping of the tables, but we also
422-- need to specify a 'TableSchema' for each table. A @TableSchema@ contains the
423-- name of the table and the name of all columns in the table, which will
424-- ultimately allow us to @SELECT@ and @INSERT@ rows for these tables.
426-- To define a @TableSchema@, we just need to fill construct appropriate
427-- @TableSchema@ values. When it comes to the @tableColumns@ field, we
428-- construct values of our data types above, and set each field to the name of
429-- the column that it maps to:
431-- First, @authorSchema@ describes the column names of the @author@ table when
432-- associated with the @Author@ type:
435-- authorSchema :: TableSchema (Author ColumnSchema)
436-- authorSchema = TableSchema
437-- { tableName = "author"
438-- , tableSchema = Nothing
439-- , tableColumns = Author
440-- { authorId = "author_id"
441-- , authorName = "name"
442-- , authorUrl = "url"
447-- And likewise for @project@ and @Project@:
450-- projectSchema :: TableSchema (Project ColumnSchema)
451-- projectSchema = TableSchema
452-- { tableName = "project"
453-- , tableSchema = Nothing
454-- , tableColumns = Project
455-- { projectAuthorId = "author_id"
456-- , projectName = "name"
461-- Aside: you might be wondering why this information isn't in the definitions
462-- of @Author@ and @Project@ above. Rel8 decouples @TableSchema@ from the data
463-- types themselves, as not all tables you define will necessarily have a
464-- schema. For example, Rel8 allows you to define helper types to simplify the
465-- types of queries - these tables only exist at query time, but there is no
466-- corresponding base table. We'll see more on this idea later!
468-- With these table definitions, we can now start writing some queries! To
469-- actually run a query, we'll need a database connection. Rel8 uses the
470-- @hasql@ library, and for this documentation we'll get the connection string
471-- from the @$TEST_DATABASE_URL@ environment variable.
473-- >>> connectionString <- System.Environment.getEnv "TEST_DATABASE_URL"
474-- >>> Right c <- Hasql.Connection.acquire (Data.ByteString.Char8.pack connectionString)
475-- >>> Control.Monad.void $ Hasql.Session.run (Hasql.Session.sql "BEGIN") c
479-- First, we'll take a look at @SELECT@ statements - usually the bulk of most
480-- database heavy applications.
482-- In Rel8, @SELECT@ statements are built using the 'Query' monad. You can
483-- think of this monad like the ordinary @[]@ (List) monad - but this isn't
484-- required knowledge.
486-- To start, we'll look at one of the simplest queries possible - a basic
487-- @SELECT * FROM@ statement. To select all rows from a table, we use 'each',
488-- and supply a 'TableSchema'. So to select all @project@ rows, we can write:
490-- >>> :t each projectSchema
491-- each projectSchema :: Query (Project Expr)
493-- Notice that @each@ gives us a @Query@ that yields @Project Expr@ rows. To
494-- see what this means, let's have a look at a single field of a
497-- >>> let aProjectExpr = undefined :: Project Expr
498-- >>> :t projectAuthorId aProjectExpr
499-- projectAuthorId aProjectExpr :: Expr AuthorId
501-- Recall we defined @projectAuthorId@ as @Column f AuthorId@. Now we have @f@
502-- is @Expr@, and @Column Expr AuthorId@ reduces to @Expr AuthorId@. We'll see
503-- more about @Expr@ soon, but you can think of @Expr a@ as "SQL expressions of
506-- To execute this @Query@, we pass it to 'select':
508-- >>> :t select c (each projectSchema)
509-- select c (each projectSchema) :: MonadIO m => m [Project Identity]
511-- When we @select@ things containing @Expr@s, Rel8 builds a new response table
512-- with the @Identity@ interpretation. This means you'll get back plain Haskell
513-- values. Studying @projectAuthorId@ again, we have:
515-- >>> let aProjectIdentity = undefined :: Project Identity
516-- >>> :t projectAuthorId aProjectIdentity
517-- projectAuthorId aProjectIdentity :: AuthorId
519-- Here @Column Identity AuthorId@ reduces to just @AuthorId@, with no
520-- wrappping type at all.
522-- Putting this all together, we can run our first query:
524-- >>> select c (each projectSchema) >>= mapM_ print
525-- Project {projectAuthorId = 1, projectName = "rel8"}
526-- Project {projectAuthorId = 2, projectName = "aeson"}
527-- Project {projectAuthorId = 2, projectName = "text"}
531-- We now know that 'each' is the equivalent of a @SELECT *@ query, but
532-- sometimes we're only interested in a subset of the columns of a table. To
533-- restrict the returned columns, we can specify a projection by using 'Query's
534-- @Functor@ instance:
536-- >>> select c $ projectName <$> each projectSchema
537-- ["rel8","aeson","text"]
541-- Another common operation in relational databases is to take the @JOIN@ of
542-- multiple tables. Rel8 doesn't have a specific join operation, but we can
543-- recover the functionality of a join by selecting all rows of two tables, and
544-- then using 'where_' to filter them.
546-- To see how this works, first let's look at taking the product of two tables.
547-- We can do this by simply calling 'each' twice, and then returning a tuple of
551-- mapM_ print =<< select c do
552-- author <- each authorSchema
553-- project <- each projectSchema
554-- return (projectName project, authorName author)
557-- ("rel8","Bryan O'Sullivan")
558-- ("rel8","Emily Pillmore")
560-- ("aeson","Bryan O'Sullivan")
561-- ("aeson","Emily Pillmore")
563-- ("text","Bryan O'Sullivan")
564-- ("text","Emily Pillmore")
566-- This isn't quite right, though, as we have ended up pairing up the wrong
567-- projects and authors. To fix this, we can use 'where_' to restrict the
568-- returned rows. We could write:
571-- > author <- each authorSchema
572-- > project <- each projectSchema
573-- > where_ $ projectAuthorId project ==. authorId author
574-- > return (project, author)
576-- but doing this every time you need a join can obscure the meaning of the
577-- query you're writing. A good practice is to introduce specialised functions
578-- for the particular joins in your database. In our case, this would be:
581-- projectsForAuthor :: Author Expr -> Query (Project Expr)
582-- projectsForAuthor a = each projectSchema >>= filter \p ->
583-- projectAuthorId p ==. authorId a
586-- Our final query is then:
589-- mapM_ print =<< select c do
590-- author <- each authorSchema
591-- project <- projectsForAuthor author
592-- return (projectName project, authorName author)
595-- ("aeson","Bryan O'Sullivan")
596-- ("text","Bryan O'Sullivan")
600-- Rel8 is also capable of performing @LEFT JOIN@s. To perform @LEFT JOIN@s, we
601-- follow the same approach as before, but use the 'optional' query transformer
602-- to allow for the possibility of the join to fail.
604-- In our test database, we can see that there's another author who we haven't
607-- >>> select c $ authorName <$> each authorSchema
608-- ["Ollie","Bryan O'Sullivan","Emily Pillmore"]
610-- Emily wasn't returned in our earlier query because - in our database - she
611-- doesn't have any registered projects. We can account for this partiality in
612-- our original query by wrapping the @projectsForAuthor@ call with 'optional':
615-- mapM_ print =<< select c do
616-- author <- each authorSchema
617-- mproject <- optional $ projectsForAuthor author
618-- return (authorName author, projectName <$> mproject)
620-- ("Ollie",Just "rel8")
621-- ("Bryan O'Sullivan",Just "aeson")
622-- ("Bryan O'Sullivan",Just "text")
623-- ("Emily Pillmore",Nothing)
627-- Aggregations are operations like @sum@ and @count@ - operations that reduce
628-- multiple rows to single values. To perform aggregations in Rel8, we can use
629-- the 'aggregate' function, which takes a 'Query' of aggregated expressions,
630-- runs the aggregation, and returns aggregated rows.
632-- To start, let's look at a simple aggregation that tells us how many projects
637-- Rel8 is also capable of aggregating multiple rows into a single row by
638-- concatenating all rows as a list. This aggregation allows us to break free
639-- of the row-orientated nature of SQL and write queries that return tree-like
640-- structures. Earlier we saw an example of returning authors with their
641-- projects, but the query didn't do a great job of describing the one-to-many
642-- relationship between authors and their projects.
644-- Let's look again at a query that returns authors and their projects, and
645-- focus on the /type/ of that query.
648-- projectsForAuthor a = each projectSchema >>= filter \p ->
649-- projectAuthorId p ==. authorId a
653-- let authorsAndProjects = do
654-- author <- each authorSchema
655-- project <- projectsForAuthor author
656-- return (author, project)
660-- >>> :t select c authorsAndProjects
661-- select c authorsAndProjects
662-- :: MonadIO m => m [(Author Identity, Project Identity)]
664-- Our query gives us a single list of pairs of authors and projects. However,
665-- with our domain knowledge of the schema, this isn't a great type - what we'd
666-- rather have is a list of pairs of authors and /lists/ of projects. That is,
669-- > [(Author Identity, [Project Identity])]
671-- This would be a much better type! Rel8 can produce a query with this type by
672-- simply wrapping the call to @projectsForAuthor@ with either 'some' or
673-- 'many'. Here we'll use 'many', which allows for the possibility of an author
674-- to have no projects:
677-- mapM_ print =<< select c do
678-- author <- each authorSchema
679-- projectNames <- many $ projectName <$> projectsForAuthor author
680-- return (authorName author, projectNames)
683-- ("Bryan O'Sullivan",["aeson","text"])
684-- ("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