Hasql.Generate

A library for compile-time generation of datatypes from Postgres introspection. Inspired by the relational-query-HDBC library's defineTableFromDB functions, but expanded to use Hasql, support more than just tables, and using the features of Postgres. Aims to eliminate most of the boilerplate with using Hasql, the duplicate-definitions and need to ensure database-definitions match code-definitions, and be simple enough to use and understand.

Developing Hasql.Generate

All the tools I use are available in the nix-shell. That will give you just, and justfile (run with just help) will have all the commands you would need. To run the tests, you will need to have postgres running, which again there's a just recipe for (just pg-start), since the tests revolve around the generation of types and functions. The just test command will setup the database, run the tests, and clean itself up afterwards. When done, just stop the database (just pg-stop). Please format and lint your work (just format/just lint).

Using Hasql.Generate

BYOD (Bring Your Own Data/Database)

To start with this library, you'll need a pre-existing PostgreSQL database with tables, views, or types.

Config

From there, you should make a Config that all the TH splices will generate with. This config should be made in a file that you will import into files that generate types (this is a TemplateHaskell requirement, not mine).

data Config
    = Config
      { connection                 :: ConnectionInfo
      , allowDuplicateRecordFields :: Bool
      , newtypePrimaryKeys         :: Bool
      , globalOverrides            :: [(String, Name)]
      }

You will need to make a ConnectionInfo, with either the PgSimpleInfo for "simple" info that's then formed into the connection string, or you can make the libpq connection string yourself (with all the advanced options) with PgConnectionString for full control, but you should know what you're doing for this case.

data ConnectionInfo
    = PgSimpleInfo
      { pgHost     :: Maybe String
      , pgPort     :: Maybe String
      , pgUser     :: Maybe String
      , pgPassword :: Maybe String
      , pgDatabase :: Maybe String
      }
    | PgConnectionString String

If you set any PgSimpleInfo field as Nothing it will use the libpq defaults. If you will be using the default libpq connection details for all of it, you can use Data.Default.def for the ConnectionInfo. If you will not be using DuplicateRecordFields, are fine with the primary keys being "regular" types (ex.: Int instead of a wrapped-newtype TypeNamePk {getTypeNamePk :: Int}), and have no global type-overrides, you can use Data.Default.def for the entire Config.

Generate Haskell Datatypes

Once that's setup, we can use that to generate our data. In a file where you want this type to be generated:

module MyApp.MySqlDatatypes where

import Hasql.Generate (generate, fromTable, fromView, fromType)
-- You made this in the last step!
import MyApp.MyHasqlGenerateConfig (config)

$(generate config $ fromType "public" "user_type")

$(generate config $ fromTable "public" "users")

$(generate config $ fromView "public" "users_view")

If you need to override types on a per-table level:

import Data.Function ((&))
import Hasql.Generate (generate, fromTable, withOverrides)
import MyApp.MyHasqlGenerateConfig (config)

$( generate config
    ( fromTable "public" "users"
      & withOverrides [ ("text", ''String) ]
    )
 )

Or if you want to generically-derive types:

import Data.Aeson ( FromJSON, ToJSON)
import Data.Function ((&))
import GHC.Generics (Generic)
import Hasql.Generate (generate, fromTable, withDerivations)
import MyApp.MyHasqlGenerateConfig (config)

$( generate config
    ( fromTable "public" "users"
      & withDerivations [''Show, ''Eq, ''Generic, ''ToJSON, ''FromJSON] 
    )
 )

(or you can do both, just chain them)

Use Generated Tables

Given the following SQL:

CREATE TYPE public.user_role AS ENUM ('admin', 'important', 'regular');

CREATE TABLE public.users
  ( id     UUID      NOT NULL PRIMARY KEY DEFAULT uuidv7()
  , name   TEXT      NOT NULL
  , "role" user_role NOT NULL DEFAULT 'regular'
  , email  TEXT
  , age    INT4
  );

$( generate def $ fromTable "public" "users") will generate the following Haskell datatype:

data Users
  = Users
    { usersId    :: !UUID
    , usersName  :: !Text
    , usersRole  :: !UserRole
    , usersEmail :: !(Maybe Text)
    , usersAge   :: !(Maybe Int32)
    }

*you are responsible for adding an unqualified import for all types for your database. In the above example, you will need

import Data.Int (Int32)
import Data.Text (Text)
import Data.UUID (UUID)
-- See important note on Postgres types below, in "Use Generated Types"
import MyApp.MyUserRoleLocation (UserRole)

It will also generate:

• usersDecoder
• usersEncoder
• insertUsers
• insertManyUsers
• a HasInsert instance

And when it has a Primary Key defined, it will also generate:

• selectUsers
• selectManyUsers
• updateUsers
• updateManyUsers
• deleteUsers
• deleteManyUsers
• a HasPrimaryKey instance
• a HasSelect instance
• a HasUpdate instance
• a HasDelete instance

If your Primary Key has a DEFAULT and you want to defer to the database to generate PK values on INSERT, add withholdPk to the fromTable generator. Without withholdPk, the primary key you supply in Haskell will be included in the INSERT, and the Postgres DEFAULT will not be used. You still need to create the record with a primary key value, but it can be a dummy value, like 0 for any Int-based type, or UUID.nil for a UUID:

import Data.Function ((&))
import Hasql.Generate (generate, fromTable, withholdPk)
import MyApp.MyHasqlGenerateConfig (config)

$(generate config (fromTable "public" "users" & withholdPk))

Use Generated Views

Given the following SQL:

CREATE VIEW public.users_view AS
  SELECT (name, "role", email, age)
  FROM public.users
  WHERE "role" = 'regular'
;

$( generate def $ fromView "public" "users_view") will generate the following Haskell datatype:

data UsersView
  = UsersView
    { usersViewName  :: !(Maybe Text)
    , usersViewRole  :: !(Maybe UserRole)
    , usersViewEmail :: !(Maybe Text)
    , usersViewAge   :: !(Maybe Int32)
    }

*All field types are Maybes in views because that's how Postgres reports the types on-introspection, even if the underlying table's column is NOT NULL.

It will also generate:

• usersViewDecoder
• selectUsersView
• a HasView instance

Use Generated Types

Given the following SQL:

CREATE TYPE public.user_role
  AS ENUM ('admin', 'important', 'regular');

$( generate def $ fromType "public" "user_role") will generate the following Haskell datatype:

data UserRole
  = Admin
  | Important
  | Regular

It will also generate:

• a PgCodec instance
• a PgColumn instance
• a HasEnum instance

IMPORTANT: If you define a type in Postgres, then use that type in a table you generate with fromTable, you must supply a matching type to the file containing the table's generator splice. You can either generate it with the fromType function as outlined above, or you can make it yourself. If you choose to write your own, you must define PgCodec and PgColumn instances for the type written yourself, and the file generating the table mentioned previously must have those instances in-scope.

The generated PgColumn instance triggers -Worphans because the functional dependency's determining types are Symbol literals. Suppress with:

{-# OPTIONS_GHC -Wno-orphans #-}

Config Options

allowDuplicateRecordFields

Pairs with the DuplicateRecordFields Haskell pragma/language-extension. When active, we drop the camelCase table-name from the front of fields of generated table and view datatypes.

So this:

data Users
  = Users
    { usersId    :: !UUID
    , usersName  :: !Text
    , usersRole  :: !UserRole
    , usersEmail :: !(Maybe Text)
    , usersAge   :: !(Maybe Int32)
    }

...would instead be this (with allowDuplicateRecordFields = True):

data Users
  = Users
    { id    :: !UUID
    , name  :: !Text
    , role' :: !UserRole
    , email :: !(Maybe Text)
    , age   :: !(Maybe Int32)
    }

If you noticed, in this second example, there is an id field. This may conflict with the id function in Prelude, so you may wish to hide the id from Prelude, or name your columns accordingly. The role column is also named role' in Haskell, since role is a reserved keyword (role is also a reserved word in Postgres, hence why we've been double-quoting it in this README). Any Haskell-keywords will append an apostrophe as such.

newtypePrimaryKeys

When active, we generate newtype-wrappers for primary keys of tables.

So this:

data Users
  = Users
    { usersId    :: !UUID
    , usersName  :: !Text
    , usersRole  :: !UserRole
    , usersEmail :: !(Maybe Text)
    , usersAge   :: !(Maybe Int32)
    }

...would instead be this (with newtypePrimaryKeys = True):

newtype UsersPk = UsersPk { getUsersPk :: UUID }

data Users
  = Users
    { usersId    :: !UsersPk
    , usersName  :: !Text
    , usersRole  :: !UserRole
    , usersEmail :: !(Maybe Text)
    , usersAge   :: !(Maybe Int32)
    }

We also support composite primary keys:

CREATE TABLE public.user_items (
    user_id     UUID     NOT NULL,
    item_id     INT4     NOT NULL,
    json_data   JSONB    NOT NULL,
    PRIMARY KEY (user_id, item_id)
);

import Data.Aeson (Value)

data UserItemsPk
  = UserItemsPk
    { userItemsPkUserId :: !UUID
    , userItemsPkItemId :: !Int32
    }
  deriving stock (Show, Eq)

data UserItems
  = UserItems
    { userItemsUserId   :: !UUID
    , userItemsItemId   :: !Int32
    , userItemsJsonData :: !Value
    }

but note we don't replace a field's type with a newtype wrapper, since it's multiple fields and it gets a little weird.

globalOverrides

This allows overrides for all generators using the config. For example, with the Postgres TEXT type, we generate Data.Text.Text by default. If you wanted to use String instead, you would add to the globalOverrides:

import Data.Default (Default(def))
import Hasql.Generate (Config(..))

{- In `("text", ''String)`: `"text"` is the PG type (must be lowercase),
   and `''String` is obviously the type you want it to map to
-}
myConfig :: Config
myConfig = def { globalOverrides = [("text", ''String)] }

If you only want to override a type just for for a table/view generator, see withOverrides, as these take precedence over the global ones.