> Maybe in the database as stored procedures?

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.

A place I worked at had an ASP app in vbscript + one stored proc that dynamically executed raw sql strings from a database table. It worked so much better than it ever should've.

> > Maybe in the database as stored procedures?

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

Out of curiosity, what would you build today?

I still believe the stored procedure route has merit. It's harder since most available libraries are built around the idea that the frontend has full access to the database. A frontend in (insert favorite frontend lang/framework here) and a backend in Postres using stored procedures could work quite well.

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

Not sure if you've looked into either of postgrest or postgraphile, but I feel like those tools may be right up your alley. Rather than have your app layer generate SQL via an ORM, these tools instead generate your app layer based on your database schema, functions, etc. Postgraphile (generates graphql APIs) is particularly friendly towards frontend-heavy web apps.

Thanks for the recommendations, I'll check them out!

You almost convinced me until the very last sentence. Do you mind sharing why you wouldnt build this today? Especially for simpler CRUD apps.

I meant more that I wouldn't use VB/ASP and SQL Server. I still believe the stored procedure route has merit. It's harder since most available libraries are built around the idea that the frontend has full access to the database. A frontend in (insert favorite frontend lang/framework here) and a backend in Postres using stored procedures could work quite well.

The company I currently work for still actively uses classic ASP in production and they still actively develop that product. Currently trying to get away asap, because I don't wanna touch spaghetti vbscript from '99 with a ten foot pole.

Lots is boilerplate, but eventually you'll need more logic, then some CPU heavy tasks, then a memory heavy task, then a task suited to a specific language, then a task that needs a GPU, then a distributed memory cache.

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.

That’s reasonable advice for growth stage companies aiming for huge user bases.

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.

Really really expensive servers that sit at 10% load most of the time is one of the points.

This isn’t an expensive server. It’s just not a problem that requires substantial resourcing.

Licensing cost is another factor. DBMS tend to be paid software than Web application server. Scaling up Oralce sometimes very expensive.

Oracle is extremely expensive. I've been using Postgres for a long time and you can eek quite a bit of performance out of it.

> That’s reasonable advice for growth stage companies aiming for huge user bases.

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?

I use a database that was designed by someone who understands one sometimes wants to run a migration. It’s not hard to add new fields.

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

> I use a database that was designed by someone who understands one sometimes wants to run a migration. It’s not hard to add new fields.

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 way you are supposed to use Postgrest or Postgraphile is to provide only views and stored procedures as the API. These views and stored procedures are layered over your data schema and should be the only way to query or change the data. This way you can make API changes without requiring migrations.

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.

Or use an API layer such as GraphQL that doesn’t require versioning for such a minor change since additive changes (or deprecations) to the API do not affect existing queries - each query states exactly what it needs.

Backwards incompatible changes will eventually be required by the business - at least once that business is oriented such that technical concerns do not overrule all other concerns.

> Or use an API layer such as GraphQL (...)

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?

You can use views for that usecase, and you can have multiple available at once.

I worked on a stored-procedure driven finance engine once. There was an internal language to build procedures quicker. Swear to god I had errors running them through the SQL IDE because they were too large. Once had to debug a 1.6m line SQL file. These things can really get out of hand if people are left unchecked!

> Maybe in the database as stored procedures?

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.

You might like http://postgraphile.org, which is "postgrest for graphql" but where you can override anything with normal Node code – it's just an express plugin. (The docs don't make it obvious how powerful/flexible the tool is). Of course, you can also use pl/v8, pl/pgsql, etc and any other advanced postgres feature as well.

Once the client is the MVC execution environment with the client-server interaction used mostly for data replication, plus some extra invokable server-side behaviours written in isomorphic code, we can congratulate ourselves on having more-or-less reinvented Lotus Notes.

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

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.

Putting business logic into stored procedures is an old idea, and very widely used from what I can tell. Especially with Oracle's PL/SQL and SQL Server's T-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.

Few more advantages:

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

The last two points rely on building a public API out of stored procedures / functions / views and disallowing direct access to base tables. That ensures no one can access anything without proper authorization and no one can change the data unless in accordance to what stored procedures allow.

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.

I am doing this at work, I have an application with all the business logic in the database. It is a huge hassle though... I am versioning a bunch of scripts with git but it really isn't convenient.

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.

One thing I particularly dislike about SQL is DDL and all the system-specific extensions (e.g. for access control). I don't really get why there is a separate thing like "ALTER TABLE foo DROP COLUMN bar;" if you could have "DELETE FROM schema.foo WHERE name = 'bar';". 90 % of DDL is just duplicating basic DML functionality, Except For That Special Kind Of Object Which Totally Smells Like It's Relational But It's Not. Especially for access control - why is that just not a table? I'm sure it literally is one.

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.

In many RDBMS this is in fact the case. Some take it up a notch, for example in postgresql even types are represented as a relation in the system catalog. However, a system schema is necessarily very close to the database’s architecture and capabilities, making it implementation specific; commonality of underlying structure doesn’t really emerge from relational algebra because it is a) primarily a query paradigm and b) SQL is only a very loose adherent. In practice, woe betide the DBA that writes “insert into pg_enum” or “insert into pg_database” without knowing all the extra stuff that CREATE <anything> actually does.

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.

That would be pretty nice,

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

You might find some eminent agreement[1], though to be fair he was talking about relational DBMS, not SQL DBMS. See rule 0 and 1:

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/

Is that you, Bryce?

Curious how far you’ve gone with this. You could have your CI/CD sync your stored procedures. But you would probably want to go further than that and manage them with a tool like db-migrate so you can keep your stored procedures tracking with your schema changes rather than just versioning them. At the end of the day, can you really separate your stored procedures from the data they operate on? I would say no you can’t.

Reminds me of a job back in the early 2000s which was Oracle PL/SQL stored procedures fronted by JSP with inline JDBC requests.

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.

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

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.

I used to avoid things like stored procedures like the plague, because of my fear that crucial business logic would end up outside of version control.

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.

> One was just a collection of PHP scripts that would trigger SQL queries.

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.

This is just my experience but I've found that business logic usually ends up living before the query - most pages (whether read or update) follow a sort of pattern like...

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.

The problem with this route is that as you scale everything breaks. You end up putting the events or activities table on DynamoDB or you have to store user presence in Redis or you have to vertically or horizontally shard and have to move all that business logic that was in your DB back into the application layer anyway.

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.

Maybe this is why we can’t ever speak in generalities in this industry. I was going to say the exact opposite - almost all business logic I write comes _after_ the query is made. But I prefer a more DDD-like approach where logic is encoded in models, so there would be no way to do anything useful with raw data from a query.

Step 2 would require user info from the DB right? That is unless your AuthZ model is simple enough to fit in a JWT.

I built pretty much the same thing for django for a private project. Html attributes to issue ajax requests and update doms. Coupled with middleware that would detect the requests and run the same django views but extract what was needed and make it an isomorphic app.

To me, apps that end up simply being translators for JSON conversion are poorly built or feature poor.

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.

Thanks for the link. htmx looks really interesting - and the size is right too (1.3kb minified + gzipped). My biggest question would be how you make an offline capable site with this but I'm guessing service workers could take care of it.

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 application I work with has billions flow through it annually with hundreds of stored SQL procedures. It works well, but can be a bit difficult to follow at times. Everything is database driven, from the displays to the data in files exported for the number crunching apps, which then send result data back to the database.

Taking the concept to the extreme, the entire crt.sh site is served from postgres. It literally constructs the html in stored procedures, and uses some small apache httpd extensions to send requests to the db, and use the response (including letting the DB response dictate specific HTTP headers when required).

You could return linked data via JSON-LD and get the best of both worlds - standardization plus smart UX.

This has been the appeal behind Phoenix LiveView for me.

This all feels backwards for me. I mean I already did YEARS of JSF with facelets/richfaces/icefaces/primefaces/JSF2.0

Okay, yeah we can serve UI over the wire. It's possible to make it work. But the experience will never be the same as a a native application that's designed to handle offline functionality, interactivity and optimistic updates.

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

And our tool takes this code and generates a single query to fetch exactly the data we need in one shot. These queries are easy to write because it's all fully typed as well.

Sounds awesome. To anyone interested in that approach, the closest open-source stack would be:

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

BlitzJS does something like this as well

Why do you suggest Unpoly over Alpine JS now? (Regarding a previous comment of yours)

code on demand is optional

self describing messages and HATEOAS is not

would love to chat w/ fielding some time

Yes, it's optional. Not forbidden. Do you understand the difference?

htmx is a breath of fresh air compared to the various Javascript frameworks.

Thank you for creating/updating it!

you just re-invented XML + Schemas, just in binary format. We had all this in the 90s and early 2000s. JSON became popular for a good reason.

Good reason being JSON is trivial to parse in the browser and webapps exploded in use? It's a valid reason but that doesn't mean something with a strong schema that has a good user experience is bad?

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?

this technology has been around for a while: Webservices (WSDL+SOAP all based on XML)

WTLDR we have reinvented SOAP