3 ms·

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

by gnode 7y ago

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

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

by 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
- doctor_eval 7y ago
  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.
- erichanson 7y ago
  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.
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.