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

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

wozniak.ca

Post reply on HN
Print view View original

101–110 of 305 posts

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

#101

Post by thomasahle »

jdreaver wrote: post #92
eudox wrote: post #86

Earlier quoted context omitted.

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  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.

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

#102

Post by craigvn »

Gustek wrote: post #9

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

I pretty much do the same. The CUD is ORM, and some simple selects in C# Linq, but as soon as the query is a bit more complicated I crack out the SQL. It does not have to be all or nothing.

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

#103

Post by aidos »

I'm a bit surprised by these criticisms from someone using sqlalchemy. I had the same opinions of orms in the past until I learnt sqlalchemy.

Partial objects, attribute creep, and foreign keys: defer the loading of columns [1] to suit your use-case. You can even have different mappings that deal with different subsets of columns depending on your situation.

Data retrieval: sqlalchemy is so good for querying that I've mostly stopped using sql for anything other than complex exploratory analysis work. Sure, you need to know sql to be effective, but whatevs - eg Window functions? Done [2]

Dual schema dangers: I understand the concern, but again, you can choose to map this as you please and I don't see what doing raw sql gains you here. You need to change the schema, if that affects your application, you need to change your application, if it doesn't, do you need to change your orm code? I routinely migrate my db and release changes to the orm code later.

Identities: I admit there are a couple of times I need to flush in my app outside of the normal lifecycle, which I don't like. With sqlalchemy, for the most part, you just connect objects and don't worry about the ids.

Transactions: Whatever happens you'll need some sort of transaction boundary in your code. Removing the orm doesn't gain you anything there, does it?

I've written about some of this before on here so I won't rehash it https://news.ycombinator.com/item?id=9180831

There's a time and a place for sql, orms and storedprocs. The more you know about each of them, the more effective you can be. As ever, learn the tools and never throw the baby out with the bathwater.

[1] http://docs.sqlalchemy.org/en/latest/orm/loading_columns.htm...

[2] http://docs.sqlalchemy.org/en/latest/core/tutorial.html#wind...

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

#105

Post by platz »

Nullabillity wrote: post #94
platz wrote: post #82

Earlier quoted context omitted.

Give the new proc a different name (e.g. append a version number). On the n+1 deployment, clean up the old proc.

And so you've reinvented the version control system on top of your deployment system, on top of your version control system. That also sounds fun if you use the proc from more than one location...

Zero downtime deployment with stored procedures should not be confused with "version control".

It's not a version control scheme; once deployed the procs are never updated.

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

#106

Post by [deleted] »

craigvn wrote: post #98
PeCaN wrote: post #47

Earlier quoted context omitted.

> If you're not using an ORM, then you ultimately end up writing one. I disagree with this. A lot of things people use ORMs for are rather easily solved with stored procedures, especially in Postgres where you can write stored procedures in Perl, Ruby, etc. Validations, “fat models”, etc are all managed with SQL easily (and this means you get that functionality from _anywhere you access the database_, not just from y…

But once you have run the stored proc how do you display it to the user? How do you get the data from the UI to the stored proc to execute? You write some code or it happens by magic? If you write some code, then you have just written an ORM.

[deleted]

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

#108

Post by teacup50 »

thomasahle wrote: post #101
jdreaver wrote: post #92

Earlier quoted context omitted.

> 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 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…

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.

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

#109

Post by [deleted] »

thomasahle wrote: post #101
jdreaver wrote: post #92

Earlier quoted context omitted.

> 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 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…

[deleted]

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

#110

Post by FLUX-YOU »

greydius wrote: post #29
ams6110 wrote: post #11

Earlier quoted context omitted.

It's quite trivial to keep stored procedure code in source control with the app code or in its own repo. I am always mystified when I hear the complaint that this is difficult because in my experience it's no more difficult than managing any other code in a SCM repo.

In my limited experience with the enterprise world, the problem with using stored procedures was that the devs had no control over the database. We could have come up with some nicer solutions to certain problems, but no one wanted to deal with the bureaucracy necessary to create and maintain parts of our applications within the database.

Even if devs had control, it's difficult to know which version is actually on the database without pulling it up (this is a timesink), especially if you have multiple environments/configurations.

AFAIK there's no way to diff/history of stored procs in the database (and certainly not against your VCS), so large companies usually do comment blocks at the top of each one.

Post reply on HN