Request for docs: Possible to have different read and write types?

[saurabhnanda]

Is it possible to have different types for reading & writing? If yes, how does that impact UPDATE statements?

Use-case for different read and write types:

A model has an auto-increment primary key, a created_at and an updated_at, where all three should be coming from the DB, and the Haskell layer should not even be allowed to specify these three columns (for greater type-safety). However, they are required to be read back from PG to Haskell world.

How this can be problem for update transactions

[mageshb]

Main idea about this lib is to have same types but different type indexes for most of db operations. For your scenario, you can configure column key, created_at and updated_at to have default value in the Table instance and the impact of that will be, those columns will become optional during writes (insert & updates). It is possible to void out those column during writes, which totally prevent user from setting the values for those columns, but also would mean that user will not be able to override the auto-gen value. Hence I have chosen to make it optional, rather than voiding it out.

Following code (not type checked btw) provides example for your scenario and I hope this helps

newtype TenantId = TenantId Int
newtype OwnerId = OwnerId
-- Assuming status is a enum in your case
data Status = S1 | S2 ...
data Tenant f = Tenant {
    key                :: Col f "key" TenantId
  , created_at         :: Col f "created_at" UTCTime
  , updated_at         :: Col f "updated_at" UTCTime
  , name               :: Col f "name" Text
  , status             :: Col f "status" Status
  , owner_id           :: Col f "owner_id" OwnerId
  , back_office_domain :: Col f "back_office_domain" Text
}

data Owner f = Owner {
    key :: Col f "key" OwnerId
  , created_at         :: Col f "created_at" UTCTime
  , updated_at         :: Col f "updated_at" UTCTime
}

data MyAppDB

instance Database MyAppDB where
  type Tables MyAppDB = '[Tenant Hask, Owner Hask]
  type Types MyAppDB = '[Status]

instance Table (Tenant Hask) where
  type HasDefault (Tenant Hask) = '["key", "created_at", "updated_at"]

instance Table (Owner Hask) where
  type HasDefault (Owner Hask) = '["key", "created_at", "updated_at"]

insertTenant :: ReaderT (Config a) PG ()
insertTenant = insert (Tab @MyAppDB @Tenant) (
  Tenant
  { key = Nothing -- Key has become (Maybe TenantId) since it is configured to have default value in Table instance
  , created_at = Nothing -- Optional during write due to same reason as above
  , update_at = Nothing  -- Optional during write
  , name = "foo"
  , status = S1
  , owner_id = (OwnerId 1)
  , back_office_domain = "dsdsd"
  })

-- HasDefault setting has effect on update too, as update is about converting a value from tab Op -> Write Op db tab

updateTenant = update (Tab @MyAppDB @Tenant) (
  \ten ->
  (constant ten :: Write Op MyAppDB Tenant) {
    name = "bar"
    }) (\ten -> key ten .== (constant $ TenantId 10))

-- constant can be used to do the required type conversion before performing the record update

[saurabhnanda]

Main idea about this lib is to have same types but different type indexes for most of db operations.

Can you elaborate what you mean by "same types but different type indexes"? Do you basically mean "same shape" by polymorphic in f over Col f colname hasktype?

In the following code, isn't is ambiguous to use Nothing to indicate a default value? What happens if I actually want to send a NULL down to SQL for a particular column?

insertTenant :: ReaderT (Config a) PG ()
insertTenant = insert (Tab @MyAppDB @Tenant) (
  Tenant
  { key = Nothing -- Key has become (Maybe TenantId) since it is configured to have default value in Table instance
  , created_at = Nothing -- Optional during write due to same reason as above
  , update_at = Nothing  -- Optional during write
  , name = "foo"
  , status = S1
  , owner_id = (OwnerId 1)
  , back_office_domain = "dsdsd"
  })

Also, is there an easier way to do updates? Doesn't the following piece of code have a lot of boilerplate? If I understand this correctly, you're basically taking a "default" tenant record constructed specially for writing to the DB (is it constant ten :: Write Op MyAppDB Tenant?) and changing a field within it? Also, what happens if the changed field is a custom type, like an Enum? Does it get converted to the PG value automatically or does one have to use ToField or constant on it?

updateTenant = update (Tab @MyAppDB @Tenant) (
  \ten ->
  (constant ten :: Write Op MyAppDB Tenant) {
    name = "bar"
    }) (\ten -> key ten .== (constant $ TenantId 10))

[mageshb]

Yes. By changing f, I'm able to alter the field's type to the required one. Specific eg would be. When f is Hask, Col f "foo" Int become Int f is Op, Col f "foo" Int become PGInt4

I didn't get how it will become ambiguous. You can pass in NULL only if you configure your column to be optional, Say for eg. you have following column with Defaults, foo :: Col f "foo" (Maybe Int) Even in this case, during Write column foo's type become Maybe (Maybe Int), allowing you to choose DEFAULT value by passing Nothing, or null by passing Just Nothing, or a actual value Just (Just 1)

As of now, this lib support Enums and NewTypes providing automatic marshaling for those (no need for ToField, FromField ..) For Enum, you are expected to create corresponding enum type in the Postgres. Eg. data UserRole = Admin | Guest | Nor'mal requires CREATE TYPE "USERROLE" AS ENUM ('Admin', 'Guest', 'Nor''mal'); For NewType, no extra work is required. Lib will simply throw the newtype wrapper and use the internal type automatically.

As for as update is concerned, the first callback accepts db row (ten :: Tenant Op) that matched the filter predicate and should return Writable row of same type (here Write Op MyAppDB Tenant).

Even more verbose way of doing this is manually copying those info from arg, like, \ten -> Tenant { key = Just $ key ten , created_at = Just $ created_at ten , update_at = Just $ updated_at ten , name = "bar" , status = status ten , owner_id = owner_id ten , back_office_domain = back_office_domain ten }

Luckily with constant we are able to avoid these manual copy by first converting it to Write Op MyAppDB Tenant and then use normal record update syntax

I think loosing type inference due to constant is one of the reason for verbosity. To fix, we can define one function called

toWriteable :: tab Op -> Write Op db tab
toWriteable = constant

with this update would look like

updateTenant = update (Tab @MyAppDB @Tenant) (
  \ten -> toWriteable ten {status = constant S2} -- Enum value can be lifted to DB Value using constant
) (\ten -> key ten .== (constant $ TenantId 10))

[saurabhnanda]

I didn't get how it will become ambiguous. You can pass in NULL only if you configure your column to be optional,

So, in the context of an INSERT statement aren't we dealing with three states:

• Explicit NULL value
• Explicit non-NULL value
• Absent from the INSERT statement, which allows DB to use default value, if present

Is it possible to model this with a Maybe, which represents only two states correctly Similarly, in the context of an UPDATE, are't we again dealing with the same three states?

Am I missing something subtle here?

[saurabhnanda]

As for as update is concerned, the first callback accepts db row (ten :: Tenant Op) that matched the filter predicate and should return Writable row of same type (here Write Op MyAppDB Tenant).

Okay, so I guessed correctly. The callback needs to return an Op type, and not a Hask type. Is the row passed to the callback a Hask or an Op? Is the constant trick something part of this library, or part of base Opaleye? Also, does this mean that the callback's return value will need to call ToSQL or constant on every field (say I'm updating an ENUM)?

[mageshb]

constant fn is from Opaleye lib which is used to convert value from Hask to Op. But I have added couple of instance as part of this lib, which made constant to work for tab Op -> Write Op db tab conversion. Row passed to callback is Op value, and yes when u have update,you need lift the value from Hask to Op using fn like constant. That's why in the example i have used constant S2 to update which lift the Haskell's Enum value to PGEnum

As for as ambiguity is concerned, I will try to explain with different eg. Eg. say for model

  data User f = User
    { userid   :: Col f "userid" Int
    , gender :: Col f "gender" (Maybe Text)
    , email :: Col f "email" Text
    , age :: Col f "age" (Maybe Int)
    }

In the above code,lets say only col userid and gender has Defaults. Now Insert for User requires User record to be following shape to capture all the states

 User { userid :: Maybe Int          --- Captures 2 states (Explict NonNull, DB DEFAULT)
          , gender :: Maybe (Maybe Text) -- Captures 3 states (Explicit Null, Explict NonNull, DB DEFAULT)
          , email :: Text
          , age :: (Maybe Int)
          }

insert fn precisely does this by fixing appropriate f to User.

Say for eg. you have following column with Defaults, foo :: Col f "foo" (Maybe Int) Even in this case, during Write column foo's type become Maybe (Maybe Int), allowing you to choose > DEFAULT value by passing Nothing, or null by passing Just Nothing, or a actual value Just (Just 1)

Above eg. explains how (Maybe (Maybe T)) let's you to capture 3 state that you were asking for

[saurabhnanda]

Ah, okay. Missed the nested Maybe

[saurabhnanda]

So, is stuff like Col f "userid" Int something you implemented or is it coming from Opaleye? Specifically from the incomprehensible tutorial given at https://github.com/tomjaguarpaw/haskell-opaleye/blob/master/Doc/Tutorial/TutorialBasicTypeFamilies.lhs

[saurabhnanda]

Also, how would you make sure that while writing (or updating) it's not possible to change the id, created_at, and updated_at fields, but they are accessible once the data is read back? Is it possible to constrain these fields to () during write operations alone, easily?

[mageshb]

stuff like Col f "userid" Int is something I implemented on top of opaleye to share same models (in turn reducing verbosity) and to improve type inference. If you notice in the Readme, all the sample code work without any explicit type annotation even for type changing operation like projection, left join, aggregation & so on. It is very much possible to provide an another variant of insert and update which will void out those column by turning its type into VoidF T rather that Maybe T and there by preventing you to set it. But that also would mean that you will lose out the ability to override those DEFAULT value as well. Reading back is unaffected, you will be be able to access the original value as it is and that is the case even now.