Add Saurabh's tutorial to the README

[tomjaguarpaw]

Requested by @williamyaoh. I'll have to refamiliarise myself with it.

https://haskell-webapps.readthedocs.io/en/latest/docs/opaleye/opaleye.html

[saurabhnanda]

Hey, should i spruce this up for the latest version? I have a bunch of other chapters that I didn't manage to publish. But this might be the required push to get this out there.

…

On Thu, 19 Dec 2019, 20:13 tomjaguarpaw, ***@***.***> wrote: Requested by @williamyaoh <https://github.com/williamyaoh>. I'll have to refamiliarise myself with it. https://haskell-webapps.readthedocs.io/en/latest/docs/opaleye/opaleye.html — You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHub <#447?email_source=notifications&email_token=AAAG5UK4FNQB2LCQP75U44LQZOCBPA5CNFSM4J5H6D52YY3PNVWWK3TUL52HS4DFUVEXG43VMWVGG33NNVSW45C7NFSM4IBVFXRA>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AAAG5UPZNRWXJTJXO5B5EJTQZOCBPANCNFSM4J5H6D5Q> .

[tomjaguarpaw]

should i spruce this up for the latest version?

Updating to use the new names would be great.

[tomjaguarpaw]

@saurabhnanda Is the source code for your tutorial available in a repo? If so I will try to bring it up to date with current Opaleye.

[saurabhnanda]

Please give me a few more days. I'm on it -- just got side-tracked with my static-site builder.

…

On Sun, Aug 23, 2020 at 2:12 PM tomjaguarpaw ***@***.***> wrote: @saurabhnanda <https://github.com/saurabhnanda> Is the source code for your tutorial available in a repo? If so I will try to bring it up to date with current Opaleye. — You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub <#447 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AAAG5UK2GCZVJS6K4IOEHYDSCDI5ZANCNFSM4J5H6D5Q> .

[saurabhnanda]

@tomjaguarpaw I finally got around to finishing my static site builder and am now revamping the tutorial. Even when I was writing the first one, I was unable to explain why the optional/required annotations are required when setting up a Table:

userTable :: Table (Field SqlInt4, Field SqlText, Field SqlText)
                   (Field SqlInt4, Field SqlText, Field SqlText)
userTable = Table "users" (p3 ( required "id"
                              , required "name"
                              , required "email"))

Aren't the type annotations enough? Is it even possible to have a required field that corresponds to a Maybe (Field a) in Haskell? Or to have an optional field that does not correspond to a Maybe (Field a)?

While we are cleaning-up (and introducing breaking changes), would it be possible to re-consider this design decision?

[saurabhnanda]

I'm getting used to the new API myself, I think my previous comment should read as Field a vs FieldNullable a, right?

[saurabhnanda]

the API has changed significantly since 0.5-0.6 that we are using in production. Is runInsert_ really the user-facing API now? It requires a user to understand many data-types to construct an INSERT statement.

Also, iOnConflict should be OnConflict instead of Maybe OnConflict and OnConflict should be changed to:

data OnConflict = OnConflictOmit | OnConflictDoNothing

[tomjaguarpaw]

I was unable to explain why the optional/required annotations are required when setting up a Table

They're not required. If you use tableField then it will infer optional vs required based on the type annotations.

[tomjaguarpaw]

Is runInsert_ really the user-facing API now?

Yes it is. We have some freedom to come up with a better API because the the old version of runInsert has been has been removed, and runUpdate and runInsert are deprecated and will be removed in the next version. We can use those names for a better API if we can think of one.

[tomjaguarpaw]

I'm getting used to the new API myself, I think my previous comment should read as Field a vs FieldNullable a, right?

The API hasn't fully switched to the Field_/Field/FieldNullable nomenclature yet, but when it does the types will be

requiredTableField :: String -> TableFields (Field_ n a) (Field_ n a)

optionalTableField :: String -> TableFields (Maybe (Field_ n a)) (Field_ n a)

That is, the Field_ type has nullability as one of its type parameters (and the SQL type as the other). Field will be a synonym Field NonNullable and FieldNullable will be a synonym for Field Nullable.

[saurabhnanda]

Is there any reason why Returning a b doesn't have a ReturnNothing constructor? Does Postgres insist on returning something in every INSERT statement?

[saurabhnanda]

I had this note in my old tutorial. Is there a technical explanation for why the API behaves this way (runSelect doesn't force you to call fromFields on its output, but runInsert_ does)?

.. note:: Strangely, while runSelect converts DB => Haskell types automagically, runInsertMany and runUpdate refuse to do Haskell => DB conversions on their own. Hence the need to do it explicitly when using these functions.

Also, in a tutorial, how does one explain why some API functions have a trailing underscore, while others don't?

[saurabhnanda]

I'm trying to rediscover Opaleye as a beginner, to be able to write an effective tutorial.

When I look at runUpdate_ the following note is very puzzling:

Be careful: providing Nothing to a field created by optional updates the field to its default value. Many users have been confused by this because they assume it means that the field is to be left unchanged. For an easier time wrap your update function in updateEasy.

Is there a valid (and extremely common) use-case for updating a row with colName = DEFAULT? If not, can we make following the default behaviour, and also emit smaller SQL statements as a side-effect?

runUpdate_ conn Update
  { uTable = sometable
  , uUpdateWith = (\r -> r & _3 .~ (toFields "someText"))
  , uWhere = whatever
  , uReturning = rCount
  }

Generated SQL:

UPDATE sometable
SET col3 = "someText" -- all other columns are omitted
WHERE whatever
RETURNING count(*)

[tomjaguarpaw]

Is there any reason why Returning a b doesn't have a ReturnNothing constructor? Does Postgres insist on returning something in every INSERT statement?

Yeah, we could add the ability to return nothing (or more precisely, ()).

[tomjaguarpaw]

how does one explain why some API functions have a trailing underscore, while others don't?

runInsert_/runUpdate_/runDelete_ have a training underscore because there were already (deprecated) functions called runInsert/runUpdate/runDelete. When the latter are removed the new functions can take their place.

[tomjaguarpaw]

I had this note in my old tutorial. Is there a technical explanation for why the API behaves this way (runSelect doesn't force you to call fromFields on its output, but runInsert_ does)?

I'm not sure what you mean. How does runInsert_ force you to call fromFields on its output? Do you mean toFields on its input?

[saurabhnanda]

I'm not sure what you mean. How does runInsert_ force you to call fromFields on its output? Do you mean toFields on its input?

Let me rephrase. runSelect doesn't force you to do convert from DB => Haskell manually, whereas runInsert_ forces you to convert from Haskell => DB manually.

[tomjaguarpaw]

Is there a valid (and extremely common) use-case for updating a row with colName = DEFAULT?

No, I don't think so.

If not, can we make following the default behaviour, and also emit smaller SQL statements as a side-effect?

The current API is a consequence of the structure of the Table type. At the moment a table definition has type Table fieldsWrite fieldsRead. fieldsWrite is used for both UPDATEs and INSERTs. INSERTs must provide all columns and must have the ability to provide DEFAULT, therefore UPDATEs have this property too.

The API you propose is achievable with updateEasy isn't it? I think you can write

runUpdate_ conn Update
  { uTable = sometable
  , uUpdateWith = updateEasy (\r -> r & _3 .~ (toFields "someText"))
  , uWhere = whatever
  , uReturning = rCount
  }

This still generates code for all fields. It will be something like

UPDATE sometable
SET col1 = col1, col2 = col2, col3 = "someText", col4 = col4
WHERE whatever
RETURNING count(*)

but I suppose I can detect unchanged columns and leave them out.

[saurabhnanda]

but I suppose I can detect unchanged columns and leave them out.

Hang on, I think I just got confused. I wrote the following example in the tutorial and I think it can be written with \r -> r & _3 .~ toField x technique without involving updateEasy.

updateUser :: Connection             
           -> (Int, String, String, Maybe Day) 
           -> IO ()
updateUser conn (i, n, e, d) = 
  void $ runUpdate_ conn u
  where
    u = Update 
      { uTable = userTable  
      , uUpdateWith = (\(iDb, _, _, _) -> (iDb, toFields n, toFields e, toFields d))
      , uWhere = (\(iDb, _, _, _) -> iDb .== toFields i)
      , uReturning = rCount
      }

Is updateEasy required only when tableW and tableR are different? And does the Default Updater machinery take care of readOnly fields?

[tomjaguarpaw]

Let me rephrase. runSelect doesn't force you to do convert from DB => Haskell manually, whereas runInsert_ forces you to convert from Haskell => DB manually.

Right, so the way I see this is that runSelect doesn't allow you to do DB => Haskell manually; Fields produced by a Select can't live outside the Select. On the other hand runInsert_ does allow you to create the fields yourself if you want to.

I think of this as a "lower level" API that other APIs can be written in terms of. If you think there's a nicer higher-level API then let's see what that could be.

[tomjaguarpaw]

And does the Default Updater machinery take care of readOnly fields?

Unfortunately readOnly fields are completely broken for UPDATEs, and I only realised this recently. The behaviour of a readOnly field is to always use DEFAULT whenever you write to it. This behaviour is correct for inserts but not what you want for UPDATEs. I'm not sure what to do about this. Maybe redesigning the Table type is the right thing to do.

[tomjaguarpaw]

Is updateEasy required only when tableW and tableR are different?

Correct.

[tomjaguarpaw]

Is updateEasy required only when tableW and tableR are different?

Correct.

And more precisely, this occurs exactly when some fields are optionalTableFields.

[saurabhnanda]

And more precisely, this occurs exactly when some fields are optionalTableFields.

And readOnlyFields?

[tomjaguarpaw]

Yes, true, but there isn't an appropriate instance to make Updater work with readOnlyTableFields and there probably should never be because as mentioned above readOnlyTableField doesn't make sense for updating. I should probably deprecate readOnlyTableField or at least put on a big warning.

[saurabhnanda]

Btw - I've updated the first chapter of the tutorial, and it's available at https://www.haskelltutorials.com/opaleye/instant-gratification.html -- still working on the rest. Targeting to update and publish one chapter every 2 days, or so.

[saurabhnanda]

Yes, true, but there isn't an appropriate instance to make Updater work with readOnlyTableFields and there probably should never be because as mentioned above readOnlyTableField doesn't make sense for updating. I should probably deprecate readOnlyTableField or at least put on a big warning.

We use our own hacked-up version of read-only fields a lot in production. We use it for id, created_at and updated_at columns all throughout our app.

[tomjaguarpaw]

We use our own hacked-up version of read-only fields a lot in production. We use it for id, created_at and updated_at columns all throughout our app.

Very cool idea. Would be good to see how you did this.

[saurabhnanda]

@tomjaguarpaw is this section approximately correct? https://www.haskelltutorials.com/opaleye/instant-gratification.html#tofields_note

[tomjaguarpaw]

Yes, looks approximately correct. It would be more correct to say that it is to do with the Default instance (which is implemented in terms of p1 ... p62) but what you have is probably close enough.

Also, there's a typo in "stange".

[saurabhnanda]

@tomjaguarpaw I've completed one more chapter in the tutorial (and also ended up creating a bunch of other TODOs in the process!). Any feedback on this - https://www.haskelltutorials.com/opaleye/implementation-guide.html ?

[ocharles]

Another minor disadvantage could be the dependency on using the `Arrows` language extension. Some people still consider it to be highly experimental.

This is no longer true, and Opaleye can now be used through just it's Functor/Applicative/Monad interface, with no loss of functionality

…

On Sun, 13 Sep 2020, at 10:45 AM, Saurabh Nanda wrote: @tomjaguarpaw <https://github.com/tomjaguarpaw> I've completed one more chapter in the tutorial (and also ended up creating a bunch of other TODOs in the process!). Any feedback on this - https://www.haskelltutorials.com/opaleye/implementation-guide.html ? — You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHub <#447 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AAAFDDRMNMINMF7BXJDLBOLSFSIDVANCNFSM4J5H6D5Q>.

[saurabhnanda]

This is no longer true, and Opaleye can now be used through just it's Functor/Applicative/Monad interface, with no loss of functionality

How did I miss this! When did this happen?

[ocharles]

276ff5b :)

…

On Sun, 13 Sep 2020, at 12:21 PM, Saurabh Nanda wrote: > This is no longer true, and Opaleye can now be used through just it's Functor/Applicative/Monad interface, with no loss of functionality How did I miss this! When did this happen? — You are receiving this because you commented. Reply to this email directly, view it on GitHub <#447 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AAAFDDXQ5T3GYPA7ZRKTM6DSFSTLPANCNFSM4J5H6D5Q>.

[saurabhnanda]

Let me update my code samples in that case. Is any interface preferred? Any known drawbacks of the new Monad interface?

@ocharles any other feedback on the implementation guide? Does this match your experience of using opaleye in production?

[ocharles]

On Sun, 13 Sep 2020, at 2:52 PM, Saurabh Nanda wrote: Let me update my code samples in that case. Is any interface preferred? Any known drawbacks of the new Monad interface?

We only recently converted all our code to it, but haven't encountered a problem yet!

@ocharles <https://github.com/ocharles> any other feedback on the implementation guide? Does this match your experience of using opaleye in production?

I can't really comment on that, we use rel8 which only uses Opaleye for implementation - none of Opaleye is visible in the client API.

[tomjaguarpaw]

Let me update my code samples in that case. Is any interface preferred? Any known drawbacks of the new Monad interface?

The monadic API adds LATERAL to every join. It's not certain what the performance impact but @ocharles and @duairc have run their benchmarks on it and not found any degradation.

[ocharles]

I'd say it's more than just benchmarks - we run with a fork that literally adds LATERAL everywhere it's permitted in production and I don't think we've noticed any performance degradation. OTOH, we've exploited this for performance improvements, especially when joining an aggregation against every row (as we can directly feed a row into an aggregation, rather than having to produce a common column to join on)

[tomjaguarpaw]

@saurabhnanda Very nice indeed! Just a few comments below.

By the way, I implemented a sample database for Munihac. I'd like to tidy it up and publish it more officially as a suite of Opaleye examples.

Using ad-hoc joins will force you to make one of one of the following choices. If you want to understand them in detail please read Opaleye Query Tutorial

MaybeFields, optional and optionalRestrict are new alternatives that provide a LEFT JOIN API. They are much more convenient to use than the old LEFT JOIN API.

Another minor disadvantage could be the dependency on using the Arrows language extension. Some people still consider it to be highly experimental.

As Ollie pointed out, there is a Monad interface now.

It is strongyly recommend

Small typo here: should be "strongly recommended"

[saurabhnanda]

@tomjaguarpaw I'm working on updating the chapters related to setting up Haskell <=> DB bridge using DefaultFromFields and am unable to explain (even to myself) why Unpackspec in QueryRunnerColumn (Unpackspec (Column pgType) ()) (FieldParser haskellType) exists. What does it hold over and above FieldParser?

[tomjaguarpaw]

If you have

myTable :: Table (Field SqlInt4, Field SqlInt4) (Field SqlInt4, Field SqlInt4)

q1 = selectTable myTable
q2 = fmap fst (selectTable myTable)

Then q1 generates (roughly)

SELECT column1, column2 FROM mytable

and q2 generates (roughly)

SELECT column1 FROM mytable

It's the Unpackspec in the FromFields that is responsible for generating column1, column2 in the first case and column1 in the second case.

[tomjaguarpaw]

But the fact that a FromFields contains an Unpackspec is completely an implementation detail. Can you avoid mentioning it at all?

[saurabhnanda]

It's the Unpackspec in the FromFields that is responsible for generating column1, column2 in the first case and column1 in the second case.

Based on this explanation, shouldn't Unpackspec be part of Query instead of FromField?

I was under the impression that via Unpackspec one has the ability to map multiple DB fields to a single Haskell value. Then that's not the case, right?

And would it be right to say, that via FromField one sets up the field/col level bridge and via Table (including profunctor magic) one sets up the row-level bridge?

[saurabhnanda]

@tomjaguarpaw the machinery to use for Haskell => DB conversion would be https://hackage.haskell.org/package/opaleye-0.7.1.0/docs/Opaleye-ToFields.html#t:ToFields , right?

Why is it plural? Is there any use-case where one would write ToFields haskells fields for multiple fields in one-shot? Isn't calling haskell -> sql on different part of a Haskell data-structure the job of the profunctor?

[saurabhnanda]

@tomjaguarpaw and as I'm looking at the user-facing API, I'm wondering whether they asymmetry is really warranted, i.e. DefaultFromField vs Default ToFields? Is it not possible to have one of the following?

• ToFields and FromFields
• ToField and FromField
• Default ToField and Default FromField
• Default ToFields and Default FromFields

[saurabhnanda]

@tomjaguarpaw are you happy with the following as a final user-facing API?

instance DefaultFromField SqlText OrderStatus where
  defaultFromField = fromPGSFieldParser orderStatusFieldParser

instance Default ToFields OrderStatus (Column SqlText) where
  def = toToFields (toFields . orderStatusToText)

[tomjaguarpaw]

the machinery to use for Haskell => DB conversion would be https://hackage.haskell.org/package/opaleye-0.7.1.0/docs/Opaleye-ToFields.html#t:ToFields , right?

Right

Why is it plural? Is there any use-case where one would write ToFields haskells fields for multiple fields in one-shot? Isn't calling haskell -> sql on different part of a Haskell data-structure the job of the profunctor?

You might not write the instance for something containing multiple fields (although you could). But even so you often call it like toFields ("Hello", 1234 :: Int) :: (Field SqlText, Field SqlInt4) so indeed it creates multiple fields at once.

[tomjaguarpaw]

are you happy with the following as a final user-facing API?

instance DefaultFromField SqlText OrderStatus where
  defaultFromField = fromPGSFieldParser orderStatusFieldParser

That looks fine

instance Default ToFields OrderStatus (Column SqlText) where
  def = toToFields (toFields . orderStatusToText)

I think lmap orderStatusToText toFields is better because that pattern generalises to all other Profunctors.

On the other hand I added an experimental function to allow easy mapping between Haskell and DB types called fromFieldToFieldsEnum. Although it has "enum" in its name it works for all DB types, not just enums. It might be best to fully support this and make it the official way of mapping Haskell <-> DB types. What do you think?

I'm wondering whether they asymmetry is really warranted, i.e. DefaultFromField vs Default ToFields?

This is a bit of a wart that was needed to support nullable and non-nullable fields uniformly. I hope it won't be needed in the new Field world.

It's the Unpackspec in the FromFields that is responsible for generating column1, column2 in the first case and column1 in the second case.

Based on this explanation, shouldn't Unpackspec be part of Query instead of FromField?

This is getting into quite technical areas. I don't understand why this question needs answering. The fact that a FromFields contains an Unpackspec is an implementation detail. Isn't it sufficient to say that a FromFields deals with running a Select and turning its Fields into Haskell values?

I was under the impression that via Unpackspec one has the ability to map multiple DB fields to a single Haskell value. Then that's not the case, right?

The Unpackspec doesn't have anything to do with Haskell values. It's a way of observing what Fields are being selected in a query.

And would it be right to say, that via FromField one sets up the field/col level bridge and via Table (including profunctor magic) one sets up the row-level bridge?

FromField certainly describes how to turn Fields into Haskell values and Table describes the full row that a table contains.