5 ms·

Don't do this. Don't FUCKING do this. I've spent the last 15 months untangling a mess of an e-commerce system that was built exactly like this. The problem i

by maxk42 7y ago

Don't do this. Don't FUCKING do this. I've spent the last 15 months untangling a mess of an e-commerce system that was built exactly like this. The problem is SQL is not a programming language and the only way to accomplish some things directly in the database is by creating solutions with O(n^2) or worse running-time. Our system has queries with O(n^4) time complexity. Less than two weeks ago, I had to untangle a query that was over 3,100 lines long with 18 sub-queries and was able to refactor it into about 100 lines of SQL and another 100 lines of code. Making matters worse, changes to stored procedures don't show up in git, and can break some regression-testing tools, making it very difficult to track changes and hunt down bugs. Use the right tool for the job: Don't treat every problem like a nail once you have a hammer. Use SQL for data and your language of choice for logic. Don't create a monstrosity that someone else will have to spend weeks fixing just because you can.

dastx 7y ago
> Making matters worse, changes to stored procedures don't show up in git This is not necessarily true. It depends on your environment. You can track changes to db with the right tools. Rest of your points I completely agree. Don't put unnecessary complexity in your db. It's a database, not a code repo.
- OJFord 7y ago
  I don't like migrations, it's like doing version control with dated copies of files. I wish there were (perhaps I'll be told there is) a migration system that used a single file per table, and was git-aware so that when the live schema didn't match the checked out schema file, it would determine a migration from the historical version that it does match. Alas, I don't think it's possible, because such a file would describe a state, not, as migration files do, transitions between states. It's tempting to imagine that it's possible to robustly infer transitions from one to another, but I've used Django.
  - samus 7y ago
    - Liquibase expects the scemea to be modeled in its schema language. This makes it possible to compute changes between the database and the new schema and apply those changes, or to just create old-fashioned migration scripts. - Th. Doctrine and Hibernate ORMs can do similar things. - Flyway expects developers to write migrations by themselves, either in Java or in SQL, and just executes them and records state information. It's not too bad to write migrations yourself because the data you work with is usually valuable.
    - kokx 7y ago
      Yes, they can do similar things. However, there are several cases which you cannot handle automatically, due to incomplete information: - The current schema has a column A and the new version does not have A but a similar column B. Is this a new column? Or should A be renamed into B? (same for renaming tables) - You add a new table A which contains columns from B, which are also removed from B. Should this data be copied? - You change the structure of a table, and have some of it split off into different tables. How does the schema tool know how to rearrange the current data? There are probably some more cases, but these are the ones I could think of quickly.
  - y4mi 7y ago
    While I think what you want is understandable - it's also extremely dangerous. You'd no longer be absolutely certain you're executing the same migration in production as on the testing environments. That's exactly why I think Django's migrations are insanely good. Simple ones are autogenerated and everything else is possible as well, with either python code or direct sql queries
cle 7y ago
It always helps to consider the context. This is a personal website for Derek. You can do all kinds of crazy stuff and get away with it in this context, because you don't have to work with others. If you're working on a big project which requires a team of people (or may grow into a team), then yeah, don't do this. Keep your systems as idiomatic, unsurprising, and consistent as possible, unless you have a very good reason not to. The less cognitive load, the easier and faster it is for others to help without making mistakes.
k__ 7y ago
Isn't PL/SQL a full fledged programming language?
xiphias2 7y ago
If you could refactor the code into smaller and faster SQL, that means the code was badly written, not that SQL (and stored procedure language) itself is bad. Also almost every algorithm can be written in PLSQL if needed, so yuu can surely improve on the O(n^4) time complexity. I agree with the procedure change tracking, that's much harder, but server side programs can be extremely responsive (low latency) and powerful.
- BubRoss 7y ago
  He never said SQL was bad, he said use it to query data because that's what it is good at.
  - xiphias2 7y ago
    I was using it and PL/pgSQL together to modify data and although I had to think differently, working in the relational space even with modifying data is awsome (of course you have to use cursors).
JohnBooty 7y ago
Every horror you've just described can happen up in the app layer as well. The problem is SQL is not a programming language and the only way to accomplish some things directly in the database is by creating solutions with O(n^2) or worse running-time. Our system has queries with O(n^4) time complexity. Two observations here. 1. The approach being espoused is not, "Move everything down into the database layer at all costs, no exceptions." It's more like, "Many things are simpler and faster at the database layer." 2. These O(n^2) (and n^3, n^4, whatever) horrors are at least as common at the app layer. I have dealt with my fair share at every possible level of the stack. I had to untangle a query that was over 3,100 lines long with 18 sub-queries I just want to say: ouch. I am sorry!
- yonixw 7y ago
  You say "could happen" he says "must happen". SQL won't be able to lower the complexity needed. But in the app level it is just a matter of being good programmer or under good review process.
samus 7y ago
> Making matters worse, changes to stored procedures don't show up in git, and can break some regression-testing tools, making it very difficult to track changes and hunt down bugs. If stores procedures become part of the system, they belong into the SCM as well. The deployment process has to include the stored procedures as well. Different to tables and other constructs that have state, stored procedures can be easily migrated. In general I agree that stored procedures massively underdeliver in many aspects, but not properly treating the database as the application server it has become is part of the problem.
onion2k 7y ago
Making matters worse, changes to stored procedures don't show up in git... They do if you use database migrations.
dustingetz 7y ago
Datomic solves this tension by letting you upload Clojure code into the database to run as (basically) a stored procedure. So there isn’t a tension between programming language and query language, there’s no network between them, they compose directly and are interchangeable. Do what makes your code simplest. Program your database with the usual data structures, if-statements, function composition, the standard library you’re used to, and share library functions between your backend and database at will (even compile them to ClojureScript and run them in the UI!)
- maxdemarzi 7y ago
  Yup. Same with Neo4j. You can write your stored procedures in Java ( or any JVM) language. Import any library you want, go crazy.
  - derision 7y ago
    Same with MSSQL. You can write stored procedures in C#
  - dustingetz 7y ago
    I wasn't clear enough. in Datomic, you upload your entire application code (web server and all) into the database and run it from the same JVM cluster as the database in the same memory space as the database. There is no opportunity for dichotomy between application logic and database logic because the database is serving the application (or the application is serving the database...this is now just a matter of perspective)
  - fulafel 7y ago
    Yes, PostgreSQL does this too, you can use JVM/Python/Node etc. But it's not really geared towards being your app server like Datomic is.
phoe-krk 7y ago
Stored procedures MUST be stored in the VCS. On every upgrade, the older set of procedures MUST be fully deleted and the new set of procedures MUST be installed. Otherwise, you have just created a huge hole in your buuild reproducibility. SQL (including pl/SQL) is a programming language, therefore treat it like a programming language - keep all of it in a versioning system and ensure that you can trace every piece of code that runs inside your database to a piece of code in your VCS.
grizzles 7y ago
I do this and I rarely have a stored procedure that's bigger than 25 LOC. Most are around ~5 LOC and look like this: https://gist.github.com/ericbets/a6a82041a9fd9181ee52ac0d34640e7c https://gist.github.com/ericbets/a6a82041a9fd9181ee52ac0d346...

by maxk42 7y ago

dastx 7y ago

> Making matters worse, changes to stored procedures don't show up in git This is not necessarily true. It depends on your environment. You can track changes to db with the right tools. Rest of your points I completely agree. Don't put unnecessary complexity in your db. It's a database, not a code repo.

OJFord 7y ago
I don't like migrations, it's like doing version control with dated copies of files. I wish there were (perhaps I'll be told there is) a migration system that used a single file per table, and was git-aware so that when the live schema didn't match the checked out schema file, it would determine a migration from the historical version that it does match. Alas, I don't think it's possible, because such a file would describe a state, not, as migration files do, transitions between states. It's tempting to imagine that it's possible to robustly infer transitions from one to another, but I've used Django.
- samus 7y ago
  - Liquibase expects the scemea to be modeled in its schema language. This makes it possible to compute changes between the database and the new schema and apply those changes, or to just create old-fashioned migration scripts. - Th. Doctrine and Hibernate ORMs can do similar things. - Flyway expects developers to write migrations by themselves, either in Java or in SQL, and just executes them and records state information. It's not too bad to write migrations yourself because the data you work with is usually valuable.
  - kokx 7y ago
    Yes, they can do similar things. However, there are several cases which you cannot handle automatically, due to incomplete information: - The current schema has a column A and the new version does not have A but a similar column B. Is this a new column? Or should A be renamed into B? (same for renaming tables) - You add a new table A which contains columns from B, which are also removed from B. Should this data be copied? - You change the structure of a table, and have some of it split off into different tables. How does the schema tool know how to rearrange the current data? There are probably some more cases, but these are the ones I could think of quickly.
- y4mi 7y ago
  While I think what you want is understandable - it's also extremely dangerous. You'd no longer be absolutely certain you're executing the same migration in production as on the testing environments. That's exactly why I think Django's migrations are insanely good. Simple ones are autogenerated and everything else is possible as well, with either python code or direct sql queries

cle 7y ago

It always helps to consider the context. This is a personal website for Derek. You can do all kinds of crazy stuff and get away with it in this context, because you don't have to work with others. If you're working on a big project which requires a team of people (or may grow into a team), then yeah, don't do this. Keep your systems as idiomatic, unsurprising, and consistent as possible, unless you have a very good reason not to. The less cognitive load, the easier and faster it is for others to help without making mistakes.

k__ 7y ago

Isn't PL/SQL a full fledged programming language?

xiphias2 7y ago

If you could refactor the code into smaller and faster SQL, that means the code was badly written, not that SQL (and stored procedure language) itself is bad. Also almost every algorithm can be written in PLSQL if needed, so yuu can surely improve on the O(n^4) time complexity. I agree with the procedure change tracking, that's much harder, but server side programs can be extremely responsive (low latency) and powerful.

BubRoss 7y ago
He never said SQL was bad, he said use it to query data because that's what it is good at.
- xiphias2 7y ago
  I was using it and PL/pgSQL together to modify data and although I had to think differently, working in the relational space even with modifying data is awsome (of course you have to use cursors).

JohnBooty 7y ago

Every horror you've just described can happen up in the app layer as well. The problem is SQL is not a programming language and the only way to accomplish some things directly in the database is by creating solutions with O(n^2) or worse running-time. Our system has queries with O(n^4) time complexity. Two observations here. 1. The approach being espoused is not, "Move everything down into the database layer at all costs, no exceptions." It's more like, "Many things are simpler and faster at the database layer." 2. These O(n^2) (and n^3, n^4, whatever) horrors are at least as common at the app layer. I have dealt with my fair share at every possible level of the stack. I had to untangle a query that was over 3,100 lines long with 18 sub-queries I just want to say: ouch. I am sorry!

yonixw 7y ago
You say "could happen" he says "must happen". SQL won't be able to lower the complexity needed. But in the app level it is just a matter of being good programmer or under good review process.

samus 7y ago

> Making matters worse, changes to stored procedures don't show up in git, and can break some regression-testing tools, making it very difficult to track changes and hunt down bugs. If stores procedures become part of the system, they belong into the SCM as well. The deployment process has to include the stored procedures as well. Different to tables and other constructs that have state, stored procedures can be easily migrated. In general I agree that stored procedures massively underdeliver in many aspects, but not properly treating the database as the application server it has become is part of the problem.

onion2k 7y ago

Making matters worse, changes to stored procedures don't show up in git... They do if you use database migrations.

dustingetz 7y ago

Datomic solves this tension by letting you upload Clojure code into the database to run as (basically) a stored procedure. So there isn’t a tension between programming language and query language, there’s no network between them, they compose directly and are interchangeable. Do what makes your code simplest. Program your database with the usual data structures, if-statements, function composition, the standard library you’re used to, and share library functions between your backend and database at will (even compile them to ClojureScript and run them in the UI!)

maxdemarzi 7y ago
Yup. Same with Neo4j. You can write your stored procedures in Java ( or any JVM) language. Import any library you want, go crazy.
- derision 7y ago
  Same with MSSQL. You can write stored procedures in C#
- dustingetz 7y ago
  I wasn't clear enough. in Datomic, you upload your entire application code (web server and all) into the database and run it from the same JVM cluster as the database in the same memory space as the database. There is no opportunity for dichotomy between application logic and database logic because the database is serving the application (or the application is serving the database...this is now just a matter of perspective)
- fulafel 7y ago
  Yes, PostgreSQL does this too, you can use JVM/Python/Node etc. But it's not really geared towards being your app server like Datomic is.

phoe-krk 7y ago

Stored procedures MUST be stored in the VCS. On every upgrade, the older set of procedures MUST be fully deleted and the new set of procedures MUST be installed. Otherwise, you have just created a huge hole in your buuild reproducibility. SQL (including pl/SQL) is a programming language, therefore treat it like a programming language - keep all of it in a versioning system and ensure that you can trace every piece of code that runs inside your database to a piece of code in your VCS.

grizzles 7y ago

I do this and I rarely have a stored procedure that's bigger than 25 LOC. Most are around ~5 LOC and look like this: https://gist.github.com/ericbets/a6a82041a9fd9181ee52ac0d34640e7c https://gist.github.com/ericbets/a6a82041a9fd9181ee52ac0d346...