Hacker News Reader

> SQL is an impressive technology and has stood the test of time! Yet, we claim that it's not the best tool for application developers who are paid to implement value-adding features for their organizations.

I'm not sure about that. We've recently switched from JavaScript based querying code to mostly raw SQL, and we've reduced our code to about 25% of what it was, and it's much simpler to understand than it was before.

> I see Prisma somewhat analogous to GraphQL on the frontend, where a similar claim could be: "Frontend developers should care about data, not REST endpoints".

I'm not sure about Prisma, but IMO that GraphQL model isn't great. Realistically (for performance, etc) it will make a difference where that data came from. Not for super simple queries, but super-simple queries are super simple to do with REST/SQL anyway. I also feel like this distinction between front-end and back-end developers isn't great.

The GraphQL approach also leads to deployment issue with non-web clients. e.g. it can take a day to get an iOS build released, and there is no way to force users of older clients to update, so this can take months. So fixing a bad query is difficult. With a proper backend you can just swap out the query.

I think the strongest argument for or against an ORM is that it changes the structure of your code, how you reason about your data.

Things like performance, ease of use, reusability, schema generation etc are minor points and not the big issue you need to think about.

Personally I feel that an ORM will lead your project in the wrong direction and your database schema will suffer.

ORMs will usually mix both reading and writing within the same class, but those are not the same thing.

Readers will have different views of your model depending on who they are or what time it is. Writing on the other hand must fulfill your constraints.

Other common mistakes of ORMs is mapping one class with one table, but your data consist of series of relationships, that can’t be explained with a class.

An ORM creates an illusion that your data is actually the class entities in your repository. This can have the effect of creating constraints in your own mental model of your data, thus making it harder to evolve your schema because you are to fixed on how your classes are designed.

Great comment! I’ve been thinking about a tangentially related subject:

TypeScript is like a relational database (and other structural statically typed languages) of your application behavior, allowing you to query the current possible states of your data at any given point in your application code.

I’ve been thinking about this more since TypeScript got template-string generics, and folks have experimented with eg type-checking SQL strings back into application code.

This is the piece that draws me to an ORM: that it brings the “relational model” into application code, NOT the “object” part of an ORM.

Does anyone have any opinions about which language ecosystems create the most effective type-mappings between a RDBMS and application language?

Part of me also wonders if there’s a ton of time lost on mapping between languages like this when the tooling that’s really missing (afaik) is better tooling for producing, consuming, and generally interacting with SQL and db schemas as the data runs through your application pipeline.

> We've recently switched from JavaScript based querying code to mostly raw SQL

Curious what your use case is, because in every codebase I've worked on, people pretty quickly get tired of writing out SELECT * FROM [table] WHERE id=? and UPDATE [table] SET field=? and looping over database cursors all day, and you end up with a half-done, buggy non-ORM.

We're using knex.js, so we're not messing around with database cursors (we get an array of objects), and we don't have to write out the columns for inserts/updates. We also sometimes use the knex query builder for super trivial selects from a single table.

But for anything more complex than that (e.g. SELECT queries joining several tables) we're using knex.raw so the query itself is raw SQL where we're very much thinking in the relational model rather than in terms of objects.

Ahh that makes more sense. My preference has been to use an ORM for your normal CRUD, but write more advanced queries using SQL (or sometimes just write the where clause using SQL, if the select is otherwise simple.) I am not a fan of query builders.

> Yet, we claim that it's not the best tool for application developers who are paid to implement value-adding features for their organizations.

You have two options when marrying RDBMS SQL and OO: either mismanage the relational data so that developers can use a class hierarchy, or stop using a class hierarchy to represent data.

IME, applications come and go. They get rewritten, thrown away, obsoleted. The database, however, is there to stay. Mismanaging the data purely so that the application developers don't have to touch all that icky relational stuff almost always results in more work for less returns.

Prisma does not use a class hierarchy that are mapped directly to tables. Instead we take advantage of structural typing to give you a very smooth developer experience that is faithful to the relational model.

I think SQL gets a lot of undeserved praise that I’m having a difficult time understanding. The only impressive thing about SQL is its prevalence but that’s a pretty poor yardstick unless one thinks that an appeal to popularity is an indicator of quality.

Now let me count the ways in which SQL is bad:

- it composes poorly due to its unwieldy cobolesque syntax

- it is a leaky abstraction revealing a lot of underlying implementation tradeoffs

- it doesn’t properly implement Cobb’s relational model

- it is poorly standardized with a ton of proprietary extensions and alterations present in virtually every implementation

- it is still poorly supported by tools because the model metadata lacks any standard interface to make universal tooling possible

A lot of the praise for SQL is just bandwagon hopping and cargo cult behaviour or a lack of vision by most people of how things could be much better

What are you comparing it to? What is the better alternative?

SQL isn't the only RDMS language, but it is the one with 50 years of incumbency.

Postgres before it was called 'PostgreSQL', used something called QUEL, which is quite a bit cleaner. There is also Microsoft's LINQ.

A typical programming language can express all that SQL can. In a way, an ORM is a cross-compiler from your programming language to SQL.

> A typical programming language can express all that SQL can. In a way, an ORM is a cross-compiler from your programming language to SQL.

SQL has always been a sort a magical, black-box in that you have very limited control over the query planner, and have to hint at it to do the right thing. (I guess `EXPLAIN` allows you to peak in the box a little)

I found [Apache Calcite](https://calcite.apache.org/docs/algebra.html) rather interesting. It provides the primitives that a DBMS is built from.

I would be interested to compare how PSQL is built in comparison. I would love a more layered/pluggable database that everyone can build off of. I think FoundationDB had this approach.

LINQ is pretty much a clone of SQL. Its nice, I agree, but do you think its materially different?

SQL could benefit from being language-integrated, yes. Very few languages can model relational algebra types though, if thats a thing you care about (it certainly is for me).

An ORM API on the other hand is completely meaningless adhoc "whatever works". It doesn't have grounding in anything.

I'm making no statements about ORMs. I'm just picking apart the mindless "SQL is the bee's knees" mantra so prevalent on HN.

The great thing about SQL is the relational model. You typically can't compose parts of SQL queries easily from the programming language calling it, but SQL queries themselves compose incredibly flexibly. The result of of a SELECT query is itself a "relation" that can be queried just like a table.

I do wish someone would create a compile-to-sql language that adds variables and basic conditional support. I think T-SQL (SQL Server) has a lot of this, but it's missing in the Open Source world.

> I think T-SQL (SQL Server) has a lot of this, but it's missing in the Open Source world.

Every database with a procedural language based on SQL (Oracle PL/SQL, Postgres PL/pgSQL, etc.) has it, T-SQL just doesn’t segregate procedural and declarative code the way most engines do. Mostly, I think this is a negative for T-SQL, but sometimes segregation means there’s no way to do one-off procedural scripts without making and then calling a procedure; but Postgres, for example, since 9.0 has the DO statement for this use case (which supports any procedural language installed in the DB, but defaults to PL/pgSQL.)

I don't think SQL does compose well. Queries aren't first-class values in SQL.

I can write a query that queries the result of another query, but I can't store a query and then pass that somewhere to query from.

Maybe the WITH statement and CTEs can help you, if I understood correctly your complaint.

Check the example at the beginning of https://www.postgresql.org/docs/13/queries-with.html

Would be interested in hearing what database and query language you think are better?

I think Datalog as implemented by Datomic has a much better syntax though I don't like Datomic per se.

Oh don’t get me wrong - ORMs have their place and do enable higher agility. They do seem magical the first time you encounter them.

What I object to is the blanket “shouldn’t care about sql” statement because that’s what empowers people to use the ORM indiscriminately without understanding what’s under it (an understanding for which SQL is relevant) and then it’s the non-value-adding developers’ job to come in and untangle the mess, usually could have been avoided by dropping down one level, looking at the SQL that was generated (or maybe analyzing the query plan - again kind of hard if you don’t understand SQL) and realizing the ORM is doing something crazy.

Exactly. ORMs, especially powerful and mature ones like DjangoORM or Active Record, can help with productivity and maintainability and for more complex use cases you have the chance to switch to raw SQL; However, it’s important for developers to know what’s happening under the hood and to know what will happen if they use a certain feature of an ORM. I can’t even count the number of times that when one of my coworkers and I tried to fix a performance issue, and after digging deep into queries and mechanics of the ORM, we’ve realized how ORM heed so much complexity from them and, how a certain data structure design and coding in a certain way can result in an inefficient data flow.

Actually nested objects kinda suck even for frontend devs, if you're trying to keep things in sync efficiently.

Often times it's much better to be able to lookup some object by ID of the entity from some Map, than consuming endpoints returning some crazy nested partial data for the current view.

Depends on how much your app relies on client side caching and incremental sync of data.

As soon as you need a lot of complex data all displayed in one place plus high performance, you're gonna find you need a list, not a hierarchy. You need to be able to treat the data coming in like a stream, not to traverse anything. Read the row, maybe do some state-machine stuff to decide how to treat it, then drop all that on the floor and move to the next row.

This is more-true the less efficient the language is that you're using. I see people screw this up when writing e.g. dashboards that source their data from SQL, while leaning on an ORM, all the time, and it always kills performance (talking tens of seconds to minutes for things that should take a single-digit count of seconds, at worst).

What's good at turning complex relational data into a list, and fast? Yep, SQL.

That too. Though I used efficiently in a sense of minimizing the communication between client/server by normalizing data and just sending changes.

Which is easier if you keep data normalized on the client side too, and look up related objects in a Map when needed, instead of keeping multiple copies of objects representing the same entity everywhere in your client code in some/vasrious denormalized forms.

It would actually be very nice for some use cases if I had a SQL interface to local data on the client side too, so that I can query/join them up arbitrarily as needed from what's loaded up to client storage. That's unpleasant to do in the browser with current platform APIs.

Basically I want WebSQL back in some form, instead of this IndexedDB thing. :)

> if you're trying to keep things in sync efficiently

I think we are all abusing GraphQL in this sense. Caching GraphQL data is a world of hurt.

The easiest way to sync is if your data is in the same model as the way it is stored, which is normalized if using SQL. Even when we do normalize most people will represent a 1-M as an array of foreign keys: `posts.comments = [1, 2, 3]` when in the db we use a foreign key on the `comment` entity: `comment.post_id = 1`. This causes such headaches for optimistic UI updates. It's a mess.

We should be using GraphQL to retrieve data and then render it directly.

Ultimately, I think we should be implementing our GraphQL API client-side against a local SQL DB that acts as a pass-through cache. Then your entire app runs offline and instantly respond to queries eliminating the need for a separate client-side cache, and you don't have to worry about about data model mismatch anymore.

> Depends on how much

I think all apps want this - unless they are SSRing with <100ms on every action.

Ha :) Yeah, I just replied in a similar vein in the other reply to my original comment. Agreed!

I keep sayin' it - SQL in the browser!

I'd love to get rid of all these client-side state management libraries and just drop an SQL DB in there and allow components to subscribe to changes to queries on record updates, and then sync it to a cloud DB.

One interesting problem I came across is how to know when your local DB has enough records to be able to run a given query. E.g. If your team has 100K tasks in their cloud DB, and you run `SELECT * FROM tasks WHERE tasks.assignee = user.id`, and then another query of `SELECT * FROM tasks WHERE tasks.assignee = user.id AND tasks.completed = true`, the second task is a subset of the first, so it doesn't need to hit the server except to check if any updates were made to the tasks table. Then apply this to all queries including ones with joins. Actually some fun relational algebra can be used figure it out - one of the perks of working with SQL. The problem is actually similar to how to [incrementally maintain a materialized view](https://wiki.postgresql.org/wiki/Incremental_View_Maintenanc...) which Postgres actually does not support (Oracle does).

If everything is simple, then so are the SQL queries.