squeal-postgresql-0.5.2.0: Squeal PostgreSQL Library

Copyright(c) Eitan Chatav 2019
Maintainereitan@morphism.tech
Stabilityexperimental
Safe HaskellNone
LanguageHaskell2010

Squeal.PostgreSQL

Description

Squeal is a deep embedding of PostgreSQL in Haskell. Let's see an example!

First, we need some language extensions because Squeal uses modern GHC features.

>>> :set -XDataKinds -XDeriveGeneric -XOverloadedLabels -XFlexibleContexts
>>> :set -XOverloadedStrings -XTypeApplications -XTypeOperators -XGADTs

We'll need some imports.

>>> import Control.Monad.IO.Class (liftIO)
>>> import Data.Int (Int32)
>>> import Data.Text (Text)
>>> import Squeal.PostgreSQL

We'll use generics to easily convert between Haskell and PostgreSQL values.

>>> import qualified Generics.SOP as SOP
>>> import qualified GHC.Generics as GHC

The first step is to define the schema of our database. This is where we use DataKinds and TypeOperators.

>>> :{
type UsersColumns =
  '[ "id"   :::   'Def :=> 'NotNull 'PGint4
   , "name" ::: 'NoDef :=> 'NotNull 'PGtext ]
type UsersConstraints = '[ "pk_users" ::: 'PrimaryKey '["id"] ]
type EmailsColumns =
  '[ "id" ::: 'Def :=> 'NotNull 'PGint4
   , "user_id" ::: 'NoDef :=> 'NotNull 'PGint4
   , "email" ::: 'NoDef :=> 'Null 'PGtext ]
type EmailsConstraints =
  '[ "pk_emails"  ::: 'PrimaryKey '["id"]
   , "fk_user_id" ::: 'ForeignKey '["user_id"] "users" '["id"] ]
type Schema =
  '[ "users" ::: 'Table (UsersConstraints :=> UsersColumns)
   , "emails" ::: 'Table (EmailsConstraints :=> EmailsColumns) ]
type Schemas = Public Schema
:}

Notice the use of type operators.

::: is used to pair an alias Symbol with a SchemasType, a SchemumType, a TableConstraint or a ColumnType. It is intended to connote Haskell's :: operator.

:=> is used to pair TableConstraints with a ColumnsType, yielding a TableType, or to pair a ColumnConstraint with a NullityType, yielding a ColumnType. It is intended to connote Haskell's => operator

Next, we'll write Definitions to set up and tear down the schema. In Squeal, a Definition like createTable, alterTable or dropTable has two type parameters, corresponding to the schema before being run and the schema after. We can compose definitions using >>>. Here and in the rest of our commands we make use of overloaded labels to refer to named tables and columns in our schema.

>>> :{
let
  setup :: Definition (Public '[]) Schemas
  setup =
    createTable #users
      ( serial `as` #id :*
        (text & notNullable) `as` #name )
      ( primaryKey #id `as` #pk_users ) >>>
    createTable #emails
      ( serial `as` #id :*
        (int & notNullable) `as` #user_id :*
        (text & nullable) `as` #email )
      ( primaryKey #id `as` #pk_emails :*
        foreignKey #user_id #users #id
          OnDeleteCascade OnUpdateCascade `as` #fk_user_id )
:}

We can easily see the generated SQL is unsurprising looking.

>>> printSQL setup
CREATE TABLE "users" ("id" serial, "name" text NOT NULL, CONSTRAINT "pk_users" PRIMARY KEY ("id"));
CREATE 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);

Notice that setup starts with an empty public schema (Public '[]) and produces Schemas. In our createTable commands we included TableConstraints to define primary and foreign keys, making them somewhat complex. Our teardown Definition is simpler.

>>> :{
let
  teardown :: Definition Schemas (Public '[])
  teardown = dropTable #emails >>> dropTable #users
:}
>>> printSQL teardown
DROP TABLE "emails";
DROP TABLE "users";

We'll need a Haskell type for Users. We give the type Generic and HasDatatypeInfo instances so that we can encode and decode Users.

>>> data User = User { userName :: Text, userEmail :: Maybe Text } deriving (Show, GHC.Generic)
>>> instance SOP.Generic User
>>> instance SOP.HasDatatypeInfo User

Next, we'll write Manipulation_s to insert Users into our two tables. A Manipulation_ like insertInto, update or deleteFrom has three type parameters, the schemas it refers to, input parameters and an output row. When we insert into the users table, we will need a parameter for the name field but not for the id field. Since it's serial, we can use a default value. However, since the emails table refers to the users table, we will need to retrieve the user id that the insert generates and insert it into the emails table. We can do this in a single Manipulation_ by using a with statement.

>>> :{
let
  insertUser :: Manipulation_ Schemas User ()
  insertUser = with (u `as` #u) e
    where
      u = insertInto #users
        (Values_ (Default `as` #id :* Set (param @1) `as` #name))
        OnConflictDoRaise (Returning_ (#id :* param @2 `as` #email))
      e = insertInto_ #emails $ Select
        (Default `as` #id :* Set (#u ! #id) `as` #user_id :* Set (#u ! #email) `as` #email)
        (from (common #u))
:}
>>> printSQL insertUser
WITH "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"

Next we write a Query_ to retrieve users from the database. We're not interested in the ids here, just the usernames and email addresses. We need to use an innerJoin to get the right result. A Query_ is like a Manipulation_ with the same kind of type parameters.

>>> :{
let
  getUsers :: Query_ Schemas () User
  getUsers = select_
    (#u ! #name `as` #userName :* #e ! #email `as` #userEmail)
    ( from (table (#users `as` #u)
      & innerJoin (table (#emails `as` #e))
        (#u ! #id .== #e ! #user_id)) )
:}
>>> printSQL getUsers
SELECT "u"."name" AS "userName", "e"."email" AS "userEmail" FROM "users" AS "u" INNER JOIN "emails" AS "e" ON ("u"."id" = "e"."user_id")

Let's create some users to add to the database.

>>> :{
let
  users :: [User]
  users =
    [ User "Alice" (Just "alice@gmail.com")
    , User "Bob" Nothing
    , User "Carole" (Just "carole@hotmail.com")
    ]
:}

Now we can put together all the pieces into a program. The program connects to the database, sets up the schema, inserts the user data (using prepared statements as an optimization), queries the user data and prints it out and finally closes the connection. We can thread the changing schema information through by using the indexed PQ monad transformer and when the schema doesn't change we can use Monad and MonadPQ functionality.

>>> :{
let
  session :: PQ Schemas Schemas IO ()
  session = do
    _ <- traversePrepared_ insertUser users
    usersResult <- runQuery getUsers
    usersRows <- getRows usersResult
    liftIO $ print (usersRows :: [User])
in
  withConnection "host=localhost port=5432 dbname=exampledb" $
    define setup
    & pqThen session
    & pqThen (define teardown)
:}
[User {userName = "Alice", userEmail = Just "alice@gmail.com"},User {userName = "Bob", userEmail = Nothing},User {userName = "Carole", userEmail = Just "carole@hotmail.com"}]
Synopsis

Documentation

class RenderSQL sql where Source #

A class for rendering SQL

Methods

renderSQL :: sql -> ByteString Source #

Instances
RenderSQL TimeUnit Source # 
Instance details

Defined in Squeal.PostgreSQL.Expression.Time

RenderSQL OnUpdateClause Source #

Render OnUpdateClause.

Instance details

Defined in Squeal.PostgreSQL.Definition

RenderSQL OnDeleteClause Source #

Render OnDeleteClause.

Instance details

Defined in Squeal.PostgreSQL.Definition

RenderSQL DeferrableMode Source #

Render a DeferrableMode.

Instance details

Defined in Squeal.PostgreSQL.Transaction

RenderSQL AccessMode Source #

Render an AccessMode.

Instance details

Defined in Squeal.PostgreSQL.Transaction

RenderSQL IsolationLevel Source #

Render an IsolationLevel.

Instance details

Defined in Squeal.PostgreSQL.Transaction

RenderSQL TransactionMode Source #

Render a TransactionMode.

Instance details

Defined in Squeal.PostgreSQL.Transaction

KnownSymbol alias => RenderSQL (Alias alias) Source #
>>> printSQL (#jimbob :: Alias "jimbob")
"jimbob"
Instance details

Defined in Squeal.PostgreSQL.Alias

Methods

renderSQL :: Alias alias -> ByteString Source #

KnownSymbol label => RenderSQL (PGlabel label) Source # 
Instance details

Defined in Squeal.PostgreSQL.Schema

Methods

renderSQL :: PGlabel label -> ByteString Source #

(KnownSymbol q, KnownSymbol a) => RenderSQL (QualifiedAlias q a) Source # 
Instance details

Defined in Squeal.PostgreSQL.Alias

RenderSQL (TypeExpression schemas ty) Source # 
Instance details

Defined in Squeal.PostgreSQL.Expression.Type

Methods

renderSQL :: TypeExpression schemas ty -> ByteString Source #

RenderSQL (GroupByClause grp from) Source #

Renders a GroupByClause.

Instance details

Defined in Squeal.PostgreSQL.Query

Methods

renderSQL :: GroupByClause grp from -> ByteString Source #

RenderSQL (By from by) Source # 
Instance details

Defined in Squeal.PostgreSQL.Query

Methods

renderSQL :: By from by -> ByteString Source #

RenderSQL (ConflictTarget constraints) Source #

Render a ConflictTarget

Instance details

Defined in Squeal.PostgreSQL.Manipulation

Methods

renderSQL :: ConflictTarget constraints -> ByteString Source #

RenderSQL (ColumnTypeExpression schemas ty) Source # 
Instance details

Defined in Squeal.PostgreSQL.Definition

RenderSQL (Definition schemas0 schemas1) Source # 
Instance details

Defined in Squeal.PostgreSQL.Definition

Methods

renderSQL :: Definition schemas0 schemas1 -> ByteString Source #

All KnownSymbol aliases => RenderSQL (NP Alias aliases) Source # 
Instance details

Defined in Squeal.PostgreSQL.Alias

Methods

renderSQL :: NP Alias aliases -> ByteString Source #

All KnownSymbol labels => RenderSQL (NP PGlabel labels) Source # 
Instance details

Defined in Squeal.PostgreSQL.Schema

Methods

renderSQL :: NP PGlabel labels -> ByteString Source #

SListI tys => RenderSQL (Distinction (NP (Expression outer commons grp schemas params from)) tys) Source # 
Instance details

Defined in Squeal.PostgreSQL.Expression.Aggregate

Methods

renderSQL :: Distinction (NP (Expression outer commons grp schemas params from)) tys -> ByteString Source #

RenderSQL (Distinction (Expression outer commons grp schemas params from) ty) Source # 
Instance details

Defined in Squeal.PostgreSQL.Expression.Aggregate

Methods

renderSQL :: Distinction (Expression outer commons grp schemas params from) ty -> ByteString Source #

(forall (x :: k). RenderSQL (expr x)) => RenderSQL (Optional expr ty) Source # 
Instance details

Defined in Squeal.PostgreSQL.Manipulation

Methods

renderSQL :: Optional expr ty -> ByteString Source #

(forall (t0 :: k) (t1 :: k). RenderSQL (p t0 t1)) => RenderSQL (AlignedList p x0 x1) Source # 
Instance details

Defined in Squeal.PostgreSQL.List

Methods

renderSQL :: AlignedList p x0 x1 -> ByteString Source #

RenderSQL (QueryClause commons schemas params columns) Source # 
Instance details

Defined in Squeal.PostgreSQL.Manipulation

Methods

renderSQL :: QueryClause commons schemas params columns -> ByteString Source #

RenderSQL (Manipulation commons schemas params columns) Source # 
Instance details

Defined in Squeal.PostgreSQL.Manipulation

Methods

renderSQL :: Manipulation commons schemas params columns -> ByteString Source #

RenderSQL (TableConstraintExpression sch tab schemas constraint) Source # 
Instance details

Defined in Squeal.PostgreSQL.Definition

Methods

renderSQL :: TableConstraintExpression sch tab schemas constraint -> ByteString Source #

(forall (c :: FromType) (s :: SchemasType) (p :: [NullityType]) (r :: RowType). RenderSQL (statement c s p r)) => RenderSQL (CommonTableExpression statement schemas params commons0 commons1) Source # 
Instance details

Defined in Squeal.PostgreSQL.Query

Methods

renderSQL :: CommonTableExpression statement schemas params commons0 commons1 -> ByteString Source #

RenderSQL (Query outer commons schemas params row) Source # 
Instance details

Defined in Squeal.PostgreSQL.Query

Methods

renderSQL :: Query outer commons schemas params row -> ByteString Source #

RenderSQL (ConflictAction tab commons schemas params columns) Source # 
Instance details

Defined in Squeal.PostgreSQL.Manipulation

Methods

renderSQL :: ConflictAction tab commons schemas params columns -> ByteString Source #

RenderSQL (ReturningClause commons schemas params from row) Source # 
Instance details

Defined in Squeal.PostgreSQL.Manipulation

Methods

renderSQL :: ReturningClause commons schemas params from row -> ByteString Source #

RenderSQL (SortExpression outer commons grp schemas params from) Source # 
Instance details

Defined in Squeal.PostgreSQL.Expression.Sort

Methods

renderSQL :: SortExpression outer commons grp schemas params from -> ByteString Source #

RenderSQL (WindowDefinition outer commons schemas from grp params) Source # 
Instance details

Defined in Squeal.PostgreSQL.Expression.Window

Methods

renderSQL :: WindowDefinition outer commons schemas from grp params -> ByteString Source #

RenderSQL (HavingClause outer commons grp schemas params from) Source #

Render a HavingClause.

Instance details

Defined in Squeal.PostgreSQL.Query

Methods

renderSQL :: HavingClause outer commons grp schemas params from -> ByteString Source #

RenderSQL (TableExpression outer commons grp schemas params from) Source #

Render a TableExpression

Instance details

Defined in Squeal.PostgreSQL.Query

Methods

renderSQL :: TableExpression outer commons grp schemas params from -> ByteString Source #

SListI (TableToColumns table) => RenderSQL (ConflictClause tab commons schemas params table) Source #

Render a ConflictClause.

Instance details

Defined in Squeal.PostgreSQL.Manipulation

Methods

renderSQL :: ConflictClause tab commons schemas params table -> ByteString Source #

RenderSQL (Expression outer commons grp schemas params from ty) Source # 
Instance details

Defined in Squeal.PostgreSQL.Expression

Methods

renderSQL :: Expression outer commons grp schemas params from ty -> ByteString Source #

RenderSQL (WindowFunction outer commons grp schemas params from ty) Source # 
Instance details

Defined in Squeal.PostgreSQL.Expression.Window

Methods

renderSQL :: WindowFunction outer commons grp schemas params from ty -> ByteString Source #

RenderSQL (Selection outer commons grp schemas params from row) Source # 
Instance details

Defined in Squeal.PostgreSQL.Query

Methods

renderSQL :: Selection outer commons grp schemas params from row -> ByteString Source #

RenderSQL (FromClause outer commons schemas params from) Source # 
Instance details

Defined in Squeal.PostgreSQL.Query

Methods

renderSQL :: FromClause outer commons schemas params from -> ByteString Source #

printSQL :: (RenderSQL sql, MonadIO io) => sql -> io () Source #

Print SQL.