Generating JSON Directly from Postgres

1. Receive, route and validate (for form) the request

2. Validate the request for function (business rule validation - can such a user do such on a Tuesday and the like)

3. Compute business attributes around the form of the response or update

4. Execute the query against the database

5. Send the results to the user

I strongly agree that step 5 there doesn't really need to involve very much stuff happening outside of the DB - in our particular platform we have a very smooth rowset -> JSON translator in the application framework that includes support for mapping functions over the rows as they come out of the DB - the result is that we pretty much stop executing application code as soon as we send a query to the DB. While we do still delay the actual JSON encoding until the application layer it's thin, dumb and done in a streamed manner so that we don't have to hold all that junk in memory - and it comes with mode switches to either redirect the big DB blob to an HTML template, echo it as a CSV or even run a PDF template to render it into what the user wants to see.

I've worked with a bunch of systems like this before. One was just a collection of PHP scripts that would trigger SQL queries. Another one was all lambdas and Cron jobs on top of mssql stored procedures.

If you have a decent team and if you use version control as intensely as you would with code, I have no reason to believe this cannot work. To be fair, those condition are true regarding of tech and architecture.

What you cannot do easily though is pivot, hire, and scale. This is what bit those teams I worked with and why those specific systems are no longer around.

The thing is that a database already stores data but all the schematics and ddl stuff would be much more conveniently kept in descriptive plaintext.

I did this for the very first external web app built for Bankers Trust Company back in the day. SQL Server back end with (classic) ASP on the front end. Even if someone had gained access to the web server, they wouldn't have had access to anything they shouldn't have. I built web apps, an online store, and a learning/video website using the same tech around the same time and they worked without issue for well over a decade. So, there is something to be said for this approach, though I wouldn't build something like this today.

I always wondered if I could make something like this work: https://github.com/plv8/plv8

Maybe couple it with this: https://postgrest.org/

Just not sure if it was worth the effort upfront to learn something other than simple Express (node.js) servers/middleware functions/router controllers with database client access. That paradigm just feels infinitely more "in control" and "extensible" to me.

The Hey email client is great example, hotwired.dev was built for Hey.

Guess what? It kind of sucks. It's buggy and slow. Randomly it stops working. When the internet goes down, random things work, random things don't work. If it weren't for Hey's unique features like the screener, I would much rather use a native app.

There's a ton we can do to make the the developer experience of rendering on the client side better, but there's only so much we can do to make the user experience of serving UI over the wire better. When the wire breaks or slows down, the UI renderer stops working.

We built an internal tool for our team we call "restless", and it lets us write server side code in our project, and import it from the client side like a normal functional call, with full typescript inference on both ends. It's a dream. No thinking about JSON, just return your data from the function and use the fully typed result in your code.

We combine that with some tools using React.Suspense for data loading and our code looks like classic synchronous PHP rendering on the server, but we're rendering on the client so we can build much more interactive UIs by default. We don't need to worry about designing APIs, GraphQL, etc.

Of course, we still need to make sure that the data we return to the client is safe, so we can't use the active record approach of fetching all data for a row and sending that to the client. We built a SQL client that generates queries like the ones in the OP for us. As a result, our endpoints are shockingly snappy because we don't need to make round trip requests to the db server (solving the n+1 ORM problem)

We write some code like:

    select(
      Project,
      "id",
      "name",
      "expectedCompletion",
      "client",
      "status"
    )
      .with((project) => {
        return {
          client: subselect(project.client, "id", "name"),
          teamAssignments: select(ProjectAssignment)
            .with((assignment) => {
              return {
                user: subselect(assignment.user, "id", "firstName", "lastName"),
              };
            })
            .where({
              project: project.id,
            }),
        };
      })
      .where({
        id: anyOf(params.ids),
      })

We do this today with SQLite, but we don't use stored procedures. Assuming you load all required facts into appropriate tables, there is no logical condition or projection of information that you cannot determine with SQL.

We also leverage user/application defined functions to further enhance our abilities throughout. It's pretty amazing how much damage you can do with SQLite when you bind things as basic as .NET's DateTime ToString() or TryParse() into it. There's also no reason you cannot bind application methods that have side effects if you want to start mutating the outside world with SQL.

I honestly think we don't do enough work on the backend these days, there's a trend for the client to get sent very raw data and to do a lot of processing on it just to render. If we can move more to the backend while still giving customers those isn't SPA style page transitions, it's a win in my books.

JSON seems to of been the message format of choice due to easy interoperability with the browser. However, with this, you end up writing serialisers on the server, deserialisers on the client. That's the majority of the job, reading what JSON fields you need to send down and for the client reading what JSON fields to read.

Using unstructured JSON has lead to OpenAPI previously Swagger. You put a bunch of metadata in your server API code to generate some loosely typed schema describing your service so clients can generate deserialisers etc. This is still unstructured JSON and seems like a kludge.

I've recently had success using Protobuf as the message format for HTTP API's. I still get easy load balancing, CDN caching, all the benefits of HTTP, which I'd lose if I went gRPC, Protobuf has a smaller size than JSON and add Brotl compression reduces page sizes. The excellent thing about using Protobuf over HTTP is it does what OpenAPI attempts to do without the kludge. You publish your Protobuf schemas, the server generates strongly typed objects to use, the clients with Typescript generate strongly typed objects. There's no more serialising JSON or manually reading JSON. You get typed parsers generated from a schema definition, so you call `encode` or `decode` on them.

Protobuf works across a large number of languages. My server side code has become trivial, my client side code has become trivial. As a side effect, I spend less on network bandwidth, pay less in compute with de/serialisation, and clients see a speed increase due to reduced page load sizes. Protobufs then also reduce the need for OpenAPI. Grpc entirely removes the need for OpenAPI/Contract based testing but going full Grpc on the client-side in the browser has more setup with a special proxy and you loose the load balancing/caching of HTTP.

TLDR; stop using JSON for web API's using something such as Protobuf with a schema. JSON is the equivalent of running your app in debug mode.

Web servers are easy to horizontally scale. Even minute to minute if needed. Scaling your database hits limits and is much harder. Keep the database dumb.

There is only so much you can do with moving data around.

Also, my approach to web app development is to put everything in a domain module, where the interface objects are pure language level objects.

It was awful. Not intrinsically because of the architecture, but because of the piss-poor implementation. Piles of badly written PL/SQL that should have been on dailywtf, with even worse JSP. It was all written by IBM contractors and was obviously a garbage rush job.

Of course the response from mgmt and the internal dev team was to rewrite everything in the "modern" J2EE style, but keep the old database partially because large parts of the existing logic were just not understood well enough to rewrite safely. So the rot kind of just continued.

After I and others left I believe everything was finally rewritten from scratch in RoR or something.

One huge advantage is that the DB has a cache that is automatically utilized, instead of having to build your own cache in an application layer.

The PostgreSQL userbase seems to be behind when it comes to utilizing stored procedures, it seems that they were not a priority for the dev team for a long time.

I'm not sure that's the issue at all.

The article complains about boilerplate code used to convert results to JSON. To me those complains make absolutely no sense at all, because that boilerplate is used for two things: validate data, and ensure that an interface adheres to it's contract.

It makes absolutely no sense at allto just dump JSON from the outside world into a db without validating or mapping. And vice-versa. That's what the so called boilerplate does.

What's more astonishing is that nowadays the so called boilerplate is pretty close to zero, thanks to the popularity of mapping and validation frameworks. Take .NET Core for example, and modules like AutoMapper and FluentValidation. They map and project and validate all the DTOs with barely any noticeable code. Why is this a problem?

Thank you for creating/updating it!

You are incorrectly assuming all JSON APIs are consumed by HTML sites on the same domain.

APIs are for machine consumption across the web.

Your solution of "just make static HTML" doesn't even begin to address the problem. Unless you're saying API consumers should go back to scraping HTML instead of having made for the purpose APIs.

I'm left with the impression you think hypermedia means "server-side static HTML".

JavaScript is part of hypermedia, it's part of the definition of REST (code on demand). If you don't like that, go argue with Fielding.

It only describes postgres native functions for formatting data.

As a data fetching approach and especially as tooling primitive to eliminate the n+1 ORM problem this is a terrific tool to be aware of.

Your database is your business logic. From constraints to the queries you write, it's all business logic.

Regardless, you misunderstood the article. This is about moving formatting into the db layer. Letting Postgres return JSON, as opposed to middleware code to translate rows to JSON.

Which is like the opposite of business logic code.

Where exactly do you draw the line?

Postgraphile is another tool that does the same but for GraphQL instead of REST using the same concept.

We investigated using tricks like this to generate the JSON directly from a SQL query - my notes here: https://til.simonwillison.net/postgresql/constructing-geojso...

The performance was a big enough improvement that, had we been bottlenecked on this particular operation, we would have shipped this (I'm afraid I don't have notes on benchmarks).

We only decided not to ship it because of concerns about the ongoing maintenance overhead, since we had other Python code generating GeoJSON that we would have still used - so we would have ended up maintaining multiple implementations.

In a few, particularly pathological cases (that nonetheless were fairly common to run into in prod), the response time of the endpoint went from literal minutes to a few hundred milliseconds on a test server with unlimited response timeout (prod would have timed out after 60s). Most of the performance increase came from not requiring Ruby to parse strings into fat objects, then turn it back into a long string to go out the wire.

// I work at EdgeDB

The schema export as json in the original article can be used to generate the schema for fquery.

> Also PG returns some JSON but if the object is not exactly what you want to send back you create another object and merge the two

Sure. Literally nothing changes. Just as if you had to assemble a JSON blob from two Postgres queries and merge them.

> Just don't do it, it's a terrible design. It "works" for simple API but for anything serious this is wrong

I think you completely misunderstood the article. Returning a JSON object from a database query is not "wrong", and I've used this pattern with great success. It's so much easier to call a json_agg to turn a subquery into an array, than to try and manually parse out rows yourself in the middle layer.

There's no reason not to expose parts of your database schema directly to users as long as you treat it like you would any other API you provide.

If you change your schema, you can just write a sql expression to convert between schemas 'at runtime'. Like if you have a table with first name and last name and then you decide you really just need the name on the frontend, just change your SELECT statement to concat the two columns, or vice versa.