Msyql, postgres, postgrest

  1. For a Jsoo-frontend, OCaml-backend REST webapp, is there general preference of MySQL vs Postgres for the DB? (maybe some amazing idiomatic lib that only binds to one) or is it fairly interchangeable ?

  2. Anyone have experience ripping out the backend entirely and going directly to PostgREST Documentation — PostgREST 9.0.0 documentation ? (so frontend = jsoo, backend = postgrest)

Thanks!

My reckon is that postgresql is more widely used than mysql in this space, but that’s just a reckon. I happen to really like ppx_rapper, but pgocaml is another take on checking queries at compile time.

I’ve been intrigued by PostgREST for a while, but its ~requirement of a client-side view library is a blocker; I’ve been using htmx for such things, and have enjoyed the results. :person_shrugging:

1 Like

I’ve can give you my anecdotal feedback having played with postgREST a while ago.

Seems like a nice idea but things start to break down once you need to join multiple tables: Tables and Views — PostgREST 9.0.0 documentation

There are workarounds, such as creating custom views, but my overall impression was that your frontend basically “knows too much” about the the database.

Because of this, the frontend code can become more complicated than strictly necessary if you were in full control over the backend API.

Sorry, I am missing something very basic. I’m inferring from your statement that:

there exists common problems P where:

  • to solve P with postgrest requires N joins
  • to solve P with an OCaml backend sitting in front of postgres, we can do it with < N joins

I understand why “< N joins” is better than “N joins”. I it is not obvious to me why having OCaml sitting in between reduces the # of joins.

That wasn’t it at all.

I meant that the tool itself feels nice at first, until you start joining.

At which point you have to work around its restrictions which makes you waste the time you initially gained.

But try it for yourself, you mileage may vary.

In regards to choosing a RDBMS, I would strongly recommend PostgreSQL. Compared to MySQL, it has a much better feature set when it comes to modern SQL. And it’s not maintained by Oracle, which is a feature by itself in my book.

PostgREST is very well made (written in Haskell), stable, and performant. If you are comfortable with PostgreSQL, it’s easy to define your API and access constraints and you can quickly produce really good results.