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

71–80 of 305 posts

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

#71

Post by bunderbunder »

tfigment wrote: post #22
bunderbunder wrote: post #12

Earlier quoted context omitted.

On C#, Dapper's useful, but not perfect, for letting you write your own queries and then making it easy to unpack the result sets. Unfortunately, it relies on property setters for doing the unpacking, so it doesn't interact super well with your code if you like to avoid unnecessary mutability. The only publicly-available lightweight ORM I know of that does a good job with that is the SQL type provider in F#.Data. Tha…

Thanks. That actually doesn't look that bad. It would be nice to have anonymous type objects but I recognize the difficulty in that. This looks like a nice compromise. Now if they could also fix passing in arrays as part of a parameterized query "select x from y where z in ?" where ? is a collection of strings or integers it would be perfect but I think that is a driver/interface problem. Edit: Looks like it actually…

Dapper accepts anonymous objects for passing query parameters. It will return an expando if you don't specify the type of object you want back, too. That can be convenient, but I don't personally like having dynamic objects running too wild in my code so I end up manually mapping it to another type before returning anyway. (AutoMapper can help here.)

The IEnumerable support will generate (parameterized, I believe) inline arrays in the query. Not really my favorite, but it works.

The thing that I couldn't get over, and which ultimately led me to write my own micro-ORM at my last job, was the weak support for table-valued parameters. But I gather they've fixed that since then, so hopefully it's not a big deal anymore.

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

#72

Post by Animats »

StevePerkins wrote: post #34

Ten years ago, there was a blog post every other week bemoaning ORM's. Ten years ago, those posts often had merit. In 2016, this sentiment is outdated. A few points: 1. If you think that using an ORM means you don't have to learn SQL, then you're going to have a bad time. This is where most of the bad press originates... from people who never really learned SQL or their chosen ORM. An ORM provides type checking at yo…

Marshalling support is useful. SQL result -> struct and struct -> SQL insert code is repetitive to write. Getting fancier than that may be overkill.

Marshalling in general needs more compile-time support. Kludges such as Google protocol buffer preprocessors are a fast but clunky way to do it. It would be useful if languages could be given a reference to an SQL CREATE TABLE and could use that information usefully. Field names, type information, and enum values should come from the CREATE TABLE info.

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

#73

Post by programmarchy »

titanomachy wrote: post #64
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…

How do you manage version-control on stored procedures? Can they be checked in with the rest of the application logic?

That's the way I've done things in the past -- save stored procs in a file in a project repo, along with deploy and rollback scripts.

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

#75

Post by maxxxxx »

nickpeterson wrote: post #44
greydius wrote: post #29

Earlier quoted context omitted.

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.

Also Java and C# developers often produce bad SQL procedures because they tend to think procedurally about problems.

I think if they were better integrated with the IDE and easier to debug then they would probably write better stored procedures.

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

#76

Post by platz »

sopooneo wrote: post #70
titanomachy wrote: post #64

Earlier quoted context omitted.

How do you manage version-control on stored procedures? Can they be checked in with the rest of the application logic?

I myself am wary of stored procedures except in very specific and uncommon circumstances. That said, you could absolutely version control your stored procedures by creating them from within database migration files that are version controlled by default.

What is the source of the wariness?

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

#77

Post by woah »

thomasahle wrote: post #52
StevePerkins wrote: post #34

Ten years ago, there was a blog post every other week bemoaning ORM's. Ten years ago, those posts often had merit. In 2016, this sentiment is outdated. A few points: 1. If you think that using an ORM means you don't have to learn SQL, then you're going to have a bad time. This is where most of the bad press originates... from people who never really learned SQL or their chosen ORM. An ORM provides type checking at yo…

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

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

#78

Post by Spooky23 »

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.

This is a great point.

In a smaller company, those barriers aren't really meaningful. In an enterprise, you're easily adding a week or more to change control process to ship.

I think another factor towards why database focused solutions aren't popular in small companies is that MySQL historically hasn't been the best platform for that approach, and you need more expertise to scale the database.

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

#79

Post by RubyPinch »

zepolen wrote: post #37
fishtoaster wrote: post #31

Earlier quoted context omitted.

I completely agree. 90% of the queries in my app are no more complex than selecting from a table with a simple condition. I definitely find users = User.where(has_foo: true).limit(10) to be a lot more readable than rows = connection.exec_query("SELECT * FROM users WHERE has_foo=true LIMIT 10") users = rows.map { |row| User.build(row) } (And that's an example with no user-provided input) Likewise, any app of sufficien…

Here's the thing, anyone who knows SQL will find the second one readable, and only Ruby programmers who have used ActiveRecord will know how the first one does.

> and only Ruby programmers who have used ActiveRecord will know how the first one does.

do you really think the first one is /that/ hard to understand?

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

#80

Post by platz »

mattdw wrote: post #46
zepolen wrote: post #37

Earlier quoted context omitted.

Here's the thing, anyone who knows SQL will find the second one readable, and only Ruby programmers who have used ActiveRecord will know how the first one does.

Really? connection.exec_, .map, .build – there's a lot more non-SQL going on in the second example than the first. The first may be syntactically far from SQL, but the use of familiar vocabulary makes it pretty understandable from a SQL point-of-view.

And what are the connection and allocation semantics of the first? (Yes, the second way is more explicit.)
Post reply on HN