[deleted]
What ORMs have taught me: just learn SQL
21–30 of 245 posts
Re: What ORMs have taught me: just learn SQL
#22I agree that SQL is a brilliant data processing language, while C#, Java or C++ are terrible at it. And there are obvious benefits to learning SQL and being able to use it efficiently, makes imho much more sense than throwing away RDBMS because they're 'slow'.
But if you write applications in an oo language objects are quite natural way of representing both data and logic. Sometimes you do application-level transaction management or use the mapping metadata to implement higher-level features, sometimes you have to integrate application events with data operations, or you need your data objects to live outside of database transaction - ORM saves you lots of coding in such situations. And adds services like caching, prefetch, intercept/triggers etc so you don't have to invent it.
Re: What ORMs have taught me: just learn SQL
#23Another problem with ORMs is that they make performance diagnostics much harder. DB-side, you might have a list of worst-performing queries and examining it reveals a huge, hundred-line monstrosity of a query. Because the queries are ORM-generated and are not usually very readable it isn't exactly clear which part of the application is generating it (or why). Even further, if your DBA says you could make the query more efficient by changing X and Y, it's not likely that you can take her advice since the query is programmatically generated! So you have the choice of abandoning the ORM in performance-sensitive paths and inserting raw SQL (making the code ugly) or living with poor performance.
I'm a fan of abstractions only when necessary. I want to have control over 100% of the code stack. When performance issues crop up (and they inevitably will), I don't want to just shrug and go "well, Hibernate must be doing something weird..."
Re: What ORMs have taught me: just learn SQL
#24I find ORMs quite handy, and they've always generated tight SQL.
Oh yeah, we had to support an additional DB recently... all that involved was changing the JDBC connection information and dropping in a new driver. Good luck doing that when everything is written in raw SQL.
Re: What ORMs have taught me: just learn SQL
#25I have a laravel project for which all of my models are raw SQL statements for this reason. At the very least, it makes the code more portable, and it makes it easier to reason about the statements when you can actually see them.
It makes your code less portable. Raw SQL means you're essentially tied to a single DB provider. What happens when you need to move to a different one?
Re: What ORMs have taught me: just learn SQL
#26I've caught a lot of flak for saying this, but I'm convinced that all ORMs are ultimately tech debt. Sure, they get you up and running quickly, but once you're there, you'll invariably find yourself wanting to do things that require you to work against and around your ORM to accomplish. By pretty much any definition I've ever encountered, that's "tech debt"
(caveat: I'm a developer but I haven't used ORMs very much.) Don't most ORMs let you write raw SQL when you really want to? In that case, you could use the ORM for simple things, but revert to raw SQL when you need more power. Or is that not the case?
Yes, but the ORM often influences the schema design. That can be very painful down the road when you realize your tables are actually tables, rather than instances of objects, which would be what your ORM led you to believe.
Re: What ORMs have taught me: just learn SQL
#27I used to write raw SQL for many years, then, around 2005 switched over to ORMs in order to be able to target different databases, have a nice model, etc. Lets be honest here, the ease of justing doing:
p.username = "Carl"
p.age = 33
p.save
However, the downside of this is that it only works as long as whatever you need from your database is the lowest common denominator of database features. I'm currently working on a Clojure + Postgres project and I'm using all the bells & whistles that Postgres has to offer: HStore types, Json Types, subqueries in subqueries, Upserts, functions, etc. I have a rather complex database that I'm running analytical queries against, and using an ORM for this would simply not work. I'd spend half my time trying to figure out how to implement a certain Postgres feature in -insert-or-name-here. I actually started with Korma [2], a simple Clojure ORM, and gave up because it was too tedious trying to figure out how to get it to correctly run my 4-function column result query.
I've since switched to YeSQL [3] which follows a very interesting idea: You write queries in .sql files (full editor support, jay) and tag every query with a name. YeSQL then reads these .sql files during compile time and dynamically generates clojure functions with the correct amount of parameters based on these queries. It is kinda the best of both worlds. Example:
queries.sql:
-- name: get-users
-- a function to retrieve the user by name
select * from users where username=:name
(defqueries "some/where/queries.sql")
;; now I can do:
(get-users "carl")
[1] http://slick.typesafe.com/ [2] http://sqlkorma.com/ [3] https://github.com/krisajenkins/yesql
Re: What ORMs have taught me: just learn SQL
#28> A recent count of one such table in my work resulted in over 600 attributes and 14 joins to access a single object, using the preferred query methodology
At some point between inception and having 600 columns someone had to have stepped in said is this necessary? Maybe I haven't worked with big enough data sets, but to me that number seems insanely high - and I really don't see how the ORM would be any worse than raw SQL - if they were equally optimized.
It sounds to me like best practices SQL are being compared with worst practices ORM. Now many ORMs, by default, may lead a developer to use bad practices, which I will concede is a problem.
But in optimizing a query with SQL you only select necessary fields - and don't use any more joins than required for the data you need - you only gain over the ORM if you didn't limit the scope of the object you asked the ORM for and instead asked for the full object.
> Attribute creep and excessive use of foreign keys shows me is that in order to use ORMs effectively, you still need to know SQL.
Well there is your problem! Thinking you can completely ignore the underlying database mechanism of an abstraction layer is a little naive - if you want to maintain maximum performance. Though bad marketing of some ORM's may claim differently which is a problem as well.
I agree, there is a potential downside to ORM's. Raw SQL likely will get you better performance almost 100% of the time. Is that necessary? Well, it depends. If the ORM is being used with best practices, I'd say until you're at the scale of billions of pageviews a month, it'd be negligible. However if you're essentially doing a SELECT * and JOINING on all related tables just to get one field from the Users table - then the SQL will win hands down.
Re: What ORMs have taught me: just learn SQL
#29In Django, for me the killer feature of the ORM is that it's (mostly) database agnostic, which means that you can use Postgres in production and in-memory sqlite when testing, which makes testing a gajillion times faster. If you start writing custom SQL you have to introduce horrible bodges to work with whatever database is in use.
Have you run your (mostly) database agnostic app at a large scale? When your app is running at scale, did you require any custom SQL or non-standard to the ORM stuff to get it running well? In my experience with Rails, the "database agnostic" features are for prototyping, and by the time you're a real app moving towards some idea of scale, there is nothing "agnostic" about your ORM and database code...
A fair point, I might be unduly influenced by having done a lot of work on a huge, unwieldy internal app. Running the tests in postgres took literally half a day, and there's no way we could have worked like that.
In that case trading some db efficiency for actually being able to do work was a pretty easy decision, but horses for courses I guess.
Re: What ORMs have taught me: just learn SQL
#30Here's a thought experiment. Lets say we lived in a world without SQL and the default way to talk to DB's was through an ORM.... And then someone came and said: "I created this concise and super flexible language for querying data." Would people want it? I think they would, and we'd see tons of articles about vast forests of objects being replaced by small snippets of SQL.
There is another theory which states that this has already happened. See: ISAM databases as found in COBOL programs.
The problem was SQL got standardized, and became static. There is so much room for innovation in the database language space, but that would mean breaking compatibility with the SQL standards...