4 ms·

What's the alternative? Are you saying it's better to use raw SQL or to use your own home grown convenience functions for creating tables, selecting rows, etc.?

What's the alternative? Are you saying it's better to use raw SQL or to use your own home grown convenience functions for creating tables, selecting rows, etc.? And once you have the data from the SQL database are you keeping it just in arrays/dictionaries? Or should it at least be mapped to a class structure? As someone dealing with a bespoke SQL schema and mix of in-house SQL translation layers for different DBs, wrapper functions... I would think any ORM would be better designed since it has a singular purpose and database experts work on their development.

bjt 5y ago
> What's the alternative? Not sure the parent comment would like it, but there's a middle ground between ORM and raw SQL that I consider a sweet spot. It's more of a "query builder" library that gives you language-appropriate constructs for building any SQL you like, but also provides more correctness guarantees than just writing raw SQL strings. SQLAlchemy's "expression" layer, for example, does this really nicely. It existed long before the higher "ORM" layer came along, and can be used without having to touch the ORM. In Go, I like the Goqu library for the same purpose. In Rails land, my understanding is that the underlying Arel layer is more like this pattern, as opposed to the higher level Active Record ORM. In Java, it's jOOQ instead of Hibernate.
- Supermancho 5y ago
  There was a PHP library called NotORM which was great.
  - g8oz 5y ago
    ezSQL is a similar project, also good.
- gmac 5y ago
  For TypeScript, there’s a very helpful list here: https://phiresky.github.io/blog/2020/sql-libs-for-typescript/ https://phiresky.github.io/blog/2020/sql-libs-for-typescript...
  - madkat 5y ago
    Is there something like this for MongoDB with Typescript?
- KptMarchewa 5y ago
  For Java, JDBI is also great choice.
  - nescioquid 5y ago
    I came to like the approach, but I got the impression that the dev team has never seen a breaking change they didn't like, so upkeep was painful. It's been a few years since I've used it, so maybe they've chilled out a bit.
- HelloNurse 5y ago
  The language appropriate tool for writing SQL is SQL. Anything else is a mess; even the ORM best case (generating a simple query from suitable metadata, without redundant source code) is both very complicated compared to just having the text of the SQL statement and very constraining for future evolution (e.g. when the query involves a new table, doing multiple queries and filtering data in the applications instead of using joins because it's the path of least resistance).
  - bjt 5y ago
    > The language appropriate tool for writing SQL is SQL. Anything else is a mess... It sounds like you're pretty set on that opinion, and that's fine, but I suspect you just haven't run into the case where the raw SQL is far messier than using a query builder. SQL strings are pretty inflexible. The big advantage of a query builder (note that I'm not saying "ORM") is that you can start with a simple base case and dynamically mutate the query to add clauses specific to the request you're handling. Maybe one user wants 10 results per page and another user wants 25, for example. I have an example of how far you can take this at http://btubbs.com/postgres-search-with-facets-and-location-awareness.html http://btubbs.com/postgres-search-with-facets-and-location-a.... I could not endorse trying to build the query interface in that post with raw SQL.
younepen 5y ago
>And once you have the data from the SQL database are you keeping it just in arrays/dictionaries? Or should it at least be mapped to a class structure? Modern SQL has functions that can be used to map rows into json objects and arrays. That is what I use in nodejs/postgres. Everything is returned in the structure I want it in. The node driver turns the json into javascript arrays and objects (which then get turned back into JSON to send to the client, hah!). I added some code to the driver so that snake case field names are converted to camel case.
mirekrusin 5y ago
Example of alternative https://observablehq.com/@mirek/tsql-examples https://observablehq.com/@mirek/tsql-examples
4lejandrito 5y ago
I’ve been using https://prisma.io https://prisma.io lately and I love it. It builds a Db client right from your db schema including Typescript typings. I don’t consider it an ORM in the classical sense. I see it more as a query builder.
- girvo 5y ago
  Prisma looks great as someone who lives in Typescript land. Have you been using it "in anger", are there any limitations you've run into that its worth keeping in mind? I'm going to give it a shot on my personal project.
  - 4lejandrito 5y ago
    Hi! Sorry for the late response. I've been using it "in a lot of pleasure". Bear in mind I've just used it for personal side projects, nothing too critical. I recommend you give it a try and form your own opinion. Feel free to get in touch!
golergka 5y ago
As I've already mentioned twice in this comment section, pgtyped and similar libraries. You wrote raw SQL, then they check it against a database at compile time and give you static types for your inputs and outputs.

bjt 5y ago

> What's the alternative? Not sure the parent comment would like it, but there's a middle ground between ORM and raw SQL that I consider a sweet spot. It's more of a "query builder" library that gives you language-appropriate constructs for building any SQL you like, but also provides more correctness guarantees than just writing raw SQL strings. SQLAlchemy's "expression" layer, for example, does this really nicely. It existed long before the higher "ORM" layer came along, and can be used without having to touch the ORM. In Go, I like the Goqu library for the same purpose. In Rails land, my understanding is that the underlying Arel layer is more like this pattern, as opposed to the higher level Active Record ORM. In Java, it's jOOQ instead of Hibernate.

Supermancho 5y ago
There was a PHP library called NotORM which was great.
- g8oz 5y ago
  ezSQL is a similar project, also good.
gmac 5y ago
For TypeScript, there’s a very helpful list here: https://phiresky.github.io/blog/2020/sql-libs-for-typescript/ https://phiresky.github.io/blog/2020/sql-libs-for-typescript...
- madkat 5y ago
  Is there something like this for MongoDB with Typescript?
KptMarchewa 5y ago
For Java, JDBI is also great choice.
- nescioquid 5y ago
  I came to like the approach, but I got the impression that the dev team has never seen a breaking change they didn't like, so upkeep was painful. It's been a few years since I've used it, so maybe they've chilled out a bit.
HelloNurse 5y ago
The language appropriate tool for writing SQL is SQL. Anything else is a mess; even the ORM best case (generating a simple query from suitable metadata, without redundant source code) is both very complicated compared to just having the text of the SQL statement and very constraining for future evolution (e.g. when the query involves a new table, doing multiple queries and filtering data in the applications instead of using joins because it's the path of least resistance).
- bjt 5y ago
  > The language appropriate tool for writing SQL is SQL. Anything else is a mess... It sounds like you're pretty set on that opinion, and that's fine, but I suspect you just haven't run into the case where the raw SQL is far messier than using a query builder. SQL strings are pretty inflexible. The big advantage of a query builder (note that I'm not saying "ORM") is that you can start with a simple base case and dynamically mutate the query to add clauses specific to the request you're handling. Maybe one user wants 10 results per page and another user wants 25, for example. I have an example of how far you can take this at http://btubbs.com/postgres-search-with-facets-and-location-awareness.html http://btubbs.com/postgres-search-with-facets-and-location-a.... I could not endorse trying to build the query interface in that post with raw SQL.

younepen 5y ago

>And once you have the data from the SQL database are you keeping it just in arrays/dictionaries? Or should it at least be mapped to a class structure? Modern SQL has functions that can be used to map rows into json objects and arrays. That is what I use in nodejs/postgres. Everything is returned in the structure I want it in. The node driver turns the json into javascript arrays and objects (which then get turned back into JSON to send to the client, hah!). I added some code to the driver so that snake case field names are converted to camel case.

mirekrusin 5y ago

Example of alternative https://observablehq.com/@mirek/tsql-examples https://observablehq.com/@mirek/tsql-examples

4lejandrito 5y ago

I’ve been using https://prisma.io https://prisma.io lately and I love it. It builds a Db client right from your db schema including Typescript typings. I don’t consider it an ORM in the classical sense. I see it more as a query builder.

girvo 5y ago
Prisma looks great as someone who lives in Typescript land. Have you been using it "in anger", are there any limitations you've run into that its worth keeping in mind? I'm going to give it a shot on my personal project.
- 4lejandrito 5y ago
  Hi! Sorry for the late response. I've been using it "in a lot of pleasure". Bear in mind I've just used it for personal side projects, nothing too critical. I recommend you give it a try and form your own opinion. Feel free to get in touch!

golergka 5y ago

As I've already mentioned twice in this comment section, pgtyped and similar libraries. You wrote raw SQL, then they check it against a database at compile time and give you static types for your inputs and outputs.