6 ms·
Over and over I keep finding that just an ORM is not enough, but raw SQL is hideous in a different way. ORMs map nicely when you are indeed modifying objects,
by makmanalp 12y ago
Over and over I keep finding that just an ORM is not enough, but raw SQL is hideous in a different way.
ORMs map nicely when you are indeed modifying objects, but somethings don't map well that way. So don't map them that way! What we need is a low level abstraction layer alongside the ORM.
The main problem with raw SQL is that what you really want is a genuine programming language. You almost want programmatic access to the SQL AST, so you can generate syntax as opposed to concatenate strings together. Kind of like a DOM API, but for SQL.
Let me give you an example from what I'm working on now. So let's say you have a generic query for fetching monthly aggregated import / export values between countries. Sometimes you want to filter by one country. Sometimes you want to filter by 10 countries. Sometimes you want to use the column containing the inflation-adjusted value instead of the regular column. Sometimes you want the average export instead of the total export.
With a low level abstraction layer, I can do stuff like already_complicated_query.filter(another_param==5). Or I can write a function that does get_world_trade_aggregate(country="USA", aggregate="average") and it'll generate the right query for me. But then that's not even all, if I have to modify or filter that query further in some other part of the code, in some unexpected way totally doable. It's less often that I have to write a whole new function or duplicate code.
So the point isn't the abstraction, it's the fact that it makes queries modifiable and composable, which is something you just can not have in raw sql. So given a query, I can decide to add something to it or change it without having to change the text based SQL. You know how it's good practice to split your 1000 line function into smaller, modular pieces? With this you can do that with your ginormous SQL query too. AND it's (well, almost) guaranteed to generate correct and safe SQL.
This also makes everything much more maintainable. E.g. If I change table names or migrate fields, often it's a non-issue and there is a single place I need to change that. But with raw SQL you have to go in and do a ctrl-f and hope you catch everything, but often you don't because someone decided that that field name should come from a variable or something.
Raw SQL spits into the face of everything we've learned about programming languages in the past 60 years.
Anyway, the neat thing is that this magical system exists, as the SQLAlchemy Core (http://docs.sqlalchemy.org/en/rel_0_9/core/tutorial.html http://docs.sqlalchemy.org/en/rel_0_9/core/tutorial.html). Enjoy!
- akavi 12y ago>With a low level abstraction layer, I can do stuff like already_complicated_query.filter(another_param==5). Or I can write a function that does get_world_trade_aggregate(country="USA", aggregate="average") and it'll generate the right query for me. But then that's not even all, if I have to modify or filter that query further in some other part of the code, in some unexpected way totally doable. It's less often that I have to write a whole new function or duplicate code. If you're working in Ruby, there's also Arel[1]. I have my complaints, but it seems to be the exact same thing: Composable, programmatic access to SQL from your application code. [1] https://github.com/rails/arel https://github.com/rails/arel
- jeltz 12y agoIf you are working with Ruby Sequel is a way better tool for building SQL queries. http://sequel.jeremyevans.net/ http://sequel.jeremyevans.net/
- doctorpangloss 12y ago> You almost want programmatic access to the SQL AST, so you can generate syntax as opposed to concatenate strings together. Kind of like a DOM API, but for SQL. I think this is the appeal of MongoDB's driver on Node: You really do have programmatic access to the AST, insofar as the microlanguage is just a plain old Javascript object. Though SQL is more universal, Mongo's approach definitely has thought hard about the balance between abstract and concrete that me and my other developers find very intuitive. So intuitive that we use their microlanguage way outside applications in ORM, like for sorting, filtering and object updates.
- jasode 12y ago>Though SQL is more universal, Mongo's approach definitely has thought hard about the balance between abstract and concrete that me and my other developers find very intuitive. I think you've got the causality and conclusion backwards here. MongoDB's easy programming API is a consequence of its storage layout on disk -- the JSON/BSON bytes on disk. From that principle, you naturally get an "ORM" type of API exposed in the programming language basically for free. I'm saying you don't have to do a lot of theoretical computer science type of research and pondering to get a disklayout+API that looks like that. In fact, the early 1960s mainframes laid out data records as fully denormalized (similar to JSON/BSON) before the relational DBs became popular in the 1970s. But, if your app starts expanding into complex data that's not 100% embedded as fields within one document, you start needing to do "relational" type of joins across documents. And those joins will manifest itself as extra programming code/logic on the client side. You're still paying for added complexity. You're just paying in a different way from the ORM+RDBMS programmers. Many MongoDB projects want it to perform more like relational. Likewise, many RDBMS want it to act more like an object hence ORMs. The impedance mismatch looks like irreducible complexity.