21 ms·

What ORMs have taught me: just learn SQL (2014)

379 points by bsg75 10y ago

Animats 10y ago
ORMs tend to assume that there is "the application" with "its database". If the application changes, so does the database. If the data is used by more than one application, it's better to have the data defined in the database and write applications as database clients.
- vrdabomb5717 10y ago
  At the big tech firm I work at, there's a best practice where any database (whether that's a traditional RDBMS or a NoSQL client) is abstracted away by a microservice with a defined API, and every other application that wants to get that data needs to interact with the microservice. That way, the database schema can change without it affecting multiple applications. There's still the traditional mismatch between ORM and database, but doesn't feel as painful because only a single application is using that data, and that application can have special knowledge of the persistence layer underneath (meaning, use database hints if necessary, defer to raw SQL, etc.)
  - prodigal_erik 10y ago
    We have decades of research into filtering, joining, and aggregating across a complex set of tables and views. With microservices you have to roll your own query planning and stream all the intermediate results on the wire even when you're throwing away most of them.
    - collyw 10y ago
      It does sound like an extra layer of unnecessary complexity to solve a problem that could be solved in other ways.
    - intrasight 10y ago
      That probably is true - if you have a crappy API design
    - elcritch 10y ago
      Unfortunately, SQL servers aren't generally scalable. Offloading joins and aggregation onto (inexpensive) app servers can increase over all system performance, despite lack of advanced query planning.
      - collyw 10y ago
        Bollocks. SQL scales fine unless you are stupid in the way you use it. All the crap performance I have seen in the last couple of years have been from doing crap like you suggest - doing joins and aggregation at the application level. Our front page currently makes over 1000 database calls because of this sort of nonsense.
    - lmm 10y ago
      The thing is, letting the system handle it isn't good enough. We have decades of research into this stuff, so 90% of the time the database gets it right - but the interface for explicitly taking control when the DB gets it wrong is black magic at best. The value of having an exposed representation of filtering/joining/aggregation in a general-purpose programming language where you can interact with it is big enough to outweigh that of a database that can do it for you most of the time.
  - abraae 10y ago
    That kind of sounds like a microservices anti-pattern in disguise. Its thought by many to be bad practice for microservices to share a common database. But that's pretty much what you're doing, but just with an API instead of jdbc for the access.
    - lmm 10y ago
      An API is much easier to version-control, automatically check for compatibility and so on.
- VLM 10y ago
  I've slathered in yet another confusing layer by creating an application specific view. Now you've got the ORM layer, which possibly translates to SQL correctly, to talk to a view, which is a front on the real untouched table. Its kind of a hassle.
- collyw 10y ago
  Ok, I have only used Django's ORM and a little bit of SQLalchemy, but in both cases you are defining the database in a Python file. Django's models.py file usually maps directly to database tables, and is less verbose than writing SQL create statements. Maybe I don't see any advantage in what your doing, as I treat the database model as the most important part of the application (or maybe thats the same as what you are saying). Get that correct, and code falls into place easily. Start hacking rules in at the application level and eventually it gets messy. Linux said something similar about bad programmers worrying about the code, and good ones worrying about the data and its relationships.
- collyw 10y ago
  Ok, I have only used Django's ORM and a little bit of SQLalchemy, but in both cases you are defining the database in a Python file. Django's models.py file maps directly to database tables, and is less verbose than writing SQL create statements. Maybe I don't see any advantage in what your doing, as I treat the database model as the most important part of the application. Get that correct, and code falls into place easily. Start hacking rules in at the application level and eventually things start to get messy. Linux said something similar about bad coders worrying about the code, and good ones worrying about the data and their relationships.
vinceguidry 10y ago
The thing I love about ActiveRecord is that it makes it easy to, anywhere you want, and at any level of the abstraction stack you want, to just toss in SQL fragments. This gets you the best of both worlds, the fluidity of being able to just define methods on model objects, and the ability to utilize database tech to the fullest. You can take any query and call .to_sql on it and it shows you exactly what it's passing to the database. I prefer Sequel's semantics but ActiveRecord is an excellent workhorse that I have no problems relying on.
- sirn 10y ago
  But how do you do: SELECT posts.*, (SELECT COUNT(1) FROM comments WHERE post_id = posts.id) AS comments_count FROM posts; In ActiveRecord, without 1+N queries, or caching comments_count in a column somewhere? Admittedly, that was not the best example. The last time I need something more intertwined than a simple COUNT in subquery, the answer was "give up and just use Arel." But at this point it is no longer quite ActiveRecord, but rather a SQL without strings. This is my biggest gripe against the Active Record pattern in general, as it ties its model too tightly to the underlying database. It is convenient for a simple CRUD tasks, which may fit about 90% of use case, but that's not the only thing the database is capable of.
  - samwillis 10y ago
    I don't know about active record but with Django it's simply: Post.objects.all().annotate(Count('comments')) Which produces (simplified, Django would actually explicitly select each column): SELECT posts.*, COUNT(comments.id) AS comments_count FROM posts LEFT OUTER JOIN comments ON (posts.id = comments.post_id) GROUP BY posts.id Nice, easy and without 1+N queries.
    - sirn 10y ago
      That is actually very nice and elegant!
  - fennecfoxen 10y ago
    I've actually never used ActiveRecord myself, but when an ORM breaks down and it becomes hard to make certain queries I've had good luck with other ORMs by creating a view in the database and telling the ORM that it's a table. Obviously you can't insert into arbitrary views, but most are happy to give you an object back, with appropriate relationships to the actual database-writable objects. I'm surprised there isn't more explicit support for this in more ORMs (things like not having 'save' methods on the ORMed classes).
maxxxxx 10y ago
I think ORMs are a great tool to get something off the ground quickly. Like with most tools you will hit a point where they make things more difficult and then it's probably time to switch to SQL only or mix SQL with ORM especially for performance critical queries. In most applications I have seen the ORM provided a lot of value but there were cases where it needed to be augmented with raw SQL. I never understand why people want one or the other exclusively. Both have their place. I think stored procedures would be very useful if they integrated better with source control and the app code. Maybe we need an ORM for stored procedures that automatically creates stored procedures from the project code?
- ams6110 10y ago
  It's quite trivial to keep stored procedure code in source control with the app code or in its own repo. I am always mystified when I hear the complaint that this is difficult because in my experience it's no more difficult than managing any other code in a SCM repo.
  - jjoonathan 10y ago
    It's not only easy to manage stored procs separately, it has huge advantages: you can play with your SQL in an IDE built for the task with luxuries like real-time error highlighting, profiling, debugging, and auto-complete, and in some cases you can even iterate on your code without restarting your application (just update the proc).
  - brightball 10y ago
    My biggest concern has always been around zero downtime deployment with stored procedures. Your database has to work with both the old and new versions of web code during the deploy in case of rollback to make that process work and that always struck me as the weird edge case that makes things tough. If a procedure never changes or remains backward compatible then it should still be fine though.
    - platz 10y ago
      Give the new proc a different name (e.g. append a version number). On the n+1 deployment, clean up the old proc.
chillacy 10y ago
> ORMs are more detriment than benefit The author mentions Hibernate and SQLAlchemy, which are both DataMapper ORMs. But what about Active Record? It's true that AR will provide even more abstraction and distance from the database, but it also provides a lot more convenience, which for me in small CRUD projects (as most are) has been worth the downsides. And as another posted mentioned, you can always optimize by replacing slow AR queries with custom SQL ones, and restructuring your database as your project scales.
tfigment 10y ago
I written a fair number of C# LOB apps and use LINQ quite a bit with mysql. I don't even want to talk about Java and some of its ORMs as its too painful to think about. I agree with the sentiment of the post but in compiled languages I really want an ORM to simplify unpacking result sets. LINQ is great when it works but joins sort of suck as well as calling in-built sql functions and it can some times generate highly unoptimized queries. I frequently pull in data via LINQ to SQL and then massage the data with LINQ for objects where I have better control of performance and operations. Trying to write pure SQL leads to lots of manual unpacking of the result set which I generally dislike and is much harder to maintain and doesn't work well in practice compared to when LINQ actually works. I think maybe what I've really learned is use scripting / loosely coupled type systems when working with SQL. In python, I usually just call sql directly rather than use sqlalchemy and its fine because of the loose typing and result set unpacking isn't terrible.
- bunderbunder 10y ago
  On C#, Dapper's useful, but not perfect, for letting you write your own queries and then making it easy to unpack the result sets. Unfortunately, it relies on property setters for doing the unpacking, so it doesn't interact super well with your code if you like to avoid unnecessary mutability. The only publicly-available lightweight ORM I know of that does a good job with that is the SQL type provider in F#.Data. That one is head-and-shoulders above any other option I've found for working with databases. It does require that you write your data access layer in F#, though, which may make it a hard one to sell at work.
  - tfigment 10y ago
    Thanks. That actually doesn't look that bad. It would be nice to have anonymous type objects but I recognize the difficulty in that. This looks like a nice compromise. Now if they could also fix passing in arrays as part of a parameterized query "select x from y where z in ?" where ? is a collection of strings or integers it would be perfect but I think that is a driver/interface problem. Edit: Looks like it actually does says it does support the enumerable but I'm questioning if it will do what I want. Will have to test out but I'm guessing there is a limit to the size of the array.
Gustek 10y ago
I like to say "ORMs make easy thing easier and hard things harder". What i mean by that is, any simple CRUD operations are much easier in ORM. The hard things, i mean any complex queries that need more than one join you are probably better of writing yourself. In the end i prefer to do inserts, updates and deletes with ORM (or some other database abstraction tools) but most SELECTs i write myself, fetching exactly what i need and mapping result to objects if needed manually.
- IndianAstronaut 10y ago
  Do uou use metadata from the db driver to do type mappings?
  - Gustek 10y ago
    Not sure what you asking about. Do you mean if I define database structure in my code? The answer is: yes. I don't know about any database abstraction library that would provide you with convenience methods to query database with type checking and stuff without giving it the schema.
- craigvn 10y ago
  I pretty much do the same. The CUD is ORM, and some simple selects in C# Linq, but as soon as the query is a bit more complicated I crack out the SQL. It does not have to be all or nothing.
EToS 10y ago
I love ORMs, they help abstract the database technology which has been really useful in a recent project switching an app from MSSQL to PostgreSQL. I also personally as developer appreciate the programmatic syntax. I would caveat the performance issue, sometimes there is no better way, however i would always try to make things work in the ORM first before switching into native SQL to get the job done.
- nommm-nommm 10y ago
  > they help abstract the database technology If you are going to do anything even remotely complex you're going to need to know that database technology otherwise your ORM is going to spit out queries that are dead stupid and a performance nightmare (pages taking 1 full minute to load). Then you end up fighting with the ORM to do what you want. That's my experience. I guess there are projects that are so simple the database layer can be abstracted away but I've never worked on one.
YeGoblynQueenne 10y ago
Sigh. Whenever I have to hurt myself at work on such issues, I find myself thinking of the web that could have been, if only... Let me backtrack a bit. There's a much better way to do things than the way we do it right now. A way that completely obviates the need for ORMs, or indeed any way to deal with the interface between application and database. You see, there would be no object-relational Impedance Mismatch (and therefore no need for such clunky kludges like ORMs) if the database and the application were both written in languages of the same paradigm, either both OOP or both relational. Ideally, that would be a single language, that could handle both data and application logic. There is such a language: Prolog [1]. Prolog is implemented as a relational database. So your program itself is the database. There is no separation between data and operation and therefore no ORIM and no need for ORMs, or stored procedures or anything, really. And, yes, it's perfectly possible to do your web dev in Prolog. Here, see this explainer on the Swi-Prolog website, titled "Can I replace a LAMP stack with SWI-Prolog?": http://www.swi-prolog.org/FAQ/PrologLAMP.txt http://www.swi-prolog.org/FAQ/PrologLAMP.txt Hint: Yes. Yes, you can. You can replace LAMP (or LAM-whatever) with an LP stack, where all you need on top of your OS is Prolog itself. Its built-in Definite Clause Grammars notation [2] can be used to parse and generate javascript, html, xml, css, YAML, whatever you like. Swi-Prolog even offers translation to RDF, which is as natural as you can expect given RDF is also a relational language. Here's a big fat tutorial to help you started: www.pathwayslms.com/swipltuts/html/index.html Can this be done for real? OMG yes it can. The Swi-Prolog website itself runs on an LP stack. There's a few more websites that do too: www.pathwayslms.com/swipltuts/html/index.html In short: you don't need to hurt yourself as badly as you 're currently doing it. There's no other need than of course, nobody wants to learn Prolog. I know that, I've made my peace with that and I've spent all of my so-far career hurting myself against the ORIM just like everyone else. ... but we could have had a better web. ___________________ [1] https://en.wikipedia.org/wiki/Prolog https://en.wikipedia.org/wiki/Prolog [2] https://en.wikipedia.org/wiki/Definite_clause_grammar https://en.wikipedia.org/wiki/Definite_clause_grammar
- jsmeaton 10y ago
  This sounds really interesting, but I'm having a lot of trouble trying to follow how all that code fits together - from what code I could find. I also strongly dislike the object/function based approach to constructing HTML (which - funnily enough, is just like using an ORM to construct SQL which I like heh). Really, I don't see enough offered to counteract what little pain I feel using an ORM. The LP actually looks more painful (to me). Obviously I'm totally unfamiliar with this stack which makes it hard for me to see where the benefits lie.
vladsanchez 10y ago
I've always said: "ORMs are for people who don't know SQL!"
- pixie_ 10y ago
  ORMs are not for people who don't know SQL. The secret to using an ORM is knowing exactly the SQL it will generate underneath. The advantage to using one is static typing as well as writing orders or magnitude less code than SQL. As well as orders of magnitude more understandable code than the equivalent SQL. If you work on heavy enterprise applications the ORM can be your best friend or worst enemy. It comes down to knowing the tool you're using inside and out. It will make you much more productive in the long run. And sometimes yea, you need straight SQL or a stored proc to get the job done. That's OK.
- bdcravens 10y ago
  I began programming in languages where SQL was a requirement in 1999; only in the past 3-4 years have I use ORMs. I often write a lot of raw SQL, but I find a well written ORM statement communicates intent more quickly.
- spriggan3 10y ago
  > I've always said: "ORMs are for people who don't know SQL!" False. To use an ORM you must know and understand data modelling and the Entity/Relationship model. I'd say, people who don't like ORMs are often those who don't understand that model. If you do understand it, then you know that relationships can be represented 100% by code in an OO language, thus completely automated. Why bother writing your own hydrators for each entity when you can model them directly in a library? it doesn't make a codebase more maintainable or readable. Furthermore you could have an ORM that uses SQL directly for queries, and still eagerly resolve relationships. It's just not practical, that's why ORMs often come query builders. That is the thing that abstracts SQL, not an ORM per say. ORMs only deal with relationships between entities. Query Builders build SQL queries.
- mb_72 10y ago
  This doesn't ring true for me, at least - I've been programming professionally (using SQL) since the mid-90s, and these days very much appreciate the time-savings that ORMs (on Windows, for the most part) provide. With my favourite ORM - XPO from Developer Express - there is a decent tool for monitoring queries and sessions, and if I need to I can write custom SQL to improve performance (which almost never happens). Another important point is that it's, IMO, much easier to maintain ORM-based code, and another programmer coming along after me can more easily get started on understanding what I've implemented. Once place I worked in the past we had a 'SQL guru' who wrote these massively complicated SQL queries that could be understood by no-one by him.
ccleve 10y ago
ORMs can alleviate a lot of tedium, until you get to a level of complexity where they start to create it. I created my own ORM-like access library which seems to work pretty well. https://github.com/dieselpoint/norm https://github.com/dieselpoint/norm Comments welcome.
Grue3 10y ago
SQL is a write-only language. You better hope you don't have to rewrite your complicated raw SQL queries in the future.
- bdcravens 10y ago
  You can write well written modular SQL that supports changes and can be read by a competent developer.
  - Grue3 10y ago
    Not in my experience. Please show me actual examples of "well-written modular SQL" in a real application.
    - Something1234 10y ago
      Would modular sql be wrapping up subqueries inside of views, and then selecting from those views?
      - jsmeaton 10y ago
        Common Table Expressions (CTEs) help with modular SQL.
    - bdcravens 10y ago
      I'm taking a bit of creative license with the term "modular". CTEs and views can help with this, and are closer to true modules. I like to encapsulate inside of stored procedures, and write my code in such a way that it reads almost like a a procedural language. Heavy use of indentation, subqueries, and temp tables lets you carry results through the proc. I like to hang conditions off of joins, so related logic is in the same place.
  - maxxxxx 10y ago
    If you do SQL full-time probably yes. The problem is that most developers don't work enough with it so their skills are lacking.
bluejekyll 10y ago
I'm curious how the author would like MyBatis, which is just a thin wrapper for calling SQL, and then translating results to POJOs. I like it, and it, IMO, keeps the API distinct from the access in the DB. You have to write your own SQL, which is good and bad. Huge amounts of control and performance, but higher portability costs.
- pbreit 10y ago
  Also Records for Python: https://github.com/kennethreitz/records https://github.com/kennethreitz/records
StevePerkins 10y ago
Ten years ago, there was a blog post every other week bemoaning ORM's. Ten years ago, those posts often had merit. In 2016, this sentiment is outdated. A few points: 1. If you think that using an ORM means you don't have to learn SQL, then you're going to have a bad time. This is where most of the bad press originates... from people who never really learned SQL or their chosen ORM. An ORM provides type checking at your application layer, and typically better performance (unless you plan on hand-rolling your own multi-level cache system). But you still must understand relational database fundamentals. 2. If you're not using an ORM, then you ultimately end up writing one. And doing a far worse job than the people who focus on that for a living. It's no different from people who "don't need a web framework", and then go on to re-implement half of Rails or Spring (without accounting for any CSRF protection). Learning any framework at a professional level is a serious time investment, and many student beginners or quasi-professional cowboys don't want to do that. So they act like their hand-rolled crap is a badge of honor. 3. Ten years ago, it was a valid complaint that ORM's made custom or complex SQL impossible, didn't play well with stored procedures, etc. But it's 2016 now, and this is as obsolete as criticizing Java by pointing to the EJB 2.x spec. I can't think of a single major ORM framework today that doesn't make it easy to drop down to custom SQL when necessary.
- PeCaN 10y ago
  > If you're not using an ORM, then you ultimately end up writing one. I disagree with this. A lot of things people use ORMs for are rather easily solved with stored procedures, especially in Postgres where you can write stored procedures in Perl, Ruby, etc. Validations, “fat models”, etc are all managed with SQL easily (and this means you get that functionality from _anywhere you access the database_, not just from your framework with an ORM). For convenient access you can roll 30 lines of Perl to wrap DBI or whatever (I use Perl for most web backends these days) and call your stored procedures in normal syntax with a little metaprogramming. Maybe this sort of scheme (heavy usage of stored procedures and offload almost everything to the DB) doesn't work for everyone, but I like databases and it works for me.
  - titanomachy 10y ago
oliwarner 10y ago
What reading articles on how bad ORMs are has taught me: More people need to use Django. Django does a lot and does it well.
- collyw 10y ago
  I wouldn't say the ORM is Djangos strongest point. Its great for simple stuff, but not great for any moderately complex queries (e.g. extra conditions on a join, subqueries, conditional aggregates - though I think that last one may be solved in the most recent version). I don't think they have done a bad job with the ORM, but you really ought to know SQL as well.
  - jsmeaton 10y ago
    I think the ORM is Djangos strongest point. Take the ORM away and then ModelForms and Admin go with it. I think Django makes little sense if you're not using the ORM. That said, you're absolutely right that it is really bad at certain types of queries. There's no way to really influence joins. Subqueries are coming (https://github.com/django/django/pull/6478 https://github.com/django/django/pull/6478), and conditional aggregates became possible in Django 1.8 with Expressions support (which I authored). Queries that map over many to many relations that have .exclude() or .annotate() generally produce incorrect results. Most of the problems with the ORM are as a result of Django handling joins for the user - which is also one of its biggest strengths. You should absolutely know SQL if you're using an ORM. If you're doing anything other than basic CRUD, you have to know SQL. An ORM should give you an escape hatch to write that custom SQL. Django's ORM does not save you from all the problems mentioned in the article.
  - oliwarner 10y ago
    I'm not arguing that people shouldn't learn SQL, I'm just saying that a lot of people who bemoan all ORMs should try using a really good one —like Django's— and see if all their complaints still ring true. Your examples, for example, might have been true a while ago but: - Extra conditions on a join (eg a prefetch filter): https://docs.djangoproject.com/en/1.9/ref/models/querysets/#django.db.models.Prefetch https://docs.djangoproject.com/en/1.9/ref/models/querysets/#... - I'm not sure what you think you'd want explicit subqueries for, at least in a context that annotations, prefetch, etc don't already do. If it's that weird, just use `.extra(..)` - Conditional aggregates: https://docs.djangoproject.com/en/1.9/ref/models/conditional-expressions/ https://docs.djangoproject.com/en/1.9/ref/models/conditional... - Not that you asked but also have a look at the new database functions stuff. http://stackoverflow.com/questions/38017076/annotate-a-comma-separated-list-of-related-items-onto-django-queryset http://stackoverflow.com/questions/38017076/annotate-a-comma... And it keeps getting better. The list of "ORMs are rubbish because I can't do X" is evaporating. Yeah there's probably some stuff that never comes up but you can still run raw SQL through the ORM. When you consider the development time saved and performance improvements (caching, good query writing, etc) an ORM delivers, I personally think it's hard to argue against them.
abraae 10y ago
I find ORMs great in some scenarios. Take a really thick client, like say a graphical diagramming tool that makes your machine's fan whir loudly when you start it up. Here an ORM can be a great tool to efficiently manage a constantly evolving, large cache of your hot objects, and keep them syched with the backing RDBMS. Big batch programs can be another good use case. Server side web apps and API servers though are the opposite of this. Web pages and API responses should be fast and small, so we don't normally build up a big cache, and in a stateless architecture we are normally throwing the cache away at the end of each request. In this case raw SQL is often easier than work with.
gregopet 10y ago
I've come to love jOOQ, the Java library that I can write type-safe pure SQL in (and with code that is immediately understandable to anyone who knows SQL). It provides mechanisms to smooth the clash of Java/SQL worlds, but otherwise never assumes it is smarter than the wisdom accumulated through decades of database usage. I've come to appreciate many aspects of databases again that tools like Hibernate try to hide from me. http://www.jooq.org/ http://www.jooq.org/ I do still use Hibernate, probably because my usual framework of choice makes it so easy to, but anything above medium complexity goes through jOOQ nowadays.
- _pmf_ 10y ago
  I was intrigued by jOOQ, but the huge amount of generated code for internal metadata of the DB was a bit offputting. I don't want 4MB of generated classes for 3 very small tables.
  - lukaseder 10y ago
    Use the code generator's <inputSchema> or <excludes> flag or other means and you'll only get those 3 small tables generated...
    - _pmf_ 10y ago
      Ah, thank you, this does the trick; I first tried to use my database name as inputSchema, but I had to use "public" in PG.
      - lukaseder 10y ago
        I see, yes. The database corresponds to jOOQ's Catalog, not the Schema (except in MySQL, which mixes up these concepts)
dkarapetyan 10y ago
Never seen a large enough project that relies on an ORM be anything other than a giant mess. I mean never. The conclusion is correct. From an application perspective the db is just another API and should be treated that way and the ORM should just be thought of as a convenient DSL for creating queries on top of that API.
- kennell 10y ago
  What do you define as "large enough"? There have been a ton of fairly big Ruby on Rails apps written over the last few years, the majority of them using ActiveRecord to handle everything.
  - dkarapetyan 10y ago
    Really? How many of those backends have you seen and worked on? Heroic effort does not qualify as success in my book. You can maintain any nightmare by just throwing enough bodies at it. What counts is the maintenance burden and the cost of change.
- rhizome 10y ago
  It strikes me as a thick/thin client tug of war. You can supplant ORMs by e.g. moving your logic into stored procedures on the db side, until you find an inconvenience there, then back and forth until the end of time. As with many dyadic architectural choices, there are good arguments on both sides.
  - dkarapetyan 10y ago
    I agree. The problem is that striking that balance is hard. If you go all-in with an ORM then trying to backpedal and figure out at which point you went wrong becomes almost impossible and so now you have a tangled web of ORM queries and no clear path to untangling things.
    - rhizome 10y ago
      Sure, but the balance is entirely context-dependent. But hey, I guess this is where all polemics fall down. That said, where is the academic problem in refactoring into the db and decomposing ORM queries into straight SQL where necessary/desired? I'm not a Fortune 500 Systems Architect, but I'm under the impression that this is a fairly straightforward process. Now, management of the systems will necessarily incur extra complexity, but that's a second order effect that should be assessed in the decision process as part of the cost estimations. Extra technical debt, for sure. So, choosing one is technically cheaper and more simple and maintainable. Choosing multiple may be faster (or, in many cases, "academically satisfying"), but more complex with a steeper learning curve. Be sure to hire the best of the best!
SchizoDuckie 10y ago
I, like many other people, also took a stab at my ORM / ActiveRecord lib (First for PHP on MySQL, later ported it to Javascript on WebSQL) and I've been following exactly the sentiment that a people here seem to have to: Don't make your ORM want to do everything. If you are going to need computed N+1 queries, write a View, or use custom SQL. ORM / ActiveRecord is a great pattern imo, but if you want to make it do everything, like so many other techniques, you're gonna end up with a behemoth of a thing. I've tried many different ORMs in the last couple of years, and while mine may not be the most complete or have a sexy API for joining custom query parameters into the results, I still feel my own provides the one and only syntax / workflow 'as it should be' in Javascript: var serie = new Serie(); serie.name = 'Arrow'; serie.TVDB_ID = '257655'; serie.Persist().then(function(result) { console.log("Serie persisted! ", result); }); I've got loads of examples with jsfiddles if you want to find out more :-) https://github.com/SchizoDuckie/CreateReadUpdateDelete.js https://github.com/SchizoDuckie/CreateReadUpdateDelete.js (browser that supports WebSQL required, obviously)
brightball 10y ago
As much as I advocate for properly using SQL, I still want an ORM (lightweight) to manage the repetitious small stuff that happens repeatedly throughout an application. I'm all about custom SQL or stored procedures when the time comes though.
kuharich 10y ago
Previous discussion: https://news.ycombinator.com/item?id=8133835 https://news.ycombinator.com/item?id=8133835
stana 10y ago
One important benefit of using an ORM is not being locked in with a particular DB server tech. Good luck migrating large app not using ORM, from, say, MySQL to Postgres if, for example, Postgres replication is something you are all of sudden interested in.
- collyw 10y ago
  Has anyone ever actually migrated a database on a non trivial application? I want to go from MySQl to Postgres, but I am not sure if its worth the risk / effort. We got told about the standard nature of SQL in University, and how you could swap engines 'easily'. Then we started using them in real life and the reality of that situation hit.
sopooneo 10y ago
I expect I am approaching things improperly, but the only time I end up fighting ORM's is when I am trying to creation complex aggregation queries, or other responses used on "reporting" pages.
gautamdivgi 10y ago
Oh finally!!! I've typically had a ambivalent relationship with ORMs. The mass of config files and auto generated code is a huge pain - especially when things go wrong. Doing multiple DBs can also be an issue and requires messing around with config/annotations, what not... Just write the SQL :) - relational algebra is not that hard...
booleanbetrayal 10y ago
The trick to using ORM is being smarter than it, so that you can ultimately rewrite everything it once did for you incredibly inefficiently.
aidos 10y ago
I'm a bit surprised by these criticisms from someone using sqlalchemy. I had the same opinions of orms in the past until I learnt sqlalchemy. Partial objects, attribute creep, and foreign keys: defer the loading of columns [1] to suit your use-case. You can even have different mappings that deal with different subsets of columns depending on your situation. Data retrieval: sqlalchemy is so good for querying that I've mostly stopped using sql for anything other than complex exploratory analysis work. Sure, you need to know sql to be effective, but whatevs - eg Window functions? Done [2] Dual schema dangers: I understand the concern, but again, you can choose to map this as you please and I don't see what doing raw sql gains you here. You need to change the schema, if that affects your application, you need to change your application, if it doesn't, do you need to change your orm code? I routinely migrate my db and release changes to the orm code later. Identities: I admit there are a couple of times I need to flush in my app outside of the normal lifecycle, which I don't like. With sqlalchemy, for the most part, you just connect objects and don't worry about the ids. Transactions: Whatever happens you'll need some sort of transaction boundary in your code. Removing the orm doesn't gain you anything there, does it? I've written about some of this before on here so I won't rehash it https://news.ycombinator.com/item?id=9180831 https://news.ycombinator.com/item?id=9180831 There's a time and a place for sql, orms and storedprocs. The more you know about each of them, the more effective you can be. As ever, learn the tools and never throw the baby out with the bathwater. [1] http://docs.sqlalchemy.org/en/latest/orm/loading_columns.html http://docs.sqlalchemy.org/en/latest/orm/loading_columns.htm... [2] http://docs.sqlalchemy.org/en/latest/core/tutorial.html#window-functions http://docs.sqlalchemy.org/en/latest/core/tutorial.html#wind...
kayman 10y ago
Use the best tool for the job. ORM's might do it 80% of the time. The other 20%, most of the mature ORMs provide a way to execute your own sql.
JelteF 10y ago
For me the time I loved SQLAlchemy the most was the time when I migrated my database from MySQL to Postgres. Almost all of the issues people were warning me about did not matter as SQLAlchemy caught them automatically.
Waterluvian 10y ago
What django gave us out of the box would have been impossible with SQL in the same timeframe with the resources we had. If we had the luxury to do things properly and fully the first time, we never would have made it to market with our robots. This isn't to say that one day we won't throw away django or use more SQL. We just had real world schedules to deal with.
eternalban 10y ago
Domain objects in the middle tier only make sense if there is pervasive reuse of component objects. I extensively chased the unicorn of component orientation but it never pans out. The logical place for domain objects is in the Database. Yes, stored procedures, with object layer mappings of views. But the reality of the practice is that (a) the toolchain for the DB backend is quite firmly stuck in 80s and (b) most IT programmers lack SQL skills.
manigandham 10y ago
Not this again. Why is this coming up at all in 2016? There isn't even a valid debate here. ORMs are a tool, that's it. The relational operations of SQL and the object-oriented (or functional) logic of your application code are usually very different and it's nice to have a mapper that lets you interact with your app's language while it takes cares of automatically mapping it to SQL. For 99% of database ops where it's pulling some stuff out, editing some fields and then saving it back, an ORM will save you a massive amount of time with better safety, security and performance. Even complicated queries/mappings/procedures run pretty well and you can always write raw SQL when you need it. Modern ORMs will even let you execute custom SQL and still give you conveniences like easy parameterization and hydrating the results back as objects. Maybe I've been spoiled with the .NET ecosystem with great ORMs like NHibernate, EntityFramework, and Dapper (along with C# features like Linq) but it seems like most of these complaints are from people who use shitty ORMs or can't comprehend that it's just a tool that they can choose to use or skip, at very granular method by method level. There's absolutely no reason for any extreme here.
- CuriousSkeptic 10y ago
  My experience with hibernate, entity framework, nhibernate, and lastly dapper has basically left me thinking Dapper is all you'll ever want. I can't imagine a use case where I'd rather opt for NHibernate or EF at the moment. (I might add, Dapper in combination with C#6 even gives me enough type saftey to be happy. String interpolation and the nameof operator complements dappers DTO approach nicely) Since you seem to have similar experiences Im curious. When would you ditch dapper for the others?
  - pbz 10y ago
    Type safety (no strings) and just as fast as Dapper (actually faster in some cases): https://github.com/linq2db/linq2db https://github.com/linq2db/linq2db
  - mistermann 10y ago
    Do you use a Dapper extension for populating and persisting entities? (Last I looked at it as I recall this wasn't default functionality.)
intrasight 10y ago
There are two types of ORMs 1. ORMs for SQL dummies 2. ORMs for SQL gurus You really need to be clear, when discussing ORMs, which type you are discussing.
raspasov 10y ago
(= 'ORM 'OMG) #Clojure
kefka_p 10y ago
Enterprise Objects Framework just worked. Pretty much every other ORM I've worked with has been terrible by comparison.
dools 10y ago
I came to the same conclusion, and wrote this ORM for SQL lovers: https://github.com/iaindooley/PluSQL https://github.com/iaindooley/PluSQL
joostdevries 10y ago
I prefer to use functional frameworks. They 1) express the sql with map, flatmap, filter, groupBy etc functions 2) use classes that are equivalent to tuples. Since I develop in Scala both are very intuïtive to write. Apparently Linq for F# is really good as well. As someone else mentioned in a comment. I get a programming interface that doesn't attempt to hide sql and still my code is fully typed. And it's really easy to make your query logic modular: make the sql fragment a function that you can call. Like any other code you reuse. I can't think of an advantage of ORM over this functional approach. Apart from ORM being more well known.
nodamage 10y ago
Just once I'd like to see someone write an opinion about ORMs without resorting to sweeping generalizations ("Vietnam of computer science") or making assumptions about how they are being used ("it wasn't a good fit for my use case so therefore it must never be"). Maybe, just maybe, it's possible that ORMs are useful for solving certain types of problems and less suitable for other types. If you work on web stuff or applications that are report-oriented where most of the time you're just fetching data (possibly with complex queries) and rendering it to display, then maybe ORMs aren't a good fit. On the other hand if you work on client-side apps where your objects are backed by a database but are otherwise long-lived, then sometimes the other features (beyond SQL generation) that ORMs provide come in handy (tracking units of work over the object graph, maintaining an identity map for consistent access to objects, and providing change notifications when an object or collection of objects is manipulated). If you've never needed any of these features that's perfectly fine. I've never to needed to use a bulldozer either. But I'm not going around declaring bulldozers useless just because I've only ever needed to use a shovel.
- notyourwork 10y ago
  Is this not the general sentiment of any piece of software? It was implemented to serve a purpose, if that purpose is not sufficing a goal of yours it is not relevant to you.
  - nodamage 10y ago
    Indeed. It would be nice though, if people, having realized that a particular tool isn't useful for their particular goals, could still acknowledge that it could be useful in other situations, instead of blaming the tool itself.
jeffdavis 10y ago
Using an ORM is for advanced users, and I think it's a bad default for beginners. A good one certainly saves time when you know how to use it, but it also adds complexity and narrows the perception of what a database can do. Narrowing the perspective is especially bad for beginners, because some options will simply never occur to them and the ORM will prevent learning by osmosis. Let's say you need reliable, efficient cache invalidation (say, to re-render a static page when the data changes). Triggers and LISTEN/NOTIFY in postgres might save a huge amount of time (and even more in maintenance costs) over a custom solution that probably isn't right anyway. Or maybe you need to prevent schedule conflicts. Postgres offers range types and exclusion constraints, which aren't supported in all ORMs. Or to keep a remote system reliably in sync (even if it's a very different kind of system), logical decoding is a very powerful feature. But how would you encounter any of these potential solutions if you are always behind an ORM?
Pawka 10y ago
Haven't red all comments and maybe somebody already mentioned this, but here are my few cents: I've worked with multiple ORM frameworks during my career. And I always reached the point where developer needs focus to framework internals instead of delivering new features. Yes, usually most situation which developer didn't expected can be solved by configuring ORM framework, changing some configuration, providing additional parameters or calling some methods, but what that means? You need fully understand your framework if you want to be sure you application does exactly what you want and not misbehave. So next time when you'll be thinking grab ORM to make things simpler, ask yourself if you really have enough time to fully learn the framework?
khorpy 10y ago
When considering using ORM we need to answer simple questions: does it help to decrease code size? In most cases ORM code is same size as SQL query. Secondly, does it hide complexity? No, it just adds up one more layer that in fact increases complexity and makes it harder to debug. Finally, does it protect us from errors or impoves code quality? This is rarely the case. Instead of using ORM I prefer moving data retrieval code to database layer with help of views. There is also updatable views that we can use to simplify database updates on code side and sometimes avoid using transactions. Separation of code and data logic is great concern when deciding to implement ORM. Nowdays database are very smart and convinient so there is no need to use ORM.
- collyw 10y ago
  In most cases its smaller (in the Django ORM anyway). myobject, created = MyObject.objects.get_or_create(field1='1' , field2=field2) That would take a number of lines. One query to check if the object exists, another to create it / retrieve it, plus application code to deal with that SQL.
  - khorpy 10y ago
    Ok, for this particular example, is operation wrapped in transaction? How will code evalute if we need to check some condition on field2 and occasionally update it?
hitr 10y ago
If you dont want to deal with quirks of ORM,use a lightweight microORM. You basically get some productivity, simple type checking,consistent way to access data , sql injection protection and you will deal with hand rolled sql queries.
lukaseder 10y ago
Use ORMs for complex CRUD. Use SQL for complex queries.
vbezhenar 10y ago
Hibernate is a very complex library. SQL+JDBC on the other hand is easy (JDBC is tedious, some complex SQL might be hard, but generally it's easy). On the other hand Hibernate could significantly reduce development time, if developers know it and application is a goot fit (CRUD, filters). My advice is to avoid ORMs unless your project is big and its database schema is a good fit. And if it is, don't think, that ORM is easy, it's not. Learn how it works, read its source code, understand its inner workings. Or find someone who does. I didn't have much experience with other ORMs, though.
pwilsony 10y ago
"quasi-professional cowboy" here. I have used both ORM and direct SQL systems with the problems outlined above. I finally gave up and wrote my own row mapper: 1. Using the DB Schema, generate stored procedures to load and save data together with code/validation in partial C# classes. 2. Transport data in XML format using single letter (or two) table/element and column/attribute names (auto generated). Use .Net to automatically build/ serialize the XML into objects. SQL Server/.Net have some nice XML features that make this simple. This means that the only user access to the database is through authorized stored procedures. The users have no access to database views or tables. I have implemented the means to load and save arbitrary hierarchical objects (e.g. an Order/Order Lines). A single request to the database can return a complex three level object which would otherwise take hundreds to round trips to load. I agree with the observation that this would be hell to maintain. However, the people likely to maintain this system would be in just a different hell if they had to work with Hibernate or MS Entity Data Model.

379 points by bsg75 10y ago

ORMs tend to assume that there is "the application" with "its database". If the application changes, so does the database. If the data is used by more than one application, it's better to have the data defined in the database and write applications as database clients.

vrdabomb5717 10y ago
At the big tech firm I work at, there's a best practice where any database (whether that's a traditional RDBMS or a NoSQL client) is abstracted away by a microservice with a defined API, and every other application that wants to get that data needs to interact with the microservice. That way, the database schema can change without it affecting multiple applications. There's still the traditional mismatch between ORM and database, but doesn't feel as painful because only a single application is using that data, and that application can have special knowledge of the persistence layer underneath (meaning, use database hints if necessary, defer to raw SQL, etc.)
- prodigal_erik 10y ago
  We have decades of research into filtering, joining, and aggregating across a complex set of tables and views. With microservices you have to roll your own query planning and stream all the intermediate results on the wire even when you're throwing away most of them.
  - collyw 10y ago
    It does sound like an extra layer of unnecessary complexity to solve a problem that could be solved in other ways.
  - intrasight 10y ago
    That probably is true - if you have a crappy API design
  - elcritch 10y ago
    Unfortunately, SQL servers aren't generally scalable. Offloading joins and aggregation onto (inexpensive) app servers can increase over all system performance, despite lack of advanced query planning.
    - collyw 10y ago
      Bollocks. SQL scales fine unless you are stupid in the way you use it. All the crap performance I have seen in the last couple of years have been from doing crap like you suggest - doing joins and aggregation at the application level. Our front page currently makes over 1000 database calls because of this sort of nonsense.
  - lmm 10y ago
    The thing is, letting the system handle it isn't good enough. We have decades of research into this stuff, so 90% of the time the database gets it right - but the interface for explicitly taking control when the DB gets it wrong is black magic at best. The value of having an exposed representation of filtering/joining/aggregation in a general-purpose programming language where you can interact with it is big enough to outweigh that of a database that can do it for you most of the time.
- abraae 10y ago
  That kind of sounds like a microservices anti-pattern in disguise. Its thought by many to be bad practice for microservices to share a common database. But that's pretty much what you're doing, but just with an API instead of jdbc for the access.
  - lmm 10y ago
    An API is much easier to version-control, automatically check for compatibility and so on.
VLM 10y ago
I've slathered in yet another confusing layer by creating an application specific view. Now you've got the ORM layer, which possibly translates to SQL correctly, to talk to a view, which is a front on the real untouched table. Its kind of a hassle.
collyw 10y ago
Ok, I have only used Django's ORM and a little bit of SQLalchemy, but in both cases you are defining the database in a Python file. Django's models.py file usually maps directly to database tables, and is less verbose than writing SQL create statements. Maybe I don't see any advantage in what your doing, as I treat the database model as the most important part of the application (or maybe thats the same as what you are saying). Get that correct, and code falls into place easily. Start hacking rules in at the application level and eventually it gets messy. Linux said something similar about bad programmers worrying about the code, and good ones worrying about the data and its relationships.
collyw 10y ago
Ok, I have only used Django's ORM and a little bit of SQLalchemy, but in both cases you are defining the database in a Python file. Django's models.py file maps directly to database tables, and is less verbose than writing SQL create statements. Maybe I don't see any advantage in what your doing, as I treat the database model as the most important part of the application. Get that correct, and code falls into place easily. Start hacking rules in at the application level and eventually things start to get messy. Linux said something similar about bad coders worrying about the code, and good ones worrying about the data and their relationships.

vinceguidry 10y ago

The thing I love about ActiveRecord is that it makes it easy to, anywhere you want, and at any level of the abstraction stack you want, to just toss in SQL fragments. This gets you the best of both worlds, the fluidity of being able to just define methods on model objects, and the ability to utilize database tech to the fullest. You can take any query and call .to_sql on it and it shows you exactly what it's passing to the database. I prefer Sequel's semantics but ActiveRecord is an excellent workhorse that I have no problems relying on.

sirn 10y ago
But how do you do: SELECT posts.*, (SELECT COUNT(1) FROM comments WHERE post_id = posts.id) AS comments_count FROM posts; In ActiveRecord, without 1+N queries, or caching comments_count in a column somewhere? Admittedly, that was not the best example. The last time I need something more intertwined than a simple COUNT in subquery, the answer was "give up and just use Arel." But at this point it is no longer quite ActiveRecord, but rather a SQL without strings. This is my biggest gripe against the Active Record pattern in general, as it ties its model too tightly to the underlying database. It is convenient for a simple CRUD tasks, which may fit about 90% of use case, but that's not the only thing the database is capable of.
- samwillis 10y ago
  I don't know about active record but with Django it's simply: Post.objects.all().annotate(Count('comments')) Which produces (simplified, Django would actually explicitly select each column): SELECT posts.*, COUNT(comments.id) AS comments_count FROM posts LEFT OUTER JOIN comments ON (posts.id = comments.post_id) GROUP BY posts.id Nice, easy and without 1+N queries.
  - sirn 10y ago
    That is actually very nice and elegant!
- fennecfoxen 10y ago
  I've actually never used ActiveRecord myself, but when an ORM breaks down and it becomes hard to make certain queries I've had good luck with other ORMs by creating a view in the database and telling the ORM that it's a table. Obviously you can't insert into arbitrary views, but most are happy to give you an object back, with appropriate relationships to the actual database-writable objects. I'm surprised there isn't more explicit support for this in more ORMs (things like not having 'save' methods on the ORMed classes).

maxxxxx 10y ago

I think ORMs are a great tool to get something off the ground quickly. Like with most tools you will hit a point where they make things more difficult and then it's probably time to switch to SQL only or mix SQL with ORM especially for performance critical queries. In most applications I have seen the ORM provided a lot of value but there were cases where it needed to be augmented with raw SQL. I never understand why people want one or the other exclusively. Both have their place. I think stored procedures would be very useful if they integrated better with source control and the app code. Maybe we need an ORM for stored procedures that automatically creates stored procedures from the project code?

ams6110 10y ago
It's quite trivial to keep stored procedure code in source control with the app code or in its own repo. I am always mystified when I hear the complaint that this is difficult because in my experience it's no more difficult than managing any other code in a SCM repo.
- jjoonathan 10y ago
  It's not only easy to manage stored procs separately, it has huge advantages: you can play with your SQL in an IDE built for the task with luxuries like real-time error highlighting, profiling, debugging, and auto-complete, and in some cases you can even iterate on your code without restarting your application (just update the proc).
- brightball 10y ago
  My biggest concern has always been around zero downtime deployment with stored procedures. Your database has to work with both the old and new versions of web code during the deploy in case of rollback to make that process work and that always struck me as the weird edge case that makes things tough. If a procedure never changes or remains backward compatible then it should still be fine though.
  - platz 10y ago
    Give the new proc a different name (e.g. append a version number). On the n+1 deployment, clean up the old proc.

chillacy 10y ago

> ORMs are more detriment than benefit The author mentions Hibernate and SQLAlchemy, which are both DataMapper ORMs. But what about Active Record? It's true that AR will provide even more abstraction and distance from the database, but it also provides a lot more convenience, which for me in small CRUD projects (as most are) has been worth the downsides. And as another posted mentioned, you can always optimize by replacing slow AR queries with custom SQL ones, and restructuring your database as your project scales.

tfigment 10y ago

I written a fair number of C# LOB apps and use LINQ quite a bit with mysql. I don't even want to talk about Java and some of its ORMs as its too painful to think about. I agree with the sentiment of the post but in compiled languages I really want an ORM to simplify unpacking result sets. LINQ is great when it works but joins sort of suck as well as calling in-built sql functions and it can some times generate highly unoptimized queries. I frequently pull in data via LINQ to SQL and then massage the data with LINQ for objects where I have better control of performance and operations. Trying to write pure SQL leads to lots of manual unpacking of the result set which I generally dislike and is much harder to maintain and doesn't work well in practice compared to when LINQ actually works. I think maybe what I've really learned is use scripting / loosely coupled type systems when working with SQL. In python, I usually just call sql directly rather than use sqlalchemy and its fine because of the loose typing and result set unpacking isn't terrible.

bunderbunder 10y ago
On C#, Dapper's useful, but not perfect, for letting you write your own queries and then making it easy to unpack the result sets. Unfortunately, it relies on property setters for doing the unpacking, so it doesn't interact super well with your code if you like to avoid unnecessary mutability. The only publicly-available lightweight ORM I know of that does a good job with that is the SQL type provider in F#.Data. That one is head-and-shoulders above any other option I've found for working with databases. It does require that you write your data access layer in F#, though, which may make it a hard one to sell at work.
- tfigment 10y ago
  Thanks. That actually doesn't look that bad. It would be nice to have anonymous type objects but I recognize the difficulty in that. This looks like a nice compromise. Now if they could also fix passing in arrays as part of a parameterized query "select x from y where z in ?" where ? is a collection of strings or integers it would be perfect but I think that is a driver/interface problem. Edit: Looks like it actually does says it does support the enumerable but I'm questioning if it will do what I want. Will have to test out but I'm guessing there is a limit to the size of the array.

Gustek 10y ago

I like to say "ORMs make easy thing easier and hard things harder". What i mean by that is, any simple CRUD operations are much easier in ORM. The hard things, i mean any complex queries that need more than one join you are probably better of writing yourself. In the end i prefer to do inserts, updates and deletes with ORM (or some other database abstraction tools) but most SELECTs i write myself, fetching exactly what i need and mapping result to objects if needed manually.

IndianAstronaut 10y ago
Do uou use metadata from the db driver to do type mappings?
- Gustek 10y ago
  Not sure what you asking about. Do you mean if I define database structure in my code? The answer is: yes. I don't know about any database abstraction library that would provide you with convenience methods to query database with type checking and stuff without giving it the schema.
craigvn 10y ago
I pretty much do the same. The CUD is ORM, and some simple selects in C# Linq, but as soon as the query is a bit more complicated I crack out the SQL. It does not have to be all or nothing.

EToS 10y ago

I love ORMs, they help abstract the database technology which has been really useful in a recent project switching an app from MSSQL to PostgreSQL. I also personally as developer appreciate the programmatic syntax. I would caveat the performance issue, sometimes there is no better way, however i would always try to make things work in the ORM first before switching into native SQL to get the job done.

nommm-nommm 10y ago
> they help abstract the database technology If you are going to do anything even remotely complex you're going to need to know that database technology otherwise your ORM is going to spit out queries that are dead stupid and a performance nightmare (pages taking 1 full minute to load). Then you end up fighting with the ORM to do what you want. That's my experience. I guess there are projects that are so simple the database layer can be abstracted away but I've never worked on one.

YeGoblynQueenne 10y ago

Sigh. Whenever I have to hurt myself at work on such issues, I find myself thinking of the web that could have been, if only... Let me backtrack a bit. There's a much better way to do things than the way we do it right now. A way that completely obviates the need for ORMs, or indeed any way to deal with the interface between application and database. You see, there would be no object-relational Impedance Mismatch (and therefore no need for such clunky kludges like ORMs) if the database and the application were both written in languages of the same paradigm, either both OOP or both relational. Ideally, that would be a single language, that could handle both data and application logic. There is such a language: Prolog [1]. Prolog is implemented as a relational database. So your program itself is the database. There is no separation between data and operation and therefore no ORIM and no need for ORMs, or stored procedures or anything, really. And, yes, it's perfectly possible to do your web dev in Prolog. Here, see this explainer on the Swi-Prolog website, titled "Can I replace a LAMP stack with SWI-Prolog?": http://www.swi-prolog.org/FAQ/PrologLAMP.txt http://www.swi-prolog.org/FAQ/PrologLAMP.txt Hint: Yes. Yes, you can. You can replace LAMP (or LAM-whatever) with an LP stack, where all you need on top of your OS is Prolog itself. Its built-in Definite Clause Grammars notation [2] can be used to parse and generate javascript, html, xml, css, YAML, whatever you like. Swi-Prolog even offers translation to RDF, which is as natural as you can expect given RDF is also a relational language. Here's a big fat tutorial to help you started: www.pathwayslms.com/swipltuts/html/index.html Can this be done for real? OMG yes it can. The Swi-Prolog website itself runs on an LP stack. There's a few more websites that do too: www.pathwayslms.com/swipltuts/html/index.html In short: you don't need to hurt yourself as badly as you 're currently doing it. There's no other need than of course, nobody wants to learn Prolog. I know that, I've made my peace with that and I've spent all of my so-far career hurting myself against the ORIM just like everyone else. ... but we could have had a better web. ___________________ [1] https://en.wikipedia.org/wiki/Prolog https://en.wikipedia.org/wiki/Prolog [2] https://en.wikipedia.org/wiki/Definite_clause_grammar https://en.wikipedia.org/wiki/Definite_clause_grammar

jsmeaton 10y ago
This sounds really interesting, but I'm having a lot of trouble trying to follow how all that code fits together - from what code I could find. I also strongly dislike the object/function based approach to constructing HTML (which - funnily enough, is just like using an ORM to construct SQL which I like heh). Really, I don't see enough offered to counteract what little pain I feel using an ORM. The LP actually looks more painful (to me). Obviously I'm totally unfamiliar with this stack which makes it hard for me to see where the benefits lie.

vladsanchez 10y ago

I've always said: "ORMs are for people who don't know SQL!"

pixie_ 10y ago
ORMs are not for people who don't know SQL. The secret to using an ORM is knowing exactly the SQL it will generate underneath. The advantage to using one is static typing as well as writing orders or magnitude less code than SQL. As well as orders of magnitude more understandable code than the equivalent SQL. If you work on heavy enterprise applications the ORM can be your best friend or worst enemy. It comes down to knowing the tool you're using inside and out. It will make you much more productive in the long run. And sometimes yea, you need straight SQL or a stored proc to get the job done. That's OK.
bdcravens 10y ago
I began programming in languages where SQL was a requirement in 1999; only in the past 3-4 years have I use ORMs. I often write a lot of raw SQL, but I find a well written ORM statement communicates intent more quickly.
spriggan3 10y ago
> I've always said: "ORMs are for people who don't know SQL!" False. To use an ORM you must know and understand data modelling and the Entity/Relationship model. I'd say, people who don't like ORMs are often those who don't understand that model. If you do understand it, then you know that relationships can be represented 100% by code in an OO language, thus completely automated. Why bother writing your own hydrators for each entity when you can model them directly in a library? it doesn't make a codebase more maintainable or readable. Furthermore you could have an ORM that uses SQL directly for queries, and still eagerly resolve relationships. It's just not practical, that's why ORMs often come query builders. That is the thing that abstracts SQL, not an ORM per say. ORMs only deal with relationships between entities. Query Builders build SQL queries.
mb_72 10y ago
This doesn't ring true for me, at least - I've been programming professionally (using SQL) since the mid-90s, and these days very much appreciate the time-savings that ORMs (on Windows, for the most part) provide. With my favourite ORM - XPO from Developer Express - there is a decent tool for monitoring queries and sessions, and if I need to I can write custom SQL to improve performance (which almost never happens). Another important point is that it's, IMO, much easier to maintain ORM-based code, and another programmer coming along after me can more easily get started on understanding what I've implemented. Once place I worked in the past we had a 'SQL guru' who wrote these massively complicated SQL queries that could be understood by no-one by him.

ccleve 10y ago

ORMs can alleviate a lot of tedium, until you get to a level of complexity where they start to create it. I created my own ORM-like access library which seems to work pretty well. https://github.com/dieselpoint/norm https://github.com/dieselpoint/norm Comments welcome.

Grue3 10y ago

SQL is a write-only language. You better hope you don't have to rewrite your complicated raw SQL queries in the future.

bdcravens 10y ago
You can write well written modular SQL that supports changes and can be read by a competent developer.
- Grue3 10y ago
  Not in my experience. Please show me actual examples of "well-written modular SQL" in a real application.
  - Something1234 10y ago
    Would modular sql be wrapping up subqueries inside of views, and then selecting from those views?
    - jsmeaton 10y ago
      Common Table Expressions (CTEs) help with modular SQL.
  - bdcravens 10y ago
    I'm taking a bit of creative license with the term "modular". CTEs and views can help with this, and are closer to true modules. I like to encapsulate inside of stored procedures, and write my code in such a way that it reads almost like a a procedural language. Heavy use of indentation, subqueries, and temp tables lets you carry results through the proc. I like to hang conditions off of joins, so related logic is in the same place.
- maxxxxx 10y ago
  If you do SQL full-time probably yes. The problem is that most developers don't work enough with it so their skills are lacking.

bluejekyll 10y ago

I'm curious how the author would like MyBatis, which is just a thin wrapper for calling SQL, and then translating results to POJOs. I like it, and it, IMO, keeps the API distinct from the access in the DB. You have to write your own SQL, which is good and bad. Huge amounts of control and performance, but higher portability costs.

pbreit 10y ago
Also Records for Python: https://github.com/kennethreitz/records https://github.com/kennethreitz/records

StevePerkins 10y ago

Ten years ago, there was a blog post every other week bemoaning ORM's. Ten years ago, those posts often had merit. In 2016, this sentiment is outdated. A few points: 1. If you think that using an ORM means you don't have to learn SQL, then you're going to have a bad time. This is where most of the bad press originates... from people who never really learned SQL or their chosen ORM. An ORM provides type checking at your application layer, and typically better performance (unless you plan on hand-rolling your own multi-level cache system). But you still must understand relational database fundamentals. 2. If you're not using an ORM, then you ultimately end up writing one. And doing a far worse job than the people who focus on that for a living. It's no different from people who "don't need a web framework", and then go on to re-implement half of Rails or Spring (without accounting for any CSRF protection). Learning any framework at a professional level is a serious time investment, and many student beginners or quasi-professional cowboys don't want to do that. So they act like their hand-rolled crap is a badge of honor. 3. Ten years ago, it was a valid complaint that ORM's made custom or complex SQL impossible, didn't play well with stored procedures, etc. But it's 2016 now, and this is as obsolete as criticizing Java by pointing to the EJB 2.x spec. I can't think of a single major ORM framework today that doesn't make it easy to drop down to custom SQL when necessary.

PeCaN 10y ago
> If you're not using an ORM, then you ultimately end up writing one. I disagree with this. A lot of things people use ORMs for are rather easily solved with stored procedures, especially in Postgres where you can write stored procedures in Perl, Ruby, etc. Validations, “fat models”, etc are all managed with SQL easily (and this means you get that functionality from _anywhere you access the database_, not just from your framework with an ORM). For convenient access you can roll 30 lines of Perl to wrap DBI or whatever (I use Perl for most web backends these days) and call your stored procedures in normal syntax with a little metaprogramming. Maybe this sort of scheme (heavy usage of stored procedures and offload almost everything to the DB) doesn't work for everyone, but I like databases and it works for me.
- titanomachy 10y ago

oliwarner 10y ago

What reading articles on how bad ORMs are has taught me: More people need to use Django. Django does a lot and does it well.

collyw 10y ago
I wouldn't say the ORM is Djangos strongest point. Its great for simple stuff, but not great for any moderately complex queries (e.g. extra conditions on a join, subqueries, conditional aggregates - though I think that last one may be solved in the most recent version). I don't think they have done a bad job with the ORM, but you really ought to know SQL as well.
- jsmeaton 10y ago
  I think the ORM is Djangos strongest point. Take the ORM away and then ModelForms and Admin go with it. I think Django makes little sense if you're not using the ORM. That said, you're absolutely right that it is really bad at certain types of queries. There's no way to really influence joins. Subqueries are coming (https://github.com/django/django/pull/6478 https://github.com/django/django/pull/6478), and conditional aggregates became possible in Django 1.8 with Expressions support (which I authored). Queries that map over many to many relations that have .exclude() or .annotate() generally produce incorrect results. Most of the problems with the ORM are as a result of Django handling joins for the user - which is also one of its biggest strengths. You should absolutely know SQL if you're using an ORM. If you're doing anything other than basic CRUD, you have to know SQL. An ORM should give you an escape hatch to write that custom SQL. Django's ORM does not save you from all the problems mentioned in the article.
- oliwarner 10y ago
  I'm not arguing that people shouldn't learn SQL, I'm just saying that a lot of people who bemoan all ORMs should try using a really good one —like Django's— and see if all their complaints still ring true. Your examples, for example, might have been true a while ago but: - Extra conditions on a join (eg a prefetch filter): https://docs.djangoproject.com/en/1.9/ref/models/querysets/#django.db.models.Prefetch https://docs.djangoproject.com/en/1.9/ref/models/querysets/#... - I'm not sure what you think you'd want explicit subqueries for, at least in a context that annotations, prefetch, etc don't already do. If it's that weird, just use `.extra(..)` - Conditional aggregates: https://docs.djangoproject.com/en/1.9/ref/models/conditional-expressions/ https://docs.djangoproject.com/en/1.9/ref/models/conditional... - Not that you asked but also have a look at the new database functions stuff. http://stackoverflow.com/questions/38017076/annotate-a-comma-separated-list-of-related-items-onto-django-queryset http://stackoverflow.com/questions/38017076/annotate-a-comma... And it keeps getting better. The list of "ORMs are rubbish because I can't do X" is evaporating. Yeah there's probably some stuff that never comes up but you can still run raw SQL through the ORM. When you consider the development time saved and performance improvements (caching, good query writing, etc) an ORM delivers, I personally think it's hard to argue against them.

abraae 10y ago

I find ORMs great in some scenarios. Take a really thick client, like say a graphical diagramming tool that makes your machine's fan whir loudly when you start it up. Here an ORM can be a great tool to efficiently manage a constantly evolving, large cache of your hot objects, and keep them syched with the backing RDBMS. Big batch programs can be another good use case. Server side web apps and API servers though are the opposite of this. Web pages and API responses should be fast and small, so we don't normally build up a big cache, and in a stateless architecture we are normally throwing the cache away at the end of each request. In this case raw SQL is often easier than work with.

gregopet 10y ago

I've come to love jOOQ, the Java library that I can write type-safe pure SQL in (and with code that is immediately understandable to anyone who knows SQL). It provides mechanisms to smooth the clash of Java/SQL worlds, but otherwise never assumes it is smarter than the wisdom accumulated through decades of database usage. I've come to appreciate many aspects of databases again that tools like Hibernate try to hide from me. http://www.jooq.org/ http://www.jooq.org/ I do still use Hibernate, probably because my usual framework of choice makes it so easy to, but anything above medium complexity goes through jOOQ nowadays.

_pmf_ 10y ago
I was intrigued by jOOQ, but the huge amount of generated code for internal metadata of the DB was a bit offputting. I don't want 4MB of generated classes for 3 very small tables.
- lukaseder 10y ago
  Use the code generator's <inputSchema> or <excludes> flag or other means and you'll only get those 3 small tables generated...
  - _pmf_ 10y ago
    Ah, thank you, this does the trick; I first tried to use my database name as inputSchema, but I had to use "public" in PG.
    - lukaseder 10y ago
      I see, yes. The database corresponds to jOOQ's Catalog, not the Schema (except in MySQL, which mixes up these concepts)

dkarapetyan 10y ago

Never seen a large enough project that relies on an ORM be anything other than a giant mess. I mean never. The conclusion is correct. From an application perspective the db is just another API and should be treated that way and the ORM should just be thought of as a convenient DSL for creating queries on top of that API.

kennell 10y ago
What do you define as "large enough"? There have been a ton of fairly big Ruby on Rails apps written over the last few years, the majority of them using ActiveRecord to handle everything.
- dkarapetyan 10y ago
  Really? How many of those backends have you seen and worked on? Heroic effort does not qualify as success in my book. You can maintain any nightmare by just throwing enough bodies at it. What counts is the maintenance burden and the cost of change.
rhizome 10y ago
It strikes me as a thick/thin client tug of war. You can supplant ORMs by e.g. moving your logic into stored procedures on the db side, until you find an inconvenience there, then back and forth until the end of time. As with many dyadic architectural choices, there are good arguments on both sides.
- dkarapetyan 10y ago
  I agree. The problem is that striking that balance is hard. If you go all-in with an ORM then trying to backpedal and figure out at which point you went wrong becomes almost impossible and so now you have a tangled web of ORM queries and no clear path to untangling things.
  - rhizome 10y ago
    Sure, but the balance is entirely context-dependent. But hey, I guess this is where all polemics fall down. That said, where is the academic problem in refactoring into the db and decomposing ORM queries into straight SQL where necessary/desired? I'm not a Fortune 500 Systems Architect, but I'm under the impression that this is a fairly straightforward process. Now, management of the systems will necessarily incur extra complexity, but that's a second order effect that should be assessed in the decision process as part of the cost estimations. Extra technical debt, for sure. So, choosing one is technically cheaper and more simple and maintainable. Choosing multiple may be faster (or, in many cases, "academically satisfying"), but more complex with a steeper learning curve. Be sure to hire the best of the best!

SchizoDuckie 10y ago

I, like many other people, also took a stab at my ORM / ActiveRecord lib (First for PHP on MySQL, later ported it to Javascript on WebSQL) and I've been following exactly the sentiment that a people here seem to have to: Don't make your ORM want to do everything. If you are going to need computed N+1 queries, write a View, or use custom SQL. ORM / ActiveRecord is a great pattern imo, but if you want to make it do everything, like so many other techniques, you're gonna end up with a behemoth of a thing. I've tried many different ORMs in the last couple of years, and while mine may not be the most complete or have a sexy API for joining custom query parameters into the results, I still feel my own provides the one and only syntax / workflow 'as it should be' in Javascript: var serie = new Serie(); serie.name = 'Arrow'; serie.TVDB_ID = '257655'; serie.Persist().then(function(result) { console.log("Serie persisted! ", result); }); I've got loads of examples with jsfiddles if you want to find out more :-) https://github.com/SchizoDuckie/CreateReadUpdateDelete.js https://github.com/SchizoDuckie/CreateReadUpdateDelete.js (browser that supports WebSQL required, obviously)

brightball 10y ago

As much as I advocate for properly using SQL, I still want an ORM (lightweight) to manage the repetitious small stuff that happens repeatedly throughout an application. I'm all about custom SQL or stored procedures when the time comes though.

kuharich 10y ago

Previous discussion: https://news.ycombinator.com/item?id=8133835 https://news.ycombinator.com/item?id=8133835

stana 10y ago

One important benefit of using an ORM is not being locked in with a particular DB server tech. Good luck migrating large app not using ORM, from, say, MySQL to Postgres if, for example, Postgres replication is something you are all of sudden interested in.

collyw 10y ago
Has anyone ever actually migrated a database on a non trivial application? I want to go from MySQl to Postgres, but I am not sure if its worth the risk / effort. We got told about the standard nature of SQL in University, and how you could swap engines 'easily'. Then we started using them in real life and the reality of that situation hit.

sopooneo 10y ago

I expect I am approaching things improperly, but the only time I end up fighting ORM's is when I am trying to creation complex aggregation queries, or other responses used on "reporting" pages.

gautamdivgi 10y ago

Oh finally!!! I've typically had a ambivalent relationship with ORMs. The mass of config files and auto generated code is a huge pain - especially when things go wrong. Doing multiple DBs can also be an issue and requires messing around with config/annotations, what not... Just write the SQL :) - relational algebra is not that hard...

booleanbetrayal 10y ago

The trick to using ORM is being smarter than it, so that you can ultimately rewrite everything it once did for you incredibly inefficiently.

aidos 10y ago

I'm a bit surprised by these criticisms from someone using sqlalchemy. I had the same opinions of orms in the past until I learnt sqlalchemy. Partial objects, attribute creep, and foreign keys: defer the loading of columns [1] to suit your use-case. You can even have different mappings that deal with different subsets of columns depending on your situation. Data retrieval: sqlalchemy is so good for querying that I've mostly stopped using sql for anything other than complex exploratory analysis work. Sure, you need to know sql to be effective, but whatevs - eg Window functions? Done [2] Dual schema dangers: I understand the concern, but again, you can choose to map this as you please and I don't see what doing raw sql gains you here. You need to change the schema, if that affects your application, you need to change your application, if it doesn't, do you need to change your orm code? I routinely migrate my db and release changes to the orm code later. Identities: I admit there are a couple of times I need to flush in my app outside of the normal lifecycle, which I don't like. With sqlalchemy, for the most part, you just connect objects and don't worry about the ids. Transactions: Whatever happens you'll need some sort of transaction boundary in your code. Removing the orm doesn't gain you anything there, does it? I've written about some of this before on here so I won't rehash it https://news.ycombinator.com/item?id=9180831 https://news.ycombinator.com/item?id=9180831 There's a time and a place for sql, orms and storedprocs. The more you know about each of them, the more effective you can be. As ever, learn the tools and never throw the baby out with the bathwater. [1] http://docs.sqlalchemy.org/en/latest/orm/loading_columns.html http://docs.sqlalchemy.org/en/latest/orm/loading_columns.htm... [2] http://docs.sqlalchemy.org/en/latest/core/tutorial.html#window-functions http://docs.sqlalchemy.org/en/latest/core/tutorial.html#wind...

kayman 10y ago

Use the best tool for the job. ORM's might do it 80% of the time. The other 20%, most of the mature ORMs provide a way to execute your own sql.

JelteF 10y ago

For me the time I loved SQLAlchemy the most was the time when I migrated my database from MySQL to Postgres. Almost all of the issues people were warning me about did not matter as SQLAlchemy caught them automatically.

Waterluvian 10y ago

What django gave us out of the box would have been impossible with SQL in the same timeframe with the resources we had. If we had the luxury to do things properly and fully the first time, we never would have made it to market with our robots. This isn't to say that one day we won't throw away django or use more SQL. We just had real world schedules to deal with.

eternalban 10y ago

Domain objects in the middle tier only make sense if there is pervasive reuse of component objects. I extensively chased the unicorn of component orientation but it never pans out. The logical place for domain objects is in the Database. Yes, stored procedures, with object layer mappings of views. But the reality of the practice is that (a) the toolchain for the DB backend is quite firmly stuck in 80s and (b) most IT programmers lack SQL skills.

manigandham 10y ago

Not this again. Why is this coming up at all in 2016? There isn't even a valid debate here. ORMs are a tool, that's it. The relational operations of SQL and the object-oriented (or functional) logic of your application code are usually very different and it's nice to have a mapper that lets you interact with your app's language while it takes cares of automatically mapping it to SQL. For 99% of database ops where it's pulling some stuff out, editing some fields and then saving it back, an ORM will save you a massive amount of time with better safety, security and performance. Even complicated queries/mappings/procedures run pretty well and you can always write raw SQL when you need it. Modern ORMs will even let you execute custom SQL and still give you conveniences like easy parameterization and hydrating the results back as objects. Maybe I've been spoiled with the .NET ecosystem with great ORMs like NHibernate, EntityFramework, and Dapper (along with C# features like Linq) but it seems like most of these complaints are from people who use shitty ORMs or can't comprehend that it's just a tool that they can choose to use or skip, at very granular method by method level. There's absolutely no reason for any extreme here.

CuriousSkeptic 10y ago
My experience with hibernate, entity framework, nhibernate, and lastly dapper has basically left me thinking Dapper is all you'll ever want. I can't imagine a use case where I'd rather opt for NHibernate or EF at the moment. (I might add, Dapper in combination with C#6 even gives me enough type saftey to be happy. String interpolation and the nameof operator complements dappers DTO approach nicely) Since you seem to have similar experiences Im curious. When would you ditch dapper for the others?
- pbz 10y ago
  Type safety (no strings) and just as fast as Dapper (actually faster in some cases): https://github.com/linq2db/linq2db https://github.com/linq2db/linq2db
- mistermann 10y ago
  Do you use a Dapper extension for populating and persisting entities? (Last I looked at it as I recall this wasn't default functionality.)

intrasight 10y ago

There are two types of ORMs 1. ORMs for SQL dummies 2. ORMs for SQL gurus You really need to be clear, when discussing ORMs, which type you are discussing.

raspasov 10y ago

(= 'ORM 'OMG) #Clojure

kefka_p 10y ago

Enterprise Objects Framework just worked. Pretty much every other ORM I've worked with has been terrible by comparison.

dools 10y ago

I came to the same conclusion, and wrote this ORM for SQL lovers: https://github.com/iaindooley/PluSQL https://github.com/iaindooley/PluSQL

joostdevries 10y ago

I prefer to use functional frameworks. They 1) express the sql with map, flatmap, filter, groupBy etc functions 2) use classes that are equivalent to tuples. Since I develop in Scala both are very intuïtive to write. Apparently Linq for F# is really good as well. As someone else mentioned in a comment. I get a programming interface that doesn't attempt to hide sql and still my code is fully typed. And it's really easy to make your query logic modular: make the sql fragment a function that you can call. Like any other code you reuse. I can't think of an advantage of ORM over this functional approach. Apart from ORM being more well known.

nodamage 10y ago

Just once I'd like to see someone write an opinion about ORMs without resorting to sweeping generalizations ("Vietnam of computer science") or making assumptions about how they are being used ("it wasn't a good fit for my use case so therefore it must never be"). Maybe, just maybe, it's possible that ORMs are useful for solving certain types of problems and less suitable for other types. If you work on web stuff or applications that are report-oriented where most of the time you're just fetching data (possibly with complex queries) and rendering it to display, then maybe ORMs aren't a good fit. On the other hand if you work on client-side apps where your objects are backed by a database but are otherwise long-lived, then sometimes the other features (beyond SQL generation) that ORMs provide come in handy (tracking units of work over the object graph, maintaining an identity map for consistent access to objects, and providing change notifications when an object or collection of objects is manipulated). If you've never needed any of these features that's perfectly fine. I've never to needed to use a bulldozer either. But I'm not going around declaring bulldozers useless just because I've only ever needed to use a shovel.

notyourwork 10y ago
Is this not the general sentiment of any piece of software? It was implemented to serve a purpose, if that purpose is not sufficing a goal of yours it is not relevant to you.
- nodamage 10y ago
  Indeed. It would be nice though, if people, having realized that a particular tool isn't useful for their particular goals, could still acknowledge that it could be useful in other situations, instead of blaming the tool itself.

jeffdavis 10y ago

Using an ORM is for advanced users, and I think it's a bad default for beginners. A good one certainly saves time when you know how to use it, but it also adds complexity and narrows the perception of what a database can do. Narrowing the perspective is especially bad for beginners, because some options will simply never occur to them and the ORM will prevent learning by osmosis. Let's say you need reliable, efficient cache invalidation (say, to re-render a static page when the data changes). Triggers and LISTEN/NOTIFY in postgres might save a huge amount of time (and even more in maintenance costs) over a custom solution that probably isn't right anyway. Or maybe you need to prevent schedule conflicts. Postgres offers range types and exclusion constraints, which aren't supported in all ORMs. Or to keep a remote system reliably in sync (even if it's a very different kind of system), logical decoding is a very powerful feature. But how would you encounter any of these potential solutions if you are always behind an ORM?

Pawka 10y ago

Haven't red all comments and maybe somebody already mentioned this, but here are my few cents: I've worked with multiple ORM frameworks during my career. And I always reached the point where developer needs focus to framework internals instead of delivering new features. Yes, usually most situation which developer didn't expected can be solved by configuring ORM framework, changing some configuration, providing additional parameters or calling some methods, but what that means? You need fully understand your framework if you want to be sure you application does exactly what you want and not misbehave. So next time when you'll be thinking grab ORM to make things simpler, ask yourself if you really have enough time to fully learn the framework?

khorpy 10y ago

When considering using ORM we need to answer simple questions: does it help to decrease code size? In most cases ORM code is same size as SQL query. Secondly, does it hide complexity? No, it just adds up one more layer that in fact increases complexity and makes it harder to debug. Finally, does it protect us from errors or impoves code quality? This is rarely the case. Instead of using ORM I prefer moving data retrieval code to database layer with help of views. There is also updatable views that we can use to simplify database updates on code side and sometimes avoid using transactions. Separation of code and data logic is great concern when deciding to implement ORM. Nowdays database are very smart and convinient so there is no need to use ORM.

collyw 10y ago
In most cases its smaller (in the Django ORM anyway). myobject, created = MyObject.objects.get_or_create(field1='1' , field2=field2) That would take a number of lines. One query to check if the object exists, another to create it / retrieve it, plus application code to deal with that SQL.
- khorpy 10y ago
  Ok, for this particular example, is operation wrapped in transaction? How will code evalute if we need to check some condition on field2 and occasionally update it?

hitr 10y ago

If you dont want to deal with quirks of ORM,use a lightweight microORM. You basically get some productivity, simple type checking,consistent way to access data , sql injection protection and you will deal with hand rolled sql queries.

lukaseder 10y ago

Use ORMs for complex CRUD. Use SQL for complex queries.

vbezhenar 10y ago

Hibernate is a very complex library. SQL+JDBC on the other hand is easy (JDBC is tedious, some complex SQL might be hard, but generally it's easy). On the other hand Hibernate could significantly reduce development time, if developers know it and application is a goot fit (CRUD, filters). My advice is to avoid ORMs unless your project is big and its database schema is a good fit. And if it is, don't think, that ORM is easy, it's not. Learn how it works, read its source code, understand its inner workings. Or find someone who does. I didn't have much experience with other ORMs, though.

pwilsony 10y ago

"quasi-professional cowboy" here. I have used both ORM and direct SQL systems with the problems outlined above. I finally gave up and wrote my own row mapper: 1. Using the DB Schema, generate stored procedures to load and save data together with code/validation in partial C# classes. 2. Transport data in XML format using single letter (or two) table/element and column/attribute names (auto generated). Use .Net to automatically build/ serialize the XML into objects. SQL Server/.Net have some nice XML features that make this simple. This means that the only user access to the database is through authorized stored procedures. The users have no access to database views or tables. I have implemented the means to load and save arbitrary hierarchical objects (e.g. an Order/Order Lines). A single request to the database can return a complex three level object which would otherwise take hundreds to round trips to load. I agree with the observation that this would be hell to maintain. However, the people likely to maintain this system would be in just a different hell if they had to work with Hibernate or MS Entity Data Model.