# 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).
```haskell
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.
```haskell
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:
```haskell
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:
```haskell
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:
```haskell
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:
```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:
```haskell
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
```haskell
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`:
```haskell
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:
```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:
```haskell
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:
```sql
CREATE TYPE public.user_role
  AS ENUM ('admin', 'important', 'regular');
```
`$( generate def $ fromType "public" "user_role")` will generate the following Haskell datatype:
```haskell
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:
```haskell
{-# 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:
```haskell
data Users
  = Users
    { usersId    :: !UUID
    , usersName  :: !Text
    , usersRole  :: !UserRole
    , usersEmail :: !(Maybe Text)
    , usersAge   :: !(Maybe Int32)
    }
```
...would instead be this (with `allowDuplicateRecordFields = True`):
```haskell
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:
```haskell
data Users
  = Users
    { usersId    :: !UUID
    , usersName  :: !Text
    , usersRole  :: !UserRole
    , usersEmail :: !(Maybe Text)
    , usersAge   :: !(Maybe Int32)
    }
```
...would instead be this (with `newtypePrimaryKeys = True`):
```haskell
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:
```sql
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)
);
```
```haskell
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:
```haskell
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.