Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

OrmHate

martinfowler.com

Post reply on HN
Print view View original

121–130 of 136 posts

Re: OrmHate

#121

Post by gnaritas »

crazygringo wrote: post #101
gnaritas wrote: post #93

Earlier quoted context omitted.

> I generally find myself writing functions to automate the trivial ones: get_single_field_by_primary_key(), get_row_by_primary_key(), get_values_by_field(), etc. There are maybe 4 or 5 of these. An entire ORM is total overkill. Making a homebrew data layer isn't avoiding an ORM, it's building one. > If you find yourself writing the same query structure 20 times, then create a function for it. What do you think ORM's…

No, ORM's provide object-relational mapping, and all sorts of functions for building up queries as part of that. They don't provide an API for common queries at all. I don't even know what "all the common queries" would be... beyond get-a-value-for-pkey, every app is totally different in the kinds of queries it needs. What I described isn't a homebrew data layer or building an ORM, it's just a few helpful shortcut fu…

> every app is totally different in the kinds of queries it needs

There is more to SQL than queries. Every app need insert, updat, and delete, and a basic select 1 and select many matching a predicate. Those can all be automated; that's what ORM's do, they automate the repetitive simple stuff.

More complex queries that strain the ORM's abilities can be done in SQL in a view, and then mapped in by querying the view with the ORM. Or you can just drop into raw SQL when necessary. Every app is not different, they are largely all very similar.

Re: OrmHate

#122

Post by dgreensp »

Not all problems are technically suitable for a NoSQL database.

You might as well say "I'm a big fan of JSON, but not all problems are technically suitable for a NoXML data format." There's nothing magical about SQL, as proven by the fact that so few apps actually use its abstractions! The reasons to use SQL today are pragmatic; battle-tested engines, tools, etc. Most developers will never write their own ORM or database, but the ones who are up to it are starting to explore the design space quite profitably.

Re: OrmHate

#123

Post by einhverfr »

sjwright wrote: post #104
einhverfr wrote: post #84

I think the hate comes from two sides: 1) Relational folks who feel that ORMs don't really enable good relational design, and 2) OO folks who feel that ORM's cause a lot of headache regarding relational to operation data structure conversion. The folks in camp 1 are usually absolutely right. Most ORMs encourage the developer to design the database around the ORM which has to be an antipattern when it comes to relatio…

> In the LedgerSMB project we decided to use stored procedures to accomplish this abstraction. Why? Stored procedures are nothing more than a bolted-on way to store SQL queries on the database server. Yes, you've create an "API" and an "abstraction" but you've done nothing you couldn't have done in any other language. > The stored procedures thus create an API I consider stored procedures to be little more than a shi…

Most of our stored procedures are currently in PL/PGSQL. I don't know that I would say the language sucks. In fact where one is doing set operations, the language is wonderfully expressive and flexible, and one can get more done in it faster than any alternative, again where the operations are set operations.

SQL has some very serious downsides and these are persent in PL/PGSQL, but these aren't that hard to avoid with good db design. Don't get me wrong, I have seen some really horrible PL/PGSQL stored procs in my day, but it doesn't have to be bad code.

As for an additional API layer, yes there is one but it is pretty thin and most of it is pretty heavily automated. I would say that at least 75% of perl module code is just identifying methods to map to stored procedures, so there isn't a lot of code there (figure three lines to map, a sub name, a sproc name, and closing the sub).

What this allows us to do is support multiple applications in multiple languages while offering consistent security and database functionality to these applications. The DB isn't just a data store, it's essentially a meeting point potentially of a larger number of applications.

We have considered adding PL/Perl and PL/Python stored procedures but since most of these are set operations and so they are things that belong in db queries.

As for source control I don't know what really would separate this from a compiled language. You can think of loading stored procedures into the database as essentially like compiling a program. As we move to future versions this will become an even closer comparison, as we start bundling these using PostgreSQL 9.1's extension framework.

Edit: Our stored procs are defined in text files that are checked into a source repository. They are also designed to be rebuildable, and there are utilities for rebuilding stored procs included in the software.

Re: OrmHate

#124

Post by einhverfr »

norkakn wrote: post #90
einhverfr wrote: post #87

Earlier quoted context omitted.

Let's define a good ORM though. A good ORM should be able to handle a database designed for a different application, normalized to 5NF or BCNF, using composite primary keys, composite foreign keys, and other solid db design concepts, and be able to reliably generate queries to bridge the gap, and should do so where sets of objects are required in a way that does not unduly burden the database with small queries that…

What ones do you like? The Django ORM makes me want to vomit every day or so.

The best one I have looked at was DBIx::Class

Re: OrmHate

#125

Post by einhverfr »

stevenwei wrote: post #100

I really have never understood the ORM hate. I've found them to be immensely useful in 99% of circumstances, and for the remaining 1%, a good ORM will always let you fall back to raw SQL. Aside from providing a simpler syntax for performing basic queries, there are a few features that ORMs provide that have greatly simplified my life: 1. Automatically using prepared statements and validating/escaping query arguments…

So now suppose we refactor your database a bit, since all cakes are deserts and we don't really need to store that the fruit cake is both a cake and a dessert.

Suppose we make category have a self-join (add a parent_id field that references category(id)).

Now we want to make sure that when we want to list all deserts, all subcategories are listed too. Assume the possibility of arbitrary depth.

How easy is tht to do in your ORM?

Re: OrmHate

#126

Post by stevenwei »

einhverfr wrote: post #125
stevenwei wrote: post #100

I really have never understood the ORM hate. I've found them to be immensely useful in 99% of circumstances, and for the remaining 1%, a good ORM will always let you fall back to raw SQL. Aside from providing a simpler syntax for performing basic queries, there are a few features that ORMs provide that have greatly simplified my life: 1. Automatically using prepared statements and validating/escaping query arguments…

So now suppose we refactor your database a bit, since all cakes are deserts and we don't really need to store that the fruit cake is both a cake and a dessert. Suppose we make category have a self-join (add a parent_id field that references category(id)). Now we want to make sure that when we want to list all deserts, all subcategories are listed too. Assume the possibility of arbitrary depth. How easy is tht to do i…

Good question. I can't speak for all ORMs, but both SQLAlchemy and Core Data provide direct support for self-referential relationships

SQLAlchemy lets you drop down to raw SQL as well if you need to, while still taking care of mapping the result set to objects for you.

Core Data is less powerful (and technically not an ORM), but it's the defacto standard on iOS so I'm pretty much stuck with it.

Re: OrmHate

#127

Post by einhverfr »

stevenwei wrote: post #126
einhverfr wrote: post #125

Earlier quoted context omitted.

So now suppose we refactor your database a bit, since all cakes are deserts and we don't really need to store that the fruit cake is both a cake and a dessert. Suppose we make category have a self-join (add a parent_id field that references category(id)). Now we want to make sure that when we want to list all deserts, all subcategories are listed too. Assume the possibility of arbitrary depth. How easy is tht to do i…

Good question. I can't speak for all ORMs, but both SQLAlchemy and Core Data provide direct support for self-referential relationships SQLAlchemy lets you drop down to raw SQL as well if you need to, while still taking care of mapping the result set to objects for you. Core Data is less powerful (and technically not an ORM), but it's the defacto standard on iOS so I'm pretty much stuck with it.

So if you have self-referential relationships of arbitrary depth then can it generate clauses like WITH RECURSIVE or CONNECT BY? Or are you functionally limited to one level of self-joins without dropping to SQL?

Re: OrmHate

#128

Post by einhverfr »

xi wrote: post #106
cico71 wrote: post #91

Earlier quoted context omitted.

I'm not sure what you mean by fit poorly in the relational model. ORDER BY, LIMIT/OFFSET have to do with presentation of data. So, although it's highly desired that a language based on the relational model supports them, they have nothing to do with the model per se. As for opaque keys, I thought the old debate about surrogate/artificial and natural keys was over years ago. The relational model has nothing to say abo…

ORDER BY, LIMIT/OFFSET have to do with presentation of data. So, although it's highly desired that a language based on the relational model supports them, they have nothing to do with the model per se. By fit poorly I mean that you cannot express most real-world business inquiries using pure relational primitives without ORDER BY or LIMIT/OFFSET and that's why I think relational algebra is not usable per se. SQL fixe…

I disagree with you on NULL. The relational model is a very good way of looking at data in many contexts.

The problem actually is that NULL has several distinct meanings and there is often no real way to differentiate between them other than to disallow all meanings but one, and that is often difficult.

For example you talk about nullable columns, and this is one aspect of NULLs. NULLs may mean missing data. They also are often used to mean the data doesn't apply. This already runs you into ambiguity problems because you can't type in a query that easily distinguishes whether the attribute doesn't apply or is merely unknown. Note that Oracle treats NULL strings as equivalent to empty strings, while PostgreSQL tries to differentiate strings by allowing empty strings which are distinct thus allowing a not-applicable value for character string fields.

Additionally, you would expect the || operator to handle unknown data differently than it does data which doesn't apply. string || not_applicable should equal string. string || unknown should equal unknown.

Now that's only the beginning of the problem. There is a third use of NULLs too, namely as a placeholder for missing rows in outer joins......

If we had three different NULL values some of this problem would be more manageable, but the problem is that as soon as you allow nulls in columns, you can't always easily tell from a query on a well-normalized database what that NULL means without a lot of additional introspection of the representative of the entity set and then you are basically guessing.

Re: OrmHate

#129

Post by einhverfr »

xi wrote: post #120
cico71 wrote: post #118

Earlier quoted context omitted.

I was about to prepare the query without ties and yes it is a lot harder than necessary and, more important IMO, much less readable/intuitive for people than have to maintain the code and that is exactly why different SQL dialects introduced ORDER BY/LIMIT/OFFSET/TOP/RANK etc. But let me be a little bit picky about this and Date's view on ORDER BY. The relational model deals just with the algebra/calculus without get…

I was about to prepare the query without ties and yes it is a lot harder than necessary and, more important IMO, much less readable/intuitive for people than have to maintain the code and that is exactly why different SQL dialects introduced ORDER BY/LIMIT/OFFSET/TOP/RANK etc. Very good point. In the book pointed out by Matt, Date explicitly states he's not saying ORDER BY is not useful, just that it doesn't return a…

First I don't know why order by, limit, offset, or windowing functions, can't be said to return a relation if we define relations in a way which is sufficiently useful to include these operations. In other words, they are used in ways which returns sets of tuples (or sets of entities if you want to see it that way), based on specific selection criteria.

I would thus agree that to the extent that these are not part of the relational model this says more about the incompleteness of that model than it does about the operations themselves.

Re: OrmHate

#130

Post by mping »

jerf wrote: post #56
mping wrote: post #40

Earlier quoted context omitted.

I strongly disagree with this. I'm consulting for a project where I cannot discuss technical decisions about database modelling; sometimes we need to query 20/30 tables or more in a tx, with very complicated mappings 5+ levels deep. If I was to write the sql for this, I'd take 10x the time and it would be wrong anyway. Of course, this is one end of the spectrum where the ORM just enable me to shoot in the foot faster…

"If I was to write the sql for this, I'd take 10x the time and it would be wrong anyway." SQL is the problem here. It's so hard to write reusable, composable fragments of SQL that we've all just internalized the idea that we have to write the query you're referring to from scratch every time, which is a bizarre and annoying intrusion of 1970s software engineering into our 2012 world. You almost certainly have pattern…

Interesting remark. Although I really can't see any other good alternative at the moment.
Post reply on HN