Hacker News Reader

Comment by jagged-chisel | Hacker News Reader

Experienced something like this early in my career when a co-worker discovered PL/SQL - he campaigned for putting the entire web app into an Oracle database with all the logic in stored procedures.

Experienced a slightly different take a few years later where Some Genius wrote an interpreter in stored procedures in an off-brand RDBMS. Web pages were a combination of HTML and his custom language, and these pages were stored in the database.

I have strong negative opinions about putting a web development platform entirely inside an RDBMS.

View on HN

> he campaigned for putting the entire web app into an Oracle database with all the logic in stored procedures.

Oracle have already done this, it's called Application Express[0]. I used it for my last job. In practice it was pretty good for fast prototyping and iterating on database-backed apps. It took the schema as the source of truth and would add useful interface features based on it (eg, dropdown lists derived from foreign keys, converting some check constraints to javascript validation code etc).

But on the downside it was essentially untestable and version control meant dumping a massive file of autogenerated PL/SQL and checking it into git. For anything more complicated than basic CRUD and reporting you'd wind up cracking the hood to directly use PL/SQL and then skin it with APEX. The tradeoff being that you lost some of the roundtrip niceties.

I wouldn't recommend it in general, but it fit ... OK in its context. It was included in the Oracle license, the org had experience with operating Oracle and it allowed each of us in a small programming group to build little apps to solve genuine business problems that were either too small to select OSS/COTS or too specialised to find anything to select.

The best part of the job was getting rid of painful processes built around sharing Excel and Word docs. I remember occasion when a user broke down in tears because a (to me) trivial app meant that a stressful, high-pressure, error-prone month-long process turned into a non-event. One of the best days of my career and I partially owe it to a platform that I think is, in most respects, a mistake.

[0] https://apex.oracle.com/en/

This was before an official release, late 1990s. I'm sure he'd gotten his hands on either some documentation discussing the concept, or an early alpha release of Oracle's ApEx.

Might have been Oracle Web DB, it predated the APEX line but was roughly the same concept and released in the late 90's.

Oh very possibly.

Wikipedia reckons HTML DB (the original package) was released in 2004, but the APEX website clear claims to being 19 years old, which would put it circa 2000.

But in the meantime if you're doing late-90s CGI with Perl, why not `SELECT my_amazing_function()`?

Hey, buddy, we were cutting edge with the brand-spankin-new ASP! (Although, we did have a Perl guy who insisted we'd be renaming all our .asp files to .aspl because MS had 'opened up' the scripting engine to take whatever language you'd want to write an engine for ... and a Perl engine existed at that moment.)

Not surprising. Databases have been basically awful at being approachable from the command-line. Let's say I have some HTML in some field in the db and want to make a change to it from the terminal. How do I do this?

PostgreSQL says use an UPDATE statement, but that looks like "update template set content='<html><body>......</html>' where id = x", which means you have to paste the entire content of the page into the update statement. There's no CL-based field editor, just one of many reasons why the db feels like a black box. Awful.

Aquameta has a filesystem integration layer that lets you browse the database from the command line, grep database content, edit the content of a field using your preferred text editor. http://blog.aquameta.com/intro-chpater2-filesystem/

Generally just trying to ease these pain points has been a big goal of the project... still a lot to do.

That is incorrect because PostgreSQL has XML operators.

You're not the first to have "strong negative opinions" about putting business logic in an RDBMS.

Now, that's not quite what you said! You're against "putting a web development platform entirely inside an RDBMS", and this much I agree with.

But you seem to object to putting business logic in the RDBMS as well, and I want to tell you why I don't agree with that.

The problem with not putting business logic in an RDBMS is that you risk ending up with an application which evolves towards... implementing features already provided by the RDBMS.

You also need to be very careful with direct DB access, since anything you do there will not be implementing a lot of your business logic. This means you can't use things like PostgREST to get a REST API for free. Conversely, if you do want to use PostgREST or alike, you must move your business logic into the RDBMS.

Now, I understand not wanting to do this with Oracle. There are many reasons for that. And I understand not wanting anyone to write an interpreter in a PL/SQL! I also understand not wanting static HTML and other such resources stored in the DB -- there's nothing wrong with using a filesystem and $FAVORITE_VCS for that, and indeed, the version control in Aquameta is something I don't quite approve of. But none of that says you shouldn't have business logic in the RDBMS.

I don't disagree particularly with any of this, and I'd say an immature part of Aquameta is where to put the business logic. I think though that Aquameta's approach is somewhat agnostic to this whole problem. As a tool right now, it's good at CRUD and a free REST API (that reflects the schema) and rapid UI development, but the best pattern for what you would typically put in an ORM isn't figured out. But, the approach really doesn't constrain adding a middleware layer if one is so inclined, or alternately putting all that logic in the database. I think there's a place for that for sure. As long as that place is somewhere in the database. :)

For a good overview of what power and speed you can have by "putting a web development platform entirely inside an RDBMS", check out this video called "Twitter-like App in 20 minutes with Oracle APEX"

https://www.youtube.com/watch?v=bosMSghczUo

I'm not sure its "should I do this" but more "can I do this".

PL/SQL is really a powerful language and entirely capable of this. It is not a language who's power is best utilized for this -- it is a maze to encode (much) business logic within the DB.

Probably better to use PL/V8 these days.

> I have strong negative opinions about putting a web development platform entirely inside an RDBMS.

What about your experiences led to your negative opinions?

Off the top of my head:

* version control

* environment mismatches (think managing dev, staging, prod with multiple devs)

* deployment management

* discoverability of where things live so you can maintain them, and autocomplete in IDEs

The last is the big one. If a client asks me to change the header, and me going to my IDE and quick searching "header" doesn't automatically show me all header related files/code then your platform has already lost the developer experience battle. Something like this would need tooling to match what is already possible in that regard.

Indeed these were the major downsides.

Further, in my Oracle instance, relying on the late-1990s Oracle database server to also run our business logic came with performance and scalability concerns, especially around licensing.

In my other case, the custom language was terrible, but that's not a complaint about the use of the db engine. The interpreter was implemented in stored procedures, the pages were stored in text fields ... every bit of the stack depended entirely on this off-brand database that had its own issues (like corrupt indexes that needed repairing about once per month.)

I think ultimately, in both cases it just felt so much like putting all your eggs in one basket and then hoping you never needed to augment the basket with additional baskets, nor replace the basket with one of a different shape or made of other materials.

While I agree with the overall negative opinion of overzealous stored procedure usage, to echo your last sentence, I've come to realize many of these problems are completely solvable with tooling. A personal anecdote, and please excuse the self-plug:

I'm the author of an infrastructure-as-code tool for MySQL/MariaDB schema management, Skeema [1]. Basically it allows you to store CREATE statements in a repo, and you can diff/push/pull between the repo and live DB environments. Originally the tool only supported tables, as my personal MySQL experience skews towards social networking companies that banned stored procs outright.

Earlier this year a company generously sponsored development of stored proc/func support in Skeema. And although I've long been a stored proc skeptic, I have to say my opinion has softened considerably after building this functionality. It essentially solves the first 3 bullets you've listed here: allowing storage of CREATE PROCEDURE statements in a git repo; ability to diff the current state of the repo against any live db environment; ability to push the current repo state to any live db environment. And the 4th bullet just depends on your IDE's support for different SQL / T-SQL / PLSQL dialects.

I still have some scalability and maintainability concerns around extensive use of stored procs (especially in MySQL/MariaDB), but nonetheless found this experience to be unexpectedly eye-opening. My perspective went from "stored procs are an operational nightmare, avoid" to "huh these are actually quite useful and totally manageable given a solid development/deployment story."

[1] https://www.skeema.io

Intriguing product - and amazing work! How do you handle column renames and more complicated data migrations (e.g. split "full_name" on whitespace)? Django's migration framework handles these by having you check into your VCS each "diff" in the migration DAG, and providing enough metadata to know how to go forward and back, but this also means that to change VCS branches you need to roll back migrations in a highly counterintuitive way. If there was a way to have a checked-in declarative schema, with metadata to indicate how data migrations should work so you can just `git checkout; skeema push`, it would be a gamechanger.

Thanks! Great questions -- unfortunately the short answer to both currently is "not supported yet" :) But there's reasoning behind it being on the back-burner in both cases; in some older threads I've discussed the lack of renames [1] and data migrations [2][3].

[1] https://news.ycombinator.com/item?id=19882611

[2] https://news.ycombinator.com/item?id=20873608

[3] https://news.ycombinator.com/item?id=19882862

We’ve built something very similar for PL/pgSQL. It integrates with Gradle so we even have transitive dependencies on different schemas. It has completely changed our approach to software development - development is faster and the application is literally an order of magnitude faster.

The key was to build the tool up front. Without the tool we couldn’t have done it.

Tooling on databases has been traditionally awful, and I think that has a lot to do with folks' negative experiences with the database taking up more architectural space.

Schema migration is hard. Skeema looks cool. Aquameta's idea with schema migration is to treat the schema as data, so that each table is a meta.table table, each view is a row in the meta.view table, each column... etc. So when version control checks out a new version of the project, if columns say were added, they're just inserted at runtime. Checkout prioritizes meta entities so they happen first and in a sensible order [1] so the migration happens first. This does technically work for all scenarios (I think!), but in the case of say a column rename, it would naively delete all the values and then have to re-set them per row, which could be a big inefficiency. It would still work just fine for smaller tables though. Alternately some kind of optional migration script per commit was another idea.

Localization alone can make a schema complex as well. One of the projects I'm on took on a lot of configurations managed in the database, and nearly all the backend logic in stored procedures. Discoverability is a huge issue, and nobody really understands the schema as a whole. The project itself is only just over a year old with a team of 6-8 devs. In the end, some like it a lot, others not so much, and it's all friction to development.

Reply on news.ycombinator.com