seems like almost every popular language has at least one library for making SQL queries in a domain-specific language that maps language functions onto SQL syntax. and that's really cool for every day type queries (getting rows matching a list of IDs, inserts, updates, deletes, getting associated records through relations).

sooner or later, however, i end up needing to write a query that's really complicated and then i always feel like i'm tripping over my own shoelaces. and the usual way out is to write the query in SQL first, then try to translate it back into library-speak after.

this happened today. i had to calculate a bunch of stuff from very normalized data, and ended up writing three subqueries with a bunch of left joins inside each one, all stapled together by more joins. and now i have to figure out how to write it in Ecto's DSL.

and since i wouldn't have to do that if i didn't choose to use Ecto in the first place, it always feels bad because i know it's all extra work i have made for myself. maybe it still saves me effort overall, but it feels kind of hard to tell sometimes.

what do y'all think about ORMs, and query DSLs? got any you like to use? any horror stories?

Comments

You must log in or register to comment.

lainproliant wrote on January 12, 2018 at 10:31 PM

I feel that ORMs can be useful in certain projects, especially ones where you are building a small app to interact with an existing database and want to get off the ground running fast. If you end up interacting with a dataset that is very relational, you'll start running into some of the edge cases of optimization in ORMs and end up with N+1 queries and such.

I have experience with Hibernate in Java and SQLAlchemy in Python. In general I like them both but I tend to avoid them for projects where the DB schema is simple and just roll my own DAO layer.

musou OP wrote on January 12, 2018 at 10:54 PM

yeah this sounds like good advice. i kinda wish i had gone that route with this project and just handled type coercion and pagination myself

flabberghaster wrote on January 19, 2018 at 4:21 AM

I'm kind of surprised they're that hard to work with. I've never used one, just hasn't come up in any of the projects i've been on; But i'd expect them to have some escape hatch. Something where you'd be able to just give it an SQL query you want to run, as a prepared statement or something, tell it some variables to put in, maybe tell it what types it would return, depending on the language, and just have it run it.

Do they not? Or is it just impractical to use somehow?

musou OP wrote on January 19, 2018 at 6:48 AM

yeah most of the ones i have used do have the ability to execute raw SQL queries, which is how i solved the problem i had the other day. but you lose a lot of the benefit of using the ORM in the first place, you usually have to handle query pagination, input sanitization, and casting the response (which usually comes back as a 2D list of primitives) to the appropriate data structures yourself.

and just as bad, the code now has inconsistency in it. i never feel good about doing the same thing two different and incompatible ways.