git.iko.soy/squeal
on dev-bytea help sign in
squeal/README.md
291 lines 10 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 operations
51## installation
53`stack install squeal-postgresql`
55## testing
57Start postgres on localhost port `5432` and create a database named `exampledb`.
59`stack test`
61## contributing
63We welcome contributors.
64Please make pull requests on the `dev` branch instead of `master`.
65The `Issues` page is a good place to communicate.
67## usage
69Let's see an example!
71First, we need some language extensions because Squeal uses modern GHC
72features.
74```Haskell
75>>> :set -XDataKinds -XDeriveGeneric -XOverloadedLabels
76>>> :set -XOverloadedStrings -XTypeApplications -XTypeOperators
77```
79We'll need some imports.
81```Haskell
82>>> import Control.Monad (void)
83>>> import Control.Monad.IO.Class (liftIO)
84>>> import Data.Int (Int32)
85>>> import Data.Text (Text)
86>>> import Squeal.PostgreSQL
87>>> import Squeal.PostgreSQL.Render
88```
90We'll use generics to easily convert between Haskell and PostgreSQL values.
92```Haskell
93>>> import qualified Generics.SOP as SOP
94>>> import qualified GHC.Generics as GHC
95```
97The first step is to define the schema of our database. This is where
98we use `DataKinds` and `TypeOperators`.
100```Haskell
101>>> :{
102type Schema =
103 '[ "users" ::: 'Table (
104 '[ "pk_users" ::: 'PrimaryKey '["id"] ] :=>
105 '[ "id" ::: 'Def :=> 'NotNull 'PGint4
106 , "name" ::: 'NoDef :=> 'NotNull 'PGtext
107 ])
108 , "emails" ::: 'Table (
109 '[ "pk_emails" ::: 'PrimaryKey '["id"]
110 , "fk_user_id" ::: 'ForeignKey '["user_id"] "users" '["id"]
111 ] :=>
112 '[ "id" ::: 'Def :=> 'NotNull 'PGint4
113 , "user_id" ::: 'NoDef :=> 'NotNull 'PGint4
114 , "email" ::: 'NoDef :=> 'Null 'PGtext
115 ])
116 ]
117:}
118```
120Notice the use of type operators.
122`:::` is used to pair an alias `GHC.TypeLits.Symbol` with a `SchemumType`,
123a `TableConstraint` or a `ColumnType`. It is intended to connote Haskell's `::`
124operator.
126`:=>` is used to pair `TableConstraints` with a `ColumnsType`,
127yielding a `TableType`, or to pair a `ColumnConstraint` with a `NullityType`,
128yielding a `ColumnType`. It is intended to connote Haskell's `=>` operator
130Next, we'll write `Definition`s to set up and tear down the schema. In
131Squeal, a `Definition` like `createTable`, `alterTable` or `dropTable`
132has two type parameters, corresponding to the schema
133before being run and the schema after. We can compose definitions using `>>>`.
134Here and in the rest of our commands we make use of overloaded
135labels to refer to named tables and columns in our schema.
137```Haskell
138>>> :{
139let
140 setup :: Definition '[] Schema
141 setup =
142 createTable #users
143 ( serial `as` #id :*
144 (text & notNullable) `as` #name )
145 ( primaryKey #id `as` #pk_users ) >>>
146 createTable #emails
147 ( serial `as` #id :*
148 (int & notNullable) `as` #user_id :*
149 (text & nullable) `as` #email )
150 ( primaryKey #id `as` #pk_emails :*
151 foreignKey #user_id #users #id
152 OnDeleteCascade OnUpdateCascade `as` #fk_user_id )
153:}
154```
156We can easily see the generated SQL is unsurprising looking.
158```Haskell
159>>> printSQL setup
160CREATE TABLE "users" ("id" serial, "name" text NOT NULL, CONSTRAINT "pk_users" PRIMARY KEY ("id"));
161CREATE 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);
162```
164Notice that `setup` starts with an empty schema `'[]` and produces `Schema`.
165In our `createTable` commands we included `TableConstraint`s to define
166primary and foreign keys, making them somewhat complex. Our `teardown`
167`Definition` is simpler.
169```Haskell
170>>> :{
171let
172 teardown :: Definition Schema '[]
173 teardown = dropTable #emails >>> dropTable #users
174:}
176>>> printSQL teardown
177DROP TABLE "emails";
178DROP TABLE "users";
179```
181Next, we'll write `Manipulation`s to insert data into our two tables.
182A `Manipulation` like `insertRow`, `update` or `deleteFrom`
183has three type parameters, the schema it refers to, a list of parameters
184it can take as input, and a list of columns it produces as output. When
185we insert into the users table, we will need a parameter for the `name`
186field but not for the `id` field. Since it's serial, we can use a default
187value. However, since the emails table refers to the users table, we will
188need to retrieve the user id that the insert generates and insert it into
189the emails table. Take a careful look at the type and definition of both
190of our inserts.
192```Haskell
193>>> :{
194let
195 insertUser :: Manipulation Schema '[ 'NotNull 'PGtext ] '[ "fromOnly" ::: 'NotNull 'PGint4 ]
196 insertUser = insertRow #users
197 (Default `as` #id :* Set (param @1) `as` #name)
198 OnConflictDoNothing (Returning (#id `as` #fromOnly))
199:}
201>>> :{
202let
203 insertEmail :: Manipulation Schema '[ 'NotNull 'PGint4, 'Null 'PGtext] '[]
204 insertEmail = insertRow #emails
205 ( Default `as` #id :*
206 Set (param @1) `as` #user_id :*
207 Set (param @2) `as` #email )
208 OnConflictDoNothing (Returning Nil)
209:}
211>>> printSQL insertUser
212INSERT INTO "users" ("id", "name") VALUES (DEFAULT, ($1 :: text)) ON CONFLICT DO NOTHING RETURNING "id" AS "fromOnly"
213>>> printSQL insertEmail
214INSERT INTO "emails" ("id", "user_id", "email") VALUES (DEFAULT, ($1 :: int4), ($2 :: text)) ON CONFLICT DO NOTHING
215```
217Next we write a `Query` to retrieve users from the database. We're not
218interested in the ids here, just the usernames and email addresses. We
219need to use an inner join to get the right result. A `Query` is like a
220`Manipulation` with the same kind of type parameters.
222```Haskell
223>>> :{
224let
225 getUsers :: Query Schema '[]
226 '[ "userName" ::: 'NotNull 'PGtext
227 , "userEmail" ::: 'Null 'PGtext ]
228 getUsers = select
229 (#u ! #name `as` #userName :* #e ! #email `as` #userEmail)
230 ( from (table (#users `as` #u)
231 & innerJoin (table (#emails `as` #e))
232 (#u ! #id .== #e ! #user_id)) )
233:}
235>>> printSQL getUsers
236SELECT "u"."name" AS "userName", "e"."email" AS "userEmail" FROM "users" AS "u" INNER JOIN "emails" AS "e" ON ("u"."id" = "e"."user_id")
237```
239Now that we've defined the SQL side of things, we'll need a Haskell type
240for users. We give the type `Generics.SOP.Generic` and
241`Generics.SOP.HasDatatypeInfo` instances so that we can decode the rows
242we receive when we run `getUsers`. Notice that the record fields of the
243`User` type match the column names of `getUsers`.
245```Haskell
246>>> data User = User { userName :: Text, userEmail :: Maybe Text } deriving (Show, GHC.Generic)
247>>> instance SOP.Generic User
248>>> instance SOP.HasDatatypeInfo User
249```
251Let's also create some users to add to the database.
253```Haskell
254>>> :{
255let
256 users :: [User]
257 users =
258 [ User "Alice" (Just "alice@gmail.com")
259 , User "Bob" Nothing
260 , User "Carole" (Just "carole@hotmail.com")
261 ]
262:}
263```
265Now we can put together all the pieces into a program. The program
266connects to the database, sets up the schema, inserts the user data
267(using prepared statements as an optimization), queries the user
268data and prints it out and finally closes the connection. We can thread
269the changing schema information through by using the indexed `PQ` monad
270transformer and when the schema doesn't change we can use `Monad` and
271`MonadPQ` functionality.
273```Haskell
274>>> :{
275let
276 session :: PQ Schema Schema IO ()
277 session = do
278 idResults <- traversePrepared insertUser (Only . userName <$> users)
279 ids <- traverse (fmap fromOnly . getRow 0) idResults
280 traversePrepared_ insertEmail (zip (ids :: [Int32]) (userEmail <$> users))
281 usersResult <- runQuery getUsers
282 usersRows <- getRows usersResult
283 liftIO $ print (usersRows :: [User])
284in
285 void . withConnection "host=localhost port=5432 dbname=exampledb" $
286 define setup
287 & pqThen session
288 & pqThen (define teardown)
289:}
290[User {userName = "Alice", userEmail = Just "alice@gmail.com"},User {userName = "Bob", userEmail = Nothing},User {userName = "Carole", userEmail = Just "carole@hotmail.com"}]
291```