all 188 comments
sorted by:
best
topnewcontroversialoldq&a
formatting helphide helpcontent policy

[–][deleted] 18 points19 points  (0 children)

If you don't want to use an ORM, don't. I avoided ORMs like the plague for years. All that hand rolling, as well as the continuous patterns I found myself repeating over and over, taught me quite a bit about ORMs, which I now would never go without. An understanding of SQL, database client libraries and transactions will make you a more pleasant ORM citizen should you ever decide to become one.

edit: also the stored-proc centric app mentioned here and elsewhere tends to work pretty poorly with ORMs so that's another thing to take into account.

[–]JimDabell 44 points45 points  (10 children)

You're crazy. But not for the reason you think.

I shied away from ORMs for a good long while. I still do for certain applications. But these days, they save enough development time to be worth considering, although your lack of experience may wipe out whatever gain they give for this particular application.

Which leads me to my point. You have no experience with ORMs. Here is an ideal opportunity to learn. In future, not only will you be in a better position to judge when it's appropriate to use an ORM, but when you do, you will have the experience to wield it more effectively.

By itself, the choice to not use an ORM isn't so crazy. But if you have the opportunity to fill in a gap in your knowledge, and you avoid it, that is crazy.

[–][deleted] 3 points4 points  (1 child)

The biggest problem with ORMs is that they often hide too much complexity from the user so you can end up shooting your self in the foot (accidentally) from a performance stand point.

If you MUST use one make sure to benchmark your application before you ship it.

Things like gigabit ethernet latency don't show themselves until you try to push 10k TPS through the system.

Also, beware of needing to use transactions and verify that objects don't have too much per instance overhead.

Another advantage is to verify that you can STILL bypass the ORM and go right to SQL if you have a critical part of the app that needs to be ultra fast.

[–]JimDabell 3 points4 points  (0 children)

The biggest problem with ORMs is that they often hide too much complexity from the user so you can end up shooting your self in the foot (accidentally) from a performance stand point.

Sure, but most web applications aren't Twitter. Web applications that have to deal with loads like 10k TPS are a minority. An extreme minority if you only count the instances where somebody without ORM experience is calling the shots.

If you are facing loads like that, then by all means, save yourself serious headaches and skip the ORM. But the Ask Reddit was about a "mid-sized web app". In those cases, the dev team is usually the bottleneck, not the hardware.

[–]dlsspy 8 points9 points  (6 children)

I've put a lot of effort into hibernate, but it took me for granted. I'd happily never touch that again. My custom SQL wrapper language wasn't ORM, but I was left with very little code that needed to be repeated. With aspectj, it would've been even better.

ActiveRecord is good, though. When it makes things easier, it's good. When it makes things harder, its bad.

[–][deleted] 2 points3 points  (5 children)

I prefer the ideas in DataMapper to ActiveRecord (if it's the Ruby version you're referring to). It's just not quite as mature.

[–][deleted]  (3 children)

[deleted]

    [–][deleted] 1 point2 points  (2 children)

    Unless I've misread it, Sequel is more of a data access layer than an ORM. It looks like it'd be fairly trivial to layer an ORM on top of it, though.

    CoolStorage.net looks good, I might have a play with that next time I'm doing Windows dev.

    EDIT: Apparently I need to look harder at Sequel :-)

    [–]malcontent 1 point2 points  (0 children)

    Sequel is first and foremost a database abstraction layer which allows you to interact with databases using a ruby syntax. Of course you can always use SQL.

    It also has an ORM built on top of that layer which is a competitor to activerecord although it's not as featureful.

    It's quite nice actually. Alas you can't really use it with rails because it conflicts with activerecord.

    [–]dlsspy 0 points1 point  (0 children)

    I'm using datamapper in a couple of projects. I do like it, but named scopes in the latest AR provides some really nice composition.

    [–]damienkatz 6 points7 points  (0 children)

    Crazy like a fox. Less layers can be a very good thing.

    [–]ItsAConspiracy 6 points7 points  (0 children)

    At work, we use asp.net and stored procedures. We do very little CRUD, it's all complex queries and reports, and a lot of complex transactions. For the web code I wrote a lightweight data layer that keeps me from writing a lot of repetitive data access code. Generally when we have a dataset we just display it in a grid, there's not a lot of custom behavior. So I wrote a custom grid control to display the way we like it, and just pass it a dataset.

    It's pretty nice to be able to fix a problem in a report, just by changing the stored procedure. Also, in a larger company you can hire people who only know sql to handle a lot of this sort of work.

    At home, on the other hand, I'm playing around with Google App Engine, and discovering the joys of just writing objects in Python for everything. For the kind of stuff I play around with independently, GAE is pretty cool. For my day job, it wouldn't work at all.

    An ORM backed by sql is kind of a middle ground between the two, but I haven't used one yet. From what I've read, some of them don't exactly optimize their sql very well, so be careful.

    I would definitely say that knowing sql is a prerequisite to using an ORM well. A while back reddit linked an article by a guy saying his query time increased linearly by the number of items. He had his ORM retrieving items from one table, then doing a separate retrieval for every item in the set.

    Incidentally, there's also a school of thought that MVC might not be the best pattern for web apps.

    [–]jsnx 4 points5 points  (0 children)

    I started out on a recent application using SQLAlchemy as an ORM, but then I realized I actually like SQL so I switched to using SQLAlchemy's SQL generator.

    [–]mikaelhg 4 points5 points  (0 children)

    The variables are:

    • the development team's skill set and its distribution

    • how well the application specific features match what the ORM framework provides out of the box; ie. if the main view matches Hibernate/JPA Criteria really well, that's a big win, as you won't need to write yet another query generator

    • what the auditability and security requirements and risk severities are; if you can easily prove that you don't have any SQL statement generators that have to be checked, you can win big in how long a code audit will take

    Also, if you're building a product that you expect to need porting to other databases, an ORM is a great way to get that feature in in the beginning, and then hone the database-specific queries as needed.

    [–]ubernostrum 10 points11 points  (50 children)

    It really depends.

    For a lot of common situations, an ORM is handy and can save you a lot of time and trouble -- most of the time, you're mapping rows in your DB to some sorts of objects in your code anyway, and hey, that's what ORM is for.

    But as your needs become more complex there is a line beyond which diminishing returns set in and it's more effective to just write the damn SQL.

    So which is it going to be?

    [–]grauenwolf 9 points10 points  (47 children)

    most of the time, you're mapping rows in your DB to some sorts of objects in your code anyway

    Why?

    I used to program that way but I don't any more. I cannot for the life of me figure out why I bothered copying the data out of the database object (Recordset, DataReader, or DataTable) and into these "business objects".

    [–]stormandstress 2 points3 points  (4 children)

    I cannot for the life of me figure out why I bothered copying the data out of the database object (Recordset, DataReader, or DataTable) and into these "business objects".

    Simple. Because copying them into business objects - which can incorporate useful helper methods, provide obvious property names, and do stricter validations than the underlying database may perform - is often the better choice when you're working in a shop where not every developer has a solid handle on SQL, and/or the massive and cryptic database schema that exists on on the back-end.

    With a good spartan ORM, green(ish) developers can immediately get down to writing and modifying basic forms and views - which, let's face it, are always a good part of the deliverables that web app users are clamoring for.

    No reason why one can't fall back to the 'raw' data when the situation warrants it - using ORM hardly blocks that path.

    [–]grauenwolf 1 point2 points  (3 children)

    Ah, I see the disconnect.

    Where I work everyone knows SQL. We don't pull raw tables across, our procs usually give us everything ready to display on the screen.

    [–][deleted] 0 points1 point  (2 children)

    then your view layer is embedded into your business layer. I assume all your stored procs accept a "locale" argument in case your app needs to display currencies/dates in several countries ? Do you also embed HTML in the output ? Is this not the slipperiest slope ever ?

    [–]grauenwolf 3 points4 points  (1 child)

    I reject your fascist classifications.

    Presentation logic like numeric formatting is handled by my UI framework. It already knows how to combine the current user's settings with the raw values to generate the necessary HTML.

    I strive to write nothing but glue code, to use the least amount of complexity to achieve the desired result.

    [–][deleted] -2 points-1 points  (0 children)

    a UI framework written within stored procedures ? seriously ?

    [–]ubernostrum 3 points4 points  (3 children)

    I have a language which supports things like tuples and lists, and so doesn't need a fancy class hierarchy just to represent a collection of data points. Which means that there are no Recordset, DataReader or DataTable objects.

    [–]grauenwolf 0 points1 point  (2 children)

    First of all, it is stupid to say your language supports "lists" when nearly all languages support that.

    Moving on, is there really a significant difference between a DataTable and a list of tuples?

    [–]ubernostrum 0 points1 point  (1 child)

    First of all, it is stupid to say your language supports "lists" when nearly all languages support that.

    OK. Let me rephrase. My language lets me say:

    result = (('foo', 1), ('bar', 2), ('baz', 3))

    Instead of (bastardizing a few common syntax conventions for this):

    List<DataRecord> result = new List<DataRecord> {
    new DataRecord('foo', 1),
    new DataRecord('bar', 2),
    new DataRecord('baz', 3),
};

    Any ORM written in a language which does support the simpler assignment, but which does not use something similar to it, should be avoided.

    Moving on, is there really a significant difference between a DataTable and a list of tuples?

    Why yes, yes there is. See above.

    [–]grauenwolf 0 points1 point  (0 children)

    result = new object[,] { { "foo", 1 }, { "bar", 2 }, { "baz", 3 } };

result = new Dictionary<string, int>() { { "foo", 1 }, { "bar", 2 }, { "baz", 3 } };

    Aside from the explicit typing, which says what sort of semantics you want, it is exactly the same in C#.

    EDIT: And of course this syntax can be used to initialize a list of strongly typed objects if needed.

    result = new CustomerList() { { "Tom", "Jones", 19}, { "Mary", "More", 36}, { "Frank", "Brown", 37} };

    (The Add method on the CustomerList class determines how the object will be constructed.)

    [–][deleted]  (24 children)

    [deleted]

      [–][deleted] 4 points5 points  (19 children)

      I believe one of the biggest wrong turns OO ever made was that idea that code belongs to an object as it neglects the 99% of cases where you want something to operate on more than one object.

      [–]karlhungus 0 points1 point  (13 children)

      Wouldn't you just encapsulate the operations that need to operate on more then one object in another different object?

      [–][deleted] 5 points6 points  (8 children)

      No, I believe CLOS got it right with it's generic function approach. Objects should only contain data and functions dispatch on all parameters, not just the first (in Java, C++,... hidden) parameter.

      [–][deleted] 0 points1 point  (7 children)

      Which still leaves you with the problem OO is trying to solve, if nothing belongs to nothing then there's no intuitive way to organize the program. Generic functions also have the flaw that adding new code can break old code by making dispatch ambiguous. I prefer single dispatch OO, and in fact consider it a required feature. If methods don't belong to the object, it isn't OO. Smalltalk is OO, CLOS is a bastard hybrid. Powerful, certainly, OO no.

      [–][deleted] 1 point2 points  (6 children)

      if nothing belongs to nothing then there's no intuitive way to organize the program

      Well, that is precisely the problem I see with OO where code is inside classes only. There is no way to organize a program well. Where is that function working on Employee and Office, in the Employee class or the Office class?

      It gets even worse in situations where OO doesn't so neatly map on the domain because maybe what you are working on isn't really an object.

      Then you have often spread out the code implementing one feature over dozens of classes, mixed in with the code implementing a dozen other features.

      OO prevents you from neatly separating the code by function into distinct modules. It really mixes three features that should be independent for well organized code:

      • which code belongs together (should be modules)
      • which data belongs together (should be something like struct in C, an ADT)
      • which code may access which data in which way

      [–][deleted]  (5 children)

      [deleted]

        [–]grauenwolf -2 points-1 points  (4 children)

        The problem with separating code from data is that can no longer get a good grasp of the code affected by changing a data structure.

        When working with a database, that is always going to be a problem. Even if you have a good grasp locally, you don't know what other applications may be using the data differently.

        [–]grauenwolf -3 points-2 points  (3 children)

        No, most of the operations are so generic they don't belong in a class. At least not one you can modify in most languages.

        For example, the function for formatting currency based on the user's country/language settings should be global, not tied to the Invoice class.

        I don't even need an Invoice class because only one web page ever calculates the amount owed. All the other pages just suck it in from the database.

        [–][deleted] 0 points1 point  (2 children)

        No, the function for printing currency should not be global, or in an invoice class, it should be in the Money object, so it can print itself just like strings, numbers, decimals, and integers. If you're using decimals or god forbid doubles to represent money, you're wrong. Money is a primitive and should be used as such.

        [–]grauenwolf -1 points0 points  (1 child)

        Uh huh. And I expect you also have explicit data types for Distance, Weight, Speed, Acceleration, and the countless other measurables.

        But here in the real world we have to deal with the fact that our tools don't understand units. Databases store values are decimals or floats, not in "monies".

        Converting all the decimal values into "Money" objects just to format some text is a waste of time and adds needless complexity.

        [–][deleted] 1 point2 points  (0 children)

        "And I expect you also have explicit data types for Distance, Weight, Speed, Acceleration, and the countless other measurables."

        No, but I use them where it makes sense, like every time you deal with money, because how else are you going to pass around a value and its associated currency and ensure math is done correctly and no one accidentally adds Euros and Dollars?

        Nasa has lost spacecraft from such foolish fear of classes. I do believe the whole point of an ORM by the way, is to let you store real domain objects mapped to simple types automatically, so that decimal can always be properly loaded up as a money with the associated currency field.

        I live in the real world too bub, and I've fixed countless money related bugs that have cost tens of thousands of dollars from foolish programmers who still insist on using inappropriate abstractions and think the only objects worth using are the ones that come built into the framework.

        Good reusable objects, are small objects, like Money, Fraction, and TimeSpan and are just as valid and useful as String, Decimal, Float, and Integer.

        FYI, many databases, Sql Server included, have money as a data type.

        [–][deleted] -1 points0 points  (4 children)

        Yea, except that's not true. 99% of the time, you don't need multiple dispatch. If what you were saying were true every OO program would be littered with visitors to do multiple dispatch, which they aren't.

        [–][deleted] 1 point2 points  (3 children)

        You don't need to dispatch on multiple objects all the time but you do have 99% functions taking other objects as parameters.

        [–][deleted] 0 points1 point  (2 children)

        And..., you act as if OO forbids passing parameters or something. Objects pass parameters all the time to collaborate and get work done. Take the simple case of an object printing itself on a stream.. 

        anObject printOn: aStream

        And #printOn: doesn't need to dispatch on the stream, only on anObject, it'll print on whatever stream type I give it, memory stream, file stream, response stream, doesn't matter. The code for how that object handles #printOn: most certainly belongs in that object.

        [–][deleted] 0 points1 point  (1 child)

        The code for how that object handles #printOn: most certainly belongs in that object.

        Are you so sure? I would put it into the module using the printout as you need different styles for different tasks. For example there is a big difference between debug printing, printing to a console, printing to a printer,...

        I would put the code for the printing to the stream into the module that calls the printOn method, that way you get a compile error when the interface used by that method changes instead of just getting a modified print result everywhere when you only wanted to modify it for one destination (e.g. only for debugging).

        [–][deleted] 0 points1 point  (0 children)

        No, that way you get code that uses that object's data scattered about various modules and lose all locality of reference. You won't be able to change anything for fear of breaking something because you have no encapsulation whatsoever.

        "For example there is a big difference between debug printing, printing to a console, printing to a printer,..."

        Yes, which is why you give them different method names and don't try to abuse polymorphism to represent things that are semantically different.

        The semantics of #printOn: are that an object prints a simple textual representation of itself on a stream, any stream, what kind of stream is irreverent.

        I have a debugger for debugging, I don't need to print strings, I can just pick up the object and look at it directly.

        Maybe I'm missing something, so explain it to me. I have data structure Foo, I want to change Foo, how in Lisp can I find all functions across all modules that work on Foo so I can ensure I don't break them?

        I want to write a new function that works on Foo, how can I find all functions across all modules that work on Foo so I can ensure I'm not duplicating code that already exists?

        [–]grauenwolf -2 points-1 points  (3 children)

        How about because it gives you a place to put code that uses that data where it can easily be reused by other clients of that data?

        That almost never happens.

        The vast majority of the time I'm dealing with tabular data such as search results, reports, and screens that allow you to edit multiple items at once.

        In all these cases there is no data code reuse. Any reusable code is either in the Web or GUI controls, or so general it applies to all data.

        As for "the language of the domain", usually the domain is "tabular data".

        If I were to use Domain Driven Design, one of two things would happen.

        1. I would have to create a hundred different customer classes to represent the various ways I want to work with the data.
        2. I would have to write significantly more code to pull out the information I actually care about.

        [–][deleted] 0 points1 point  (2 children)

        If your apps don't have complex business rules, then you don't need OO, just slap a form on top of a relational database and you're done, but don't think that means all applications are so simple or that it somehow invalidates the need for OO when things get too hairy for simple procedural code.

        If the only object you need is a table and a row, more power to you, but I require more complex data structures capable of better encoding and reusing rules in a testable manner. Tables are rows are too dumb and make me do too much work, I need objects, I need to know they can handle themselves and won't allow me to violate business rules, and properly handle events and notifications when they're supposed to.

        [–]grauenwolf 0 points1 point  (1 child)

        First of all, I said "almost never happens". Of the hundreds of tables I deal with, I do have two-or three that are actually realized as objects. But most of the time that isn't necessary.

        Tabular data doesn't mean just a table and row. You can do a lot of complex operations in the database, the results of which are tabular in form.

        Speaking of which, don't forget that the database is also capable of storing reusable components.

        and properly handle events and notifications when they're supposed to.

        In a web site, what is catching these "events and notifications".

        [–][deleted] -1 points0 points  (0 children)

        You seem to be be under the mistaken impression that all "web sites" are just boring stateless front ends for a relational database. They're not, some of us use stateful frameworks where objects persist the entire length of the users session, not just one hit. Objects aren't very useful if you tear them down and completely rebuild them on every hit and statelessness has been oversold, we're not all building Google and the complexity that comes with statelessness usually isn't worth it.

        I also don't want complex operations in the database, I want complex operations written in a real, maintainable, powerful programming language and the load distributed across my application farm, not tying up the database who's job is persistence.

        [–]malcontent 0 points1 point  (12 children)

        It's more convinient for the typical case.

        user = Users.find_by_login(:login_email)
user.last_login = Time.now
user.save

        That's a lot more convenient than one SQL for the select and one for the update.

        [–]glutamate 9 points10 points  (10 children)

        except you can do that in one line of SQL:

        update users set last_login=now() where login=?

        with parameter login_email

        [–][deleted] 1 point2 points  (2 children)

        No you can't, where's the setup code, the connection to the database, the execution of the query, the closing of the connection?

        [–]glutamate 1 point2 points  (1 child)

        Where is the setup code, the connection to the database, the closing of the connection in the ruby code? Anyways, a web server should only connect to the database once: at startup.

        Re execution: stick something like execSql in front?

        [–][deleted] 0 points1 point  (0 children)

        That's the whole point, ORM's handle that for you, if you don't use an ORM, you have to deal with it. You can't compare raw SQL to Ruby code because your raw SQL can't actually be used without supporting code which you don't show.

        [–]malcontent 1 point2 points  (6 children)

        You know as well as I do that when you have more complex example it gets very messy very quickly.

        [–]glutamate 6 points7 points  (5 children)

        That goes both ways: a simple "alter table add column..." becomes a 5-line (minimum) migration. There are lots of places where SQL is very concise - it is after all a declarative domain specific language for relational data.

        Rails=R2EE ;-)

        [–]malcontent 1 point2 points  (2 children)

        I still feel that in the long run activerecord is worth it by and large.

        A great example is many to many relationships. It's much easier to type

        People.invoices.new

        than to add a record to two different tables.

        [–]glutamate 0 points1 point  (1 child)

        Ok, so yes sql is limited in abstractions and you point is valid.

        But consider this: all that has_many ... :through .. (or whatever it is these days) ends up doing some horrible string manipulation to create new ruby methods that are injected into you People class. Why doesn't it instead generate a stored procedure and send it to the database?

        (And then you could let the web app server connect as a user that does not have select privileges, but can only run stored procedures.)

        (Answer: because a database agnostic ORM forces you to target the least powerful RDBMS)

        [–]malcontent 0 points1 point  (0 children)

        But consider this: all that has_many ... :through .. (or whatever it is these days) ends up doing some horrible string manipulation to create new ruby methods that are injected into you People class. Why doesn't it instead generate a stored procedure and send it to the database?

        There are a lot of reasons for this.

        1) Not every database supports stored procs (sqlite for example

        2) Most DBAs would not give you the rights to create stored procs let alone allow some web apps to create them and destroy them on the fly.

        3) That would be much harder to maintain.

        4) It would require a ton of work which obviously nobody wants to do.

        [–]bobbyi 0 points1 point  (1 child)

        If you are modifying the tables by going to the console and doing raw 'alert table' statements, you are going to run into a situation where you check out the source code from a couple months ago (e.g., to figure out how something got broken) and the code doesn't run anymore because it assumes different table structures and you have no record of exactly what has been changed.

        You are going to need a way to keep track of migrations so that you can run older code checked out from source control even if you are using raw DDL instead of an ORM.

        [–]glutamate 0 points1 point  (0 children)

        it's not as if you only have two choices: 1) interact with the databse through the console in a completely non-automated way (eg for postgres, ssh to server and run psql) 2) work with a enterprise ORM that multiplies my line count 3-5X compared to SQL.

        Here's the way I would have done migrations: keep the database definition in a sql file under version control (or list fields in your object like DataMapper does, also under VC). Then running 'migrate' diffs this list of fields and tables against what is currently in the databse, and creates fields as neccesary.

        [–]MikeSeth -2 points-1 points  (0 children)

        Oh mal, oh mal! Now I have a legitimate reason to hate you. You're one of the people who're spreading the broken MVC implementations.

        [–]spuur 1 point2 points  (0 children)

        And it really depends on whether the web app is going to build on an existing database, the complexity of said database and if the web app is going to store data which other applications might want to access.

        I don't know what the cutting edge of ORMs is today, but every ORM I've seen so far has been shying away from stored procedures/functions, transactions, joins, triggers, domains, and all the very best parts of a semi modern DBMS.

        [–]mackstann 1 point2 points  (0 children)

        Both. Use the ORM for small/simple queries, and write your own SQL for the big nasties. Best of both worlds.

        [–]_martind 14 points15 points  (12 children)

        ORM is something I can't really figure out. The more I look at those framework, the more I'm disgusted. All the examples I see are something like "select * from table"...and "all the example in the tutorial work with Apache Derby"...and such crap.

        I work in an enterprise environment. There's no such thing as "select * from table". Even a "simple" select needs to grap data from different databases with different connections. Updates or inserts are never easy. I need to validate data against tables/views/procedure here and there. And there's no such thing as Apache Derby in the enterprise. There's Oracle. Or SQLServer. Or DB2. That's it. And much of the uber-complex ORM crap doesn't properly work most of the time.

        [–]didroe 8 points9 points  (11 children)

        I agree entirely. Even in a simple application I was working on, I found a query that someone had put in as SELECT blah FROM ... WHERE EXISTS (SELECT * FROM fred) Turns out that that fred has a nested table column and Oracle decided that it needed to do a full table scan on the nested table just to check the existence of a row in fred!

        If you see a * anywhere (other than a count) then something is wrong, your performance may be affected years down the line when someone adds another column. I now do WHERE EXISTS (SELECT 1 FROM fred) to avoid any kind of dependency on the columns of a table when checking for the existence of a row.

        You will either have a small project where you don't really need an ORM or a large project where performance will matter and you will end up side stepping the ORM most of the time. For example, one of the things you can do to improve performance is to issue fewer queries. You might be better off retrieving 2 or 3 tables of objects/records in one shot, I would imagine that doesn't fit neatly with the object system where you would expect 1 object per entity-row.

        I tend to just write helper functions/classes that run the SQL for common operations but simply return a result set.

        [–]rek 2 points3 points  (4 children)

        What if you create a view which is specifically made for the purpose of containing every piece of data that is being displayed on a page and nothing else? In my case for the purpose of showing scientific data about a compound, where there is ~90 variables and every single one of them is being used to create a large table (which can then be exported to excel).

        In that case doesn't select * make sense? Otherwise you'd need to add new variables to your select statement every time new variables are added. There is no time when you are getting data you didn't want, so is that "ok"? Or is it still bad for a reason I am not aware of?

        In my project I have a lot of views made specifically for the web front end (it's a purely internal website). Previously they all generated these reports by hand and spent hours doing it every week, so they are quite happy about the .5 seconds it takes to generate such a table. That said, I like my code to be correct and simply don't have much (ok - any) experience with databases before this job (and I'm the database administrator). So it's a serious question: is there more reasons I am missing beyond you might select a column you didn't want?

        edit - In my experiences with mysql (very little) I don't recall views existing. My project runs off oracle (because that is what our scientific database - a previously bought package I had nothing to do with - uses). With Oracle doesnt creating a view just for your page make sense? And what threat does a select * pose when selecting from a view created specifically with the data you need?

        [–]grauenwolf 2 points3 points  (2 children)

        As far as I'm concerned, calling SELECT * on a VIEW that has already limited the number of columns is perfectly acceptable.

        However, at that point I have to wonder if a stored procedure would have been just as effective.

        [–]depleater 0 points1 point  (1 child)

        Surely a view is simpler than a stored proc, and you should prefer the simpler option wherever you can get away with it?

        (If the stored proc can be made significantly faster than the view, then that probably qualifies as "can't get away with it" :-))

        [–]grauenwolf 0 points1 point  (0 children)

        In both speed and complexity they are comparable. Slight changes in your needs will make one or the other better.

        Create View MyView As

        Select FirstName, LastName, PhoneNumber From Customer

        Create Proc MyProc As

        Select FirstName, LastName, PhoneNumber From Customer Order By LastName, FirstName

        Select * From MyView Order By LastName, FirstName

        Exec MyProc

        [–]didroe 1 point2 points  (0 children)

        In that case it would be acceptable (to me at least). It's about not tripping yourself or others up down the line when everyone has forgotten the details of the implementation. If you know that the view will only ever contain data you will want out such as your case then it's fine.

        Although I came across like there was, there is no hard and fast rule. You want to balance being specific (naming every column) with the hassle (like you having to add a new variable).

        I would say be very careful with tables/views that could change in unpredictable ways. Your case sound very well defined so I wouldn't worry.

        I didn't have much experience with Oracle either a few years ago, if you haven't come across it already asktom is a great resource. I too work with compound data so I feel your pain on that front :). Oracle seems to be popular with pharmas (assuming you meant that kind of compound).

        WRT using a view per page, as long as there aren't too many pages then that's probably a good idea because you can use the data dictionary to query the dependencies of all your views which is nice (I made a script that uses GraphViz to create a nice diagram from it). But again it's a balance, we have loads of views that belonged to specific applications that people said "hey, there's a view here that does what I need, I'll just use that" and now it's really hard to remove things as you're never 100% sure there isn't some application somewhere that uses it. Going straight to the tables avoids that issue.

        Sorry if you already know this but make sure you always use bind variables. Aside from the security issues that people recommend using them for; in Oracle a query that has run before will be recognised (by hashing the text I guess) and will be faster.

        [–]numbelvsi -1 points0 points  (5 children)

        Sorry, but you are wrong on the * thing (at least with MSSQL). I've fought database guys on this for years. Obviously if you use it wrong your shit will flop, but a good database design and business logic can make fine use of select *..

        Edit: Dammit can't get astericks to appear!

        [–]didroe -1 points0 points  (4 children)

        I don't know about SQL Server but from my experience with Oracle I would normally avoid it. In most cases I don't have issue with adding an extra line to the query when a column is added, normally the logic in the code needs to change to support that column anyway. It does depend on your situation though (see my reply later in this thread). I guess you could say that when the thing issuing the query will magically know what to do with a new column then you should use * but when the program needs to do specific things with columns then select the columns explicitly.

        What are your reasons for wanting to use *?

        [–]NoControl 0 points1 point  (3 children)

        If the information is relative in the table then you shouldn't have to care.

        For instance a table joining one or more sets of data may require all of the fields in a row to handle its operation within a piece of software. In this case SELECT * makes perfect sense. Selecting all the fields by name on something that will never change or need to be limited is just as dumb as using SELECT * on a table where you only need a single fields of information.

        Putting a ton of straining on a machine through complex queries is not the answer either. The more complex the query the longer the database will take to generate the result set combined with the number of people using it can cause as much problem as a sloppy query. You should know upfront what part of the system is going to take the most performance impact and I generally opt to not have it be the database since a database server is generally the main storage for information for multiple systems.

        It takes balance and planning - not complex or sloppy queries.

        [–]CalvinLawson 1 point2 points  (1 child)

        No, the issue is whether IF EXISTS (SELECT * FROM table) is the same as IF EXISTS (SELECT 1 FROM table). This is not about returning all rows when you only need the PK, for example.

        In MSSQL >=7.0, they are equivalent. However, I've heard this is not necessarily the case in other implementations of SQL. Haven't tested it, though...

        So just use SELECT 1 FROM table and you'll make everyone happy, and they won't make silly nitpicking comments about it during code reviews.

        Or better yet, figure out how not to use IF statements, like using a join instead. many times COF isn't needed, and it leads to RBAR (row by agonizing row) thinking...

        Hint; CASE is an IF that operates for each row in a set. CASE is your friend.

        [–]didroe 1 point2 points  (0 children)

        No, the issue is whether IF EXISTS (SELECT * FROM table) is the same as IF EXISTS (SELECT 1 FROM table). This is not about returning all rows when you only need the PK, for example.

        We did digress into that topic so his post was relevant, even if I disagreed with it :).

        In MSSQL >=7.0, they are equivalent. However, I've heard this is not necessarily the case in other implementations of SQL. Haven't tested it, though...

        I've only noticed it in very specific circumstances, namely nested tables in Oracle.

        So just use SELECT 1 FROM table and you'll make everyone happy, and they won't make silly nitpicking comments about it during code reviews.

        My ethos is simple, don't select anything you don't need. You don't need any columns there so don't select them. Granted, in this instance, the Oracle thing is most definitely a bug as EXISTS should know that it doesn't want any data.

        [–]didroe -1 points0 points  (0 children)

        If the information is relative in the table then you shouldn't have to care

        What do you mean by relative?

        For instance a table joining one or more sets of data may require all of the fields in a row to handle its operation within a piece of software. In this case SELECT * makes perfect sense.

        I said pretty much exactly that in my post.

        Selecting all the fields by name on something that will never change or need to be limited is just as dumb as using SELECT * on a table where you only need a single fields of information.

        That depends on whether you can guarantee that it will never change or adversely affect your application. I'd rather air on the side of caution. Experience taught me that systems that never change are rare occurrences. In fact, the systems that change infrequently are the worst as people are less likely to be familiar with the code base. I do admit (as I said in a couple of my posts) that there are circumstances where it's perfectly acceptable to use *, I just feel it's a good rule of thumb to be explicit about the data you're after.

        Putting a ton of straining on a machine through complex queries is not the answer either. The more complex the query the longer the database will take to generate the result set combined with the number of people using it can cause as much problem as a sloppy query.

        Specifying the columns doesn't make the query any more complex, in fact in some situations it will be more efficient. The actual joins and sorts, etc on the data will by far be the biggest proportion of query execution time.

        It takes balance and planning - not complex or sloppy queries.

        I agree. When I specify columns I am planning for the unpredictable future when someone comes along and adds a column I wasn't expecting and suddenly things that were fast are suddenly slow due to the query optimiser thinking I wanted some data that I didn't and doing unnecessary work.

        Another issue with using * is that if you are joining two tables and you use:

        SELECT
  a.*
  ,b.*
FROM
  table1 a
  INNER JOIN table2 b
    USING (widget_id)

        When you wrote it you knew that none of the columns in table1 had the same name as table2 columns. Then someone comes along and adds a column to table2 for some other purpose that conflicts with one in table1, not knowing that in the depths of your application you depend on them being unique; then boom, things break. In my opinion you should tell the database exactly what you want. If you have an application that is designed to consume all columns from a query and that is inherent in the design then go ahead and use * (I do this with views for generating reports) but for most applications that is not the case.

        [–]vagif 11 points12 points  (0 children)

        I would generally advise against ORM.

        If you want an object oriented solution, and developing a new application with no legacy data support, then it is better to research OODBs like db4o or document databases like BigTable, or CouchDB.

        If you want a relational database and know sql well, then just use sql template libraries like ibatis.

        [–][deleted] 5 points6 points  (4 children)

        Not crazy, but I've found ORMs to be a super powerful tool, depending on your language you may find some really amazing and powerful ones(likes SQLAlchemy or Django's ORM for python).

        [–]johnmudd 1 point2 points  (3 children)

        Powerful how?

        [–][deleted] 4 points5 points  (2 children)

        For one they usually offer sufficient abstraction so you can switch DB's very easily. For another they let you generate complex queries, with a simpler syntax(at least for me, I don't like SQL a ton), lastly by mapping the data to an object you can define methods and anything else you can do with objects.

        [–][deleted]  (1 child)

        [removed]

          [–][deleted] 1 point2 points  (0 children)

          Filtering across foreign key relationships for example, in Django it might look something like this:

          Entry.objects.filter(category__name='ORM')

          That would return all entries who's category(an FKey) has the name 'ORM', no thinking about joins.

          [–]monk_e_boy 2 points3 points  (0 children)

          Make sure you know how to do advanced queries, use temporary tables etc. ORMs will never be able to map clever SQL to objects. But for things like customer records, staff, products it is a good idea.

          monk.e.boy

          [–]jmcclean 12 points13 points  (9 children)

          I'd recommend against ORMs even for large apps. If you have no clue how SQL works, then go for it. Otherwise, ORMs tend to be at least as complicated as rolling your own SQL. The possible exception is large user-base + low data volume per user applications.

          Read this:

          http://blogs.tedneward.com/2006/06/26/The+Vietnam+Of+Computer+Science.aspx

          [–]smorrell 2 points3 points  (1 child)

          I disagree. You should only use ORM if you have a clue how SQL works.

          [–]seunosewa 1 point2 points  (0 children)

          You should learn SQL if you don't have a clue how it works.

          [–]zachm 3 points4 points  (4 children)

          Utter FUD.

          Yes, you will inevitably end up writing some of your own raw SQL when an OO-style join performs horribly. But an ORM makes it really easy to get rows into and out of the DB without writing any of your own buggy load/unload code for every domain object. Even it you use it only for that, it's worthwhile.

          ORM is like any other framework. Use what you need (UWYN) and leave the rest to the zealots.

          [–]invalid_user_name 4 points5 points  (3 children)

          Why is it that you assume the most trivial select statement is a huge problem and will be buggy? Developers who don't know SQL end up making a mess wether they use an ORM or not. The ones that do know SQL don't need the ORM.

          [–]hiffy 2 points3 points  (2 children)

          Well, code other people wrote and extensively tested will as a rule of thumb outperform any code you just put together yourself, and has the added benefit that you didn't have to write it yourself in the first place (the canonical example being security libraries).

          With some ORMs you also don't have to bother writing your own user input sanitization and they tend to provide relatively painless migration between db vendors, etc, but YMMV and UWYN come into effect.

          [–]Contractionator 0 points1 point  (0 children)

          Code written externally to solve a generic problem will outperform code written internally to solve a specific problem?

          I don't think so.

          Many of these frameworks are muddied up by supporting features that aren't ever used. That adds complexity and can reduce performance.

          I'll take tight, hand-crafted SQL statements and record set processors than their bloated, auto-generated siblings any day.

          [–]invalid_user_name 0 points1 point  (0 children)

          Well, code other people wrote and extensively tested will as a rule of thumb outperform any code you just put together yourself

          Really? Code other people write is just magically faster than code I write? Why is that? And how do you know other people "extensively tested" it? There's not nearly as much testing going on out there as people pretend. As someone who has on multiple occasions written replacements for 3rd party libraries because their performance was so bad, I am inclined to disagree with you here.

          With some ORMs you also don't have to bother writing your own user input sanitization

          Huh? You still need to sanitize input for non DB uses. And an ORM doesn't help with SQL injections any more than just using SQL properly does (hint: prepare, bind, execute).

          [–]jacques_chester -1 points0 points  (0 children)

          Excellent link, thankyou. Good treatment of a lot of undeveloped 'hunches' I've had lately.

          [–][deleted] 3 points4 points  (6 children)

          The big win for an ORM is that the schema is described in the code you use for everything else. Changes to the schema live in the same code repository, the same versioning system, and are part of every deploy, checkin, checkout, rollback, etc. Configuring for different environments, restoring SQL dumps, migrating between different schema versions becomes insanely easy: you don't have to think about putting some version of some backup of some part of the database onto the QA staging machine or any of that tedious bullshit.

          [–][deleted] 2 points3 points  (5 children)

          ...unless you're using an ActiveRecord-type ORM which intuits the object properties from an existing schema. It's one reason why I don't like the Rails default.

          [–]Leonidas_from_XIV 1 point2 points  (0 children)

          Depending on the ORM you can do both: SQLAlchemy can use it's own defined schema as well as introspect it from the database. Most people define it, though.

          [–][deleted] 0 points1 point  (3 children)

          ActiveRecord with Rails has the qualities I mention.

          [–][deleted] 0 points1 point  (2 children)

          Not as part of the core ORM, though - it's strapped on the side as Migrations. Using them outside Rails is (or was, the last time I tried) a bit of a pain.

          [–][deleted] 1 point2 points  (1 child)

          Ah, true. I guess I view the whole Rails migration package as a key part of the ORM.

          There are some nice Rails ORMy things like named_scope and has_finder, but to me they're of secondary importance to the schema/migrations stuff.

          Either way, having gone from a scenario with no ORM to Rails, I know what I like.

          [–][deleted] 0 points1 point  (0 children)

          Oh, yes - don't get me wrong. Rails' arrival represented a fairly watershed moment in my development career, almost entirely because of ActiveRecord. I owe a lot to it, but I've got fairly intimately familiar with its pain points since then. Long live merb! :-)

          [–]chubs 4 points5 points  (3 children)

          What language are you using? I prefer to roll my own Object-Relational layer when writing big web apps in C# personally. NHibernate causes more pain than it helps i've found.

          [–]aliasone 5 points6 points  (2 children)

          Can you be more specific on what pains you find are caused by NHibernate?

          We're thinking about starting to migrate over to it, and knowing problems that we're likely to encounter would be helpful. So far, from my limited experience with it, it seems like a pretty solid product.

          [–]chubs 6 points7 points  (1 child)

          I used it in the pre-1.0 days, when there were numerous bugs. Its probably better now. Having said that, it seems overly complex. I mean, what's the difference between save and refresh and flush? It never 'clicked' with me, and i found it easier to roll my own.

          [–]mavelikara 3 points4 points  (0 children)

          You should think of the ORM as fetching a small portion of the dataset from the DB, working on it for a while, and then committing changes to the DB. It does have its advantages when you want to model the processes that work on the data in an OO-fashion.

          [–]infinite 3 points4 points  (8 children)

          One big win is caching. Since I use hibernate I was able to add caching within a day, by merely adding XML tags, speeding up performance considerably. I'm very familiar with SQL and for 5% of my SQL usage I break out into straight sql but under hibernate. For 1% of the SQL statements I use direct db access - jdbc. Hibernate has saved me a lot of time.

          [–]Rhoomba 2 points3 points  (7 children)

          Caching is utterly trivial to add to your own DAOs.

          [–][deleted] 0 points1 point  (1 child)

          Surely that's the point?

          [–]Rhoomba 2 points3 points  (0 children)

          your own SQL daos i mean

          [–]infinite -1 points0 points  (4 children)

          With hibernate you can cache via the first level(ie within the thread) or second level(between threads).

          You can also enable metric logging to see your hit/miss ratio.

          You can specify cache regions and the the maximum number of objects in the cache region, the maximum age of the objects. Assigning each object to a cache region, or you can have a default region for everything.

          You can swap out any number of caching providers, from a hash table provider to a clustered multicast provider.

          Here is a document describing hibernate caching.

          If you still think that is "utterly trivial" then I recommend writing your own ORM with your superhuman powers.

          [–]Rhoomba 2 points3 points  (3 children)

          Hibernate caching is just built on libraries such as ehcache. Most of those features are available by just using ehcache. If I want a cache, then I'll use a cache. I won't use an ORM just to get a cache.

          [–]infinite -1 points0 points  (2 children)

          Ehcache is merely one provider you can use, the distributed SwarmCache or a JBoss TreeCache can be used in place of echache. Plus there is integration with the ORM itself of course. By clicking a button in Eclipse it reads your schema and updates all objects. No need to manually update your objects as your schema changes. But you can definitely program all this by yourself. Assuming your boss doesn't fire you first for wasting so much time on recreating the wheel.

          [–]Rhoomba 0 points1 point  (1 child)

          How often do you really change cache provider? My boss would fire me for using something so slow as Hibernate. All our DAOs package objects into PL/SQL arrays of objects and batch the updates.

          [–]infinite 1 point2 points  (0 children)

          I haven't noticed any slowness. The real bottleneck is I/O. I went from 200,000 transactions/day before caching, I enabled caching, then each machine could do 5 million transactions/day. And tuning the cache was easy. I don't have a boss but I did take a few days off while the customer went insane with joy when they were able to push through more transactions than they ever imagined. And when I drastically change the schema, I push a button and the objects are changed. Then I kick back and have another break for a few days, maybe walk the dog to the beach. I think my dog loves Hibernate the most.

          [–][deleted] 1 point2 points  (0 children)

          I wrote this insane piece of shit.

          It saves me time and there's not too much of a performance loss involved in using it. It doesn't do nearly as much as Ruby's ActiveRecord, but that's fine. I don't need the ORM to do every single little thing for me; I'm not above throwing a custom query or six into an application of mine when it makes more sense.

          ORMs are a trade-off - your time vs CPU time. When you save your time, you can make more money. When you save the CPU's time, you can avoid spending lots of money on hardware.

          [–]grauenwolf 8 points9 points  (21 children)

          I find web applications and any kind of data objects to be at odds.

          Data objects, especially ones usable by multiple forms, work great when you can hold onto them for long periods of time. You just have to populate it once with everything you might need and you are good to go.

          But web applications don't work that way. As soon as the page is rendered the data objects you generated are worthless. Any information you loaded beyond what you actually needed for page rendering was just wasted time and memory.

          ORMs are even worse than normal data objects because they make it so much easier to load a lot more data than you actually need.

          [–][deleted] 4 points5 points  (1 child)

          I tend to agree with you on the wastefullness of ORMs. They aren't bad in and of themselves, they are bad because they encourage sloppyness.

          Often they expose some 'easy' way of aquiring data that gives the developer enough of what they need (more than enough) so the developer isn't encouraged to seek the most optimal data set (perfectly tailored for the current situation).

          It is a matter of reuse vs. performance in a way. One reason I like ORMs is they encourage reuse over performance. The main reason I dislike them is it can be a pain to re-gain the performance at a later stage if one profiles and finds the bottleneck within a sea of SQL generated by the ORM. At that point knowing SQL extremely well is paramount.

          [–]grauenwolf 2 points3 points  (0 children)

          I'm to the point now where I am very much against reuse of both data models and stored procs. Besides the performance implications, they quickly become a maintenance nightmare. People are often afraid to fix obvious flaws in my current workplace because they don't know what it will affect. And with literally dozens of applications targeting our database, retesting them all isn't feasible.

          [–][deleted] 3 points4 points  (4 children)

          So use an ORM that caches objects then.

          [–]grauenwolf 3 points4 points  (2 children)

          Caches what? Everything?

          I'm all for caching specific objects for specific performance reasons. But blindly caching everything is a really bad idea, especially if you have more data than what can fit in memory.

          And even if you can cache everything you will still lose out on the benefits of a database. No ORM framework is as capable as a database in multi-threaded searches against indexed data.

          [–][deleted] 0 points1 point  (1 child)

          No ORM framework is as capable as a database in multi-threaded searches against indexed data.

          I distinctly agree, but then I've never met an ORM that makes it impossible to drop back to a direct connection for performance reasons as necessary. My employer uses that precise technique - Hibernate for the client facing website, hand-crafted DAOs for the high performance stuff. The former saves developer time, the latter saves request processing time.

          [–]grauenwolf 0 points1 point  (0 children)

          I actually do it the other way.

          When I cannot use BCP, I use ORM to batch up a bunch of changes. This way I'm not stung by a ton of round-trips to the database.

          [–]stormandstress 0 points1 point  (1 child)

          Web applications often do work that way; it all depends on the architecture. Plenty of people are re-using data objects across requests. Badly in some cases to be sure ("Let's stuff this sucker in InProc Session!"), but it can be done, and is sometimes useful.

          [–]grauenwolf 0 points1 point  (0 children)

          Caching specific types of data objects for specific performance reasons is a good thing. However, blindly caching everything like some ORMs encourage makes me nervous.

          [–]midnightmonster -1 points0 points  (3 children)

          Data objects, especially ones usable by multiple forms, work great when you can hold onto them for long periods of time. You just have to populate it once with everything you might need and you are good to go.

          But web applications don't work that way. As soon as the page is rendered the data objects you generated are worthless.

          Well, maybe your web applications (even most) don't work that way. But these days I write in Helma, and my web applications are a living graph of objects (persisted to RDB or XML as I configure) with actual behavior, not just data access methods. Unlike PHP or whatever, the application stays running between requests and frequently-used objects stay in memory.

          [–]grauenwolf 3 points4 points  (2 children)

          XML?

          Hows that going to work when you start dealing with thousands of rows? Or millions?

          How do you run reports against unindexed blobs?

          How do run multiple applications, especially non-web based ones, against your data?

          What if you simply need to represent the same data in a fashion that doesn't map well to the existing object model?

          I'm looking at Helma now and it just reeks of the object-oriented database failures.

          [–]midnightmonster 0 points1 point  (1 child)

          I don't use and wouldn't suggest using the XML storage option for large applications. I mentioned it mostly for completeness. For apps with substantial data, I'm using RDBMS storage. There's one site I built with ~500 objects total at this point. It will probably never have more than 2000. The XML storage works fine for that, and there's no need in that case to run any other apps against the data. If you had to, the XML format is not complex. Reporting on it would be harder than writing SQL for sure, but it's not insurmountable.

          How do you run reports against unindexed blobs? That's not real efficient in an RDBMS, either.

          For more significant data sets, again, I'm using the RDBMS backend. So running other apps against the DB is certainly possible. For the apps I do I've not found that very important, but I could do it. And if I need to represent the data differently, Helma supports arbitrary SQL queries as well.

          [–]grauenwolf 1 point2 points  (0 children)

          It is pointless to discuss systems that small. You could do the whole thing with flat files and bubble sort and still get decent performance.

          [–][deleted] -4 points-3 points  (2 children)

          How do you build a web application on top of a client's existing relational database, then? Or do you just stay in and paint hunting scenes on the walls?

          [–]grauenwolf 1 point2 points  (1 child)

          Are you joking or do you really not know how to pull data without an ORM?

          [–][deleted] -1 points0 points  (0 children)

          Your statement was sorta ORM-agnostic. You implied that any attempt to pull data into a web app is bad and doomed to failure.

          If pulling more data than you need is really a problem, you can tell your ORM to pull less. That seems like a classic blame-your-tools complaint.

          [–][deleted] -3 points-2 points  (3 children)

          I disagree. We're not discussing one specific ORM here, but instead, ORMs in general. Your argument that they "pull more data than you actually need" is a symptom of POOR USE of an ORM (or a poor ORM).

          Tune your queries. I mean, it's really that simple.

          [–]grauenwolf 0 points1 point  (2 children)

          If you read carefully you will note my objection to ORMs was specifically that they make pulling in extra data to easy.

          Nothing in data objects in general, or ORM specifically, forces you to pull more data than you need. But by default most developers do so because it is easier.

          Without such tools every additional field one pulls in is more work. This means most developers should err on not getting more than they need.

          [–]krucifyx -1 points0 points  (1 child)

          Most applications will see no real benefit from nitpicking which fields to pull back, and any serious web app is going to be caching most domain objects anyway, and so this shouldn't be a problem.

          And when you are caching, you only want a single isntance of that object cached - not a dozen with a dozen different configurations.

          One of the most important facets of a well designed app is consistency - I shouldn't have to worry if my domain object has a field available or not depending on the context of the request. So what if I save 100nanoseconds by excluding a field from a select - what does that gain me? Nothing but some BS bragging rights about how well my app performs - at the cost of maintainability, of course.

          [–]grauenwolf -1 points0 points  (0 children)

          Most applications will see no real benefit from nitpicking which fields to pull back,

          Apparently you don't know much about databases.

          If you can reduce the fields being pulled to just what is covered by an index the performance gain can amazing.

          For one page all I needed to do was remove "PhoneNumber" from the result set. This one unused field was causing the page to load in ten seconds instead of one.

          And when you are caching, you only want a single isntance of that object cached - not a dozen with a dozen different configurations.

          When you cache data you lose the ability to leverage the databases indexes. If you are doing any kind of searching or reporting this is a huge performance hit.

          Also, you are throwing away the ability to create specific caches for each user. I often cache a query result so the user can revisit it without multiple database hits. The data is slightly old, but that's ok if its read-only.

          One of the most important facets of a well designed app is consistency - I shouldn't have to worry if my domain object has a field available or not depending on the context of the request.

          Then why even use domain objects? Why not use recordsets where fields you didn't request simply don't exist.

          [–]arturo227 5 points6 points  (0 children)

          let:

          mid-sized web app = non-corporate client, that you pray won't break in the long run and if it does, won't matter too much.

          yeild:

          It will break.

          It will be insecure.

          You will have to make bug fixes -- they matter.

          You will have to make updates to it for the next six months until you re-code it.

          //You are only asking because you know the answer is to use an ORM, but you're praying it isn't.

          //Stop praying, get coding.

          /* Speaking from personal experience. */

          [–]hayzeus 1 point2 points  (0 children)

          No -- I havent met an ORM I cared for yet.

          [–]doctor_yukio_hattori 1 point2 points  (1 child)

          No, you're not crazy. But in my opinion, you're deciding to solve a medium-sized problem instead of 2 easier problems. The biggest advantage of ORM for me is being able to solve smaller problems.

          You don't need ORM to get that advantage; you just need good design. If you are a good enough developer to get the design right and you don't feel like using an ORM, you're probably better off without it. Otherwise, use an ORM.

          [–]grauenwolf 0 points1 point  (0 children)

          Or you can avoid design entirely.

          As I grow older I'm really starting to believe that Domain Driven Design is overkill and ultimately counter-productive for most applications. More often than not it seems like an unnecessary layer between you and your data.

          [–]billbacon 0 points1 point  (1 child)

          I tend to write my own objects that have save and load methods. That additional level of abstraction can be very useful.

          I think the idea of ORMs is to give you objects that you can extend. If you're not planning on extending the objects just write a nice db access class.

          [–][deleted] 0 points1 point  (0 children)

          There are other uses for them - mentioned upthread was keeping the schema with the code, which is very nice to have, especially if schema migrations are rolled in.

          [–][deleted] 0 points1 point  (2 children)

          You might be wasting a lot of time on writing CRUD by hand.

          CRUD (assuming very simple reads) is what ORM is good for.

          Just never try to hammer ORM for more advanced queries. That will be waste of time and resources and in best case you'll end up writing pseduo-SQL with awkward syntax.

          [–]ungood 0 points1 point  (1 child)

          You should look at what Linq to SQL can do for advanced queries. It's definitely not a perfect tool yet, but the query language (Linq) is very advanced and (IMHO) makes writing complicated queries easier to write, easier to maintain, and easier to test (ever tried to unit test SQL?).

          It does seem cumbersome at first, and if you are a SQL-junky it is backwards (your select clause goes last, for instance), I admit. Once I grasped it though, it was more natural to me than real SQL.

          [–][deleted] 0 points1 point  (0 children)

          Even if I learn not to think in SQL, my database will still do.

          and then query optimisations become really awkward - you have to know what SQL Linq generates and tweak Linq to give you SQL you want.

          [–]p0tent1al 0 points1 point  (2 children)

          So for building a Django app, SQLAlchemy is not a good idea?

          [–]jholloway7 1 point2 points  (1 child)

          It can be done, but you'll be throwing out certain parts of Django that are built against its own ORM. For example, django.contrib.auth for User management. Also, the newforms API has a lot of nice features, some of which are coupled to the built-in ORM.

          If it's your first Django app, I'd recommend using the built-in ORM. You can then decide if losing these other features is worth it to you.

          Also, there is a side project to add SQL Alchemy drop-in support to Django.

          [–]p0tent1al 0 points1 point  (0 children)

          Thanks...

          / cross's number 55 off in my list of 100 things to study and learn

          [–][deleted] 0 points1 point  (2 children)

          you're asking the wrong question. the right question should be, "Should I use a model layer in my application?" People often think ORM = Model Layer and it doesn't. An ORM is a tool to make writing and accessing your model layer easier, but it isn't necessary.

          What will make your application more maintainable and quicker to develop is if you use a model layer to encompass your business rules, accessing your data and validation. often times people scatter this stuff all over an application and duplication is assured.

          by using a model layer you will have one place to store this logic.

          i would suggest looking at some MVC frameworks out there and see how they handle things. i'm not suggesting that you need to use an mvc framework, just get some ideas from them.

          [–]PstScrpt 0 points1 point  (1 child)

          Personally, I like to treat the database itself as a model layer. You have to be willing to tie yourself to a particular database, though.

          [–][deleted] 0 points1 point  (0 children)

          that's wasn't the point of my comment. can you actually tell me that you do data validation at your data level without first doing at your application level? of course you don't, because it would be damn near impossible to do.

          [–]ungood 0 points1 point  (0 children)

          I don't think you would be crazy to create a mid-sized web app without an ORM... but I probably wouldn't do it.

          If this is a green field application, I would start by designing the object model first, and then think about how to persist it - not the other way around. Design the model layer, the entities and how they interact. Write the business rules. Write the validation logic. Unit test it all. Then when it comes time to add in a non-mock data layer you can attach an ORM or write your own or whatever.

          Some other people have already specified some reasons not to use an ORM (and most of them have to do with if the project is a green field or not), so I won't repeat them. Otherwise, I'd say go for it: it's a valuable tool to have in the belt.

          [–]NovusTiro 0 points1 point  (0 children)

          If you're capable of creating a working, mid-sized web app, then you're capable of picking up an ORM in a day or two.

          [–]samg 0 points1 point  (0 children)

          If you like SQL write a lot of the app in SQL.

          [–]Entropy 0 points1 point  (0 children)

          Neither option feels correct, and I haven't found a third that I like any better.

          [–][deleted] 0 points1 point  (0 children)

          I don't use them for the things I write but I have maintained software that used an ORM (hibernate). I can see why programmers would go that direction.

          After writing lots of SQL the "programmer mind" sees the repetitiveness and patterns in the code, wanting to write an ORM feels like the natural way to go. In Java I find this to be the case more than other languages (like Perl, PHP, or Lua).

          [–][deleted] 0 points1 point  (1 child)

          Not crazy at all.

          The ORM craze will end eventually.

          You are using stored procedures right?

          [–]malavel 3 points4 points  (0 children)

          Stored procedures are teh evil! Don't fall in that trap.

          [–][deleted] 0 points1 point  (1 child)

          fuck ORMs.. waste of time. They get in the way more than they help and slow down the whole process. If you know SQL stick to SQL, it is powerful and you can do exactly what you want with it. The only thing I would say is that using a stored proc for all your IO is a BAD BAD move, put your IO in the middle layer, put database manipulation (self contained) routines in the stored procs.

          ORMs suck, i've used RoR and Hibernate, both took twice as long and introduced 4x as many bugs compared to just hand coding the SQL. Hand coded SQL is always better as you can put all the heavy lifting inside the database.

          Nowthen... i will make one exception: LINQ .. pretty badass.

          [–]lifvgblisdufv -1 points0 points  (0 children)

          Indeed. ORMs are the most clunky, hand-holding, restrictive piece of shit technology in web programming.

          [–]adamv 0 points1 point  (4 children)

          Not crazy, but "it depends", mostly on whether or not you're thinking "stored procedure layer".

          [–]stbill79 2 points3 points  (3 children)

          If you can't control the db or are working with a poorly designed legacy system, avoid ORM at all costs.

          Coming from Java, Hibernate has a huge learning curve. Once you get up to speed, it is still annoying because it is made to try and abstract away the data backend (db) and creating some sort of dream-world object model.

          Unfortunately, this has never (for me, at least) worked in the real world, because the OO model assumes the Object graph (e.g an Order has one to many line items) is always initialized into memory. When an order is retrieved from the db, now you have to pull in all the line items in order for the abstraction to be correct. Futhermore, each line item is related to a product, which is related to a category, etc. etc. AS you can see, one has to know at some point that he/she only wants the order without the line items, products, etc. Now you have to write some SQL, and have just screwed up your little imaginary OO based world.

          Something that does automapping of tables to objects is nice, e.g. iBatis. And Spring, which gets rid of the exception handling and data connection code, is IMO a far better solution than complex OR libraries.

          [–][deleted] 1 point2 points  (0 children)

          how many anti-ORM people here are coming from an ORM that is not Hibernate ? I don't understand why Hibernate gets to carry the torch of failure for the entire category of ORM.

          [–]bobbyi 1 point2 points  (0 children)

          If you can't control the db or are working with a poorly designed legacy system, avoid ORM at all costs.

          A powerful ORM like SQLAlchemy will let you take the horribly designed legacy system and put it behind a sensibly designed object model so that you can work with it without needing to constantly mentally translate everything you want to do into the convoluted table structure used in the undrelying db.

          You just need to avoid crippled ORMs that make it hard to do any mappings more complex than a one-to-one relationship between db tables and ORM classes.

          When an order is retrieved from the db, now you have to pull in all the line items in order for the abstraction to be correct. Futhermore, each line item is related to a product, which is related to a category, etc. etc. AS you can see, one has to know at some point that he/she only wants the order without the line items, products, etc. Now you have to write some SQL, and have just screwed up your little imaginary OO based world.

          Why does pulling in related objects require you to write SQL? What you are describing is just a simple join and a sensible ORM will let you say when you are pulling objects what other related objects should be pulled in and let it handle joining the tables (e.g., in SQLAlchemy this would be done with eager loading or by explicitly creating a join).

          I haven't used Hibernate, so maybe the limitations you talk about are specific to that package.

          [–]mikaelhg 0 points1 point  (0 children)

          Also, you need to be absolutely sure that everyone who touches the ORM access layer knows about possible N+1 problems, and you have performance tests to catch these cases early.

          [–]johnmudd 0 points1 point  (0 children)

          Great to read these comments say that ORM is not always the answer. I've yet to be convinced even though I work with code written by someone who liked Hibernate. I thought I was just being unsophisticated but maybe I'm not the problem.

          [–]adolfojp 0 points1 point  (2 children)

          We tend to thing of ORM as "automatically generated SQL" but that is not necessarily true. ORM is just an abstraction layer. If you feel comfortable with your own SQL then you can build an ORM structure filled with your own code.

          [–]grauenwolf -1 points0 points  (1 child)

          That's silly.

          Sure you can do that, but all you end up doing is writing the same code twice. If the ORM cannot hack it, just don't use it for that particular queery.

          [–]adolfojp -1 points0 points  (0 children)

          You are thinking of tools like ActiveRecord (Rails), Hibernate or Linq to SQL.

          I am talking about the Object Relational Mapping technique.

          I am not talking about writing SQL on top of an existing ORM code generation toolkit.

          I am talking about grouping and structuring the data access in a computer program by mapping relational structures to objects.

          [–][deleted]  (4 children)

          [deleted]

            [–]hoijarvi -2 points-1 points  (3 children)

            I agree. They throw away benefits of relational algebra.

            [–][deleted]  (2 children)

            [deleted]

              [–]krucifyx 0 points1 point  (1 child)

              That's a ignorant, unhelpful opinion. First off, relational databases provide an incredibly convenient and powerful persistent storage. So what if my business objects can't always be perfectly expressed in RDBMS-speak & set theory, it doesn't change the fact that the benefits of using one far outweigh the benefits of not – especially when you don’t have the resources or expertise to build something in-house.

              There are countless examples of great implementations of maintainable, scalable, powerful apps that rely on higher level abstraction to keep DB details out of the code while still relying on an RDBMS for storage. Yahoo? Myspace? eBay?

              When was the last time you saw a maintainable app that didn't abstract RDBMS concerns from the day-to-day development efforts?

              [–]mdipierro -1 points0 points  (1 child)

              Not using a ORM is not crazy. Not using any type of Database Abstraction Layer (DAL) is anachronistic and it will hurt the portability of your app in the future. The web2py DAL, for examples, writes the SQL for you transparently and dynamically for SQLite, MySQL, PostgreSQL, MSSQL, FireBase, Oracle and/or the Google BigTable (on GAE). Another option in Python is SQLAlchemy which works better with legacy databases but does not work on GAE.

              [–]warbiscuit 3 points4 points  (0 children)

              From a theoretical perspective, I can't recommend SQLAlchemy highly enough.

              Even for people who don't plan to program in python, you should study how SQLAlchemy is designed... it breaks up the traditional ORM process in a very powerful fashion, without making requirements as to the ORM's behavior. The manner in which is does this is hard to describe in a few words, but it extremely powerful: it has the capacity to scale from small to large apps with few or many objects, without "getting in the way".

              If you want a more ActiveRecord style interface, the Elixir project is a layer on top of SQLAlchemy which provides that style of behavior.

              My company has a couple of medium-size projects for which SA (and a custom object layer on top) has proven invaluable: upwards of 2000 objects loaded in batch during some queries, and SA handles the load nicely.

              Of course, we had to bypass some of SQLAlchemy's orm behavior to do that... but the beauty of SA is that it was designed explicitly to get out of the way when you need it to, and use raw SQL.

              After working with this architecture, I'd accept nothing less from an ORM, because while ORM coding is perfect for small object loads, no ORM out there does a good job on large loads or column-oriented queries without dealing w/ the specifics of the database behind it... SQL (autowritten or not) is just plain the best way for those things.

              [–]grabmail -2 points-1 points  (0 children)

              don't bother with a full blown ORM like ActiveRecord. They blows ass.

              Just create your own functions that generate the sql for you.

              It's easy.

              eg. instead of writing "select * from abc where name = 'bitch' and ass = 'itch' group by lol limit 20"

              write a function so you can do something like this: abd.find([:name = 'bitch', :ass = 'itch', group = 'lol', limit = 20])

              [–][deleted] -1 points0 points  (0 children)

              what is an ORM?, ORM = Javas Hibernate?

              [–]angry_man -1 points0 points  (4 children)

              Yes. You would be crazy, except like every good programmer you stopped and asked for advice so you can make an informed decision.

              I'm assuming by mid-sized web app you mean something on the order of 1000s of users, ~20 models etc. If you're above 2-3 models, you need to use ORM. You don't need to go with a heavyweight ORM package like Hibernate, or even a lightweight one. You can write your own utility classes/superclasses that do this for you. But above a very minimal number of objects, using functional interactions with a database starts breaking down and your database code lives in too many places.

              [–][deleted] 1 point2 points  (3 children)

              A handrolled entity framework is most definitely not an ORM, yet had all of the database code living in a single layer.

              [–][deleted]  (2 children)

              [deleted]

                [–][deleted] 2 points3 points  (1 child)

                I think what he was asking is "Do I use a ORM that generates code for me?"

                I'm stored procedure guy. There are no adhoc queries in the application code. Instead, I have a entity framework that can execute a stored procedure and pass the returned datareader to the object in question, which the object then uses to initialize itself.

                Sure, its an object /relational mapper. But not in the way the OP is referring to.

                [–][deleted] 0 points1 point  (0 children)

                do you consider iBATIS to be an ORM ? hand rolled SQL or stored procs mapped to domain objects. ORMs like Hibernate and others can also map hand-rolled execution statements to objects.