Sketch of a Post-ORM | Hacker News Reader

Sketch of a Post-ORM

borretti.me

83 pointsTomte80 comments

Good tools explain the tradeoffs they make and provide escape hatches for when you need to sink down to the lower level (since all abstractions are leaky in practice).

Use the escape hatches appropriately. Also - not turning on logging for your ORM and looking at what it generates or using an n+1 detector, linter, etc is the fault of the dev team, not the 'incompetent PM'.

Also the example declarative migration doesn't really feel declarative to me. Seems pretty... procedural? To me declarative is "describe the final state desired and I will generate/execute the diff". In practice I find migrations need backfill + biz logic sprinkled in.

> Migrations first, not schema first.

Similarly I don't think this works in the real world either. Please consider the poor junior dev who checks out the code base for the first time and tries to run migrations and then seed data.

The best practice has, imo, long been to load a schema file which represents what the db should be at the head of a branch and then load up your seeds. Migrations are to catch your team and live environments up. They occasionally get nuked from the codebase as everyone reconciles their environment.

> And the problem with portability is it comes at the cost of specificity. I don’t want a database access tool, I want a Postgres access tool. I want it to expose Postgres’ power user features as first-class features,

I don't know about Python based ORMs but ActiveRecord does this.

Yes I thought ActiveRecord's ORM addressed a lot of the pain the OP mentioned with traditional ORM's.

ActiveRecord is also the first and only ORM I've used extensively and I've been a fan of it. Prior to that, I used to stick to raw sql, most apps were smaller but even then it felt like a drain to write repetitive sql to find, insert and update records.

> They occasionally get nuked from the codebase as everyone reconciles their environment.

Yes, and also migrations really have to be expressed as code, and you don't want to have to maintain your old functions forever because they are tied to a migration; alternately you don't want to always have to be refactoring your migrations, which is a dangerous and thankless task

Or just avoid all use of application functions in your database migrations, and you never have to worry about either problem.

> > Migrations first, not schema first.

> ...

TFA says this in the context of ORMs. But in the context of not-ORMs I think migration-first == schema-first: just use `CREATE .. IF NOT EXISTS`, `ALTER ..`, etc. to set up the schema from the get-got, and you'll think about and cover migrations as you go. The problem with ORMs is that they hide the SQL bits and so if they don't get this right then you'll suffer.

> The best practice has, imo, long been to load a schema file which represents what the db should be at the head of a branch and then load up your seeds. Migrations are to catch your team and live environments up.

I'm delighted to read this - I tried this method as an experiment in one of my projects, and I really liked it, but I had never seen anyone else do it. Good to hear a confirmation that it's worked elsewhere too.

I'd like to add that it's also advantageous when spinning up DBs in Docker; you can mount your "head" schema files right into initdb.d and have them execute when the container runs.

ORMs (especially the Django ORM) fulfill 95% of my needs. For the 5%, I use raw SQL via the ORM's escape hatch (which Django and SQLAlchemy provides).

To be fair, my needs aren't that complex, I do mostly CRUD on database tables, most of the business logic is handled in my code, eventually wrapped in a `@transaction.atomic` (thank you Django).

If I need stored procedures, I'll add a Django migration which creates the said procedure with raw SQL.

If I need to manage the schema myself, I'll also make a Django migration with raw SQL, then I'll create Django unmanaged models.

For the portability across RDBMS, it's nice to have an SQLite database in the dev environment, so that the developer does not need to run a PostgreSQL instance (in docker or whatever). And for the test suite, I even use an in-memory SQLite database.

I do put my ORM queries in a specific module which provides a higher level API, I will have functions like `get_users_sent_invites` or `publish_article` etc... so that the rest of my code never sees database code. A function `get_user_by_id` will return the User model or None, and handle the ORM's potential exceptions to return meaningful errors to the business logic.

It also makes database access easier to test and benchmark. You might call this DAO or not, terms are irrelevant, it's just good practice to separate concerns IMHO.

> Performance: the generated SQL is often badly optimized.

I can't but disagree completely with this statement. There might be an edge case in which a raw query, tailored to a specific need or a particular use case, might be faster, but generally speaking ORMs will know much much better than the average developer how to optimise queries.

They will also know exactly what DB features to use to leverage extra features (for example, Django's F() functions).

Edit: I'm totally amused that the author didn't even mention the fact that ORMs will go ways to prevent you from shooting yourself in the foot (from a security stand point). ORMs have made SQLi so much less common than it used to be. Of course, there still might be a bug here and there which will lead to an SQLi, but it's like day and night compared to how it used to be before, when average Joe would just slap a bunch of queries in a "queries.php" and call it a day.

Edit 2:

> I think migrations should come first. You should write migrations as separate files

Why? Why would you want any of that? The entire point of having the ORM do its thing is so that you (the developer) can focus on the code itself (the models, in this case). Modifying the models to adapt them to the requested changes is something that you'll do anyways, as that's your job. Then the ORM generates the migrations between each modification you did in the code, which leads you to not having to think or even care about how to apply these changes to the database. Why would you want it any different? We've literally reached the point where the code itself can represent "what the database should look like", and the changes to that code represent (via migrations generated by the ORM) "how the database should be changed in order to adapt to the new code".

> There might be an edge case in which a raw query, tailored to a specific need or a particular use case, might be faster, but generally speaking ORMs will know much much better than the average developer how to optimise queries.

This statement is wrong. ORMs can't use DB specific features, so they have to generate lowest common denominator SQL.

With Postgres e.g. I can insert an entry and return the generated ID in a single query. ORMs can't do that.

I can also upsert entries in a single statement. ORMs can't do that.

> ORMs can't use DB specific features

This is technically false. There is no technical reason an ORM couldn't use DB-specific features. It's also practically false. Have a look at https://docs.djangoproject.com/en/4.2/ref/databases/

Django's ORM will do quite clever stuff, specific to your database (eg, if you're using Postgres, it will create additional indexes using appropriate PSQL operator classes).

> I can insert an entry and return the generated ID in a single query. ORMs can't do that

This is also false. Calling .save() on a model will issue an INSERT statement, then the ORM will assign the auto-incremented PK to the "id" property of the model without issuing any further queries. https://docs.djangoproject.com/en/dev/ref/models/instances/?...

> I can also upsert entries in a single statement. ORMs can't do that.

This is also technically and practically false. There is no technical reason an ORM couldn't do an upsert with a single query (granted that the database itself supports it). Practically, while Django's ORM itself can't do this, you could easily use django-postgres-extras (which is just an extension to the ORM's capabilities) to issue an "ON CONFLICT target", which will achieve an upsert with a single query: https://django-postgres-extra.readthedocs.io/en/master/confl...

This is absolutely not the case. In fact I've never used an ORM myself that generates SQL directly – they all generate some kind of abstract model then render that to SQL using a database-specific adapter.

Specifically, Rails' ActiveRecord will transform e.g.

  Customer.create(name: 'test')

into the following when using Postgres

  INSERT INTO "customers" ("name") VALUES ('test') RETURNING "id";

Things like

  Customer.upsert_all([{name: 'test'}])

will also work to perform upserts.

In practice, ORMs are more advanced than you might think, and can be a useful tool in some cases!

Laravel's Eloquent does upserts no problem. Please actually do some research.

Of course they can use DB specific features. I use ecto (elixir) and there you use it with an adapter for the specific db you want. Does is have all the million different specific features? No, but the common ones. Do you need more? Many extensions exist, you can write your own extension of ecto or you could just use "fragment()" and inject raw sql in the middle of a query

I literally never saw an ORM output anything approaching optimized.

It’s mostly.. adequate, which is fine. I guess. It depends.

Also have a bit of a history with these “magic migrations”. It doesn’t come close to “we have reached the point”, it’s more like “in some circumstances and under extremely strict conditions these features may provide some benefit and will keep working over the years”.

I think making schema changes via migrations would be explicit and should lead to it being predictable. Maybe the best of both worlds is the ORM spitting out what it thinks the migration file should be based on the database schema and the new model changes?

The migration process then runs the actions in the migration file. If it fails in new environments this is telling that something is different in that environment and needs to be investigated.

This is how it works. ORMs generate migration files automatically.

> You have omnipresent performance problems. There are n+1 queries everywhere, but where specifically? You don’t know. It’s impossible to statically determine where a specific query is happening. You have to instrument at runtime, which is rarely done rigorously or uniformly, tracing every call and staring at logs until you find your performance problems.

One of the tools I've really come to enjoy in our modern age is telemetry tools that are designed to solve these kinds of problems. If one finds oneself tasked with identifying the sources of omnipresent database-related performance problems and one is not in a position to rewrite the database access layer for the project(!), I find tools like New Relic to be incredibly helpful. For some setups, they even let you identify the line of code that produces slow queries. Those can then be optimized either within or outside of the ORM.

Having pitched both options, I have also found that "let's install this tool, which may be free for our use" to be an easier sell than "let's rewrite the database layer."

I honestly found myself tuning out of the essay after the intro exhibited ignorance of modern tooling built to address the specific issues under consideration in the essay.

IMHO it still makes sense to use an ORM to build quickly, given that we have tools to help effectively profile downstream. This is the approach we take for code (by using a language like Python and not C/Rust), and the tooling around databases makes the same approach viable for db code.

Where to read more on omnipresent?

> SQL is bad, for two reasons:

> 1. The syntax is bad.

In this context, "bad" means "hard for a computer to parse." And this statement is 100% true.

Though from a human perspective, the syntax is relatively fluent.

Which would would you (as a human) prefer for writing raw ad-hoc queries? SQL or MongoDB? For me, it's no contest the former.

> 2. Type checking is absent.

PostgreSQL complains at me if my types are wrong.

E.g. if I try to sum two strings.

The author mentions prql as an alternative to sql in the post. I personally find prql much more fluent than sql.

How is SQL hard for a computer to parse? SQLite can parse its flavour of SQL using a plain old LALR grammar and that covers a lot of SQL.

And even if it was, how does having an ORM generate the SQL behind your back change anything for the computer that still has to parse SQL?

I suspect what is really meant is that it is time consuming to write a parser that encompasses all of its syntax, not that it is performance intensive or is high on the Chomsky hierarchy.

But, at least for SQLite, the parser is autogenerated from a plain grammar.

It's not so much the parsing... it's the validation against an underlying data source. I can write `SELECT A, B from Foo` and it's "valid SQL" but that doesn't mean the underlying Foo table exists, or that the columns are there. Beyond this, that doesn't mean it understands that A should map to an Int32 or that B should be a UTF-8 byte array in the outer language I'm working with.

Personally, I'm fine with the likes of type mappers like what Dapper in C# does, or serde_postgres offers in Rust. For that matter template string processors in JS/Node/Deno are also really nice. Yeah, you need to understand the underlying data source, but that's often far easier than massive amounts of boilerplate.

I'm not sure about SQLite, but try looking for "PostgreSQL parser".

It's far from simple.

It's hard to compose (you could call it metaprogramming maybe, since you're writing SQL programs with a program), too.

You know the problem: take this query but with this extra table joined and this other condition in the WHERE clause.

Solutions exist, but they mostly are married to ORMs, thus the frustration. But I'm confident that "either raw paleolithic SQL or clunky ORMs" is a false dichotomy (and I've written a thing in PHP over a decade ago to scratch my own itch and compose SQL, so I just know for a fact how false it is).

The future isn't writing query handling code by hand or using ORM's. The future is reflection or code generation tools like https://sqlc.dev

Whether you use traditional queries or ORM's you still have to write all the model query code by hand in traditional systems. Projects like sqlc generate all the shapes/objects, validation, and everything else for you.

Simply define all your SQL queries in an easy to audit file and sqlc generates the entire model/store package for you. doesn't matter what language the generated code is in, as long as you can all read SQL you can understand the app. Then just plug the generated package into your HTTP API or GraphQL revolvers.

Try it out: https://play.sqlc.dev/

This reminds me of the benefits that OpenAPI brings. A lot of teams are still writing client libraries by hand for each language instead of using an OpenAPI/Swagger spec.

Beautiful. i was just looking for something like this for go yesterday.

This!

Ever since I discovered sqlc it's my go to for any dB it supports.

It's a joy to work with and does exactly what you expect it to, no strange magic.

Stable for go code using postgres and mysql only at this time.

I feel like SQLAlchemy is the elephant in the room here that addresses most of his perceived shortcomings with ORMs.

I tried to learn SQLAlchemy to avoid writing explicit SQL, but except for the most trivial relationships it requires me to write several times more code in an arcane DSL to accomplish the same thing, at which point, why bother?

And SQLAlchemy Core, among many other SQL generators, gives the lie to "The state of things is bimodal: you either write raw SQL, or you use an ORM."

This is just not liking SQL, which is fine, but it's not 'the future of querying databases'. It's just a simplified language that is under-specified and doesn't really support even a fraction of the use cases of SQL, and therefore looks 'cleaner'. It's a bad abstraction.

Here is my attempt at a 'post orm' if anyone is interested, as a bonus it is fully implemented and some people actually use it: https://github.com/justinvanwinkle/Norm

Just write the damn SQL. I can't believe this stupid conversation is still going on.

Just use a damn ORM. I can't believe this conversation is still going on.

For my money there isn't a better tool than Prisma right now. It's a truly fantastic database experience. It's significantly better than TypeORM and/or Knex, and of course better than the unfiltered nightmare of ActiveRecord. SQLc for Golang isn't a great library, but it has the right overall idea: stay close to SQL while still getting language typings, and codegen. I think everyone should learn from the Prisma developer experience, it's the best of all worlds: static typing, codegen, seamless database interface, while still having as much granular control as you want.

I agree.

However, a tool like Prisma only seems possible (with all its benefits) within Typescript, which is able to compute arbitrary types dynamically.

I don't entirely follow, because the Prisma client is static code generated from your database schema. There's nothing dynamic about it, unless you're referring to generics?

> unfiltered nightmare of ActiveRecord

Huh, I've only heard good things about ActiveRecord. Care to elaborate?

The entire article is about problems with ActiveRecord.

Even at a cursory glance some of the things they suggest as the solution like being "migration first" is exactly in alignment with ActiveRecord, which has always been migration first. Even the | based syntax they post functions similarly to AR . chaining. This person is clearly writing from a problems with python's ORM perspective.

I am very happy with AR, I have spent a lot of time in data engineering as well and I find raw SQL perfectly fine and expressive for reporting, and data query though I understand why many don't get or like it, etc.

ORM's really shouldn't be used outside OO app dev but for application mapping AR is great and I'm incredibly happy with it's ergonomics and dev friendliness, quite frankly for SQL naive app devs I would much rather they use it because it tends to optimize SQL queries better than your typical app dev with lower-mid tier sql experience. SQL is basically a functional language (saying that ticks a lot of people off but it's true) though which is why many devs hate it as it requires you think functionally.

The only thing I think is an actual improvement recently in the space in terms of writing queries is what DuckDB has done.

Prisma is pretty good, but I'm hoping they pay attention to migrations soon and reverse the decision not to use transactions in migrations.

> Migrations first, not schema first. > Migrations are specified in a declarative format, not in SQL.

Strongly disagree about this. Consider how Terraform works. You declare what the end state should look like and it decides what the "migrations" should be to get you there (the plan). With a relational DB, we would declare the end-state DDL and execute a "plan" that will derive the migration for you and, preferably, derive corresponding types in whatever language you are using.

The problem is that when you go beyond DDL, or you want to execute backwards incompatible DDL changes, you can no longer make these migrations declaratively.

SQL Server Data Tools (SSDT) does this. It's the biggest thing I've missed after moving to postgres.

How do you deal with for example with this:

New column C

C = A + B

Drop A, B

If I just specify the desired state I get data loss...

Good migration systems like Django's will have escape hatches.

Yes but then it's no longer declarative, is it?

I don't mind.

Is absolute purity a requirement?

Yeah, so I don't agree with much of this.

I usually end up rolling my own abstraction layer on top of SQL in whatever language I'm using, and have been doing so for 25 years now; it's not that much code if you only write what you need.

First of all, it makes the same class=table assumption that's hurting every ORM I've come across. This only scratches the surface of what a relational database can do. You want support for records that may contain arbitrary number of columns from different tables on some level.

Secondly, while I agree that migrations should be first class, I also want support for them (and DDL) in the host language. My last project was based on event sourcing; which meant that to be able to replay the event stream reliably, migrations had to create events.

First class queries is another thing that I've never seen out in the wild. It's not that complicated to add a composable layer on top of SQL to generate arbitrary queries.

And while there are certainly differences between databases and SQL dialects, I've been involved in 2Mloc projects that ran on several different databases with a thin compatibility layer in between. It's perfectly doable.

My abstraction layer over SQL turned into an ORM over time. Same pattern.

> First class queries is another thing that I've never seen out in the wild. It's not that complicated to add a composable layer on top of SQL to generate arbitrary queries.

I'm interpreting the post as wanting to do just that with static typing and calling it an ORM.

From TFA

> I want a query language that’s better than SQL. Specifically, it has to be 1) composable, 2) statically typed and with 3) a sane syntax. I want sum types.

I want a statically-typed way of constructing composable queries that follow SQL rather than reinvent a different thing. It doesn't have to be the same syntax but it has to be the same structuring.

I started writing one[0] and stopped before doing all the boilerplate code generation, having moved on from the JVM ecosystem for the time being. One thing it does is treat most things like sets so we don't end up with N+1 queries. Another trick it uses is collapsing constant expressions via an expression evaluation library[1].

[0] https://github.com/karmakaze/safeql

[1] https://github.com/karmakaze/moja

The Scala ecosystem has a few ways to do composable type-safe query building, e.g. Slick[0] or more recently Quill[1]. I believe both also have ways to do compile-time string interpolation (e.g. sql"""select * from users where id = ${user.id}""".as[User]) which generate prepared statements (I know Slick does prepared statements. Quill appears to have a similar interpolator).

[0] https://scala-slick.org/

[1] https://zio.dev/zio-quill/

I found JDBi[1] to be a really nice balance between ORM and raw SQL. It gives me the flexibility I need but takes care of a lot of the boilerplate. It's almost like a third category.

1. http://jdbi.org

If you want a db tool which can be an ORM for your app, and drop down to a lower level dsl, while targeting specific features of the databases it supports, + having a "composable superset for building queries", there's [ruby sequel](http://sequel.jeremyevans.net/), which is the best tool of the kind you'll get for any proglang. Everything the author wants, minus the typrchecking perhaps, which is IMO shooting at the stars.

For years now, I’ve been using a query builder that supports migrations and basic, non-instanced “model” functions that represent the cases of fetching and modifying data as required by my application.

This allows me to have as much to data access as I need in easy-to-stub methods, without an overly opinionated ORM stepping in. The query builder does its job to simply build queries I ask of it and I don’t have to worry about a gigantic piece of code liability. I really tend to avoid using a library for everything these days and using libraries that do far too much (and if I do, I limit exposure by wrapping calls to it in functions I create).

To me this looks a lot like ecto https://github.com/elixir-ecto/ecto

Is there a significant difference?

You completely lost me at the sum types part. Humanity needs sum types... so that we can generate offensively denormalized RDBMS schemas? Echos of OOP-mania.

However, I strongly agree about "pointless portability". 'Django' would be perfect if it just focused on Postgres. Cut the call stack in half and add more power+perf. Point SQLite users to SQLAlchemy, and forget about the others.

Or use JOOQ today.

Not strictly relevant, but:

I've yet to see tools that handle zero-downtime-migrations well. The manual task of keeping two versions of the same query / model alive (version N and version N+1) is... well, how else would you handle it?

While tools that can generate the model or schema are nice, they never seem to account for this.

Is there some alternative approach I'm missing?

Have your query as a function in the database. BEGIN, do the migration, replace the function, COMMIT. Postgres DDL is transactional so at no point does the function stop working.

What I want: PRQL (which they do eventually mention at the very bottom) + a database that supports something like protobuf defined tables.

I'm half convinced that after my current gig I'll do a startup based on that idea. I just want it to exist!

What's so good about Protobuf that you want it for your schema definition?

Ecosystem.

Its an IDL that I can generate language bindings for and supports a well developed ecosystem for RPCs between systems (or bounded contexts in your modular monoliths).

Well written article, but this is HN so naturally I have a, "Why don't you just..." You know your situation better than anyone else, so take it with a grain of salt.

But, why don't you just not use a general-purpose language? No really. Hear me out.

It seems the main problem is the proverbial impedance mismatch between SQL and say, Perl or Python or whatever. Ok. One way to remove that mismatch is to remove the boundary altogether. Remove the general-purpose language and really embrace the "just use SQL" path. That may have drawbacks and we can talk about those or we could not, but one thing it certainly would do is remove the problem of the impedance mismatch.

As in, write your entire application in SQL? That sounds like a nightmare of epic proportions. Every time I've tried to use SQL for anything more general-purpose (like string manipulation, say) I've wanted to gouge my eyes out by the end.

Yes. Write your entire application in SQL, IF it's the kind of application for which this can be done. Is the application a flight simulator or a game engine or resource manager or a distributed task scheduler? No, that's probably not a good case for SQL. Neither would be a machine learning application that relies heavily on Python packages, and it's easy to think of other examples as well. But, is the application responding to user input to query and display data, perform transformations over those data, impose constraints and invariants over those data, and validate user input against those data? Those are tasks for which relational databases with SQL (and other kinds of databases as well, but here we're talking about SQL) are tailor-made. What then does, say, Python bring to the party?

The obvious thought that occurs to me is UI - how are users going to access this hypothetical application? If it's via the web, then you'll need a whole setup for generating HTML, and doing that in SQL sounds extremely painful. If it's a native app, then you've got to interact with the OS somehow to do things like draw to the screen, and I've never even heard of trying to do that from SQL.

More broadly, this just seems like it would sharply limit how you could extend the application in the future. What if at some point you want to query a web API for some additional data with which to enrich what you're returning from your database? (Not an unusual situation, in my experience.) You'd be stuck trying to make web requests from SQL, which again seems needlessly painful.

Note that I'm not against the basic idea of "do as much data-munging in SQL as possible" - in my experience that's a great way to ensure that your application stays fast and efficient. It' just all the ancillary things surrounding the data-munging for which I don't think SQL is the best fit.

The OP didn't write about a UI and we don't know what their needs are, so for all we know they're writing a Python-based back-end API in REST or GraphQL which is being consumed by a mobile UI or a SPA UI in React or Vue.js or whatever, such that that back-end API doesn't have to provide a UI. If the existing Python back-end doesn't provide a UI then any proposed substitute--including one in SQL--shouldn't have to provide a UI either, just an API. In that case, there are some ready-made solutions already available:

- PostgREST (https://postgrest.org/): REST API for PostgreSQL

- PostGraphile (https://www.graphile.org/) GraphQL API for PostgreSQL

- pg_graphql (https://github.com/supabase/pg_graphql) GraphQL API for PostgreSQL

- Hasura (https://hasura.io/) GraphQL API for various databases

If you want to blend data from a web API and you're content with GraphQL, for some use-cases (not all, but some), there are options:

- Apollo Federation (https://www.apollographql.com/apollo-federation/)

- GraphQL Mesh (https://the-guild.dev/graphql/mesh)

- Hasura Remote Schema (https://hasura.io/blog/tagged/remote-schemas/)

If you want more control over the web API and you were going to fetch the data within your Python back-end and process it there, for some use-cases (not all, but some), there are options:

- pg_http (https://github.com/pramsey/pgsql-http)

Life is about trade-offs. Doing the work in SQL is not without its drawbacks, but it's also not without its benefits, and that's true for doing the work in a general-purpose language as well. Whatever the drawbacks of doing it in SQL, one of the benefits has got to be eliminating the impedance mismatch (for people who regard that mismatch as a problem, and the OP seems to be one such person). What I claim is that doing the work directly in the database shouldn't be ruled out in general (the specifics of a given use-case may rule it out in particular) any more than the other common patterns (API hand-written in Python, for instance) shouldn't be ruled out in general.

> The OP didn't write about a UI and we don't know what their needs are, [...]

> What I claim is that doing the work directly in the database shouldn't be ruled out in general (the specifics of a given use-case may rule it out in particular) any more than the other common patterns (API hand-written in Python, for instance) shouldn't be ruled out in general.

Hear hear.

With PG's COMMENT feature one can enrich SQL schema with the sorts of metadata one needs for UI generation.

Here's a script that generates a JSON view of a PG SQL schema enriched with JSON from COMMENTs: https://github.com/twosigma/postgresql-contrib/blob/master/s... and https://github.com/twosigma/postgresql-contrib/blob/master/s...

> The obvious thought that occurs to me is UI - how are users going to access this hypothetical application? If it's via the web, then you'll need a whole setup for generating HTML, and doing that in SQL sounds extremely painful. If it's a native app, then you've got to interact with the OS somehow to do things like draw to the screen, and I've never even heard of trying to do that from SQL.

Generating HTML?? No, generate UI declarations from the SQL schema (enriched with extra metadata) then interpret those in JS in minimal static pages that use JS to talk to PostgREST.

PG has a `COMMENT` statement that can be used to attach commentary to every single schema element -- tables, columns, views, indices, etc., all can have free-form commentary. Use JSON COMMENTs and then extract the whole schema using the pg_catalog as one big JSON blob, then post-process to generate UIs. See below for links.

"Write your entire app in SQL" is a perfectly sensible thing to say and do. I've done it. PostgREST and related projects make it real easy, and then you don't have to wish you could have sum types in SQL like TFA does.

I was kind of onboard until I realized there is code generation steps required for every DB query you want to perform. That would be a dealbreaker for me.

TL;DR - "post-ORM" is to ORMs what "post-rock" is to rock music

Same sounds, same patterns, but the performers are 100% more convinced of the uniquely genius nature of THEIR power-chords.

The author should check out Quill[1] as prior art. Compile-time query generation where you can see the generated query just by hovering over the code is chef's kiss.

One of the terrible things about most ORMs is that you can't see the queries when you look at the code. There's usually some awkward hoop you have to jump through to see it, like writing a unit test and logging the query from it, and Quill bypasses all of that.

[1] https://getquill.io/

Overall I'd say that this post does a better job of presenting the trade-offs than most. Some of the statements ring false to me, but I won't bother picking nits.

> Portable Across Languages

This is interesting, because what you're proposing in this section is a better SQL.

I don't think that's a bad idea. A relational DDL and query language that is optimized for integrating with modern programming languages might be enough of a step forward to actually displace SQL. However, implementing it as a middle layer between SQL and a programming language doesn't seem like the way to go, because it adds an extra layer that programmers have to understand. Make no mistake, people will encounter problems even if the tooling is perfect, because their own understanding won't be perfect, and they will have to go on debugging odysseys to fix their understanding, by tracing down through the layers from their code to SQL. Now you're adding a language that is just as powerful as SQL, maybe more powerful, right in the middle. I would never in a million years use a tool architected like that. The potential for getting bogged down in the details of how the different layers translate is too high.

This is why after all these years of ORMS and other database libraries people often still embed SQL in strings in their code. It isn't (always) because they're stupid or stubborn. It's because they know that embedding literal SQL puts a hard ceiling on an entire dimension of debugging.

Painfully often, when I ask somebody, "I think there's an error in this code that queries the database. What SQL does it generate?" they reply, "Oh, give me a second, I have a Datadog trace that shows it, I just have to find that link," or, "Let me run this unit test that logs it." Or, even more depressingly, they haven't even seen the SQL yet, because there were other things that were easier to investigate, so they investigated the easy things first even though they suspected the query was the culprit, like the proverbial drunk looking for his keys under the streetlight instead of where he thought he dropped them.

Introducing another equally powerful conceptual layer between code and SQL is a step in the wrong direction. Replacing SQL with a better query language that integrates better with code would be great. It will happen someday; fingers crossed it will happen in our lifetime.

Great article, thank you!

> The next problem is type-checking disappears at the query boundary.

This is a limitation of current tools. It doesn't have to be that way. The encoding of result row sets could elide type information and then the parser on the receiving end could just know the types from knowledge of the query that produced the result being parsed. If the application and the RDBMS were colocated then serialization could be avoided, though the price to be paid for that is that the application would have to use DB types.

> Migrations should not be written in SQL but in some parseable, declarative format like JSON or YAML.

> Why? Because to typecheck queries, you need to know what the schema looks like. To know what the schema looks like, you can either query the live database (it’s arguable whether this is good), or build a virtual model of that schema. I prefer static solutions.

Nothing stops you from compiling a schema from a spec, or vice-versa even.

I've used PostgreSQL's `COMMENT` feature to set JSON-coded comments, then I have a SQL query and jq-using script that produces a very nice JSON representation of the entire schema a) using all the metadata about the schema that PG itself makes available in the `pg_catalog`, b) enriched with the JSON `COMMENT`s embedded directly in the resulting JSON. Such `COMMENTs` in my case have UI-related metadata such that the unified schema JSON can be used to drive UI codegen. No need to live-query the DB once this JSON has been compiled unless you make schema changes.

> If the database schema/migrations are defined in a declarative format, and the query language is some type-checked compile-to-SQL language, the database access tool can easily typecheck the queries separately from your codebase, and generate code to: [...]

You can do it the other way around too (see above), but only if SQL's DDL is augmented. PostgreSQL's `COMMENT` allows for user-defined augmentation -- it's only not great because comments are free-form text, whereas a `JSONCOMEMNT` or alike that enforces valid JSON would be better.

Everything TFA says about NoSQLs resonates.

> I want strong and static types. I want queries I can typecheck statically, before executing them.

Yes.

> SQL is bad, for two reasons:

> The syntax is bad.

> Type checking is absent.

SQL itself does not preclude static type-checking. PG even has the appearance of static type-checking, except it's done at run-time and is a bit leaky -- but this is an implementation problem, not a language problem.

Not that SQL is a great language. Linq is clearly a superior idea, except that it dispenses with a language as such, so it's not quite a superior idea :)

> > Why can’t SQLx just look at my database schema/migrations and parse the SQL itself?

PG nowadays makes an AST of queries available. I don't recall what the stability of that AST's schema is, but it is possible to do this. It's still complicated, but so is writing a compiler, and writing a compiler is what we're talking about here.

> Maybe someone can work out a way to do stored procedures that isn’t a huge liability with regards to migration and deployment.

"Be more careful"

Now for my opinion:

- SQL is still evolving, and so is PostgreSQL -- we can expect better type-checking and better schema augmentation in the future, and we should demand it

- PostgREST and similar make it very easy to build pure-SQL (or pure-PlPgSQL or similar extended SQLs) applications without a thought of ORMs, and without applications really having to know anything about SQL -- VIEWs, table-valued functions, JSON/whatever schema compiled from the SQL, etc. are enough to make the application SQL-unaware

- therefore I think the post-ORM future is PostgREST-like front-ends for RDBMSes.

Regarding "SQL is not typed": as a matter of fact you can always type-check your queries against a transaction. If it fails, there's your syntax and type error right there. If it doesn't fail, then it either reads, or also modifies the database. But when you are in a transaction, you can simply not commit the transaction. You're always isolated. You can always check your SQL, safely.

BTW the author doesn't know what the word "liability" means.

As for Post-ORM, like ORM it treats the DB as a hidden implementation detail, which means no free interaction with existing DBs and uneasy solutions when something isn't in your abstraction, but it's trivial in the database itself. This approach has its place, but it doesn't seem like Post-ORM as much as a continuation of what ORM did in the first place, including many of the problems following.

Reply on news.ycombinator.com