git.iko.soy/squeal
on dev-params help sign in
squeal/squeal-presentation-raveline.md
794 lines 19 kB history raw
1# Squeal
3_A bridge between SQL and Haskell_
5by [@Raveline](https://github.com/raveline)
7---
9# Rationale
11Using `postgresql-simple` you have a typical "Trial, error, despair" workflow:
13- Write query manually or semi-automatically
15- Write haskell code using the query
17- Pray that the query syntax is correct
19- Pray that it returns what you want
21- Realize the inefficiency of prayer when it comes to SQL
23- Iterate till it works
25---
27![Can't take this anymore](http://gif.eraveline.eu/static/img/0x16e.gif)
29---
31# Use your best friend: GHC
33Squeal provides several eDSL to make your SQL typesafe:
35- Type level eDSL to express schema;
37- Value level eDSL to manipulate schema (plus migration, yeehaw !);
39- Type level eDSL to express queries;
41- Value level eDSL to perform queries
43However, it's not an ORM. There's no caching, lazy loading - you retain control over your memory. Also, joins - and mostly aggregation after joins - have to be handled manually.
45---
47# Part I. Schema & migration
49---
51# The Schema
53- The schema will be used to validate _everything_: migration, queries, etc.
55- A schema is mostly a collection of tables.
57- In this presentation, we will create a very basic database modeling a parliament. We will store _members of parliament_ and _parliamentary groups_.
59- A simple Schema to represent a Parliament could be:
61```haskell
62type Schema = '[ "mp" ::: 'Table MemberOfParliament
63 , "groupp" ::: 'Table ParliamentGroup ]
64```
66---
68> _note_ We need the `DataKinds` extension to be able to express heterogenous lists containing
69> specific types like this one.
71> You can perfectly call your table "group" and not "groupp" even though it is a
72> keyword in SQL - Squeal queries will be properly escaped.
74---
76# A small example
78```haskell
79type ParliamentaryGroup =
80 '[ "pk_groupp" ::: 'PrimaryKey '["groupp_id"]]
81 :=>
82 '[ "groupp_id" ::: 'NoDef :=> 'NotNull 'PGuuid
83 , "name" ::: 'NoDef :=> 'NotNull 'PGtext
84 ]
85```
87- That's a whole simple table defined in one go.
89- `:::` lets us define a column or a constraint.
91- `:=>` associates the constraints to the column.
93- Let's split constraints and column to study the syntax a bit more.
95---
97> _note_ We are using `:::` and `:=>` to quickly express associations when writing
98> our schema. We need the `TypeOperators` extension.]
100---
102![What does this means ?!](http://gif.eraveline.eu/static/img/0x27e.gif)
104---
106# Defining a table
108```haskell
109type MemberOfParliament =
110 '[ "pk_mp" ::: 'PrimaryKey '["mp_id"]
111 , "fk_mp_groupp" ::: 'ForeignKey '["mp_group"] "groupp" '["groupp_id"]
112 ] :=> MpCols
113```
115- A table is a collection of constraints associated to a collection of columns.
117- We defined a table named "mp".
119- It has a constraint named "pk_mp", defining its primary key on column "mp_id".
121- It has a constraint named "fk_mp_groupp" defining a foreign key on column "mg_group", connected to the column "groupp_id" of table "groupp".
123- These constraints will be associated to the columns defined in `MpCols`.
125---
127# Defining columns
129```haskell
130type MpCols =
131 '[ "mp_id" ::: 'NoDef :=> 'NotNull 'PGuuid
132 , "first_name" ::: 'NoDef :=> 'NotNull 'PGtext
133 , "last_name" ::: 'NoDef :=> 'NotNull 'PGtext
134 ]
135```
137- A listing of column associate, for each element:
139 * a name;
141 * the mention of an eventual default value;
143 * the nullability;
145 * the type (obviously).
147---
149> _note_ GHC is already helping. If I named the "mp_id" column differently,
150> GHC would yell because I promised a primary key constraint on a column named `mp_id`, so there must be one.]
152---
154# Implementating the schema
156- We will carry this schema type pretty much everywhere.
158- But before we play with this schema, we need to implement it.
160---
162```haskell
163setup :: Definition '[] Schema
164setup =
165 createTable #groupp
166 ( notNullable uuid `as` #groupp_id
167 :* notNullable text `as` #name)
168 ( primaryKey #groupp_id `as` #pk_groupp )
169 >>> createTable #mp
170 ( notNullable uuid `as` #mp_id
171 :* notNullable text `as` #first_name
172 :* notNullable text `as` #last_name
173 :* notNullable text `as` #mp_group)
174 ( primaryKey #mp_id `as` #pk_mp
175 :* foreignKey #mp_group #groupp #groupp_id
176 OnDeleteCascade OnUpdateCascade
177 `as` #fk_mp_groupp)
178```
180---
182# Damn, that's verbose
184- Yes. But you need verbosity to get type safety.
186- On the plus side, it's fairly straightforward.
188- You use `:*` to compose the element of the heterogenous list of columns.
190- You use `>>>` to compose table creation.
192> _note_ The compiler will catch any mistype between Schema and
193> definition; wrong nullability, wrong type, wrong name, etc.
195---
197> _note_ You'll also need `OverloadedLabels`, for naming stuff.
198> This is mostly to avoid having to write manual proxies all
199> the time and for convenience.
201---
203# Setting up the schema
205- Squeal comes with a very good migration manager, handling _upgrades_ AND
206_downgrades_.
208- A migration is a simple type:
210```haskell
211Migration io schema0 schema1
212```
214- The first parameter is a BaseMonad (typically, IO).
216- The second parameter is "the current schema of your DB".
218- The last one is "what you will migrate to".
220- The `Definition` type used to define upgrade and downgrade functions use the
221 same logic (from one schema to the other).
223---
225# Defining our first migration
227- We have the setup bit, we need the teardown:
229```haskell
230tearDown :: Definition Schema '[]
231tearDown = dropTable #mp >>> dropTable #groupp
232```
234> _note_ GHC will also detect the _proper_ order of what you typed in
235> downgrade and upgrade should there be any conflict (with foreign keys).
237```haskell
238initDB :: Migration IO '[] Schema
239initDB =
240 Migration { name = "Schema creation"
241 , up = void $ define setup
242 , down = void $ define tearDown }
243```
245---
247# Simple migrator example
249```haskell
250main :: IO ()
251main = do
252 printSQL setup
253 void $ withConnection connectionString $
254 migrateUp $ single initDB
255```
257- We print the migration query; you can do this for all queries generated through Squeal.
259- We use `migrateUp` to perform the migration.
261- You can run several migration at the same time, to run only one use `single`.
263---
265![That was easy](http://gif.eraveline.eu/static/img/0x47f.gif)
267---
269# Part II. Insertions
271---
273# Manipulations & Queries
275Besides the specific case of migration, you will mostly perform:
277- `Manipulation` : inserting, updating and deleting data.
279- `Query` : fetching data.
281- Both types are parametric over the same things:
283 * A schema
285 * Input parameters
287 * Output
289- Simplified, they look like this:
291```haskell
292Manipulation schema params columns
294Query schema params columns
295```
298---
300# Our Haskell model
302```haskell
303type Parliament = [Group]
305data Group =
306 Group { name :: Text
307 , members :: [MemberOfParliament] }
309data MemberOfParliament =
310 MemberOfParliament { firstName :: Text
311 , lastName :: Text }
312```
314> _note_ We didn't use anything from Squeal.
315> The model can be entirely separated from the persistence layer.
317---
319# Inserting a Parliamentary Group
321- A group is very simple: it's a uuid and a name. Let's define our params:
323```haskell
324type GroupInsertionParams = '[ 'NotNull 'PGuuid
325 , 'NotNull 'PGtext ]
326```
328- Params are not named, but they are indexed. You just need nullability and type.
330---
332```haskell
333groupInsertion :: Manipulation Schema GroupInsertionParams '[]
334groupInsertion =
335 insertRow_ #groupp ( Set (param @1) `as` #groupp_id
336 :* Set (param @2) `as` #name )
337```
339---
341- `TypeApplication` lets us use the index of parameters (counting from 1).
343- Once again: all this is checked by the compiler. Wrong name, wrong type... you'll get a compile error.
345- `insertRow_` is a simplified version of `insertRow`. `insertRow` lets you express a `RETURNING` clause and the expected behaviour in case of conflict.
347---
349_Inserting a Member of Parliament_
350# Doing an INSERT INTO ... SELECT
352- We could create a naive query that takes MP uuid, first name, last name and group uuid...
354---
356- But that's boring. So let's use the `INSERT INTO ... SELECT`.
358---
360- We will build a query that will return as constants our MP's uuid, first name and last name...
362---
364- ... and fetch the uuid of a group given the name of the group.
366---
368- There's a `insertQuery` utility function for that. All we have to do is write the `select` !
370```haskell
371mpInsertion :: Manipulation Schema MpParams '[]
372mpInsertion =
373 insertQuery_ #mp selectGroup -- we just have to write selectGroup !
374```
376---
378_Inserting a Member of Parliament_
379# The query type
381- A query looks just like a `Manipulation`:
383```haskell
384Query schema params returns
385```
387- Returns use a special syntax, that demands names, nullability and types:
389```haskell
390type ExampleReturnType = '[ "some_column" ::: 'NotNull 'PGuuid
391 , "other_column" ::: 'Null 'PGtext ]
392```
394- It is not the same as the column definition we used in the schema. That one also expects that you specify, for each column, an eventual default:
396```haskell
397type ExampleSchemaCols =
398 '[ "some_column" ::: 'NoDef :=> 'NotNull 'PGuuid
399 , "other_column" ::: 'NoDef :=> 'Null 'PGtext ]
400```
402---
404_Inserting a Member of Parliament_
405# Don't rewrite column definitions
407- We want our return type to be "all columns from table MP expressed as return type".
409- But we cannot reuse our neat `MpCols` alias, since the type do not match as we've just seen.
411- Hopefully, there's a neat Type Family that will let you convert any table you defined to the type of the equivalent row: `TableToRow`.
413- We finally have the signature for our SELECT query.
415```haskell
416selectGroup :: Query Schema MpParams (TableToRow MemberOfParliament)
417```
419---
421_Inserting a Member of Parliament_
422# Our final Select query
424- Our intermediate query looks like this:
426```haskell
427selectGroup =
428 select
429 ( param @1 `as` #mp_id
430 :* param @2 `as` #first_name
431 :* param @3 `as` #last_name
432 :* #groupp ! #groupp_id `as` #mp_group
433 )
434 ( from (table #groupp)
435 & where_ ( #groupp ! #name .== param @4 )
436 )
437```
439- The first three columns are constant defined through our params;
441- ... we alias the column name using `as`...
443- ... and when we need the result from the table we use the `#table ! #column` syntax.
445- We'll go back to the "from" block later.
447---
449# Dealing with the connection context
451- Queries are runned in a connection context:
453 * When dealing with a single-connection context, use the type `PQ`.
455 * When dealing with a pool of connection, use the type `PoolPQ`.
457- Or get rid of the context and use `mtl` style, with the typeclass `MonadPQ`.
459- We'll use that to demonstrate how to actually run our queries.
461---
463# Inserting a whole group
465```haskell
466insertGroup :: (MonadPQ Schema m, MonadBaseControl IO m) => Group -> m ()
467insertGroup g@(Group name _) = do
468 uuid' <- liftBase nextRandom
469 void $ manipulateParams groupInsertion (uuid', name)
470 insertMps g
471```
473- Remember that we defined a group as a name and a list of MPs.
475- We are in `MonadBaseControl`, so we cannot use `liftIO`, we need `liftBase`.
477- To perform a simple insertion, use `manipulateParams`. It takes into parameter
478instances of `ToParam`, but you will typically use tuples or Generic-SOP.
480---
482# Inserting a bunch of MPs
484```haskell
485insertMps :: (MonadPQ Schema m, MonadBaseControl IO m) => Group -> m ()
486insertMps (Group groupName mps) =
487 let tuplify Mp{..} = (, firstName, lastName, groupName) <$> nextRandom
488 params = traverse tuplify mps
489 in void $ liftBase params >>= traversePrepared mpInsertion
490```
492- This time, we want to do a `preparedStatement`. We'll use `traversePrepare`
493which behaves like `manipulateParams`, but expect a list of `ToParam`
494instances.
496- We build our tuple manually again, mostly because we want to generate UUID on
497the fly and we don't want to have them in our model.
499---
501# Inserting a whole parliament
503```haskell
504insertParliament ::
505 (MonadPQ Schema m, MonadBaseControl IO m) => Parliament -> m ()
506insertParliament = traverse_ insertGroup
507```
509- That's simple enough !
511- If we want to be a bit safer, though, we can wrap this call in a
512`transactionally_` function, which will put all that in a transaction.
514---
516![Very simple](https://media1.tenor.com/images/0188c63209aced59f1583e1ca94e509e/tenor.gif?itemid=3550689)
519---
521# Part III. Selects
523---
525# Composable queries
527- Building basic queries is easy, and is well documented.
529- However, the real interest of a tool like Squeal is in DRYness, and the documentation is still lacking in "how-to" related to composability.
531- I'll build an example showing how column selections and from clause can be factorized.
533- We want to write two queries:
535 * One to get all members of a specific group.
537 * One to get all the parliament.
539---
541# Decomposing a `Query`
543- A query typically stars with `select` (variants are available).
545- It then takes:
547 * A heterogenous list of fields with a scary signature;
549 * A virtual table (the from clause and filter clauses), called a `TableExpression`.
551- And return fields. Ours will look like this:
553```haskell
554type GroupRowResult =
555 '[ "groupName" ::: 'NotNull 'PGtext
556 , "firstName" ::: 'NotNull 'PGtext
557 , "lastName" ::: 'NotNull 'PGtext ]
558```
560---
562# The Table Expression
564- It necessarily contains a `fromClause` (table, view or subquery, plus optional joins).
566- You can add where, groups, "HAVING" clause, order, etc.
568- `from` creates a basic `TableExpression` that you extend through various function to add clauses.
570- For our queries, we will share a common `FromClause`.
572---
574# The From Clause
576```
577FromClause schema params from
578```
580- We need Schema, input parameters and a `FromType` giving the available fields of the expression.
582- In our case, we'll join the table `mp` and the table `groupp`, meaning the `from` will be all fields of these tables.
584- We can use `TableToRow` to be more DRY:
586```haskell
587type BaseParliamentSelection =
588 '[ "g" ::: TableToRow GroupCols
589 , "m" ::: TableToRow MpCols ]
590```
592> _note_ We've also put everything with table aliases: "g" and "m".
594---
596# Writing our from clause
598```haskell
599baseParliamentTables ::
600 FromClause Schema (param :: [NullityType]) BaseParliamentSelection
601baseParliamentTables =
602 table
603 (#groupp `as` #g)
604 & innerJoin
605 (table (#mp `as` #m))
606 (#m ! #mp_group .== #g ! #groupp_id)
607```
609- When picking "from" something, you need to specify the type with a function: `table` for a table, `view` for a view, etc.
611- All joins are available. `innerJoin` is the most basic one. It takes the joined table and the joining condition.
613- We have to specifiy the type of `param` (current limitation of the lib), even though _any_ params will be compatible with this, so we can plug `where` clauses depending on params should we need to !
615---
617# Typing the common selection
619- Both our querie will need the same fields. This has to be factorized too.
621- The scary signature of selection fields:
623```haskell
624NP (Aliased (Expression schema from grouping params)) cols
625```
627- `NP` is for heterogenous lists ("n-ary product").
629- `from` is the virtual table type, so our `BaseParliamentSelection`.
631- `grouping` is there to make a distinction between aggregated / unaggregated queries.
633- And finally, cols is the return type.
635---
637# Our common selection
639```haskell
640groupSelection ::
641 NP (Aliased (Expression Schema BaseParliamentSelection 'Ungrouped param))
642 GroupRowResult
643groupSelection =
644 #g ! #name `as` #groupName
645 :* #m ! #first_name `as` #firstName
646 :* #m ! #last_name `as` #lastName
647```
649- I hope you like big signatures.
651- But it's what let GHC checks that all alias and columns are available.
653- And that you're returning what you really intend to return.
655---
657# Putting it all together
659```haskell
660selectParliament :: Query Schema '[] GroupRowResult
661selectParliament =
662 select groupSelection (from baseParliamentTables)
663```
665```haskell
666selectGroupMembers :: Query Schema '[ 'NotNull 'PGtext] GroupRowResult
667selectGroupMembers =
668 select groupSelection
669 (from baseParliamentTables
670 & where_ (#g ! #name .== param @1))
671```
673- That is fairly DRY. And _entirely typesafe_.
675---
677# Actually fetching the data
679- We'll build an intermediary datatype representing our rows.
681- We'll make it match our row.
683- And we'll add generic-SOP so that we can build them from query results.
685```haskell
686type GroupRowResult =
687 '[ "groupName" ::: 'NotNull 'PGtext
688 , "firstName" ::: 'NotNull 'PGtext
689 , "lastName" ::: 'NotNull 'PGtext ]
690```
692```haskell
693data GroupRow =
694 GroupRow { groupName :: Text
695 , firstName :: Text
696 , lastName :: Text }
697 deriving (Generic)
699instance SOP.Generic GroupRow
700instance SOP.HasDatatypeInfo GroupRow
701```
703---
705# Aggregate result logic
707- We will get tabular, SQL data.
709- Our results will be `[GroupRow]`.
711- We'll build `Groups` from this:
713```haskell
714buildGroup :: NE.NonEmpty GroupRow -> Group
715buildGroup grs =
716 let buildMP (GroupRow _ f l) = Mp f l
717 name = groupName . NE.head $ grs
718 members = NE.toList (buildMP <$> grs)
719 in Group{..}
721rowToGroups :: [GroupRow] -> [Group]
722rowToGroups =
723 let grouped = fmap NE.fromList . L.groupBy ((==) `on` groupName)
724 in fmap buildGroup . grouped
725```
727---
729# Finally calling our queries
731- Query with params will use `runQueryParams`.
733- Query without params will use `runQuery`.
735- Result is inside a `MonadPQ` and of type `K` from generic-sop.
737- You get your actual result using `getRows`. Exemple:
739```haskell
740getParliament :: (MonadPQ Schema m, MonadBaseControl IO m) => m [Group]
741getParliament = do
742 res <- runQuery selectParliament
743 rows <- getRows res
744 pure $ rowToGroups rows
745```
747- In real life of course you'll write:
749```haskell
750getParliament =
751 rowToGroups <$> (runQuery selectParliament >>= getRows)
752```
754---
756# Our query with params
758```haskell
759getGroupMembers ::
760 (MonadPQ Schema m, MonadBaseControl IO m) => Text -> m (Maybe Group)
761getGroupMembers =
762 listToMaybe . rowToGroups <$>
763 (runQueryParams selectGroupMembers (Only t) >>= getRows)
764```
766- And that's it.
768---
770![Eazy](https://media.tenor.com/images/8fc7c4077efe11b4a3a3b9ae4e643e87/tenor.gif)
772---
774# Conclusion
776---
778# Pros & Cons
780- Not everything is included: some cool stuff like array_agg and window functions are not available yet.
782- But development is _very_ active. `IN` clauses were missing but were added in the 4.0 released recently.
784- It's bleeding edge. You need latest LTS to be comfortable and psql >= 9.5.
786- It's Postgres-only but I would say that's a feature. Multi-DB tools are even more complex.
788- The author & maintainer is <3.
790- Typesafe. 'nuff said.
792---
794# Thank you !