ANN: squeal-postgresql-qq

Announcements
1

I would like to announce a new package: squeal-postgresql-qq

This is a QuasiQuoter which allows you to write regular SQL statements and have them translated into the corresponding squeal-postgresql statement.

The motivation is that Squeal is an absolutely amazing way to write type safe queries. But I don’t use it so often that I can ever memorize all the little bits of how it contorts SQL syntax into Haskell. Sometimes I know exactly the statement I want to write, but get frustrated relearning how to make it happen. This lets me simply write the SQL statement in SQL language that I know[1] and the QuasiQuoter will do the translation for me!

It is still missing a lot of features (where “features” means coverage of the Postgres SQL syntax), but on the other hand it can be useful in a decent number of straightforward scenarios where you just want to write some basic sql that typechecks.

I plan to continue fleshing out its coverage over time. If you use it and find something you would like that is unsupported, please submit an issue. I will prioritize real-world examples when adding features.

Thanks!

[1] Ha. Well. Allegedly know. Turns out there is a lot about SQL I don’t know.

Examples

Some examples (take from the docs)

For the examples, let’s assume you have a database like this:

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

Insert Example

mkStatement :: Int32 -> Text -> Maybe Text -> Statement DB () ()
mkStatement emailId uid email =
  [ssql|
    insert into
      emails (id, user_id, email)
      values (inline("emailId"), inline(uid), inline_param(email))
  |]

Notice the quotes around @“emailId”@. This is because postgres SQL parsing mandates the unquoted idents be converted to lower case (as a way of being “case insensitive”), which would result in the quasiquoter injecting the lower case @emailid@ variable, which is not in scope. The solution is to double quote the SQL ident so that its casing is preserved.

Select Example

mkStatement
  :: Text
  -> Statement
       DB
       ()
       ( Field "id" Text
       , ( Field "name" Text
         , ( Field "email" (Maybe Text)
           , ()
           )
         )
       )
mkStatement targetEmail =
  [ssql|
    select users.id, users.name, emails.email
    from users
    left outer join emails
    on emails.user_id = users.id
    where emails.email = inline("targetEmail")
  |]
7 Likes