1{-# language DuplicateRecordFields #-}
20 -- *** Deriving-via helpers
28 -- *** @TypeInformation@
31 , parseTypeInformation
33 -- ** The @DBType@ hierarchy
39 -- * Tables and higher-kinded tables
42 , EqTable, (==:), (/=:)
43 , OrdTable, ascTable, descTable
50 , maybeTable, nothingTable, justTable
51 , isNothingTable, isJustTable
59 , eitherTable, leftTable, rightTable
60 , isLeftTable, isRightTable
64 , bitraverseEitherTable
68 , theseTable, thisTable, thatTable, thoseTable
69 , isThisTable, isThatTable, isThoseTable
70 , hasHereTable, hasThereTable
71 , justHereTable, justThereTable
73 , keepHereTable, loseHereTable
74 , keepThereTable, loseThereTable
75 , keepThisTable, loseThisTable
76 , keepThatTable, loseThatTable
77 , keepThoseTable, loseThoseTable
79 , bitraverseTheseTable
104 -- ** Boolean operations
108 , (==.), (/=.), (==?), (/=?)
114 , (<.), (<=.), (>.), (>=.)
115 , (<?), (<=?), (>?), (>=?)
116 , leastExpr, greatestExpr
141 -- ** @LIMIT@/@OFFSET@
173 , DBString, stringAgg
192 -- * Running statements
216 -- These need organizing, but are reachable from Rel8's documentation so we
217 -- do need to export and document them.
229import Rel8.Type.Information
231import Rel8.Type.Semigroup
232import Rel8.Type.Monoid
238import Rel8.Expr.Function
240import Rel8.Query.SQL (showQuery)
241import Rel8.Query.Each
242import Rel8.Query.Values
243import Rel8.Query.Filter
244import Rel8.Query.Exists
245import Rel8.Query.Distinct
246import Rel8.Query.Limit
248import Rel8.Table.Maybe
249import Rel8.Query.Maybe
251import Rel8.Query.Aggregate
252import Rel8.Query.List
253import Rel8.Table.NonEmpty
254import Rel8.Table.List
255import Rel8.Query.Order
257import Rel8.Table.Serialize
258import Rel8.Statement.Select
259import Rel8.Statement.Insert
260import Rel8.Statement.Delete
261import Rel8.Statement.Returning
262import Rel8.Statement.Update
263import Rel8.Table.Aggregate
264import Rel8.Expr.Aggregate
265import Rel8.Type.String
268import Rel8.Query.Null
269import Rel8.Expr.Order
270import Rel8.Schema.Nullability
271import Rel8.Schema.HTable
272import Rel8.Schema.Context.Label
273import Rel8.Schema.Table
274import Rel8.Table.These
275import Rel8.Query.These
276import Rel8.Table.Recontextualize
278import Rel8.Table.Alternative
279import Rel8.Table.Either
280import Rel8.Table.Bool
281import Rel8.Query.Either
284import Rel8.Table.Order
285import Rel8.Expr.Serialize
286import Rel8.Table.Insert
291-- In this section, we'll take a look at using Rel8 to work with a small
292-- database for Haskell packages. We'll take a look at idiomatic usage of Rel8,
293-- mapping tables to Haskell, and then look at writing some simple queries.
295-- The documentation in this library uses the following language extensions:
297-- >>> :set -XBlockArguments
298-- >>> :set -XDeriveAnyClass
299-- >>> :set -XDeriveGeneric
300-- >>> :set -XDerivingStrategies
301-- >>> :set -XDerivingVia
302-- >>> :set -XDuplicateRecordFields
303-- >>> :set -XGeneralizedNewtypeDeriving
304-- >>> :set -XOverloadedStrings
305-- >>> :set -XStandaloneDeriving
306-- >>> :set -XTypeApplications
307-- >>> :set -XTypeFamilies
309-- Before we start writing any Haskell, let's take a look at the schema we'll
314-- > Column │ Type │ Nullable Column │ Type │ Nullable
315-- > ═══════════╪═════════╪══════════ ═══════════╪═════════╪══════════
316-- > author_id │ integer │ not null author_id │ integer │ not null
317-- > name │ text │ not null name │ text │ not null
320-- Our schema consists of two tables - @author@ and @project@. A @project@
321-- always has an @author@, but not all @author@s have projects. Each @author@
322-- has a name and (maybe) an associated website, and each project has a name.
324-- Now that we've seen our schema, we can begin writing a mapping in Rel8. The
325-- idiomatic way to map a table is to use a record that is parameterised what
326-- Rel8 calls an /interpretation functor/, and to define each field with
327-- 'Column'. For this type to be usable with Rel8 we need it to be an instance
328-- of 'HigherKindedTable', which can be derived with a combination of
329-- @DeriveAnyClass@ and @DeriveGeneric@.
331-- Following these steps for @author@, we have:
333-- > data Author f = Author
334-- > { authorId :: Column f Int64
335-- > , name :: Column f Text
336-- > , url :: Column f (Maybe Text)
337-- > } deriving (Generic, HigherKindedTable)
339-- This is a perfectly reasonable definition, but cautious readers might notice
340-- a problem - in particular, with the type of the @authorId@ field. While
341-- @Int64@ is correct, it's not the best type. If we had other identifier types
342-- in our project, it would be too easy to accidentally mix them up and create
343-- nonsensical joins. As Haskell programmers, we often solve this problem by
344-- creating @newtype@ wrappers, and we can also use this technique with Rel8:
347-- newtype AuthorId = AuthorId { toInt64 :: Int64 }
348-- deriving newtype (DBEq, DBType, Eq, Show)
351-- Now we can write our final schema mapping. First, the @author@ table:
354-- data Author f = Author
355-- { authorId :: Column f AuthorId
356-- , authorName :: Column f Text
357-- , authorUrl :: Column f (Maybe Text)
359-- deriving stock Generic
360-- deriving anyclass HigherKindedTable
363-- And similarly, the @project@ table:
366-- data Project f = Project
367-- { projectAuthorId :: Column f AuthorId
368-- , projectName :: Column f Text
370-- deriving stock Generic
371-- deriving anyclass HigherKindedTable
374-- To show query results in this documentation, we'll also need @Show@
375-- instances: Unfortunately these definitions look a bit scary, but they are
376-- essentially just @deriving (Show)@:
378-- >>> deriving stock instance f ~ Identity => Show (Author f)
379-- >>> deriving stock instance f ~ Identity => Show (Project f)
381-- These data types describe the structural mapping of the tables, but we also
382-- need to specify a 'TableSchema' for each table. A @TableSchema@ contains the
383-- name of the table and the name of all columns in the table, which will
384-- ultimately allow us to @SELECT@ and @INSERT@ rows for these tables.
386-- To define a @TableSchema@, we just need to fill construct appropriate
387-- @TableSchema@ values. When it comes to the @tableColumns@ field, we
388-- construct values of our data types above, and set each field to the name of
389-- the column that it maps to:
391-- First, @authorSchema@ describes the column names of the @author@ table when
392-- associated with the @Author@ type:
395-- authorSchema :: TableSchema (Author ColumnSchema)
396-- authorSchema = TableSchema
397-- { tableName = "author"
398-- , tableSchema = Nothing
399-- , tableColumns = Author
400-- { authorId = "author_id"
401-- , authorName = "name"
402-- , authorUrl = "url"
407-- And likewise for @project@ and @Project@:
410-- projectSchema :: TableSchema (Project ColumnSchema)
411-- projectSchema = TableSchema
412-- { tableName = "project"
413-- , tableSchema = Nothing
414-- , tableColumns = Project
415-- { projectAuthorId = "author_id"
416-- , projectName = "name"
421-- Aside: you might be wondering why this information isn't in the definitions
422-- of @Author@ and @Project@ above. Rel8 decouples @TableSchema@ from the data
423-- types themselves, as not all tables you define will necessarily have a
424-- schema. For example, Rel8 allows you to define helper types to simplify the
425-- types of queries - these tables only exist at query time, but there is no
426-- corresponding base table. We'll see more on this idea later!
428-- With these table definitions, we can now start writing some queries! To
429-- actually run a query, we'll need a database connection. Rel8 uses the
430-- @hasql@ library, and for this documentation we'll get the connection string
431-- from the @$TEST_DATABASE_URL@ environment variable.
433-- >>> connectionString <- System.Environment.getEnv "TEST_DATABASE_URL"
434-- >>> Right c <- Hasql.Connection.acquire (Data.ByteString.Char8.pack connectionString)
435-- >>> Control.Monad.void $ Hasql.Session.run (Hasql.Session.sql "BEGIN") c
439-- First, we'll take a look at @SELECT@ statements - usually the bulk of most
440-- database heavy applications.
442-- In Rel8, @SELECT@ statements are built using the 'Query' monad. You can
443-- think of this monad like the ordinary @[]@ (List) monad - but this isn't
444-- required knowledge.
446-- To start, we'll look at one of the simplest queries possible - a basic
447-- @SELECT * FROM@ statement. To select all rows from a table, we use 'each',
448-- and supply a 'TableSchema'. So to select all @project@ rows, we can write:
450-- >>> :t each projectSchema
451-- each projectSchema :: Query (Project Expr)
453-- Notice that @each@ gives us a @Query@ that yields @Project Expr@ rows. To
454-- see what this means, let's have a look at a single field of a
457-- >>> let aProjectExpr = undefined :: Project Expr
458-- >>> :t projectAuthorId aProjectExpr
459-- projectAuthorId aProjectExpr :: Expr AuthorId
461-- Recall we defined @projectAuthorId@ as @Column f AuthorId@. Now we have @f@
462-- is @Expr@, and @Column Expr AuthorId@ reduces to @Expr AuthorId@. We'll see
463-- more about @Expr@ soon, but you can think of @Expr a@ as "SQL expressions of
466-- To execute this @Query@, we pass it to 'select':
468-- >>> :t select c (each projectSchema)
469-- select c (each projectSchema) :: MonadIO m => m [Project Identity]
471-- When we @select@ things containing @Expr@s, Rel8 builds a new response table
472-- with the @Identity@ interpretation. This means you'll get back plain Haskell
473-- values. Studying @projectAuthorId@ again, we have:
475-- >>> let aProjectIdentity = undefined :: Project Identity
476-- >>> :t projectAuthorId aProjectIdentity
477-- projectAuthorId aProjectIdentity :: AuthorId
479-- Here @Column Identity AuthorId@ reduces to just @AuthorId@, with no
480-- wrappping type at all.
482-- Putting this all together, we can run our first query:
484-- >>> select c (each projectSchema) >>= mapM_ print
485-- Project {projectAuthorId = 1, projectName = "rel8"}
486-- Project {projectAuthorId = 2, projectName = "aeson"}
487-- Project {projectAuthorId = 2, projectName = "text"}
491-- We now know that 'each' is the equivalent of a @SELECT *@ query, but
492-- sometimes we're only interested in a subset of the columns of a table. To
493-- restrict the returned columns, we can specify a projection by using 'Query's
494-- @Functor@ instance:
496-- >>> select c $ projectName <$> each projectSchema
497-- ["rel8","aeson","text"]
501-- Another common operation in relational databases is to take the @JOIN@ of
502-- multiple tables. Rel8 doesn't have a specific join operation, but we can
503-- recover the functionality of a join by selecting all rows of two tables, and
504-- then using 'where_' to filter them.
506-- To see how this works, first let's look at taking the product of two tables.
507-- We can do this by simply calling 'each' twice, and then returning a tuple of
511-- mapM_ print =<< select c do
512-- author <- each authorSchema
513-- project <- each projectSchema
514-- return (projectName project, authorName author)
517-- ("rel8","Bryan O'Sullivan")
518-- ("rel8","Emily Pillmore")
520-- ("aeson","Bryan O'Sullivan")
521-- ("aeson","Emily Pillmore")
523-- ("text","Bryan O'Sullivan")
524-- ("text","Emily Pillmore")
526-- This isn't quite right, though, as we have ended up pairing up the wrong
527-- projects and authors. To fix this, we can use 'where_' to restrict the
528-- returned rows. We could write:
531-- > author <- each authorSchema
532-- > project <- each projectSchema
533-- > where_ $ projectAuthorId project ==. authorId author
534-- > return (project, author)
536-- but doing this every time you need a join can obscure the meaning of the
537-- query you're writing. A good practice is to introduce specialised functions
538-- for the particular joins in your database. In our case, this would be:
541-- projectsForAuthor :: Author Expr -> Query (Project Expr)
542-- projectsForAuthor a = each projectSchema >>= filter \p ->
543-- projectAuthorId p ==. authorId a
546-- Our final query is then:
549-- mapM_ print =<< select c do
550-- author <- each authorSchema
551-- project <- projectsForAuthor author
552-- return (projectName project, authorName author)
555-- ("aeson","Bryan O'Sullivan")
556-- ("text","Bryan O'Sullivan")
560-- Rel8 is also capable of performing @LEFT JOIN@s. To perform @LEFT JOIN@s, we
561-- follow the same approach as before, but use the 'optional' query transformer
562-- to allow for the possibility of the join to fail.
564-- In our test database, we can see that there's another author who we haven't
567-- >>> select c $ authorName <$> each authorSchema
568-- ["Ollie","Bryan O'Sullivan","Emily Pillmore"]
570-- Emily wasn't returned in our earlier query because - in our database - she
571-- doesn't have any registered projects. We can account for this partiality in
572-- our original query by wrapping the @projectsForAuthor@ call with 'optional':
575-- mapM_ print =<< select c do
576-- author <- each authorSchema
577-- mproject <- optional $ projectsForAuthor author
578-- return (authorName author, projectName <$> mproject)
580-- ("Ollie",Just "rel8")
581-- ("Bryan O'Sullivan",Just "aeson")
582-- ("Bryan O'Sullivan",Just "text")
583-- ("Emily Pillmore",Nothing)
587-- Aggregations are operations like @sum@ and @count@ - operations that reduce
588-- multiple rows to single values. To perform aggregations in Rel8, we can use
589-- the 'aggregate' function, which takes a 'Query' of aggregated expressions,
590-- runs the aggregation, and returns aggregated rows.
592-- To start, let's look at a simple aggregation that tells us how many projects
597-- Rel8 is also capable of aggregating multiple rows into a single row by
598-- concatenating all rows as a list. This aggregation allows us to break free
599-- of the row-orientated nature of SQL and write queries that return tree-like
600-- structures. Earlier we saw an example of returning authors with their
601-- projects, but the query didn't do a great job of describing the one-to-many
602-- relationship between authors and their projects.
604-- Let's look again at a query that returns authors and their projects, and
605-- focus on the /type/ of that query.
608-- projectsForAuthor a = each projectSchema >>= filter \p ->
609-- projectAuthorId p ==. authorId a
613-- let authorsAndProjects = do
614-- author <- each authorSchema
615-- project <- projectsForAuthor author
616-- return (author, project)
620-- >>> :t select c authorsAndProjects
621-- select c authorsAndProjects
622-- :: MonadIO m => m [(Author Identity, Project Identity)]
624-- Our query gives us a single list of pairs of authors and projects. However,
625-- with our domain knowledge of the schema, this isn't a great type - what we'd
626-- rather have is a list of pairs of authors and /lists/ of projects. That is,
629-- > [(Author Identity, [Project Identity])]
631-- This would be a much better type! Rel8 can produce a query with this type by
632-- simply wrapping the call to @projectsForAuthor@ with either 'some' or
633-- 'many'. Here we'll use 'many', which allows for the possibility of an author
634-- to have no projects:
637-- mapM_ print =<< select c do
638-- author <- each authorSchema
639-- projectNames <- many $ projectName <$> projectsForAuthor author
640-- return (authorName author, projectNames)
643-- ("Bryan O'Sullivan",["aeson","text"])
644-- ("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