HN Explorer

The ORM and SQL issue we have

4 points by brainless ↗ HN
Hello everyone, good morning from the Himalayas.

There was a post yesterday about an ORM and there was the obvious debate on why ORMs overall are a bad thing. I had posted that the experience of ORM varies by language. A language needs to support run-time manipulation of the definitions of schema to then allow shuttling of data from native types in that language to SQL.

But it does not stop there. If you use a query builder it goes a long way, sure. If you write SQL, it is a lot worse. What happens when your data is the source of truth for building rich query experience? Say we have 12 types of taxononmy (Size, Region, Price, Supplier, ...) and the user can filter dynamically? Also your environment (where is the user currently querying from) determines the set filters.

This is not easy to solve directly with SQL. How do you loop over all the incoming filters? How you extract data coming in from HTTP requests and bind them to an SQL query? Manually write each possible combination? This falls apart quickly and we need a better abstraction for SQL itself, not the underlying data. SQL was created for database users to sit and type out queries. The query parameters were known at the time of typing the SQL. This is mostly NOT how it is used in (backend) applications. SQL is being dynamically generated all the time. So the (backend) application strives to have a complete understanding of the model, relations, data types, constraints.

There are other advantages when you have the entire data definition in your programming language: CRUD becomes easier. Migrations also can be written in the programming language instead of SQL, although I have always appreciated writing SQL migrations too.

I really hope the community keeps experimenting and trying new ways to implement ORMs or something totally new - we need this. My 2 Rupees.

5 comments

[ 5.1 ms ] story [ 33.6 ms ] thread
brainless
One might say SQL is the issue, why do we simply not have a better protocol between languages and storage. I do not know the answer to that. I am simply stating that if you enforce SQL as the language to define your data and manipulate, then you are over optimizing, from the point of view of the application. NOT from the point of the data store. As a software developer who is focused on building products, I need the power shifted to the application. That abstraction helps build applications safely. Surely there will be edge cases because there is an impedance mismatch. We tackle that when we get there.
sscarduzio
The declarative nature of SQL has a far higher impedence mismatch with imperative programming languages.

Maybe try fixing the language: go fully funcional with Haskell or type level Scala.

taffer
Dynamic filtering and sorting can be performed using static SQL. In the following example the parameters :filter_region, :filter_supplier and :order_column are assumed to be defined by the user:

    SELECT *
    FROM example
    WHERE
            coalesce(:filter_region   = region,   TRUE)
        AND coalesce(:filter_supplier = supplier, TRUE)
    ORDER BY
        CASE 
            WHEN :order_column = 'supplier' THEN supplier
            WHEN :order_column = 'region'   THEN region
            ELSE ''
        END;
Compared to dynamic SQL, there is no possibility for SQL injections here and you don't have the mental and performance overhead of using an ORM. Another interesting alternative to an ORM is jOOQ, basically a query builder and lightweight ORM that generates Java classes from the database schema: https://www.jooq.org/
brainless
Have you considered that "filter_region" will itself not be know in advance? So if there are 12 attributes that I can filter with, how do you propose to generate any combination of them with SQL?
taffer
Yes, filter_region, filter_supplier and order_column are parameters.

The filter parameters are combined using AND, or if they are NULL, they are ignored. For example, you could set filter_region = 'Oceania' and filter_supplier = 'Acme Corporation' or any combination of them.