How to Serialize a Type to specified database columns

Question

I am using postgresql-simple in a haskell application, and i want to be able to serialize a data type to a row in my database that doesn't have a 1 to 1 mapping of the record fields used in the data type because i am using them in other data types. (I'm Fairly New to Haskell but I think this involves using the Identifier data constructor).

Example I have a database table Users with the following columns: user_id, email, name, password, address, phone_number

now I have a type with the following format:

data UserDetails = UserDetails {
  user_id :: Int,
  email :: Text,
  phone_number :: Maybe (Text),
  password :: Text,
  name :: Maybe (Text),
  address :: Maybe (Text),
} deriving (Show, Generic, FromRow)

And i can have a generic ToRow implemented for this type no problem since the record fields are the same as the column names, but I have another type that i want to generate a ToRow instance for which is:

data UserEditDetails = UserEditDetails {
  ued_email :: Maybe (Email),
  ued_phone_number :: Maybe (Text),
  ued_address :: Maybe (Text),
  ued_name :: Maybe (Text),
} deriving (Show, Generic)

how would i implement a ToRow instance of this type or more generally how can i write a ToRow instance like the following pseudo code

instance ToRow UserEditDetails where
  toRow a = (columnname, ued_email a)... etc

hopefully it has a function similar to Aeson where you can easily write something like:

instance ToJSON Login where
  toJSON = genericToJSON defaultOptions { fieldLabelModifier = drop 4 }

but i havent found this.

Sorry the ToRow is a typeclass for postgresql-simple i will edit the question to show that — emg184, Apr 16 '19 at 11:13

score 1 · Answer 1 · answered Apr 17 '19 at 05:50

postgresql-simple doesn't pay any attention to database column names. It just expects the count and order of columns serialized or deserialized by the ToRow/FromRow instances will match the count and order of columns in the SQL query. You can use the generic instance for ToRow UserEditDetails as long as the query you're feeding it to matches the four columns in the right order. For example, if you have the following definition and generic ToRow instance:

data UserEditDetails = UserEditDetails
  { ued_email :: Maybe Text
  , ued_phone_number :: Maybe Text
  , ued_address :: Maybe Text
  , ued_name :: Maybe Text
  } deriving (Show, Generic, ToRow)

then I believe the following query will work fine:

do let q = "update user_table set email=?, phone_number=?, address=?, name=? "
           ++ "where user_id=?"
   execute conn q $ (UserEditDetails
                      (Just "me@example.invalid")
                      (Just "555-1212")
                      (Just "123 Fake Street")
                      (Just "Bob Jones"))
                    :. Only (15 :: Int)  -- user_id 15

Note that the generic instances from FromRow and ToRow are equivalent to:

instance ToRow UserEditDetails where
  toRow (UserEditDetails a b c d) = [toField a, toField b, toField c, toField d]
instance FromRow UserEditDetails where
  fromRow = UserEditDetails <$> field <*> field <*> field <*> field

The fields are anonymous and there's no place to give specific database columns anyway.

But when i save the type UserDetails type into my database i save it into a table with more columns than type fields are present and each record field maps accordingly to the column of the same name in the database. — emg184, Apr 19 '19 at 01:57
How do you save your `UserDetails` type into your database? Is it not with an `insert` query? — K. A. Buhr, Apr 19 '19 at 02:53

How to Serialize a Type to specified database columns

1 Answers1