Even if it's easy to use, I can't imagine it'd take less time to read the documentation than to write some SQL. And then there's another dependency; Another thing to check if there's performance issues, another attack vector.

For example let user decide which columns to select, in which order to fetch the rows, which table to query etc. Or not even let the user decide but decide based on some config file or the system state.

You end up with a bunch of string manipulation that is fragile and does not compose well.

What solution is there except from simulating SQL via your languages nestable data structures?

Still fundamentally manipulating SQL text (which is a feature as I don't want to learn a full DSL), but it handles wrangling embedded placeholders while you're composing stuff and some other common compositional tasks. It's worked well for me anyway but I'm under no illusions it'd be right for everyone.

Not an original concept regardless; my original version of this was in Node: https://github.com/bdowning/sql-assassin, but a few years after I wrote that (and mostly didn't use it) I found https://github.com/gajus/slonik which was very similar and much more fleshed-out; I rolled _some_ of its concepts and patterns into sql-athame.

Alas, SQLite doesn't have them, so query building it is.

Simply stated the problem is: Viewing code as data (in a lisp sense) and transforming arbitrary data into a query that can be executed.

Stored procedures are the slipperiest slope I've seen as a developer.

If you have good knowledge and experience in the language your preferred version of SQL implements, that's good. If you just have people that understand how to optimize schemas and queries, you might find that you encounter some of the same problems as if you shelled out to somewhat large and complex bash scripts. The value of doing so over using your core app language is debatable.

So, the question is, does your team know SQL/PSM, or PL/SQL, or PL/pgSQL, or some other variant, and how well.

1: https://en.wikipedia.org/wiki/SQL#Procedural_extensions

That said, I wasn't making a case about replacing your DBMS. I specifically avoided that because yes, most people stay with what they know and used, and even if they switch, they switch for a different project, not within the same project. There are some cases where multiple DBMS back-end support is useful, but I think that's a fairly small subset (software aimed towards enterprises which wants to ease into your existing system and note add new requirements, and open source software meant to use one of the many DBMS back-ends you might have).

My actual point is more along the lines of:

- Most DBMS hosted languages I've seen are pretty shitty in comparison to what you're already using.

- The tooling for it is likely much worse or possibly non-existent.

- You are probably less familiar with it and likely to fall into the pitfalls of the language. All languages have them, shitty languages have more. See first point.

- If you accept those points and the degree to which you accept them should definitely play a role in deciding to use stored procedure you've written in the language your DBMS provides.

- I think trade offs are actually similar to what you would see writing chunks of your program in bash and calling out to that bash script. People can write well designed and safe bash programs. It's not easy, and there are a lot of pitfalls, and you can do it in the main language you're writing probably. Thus the reasons against calling out to bash for chunks of core are likely similar to the reasons against calling a stored procedure.

To me, the shitty procedural languages you mention are just for gluing queries together. The important stuff happens in SQL and the simplicity of keeping it all in the database is worth it.

There are a lot of data retrieval where a stores procedure will will save you incredibly amounts of resources because it gives you the exact dataset you need exactly when you need it.

With SQLite, your entire application IS the process, and your SQLite data moves from disk to your app processes RAM.

I think the SQLite model is much better as you get to use your modern language and tooling (which is better than the language used for stored procedures which has not changed in 20 years, and is generally a bear to observe, test, develop).

I've gone down this rabbit hole... you've described the in-house query engine I work with.

Instead of sql, queries are nested json representing a dumbest-possible sql parse tree. It's only a subset of sql features, but it provides, as you point out, composability. By having the query as a data struct, all sorts of manipulations become possible... toggle fields for access control; recovery from dropped or renamed columns (schema has a changelog); auto-join related tables as required; etc.

It's magic when it works but it suffers from the drawbacks noted elsewhere in the thread about traditional ORMS... onboarding new devs, debugging when something goes wrong.

Adding new features to the dsl is a pain because the surface area is not just _the query_ but _all the queries it might form_ when composed with other queries in the system O_o

(Not that there are lots of other devs on my project at the moment, but)

I am managing this by aggressively limiting the size / features of my query builder, so it's (relatively) easy to understand just by looking at the code.

If it ever passes that point, it's probably time to switch to a "real" query builder / ORM.

I'm curious what feature subset you settled with. Ours got as far as aggregate ops, and the complexity of that turned out to be 'hydra'. (Surely I've cut off the last head!)

By that point we were painted into a corner with the in-house query builder... there was no off-the-rack ORM that had semantics for combining queries. (i.e. meld together query A and query B so that the result cells are union or intersection.)

You seem familiar with the problem space, I'm curious what your experience was. What feature(s) made you draw the line and say, 'when we need it we'll migrate to X'? What are your "real ORM" candidates for X to bridge that gap?

We have 2 modes of using that thing.

Tightly bound. Basically you have a bit of query string with question marks in it and you bind out your data points. Either for sending/recieving.

Loosely bound. Here is a totally composed string ready to go just run it. Also a good way to make SQL injection attacks.

Both involve string manipulation.

I think the issue is the ODBC interface does not really map to what SQL is and does. The column binding sort of does. But not table, not where, not having, not sort, etc. So we end up re-inventing something that pastes over that. Building a string to feed into an API that feeds that over the network to a string parser on the other side to decompose it again then runs it and goes back the other way in a similar manner.

fmt ("SELECT foo, bar FROM mytable WHERE id = {}", id);

'id' gets properly quoted, so that's not a concern anymore.

fmt ("SELECT {:v}, {:v} FROM {:v} WHERE id = {}", colname1, colname2, tablename, id);

The :v means "don't quote this, insert this string exactly as specified." It also has specialized handling for NULL values (i.e. it can generate 'IS NULL' instead of '= ...' if the value passed on is intended as a NULL value).

a. Dynamically adding "and SomeColumn like @someValue" to the where clause of the query

or

b. Having the query include all filters but ignore them when they are left blank, such as

`where @someValue is null or SomeColumn like @someValue`

The latter approach does allow you get by without string-building queries for filtering, but tends to perform worse if you have a lot of columns you might filter on.

And you still have to play the string concatenation game if you let the user click column headers to change the ORDER BY.

With a decent ORM, like Entity Framework for C#, any of this is very simple and there is no need to write SQL string manipulation code in your application. But under the covers that is what is happening.

I authored a library for F# that did static typechecking of SQL in strings at compile time. So it could tell you if you, for example, mistyped a column name or selected a column that didn't exist on a table or passed a SQL function the wrong number or type of parameters, etc. That was nice, but it still sucked for trying to compose queries or dynamically add filtering/sorting to a query.

With some runtime query builder most databases will have decent performance for the space between "too much to reasonably load in memory" to "we need a dedicated query service like elastic". Unfortunately taking the example of sort by X then by Y where X and Y are dynamic I don't know of a nice solution in SQL only in MSSQL 2012 or MySQL 5.

At some point, yes, you will want these things. But the basics can get you really far.

I'm particularly proud of the last one I built that still absolutely flies despite doing wildcard querying on text fields (no full text index) with multiple combined queries and a dynamically computed status from aggregate child properties in MySQL 5 with its lack of window functions. The solution was basically dynamically construct an efficient as possible inner query before applying the joins.

To clarify I believe in doing as much as possible in raw SQL and have a fondness for the stored procedure driven system I worked with but this problem remains unsolvable in SQL alone and I just keep encountering it.

Additionally, I think unit testing would benefit from an ORM as well. Instead of calling an actual database, you can easily have an in-memory database with an ORM with mocked data. Though, I suppose that is technically possibly with straight SQL queries as well, but I imagine it's easier with an ORM.

I've even seen one database layer for in memory and a completely different one for real database. That means that your unit tests are not really testing your production code

That said, if that wasn't as much of an issue, or if I still had the patience and typing skills to write a lot of boring code, I wouldn't object too much. I'd also treat SQL as a first class citizen, so dedicated .sql files that the application will load or embed and use. That way, the SQL can be developed, maintained and run outside of the context of your application - editors can execute them directly to a (local) development database.

The sooner we move away from the big lie that is DDD, the more productive we can be.

But at my company, our in house app is written using django but treats the django model asan interface to elasticsearch, Postgres, and redis.

The django model is sub optimal for sql, but it’s a good base for interacting with the system as a whole.

The value of the ORM is what the name implies: mapping your data from rows into something convenient to use in your language.

What people are really complaining about is ORMs that needlessly try to re-invent SQL, but they don't all do this. Someone else brought up Ecto, it's a brilliant ORM because the resulting SQL is always obvious.

Like you say, I find there's way more mental friction dealing with new ORMs and wading through the documentation than there is in just going straight for raw SQL. There's less "magic" that way too which is always nice when you're debugging or optimising things. Different strokes for different folks I guess.

I think ORMs compliment SQL, not replace it. Without ORMs, you end up getting a lot of clunky boilerplate trying to do simple CRUD operations to your data. It's a bit like Greenspun's 10th law: "Any sufficiently advanced RDBMS-backed program contains an ad hoc, informally-specified, bug-ridden, slow implementation of half of an ORM."

But if you're using it to increase productivity for straightforward database calls, it can be a useful tool in a lot of scenarios.

I learned SQL almost 30 years ago and used it in Oracle/Informix/Sybase/IBMDB2/MSSQL/MySQL/SQLite/etc and have written exam questions on SQL for outer-joins, nested subqueries, crosstab, etc.

All that said, concatenating raw strings to form SQL that has no compile time type-checking is tedious and error prone. Thankfully, my "expert" knowledge of SQL does not blind me to the benefits of what a well-written ORM can do for productivity. (E.g. results caching, compile-time validation, auto-completion in the IDE, etc)

The real technical reason why ORMs persist as a desirable feature is the decades-old technical concept of "working memory of field variables" that mirror the disk file table structure.

In the older languages like 1960s COBOL, and later 1980s/1990s 4GL type of business languages such as dBASE, PowerBuilder, SAP ABAP... they all have data tables/records as a 1st-class language syntax without using any quoted strings:

- In COBOL, you had RECORDS and later versions of COBOL had embedded SQL without quoted strings

- dBASE/Foxpro had SCATTER/GATHER which is sort of like a built-in ORM to pull disk-based data from dbf files into memory

- SAP ABAP has built-in SQL (no quoted raw strings required) with no manual typing of variables or structs to pull data into working memory

The issue with "general purpose" languages like Python/Javascript/C++ is that SQL is not 1st-class language syntax to read database data into memory variables and write them back out. The SCATTER/GATHER concept has to be bolted-on as a library ... hence you get the reinvention of of the "db-->memory-->db" data read/write paradigm into Python via projects like SQLAlchemy.

After tediously typing out hundreds of variable names or copy-pasting fields from SQL tables via strings such as ... db.execquery("SELECT cust_id, name, address FROM customer WHERE zipcode=12345;")

... it finally dawns on some programmers that this is mindless repetitive work when you have hundreds of tables. You end up writing a homemade ORM just to reduce the error-prone insanity.

Yes, a lot of ORM projects are bad quality, with terrible performance, bugs, etc. But that it still doesn't change the fact that non-trivial projects with hundreds of tables and thousands of fields need some type of abstraction to reduce the mindless boilerplate of raw SQL strings.

I dislike ORMs that hide database operations from the application code by pretending that in-memory application objects are in concept interchangeable with persistable objects; though just saying that hurts a bit because I don't want to think in terms of objects, but in data.

Query builders are fine if they allow you to build efficient queries in a type-safe and composable manner to get data in and out of the database in the form your application requires at the site of the database query, but I don't want to be forced to pretend I fetch "User" objects if all I really need from the database are the name and e-mail.

Shameless plug, but I just posted a library I wrote (for node https://github.com/vramework/postgres-typed/blob/master/READ...) which pretty much is a tiny layer ontop of pg-node (which is query based / with value parameters) and provides small util functions with typescript support derived straight from postgres tables.

In an ideal world (one I think we are getting very close to) I think we will end up having SQL queries validated in our code against the actual DB structure the same way we have any other compilation error. But until then we'll need to rely on tests or helper libraries, and for the purpose of refactoring and development I find the latter more enjoyable (although still far from perfect).

This library for TypeScript works exactly like this

https://github.com/MedFlyt/mfsqlchecker

Good point about validation. The ideal scenario you propose would indeed be ideal.

if you want to write thousands of SQL queries that naturally derive from an object model of 30, 100, or 800 domain classes or so and would otherwise be thousands of lines of repetitive boilerplate if all typed out individually, then you use a query builder and/or ORM.

different tools for different jobs.

I talk more about this use case here: https://death.andgravity.com/query-builder-why#the-problem

  query = from u in "users",
            where: u.age > 18 or is_nil(u.email),
            select: %{name: u.name, age: u.age}
  
  # Returns maps as defined in select
  Repo.all(query)

Hey everyone, look at this language with this incredibly intuitive and simple syntax, perhaps the most "easy to read and understand" thing you'll see in computing...

...and now here's a bunch of painful stupid garbage that you'll have to learn about, because industry has accreted a bunch of crap around it to make it turn on and work.

  - allow certain apps to work with any sql dialect (though tbh I question the value of this in the age of docker)  
  - makes simple selects easier  
  - enables plugins for web libraries

Haha. In the world outside of HN, big companies enforce specific database servers for reasons such as centralized monitoring, security, compliance and competence. Setting all this aside because of "docker" is classic HN.

Handjamming SQL where statements for arbitrarily deeply nested types is insanity.

Composing a SQL query is often the easiest part. The issue is that throwing that static SELECT statement string in your code has a ton of downsides (as discussed everywhere in this thread).

And fiddling with an ORM is tremendously frustrating when you're just trying to get it to compose the SQL you've already written in your head.

For a dev who's bothered to learn SQL, flipping the thing on its head makes a ton of sense.

Parameters.

A couple of examples:

https://www.psycopg.org/docs/usage.html#passing-parameters-t...

https://www.php.net/manual/en/mysqli.quickstart.prepared-sta...

What can be parameterized depends wildly on the SQL database in question. I haven't used one that could parameterize table names (for use in dynamic JOINs or CTEs) and many cannot parameterize values outside of a WHERE clause. Dynamically selecting which function to call, clauses to add or subtract, and sort orders are just a slice of places parameters don't help.

In short, parameters alone do no eliminate the need for a query builder. A good query builder should appropriately parameterize values as the underlying database supports and hopefully uses a type system or validation to constrict the domain of values it uses to construct parts of the expression that cannot be parameterized.

However, good orms like LINQ in C# really shines in a lot of places.

> A good ORM will enable people to do that easily as well

I agree. EF isn't a terrible ORM and covers many use cases developers encounter when interacting with a database server in your average line of business app. But you do need to keep an eye on the SQL it generates with more complex queries i.e. beware of cartesian hand grenades. Fortunately MS provides some guidance on this kind of thing, but sadly many developers often don't bother to read the docs, and this is why ORM's get a bad name:

https://docs.microsoft.com/en-us/ef/core/performance/efficie...

From a purely learning point of view, it is indeed an interesting read, however.

Yup, good thing to note, thank you!

I've kinda assumed everyone knows about the named parameters some bindings offer, but that's probably not true. Will add a disclaimer.

In the previous articles, I do talk about how this is an upgrade from plain SQL, not from regular full-blown query builders / ORMs.

This statement is about functionality / ease of use, which is fairly orthogonal from (preventing) SQL injection: with plain SQL it's perfectly possible to avoid injection attacks, in fact that's probably the most common and easiest way to do it. In that sense, if anything this is a downgrade from regular SQL.

It likely is.

Ideally, instead of dynamically building queries, one would use stored procedures.

I'm using this with SQLite, which doesn't have stored procedures, so it's an acceptable downgrade for me. Appending strings to lists gets messy quickly, example: https://death.andgravity.com/query-builder-why#preventing-co...

I don't really like this API. SQL is weird because it's written backwards. What does `query.SELECT("one")` on its own represent? A query on any table that happens to have a field called "one"? I know you're not trying to build an ORM, but `Table("table").select("one")` makes a lot more sense for an API since the object `Table("table")` actually has a purpose and would make sense to pass around etc.

    var companyNameQuery =
        from cust in nw.Customers
        where cust.City == "London"
        select cust.CompanyName;

data['SELECT'].append('one')

I'm intentionally trying to not depart too much from SQL / list-of-strings-for-each-clause model, since I'd have to invent/learn another one.

For a full-blown query builder, I agree Table("table").select("one") is better.

Have used in multiple projects and have found it's the right balance between ORMs and writing raw SQL. It's also easily extensible and takes care of the many edge cases and nuances of rolling your own SQL generator.

Want to elaborate?

Since SQL is ever-so-slightly different across databases, I imagine trying to cover all of them as a single dev is a nightmare (especially if that's not the problem you're trying to solve).

I wrote my own query builder because I know for sure I'm only targeting SQLite. The second I need my feed reader library to work with another database engine I'm dumping my own for something more serious – either a full blown database abstraction layer like SQLAlchemy or Peewee (likely without the ORM part), or something simpler like PyPika or python-sql.[1]

[1]: I talk more about them here: https://death.andgravity.com/own-query-builder#sqlbuilder-py...

Several years ago, I prototyped a relational mapping language based on XPath expressions. The result is a database-, language-, and output-agnostic mechanism to generate structured documents from flat table structurse. Although the prototype uses XML, other formats are possible by changing the RXM library.

The queries might be bidirectional. Given a document and the RXM query that generates the document, it may be possible to update the database using the same query that produced the document. I haven't tried, though.

https://bitbucket.org/djarvis/rxm/src/master/

For learning SQL, my favorite resource to get started:

Become a SELECT star! https://wizardzines.com/zines/sql/

followed by

The Best Medium-Hard Data Analyst SQL Interview Questions https://quip.com/2gwZArKuWk7W

and fiddle: https://kobalicek.com/experiments/fiddle-xql.html

Interesting how all these builders look the same :)

Here's the tl;dr of a real-world example[2]:

    query = Query().SELECT(...).WHERE(...)
    for subtags in tags:
        tag_query = BaseQuery({'(': [], ')': ['']}, {'(': 'OR'})
        tag_add = partial(tag_query.add, '(')
        for subtag in subtags:
            tag_add(subtag)
        query.WHERE(str(tag_query))

    for subtags in tags:
        tag_query = QList('OR')
        for subtag in subtags:
            tag_query.append(subtag)
        query.WHERE(str(tag_query))

[2]: https://github.com/lemon24/reader/blob/10ccabb9186f531da04db...

Here are two examples:

https://death.andgravity.com/query-builder-why#introspection https://github.com/lemon24/reader/blob/10ccabb9186f531da04db...

In general, SQLAlchemy is an excellent choice; in my specific case (single dev with limited time), the overhead would be too much (even with my previous experience with it).

[0] https://docs.python.org/3/library/sqlite3.html

Seriously, it’s just a joke. Chill out.