"Compose" here doesn't mean "to write". It means to build up a database query in multiple steps, gradually adding (or removing) restrictions or relations dynamically in response to input. So one method might add a certain restriction that will get translated to a part of the where clause. Another might change which fields are being selected for or joined on or add transformations to the field. Meanwhile, the developer doesn't have to worry about special cases, or whether you need an AND or an OR, or how to go about wrapping a field in the SELECT clause after the fact. These methods might be in totally different contexts in the underlying code, and it would not be possible to make that style of query composition possible without abstracting away the SQL syntax. And that's exactly what an ORM does for you.

Oh I know what compose means. Having written a ton of stored procedures for large scale web applications I've never found the need to dynamically build such queries. Instead I focus on what I need a specific query to do, provide the options to a stored procedure or function (depending on the RDMS) and that's it. Much like unix with simple programs that do very niche things I look at procedures much the same way.

Don't get me wrong I see the simplicity in it from the ORM side I've just never had a good experience with ORM tools. Having worked directly against many RDMS and key value stores at least for me I don't see much value in them.

But anything that helps get a product out the door more power to ya, in my opinion.

What do you do when you want e.g. user controlled sorting and filtering?

I just implemented this recently in my job, but it was even tougher than that; I was joining dynamically created tables, and the number of joins depended on criteria. I ended up building my own AST to represent filters, and compiling to SQL. Every extra table join hurts performance - typically filters are implented as joins against a filtered sorted limited subquery returning ids, so fewer different table joins means less data to pull in when evaluating filters even when all the data is needed for the results page.

Point being, I had to compose complex dynamic queries and SQL was not a particularly pleasant target language to target directly. Abstractions (like asts) help with composition.

You can use a CASE-statement for each possible filter and apply it only if the filter value(s) are non-trivial.

    -- Parameter :filter
    --
    AND CASE WHEN :filter IS NOT NULL THEN
        table.column = :filter
    ELSE TRUE END

This has zero cost in the case where :filter is unset.

The filters are predicates, not values.

> What do you do when you want e.g. user controlled sorting and filtering?

Sorting is easy. The stored procedure just accepts a sorting flag, add it into your query and your done. Filtering though, I could think of a dozen types of things you may want to filter on and I still may not hit your use case so it's hard to say how to do it based on what data is stored in the database, how it's stored and which RDMS you're using.

I've primarily done this type of work in MS SQL where I've built some pretty complex queries but I was always able to replace dynamically generated SQL with SQL that could generate a cacheable query plan.

Not saying there won't be edge cases and I don't know how your filtering worked so I don't have any type of satisfying answer for you. I would just be surprised if it wasn't doable somehow (I've seen some crazy shit in SQL queries :D)

The job I have in mind was basically implementing a subset of Excel's sorting and filtering capabilities into a paginated table view on a SPA web page.

Sorting has a couple of wrinkles. When Excel sorts rows, it changes the in-memory order; its sort is also stable. That means if you sort by column C, then B, then A, you're effectively doing "order by a, b, c". Seems simple enough. But that's not the only wrinkle in this problem.

Not all sortable columns are in the same table. If you do something naive like "select x.* , y.* from x join y [...] where [...] order by x.a, y.b limit 10 offset 20000", you're doing too much work - you're sorting a wide result set. It's normally better to do your pagination in a subquery and join against it, like this:

    select x.*, y.* from x join y [...] join (
      select x.id from x join y [...] where [...]
      order by x.a, y.b limit 10 offset 20000
    ) f on f.id = x.id
    order by x.a, y.b

Depending on the body of the where clause, you want to join the minimum set of tables to include just enough data to evaluate the page of ids - every join has a cost.

The body of the where clause is another story, it's pretty much entirely arbitrary. The user may choose to filter on any column, just like Excel autofilter. Creating good indexes up front isn't feasible - you don't know how many columns to include in any one index, they take time to create and they're not free in disk storage either. And the filtered columns span multiple tables; the final result set may contain several hundred columns across all these tables.

So what I did is take the criteria - the filter clause, which might look something like this:

    (and (or (eq (field 'x.f1') "foo")
             (eq (field 'y.f2') "bar"))
         (between (field 'm.timestamp') "date1" "date2"))

And turn it into a where clause. I analyze the filter tree to determine the minimum set of joins required to access all the fields needed by the filter and sorting criteria. I can then put that into a subquery to join against.

There are more wrinkles. To give a good experience, we need to populate a dropdown of potential filter values, like Excel autofilter. Potential filter values need to be filtered themselves - we only display filter values for that are potentially visible given the current filter. They also need to be searchable, because there may be millions of them. And they may include joins, like when you want to filter by tags or some other 1:N style relation, like row:tags. There are a bunch of different shapes to the different queries, but there are common composable underlyers to them.

All the above runs on mostly unindexed tables - we're relying on pure query compute performance within the designed data limits (less than 3 million rows). Sub-millisecond query response times aren't the goal here; we're supporting the user doing reasonably complex data manipulation operations with worst case performance measured in single-digit seconds.

(This is all in MySQL, because reasons that include business model constraints.)

So you don't even build dynamic queries in the stored procedures themselves (e.g. with string concatenations and EXECUTE)?

That's where I usually get fed up with stored procedures, as pseudo-ADA ain't a nice language for list and string manipulation. I'd rather do that in a language better suited, and that quickly leads to query builders…

Stored procedures are no more composeable than SQL strings are. Now it's certainly easy to say that I haven't used SQL directly. But I did spend years working that way (though of course this statement shouldn't give you much confidence by itself).

Ultimately an ORM can help you to build more maintainable and resilient (to changing market conditions) software than direct SQL. Diesel in particular can catch mistakes that SQL strings cannot.

I do understand why a lot of people have reservations about ORMs. I share a lot of them. I've maintained Active Record for about 2 years now, and learned a lot from doing so (note: This does not mean that I like AR or want this to be anything like it).

TL;DR: Your points aren't wrong. Diesel is different. Maybe give it a shot. ;)

> Stored procedures are no more composeable than SQL strings are[...]Ultimately an ORM can help you to build more maintainable and resilient (to changing market conditions) software than direct SQL.

I couldn't disagree more. Creating the separation between SQL DB and web application allows you to maintain, deploy, fix and upgrade things independently and it's easy (maybe not quite as easy as an ORM would make it but not really harder either; you're going to have to know how SQL works anyway if you want to effectively optimize and secure your data).

Not only that but you can provide far better security controls because you can limit certain users access to certain procedures, never direct access to tables and various other things and if you don't do any dynamic SQL in the procedures itself you not only never have to worry about SQL injection but unless there is a critical vulnerability in the SQL system itself it's going to be impossible to conduct an injection attack.

Yeah you can separate your code to make it easier to deploy separately to get a similar benefit and yeah your code can prevent SQL injections too but it's so much more work, more possible points of failure and the differences between databases can really bite you in the ass when you do everything generically. SQL is fun :)

> TL;DR: Your points aren't wrong. Diesel is different. Maybe give it a shot. ;)

Yeah I'm not saying NEVER use ORMs; they're great for creating things quickly that don't need to scale. But after my experience with them I'm highly skeptical using them outside of that type of use case. Of course you can scale ORMs but I've never seen it done well.

I think we're violently agreeing in a lot of ways. :)

I'm curious what you mean by scale. Are you referring to performance, or code size? If you're referring to performance, you'll probably find it interesting that we out-perform or are competitive with ideomatic C.

By scale I mean performance though I'm not sure the application's language matters as much as the SQL that's being generated and run against the database. I've just never had good luck with ORMs generating the best, most optimized SQL for the query I'm trying to run and I've known a few people who used an ORM until they started getting heavy traffic, tested out talking directly to the RDMS, found it to be more performant and end up dropping the ORM.

Don't get me wrong ORMs can certainly be performant. I just haven't seen or know anyone who used it in a manner that scaled to thousands of concurrent users and, at the same time, didn't have a lot of pain trying to do that with the ORM.

I think this conversation shows a mismatch between two different philosophies about how to use the database. The traditional way of doing things you've described involves multiple different applications connecting to the database. Data access is restricted by locking their database users down to specific APIs exposed by stored procedures. Non-trivial logic lives in the database in order to present these APIs.

A lot of systems lately are being built in a different way, that is, only one application connects to the database. This application presents an API (ReST, protobufs, thrift) for other apps that need that data. It handles access control and business logic, using the DB as a backing store. That means you don't have to worry about dababase user access control, because you don't have more than one user.

I don't have a strong opinion about either of these, but the latter way of working is how we do things at my current job (reasonably large payments software company), and it does work quite well. One advantage I appreciate is that there isn't arbitrary business logic sitting in the database where it can be changed on the fly - I wouldn't consider that a feature.

I'll make a stronger statement - stored procedures are generally not composable in production.

At least in Postgres they act as an optimization fence. In practice this means you can use them as an abstraction layer only in very limited ways where they happen to fit into a decent execution plan.

SQL is hard to programatically compose. At the very least, you have to use a Lang/SQL.

That's a new term to me - lang/SQL?

I guess he means a procedural language for sql, like PL/Pyton, PL/V8, etc., so PL/

Oh god, ick. I've used T-SQL and Pl/SQL procedurally and hate them with the burning passion of many suns.