News for hackers. Stuff that matters.
Re:What ORMs have taught me: just learn SQL by makmanalp on Monday August 04, 2014 @05:37PM (#8134191)
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 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.

Let me give you an example from what I'm working on now. So let's say you have a generic query for fetching monthly aggregated import / export values between countries. Sometimes you want to filter by one country. Sometimes you want to filter by 10 countries. Sometimes you want to use the column containing the inflation-adjusted value instead of the regular column. Sometimes you want the average export instead of the total export.

With a low level abstraction layer, I can do stuff like already_complicated_query.filter(another_param==5). Or I can write a function that does get_world_trade_aggregate(country="USA", aggregate="average") and it'll generate the right query for me. But then that's not even all, if I have to modify or filter that query further in some other part of the code, in some unexpected way totally doable. It's less often that I have to write a whole new function or duplicate code.

So the point isn't the abstraction, it's the fact that it makes queries modifiable and composable, which is something you just can not have in raw sql. So given a query, I can decide to add something to it or change it without having to change the text based SQL. You know how it's good practice to split your 1000 line function into smaller, modular pieces? With this you can do that with your ginormous SQL query too. AND it's (well, almost) guaranteed to generate correct and safe SQL.

This also makes everything much more maintainable. E.g. If I change table names or migrate fields, often it's a non-issue and there is a single place I need to change that. But with raw SQL you have to go in and do a ctrl-f and hope you catch everything, but often you don't because someone decided that that field name should come from a variable or something.

Raw SQL spits into the face of everything we've learned about programming languages in the past 60 years.

Anyway, the neat thing is that this magical system exists, as the SQLAlchemy Core (http://docs.sqlalchemy.org/en/rel_0_9/core/tutorial.html). Enjoy!

Re:What ORMs have taught me: just learn SQL by GeoffWozniak on Monday August 04, 2014 @06:57PM (#8134527)
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 fact that you need to know SQL to use it effectively.

I agree with your assertions that just an ORM is not enough, raw SQL is hideous, and that raw SQL spits in the face of programming language advancements. Sadly, it's the assembly language of databases and, unlike CPUs, doesn't have a good abstraction model.

Re:What ORMs have taught me: just learn SQL by zzzeek on Tuesday August 05, 2014 @12:28AM (#8135515)
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 only selects from the relational model you've created, if your model has N number of foreign keys and the objects you're querying from span M of them, that's how many it will use, there is no "overuse" or "underuse" of foreign keys possible; "data retrieval": SQLAlchemy's Query object maps to SQL joins in a fully explicit fashion, no bending over necessary (see http://docs.sqlalchemy.org/en/rel_0_9/orm/tutorial.html#quer... ); "Dual schema dangers" - use metadata.create_all() in one direction, or metadata.reflect() in the other, the "dual schema" problem is only in systems like Hibernate that don't offer such features (and actually it does, just not as easily); "Identities" - manual flushing and hand-association of primary key values to foreign keys is not necessary, use relationship(); "transactions"- ORMs don't create this problem and only help to solve it by providing good transactional patterns and abstractions).

I'd appreciate if you amend your article to clarify that you only used SQLAlchemy Core, if this is in fact the case. Your key point that one needs to know SQL in order to use an ORM is absolutely true. However, the value of the ORM is not that it hides awareness of relational databases and SQL; it is in that of automating the task of generating database-specific SQL as well as that of mapping SQL statement execution and result sets, specific to the database driver in use, to object-oriented application state, and keeping these two states in sync without the need for explicit and inconsistent boilerplate throughout the application. I discuss this in many of my talks (see http://www.sqlalchemy.org/library.html#talks).

If you worked in soda bottling company, you probably still know how to fill a bottle of soda by hand. It's the complex machinery that does this automatically which allows this task to scale upwards dramatically. Configuring and using this machinery wouldn't make much sense if you didn't understand its fundamental task of putting soda in bottles, however. The situation is similar when using an ORM to automate the task of generating SQL and mapping in-application data to the rows it represents. Removing the need for "knowledge" has nothing to do with it. The goal instead is to automate work that is tedious and repetitive on a manual scale.

Re:What ORMs have taught me: just learn SQL by chipotle_coyote on Tuesday August 05, 2014 @02:25AM (#8135790)
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 by snarfy on Monday August 04, 2014 @07:07PM (#8134569)
>The main problem with raw SQL is that what you really want is a genuine programming language. 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.

What you described is basically EF+LINQ.

    var query = context.Users.Include("Users.Group").Where( u => u.UserType 
    == UserTypes.Basic).Select( u => u.Group);
That's going to spit out a SQL statement that joins Users to Group and selects all groups where the UserType is UserTypes.Basic. You can even do ToTraceString() to see the generated statement.
Re:What ORMs have taught me: just learn SQL by taspeotis on Monday August 04, 2014 @08:31PM (#8134881)
> You can even do ToTraceString() to see the generated statement.

And then if you're a control freak, feed that generated query into ScriptDom [1].

[1] http://msdn.microsoft.com/en-us/library/microsoft.data.schem...

Re:What ORMs have taught me: just learn SQL by virtualwhys on Monday August 04, 2014 @09:30PM (#8135082)
The same in Scala + Slick would be:

    val q = Users.filter(_.userType == UserTypes.Basic).map(_.group)
Although I'm not sure what the context.Users.Include bit in the EF example is doing. Both will generate statement at compile time, and I assume EF queries are composable as is the case with Slick.

Slick's readability does suffer though with more complex queries -- unfortunate they strayed away from SQL semantics in favor of Scala collections. We'll see where it goes...

Re:What ORMs have taught me: just learn SQL by snarfy on Monday August 04, 2014 @09:39PM (#8135111)
Include() on an ObjectContext will return the corresponding foreign key objects. It's a poor man's join to related entities. There is also Join(), but that will join by anything.
Re:What ORMs have taught me: just learn SQL by virtualwhys on Tuesday August 05, 2014 @07:08AM (#8136309)
Ah ok, I see. Slick actually does have a nice shorthand for fkey joins:

    val q = for{
      ur <- UserRole; u <- ur.user; r <- ur.role
    } yield(ur,u,r)
which would produce semantic equivalent to:

    select ur.id, ur.a, ..., u.id, u.a ...
    from userrole ur
    join user u on u.id = ur.userId
    join role r on r. id = ur.roleId
grouping and sorting is where Slick becomes less readable SQL-like DSL and more boilerplate DSL full of meaningless tuples O_o
Re:What ORMs have taught me: just learn SQL by lloeki on Tuesday August 05, 2014 @06:20AM (#8136231)
> The main problem with raw SQL is that what you really want is a genuine programming language. 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.

Congratulations, you just described Arel.

I liberally use rails/active_record where it shines (operating on a single record, or writing composable scopes) but very often find myself leveraging Arel (accessible via YourModel.arel_table) to generate a carefully crafted SQL AST.

At my current job we have two gems leveraging this power: "Massive Record", allowing one to perform bulk operations (insert, upsert) on huge lists of records in an efficient way (instantiating hashes instead of full-blown ActiveRecord instances), and "Chains", which allows one to handle authorization at a per-entity or per-record level with a iptables-like system. There is no way the generated queries could be sanely written by hand, nor as concatenated strings. Arel allows us to build highly dynamic queries while still tuning for performance. Database independence comes as a bonus, and we can easily extend Arel with more nodes, possibly some database specific ones that get selectively added depending on the configured database.

Arel also allows us to write clean and efficient database migrations, where we basically use your typical ActiveRecord faux models merely for datatable reflection to obtain column names and types.

[0]: https://github.com/rails/arel

Re:What ORMs have taught me: just learn SQL by adamlett on Tuesday August 05, 2014 @07:32AM (#8136373)
> Congratulations, you just described Arel.

Unfortunately SQL leaks through Arel's abstractions and make it behave in surprising ways. Arel falls short of achieving the goal of being able to modularise and compose queries. The same is true of nearly every SQL connectivity library (or so this article claims: http://www.try-alf.org/blog/2013-10-21-relations-as-first-cl...).

Re:What ORMs have taught me: just learn SQL by lloeki on Wednesday August 06, 2014 @05:54AM (#8141745)
> Unfortunately SQL leaks through Arel's abstractions

This is voluntary. Arel allows one to write SQL almost as is, only with native types and features that make it possible to compose and aggregate queries. It's not a 1:1 mapping but it's not a blackbox abstraction either since the goal is to finely control your SQL. Of note, Arel is neither ActiveRecord nor ActiveRecord::Relation (which is what you get when you use #all, #where and scopes, sadly often mistaken for Arel)

The article you linked to is wrong, Arel is perfectly able to compose projections and joins, see this gist[0] implementing what the author demands.

[0]: https://gist.github.com/lloeki/2bc0ece35b2ba42b681d

Re:What ORMs have taught me: just learn SQL by jl6 on Tuesday August 05, 2014 @02:50AM (#8135841)
>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 by akavi on Monday August 04, 2014 @05:44PM (#8134225)
>With a low level abstraction layer, I can do stuff like already_complicated_query.filter(another_param==5). Or I can write a function that does get_world_trade_aggregate(country="USA", aggregate="average") and it'll generate the right query for me. But then that's not even all, if I have to modify or filter that query further in some other part of the code, in some unexpected way totally doable. It's less often that I have to write a whole new function or duplicate code.

If you're working in Ruby, there's also Arel[1]. I have my complaints, but it seems to be the exact same thing: Composable, programmatic access to SQL from your application code.

[1] https://github.com/rails/arel

Re:What ORMs have taught me: just learn SQL by jeltz on Tuesday August 05, 2014 @05:57AM (#8136198)
If you are working with Ruby Sequel is a way better tool for building SQL queries.

http://sequel.jeremyevans.net/

Re:What ORMs have taught me: just learn SQL by twic on Tuesday August 05, 2014 @05:04PM (#8139409)
> Kind of like a DOM API, but for SQL.

In Java land, the big persistence API is JPA. It's an API for ORMs, but it has features which go a long way towards this - just from the ORM side rather than the SQL side.

Firstly, it has its own query language, JPQL, which is basically a large subset of SQL (on the order of MySQL's SQL rather than PostgreSQL's, eg no window functions) with some added niceties, such as for joining (eg "select u from User u where u.manager.location.country.code = 'UK'").

Secondly, it has something called the 'criteria API' [1] which is basically a DOM for JPQL. So not SQL, but close. I worked on a large, byzantine financial application where we did a lot of building up of queries using this API. It worked pretty well as a way of separating the logic for different aspects of queries: we had one bit of code that set up the core of a query to limit results according to the user's permissions, another bit that would apply temporal filters, another bit that would apply subject filters, an optional bit that would take a query for trades and turn it into a query to find stocks involved in those trades, etc.

The API itself is not massively typesafe (the programmer specifies an attribute with a string name and type, so they can get both the name and the type wrong if they like), and it is rather verbose (of course). There is an extension called the 'metamodel', where you can automatically generate classes which have ready-made bits and bobs which describe your schema, and saves both typing and errors. Using that, a query to find all trades on a particular stock could look like:

        @PersistenceUnit EntityManager em; // someone will dependency inject this

        CriteriaBuilder cb = em.getCriteriaBuilder();
        CriteriaQuery<Trade> q = cb.createQuery(Trade.class);
        Root<Trade> trade = q.from(Trade.class);
        q.select(trade).where(cb.equal(trade.get(Trade_.stockTicker), "NXJ"));
        TypedQuery<Trade> = em.createQuery(q);
Which is is still verbose and stilted in the grand traditions of High Java, of course.

There are some third-party libraries for building JPQL strings which can be used for much the same purposes. Liquidform is quite good:

        Trade t = alias(Trade.class, "t");
        SubQuery<Trade> q = select(t).from(Trade.class).as(t).where(eq(t.getStockTicker(), "NXJ"));
        TypedQuery<Trade> = em.createQuery(q.toString(), Trade.class);
A key thing here is that the alias function takes a class and, via bytecode sorcery, produces an object which conforms to the type of that class, but which isn't really an instance of it, but instead has methods which act as query primitives. It works a lot like things like Mockito and so on.

The new hotness, though, is Jinq, which uses Java 8 lambdas to imitate the great .net LINQ. I haven't used it, but i believe the query would look something like:

        List<Trade> = streams.streamAll(em, Trade.class).where(t -> t.getStockTicker().equals("NXJ")).toList();
Again, this uses bytecode sorcery under the hood, but this time, it does it by pulling apart the lambda to determine the condition it expresses, and then constructing a corresponding query string. A different knid of sorcery - in AD&D terms, divination rather than conjuration.

[1] http://docs.oracle.com/javaee/6/tutorial/doc/gjitv.html

Re:What ORMs have taught me: just learn SQL by matrix on Monday August 04, 2014 @11:22PM (#8135356)
If you are using Java, I suggest taking a look at JOOQ. JOOQ implements SQL statements as an AST:

http://blog.jooq.org/2014/07/28/jooq-tip-of-the-day-reuse-bi...

I've been using JOOQ lately and liking it a lot.

Re:What ORMs have taught me: just learn SQL by dharbin on Monday August 04, 2014 @05:51PM (#8134259)
Take a look at my library, swigql[1]. You can create a base query and then extend that query however you want via template inheritance.

[1] https://github.com/civitaslearning/swigql

Re:What ORMs have taught me: just learn SQL by makmanalp on Monday August 04, 2014 @06:06PM (#8134331)
My personal opinion on such libraries is that they're cool, and I think they do make life easier but they're just a more advanced form of string concatenation. No notion of SQL types, syntax or structure. To follow the template analogy, a low level abstraction library allows me to not only change the variables but also change the template programmatically. And not by concatenating more stuff onto the template either :P

Prepared statements, by contrast, are also a form of string concatenation. And they have some understanding of type and syntax, but only because they learn them from the server:

http://www.postgresql.org/docs/9.2/static/sql-prepare.html

http://dev.mysql.com/doc/refman/5.0/en/sql-syntax-prepared-s...

Re:What ORMs have taught me: just learn SQL by jimktrains2 on Tuesday August 05, 2014 @08:53AM (#8136627)
> Prepared statements, by contrast, are also a form of string concatenation. And they have some understanding of type and syntax, but only because they learn them from the server:

Prepared statements are NOT string concatenation. The server parses the SQL, creates what internal representation it does, and then uses the parameters more as arguments to that executable statement than checks them and sticks them in. At no point does a SQL statement exist with the parameters and SQL.

Re:What ORMs have taught me: just learn SQL by doctorpangloss on Monday August 04, 2014 @05:47PM (#8134242)
> 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 the balance between abstract and concrete that me and my other developers find very intuitive. So intuitive that we use their microlanguage way outside applications in ORM, like for sorting, filtering and object updates.

Re:What ORMs have taught me: just learn SQL by jasode on Monday August 04, 2014 @06:48PM (#8134500)
>Though SQL is more universal, Mongo's approach definitely has thought hard about the balance between abstract and concrete that me and my other developers find very intuitive.

I think you've got the causality and conclusion backwards here. MongoDB's easy programming API is a consequence of its storage layout on disk -- the JSON/BSON bytes on disk. From that principle, you naturally get an "ORM" type of API exposed in the programming language basically for free. I'm saying you don't have to do a lot of theoretical computer science type of research and pondering to get a disklayout+API that looks like that. In fact, the early 1960s mainframes laid out data records as fully denormalized (similar to JSON/BSON) before the relational DBs became popular in the 1970s.

But, if your app starts expanding into complex data that's not 100% embedded as fields within one document, you start needing to do "relational" type of joins across documents. And those joins will manifest itself as extra programming code/logic on the client side. You're still paying for added complexity. You're just paying in a different way from the ORM+RDBMS programmers.

Many MongoDB projects want it to perform more like relational. Likewise, many RDBMS want it to act more like an object hence ORMs. The impedance mismatch looks like irreducible complexity.

Re:What ORMs have taught me: just learn SQL by lukaseder on Tuesday August 05, 2014 @03:30AM (#8135909)
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 by walshemj on Tuesday August 05, 2014 @10:10AM (#8136939)
Have you not heard of PL/SQL? or its equivalent for other variants of SQL.