PureScript RealWorld Backend

Feedback
#1

Hey everybody!
I didn’t know which backend technology to use. I tried purescript-express and I couldn’t get the unit tests to work. I tried purescript-payload and implemented the RealWorld API Spec. I had to patch the payload repo. Apart from that, it works pretty good. It uses PostgreSQL in order to store the data.

The implementation can be found at https://github.com/jim108dev/purescript-payload-realworld-example.

Maybe you would have done it differently. If you like to give some feedback, Approach.md contains some comments about the decisions which I have made.

Thanks.

6 Likes
#2

Nice work!

I’m curious about the options for using PostgreSQL in PureScript. I see you went with node-postgres. What were your deciding factors for using that versus these other options?

Also, are there any ways to get more compile-time guarantees that the magic postgres query strings are formatted correctly? (example of file containing these strings). Have we considered writing a DSL to generate these query strings, or is that not really worth the effort?

Edit: Looks like selda is the most promising DSL solution. To summarize, these are all the DB libs proposed so far:

3 Likes
#3

Thank you for the reply!

  • postgresql-client forces me to specify the order in which the fields are coming back from the database. but I already stated by choosing the type record, that the order is not important to me.
  • I did not know about nonbili-postgres. One problem was, that node-postgres somehow loses the full error information at queries and I had to patch it. Not sure if the same is going on with nonbili-postgres.
  • There are no guarantees. I am surprised you have picked Persistence Profile. Persistence Article is way worse.
Query
          ( (selectArticle "$1")
              <> """
        LEFT JOIN "user" AS fa_user ON (fa.user_id = fa_user.id)
        ...

There is definitely room for improvement. I thought PostgreSQL queries can get pretty complicated and I wanted to have the ability to copy the query, make a few changes and run it in the psql console and vice versa. And where to start and where to stop? I know I don’t want to stop at purescript-selda . Do you know of an example with SQL DSL to quickly compare?

2 Likes
#4

Good note about selda. I forgot to mention that one - haven’t done any backend work in PS yet myself, so all the DB libs aren’t fresh in my mind. Tags in Pursuit (issue) would have helped with discoverability, since I just did a quick search for stuff with “postgres” in the name. I’ll add selda to the list in my previous message.

I understand the issue with not being able to simply copy a query string from a DSL for easy debugging, but I’m not sure what you mean by “stop” here. Are you saying that you’d want more abstraction abilities from selda to make the DSL worth the debugability penalty?

1 Like
#5

I want to explain that this library is somewhat low level. It is true that javascript library uses some dirty tricks underneath to turn rows coming from the database into “semi-typed” JS objects but we don’t want to use them and we want to bypass / skip this unnecessary mechanism / overhead entirely in the future. DBs work on tuples. I don’t want to claim that we want to provide backend agnostic layer for the postgres in the future… but who knows :wink:

On the other hand purescript-selda was strictly designed to provide a nice, monadic API based on PS records which is much nicer when compared to Haskell selda API in my opinion. We use selda in production and I think that it is able to generate really complicated queries. It provides a really nice docs and guide but of course we are really happy to help / respond if you encounter any problems or limitations when using the lib.

2 Likes
#6

@paluh: I must have missed a lot of problems/discussions about this. Can you just provide one example where the dirty tricks don’t work?
@milesfrain: I mean I don’t want to end up at purescript-selda.

My reasoning:

  1. purescript-selda and postgresql-client are not part of the package set.
  2. I think SQL is great. The language is great. The documentation is great. If you have run it once, you can almost be sure it won’t break.

I might be wrong, let’s pick one of my longer sql statements:

 SELECT
  u.bio,
  fo.followee_id IS NOT NULL AS following,
  u.image,
  u.username,
  a.body,
  timestamp_to_char (a.created_at) AS created_at,
  a.description,
  a.id,
  fa.article_id IS NOT NULL AS favorited,
  CAST((SELECT COUNT(*) FROM favorited WHERE favorited.article_id = a.id) AS INTEGER) AS favorites_count,
  a.slug,
  a.tag_list::TEXT[],
  a.title,
  timestamp_to_char (a.updated_at) AS updated_at
FROM
  article AS a
  INNER JOIN "user" AS u ON (a.author_id = u.id)
  LEFT JOIN following AS fo ON (u.id = fo.followee_id) AND (fo.follower_id = $1)
  LEFT JOIN favorited AS fa ON (a.id = fa.article_id) 
  LEFT JOIN "user" AS fa_user ON (fa.user_id = fa_user.id)
WHERE (($2::text IS NULL) OR (u.username = $2))
      AND (($3::text IS NULL) OR (fa_user.username = $3))
      AND (($6::text IS NULL) OR ($6=ANY(a.tag_list))) 
      ORDER BY a.updated_at DESC LIMIT $4 OFFSET $5

with tag_list as an Array and timestamp_to_char a custom function.

and also INSERT-SELECT:

WITH inserted AS (
INSERT INTO FOLLOWING (follower_id, followee_id)
  SELECT
    $1,
    followee.id
  FROM
    "user" AS followee
  WHERE
    followee.username = $2
  RETURNING
    *
)
SELECT
  followee.*,
  TRUE AS FOLLOWING
FROM
  "user" AS followee,
  inserted
WHERE
  id = inserted.followee_id

@paluh: Can this be done in purescript-selda (without too much overhead)?

1 Like
#7

I’m not sure what is the value of “guessing” names from the queries or the schema when we can provide a layer of proper SQL codegen which knows how it has named generated and intermediate fields in the query and map them back to nice and typed record fields. This is what selda is doing underneath.
Additionally like I said before - if we want to make encoders / decoders from postgresql-client to be portable to other backends we want to rather use native tuples structure returned from the DB.
Regarding other js “dirty tricks” which possibly can fail we have encountered one of them directly:

I totally agree. It would be cool to have SQL with annotations in PS code which can be type checked. postgresql-client links to an old script (purspg) which was used in the past for type annotations codegen but I think it is not usable any more. We have discussed this also with @akheron (who contributed proper pg error handling to the lib and is an author of https://github.com/akheron/sqltyper) in the past and I hope that we can provide this feature at some point (a type checker against postgres or codegen for types).

What I think is not great is composition of queries by String concatenation, typing queries by hand when schema is known and types can be derived etc. The main point of selda is to provide these features for you. It provides a way to compose large queries in a safe manner.

I wouldn’t call this approach “real world”. In any larger project this is not the case.

But of course I understand that selda is not the only solution to the problem :slight_smile: It can be a bit intimidating for the new PS users and error messages can be hard to understand in the case of more complicated queries. I can only say that we really try to improve the situation and continue investing work in the development of this lib :wink:

2 Likes
#8

I’m really sorry but I don’t have time at the moment to setup the project and build these queries. I hope that I can provide the answer on the weekend or we can discuss the details in between on the priv or on selda issues section.
One additional side note - @Kamirus put a lot of effort into documentation of the library - so we have:

1 Like
#9

I wonder if a prisma like approach is applicable to purescript.

#10

@paluh:

  • I have encountered this date conversion problem too. That’s why I used timestamp_to_char.
  • String concatenation is not nice, that’s a good point.
  • Actually I have meant these examples just as a quick yes/no problem check, like sub-queries - “no problem”, sql arrays - “out of the box” or insert-select - “you have to implement and test this feature yourself”. I will read up on the documentation, thank you.
3 Likes
#11

@paluh: Can this be done in purescript-selda (without too much overhead)?

Writing purescript-selda queries is just like writing SQL.
There is a corresponding function for each SQL statement (e.g. SELECT/JOIN/WHERE/…) that writes exactly that statement to the generated query, nothing more.

So each SQL query can be rewritten to selda and the structure of the generated query should be preserved.
It is just a structured, type-safe way of writing SQL.

As a result:

  • more errors are caught during type checking
  • you can abstract over parts of the queries - share, reuse, partially specify
  • but missing features require some additional work over writing SQL as a string

Although, extending SQL expressions (like custom functions or casting to array) in selda is easy (example: support for custom pg function)

I wanted to have the ability to copy the query, make a few changes and run it in the psql console and vice versa

You can easily generate a string from a query e.g. for debugging.

2 Likes