Composite Types with postgresql-simple

Posted on 2016-06-17

Composite Types and Domains in PostgreSQL

Suppose you want to embed an address in a PostgreSQL table. You might do it like so with a composite type:

create type address as
( street    text
, street2   text
, city      text
, state     text
, post_code text
);

create table people
( id           bigserial primary key
, name         text      not null
, email        citext    not null unique
, home_address address   not null
);

Simple enough. Everything seems safe & no chance of nulls in sight!

Now let’s insert some data:

insert into people
  values ( default, 'Jonathan', 'me@example.com'
         , row(null, null, null, null, null));
-- INSERT 0 1
-- Time: 11.131 ms

Oh no!

I inserted an address that was full of nulls. What gives?

Well, PostgreSQL only ensures that the home_address column is not null, not its contents.

If we want to ensure that the address type can never contain nulls we can try doing:

create type address as
( street    text not null
, street2   text          -- ok to be null
, city      text not null
, state     text not null
, post_code text not null
);

But PostgreSQL complains:

create type address as
( street    text not null
, street2   text
, city      text not null
, state     text not null
, post_code text not null
);
-- ERROR:  42601: syntax error at or near "not"
-- LINE 2: ( street    text not null

It turns out that we cannot specify constraints inside a composite type. Now what?

We can use Domains! In PostgreSQL we can alias an existing type and add constraints to it like so:

create domain text_not_null as text
check (value is not null);

Now we can create an address which prevents nulls in the columns we care about:

create type address as
( street    text_not_null
, street2   text
, city      text_not_null
, state     text_not_null
, post_code text_not_null
);

If we try to create an address with the street set to null we will get an error:

select row(null,null,'city','state','zip')::address;
-- ERROR:  23514: value for domain text_not_null violates check constraint "text_not_null_check"

Super!

Reading Composite Types in Haskell

The equivalent Haskell data types for what we created above would be:

data Address =
  Address
  { addressStreet   :: Text
    addressStreet2  :: Maybe Text
    addressCity     :: Text
    addressState    :: Text
    addressPostCode :: Text
  }
  deriving (Eq, Show)

data Person =
  Person
  { personId          :: Integer
  , personName        :: Text
  , personEmail       :: CI Text
  , personHomeAddress :: Address
  }
  deriving (Eq, Show)

To read a Person row with the embedded Address type we need to create a FromField instance for Address like so:

import qualified Data.Attoparsec.ByteString.Char8 as A

instance FromField Address where
  fromField = fromPGRow "address" parseAddress
  -- "address" is the name of the type we created in PostgreSQL

parseAddress :: A.Parser Address
parseAddress = do
  _ <- A.char '('
  addressStreet <- textContent
  _ <- A.char ','
  addressStreet2 <- (Just <$> textContent) <|> pure Nothing)
  _ <- A.char ','
  addressCity <- textContent
  _ <- A.char ','
  addressState <- textContent
  _ <- A.char ','
  addressPostCode <- textContent
  _ <- A.char ')'
  pure Address{..}

Then, our FromRow instance for Person simply becomes:

instance FromRow Person where
  fromRow = Person <$> field <*> field <*> field <*> field

In a nutshell, we have to create a parser to read the content of the field that is returned.

In this case when we execute a query like select id, name, email, home_address from people where id = 1234, PostgreSQL returns something like:

(1234,'Mr X',foo@bar.com,row('123 main st',,dallas,tx,12345))

The home_address column is represented as a row.

The helper functions that go along with the above are:

import           ClassyPrelude
import           Data.Attoparsec.ByteString.Char8
import qualified Data.ByteString.Char8                as B
import           Database.PostgreSQL.Simple.FromField

textContent :: Parser Text
textContent = decodeUtf8 <$> (quoted <|> plain)

{- the following methods were lifted and adapted from
     - internals of postgresql-simple
     - https://github.com/tomjaguarpaw/haskell-opaleye/issues/98
-}

-- | Recognizes a quoted string.
quoted :: Parser ByteString
quoted = char '"' *> option "" contents <* char '"'
  where
    esc = char '\\' *> (char '\\' <|> char '"')
    unQ = takeWhile1 (notInClass "\"\\")
    contents = mconcat <$> many (unQ <|> B.singleton <$> esc)

-- | Recognizes a plain string literal, not containing comma, quotes, or parens.
plain :: Parser ByteString
plain = takeWhile1 (notInClass ",\"()")

fromPGRow :: Typeable a => String -> Parser a -> Field -> Maybe ByteString -> Conversion a
fromPGRow _ _ f Nothing = returnError UnexpectedNull f ""
fromPGRow fname parser f (Just bs) = do
  typename' <- typename f
  if typename' /= B.pack fname
    then returnError Incompatible f ("Wanted " <> fname <> ", got " <> show typename')
    else case parseOnly parser bs of
           Left err -> returnError ConversionFailed f err
           Right a  -> pure a

Writing Composite Types in Haskell

Suppose we want to go the other way and insert a row with an embedded address:

addPerson connection Person{..} =
  execute connection stmt ( personName
                          , personEmail
                          , personHomeAddress)
  where
    stmt = [sql| insert into people values (default, ?, ?, ?) |]

To be able to do this we have to create a ToField instance for Address:

instance ToField Address where
  toField Address{..} =
    Many [ Plain "row("
         , Escape (encodeUtf8 addressStreet)
         , Plain ","
         , maybe (Plain "null") (Escape . encodeUtf8) addressStreet2
         , Plain ","
         , Escape (encodeUtf8 addressCity)
         , Plain ","
         , Escape (encodeUtf8 addressState)
         , Plain ","
         , Escape (encodeUtf8 addressPostCode)
         , Plain ")"
         ]

We simply encode Address as a row and we’re good to go!

Source Material and Further Reading

PostgreSQL Composite Types

postgresql-simple Array Parser

Opaleye Composite Type Issue