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

What ORMs have taught me: just learn SQL

wozniak.ca

Post reply on HN
Print view View original

161–170 of 245 posts

Re: What ORMs have taught me: just learn SQL

#161

Post by chipotle_coyote »

zzzeek wrote: post #150
GeoffWozniak wrote: post #82

Earlier quoted context omitted.

What I didn't mention in my post was that I did use SQLAlchemy Core to write some pretty complicated queries. It's actually quite good. I like it. There were some spots that things got hairy though, and the code was pretty hard to follow. I don't fault SQLAlchemy here, but I wrote the query in SQL and it was simpler to work with. SQLAlchemy is absolutely on the right track, but using the core doesn't diminish the fac…

So you didn't use SQLAlchemy ORM at all, yet you wrote a whole article about how ORMs "don't work", naming SQLAlchemy (strongly implying the ORM) as an example... if so, it would explain why all the complaints you have about ORMs seem to indicate a misunderstanding of the SQLAlchemy ORM ("attribute creep": query for individual attributes or use `load_only()`, `deferred()`, or other variants; "foreign keys": the ORM o…

The OP wrote: "What I didn't mention in my post was that I did use SQLAlchemy Core to write some pretty complicated queries."

What you seem to have read: "What I didn't mention in my post was that I did use SQLAlchemy Core to write some pretty complicated queries and didn't use SQLAlchemy ORM at all."

It seems to me that it would be more plausible to read: "What I didn't mention in my post was that I did use SQLAlchemy Core to write some pretty complicated queries, in addition to using SQLAlchemy ORM."

Re: What ORMs have taught me: just learn SQL

#162

Post by jl6 »

makmanalp wrote: post #33

Over and over I keep finding that just an ORM is not enough, but raw SQL is hideous in a different way. ORMs map nicely when you are indeed modifying objects, but somethings don't map well that way. So don't map them that way! What we need is a low level abstraction layer alongside the ORM. The main problem with raw SQL is that what you really want is a genuine programming language. You almost want programmatic acces…

>The main problem with raw SQL is that what you really want is a genuine programming language.

In my experience, if you take a data-oriented approach (where you add value through structuring/modelling data), SQL is just fine. If you take only an application-oriented approach (where you add value by making use of data), you may be tempted into viewing the database as a dumb object store, and SQL starts to creak.

Having said that, in the cases where I have wanted composable SQL, SQLAlchemy has done a fine job.

Re: What ORMs have taught me: just learn SQL

#163

Post by ddispaltro »

terhechte wrote: post #27

I used to write raw SQL for many years, then, around 2005 switched over to ORMs in order to be able to target different databases, have a nice model, etc. Lets be honest here, the ease of justing doing: p.username = "Carl" p.age = 33 p.save instead of "update users set username=:username, age=:age where id=:id" has a ton of advantages. For one, some sort of syntax or type checker is actually trying to understand your…

I really like scalikejdbc [1] over slick especially using the parameterized queries. They handle it more elegantly than slick which even for Scala has a pretty meteoric learning curve. I also really like how I don't need specific dialect to get it working on newer restricted sql db's/olap systems like phoenix [2] or presto [3].

[1] http://scalikejdbc.org/ [2] http://phoenix.apache.org/ [3] http://prestodb.io/

Re: What ORMs have taught me: just learn SQL

#164

Post by lukaseder »

Interestingly enough, no one ever listened to Gavin King (creator of Hibernate), when he said that you shouldn't use an ORM for everything.

It is relatively easy to draw a clear line between using:

  * ORMs for domain model persistence (complex, stateful CRUD)
  * SQL for relational model interaction (complex, stateless querying)
Bottom line:

  * Don't use ORMs for querying
  * Don't use SQL for complex CRUD

Re: What ORMs have taught me: just learn SQL

#165

Post by bni »

kayman wrote: post #126

Learning ORM without learningn sql is great for beginnners. Ex: Django Framework for new comers. But like any abstracction, learning SQL will allow you to optimize w raw sql as needed. Its like trying to learn coffeescript without learning JavaScript. It always helps to learn from bottom up..

I disagree. Some of the worst horrors Ive seen over my carrer is systems written by developers using an ORM "So they don't have to learn SQL".

I find some folks has these really strong beliefs:

1. SQL is for DBAs, Im a developer! 2. SQL is legacy and if you use it directly you must be some kind of luddite. 3. I tried to write some SQL once by copy/pasting someone else's, and I got really strange results back. Nevermind that I dint try to actually understand the query.

Re: What ORMs have taught me: just learn SQL

#166

Post by lukaseder »

doctorpangloss wrote: post #39
makmanalp wrote: post #33

Over and over I keep finding that just an ORM is not enough, but raw SQL is hideous in a different way. ORMs map nicely when you are indeed modifying objects, but somethings don't map well that way. So don't map them that way! What we need is a low level abstraction layer alongside the ORM. The main problem with raw SQL is that what you really want is a genuine programming language. You almost want programmatic acces…

> You almost want programmatic access to the SQL AST, so you can generate syntax as opposed to concatenate strings together. Kind of like a DOM API, but for SQL. I think this is the appeal of MongoDB's driver on Node: You really do have programmatic access to the AST, insofar as the microlanguage is just a plain old Javascript object. Though SQL is more universal, Mongo's approach definitely has thought hard about th…

You can do this with jOOQ (http://www.jooq.org), provided you're actually creating the original query using jOOQ...

Unlike MongoDB, the DSL really resembles SQL, which makes it far easier to read / write queries for someone who is accustomed to SQL

Re: What ORMs have taught me: just learn SQL

#167

Post by weavie »

Most people who complain about ORMs complain about the input - the querying aspect of them. And it is true, particularly for complex queries ORMs can be pretty hideous. If a query goes beyond a certain level of complexity I would much rather replace them with a series of views or stored procedures. ORMs are not good for complex queries.

For me, where ORMs do shine is with their output. If you have two tables, A and B with one to many relationships between them - with pure SQL running a join on these tables will return a single result set. Table As data will be duplicated for each row of B. With an ORM you can get back a single object A containing a collection of B's rows.

This is enough reason for me to reach for an ORM for anything but the simplest of problems.

Lazy loading generally also comes for free.

Re: What ORMs have taught me: just learn SQL

#168

Post by junto »

This is why I love Dapper micro-ORM in the .NET world. It gives you the best of both worlds.

You are still close to SQL, but you get objects back. Started by Sam Saffron (StackOverflow) and used by StackOverflow themselves, it is fast, well written, concise and easy to use.

Basic usage:

   IEnumerable resultList = conn.Query(@"
                    SELECT * 
                    FROM Account
                    WHERE shopId = @ShopId", 
   new {  ShopId = shopId });
Performance of SELECT mapping over 500 iterations - POCO serialization:

   Hand coded (using a SqlDataReader)	47ms
   Dapper ExecuteMapperQuery	49ms
   ServiceStack.OrmLite (QueryById)	50ms
   PetaPoco				52ms
   BLToolkit				80ms
   SubSonic CodingHorror		107ms
   NHibernate SQL			104ms
   Linq 2 SQL ExecuteQuery		181ms
   Entity framework ExecuteStoreQuery	631ms
- https://code.google.com/p/dapper-dot-net/

- http://www.tritac.com/bp-24-dapper-net-by-example

- http://en.wikipedia.org/wiki/Dapper_ORM

- http://code.google.com/p/dapper-dot-net/source/browse/Tests/...

Re: What ORMs have taught me: just learn SQL

#169

Post by rollthehard6 »

Disclaimer - I have little know how of ORMs and haven't developed anything more than scripts for a long long time.

What happened to 4GLs when the web came? i.e. It looks a little like this would be a solved problem if there were a open source web 4GL language that integrated SQL into say, python as deeply as SQL is intergrated into Oracle PL/SQL or Ingres OpenROAD whilst providing a decent web app framework to go along with it.

Post reply on HN