6 ms·

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 wit

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.

gnode 7y ago
> I have strong negative opinions about putting a web development platform entirely inside an RDBMS. What about your experiences led to your negative opinions?
- ehnto 7y ago
  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.
  - jagged-chisel 7y ago
    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.
  - evanelias 7y ago
    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 https://www.skeema.io
    - btown 7y ago
      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.
      - evanelias 7y ago
        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 https://news.ycombinator.com/item?id=19882611 [2] https://news.ycombinator.com/item?id=20873608 https://news.ycombinator.com/item?id=20873608 [3] https://news.ycombinator.com/item?id=19882862 https://news.ycombinator.com/item?id=19882862
  - tracker1 7y ago
    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.
jacques_chester 7y ago
> 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/ https://apex.oracle.com/en/
- jagged-chisel 7y ago
  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.
  - dahdum 7y ago
    Might have been Oracle Web DB, it predated the APEX line but was roughly the same concept and released in the late 90's.
  - jacques_chester 7y ago
    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()`?
erichanson 7y ago
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/ 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.
- LunaSea 7y ago
  That is incorrect because PostgreSQL has XML operators.
justbaker 7y ago
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.
- solidsnack9000 7y ago
  Probably better to use PL/V8 these days.
DatabaseGuy42 7y ago
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 https://www.youtube.com/watch?v=bosMSghczUo
cryptonector 7y ago
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.
- erichanson 7y ago
  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. :)

gnode 7y ago

> I have strong negative opinions about putting a web development platform entirely inside an RDBMS. What about your experiences led to your negative opinions?

ehnto 7y ago
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.
- jagged-chisel 7y ago
  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.
- evanelias 7y ago
  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 https://www.skeema.io
  - btown 7y ago
    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.
    - evanelias 7y ago
      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 https://news.ycombinator.com/item?id=19882611 [2] https://news.ycombinator.com/item?id=20873608 https://news.ycombinator.com/item?id=20873608 [3] https://news.ycombinator.com/item?id=19882862 https://news.ycombinator.com/item?id=19882862
- tracker1 7y ago
  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.

jacques_chester 7y ago

> 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/ https://apex.oracle.com/en/

jagged-chisel 7y ago
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.
- dahdum 7y ago
  Might have been Oracle Web DB, it predated the APEX line but was roughly the same concept and released in the late 90's.
- jacques_chester 7y ago
  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()`?

erichanson 7y ago

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

LunaSea 7y ago
That is incorrect because PostgreSQL has XML operators.

justbaker 7y ago

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.

solidsnack9000 7y ago
Probably better to use PL/V8 these days.

DatabaseGuy42 7y ago

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 https://www.youtube.com/watch?v=bosMSghczUo

cryptonector 7y ago

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.

erichanson 7y ago
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. :)