Yeah. This is the problem: we've abandoned the hypermedia architecture of the web for a dumb-data, RPC model. I suppose if you are going to go that direction and things are simple enough, you can jam it in the DB and get smoking perf.

But as things get more complicated, where does the business logic live? Maybe in the database as stored procedures? It's less crazy than it sounds.

An alternative is to cut out the other middle man: JSON, ditch heavy front-end javascript and return HTML instead, going back to the hypermedia approach. The big problem with that is a step back in UX functionality, but there are tools[1][2][3] for addressing that.

[1] - https://htmx.org (my library)

[2] - https://unpoly.com

[3] - https://hotwired.dev

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 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.

What, "(classic) ASP on the front end"? I thought ASP was a server technology; wasn't your actual front end, like, Internet Explorer?

Anyway, I did that too, twenty years ago, using a couple of Oracle PL/SQL packages. Weird dual-language meta-programming, writing SQL that spits out JavaScript that in turn calls the next PL/SQL-generated page...

Unfortunately, the 'meta' is all about frontends with full backend access, so that's what I'm learning alongside other stuff.

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.

I work at an Alexa top10k, and our database server is quite small and sits at about 10% load most of the time.

You can grow huge with vertical scaling. Not all the way to “household name” size, but still truly huge. Servers with tens of tb of ram are available by the hour.

Not really, that's the result of hard-earned lessons that pay off from day 1.

Think about it: you have data in a database, and you put up an API to allow access to it.

Your API needs change, and now you need to make available a resource with/without an extra attribute. You need to provide it as a new API version in parallel with the current API version you're supporting.

What will you do then? Reupdate the whole db with a new migration? Put up two databases? Or are you going to tweak the DTO mapper and be done with it?

Perhaps my experience is unusual having worked on a distributed database for several years but this stuff just seems like table stakes.

We're not talking about adding attributes to a database. We're talking about one of the most basic cases in API design: changing a interface and serving two or more versions.

If the mapping step between getting data from the db and outputting it in a response is expected to be eliminated by getting the DB to dump JSON, how is this issue solved?

And how exactly is a full blown db migration preferable to mapping and serializing a DTO?

The point is that instead of having queries that return tables that you then map to DTOs for serialization, you have queries that return JSON directly.

Another way to accomplish the same thing, if you don't want views or stored procedures, is to use a library like jOOQ that does much the same thing in your Java middleware.

But that already qualifies as the boilerplate code that was supposedly the root problem, isn't it?

But now instead of a simple mapper you've bolted on another complex system.

So exactly how does JSON in the db solve any of the problems that were presented? Other than adding two buzzwords, what were the improvements?

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.

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.

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.

  - Round-trips avoided.
  - Security.
  - Data integrity.

It's just a shame that procedural extensions of SQL (T-SQL, PL/SQL and brethren) seem stuck in the '80s when it comes to developer experience.

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.

This makes SQL needlessly complex and tools to work with schemas far more complicated than they actually have to be.

In Lisp, most things are a list/pair. In SQL, most things should have been a table.

The DDL is also implementation specific but there’s a much higher degree of practical commonality. When writing application db migrations, and especially when supporting multiple backend databases, I’d definitely rather compose/generate DDL than engage with the minutiae of each.

    insert into schema.users (type, name, columns) values ('btree_index', 'index_dob', 'dob DESC, name ASC')

Even table names, column names and domain names are represented as character strings in some tables. Tables containing such names are normally part of the built-in system catalog. The catalog is accordingly a relational data base itself — one that is dynamic and active and represents the metadata (data describing the rest of the data in the system)

[1] The Twelve Rules, E.F.Codd https://reldb.org/c/index.php/twelve-rules/

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.

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.

These days I'm comfortable enough driving EVERY database change through a version controlled migration system (usually Django migrations) that I'm not concerned about this any more. It's not hard to enforce version controlled migrations for this kind of thing, provided you have a good migration system in place.

Yes, I've had to build a system like that (Java, but same diff.)

This model is popular in particular with banks, because they can split sensitive responsibilities in a way that makes sense to them.

It works fine if you have good communications between developers and DBAs. If you don't... well, I won't have to suggest finding another gig, you already want to.

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.

Also, it's just harder to debug stored procedures when they do the wrong thing than it is with a typical problem in application code on a stack like Rails or Django.

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.

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.

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),
      })

  - PostgreSQL
  - PostGraphile
  - Restrict prod to only allow persisted queries
  - Generate TS types using apollo-cli or graphql-code-generator

self describing messages and HATEOAS is not

would love to chat w/ fielding some time

Thank you for creating/updating it!

There's nothing fundamentally wrong with XML + Schemas just they are a hassle to deal with being verbose and more effort generating/parsing. XML still has a place it's just forgotten and not cool anymore. Personally I like XML, maybe not for webapps message format but it's properties still hold today.

Protobuf / Thrift etc are probably a evolution of XML+Schemas on the web using a binary format. They are less verbose and Javascript isn't the Javascript of 15 years ago so also easy to work with in the browser.

It seems my original comment is down voted which is fair enough. I've been generally surprised at how much better an experience all round it has been switching to Protobuf instead of using JSON. My code is easier, there's less of it, it's more performant, it's easy to share across the backend/frontend layers, it has a long list of languages it can be used from with zero effort.

I was a bit pessimistic about it but had a API I needed to save bandwidth on for end users so tried switching from JSON as an experiment. I expected it to be a hassle but it turned in to the complete opposite. Any REST service I create going forward JSON won't be my first choice. Protobuf is simpler to work with in the browser (no manual parsing/typing the wrong field) and server side, it's also far more efficient.

For clients they get evolution support, can download prebuilt parsers/deserilisers or create there own from published schema definitions, I see it as win win all round and no drawbacks.

SOAP is probably closer to gRPC?

The bulk of the content is simply about formatting JSON in the DB instead of manually mapping rows in the application layer.

It doesn't say "eliminate your API layer" or "have no application logic between client and db" as most are jumping to.

I find the actual methods described as helpful in that I can convert to the data structures I ultimately want in one pass instead of two.

Doesn't mean I don't have validation or a traditional API layer. Just easier to use.

[0] https://postgrest.org/en/stable/

I've implemented a large system managing billions of records using exactly this approach of cutting out a lot of boilerplate in the application layer.

The most important thing is to be pragmatic. This approach works great for CRUD as well as many types of complex analytical queries. However, for cases where the the complexity or performance of the SQL was unacceptable, we also decoded data into application data structures for further processing.

When done well, you can get the best of both worlds as needed.

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.

More complex logic is a lot harder to maintain. You don't want to stress your DB's CPU if you can avoid it. You still need some kind of connection pooler anyway. There's a lot of reasons it doesn't make sense to put all your business logic in the DB. SQL is not the easiest language to maintain (not enough abstraction).

IMO the line to draw is just enough to keep data sanity.

Error states are a bit funky if you don't validate in the backend logic.

Meaning constraints and the model represent business rules.

1. https://en.wikipedia.org/wiki/Business_logic

Since the application layer is much more naturally aligned with small well isolated chunks of logic it is easier to minimize the volume of code and logic in scope when testing a particular attribute - when that goes into the DB I've always seen things get more complex rapidly.

As far as automatic tests for dbs - I have no idea what you are talking about unless you mean integration testing through a backend.

Automatic tests for a database could simply be writes followed by reads with expectations for the returned rows. This is how Postgres tests itself.

My personal go-to right now is ActiveRecord and RSpec. The examples could just as easily run raw SQL directly using the Ruby pg driver.

But this is not code, this SQL or equivalent, if SQL was known to be better than code to do busines logic everyone would do that.

What is your definition of code?

Plus, lots of those ORM things get plenty of hate from DBAs, I mean I like them but then again I'm not a very good programmer.

I am actually "good" at this stuff - writing logic in SQL through triggers, which call functions (at which SQL Server is way better than PG is) and stored procs, etc, and it's just a horrible, horrible practice.

Sometimes business logic is implemented as rules, in which case the rules (configuration) can go into either configuration files or a database. But that doesn't make it data...

Where exactly do you draw the line?

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.

It only describes postgres native functions for formatting data.

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

While interesting, the advice offered in this post is generally bad, or at least in complete. OK, so, you "cut out the middle tier..." what now? Are web clients connecting to PostGRES directly? Will PostGRES handle connection authentication and request authorization? Logging that a particular user looked at particular data? Can the client cache this data? If so, for how long? Even taking the author's premise, this is not a good pattern - a simple JSON get request still comes with a bunch of other stuff that this doesn't bother to address.

But the premise is wrong - few applications just spit out JSON like this, they also have to handle updates, and there's business logic involved in that. Data needs to be structured in a way that's reasonable for clients to consume, which isn't always a row of data (maybe it should be collapsing all values in a column, so that in the JSON the client just gets a JSON array of the values).

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.

If we lived in XHTML 2.0 universe instead of HTML5 universe, then that would be the way you do things. Not with SQL and JSON mind you, but with XML everywhere.

I had a glimpse into that world and it looked pretty good at the time. XML databases + XQuery can produce whatever you want as long as it's XML. And XML could be many things. Many horrible things too, made by people with arms growing out of places they shouldn't.

"WITH _ AS (" real query goes here ") SELECT COALESCE(array_to_json(array_agg(_.*)), '[]') AS xyz FROM _";

From there it behaves as standard JSON, wich can be easily loaded into any language.

It generates jsonapi right now, since that is standard for Ember projects, but it'd be pretty easy to add an adapter for some other format.

[0] https://github.com/pjungwir/active_model_serializers_pg

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.

It removed a crazy amount of boilerplate code [2] though and made the project much more focused on the actual data which is always a good thing.

Ever since, I've been very curious to try edgedb [3] since it promises to do similar things without making the queries more complex.

[1]https://github.com/green7ea/newsy/blob/master/src/feed_overv...

[2]https://github.com/green7ea/newsy/blob/master/src/main.rs#L4...

[3]https://www.edgedb.com/

But the underlying SQL gets complex and it gets harder to maintain high-performance code - or hire good engineers.

This has made me intrested in EdgeDB [1]. They are abstracting the high-performance SQL bit from the engineer by introducing a new query language EdgeQL [2].

You can think of EdgeQL and EdgeDB to be the next step in abstraction just like how we have many programming languages, that did just that for machine code and beyond. You may be able to write high performance assembly, but it's tiresome and may not lead to high performance. Also a better UX for the engineer (eg: Kotlin on JVM) improves time to market and overall better for the ecosystem.

[1] https://www.edgedb.com/

[2] https://www.edgedb.com/docs/edgeql/index

// I work at EdgeDB

https://github.com/zbjornson/bson-to-json

https://github.com/plv8/plv8

Discussions like these encourage me to put more work into the DB layer however only recently have SQL databases like PostgreSQL become horizontally scalable (I know read replicas are a thing and maybe they are the answer).

So why would I put more work and effort into the less scalable data storage over the horizontally scalable, stateless app layer?

It will create various problems when next engineer adds some business logic, another one adds some stored procedures....

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.

And when you have an interface, you really ought to put some thought into its design regardless of how "private" it is.

An API is an interface used by a program in some way, and any user-exposed database objects definitely qualify under that definition.

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.