Hacker News Reader

Comment by thomasahle | Hacker News Reader

> An ORM provides type checking at your application layer

This. When composing complex queries, we really want type checking and SQL injection safety.

Don't parameterized queries provide all the safety one might need?

Parameterized queries are to prevent injection. They don't tell you, at compile time, when you're building an invalid/unrepresentable SQL query.

> They don't tell you, at compile time, when you're building an invalid/unrepresentable SQL query.

Sure they do, with a proper library. Haskell's persistent library does this very well.

The Persistent library is an ORM. It provides syntax and safety for sql. For example

    delete $ from $ \t -> do
             where_ $ (t ^. TutorialAuthor) ==. 
                      (sub_select $ from $ \a -> do
                                    where_ (a ^. AuthorEmail ==. val "anne@example.com")
                                    return (a ^. AuthorId)){-/hi-}
    tuts <- select $ from $ \t -> do
            where_ (t ^. TutorialSchool !=. val True)
            return (t ^. TutorialTitle)

looks a lot like something you might get with a good ORM. It might be more general and slightly different, but haskell usually does things slightly different.

Haskell doesn't have OO, so it can hardly be an ORM.

The library in question is modeling relational theory at the language/type level. That's not remotely like an ORM.

Haskell does OO just fine. It doesn't do nominal subtyping, but that's really a misfeature in OO anyway.

That said, persistent doesn't do OO.

Data encapsulation (e.g. via ADTs) does not equal "OO".

Existential types are kind of like OO interfaces (allow storing a collection of things that only have in common that they implement the interface), and typeable can be used for casting them back to their base types.

I would argue that OO is polymorphism + innate object identity. Though the latter only matters if you have mutability; but then, whether OO without mutability is "true OO" is one of those deeply philosophical questions.

I find the dividing line in formalism; we can always find similarities with language-level features from OO, but OO is by definition an informal set of ideas about how programming is best done and understood, rooted deeply in empiricism, not formal theory and analysis.

If I can model your type/language model as a higher-kinded dependently typed lambda calculus (e.g. using Boehm-Berarducci encoding for non-turing complete recursive ADT definitions), then it's not OO -- it's functional programming, rediscovered.

Looks like Persistent has no support for table joins...bit of a show stopper that, but there's a companion library[1] which looks quite powerful (similar to Scala's Slick wrt to type safety and composability).

Can't say I care for the syntax much (`^.`, `where_`, etc.) but I guess that's par for the course in Haskell given the open world (i.e. module system leaves something to be desired relative to OCaml and SML).

[1] http://www.yesodweb.com/book/sql-joins#sql-joins_esqueleto

Yes, but that library is using Haskell functions to compose SQL strings at run-time. I should have clarified that by interpolation I meant manually writing the string you send to the DBMS.

No, but your unit tests should catch bad queries and then you're good to go.

You can use that argument against any kind of static analysis/checking.

Unfortunately, ORMs sometimes undermine safety for the sake of convenience. Even ORMs which use parameterized queries (which any sane framework will) may be vulnerable if they build dynamic queries using string concatenation.

No ORM will pass queries using string concatenation? (Right? I know nothing about ORMs written in PHP by beginners that don't know SQL if it jumped up and bit them in the ass... But surely no half-decent ORM would concatenate strings to pass arguments?)

Anyway. Type safe queries like QueryDSL is extremely nice to work with.

But as was mentioned, it all boils down to this: There IS NO silver bullet.

You have to learn SQL, and then the ORM tool. And the abstractions will leak, and you will be pissed of sometimes, but It Is Worth It because you will save a lot of development time.

There are some pain points. Large joins where you'd need to eager-fetch a few one-to-many "leaves" at the end of a huge and complex join is a bit of a pain, as the ORM will need to split the joins for efficiency, and there is no obvious way of reusing the complex part between the calls available to the user of the ORM.

(Like how you could use a temporary table when using Oracle for instance. Nothing should stop a ORM to use that as a join-strategy though, when I come to think of it...)

The other obstruction is mindset. To use a ORM efficiently, the developer need to step away from the data-layer model.

There is no separate data access layer when working with persistent objects. The objects represent the model, and are simply persistent. If they are changed, the change stays.

Preferably, the model should be available to the whole application, and the objects should be changed and used where it is suitable, not restricting access based on that they will trigger a database access. (The important thing should be to keep the model and it's rules together - in some sort of abstraction, not that some things happen to write to a database.)

>And the abstractions will leak, and you will be pissed of sometimes, but It Is Worth It because you will save a lot of development time.

Only on some languages. As I mentioned on another comment, on Go I'm very productive using simple database/sql + sqlx. On C# I could die writing mapping boilerplate before getting any business logic done.

Does database/sql check syntax at compile time? Or do you just try to unit-test you out of that?

No, but even though Entity Framework (as an example) does check types at compile time it doesn't prevent you from making mistakes that will cause runtime errors (oh the number of times I have run into issues trying to do simple things like ThingsToCheck(x => x.Created <= DateTime.Now.AddHours(-1)) which you would think works, after all it compiles, but it will just throw a runtime error because LINQ to Entities doesn't know how to parse the expression tree).

Type-safe ORM's don't preclude integration testing, end of story.

> But surely no half-decent ORM would concatenate strings to pass arguments?

I wish... Drupal had an issue in ORM itself. https://www.drupal.org/SA-CORE-2014-005

No ORM is building query bits from user input with string concat.

I don't need a full O/RM to provide query composition, type checking and protection against sql injection. SQLAlchemy core provides all of these (you can use the ORM if you really want, and I have done so, but when I am pulling 16K records in the whole object mapping layer becomes expensive when a simple tuple works fine).

The problem is that doing queries over relational databases in a type-safe way requires either 1) some form of structural typing (for projections), or 2) ignoring projections altogether, which brings a slew of perf problems to the table.

Ironically, this means that OCaml is probably the best [reasonably popular] language to use an ORM in.

Reply on news.ycombinator.com