git.iko.soy/squeal
on master help sign in
squeal/README.md
280 lines 9.6 kB history raw
1# squeal
3![squeal-icon](http://www.emoticonswallpapers.com/emotion/cute-big-pig/cute-pig-smiley-046.gif)
5[![CircleCI](https://circleci.com/gh/echatav/squeal.svg?style=svg&circle-token=a699a654ef50db2c3744fb039cf2087c484d1226)](https://circleci.com/gh/morphismtech/squeal)
7[Github](https://github.com/morphismtech/squeal)
9[Hackage](https://hackage.haskell.org/package/squeal-postgresql)
11[Stackage](https://www.stackage.org/package/squeal-postgresql)
13[YouTube](https://www.youtube.com/watch?v=rWfEQfAaNc4)
15## introduction
17Squeal is a deep embedding of SQL into Haskell. By "deep embedding", I am abusing the
18term somewhat. What I mean is that Squeal embeds both SQL terms and SQL types
19into Haskell at the term and type levels respectively. This leads to a very high level
20of type-safety in Squeal.
22Squeal embeds not just the structured query language of SQL but also the
23data manipulation language and the data definition language; that's `SELECT`,
24`INSERT`, `UPDATE`, `DELETE`, `WITH`, `CREATE`, `DROP`, and `ALTER` commands.
26Squeal expressions closely match their corresponding SQL expressions so that
27the SQL they actually generate is completely predictable. They are also highly
28composable and cover a large portion of SQL.
30## features
32* generic encoding of Haskell tuples and records into query parameters
33 and generic decoding of query results into Haskell records
34 using [`generics-sop`](https://hackage.haskell.org/package/generics-sop)
35* access to SQL alias system using the `OverloadedLabels` extension
36* type-safe `NULL` and `DEFAULT`
37* type-safe SQL constraints `CHECK`, `UNIQUE`, `PRIMARY KEY` and `FOREIGN KEY`
38* type-safe aggregation
39* escape hatches for writing raw SQL
40* [`mtl`](https://hackage.haskell.org/package/mtl) compatible monad transformer
41 for executing as well as preparing queries and manipulations
42 and [Atkey](https://bentnib.org/paramnotions-jfp.pdf) indexed monad transformer
43 for executing definitions.
44* linear, invertible migrations
45* connection pools
46* transactions
47* views
48* array, composite and enumerated types
49* json functions and operations
50* multischema support
51* correlated subqueries
52* window functions
53* text search
55## installation
57`stack install squeal-postgresql`
59## testing
61Start postgres on localhost port `5432` and create a database named `exampledb`.
63`stack test`
65## contributing
67We welcome contributors.
68Please make pull requests on the `dev` branch instead of `master`.
69The `Issues` page is a good place to communicate.
71## usage
73Let's see an example!
75First, we need some language extensions because Squeal uses modern GHC
76features.
78```Haskell
79>>> :set -XDataKinds -XDeriveGeneric -XOverloadedLabels -XFlexibleContexts
80>>> :set -XOverloadedStrings -XTypeApplications -XTypeOperators -XGADTs
81```
83We'll need some imports.
85```Haskell
86>>> import Control.Monad.IO.Class (liftIO)
87>>> import Data.Int (Int32)
88>>> import Data.Text (Text)
89>>> import Squeal.PostgreSQL
90```
92We'll use generics to easily convert between Haskell and PostgreSQL values.
94```Haskell
95>>> import qualified Generics.SOP as SOP
96>>> import qualified GHC.Generics as GHC
97```
99The first step is to define the schema of our database. This is where
100we use `DataKinds` and `TypeOperators`.
102```Haskell
103>>> :{
104type UsersColumns =
105 '[ "id" ::: 'Def :=> 'NotNull 'PGint4
106 , "name" ::: 'NoDef :=> 'NotNull 'PGtext ]
107type UsersConstraints = '[ "pk_users" ::: 'PrimaryKey '["id"] ]
108type EmailsColumns =
109 '[ "id" ::: 'Def :=> 'NotNull 'PGint4
110 , "user_id" ::: 'NoDef :=> 'NotNull 'PGint4
111 , "email" ::: 'NoDef :=> 'Null 'PGtext ]
112type EmailsConstraints =
113 '[ "pk_emails" ::: 'PrimaryKey '["id"]
114 , "fk_user_id" ::: 'ForeignKey '["user_id"] "users" '["id"] ]
115type Schema =
116 '[ "users" ::: 'Table (UsersConstraints :=> UsersColumns)
117 , "emails" ::: 'Table (EmailsConstraints :=> EmailsColumns) ]
118type Schemas = Public Schema
119:}
120```
122Notice the use of type operators.
124`:::` is used to pair an alias `GHC.TypeLits.Symbol` with a `SchemumType`,
125a `TableConstraint` or a `ColumnType`. It is intended to connote Haskell's `::`
126operator.
128`:=>` is used to pair `TableConstraints` with a `ColumnsType`,
129yielding a `TableType`, or to pair a `ColumnConstraint` with a `NullityType`,
130yielding a `ColumnType`. It is intended to connote Haskell's `=>` operator
132Next, we'll write `Definition`s to set up and tear down the schema. In
133Squeal, a `Definition` like `createTable`, `alterTable` or `dropTable`
134has two type parameters, corresponding to the schema
135before being run and the schema after. We can compose definitions using `>>>`.
136Here and in the rest of our commands we make use of overloaded
137labels to refer to named tables and columns in our schema.
139```Haskell
140>>> :{
141let
142 setup :: Definition (Public '[]) Schemas
143 setup =
144 createTable #users
145 ( serial `as` #id :*
146 (text & notNullable) `as` #name )
147 ( primaryKey #id `as` #pk_users ) >>>
148 createTable #emails
149 ( serial `as` #id :*
150 (int & notNullable) `as` #user_id :*
151 (text & nullable) `as` #email )
152 ( primaryKey #id `as` #pk_emails :*
153 foreignKey #user_id #users #id
154 OnDeleteCascade OnUpdateCascade `as` #fk_user_id )
155:}
156```
158We can easily see the generated SQL is unsurprising looking.
160```Haskell
161>>> printSQL setup
162CREATE TABLE "users" ("id" serial, "name" text NOT NULL, CONSTRAINT "pk_users" PRIMARY KEY ("id"));
163CREATE TABLE "emails" ("id" serial, "user_id" int NOT NULL, "email" text NULL, CONSTRAINT "pk_emails" PRIMARY KEY ("id"), CONSTRAINT "fk_user_id" FOREIGN KEY ("user_id") REFERENCES "users" ("id") ON DELETE CASCADE ON UPDATE CASCADE);
164```
166Notice that `setup` starts with an empty schema `'[]` and produces `Schema`.
167In our `createTable` commands we included `TableConstraint`s to define
168primary and foreign keys, making them somewhat complex. Our `teardown`
169`Definition` is simpler.
171```Haskell
172>>> :{
173let
174 teardown :: Definition Schemas (Public '[])
175 teardown = dropTable #emails >>> dropTable #users
176:}
178>>> printSQL teardown
179DROP TABLE "emails";
180DROP TABLE "users";
181```
183We'll need a Haskell type for @User@s. We give the type `Generics.SOP.Generic` and
184`Generics.SOP.HasDatatypeInfo` instances so that we can encode and decode @User@s.
186```Haskell
187>>> data User = User { userName :: Text, userEmail :: Maybe Text } deriving (Show, GHC.Generic)
188>>> instance SOP.Generic User
189>>> instance SOP.HasDatatypeInfo User
190```
192Next, we'll write `Manipulation_`s to insert @User@s into our two tables.
193A `Manipulation_` like `insertInto`, `update` or `deleteFrom`
194has three type parameters, the schemas it refers to, input parameters
195and an output row. When
196we insert into the users table, we will need a parameter for the @name@
197field but not for the @id@ field. Since it's serial, we can use a default
198value. However, since the emails table refers to the users table, we will
199need to retrieve the user id that the insert generates and insert it into
200the emails table. We can do this in a single `Manipulation_` by using a
201`with` statement.
203```Haskell
204>>> :{
205let
206 insertUser :: Manipulation_ Schemas User ()
207 insertUser = with (u `as` #u) e
208 where
209 u = insertInto #users
210 (Values_ (Default `as` #id :* Set (param @1) `as` #name))
211 OnConflictDoRaise (Returning_ (#id :* param @2 `as` #email))
212 e = insertInto_ #emails $ Select
213 (Default `as` #id :* Set (#u ! #id) `as` #user_id :* Set (#u ! #email) `as` #email)
214 (from (common #u))
215:}
217>>> printSQL insertUser
218WITH "u" AS (INSERT INTO "users" ("id", "name") VALUES (DEFAULT, ($1 :: text)) RETURNING "id" AS "id", ($2 :: text) AS "email") INSERT INTO "emails" ("user_id", "email") SELECT "u"."id", "u"."email" FROM "u" AS "u"
219```
221Next we write a `Query_` to retrieve users from the database. We're not
222interested in the ids here, just the usernames and email addresses. We
223need to use an `innerJoin` to get the right result. A `Query_` is like a
224`Manipulation_` with the same kind of type parameters.
226```Haskell
227>>> :{
228let
229 getUsers :: Query_ Schemas () User
230 getUsers = select_
231 (#u ! #name `as` #userName :* #e ! #email `as` #userEmail)
232 ( from (table (#users `as` #u)
233 & innerJoin (table (#emails `as` #e))
234 (#u ! #id .== #e ! #user_id)) )
235:}
237>>> printSQL getUsers
238SELECT "u"."name" AS "userName", "e"."email" AS "userEmail" FROM "users" AS "u" INNER JOIN "emails" AS "e" ON ("u"."id" = "e"."user_id")
239```
241Let's create some users to add to the database.
243```Haskell
244>>> :{
245let
246 users :: [User]
247 users =
248 [ User "Alice" (Just "alice@gmail.com")
249 , User "Bob" Nothing
250 , User "Carole" (Just "carole@hotmail.com")
251 ]
252:}
253```
255Now we can put together all the pieces into a program. The program
256connects to the database, sets up the schema, inserts the user data
257(using prepared statements as an optimization), queries the user
258data and prints it out and finally closes the connection. We can thread
259the changing schema information through by using the indexed `PQ` monad
260transformer and when the schema doesn't change we can use `Monad` and
261`MonadPQ` functionality.
263```Haskell
264>>> :{
265let
266 session :: PQ Schemas Schemas IO ()
267 session = do
268 _ <- traversePrepared_ insertUser users
269 usersResult <- runQuery getUsers
270 usersRows <- getRows usersResult
271 liftIO $ print (usersRows :: [User])
272in
273 withConnection "host=localhost port=5432 dbname=exampledb" $
274 define setup
275 & pqThen session
276 & pqThen (define teardown)
277:}
278[User {userName = "Alice", userEmail = Just "alice@gmail.com"},User {userName = "Bob", userEmail = Nothing},User {userName = "Carole", userEmail = Just "carole@hotmail.com"}]
279-}
280```