Database.Persist.Sql

data Switch = On | Off
  deriving (Show, Eq)

instance PersistField Switch where
  toPersistValue s = case s of
    On -> PersistBool True
    Off -> PersistBool False
  fromPersistValue (PersistBool b) = if b then Right On else Right Off
  fromPersistValue x = Left $ "File.hs: When trying to deserialize a Switch: expected PersistBool, received: " <> T.pack (show x)

instance PersistFieldSql Switch where
  sqlType _ = SqlBool

import qualified Data.UUID as UUID
instance PersistField UUID where
  toPersistValue = PersistLiteralEncoded . toASCIIBytes
  fromPersistValue (PersistLiteralEncoded uuid) =
    case fromASCIIBytes uuid of
      Nothing -> Left $ "Model/CustomTypes.hs: Failed to deserialize a UUID; received: " <> T.pack (show uuid)
      Just uuid' -> Right uuid'
  fromPersistValue x = Left $ "File.hs: When trying to deserialize a UUID: expected PersistLiteralEncoded, received: "-- >  <> T.pack (show x)

instance PersistFieldSql UUID where
  sqlType _ = SqlOther "uuid"

CREATE DOMAIN ssn AS text
      CHECK ( value ~ '^[0-9]{9}$');

instance PersistFieldSQL SSN where
  sqlType _ = SqlOther "ssn"

CREATE TYPE rainbow_color AS ENUM ('red', 'orange', 'yellow', 'green', 'blue', 'indigo', 'violet');

instance PersistFieldSQL RainbowColor where
  sqlType _ = SqlOther "rainbow_color"

SELECT ??
FROM my_long_table_name AS mltn
INNER JOIN other_table AS ot
   ON mltn.some_col = ot.other_col
WHERE ...

getStuff :: SqlPersistM [EntityWithPrefix "mltn" MyLongTableName]
getStuff = rawSql queryText []

getStuff :: SqlPersistM [Entity MyLongTableName]
getStuff = unPrefix @"mltn" <$> rawSql queryText []

myQuery :: SqlPersistM [Entity Person]
myQuery = fmap (unPrefix @"p") $ rawSql query []
  where
    query = "SELECT ?? FROM person AS p"

{-# LANGUAGE GADTs #-}
{-# LANGUAGE ScopedTypeVariables #-}
{-# LANGUAGE OverloadedStrings #-}
{-# LANGUAGE MultiParamTypeClasses #-}
{-# LANGUAGE TypeFamilies#-}
{-# LANGUAGE TemplateHaskell#-}
{-# LANGUAGE QuasiQuotes#-}
{-# LANGUAGE GeneralizedNewtypeDeriving #-}

import Control.Monad.IO.Class  (liftIO)
import Control.Monad.Logger
import Conduit
import Database.Persist
import Database.Sqlite
import Database.Persist.Sqlite
import Database.Persist.TH

share [mkPersist sqlSettings, mkMigrate "migrateAll"] [persistLowerCase|
Person
  name String
  age Int Maybe
  deriving Show
|]

openConnection :: LogFunc -> IO SqlBackend
openConnection logfn = do
 conn <- open "/home/sibi/test.db"
 wrapConnection conn logfn

main :: IO ()
main = do
  runNoLoggingT $ runResourceT $ withSqlConn openConnection (\backend ->
                                      flip runSqlConn backend $ do
                                        runMigration migrateAll
                                        insert_ $ Person "John doe" $ Just 35
                                        insert_ $ Person "Divya" $ Just 36
                                        (pers :: [Entity Person]) <- selectList [] []
                                        liftIO $ print pers
                                        return ()
                                     )

Migrating: CREATE TABLE "person"("id" INTEGER PRIMARY KEY,"name" VARCHAR NOT NULL,"age" INTEGER NULL)
[Entity {entityKey = PersonKey {unPersonKey = SqlBackendKey {unSqlBackendKey = 1}}, entityVal = Person {personName = "John doe", personAge = Just 35}},Entity {entityKey = PersonKey {unPersonKey = SqlBackendKey {unSqlBackendKey = 2}}, entityVal = Person {personName = "Hema", personAge = Just 36}}]