4 ms·

After working with ORMs for a long time, all I really want is a nice API for building queries (that actually supports all underlying database features) and auto

by vlunkr 4y ago

After working with ORMs for a long time, all I really want is a nice API for building queries (that actually supports all underlying database features) and automatic mapping of the results to whatever objects/structs and primitives the language supports. Everything else is IMO a bad layer of abstraction that will eventually bite you when you need to get closer to SQL.

jkaptur 4y ago
> a nice API for building queries (that actually supports all underlying database features) Honestly asking - I'm not an expert in this space: is this not just parameterized queries? It seems like anything else would have to have exactly the same "shape" in order to support all underlying database features...
- mbreese 4y ago
  The only part of ORMs I liked was the ability to dynamically build queries. You’re right that a query would have to return the same columns (for common downstream processing), but a query builder can make adding conditions much easier. For example, if you’re adding filtering to a product list. The query will always return the product id, name, image url and description. But you might have multiple filters (in stock, blue, etc). The query builder can make that process easier and less error prone. Same with pagination, depending on how you do it.
- vlunkr 4y ago
  A query builder is much more flexible. For example, in some cases there might be an additional condition you want to add to the WHERE clause. You can't do that with parameterized queries, so you'd have to make a new query for every possible variation. That may be fine in some cases, but it could become a maintenance headache in large project.
collinvandyck76 4y ago
https://sqlc.dev/ https://sqlc.dev/ is nice in this regard.
hatchnyc 4y ago
Yeah I built one of these for .NET specifically for this, with DB-specific drivers to extend the API, especially for Postgres. It just tries to be a dead simple representation of the query, then provides a command API to do mapping and execution, some unit of work support. Really happy with it so far, especially compared to an ORM.
codegeek 4y ago
nice API for building queries" You just described ORMs. The implementation details can vary but ORM is pretty much an API for building queries on top of a DB.
- vlunkr 4y ago
  That's true. I guess what I have in mind are ORMs like Active Record, which adds all sorts of magical layers on top the query building, but still somehow gets the some of the basics wrong, like being able to select arbitrary values easily, or making OR queries an intuitive mess.
- jeltz 4y ago
  No, most ORMs are bad APIs for building queries due to trying to hide that the database is relational.
pkphilip 4y ago
I feel that the JDBCTemplate from Spring gives a pretty good compromise for this
- za3faran 4y ago
  What do you think of Spring Data JDBC (https://spring.io/projects/spring-data-jdbc https://spring.io/projects/spring-data-jdbc)? It seems it's somewhere between JDBCTemplate and Spring Data JPA.
hans_castorp 4y ago
> all I really want is a nice API for building queries (that actually supports all underlying database features) and automatic mapping of the results to whatever objects/structs and primitives the language supports. For Java based solutions, check out https://www.jooq.org/ https://www.jooq.org/ or http://querydsl.com/ http://querydsl.com/
Izkata 4y ago
From what little I've seen of it, SQLAlchemy on python might fit. It's not an ORM, it's a query builder.
- claytonjy 4y ago
  Even better we now have SQLModel¹, which bridges SQLAlchemy and Pydantic. ¹: https://sqlmodel.tiangolo.com/ https://sqlmodel.tiangolo.com/
  - ac130kz 4y ago
    Too bad it is not ideal compared to a full-blown SQLAlchemy setup, and it's not production quality yet. Hopefully he'll get it done sooner or later.
- bulatb 4y ago
  It has separate, stackable layers for query building, data mapping, ORM, and ActiveRecord-ish declarative models. You can pick the parts you want and ignore the rest. The query builder makes composable SQL syntax trees from raw text or Python objects. Executing those as queries gives you rows of tuples. If you teach it the shapes of your data with the data mapper, you can query in terms of your tables (instead of ad-hoc column names) and get rows of populated custom objects. On top of the mapper, the ORM defines your application-level data model, manages relationships and loading strategies, and powers up the query builder with the usual stuff like automatic joins (if you want them). You can still build composable queries, now against your application objects, but you can also work directly with the model like a standard ORM. If you know you want the whole thing from the start, Declarative will let you do the mapping and the ORMing all at once by defining a class.

by vlunkr 4y ago

jkaptur 4y ago

> a nice API for building queries (that actually supports all underlying database features) Honestly asking - I'm not an expert in this space: is this not just parameterized queries? It seems like anything else would have to have exactly the same "shape" in order to support all underlying database features...

mbreese 4y ago
The only part of ORMs I liked was the ability to dynamically build queries. You’re right that a query would have to return the same columns (for common downstream processing), but a query builder can make adding conditions much easier. For example, if you’re adding filtering to a product list. The query will always return the product id, name, image url and description. But you might have multiple filters (in stock, blue, etc). The query builder can make that process easier and less error prone. Same with pagination, depending on how you do it.
vlunkr 4y ago
A query builder is much more flexible. For example, in some cases there might be an additional condition you want to add to the WHERE clause. You can't do that with parameterized queries, so you'd have to make a new query for every possible variation. That may be fine in some cases, but it could become a maintenance headache in large project.

collinvandyck76 4y ago

https://sqlc.dev/ https://sqlc.dev/ is nice in this regard.

hatchnyc 4y ago

Yeah I built one of these for .NET specifically for this, with DB-specific drivers to extend the API, especially for Postgres. It just tries to be a dead simple representation of the query, then provides a command API to do mapping and execution, some unit of work support. Really happy with it so far, especially compared to an ORM.

codegeek 4y ago

nice API for building queries" You just described ORMs. The implementation details can vary but ORM is pretty much an API for building queries on top of a DB.

vlunkr 4y ago
That's true. I guess what I have in mind are ORMs like Active Record, which adds all sorts of magical layers on top the query building, but still somehow gets the some of the basics wrong, like being able to select arbitrary values easily, or making OR queries an intuitive mess.
jeltz 4y ago
No, most ORMs are bad APIs for building queries due to trying to hide that the database is relational.

pkphilip 4y ago

I feel that the JDBCTemplate from Spring gives a pretty good compromise for this

za3faran 4y ago
What do you think of Spring Data JDBC (https://spring.io/projects/spring-data-jdbc https://spring.io/projects/spring-data-jdbc)? It seems it's somewhere between JDBCTemplate and Spring Data JPA.

hans_castorp 4y ago

> all I really want is a nice API for building queries (that actually supports all underlying database features) and automatic mapping of the results to whatever objects/structs and primitives the language supports. For Java based solutions, check out https://www.jooq.org/ https://www.jooq.org/ or http://querydsl.com/ http://querydsl.com/

Izkata 4y ago

From what little I've seen of it, SQLAlchemy on python might fit. It's not an ORM, it's a query builder.

claytonjy 4y ago
Even better we now have SQLModel¹, which bridges SQLAlchemy and Pydantic. ¹: https://sqlmodel.tiangolo.com/ https://sqlmodel.tiangolo.com/
- ac130kz 4y ago
  Too bad it is not ideal compared to a full-blown SQLAlchemy setup, and it's not production quality yet. Hopefully he'll get it done sooner or later.
bulatb 4y ago
It has separate, stackable layers for query building, data mapping, ORM, and ActiveRecord-ish declarative models. You can pick the parts you want and ignore the rest. The query builder makes composable SQL syntax trees from raw text or Python objects. Executing those as queries gives you rows of tuples. If you teach it the shapes of your data with the data mapper, you can query in terms of your tables (instead of ad-hoc column names) and get rows of populated custom objects. On top of the mapper, the ORM defines your application-level data model, manages relationships and loading strategies, and powers up the query builder with the usual stuff like automatic joins (if you want them). You can still build composable queries, now against your application objects, but you can also work directly with the model like a standard ORM. If you know you want the whole thing from the start, Declarative will let you do the mapping and the ORMing all at once by defining a class.