WIP: polysemy-hasql - Polysemy

https://github.com/tek/polysemy-hasql

This one is still pretty raw and incomplete, some things that grew out of one of my projects.
would love to hear some comments about the schema derivation mechanism!

It looks very cool. Great to be able to switch between an in-memory Store and an actual database with no changes to application code. I hope to try it out some time.

do you have something like group bys, orders and limits? I'm currently thinking about how to fit them (or rather a more restricted version that would work for me) inside either an effect or an ADT so that I can interpret them to both SQL and some other thing (Data.Set)

I haven't gotten that far yet.

My concept so far is to create a new instance of StoreQuery q e d for each non-trivial-CRUD query.
So basically the types q and d should encode all of the parameters that fully describe the query, which in your case might include a limit field, like

data PostsForTopic =
  PostsForTopic {
    topic :: Topic,
    limit :: Int,
    order :: Order
}

Now the interpreters would figure out how to transform these things into backend structures, be it SQL or some Set combinators.

Of course, this is very high-level abstraction, leaving much room for incoherence under the hood.
The implementations for StoreQuery I have so far are only for multi-column selects that AND the operands, which relies on the data type QueryTable that derives a where clause generically. Those wouldn't work with PostsForTopic, since they would try to match limit and order with d as field names. In that case there needs to be some intermediate machinery that translates the type to more concrete representations of "fields" and "parameters".
So, it devolves into doing most of the work manually behind the interpreter, maybe not optimal.

Another approach would be to add a parameter to the StoreQuery constructor that encodes db params – either something static with predefined query features or another type param that facilitates more targeted derivation of SQL code.

What would you consider to be an ergonomic interface to this, @Georgi Lyubenov // googleson78 ?

I think I don't quite understand, due to not having any experience with hasql

in your Queries example, how are the values you pass to StoreQuery.basic used?

they are inserted into a where clause, something like where a = 1 and b = 2

I think this problem isn't really related to hasql

it's hard to tell with the moving parts (a lot of type params) :sweat_smile:

it's hard to tell with the moving parts (a lot of type params) :sweat_smile:

so basically you're selecting a subset of your values, determined by a RecordQuery

where do you do the "insert into a where"?

but what's done with the query type is up to the interpreter, the question I'm asking is what would be a good interface for the abstraction. once you get all the parameters into the native interpreter (be it hasql or anything else), you can do what you want – you could create a different kind of where clause than the and variant

well, the interpreter uses the generically derived information about the result type (determining the table name and which columns to select) and the query type (determining on which columns to match) to assemble an SQL statement

well this sounds like a decent interface - it sounds to me like you give the user full power to thread some information (of his own choice of datatype!) from business logic to his interpreter

I'm just not sure how the whole thing works and if this is correct

in the example, it would be select elems, number from record where elems = ["one", "two"] and number = 5.5

yeah so the question is, how would a good interface for passing in meta-parameters like limit and order look

especially if those values are dynamic, e.g. given by the web app user

otherwise you could use a query type named First100WhereNumberIs5 and handle the details in the implementation

Another approach would be to add a parameter to the StoreQuery constructor that encodes db params – either something static with predefined query features or another type param that facilitates more targeted derivation of SQL code.
if you're going this route it sounds to me like you're basically going to be implementing an AST for sql

I think letting the user decide what structure he needs, and giving them some predefined things like PostsForTopic but generalised to an a from your example above, is pretty good (for ease of use in common cases)

(maybe I'm just entirely misunderstanding right now though..)

what does that "generalised to an a" apply to?

data OrderAndLimitFor a =
  OrderAndLimitFor {
    topic :: a,
    limit :: Int,
    order :: Order
  }

this kind of thing?

ah

that would then be the hardcoded variant, but optional

I think the best course of action is to build a few test cases and let the code decide what's right :smile:

I wanted to start with joins, that's probably a bit harder and might produce the rest in its wake

what database library are you using, @Georgi Lyubenov // googleson78 ?

persistent + esqueleto

due to it being the only (or one of two) things that support mysql

they use combinators for where and limit et. al, right?

in esqueleto, yeah

I guess that is pretty incompatible with my concept

or do these combinators abstract over classes?

why? if you're just passing data you need to construct a query in your effect you can just construct it in your interpreter, as you said

these combinators produce a value that's basically a Writer that you can run to produce your final query

right, but wouldn't that reduce the comfort they provide dramatically?

I assume that the purpose there is to write sql queries in business logic for readability

I think the purpose is to write "SQL with some type safety" - I'm not sure whether they are "intended" to be used in business logic or not

in any case using them in business logic is nasty for tests afterwards

because you can't convert the Writer thing to pure operations

mh, right. could still be useful in interpreters

I'll give it a look to see how well I could abstract over different backends

currently I'm doing some form of the "pass data that's required to the interpreter", but my issue is that my effect constructor is a monolith - instead of being composed of "small parts" corresponding to some sql concepts I just have this big thing with lots of parameters, and what ends up happening is that a lot of implicit logic is hidden in my interpreter (which obviously is sucks for testing - if I ever write a pure interpreter I have to remember all this implicit logic I have "built into" the effect constructor)

yeah that's how I started out as well

send me the link if you ever publish your stuff (or have already published it)

it's an application specific effect for an @work project :/ so I can't share it and even if I could, it's not really anything general enough to make a lib out of

I'm thinking more that there should be some "approach" into modelling such sql applications, but if you actually manage to make the approach into a library it would be even cooler

well, maybe you could come up with some reasons why my Store and StoreQuery don't fit some of your use cases, would surely be helpful for me

(apart from the missing limit etc :smile: )

so I built a pretty fun thing: sum type columns! :tada:

given some types:

data SumRec =
  L { d :: Int }
  |
  R { e :: Text, f :: Double }
  deriving (Generic)

data Rec =
  Rec {
    a :: Text,
    b :: Int,
    c :: SumRec
  }
  deriving (Generic)

t :: TableStructure
t =
  tableStructure @Rec

it generates statements like:

create table "rec" ("a" text not null, "b" bigint not null, "c" sum_rec not null)
select "a", "b", ("c").sum_index, ("c")."l"."d", ("c")."r"."e", ("c")."r"."f" from "rec"
insert into "rec" ("a", "b", "c") values ($1, $2, row($4, row($5), row($6, $7)))

plus the corresponding Row/Param values.

now even without SOP.Generic instance!