What ORMs have taught me: just learn SQL (2014) • Hacker News Board

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

#41

Post by deanward81 » Sun, Jun 26, 2016, 9:14 PM UTC

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 supports IN statements being passed an array:

SELECT x, y FROM A WHERE z IN @ids

Works if you pass in a parameters object containing a property called ids that is a list/array of some sort (see https://github.com/StackExchange/dapper-dot-net#parameterize...).

On SQL 2016 this uses STRING_SPLIT across an nvarchar(MAX) which makes it effectively unbounded. Earlier versions and other platforms have restrictions on the number of elements in the array/list.

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

#42

Post by gregopet » Sun, Jun 26, 2016, 9:14 PM UTC

I've come to love jOOQ, the Java library that I can write type-safe pure SQL in (and with code that is immediately understandable to anyone who knows SQL). It provides mechanisms to smooth the clash of Java/SQL worlds, but otherwise never assumes it is smarter than the wisdom accumulated through decades of database usage. I've come to appreciate many aspects of databases again that tools like Hibernate try to hide from me.

http://www.jooq.org/

I do still use Hibernate, probably because my usual framework of choice makes it so easy to, but anything above medium complexity goes through jOOQ nowadays.

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

#43

Post by collyw » Sun, Jun 26, 2016, 9:18 PM UTC

prodigal_erik wrote: post #25

vrdabomb5717 wrote: post #13

Earlier quoted context omitted.

At the big tech firm I work at, there's a best practice where any database (whether that's a traditional RDBMS or a NoSQL client) is abstracted away by a microservice with a defined API, and every other application that wants to get that data needs to interact with the microservice. That way, the database schema can change without it affecting multiple applications. There's still the traditional mismatch between ORM…

We have decades of research into filtering, joining, and aggregating across a complex set of tables and views. With microservices you have to roll your own query planning and stream all the intermediate results on the wire even when you're throwing away most of them.

It does sound like an extra layer of unnecessary complexity to solve a problem that could be solved in other ways.

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

#44

Post by nickpeterson » Sun, Jun 26, 2016, 9:19 PM UTC

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.

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

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

#45

Post by collyw » Sun, Jun 26, 2016, 9:22 PM UTC

Animats wrote: post #8

ORMs tend to assume that there is "the application" with "its database". If the application changes, so does the database. If the data is used by more than one application, it's better to have the data defined in the database and write applications as database clients.

Ok, I have only used Django's ORM and a little bit of SQLalchemy, but in both cases you are defining the database in a Python file. Django's models.py file usually maps directly to database tables, and is less verbose than writing SQL create statements.

Maybe I don't see any advantage in what your doing, as I treat the database model as the most important part of the application (or maybe thats the same as what you are saying). Get that correct, and code falls into place easily. Start hacking rules in at the application level and eventually it gets messy. Linux said something similar about bad programmers worrying about the code, and good ones worrying about the data and its relationships.

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

#46

Post by mattdw » Sun, Jun 26, 2016, 9:23 PM UTC

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.

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.

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

#47

Post by PeCaN » Sun, Jun 26, 2016, 9:23 PM UTC

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…

> 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 your framework with an ORM). For convenient access you can roll 30 lines of Perl to wrap DBI or whatever (I use Perl for most web backends these days) and call your stored procedures in normal syntax with a little metaprogramming.

Maybe this sort of scheme (heavy usage of stored procedures and offload almost everything to the DB) doesn't work for everyone, but I like databases and it works for me.

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

#48

Post by dkarapetyan » Sun, Jun 26, 2016, 9:23 PM UTC

Never seen a large enough project that relies on an ORM be anything other than a giant mess. I mean never. The conclusion is correct. From an application perspective the db is just another API and should be treated that way and the ORM should just be thought of as a convenient DSL for creating queries on top of that API.

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

#49

Post by bdcravens » Sun, Jun 26, 2016, 9:23 PM UTC

vladsanchez wrote: post #17

I've always said: "ORMs are for people who don't know SQL!"

I began programming in languages where SQL was a requirement in 1999; only in the past 3-4 years have I use ORMs. I often write a lot of raw SQL, but I find a well written ORM statement communicates intent more quickly.

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

#50

Post by collyw » Sun, Jun 26, 2016, 9:24 PM UTC

Animats wrote: post #8

ORMs tend to assume that there is "the application" with "its database". If the application changes, so does the database. If the data is used by more than one application, it's better to have the data defined in the database and write applications as database clients.

Ok, I have only used Django's ORM and a little bit of SQLalchemy, but in both cases you are defining the database in a Python file. Django's models.py file maps directly to database tables, and is less verbose than writing SQL create statements.

Maybe I don't see any advantage in what your doing, as I treat the database model as the most important part of the application. Get that correct, and code falls into place easily. Start hacking rules in at the application level and eventually things start to get messy. Linux said something similar about bad coders worrying about the code, and good ones worrying about the data and their relationships.