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-generatorYou 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.
self describing messages and HATEOAS is not
would love to chat w/ fielding some time
Thank you for creating/updating it!
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.
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?
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?
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