git.iko.soy/squeal
on dev help sign in
squeal/RELEASE NOTES.md
1217 lines 40 kB history raw
1## RELEASE NOTES
3### Version 0.5
5Version 0.5 makes a number of large changes and additions to Squeal.
6I want to thank folks who contributed issues and pull requests;
7Raveline, rimmington, ilyakooo0, chshersh, reygoch, and more.
9**Multi-schema support**
11Previous versions of Squeal only supported definitions in the "public"
12schema. Squeal 0.5 adds support for multiple schemas with the new kind
14```Haskell
15type SchemasType = [(Symbol,SchemaType)]
16```
18In order to upgrade your queries, manipulations and definitions from
19Squeal 0.4, you will have to apply the `Public` type family, which indicates
20that your only schema is the "public" schema.
22```Haskell
23-- Squeal 0.4
24setup :: Definition '[] Schema
26-- Squeal 0.5
27setup :: Definition (Public '[]) (Public Schema)
28```
30You can create non-public schemas using `createSchema` and inversely
31remove them using `dropSchema`. There is also an idempotent
32`createSchemaIfNotExists`.
34In order to handle aliases which may refer to non-public schemas, Squeal 0.5
35introduces `QualifiedAlias`es. A `QualifiedAlias` can be referred to using
36the `(!)` operator from the `IsQualified` typeclass; but if you want to refer
37to an alias from the "public" schema, you can continue to use a single
38overloaded label, no need to write `#public ! #tab`, just write `#tab`.
40**Top-level statements**
42As a consequence of multi-schema support, common table expressions that
43a `Query` or `Manipulation` may refer to had to be broken into a new parameter.
44Additionally, `Query` gained another new parameter for its outer scope which
45will be discussed in the next section.
47To simplify type signatures that you have to write for top-level queries
48and manipulations, Squeal 0.5 introduces the `Query_` and `Manipulation_`
49type families.
51```
52type family Query_
53 (schemas :: SchemasType)
54 (parameters :: Type)
55 (row :: Type) where
56 Query_ schemas params row =
57 Query '[] '[] schemas (TuplePG params) (RowPG row)
58```
60As you see, a top-level `Query_` has no outer scope and no common
61table expressions in scope, they are empty `'[]`. Moreover,
62its parameters and row parameters are now `Type`s, which are
63converted to corresponding Postgres types using the `TuplePG`
64and `RowPG` type families. This means you can write the type signatures
65for your top-level statements using the Haskell types that you
66intend to input and retrieve when running the statements. Similar
67to `Query_`, there is a `Manipulation_` type, the only difference
68being that a general `Manipulation` doesn't have an outer scope,
69only a scope for common table expressions.
71**Subquery expressions**
73Previous versions of Squeal offered subquery expression support
74but there were issues. The common use case of using the `IN` operator
75from SQL was overly complex. There was a proliferation of subquery
76functions to handle different comparison operators, each with two versions
77for comparing `ALL` or `ANY` of the query rows. And, most confoundedly,
78there was no way to support the `EXISTS` subquery because the "outer scope"
79for a query was not available.
81Squeal adds an outer scope to the `Query` type.
83```Haskell
84newtype Query
85 (outer :: FromType)
86 (commons :: FromType)
87 (schemas :: SchemasType)
88 (params :: [NullityType])
89 (row :: RowType)
90 = UnsafeQuery { renderQuery :: ByteString }
91```
93This enables a well-typed `exists` function.
95```Haskell
96exists
97 :: Query (Join outer from) commons schemas params row
98 -> Condition outer commons grp schemas params from
99```
101The `in_` and `notIn` functions were simplified to handle the most
102common use case of comparing to a list of values.
104```Haskell
105in_
106 :: Expression outer commons grp schemas params from ty -- ^ expression
107 -> [Expression outer commons grp schemas params from ty]
108 -> Condition outer commons grp schemas params from
109```
111Finally, general subquery comparisons were abstracted to work with
112any comparison operators, using the `Operator` type which will be discussed in
113the next section.
115```Haskell
116subAll
117 :: Expression outer commons grp schemas params from ty1
118 -> Operator ty1 ty2 ('Null 'PGbool)
119 -> Query (Join outer from) commons schemas params '[col ::: ty2]
120 -> Condition outer commons grp schemas params from
122subAny
123 :: Expression outer commons grp schemas params from ty1
124 -> Operator ty1 ty2 ('Null 'PGbool)
125 -> Query (Join outer from) commons schemas params '[col ::: ty2]
126 -> Condition outer commons grp schemas params from
127```
129**Expression RankNTypes**
131Squeal 0.5 introduces RankNType type synonyms for common expression patterns.
132The simplest example is the `Expr` type, a type for "closed" expressions
133that cannot reference any aliases or parameters.
135```Haskell
136type Expr x
137 = forall outer commons grp schemas params from
138 . Expression outer commons grp schemas params from x
139```
141There is also a function type `(-->)`, which is a subtype of the usual Haskell function
142type `(->)`.
144```Haskell
145type (-->) x y
146 = forall outer commons grp schemas params from
147 . Expression outer commons grp schemas params from x
148 -> Expression outer commons grp schemas params from y
149```
151We saw in the subquery section that there is an `Operator` type.
153```Haskell
154type Operator x1 x2 y
155 = forall outer commons grp schemas params from
156 . Expression outer commons grp schemas params from x1
157 -> Expression outer commons grp schemas params from x2
158 -> Expression outer commons grp schemas params from y
159```
161There are also types `FunctionN` and `FunctionVar` for n-ary functions
162and variadic functions.
164```
165type FunctionN xs y
166 = forall outer commons grp schemas params from
167 . NP (Expression outer commons grp schemas params from) xs
168 -> Expression outer commons grp schemas params from y
169```
171An n-ary function takes an `NP` list of `Expression`s as its argument.
172Squeal 0.5 adds a helpful operator `(*:)`, to help scrap your `Nil`s.
173You can construct an `NP` list now by using the operator `(:*)`,
174until your last element, using `(*:)` there. For instance, the function
175`atan2_` takes two arguments, `atan2_ (pi *: 2)`.
177**Selections**
179Previously, Squeal provided a couple versions of `SELECT` depending
180on whether you wanted to select all columns of a unique table
181in the from clause, i.e. `*`, all columns of a particular table
182in the from clause, i.e. `.*`, or a list of columns from the from clause.
184Squeal 0.5 refactors this pattern into a `Selection` GADT type, allowing
185for abstraction and combinations that were not possible before.
187```Haskell
188data Selection outer commons grp schemas params from row where
189 Star
190 :: HasUnique tab from row
191 => Selection outer commons 'Ungrouped schemas params from row
192 DotStar
193 :: Has tab from row
194 => Alias tab
195 -> Selection outer commons 'Ungrouped schemas params from row
196 List
197 :: SListI row
198 => NP (Aliased (Expression outer commons grp schemas params from)) row
199 -> Selection outer commons grp schemas params from row
200 Over
201 :: SListI row
202 => NP (Aliased (WindowFunction outer commons grp schemas params from)) row
203 -> WindowDefinition outer commons grp schemas params from
204 -> Selection outer commons grp schemas params from row
205 Also
206 :: Selection outer commons grp schemas params from right
207 -> Selection outer commons grp schemas params from left
208 -> Selection outer commons grp schemas params from (Join left right)
209```
211In addition to the `Star`, `DotStar` and `List` constructors, there is an
212`Also` constructor combinator, which enables users to combine `Selection`s.
213Additionally, there is an `Over` constructor that is used to enable
214window functions, described in the next section.
216To upgrade from Squeal 0.4, you will replace `selectStar` with `select Star`,
217replace `selectDotStar #tab` with `select (DotStar #tab)`. The `List`
218selection is such a common use case that there is a function `select_` which
219automatically applies it, so to upgrade from Squeal 0.4, replace `select`
220with `select_`. There are also independent `selectDistinct` and `selectDistinct_`
221functions to filter out duplicate rows.
223**Query clauses**
225Previously, Squeal provided a couple versions of `INSERT` depending
226on whether you wanted to insert `VALUES` or a query.
228Squeal 0.5 refactors this pattern into a `QueryClause` GADT.
230```
231-- Squeal 0.4
232insertRow_ #tab (Set 2 `as` #col1 :* Default `as` #col2)
233insertQuery_ #tab (selectStar (from (table #other_tab)))
235-- Squeal 0.5
236insertInto_ #tab (Values_ (Set 2 `as` #col1 :* Default `as` #col2))
237insertInto_ #tab (Subquery (select Star (from (table #other_tab))))
238```
240**Window functions and aggregation**
242Previous versions of Squeal provided no support for window functions.
243Squeal 0.5 adds support for window functions. We saw `Over` in the
244previous section.
246```Haskell
247Over
248 :: SListI row
249 => NP (Aliased (WindowFunction outer commons grp schemas params from)) row
250 -> WindowDefinition outer commons grp schemas params from
251 -> Selection outer commons grp schemas params from row
252```
254`Over` combines window functions with window definitions. A `WindowDefinition`
255is constructed using the `partitionBy` function, optionally with an `orderBy`
256clause. For example,
258```Haskell
259query :: Query_ (Public Schema) () (Row Int32 Int32)
260query = select
261 (#col1 & Also (rank `as` #col2 `Over` (partitionBy #col1 & orderBy [#col2 & Asc])))
262 (from (table #tab))
263```
265Here the `rank` function is a `WindowFunction`
267```Haskell
268rank :: WinFun0 ('NotNull 'PGint8)
269rank = UnsafeWindowFunction "rank()"
270```
272`WinFun0` is a RankNType, like `Expr` was, used for no argument window functions.
273Similarly, there are also `WinFun1` and `WinFunN` types for
274window functions and n-ary window functions.
276In order to use the same syntax for certain window functions and aggregate functions,
277a new typeclass `Aggregate` was introduced. So for instance, `sum_` can be
278either a `Distinction` `Expression` or a `WindowFunction`, used either with
279a `groupBy` or with a `partitionBy` in the appropriate way.
281```
282data Distinction (expr :: kind -> Type) (ty :: kind)
283 = All (expr ty)
284 | Distinct (expr ty)
285```
287Aggregate functions can be run over all rows or only over distinct rows,
288while window functions bear no such distinction.
290**Migrations**
292Thanks to [https://github.com/Raveline](Raveline) Squeal 0.5 introduces
293a function `defaultMain` to easily create a command line program to
294run or rewind your migrations.
296Previously, Squeal's migrations were impure, allowing in addition to
297running `Definition`s to run arbitrary `IO` operations, such as inserting
298data into the database or printing out status messages. In order to
299provide compatibility with other migration systems, Squeal 0.5 introduces
300pure migrations, that is migrations that are only `Definition`s.
302```Haskell
303data Migration p schemas0 schemas1 = Migration
304 { name :: Text -- ^ The `name` of a `Migration`.
305 -- Each `name` in a `Migration` should be unique.
306 , up :: p schemas0 schemas1 -- ^ The `up` instruction of a `Migration`.
307 , down :: p schemas1 schemas0 -- ^ The `down` instruction of a `Migration`.
308 } deriving (GHC.Generic)
309```
311A pure migration is a `Migration Definition`, a pair of inverse
312`Definition`s with a unique name. To recover impure migrations, Squeal 0.5
313introduces the `Terminally` type.
315```Haskell
316newtype Terminally trans monad x0 x1 = Terminally
317 { runTerminally :: trans x0 x1 monad () }
318```
320`Terminally` applies the indexed monad transformer and the monad it transforms
321to the unit type `()`, thereby turning an indexed monad into a `Category`. An
322impure migration is a `Migration (Terminally PQ IO)`. You can always cast
323a pure migration into an impure migration with the functor, `pureMigration`.
325```Haskell
326pureMigration
327 :: Migration Definition schemas0 schemas1
328 -> Migration (Terminally PQ IO) schemas0 schemas1
329```
331To run either pure or impure migrations, Squeal 0.5 introduces
332a typeclass, `Migratory`.
334```
335class Category p => Migratory p where
337 migrateUp
338 :: AlignedList (Migration p) schemas0 schemas1
339 -> PQ schemas0 schemas1 IO ()
341 migrateDown
342 :: AlignedList (Migration p) schemas0 schemas1
343 -> PQ schemas1 schemas0 IO ()
344```
346The signatures of `migrateUp` and `migrateDown` have been changed
347to make them easier to compose with other `PQ` actions.
349**Transactions**
351You can now run transactions `ephemerally`, guaranteed to roll back,
352but return the result or throw the exception that the transaction
353would have generated. This is useful for testing. You can also
354`transactionallyRetry` computations, retrying the transaction if
355a [serialization failure](https://www.postgresql.org/docs/11/transaction-iso.html#XACT-REPEATABLE-READ) occurs.
357**Types**
359Squeal now supports money via a `Money` Haskell `Type` and a `'PGmoney`
360`PGType`. Squeal 0.5 also adds support for creating domain types
361with `createDomain`.
363**Time**
365Squeal 0.5 adds support for new functions; `now`, `makeDate`, `makeTime`,
366, `makeTimestamp`, and `makeTimestamptz`, a function `interval_` for
367constructing time intervals out multiples of `TimeUnit`s, and a new
368`TimeOp` class, defining affine space operators for time types and their
369differences.
371**Literals**
373Squeal allows you to include Haskell values in your statements using
374out of line `parameter`s, but often you want to include them inline,
375as a SQL `literal`. Squeal 0.5 enables this using the `Literal` class.
377**Set returning functions**
379Squeal 0.5 adds support for set returning functions such as `unnest`,
380with the `RankNType` `SetOfFunction`, which can be used as as a `FromClause`.
382**Text search**
384Squeal 0.5 adds extensive support for text search types, functions and operators.
386**Much more**
388Lots more changes went into and under the hood of the new version.
389The `Expression` module was split into coherent submodules since it
390had grown to immense proportions. New modules `Alias`, `PG` and `List`
391were added to relieve some of the burden that the `Schema` module
392had been carrying. Rendering has been better unified with a new
393`RenderSQL` typeclass. Type level list concatenation with the `Additional`
394typeclass has been added. `Manipulation`s `update` and `delete`
395were upgraded, so you can leave out fields you don't want to update and
396use `USING` clauses in deletes. Upserts which were previously broken
397now work. The `IO` typeclass hierarchy was changed from `MonadBase`
398and `MonadBaseControl` to `MonadIO` and `MonadUnliftIO`. A new
399`withRecursive` `Manipulation` was added. `SquealException`s were
400refactored and a `trySqueal` function added. Arrays were refactored.
401And there was probably more I've forgotten.
403### Version 0.4
405Version 0.4 strengthens Squeal's type system, adds
406support for multidimensional arrays, improves support
407for container type, improves `with` statements,
408improves runtime exceptions, accomodates SQL's three-valued logic,
409adds subquery expressions, and adds table and view type expressions.
411**Types**
413Squeal 0.4 renames some kinds to aid intuition:
415```Haskell
416type RowType = [(Symbol, NullityType)] -- previously RelationType
417type FromType = [(Symbol, RowType)] -- previously RelationsType
418```
420Null safety for array and composite types is gained by having the base
421type of an array be a `NullityType` and the base type of a composite
422a `RowType`.
424```Haskell
425data PGType
426 = ..
427 | PGvararray NullityType
428 | PGfixarray Nat NullityType
429 | PGcomposite RowType
430```
432Squeal embeds Postgres types into Haskell using data kinds and type-in-type:
434```Haskell
435data PGType = PGbool | ..
436data NullityType = Null PGType | NotNull PGType
437type RowType = [(Symbol, NullityType)] -- previously RelationType
438type FromType = [(Symbol, RowType)] -- previously RelationsType
439```
441In another sense, we can embed Haskell types
442into Postgres types by providing type families:
444```Haskell
445type family PG (hask :: Type) :: PGType
446type family NullPG (hask :: Type) :: NullityType
447type family TuplePG (hask :: Type) :: [NullityType]
448type family RowPG (hask :: Type) :: RowType
449```
451Let's look at these one by one.
453`PG` was introduced in Squeal 0.3. It was a closed type family that
454associates some Haskell types to their obvious corresponding Postgres
455types like `PG Double = 'PGfloat8`. It only worked on base types,
456no arrays or composites. Squeal 0.4 extends it to
457such container types and makes it an open type family so that
458users can make their own type instances.
460`NullPG` had a different name before but it does the obvious
461thing for Haskell with `Maybe`s:
463```Haskell
464type family NullPG hask where
465 NullPG (Maybe hask) = 'Null (PG hask)
466 NullPG hask = 'NotNull (PG hask)
467```
469`TuplePG` uses generics to turn tuple types (including records)
470into lists of `NullityType`s in the logical way, e.g.
471`TuplePG (Bool, Day) = '[ 'PGbool, 'PGdate]`.
473`RowPG` also uses generics to turn record types into a `RowType` in the logical way, e.g.
475```Haskell
476>>> data Person = Person { name :: Text, age :: Int32 } deriving GHC.Generic
477>>> instance SOP.Generic Person
478>>> instance SOP.HasDatatypeInfo Person
479>>> :kind! TuplePG Person
480TuplePG Person :: [NullityType]
481= '['NotNull 'PGtext, 'NotNull 'PGint4]
482>>> :kind! RowPG Person
483RowPG Person :: [(Symbol, NullityType)]
484= '["name" ::: 'NotNull 'PGtext, "age" ::: 'NotNull 'PGint4]
485```
487We've already seen a hint of why these types are useful in one construction
488from Squeal 0.3. Creating composite types in Postgres directly from a Haskell
489record type essentially uses `RowPG`. Another important use is in simplifying
490the type signatures for a `Query` or `Manipulation`. Very often, you will have
491a tuple type corresponding to the parameters and a record type corresponding
492to the returned columns of a `Query` or `Manipulation`. Instead of writing
493boilerplate signature you can reuse these with the help of `TuplePG` and `RowPG`
495For instance:
497```Haskell
498>>> :{
499let
500 query :: Query '["user" ::: 'View (RowPG Person)] (TuplePG (Only Int32)) (RowPG Person)
501 query = selectStar (from (view #user) & where_ (#age .> param @1))
502:}
503```
505**Arrays**
507In addition to being able to encode and decode basic Haskell types
508like `Int16` and `Text`, Squeal 0.4 permits you to encode and decode Haskell types to
509Postgres array types. The `Vector` type corresponds to to variable length arrays.
510And thanks to an idea from [Mike Ledger](https://github.com/mikeplus64),
511homogeneous tuples correspond to fixed length arrays. We can even
512create multi-dimensional fixed length arrays. Let's see an example.
514```Haskell
515>>> :{
516data Row = Row
517 { col1 :: Vector Int16
518 , col2 :: (Maybe Int16,Maybe Int16)
519 , col3 :: ((Int16,Int16),(Int16,Int16),(Int16,Int16))
520 } deriving (Eq, GHC.Generic)
521:}
523>>> instance Generic Row
524>>> instance HasDatatypeInfo Row
525```
527Define a simple round trip query.
529```Haskell
530>>> :{
531let
532 roundTrip :: Query '[] (TuplePG Row) (RowPG Row)
533 roundTrip = values_ $
534 parameter @1 (int2 & vararray) `as` #col1 :*
535 parameter @2 (int2 & fixarray @2) `as` #col2 :*
536 parameter @3 (int2 & fixarray @2 & fixarray @3) `as` #col3
537:}
539>>> :set -XOverloadedLists
540>>> let input = Row [1,2] (Just 1,Nothing) ((1,2),(3,4),(5,6))
541>>> :{
542void . withConnection "host=localhost port=5432 dbname=exampledb" $ do
543 result <- runQueryParams roundTrip input
544 Just output <- firstRow result
545 liftBase . print $ input == output
546:}
547True
548```
550**Containers**
552Squeal aims to provide a correspondence between Haskell types and Postgres types.
553In particular, Haskell ADTs with nullary constructors can correspond to
554Postgres enum types and Haskell record types can correspond to Postgres
555composite types. However, it's not always obvious that that's how a user
556will choose to store values of those types. So Squeal 0.4 introduces newtypes
557whose purpose is to specify how a user wants to store values of a type.
559```Haskell
560newtype Json hask = Json {getJson :: hask}
561newtype Jsonb hask = Jsonb {getJsonb :: hask}
562newtype Composite record = Composite {getComposite :: record}
563newtype Enumerated enum = Enumerated {getEnumerated :: enum}
564```
566Let's see an example:
568```Haskell
569>>> data Schwarma = Beef | Lamb | Chicken deriving (Eq, Show, GHC.Generic)
570>>> instance SOP.Generic Schwarma
571>>> instance SOP.HasDatatypeInfo Schwarma
572>>>
573>>> data Person = Person {name :: Text, age :: Int32} deriving (Eq, Show, GHC.Generic)
574>>> instance SOP.Generic Person
575>>> instance SOP.HasDatatypeInfo Person
576>>> instance Aeson.FromJSON Person
577>>> instance Aeson.ToJSON Person
578```
580We can create the equivalent Postgres types directly from their Haskell types.
582```Haskell
583>>> :{
584type Schema =
585 '[ "schwarma" ::: 'Typedef (PG (Enumerated Schwarma))
586 , "person" ::: 'Typedef (PG (Composite Person))
587 ]
588:}
590>>> :{
591let
592 setup :: Definition '[] Schema
593 setup =
594 createTypeEnumFrom @Schwarma #schwarma >>>
595 createTypeCompositeFrom @Person #person
596:}
597```
599Let's demonstrate how to associate our Haskell types `Schwarma` and `Person`
600with enumerated, composite or json types in Postgres. First create a Haskell
601`Row` type using the `Enumerated`, `Composite` and `Json` newtypes as fields.
603```Haskell
604>>> :{
605data Row = Row
606 { schwarma :: Enumerated Schwarma
607 , person1 :: Composite Person
608 , person2 :: Json Person
609 } deriving (Eq, GHC.Generic)
610:}
612>>> instance Generic Row
613>>> instance HasDatatypeInfo Row
614>>> :{
615let
616 input = Row
617 (Enumerated Chicken)
618 (Composite (Person "Faisal" 24))
619 (Json (Person "Ahmad" 48))
620:}
621```
623Once again, define a round trip query.
625```Haskell
626>>> :{
627let
628 roundTrip :: Query Schema (TuplePG Row) (RowPG Row)
629 roundTrip = values_ $
630 parameter @1 (typedef #schwarma) `as` #schwarma :*
631 parameter @2 (typedef #person) `as` #person1 :*
632 parameter @3 json `as` #person2
633:}
634```
636Finally, we can drop our type definitions.
638```Haskell
639>>> :{
640let
641 teardown :: Definition Schema '[]
642 teardown = dropType #schwarma >>> dropType #person
643:}
644```
646Now let's run it.
648```Haskell
649>>> :{
650let
651 session = do
652 result <- runQueryParams roundTrip input
653 Just output <- firstRow result
654 liftBase . print $ input == output
655in
656 void . withConnection "host=localhost port=5432 dbname=exampledb" $
657 define setup
658 & pqThen session
659 & pqThen (define teardown)
660:}
661True
662```
664**With**
666Squeal 0.3 supported WITH statements but there's a couple problems with them.
667Here's the type signature for `with` in Squeal 0.3.
669```Haskell
670with
671 :: SOP.SListI commons
672 => NP (Aliased (Manipulation schema params)) (common ': commons)
673 -- ^ common table expressions
674 -> Manipulation (With (common ': commons) schema) params results
675 -> Manipulation schema params results
676```
678The first problem is that `with` only works with `Manipulations`.
679It can work on `Query`s by using `queryStatement` but it still will
680return a `Manipulation`. We can fix this issue by making `with` a
681method of a type class with instances for both `Query` and `Manipulation`.
683The second problem is that all the common table expressions
684refer the base schema, whereas in SQL, each subsequent CTE can
685refer to previous CTEs as well. But this can be fixed! First define a datatype:
687```Haskell
688data CommonTableExpression statement params schema0 schema1 where
689 CommonTableExpression
690 :: Aliased (statement schema params) (alias ::: cte)
691 -> CommonTableExpression statement params schema (alias ::: 'View cte ': schema)
692```
694It's just a wrapper around an aliased statement, where the statement
695could be either a `Query` or `Manipulation`, but it augments the schema
696by adding a view to it. It almost looks like a morphism between schemas
697but there is no way yet to compose them. Luckily, Squeal already has
698a datatype for this, which is used for migrations, the `AlignedList`
699type which is really the "free category". So we can then define a `With` type class:
701```Haskell
702class With statement where
703 with
704 :: AlignedList (CommonTableExpression statement params) schema0 schema1
705 -> statement schema1 params row
706 -> statement schema0 params row
707```
709By giving `Aliasable` instances to CTEs and aligned singleton lists of CTEs (i.e. scrap-your-nils), we get a nice syntax for WITH statements in Squeal.
711Here's an example of using `with` for a `Query` and a `Manipulation`:
713```Haskell
714>>> :{
715let
716 query :: Query
717 '[ "t1" ::: 'View
718 '[ "c1" ::: 'NotNull 'PGtext
719 , "c2" ::: 'NotNull 'PGtext] ]
720 '[]
721 '[ "c1" ::: 'NotNull 'PGtext
722 , "c2" ::: 'NotNull 'PGtext ]
723 query = with (
724 selectStar (from (view #t1)) `as` #t2 :>>
725 selectStar (from (view #t2)) `as` #t3
726 ) (selectStar (from (view #t3)))
727in printSQL query
728:}
729WITH "t2" AS (SELECT * FROM "t1" AS "t1"), "t3" AS (SELECT * FROM "t2" AS "t2") SELECT * FROM "t3" AS "t3"
731>>> type ProductsTable = '[] :=> '["product" ::: 'NoDef :=> 'NotNull 'PGtext, "date" ::: 'Def :=> 'NotNull 'PGdate]
733>>> :{
734let
735 manipulation :: Manipulation
736 '[ "products" ::: 'Table ProductsTable
737 , "products_deleted" ::: 'Table ProductsTable
738 ] '[ 'NotNull 'PGdate] '[]
739 manipulation = with
740 (deleteFrom #products (#date .< param @1) ReturningStar `as` #deleted_rows)
741 (insertQuery_ #products_deleted (selectStar (from (view (#deleted_rows `as` #t)))))
742in printSQL manipulation
743:}
744WITH "deleted_rows" AS (DELETE FROM "products" WHERE ("date" < ($1 :: date)) RETURNING *) INSERT INTO "products_deleted" SELECT * FROM "deleted_rows" AS "t"
745```
747**Three Valued Logic**
749In previous versions of Squeal, conditions followed classical two valued logic
750of `true` and `false`.
752```Haskell
753-- Squeal 0.3
754type Condition schema from grouping params =
755 Expression schema from grouping params ('NotNull 'PGbool)
756```
758I had thought that three valued logic, which is what SQL uses, was confusing.
759However, multiple users reported being confused at being forced to do `NULL`
760handling, particularly in their left joins. Since the original motivation
761of being less confusing evaporated I decided to switch to three valued logic
762of `true`, `false` and `null_`.
764```Haskell
765-- Squeal 0.4
766type Condition schema from grouping params =
767 Expression schema from grouping params ('Null 'PGbool)
768```
770**Subquery Expressions**
772Squeal 0.4 adds support for subquery expressions such as `IN` and `op ANY/ALL`.
774**Row Types**
776Squeal 0.4 adds functions to define type expressions from tables and views
777and a type expression for user defined types, `typetable`, `typeview` and
778`typedef`.
780**Runtime Exceptions**
782Squeal now has an exception type which gives details on the sort of error
783encountered and handler functions.
785```Haskell
786data SquealException
787 = PQException
788 { sqlExecStatus :: LibPQ.ExecStatus
789 , sqlStateCode :: Maybe ByteString
790 -- ^ https://www.postgresql.org/docs/current/static/errcodes-appendix.html
791 , sqlErrorMessage :: Maybe ByteString
792 }
793 | ResultException Text
794 | ParseException Text
795 deriving (Eq, Show)
796instance Exception SquealException
798catchSqueal
799 :: MonadBaseControl IO io
800 => io a
801 -> (SquealException -> io a) -- ^ handler
802 -> io a
804handleSqueal
805 :: MonadBaseControl IO io
806 => (SquealException -> io a) -- ^ handler
807 -> io a -> io a
808```
810**Additional Changes**
812Squeal 0.4 adds `field` and `index` functions to get components of composite
813and array expressions.
815Squeal 0.4 adds a dependency on `records-sop` to offload a lot of boilerplate
816type family logic that was needed for `RowPG`.
818The above changes required major and minor changes to Squeal DSL functions.
819Please consult the documentation.
821### Version 0.3.2 - August 4, 2018
823Version 0.3.2 features extensive support for `JSON` functionality with
824more than 50 new functions.
825This work is entirely due to [Mike Ledger](https://github.com/mikeplus64)
826who has been making terrific contributions to Squeal. Thanks!
827We also got some examples in the documentation for pools submitted by
828[Raveline](https://github.com/Raveline). I'm so pleased to be
829getting pull requests and issue submissions from you all!
831### Version 0.3.1 - July 7, 2018
833Version 0.3.1 of Squeal enables the "Scrap your Nils" trick for
834heterogeneous lists of `Alias`es, `Aliased` expressions, `PGlabel`s and `By`s
835with the typeclasses `IsLabel`, `IsQualified`, `IsPGlabel`,
836and the new `Aliasable` typeclass, to eliminate all need of using `Nil` in a list.
837There were a couple minor name changes, i.e. the function `group` was renamed to `groupBy`.
838Please consult the documentation.
840### Version 0.3 - June 26, 2018
842Version 0.3 of Squeal adds views as well as composite and enumerated types to Squeal.
843To support these features, a new kind `SchemumType` was added.
845```Haskell
846data SchemumType
847 = Table TableType
848 | View RelationType
849 | Typedef PGType
850```
852As a consequence, you will have to update your schema definitions like so:
854```Haskell
855-- Squeal 0.2
856type Schema =
857 '[ "users" :::
858 '[ "pk_users" ::: 'PrimaryKey '["id"] ] :=>
859 '[ "id" ::: 'Def :=> 'NotNull 'PGint4
860 , "name" ::: 'NoDef :=> 'NotNull 'PGtext
861 ]
862 ]
864-- Squeal 0.3
865type Schema =
866 '[ "users" ::: 'Table (
867 '[ "pk_users" ::: 'PrimaryKey '["id"] ] :=>
868 '[ "id" ::: 'Def :=> 'NotNull 'PGint4
869 , "name" ::: 'NoDef :=> 'NotNull 'PGtext
870 ])
871 ]
872```
874**Views**
876You can now create, drop, and query views.
878```Haskell
879>>> :{
880type ABC =
881 ('[] :: TableConstraints) :=>
882 '[ "a" ::: 'NoDef :=> 'Null 'PGint4
883 , "b" ::: 'NoDef :=> 'Null 'PGint4
884 , "c" ::: 'NoDef :=> 'Null 'PGint4
885 ]
886type BC =
887 '[ "b" ::: 'Null 'PGint4
888 , "c" ::: 'Null 'PGint4
889 ]
890:}
892>>> :{
893let
894 definition :: Definition '["abc" ::: 'Table ABC ] '["abc" ::: 'Table ABC, "bc" ::: 'View BC]
895 definition = createView #bc (select (#b :* #c :* Nil) (from (table #abc)))
896in printSQL definition
897:}
898CREATE VIEW "bc" AS SELECT "b" AS "b", "c" AS "c" FROM "abc" AS "abc";
900>>> :{
901let
902 definition :: Definition '["abc" ::: 'Table ABC, "bc" ::: 'View BC] '["abc" ::: 'Table ABC]
903 definition = dropView #bc
904in printSQL definition
905:}
906DROP VIEW "bc";
908>>> :{
909let
910 query :: Query '["abc" ::: 'Table ABC, "bc" ::: 'View BC] '[] BC
911 query = selectStar (from (view #bc))
912in printSQL query
913:}
914SELECT * FROM "bc" AS "bc"
915```
917**Enumerated Types**
919PostgreSQL has a powerful type system. It even allows for user defined types.
920For instance, you can define enumerated types which are data types that comprise
921a static, ordered set of values. They are equivalent to Haskell algebraic data
922types whose constructors are nullary. An example of an enum type might be the days of the week,
923or a set of status values for a piece of data.
925Enumerated types are created using the `createTypeEnum` command, for example:
927```Haskell
928>>> :{
929let
930 definition :: Definition '[] '["mood" ::: 'Typedef ('PGenum '["sad", "ok", "happy"])]
931 definition = createTypeEnum #mood (label @"sad" :* label @"ok" :* label @"happy" :* Nil)
932:}
933>>> printSQL definition
934CREATE TYPE "mood" AS ENUM ('sad', 'ok', 'happy');
935```
937Enumerated types can also be generated from a Haskell algebraic data type with nullary constructors, for example:
939```Haskell
940>>> data Schwarma = Beef | Lamb | Chicken deriving GHC.Generic
941>>> instance SOP.Generic Schwarma
942>>> instance SOP.HasDatatypeInfo Schwarma
944>>> :kind! EnumFrom Schwarma
945EnumFrom Schwarma :: PGType
946= 'PGenum '["Beef", "Lamb", "Chicken"]
948>>> :{
949let
950 definition :: Definition '[] '["schwarma" ::: 'Typedef (EnumFrom Schwarma)]
951 definition = createTypeEnumFrom @Schwarma #schwarma
952:}
953>>> printSQL definition
954CREATE TYPE "schwarma" AS ENUM ('Beef', 'Lamb', 'Chicken');
955```
957You can express values of an enum type using `label`, which is an overloaded method
958of the `IsPGlabel` typeclass.
960```Haskell
961>>> :{
962let
963 expression :: Expression sch rels grp params ('NotNull (EnumFrom Schwarma))
964 expression = label @"Chicken"
965in printSQL expression
966:}
967'Chicken'
968```
970**Composite Types**
972In addition to enum types, you can define composite types.
973A composite type represents the structure of a row or record;
974it is essentially just a list of field names and their data types.
977`createTypeComposite` creates a composite type. The composite type is
978specified by a list of attribute names and data types.
980```Haskell
981>>> :{
982let
983 definition :: Definition '[] '["complex" ::: 'Typedef ('PGcomposite '["real" ::: 'PGfloat8, "imaginary" ::: 'PGfloat8])]
984 definition = createTypeComposite #complex (float8 `As` #real :* float8 `As` #imaginary :* Nil)
985:}
986>>> printSQL definition
987CREATE TYPE "complex" AS ("real" float8, "imaginary" float8);
988```
990Composite types are almost equivalent to Haskell record types.
991However, because of the potential presence of `NULL`
992all the record fields must be `Maybe`s of basic types.
993Composite types can be generated from a Haskell record type, for example:
995```Haskell
996>>> data Complex = Complex {real :: Maybe Double, imaginary :: Maybe Double} deriving GHC.Generic
997>>> instance SOP.Generic Complex
998>>> instance SOP.HasDatatypeInfo Complex
1000>>> :kind! CompositeFrom Complex
1001CompositeFrom Complex :: PGType
1002= 'PGcomposite '['("real", 'PGfloat8), '("imaginary", 'PGfloat8)]
1004>>> :{
1005let
1006 definition :: Definition '[] '["complex" ::: 'Typedef (CompositeFrom Complex)]
1007 definition = createTypeCompositeFrom @Complex #complex
1008in printSQL definition
1010CREATE TYPE "complex" AS ("real" float8, "imaginary" float8);
1011```
1013A row constructor is an expression that builds a row value
1014(also called a composite value) using values for its member fields.
1016```Haskell
1017>>> :{
1018let
1019 i :: Expression '[] '[] 'Ungrouped '[] ('NotNull (CompositeFrom Complex))
1020 i = row (0 `As` #real :* 1 `As` #imaginary :* Nil)
1022>>> printSQL i
1023ROW(0, 1)
1024```
1026You can also use `(&)` to apply a field label to a composite value.
1028```Haskell
1029>>> :{
1030let
1031 expr :: Expression '[] '[] 'Ungrouped '[] ('Null 'PGfloat8)
1032 expr = i & #imaginary
1033in printSQL expr
1035(ROW(0, 1)).imaginary
1036```
1038Both composite and enum types can be automatically encoded from and decoded to their equivalent Haskell types.
1039And they can be dropped.
1041```Haskell
1042>>> :{
1043let
1044 definition :: Definition '["mood" ::: 'Typedef ('PGenum '["sad", "ok", "happy"])] '[]
1045 definition = dropType #mood
1047>>> printSQL definition
1048DROP TYPE "mood";
1049```
1051**Additional Changes**
1053Squeal 0.3 also introduces a typeclass `HasAll` similar to `Has` but for a list of aliases.
1054This makes it possible to clean up some unfortunately messy Squeal 0.2 definitions.
1056```Haskell
1057-- Squeal 0.2
1058>>> unique (Column #a :* Column #b :* Nil)
1060-- Squeal 0.3
1061>>> unique (#a :* #b :* Nil)
1062```
1064Squeal 0.3 also adds `IsLabel` instances for `Aliased` expressions and tables as well as
1065heterogeneous lists, allowing for some more economy of code.
1067```Haskell
1068-- Squeal 0.2 (or 0.3)
1069>>> select (#a `As` #a :* Nil) (from (table (#t `As` #t)))
1071-- Squeal 0.3
1072>>> select #a (from (table #t))
1073```
1075Squeal 0.3 also fixes a bug that prevented joined queries on self-referencing tables.
1077The above changes required major and minor changes to Squeal DSL functions.
1078Please consult the documentation.
1080### Version 0.2.1 - April 7, 2018
1082This minor update fixes an issue where alias identifiers could conflict with
1083reserved words in PostgreSQL. To fix the issue, alias identifiers are now
1084quoted. Thanks to Petter Rasmussen for the fix.
1086### Version 0.2 - March 26, 2018
1088**Changes**
1089- **Constraints** - Type level table constraints like primary and foreign keys and column constraints like having `DEFAULT`.
1090- **Migrations** - Support for linear, invertible migrations tracked in an auxiliary table
1091- **Arrays** - Support for fixed- and variable-length arrays
1092- **Aliases** - Generalized `Has` constraint
1093- **Pools** - Support for pooled connections
1094- **Transactions** - Support for transaction control language
1095- **Queries, Manipulations, Definitions** - Small and large changes to Squeal's DSL
1097Well, a lot of things changed!
1099**Constraints**
1101An important request was to bring constraints to the type level.
1102This means that more of the schema is statically known. In `0.1` column constraints - which boil down
1103to having `DEFAULT` or not - were at the type level, but they were confusingly named.
1105```haskell
11060.1: 'Optional ('NotNull 'PGInt4)
11070.2: 'Def :=> 'NotNull 'PGInt4
11080.1: 'Required ('NotNull 'PGInt4)
11090.2: 'NoDef :=> 'NotNull 'PGInt4
1110```
1112The `:=>` type operator is intended to helpfully connote a constraint relation.
1113It's also used for table constraints which are new in `0.2`.
1115```haskell
1116"emails" :::
1117 '[ "pk_emails" ::: 'PrimaryKey '["id"]
1118 , "fk_user_id" ::: 'ForeignKey '["user_id"] "users" '["id"]
1119 ] :=>
1120 '[ "id" ::: 'Def :=> 'NotNull 'PGint4
1121 , "user_id" ::: 'NoDef :=> 'NotNull 'PGint4
1122 , "email" ::: 'NoDef :=> 'Null 'PGtext
1124```
1126Another change in the constraint system was the removal of column constraints from
1127query and manipulation results, as result columns don't support a notion of `DEFAULT`.
1128This necessitates a distinction between `TableType`s which have both column and table
1129constraints and `RelationType`s which have neither.
1131**Migrations**
1133Migrations are a hot topic and many people have requested this feature. Squeal `0.2`
1134adds support for linear, invertible migrations. That is, a migration is a named,
1135invertible, schema-tracking computation:
1137```haskell
1138data Migration io schema0 schema1 = Migration
1139 { name :: Text -- ^ The `name` of a `Migration`.
1140 -- Each `name` in a `Migration` should be unique.
1141 , up :: PQ schema0 schema1 io () -- ^ The `up` instruction of a `Migration`.
1142 , down :: PQ schema1 schema0 io () -- ^ The `down` instruction of a `Migration`.
1144```
1146And, `Migration`s can be put together in an "aligned" list:
1148```haskell
1149data AlignedList p x0 x1 where
1150 Done :: AlignedList p x x
1151 (:>>) :: p x0 x1 -> AlignedList p x1 x2 -> AlignedList p x0 x2
1152```
1154These aligned lists are free categories and might look familiar from
1155the [reflections without remorse](http://okmij.org/ftp/Haskell/zseq.pdf) technique,
1156which uses their cousins, aligned sequences.
1158In the context of migration, they allow one to chain new migrations as a
1159schema evolves over time. `Migration`s' execution is tracked in an auxiliary
1160migrations table. Migration lists can then be run or rewinded.
1162```haskell
1163migrateUp
1164 :: MonadBaseControl IO io
1165 => AlignedList (Migration io) schema0 schema1 -- ^ migrations to run
1166 -> PQ
1167 ("schema_migrations" ::: MigrationsTable ': schema0)
1168 ("schema_migrations" ::: MigrationsTable ': schema1)
1169 io ()
1171migrateDown
1172 :: MonadBaseControl IO io
1173 => AlignedList (Migration io) schema0 schema1 -- ^ migrations to rewind
1174 -> PQ
1175 ("schema_migrations" ::: MigrationsTable ': schema1)
1176 ("schema_migrations" ::: MigrationsTable ': schema0)
1177 io ()
1178```
1180**Aliases**
1182In Squeal `0.1`, we had different typeclasses `HasColumn` and `HasTable` to indicate
1183that a table has a column or that a schema has a table. In Squeal `0.2` this has been
1184unified to a single typeclass,
1186```haskell
1187class KnownSymbol alias =>
1188 Has (alias :: Symbol) (fields :: [(Symbol,kind)]) (field :: kind)
1189 | alias fields -> field where
1190```
1192**Arrays**
1194Support for array types has been added to Squeal `0.2` through
1195the `'PGfixarray` and `'PGvararray` `PGType`s. Array values can be
1196constructed using the `array` function and can be encoded from and decoded to
1197Haskell `Vector`s.
1199**Pools**
1201Squeal `0.2` provides a monad transformer `PoolPQ` that's an instance of `MonadPQ`.
1202The `resource-pool` library is leveraged to provide striped pools of `Connection`s.
1203`PoolPQ` should be a drop in replacement for running `Manipulation`s and `Query`s with
1204`PQ`.
1206**Transactions**
1208Squeal `0.2` supports a simple transaction control language. A computation in
1209`MonadPQ` can be called `transactionally` with different levels of isolation.
1210Additionally, a schema changing computation, a data definition, can be run in a
1211transaction. Running a computation in a transaction means that all SQL statements
1212will be rolled back if an exception is encountered.
1214**Queries, Manipulations and Definitions**
1216The above changes required major and minor changes to Squeal DSL functions.
1217Please consult the documentation.