8 ms·

> The application language was a pass through later between the client and the database. This style of doing things resulted in spaghetti style unmanageable da

> The application language was a pass through later between the client and the database. This style of doing things resulted in spaghetti style unmanageable databases, filled with an unknowable number of triggers and procedures, all written in PL/SQL (which is much, much worse than either Java or PHP). The reason why ORMs started to become popular is that you can write your application without filling your DB with arcane and inscrutable logic

coliveira 7y ago
This is trying to avoid the reality that the data is in a database. A database is not just a flat storage area that can be poked by code from outside. It has a lot of relationships and integrity constraints that need to be maintained by the DB itself, and all this is expressed in SQL/stored procs. Trying to avoid this is basically removing the power of a DB and converting it into just an expensive and cumbersome storage area.
karatestomp 7y ago
But going too far the other way is how you end up with performance 100-1000x worse than it should be. Which is an actual thing that happens quite often in the wild. Also often ends up heavily dependent on some ORM or framework, making rewrites or multi-client DB access dangerous and painful.
- eternauta3k 7y ago
  You can create pretty complex SQL queries with SqlAlchemy's ORM, without going back and forth to the DB.
harikb 7y ago
There is an entire world between ORM and PL/SQL. Programmatically constructing SQL statements is also a thing. Just because someone writes SQL does not mean SQL needs to be spread through out code or that we need to have lot of logic in PL/SQL. Of course, there will be cases where a store procedure is desired (any kind of validation that cannot be expressed as fkeys, canonicalization of some core data components etc). I worked on DBs writing SQL for several years with only minimal code as stored procedures. Of course the database vendor and culture also plays a role. We were primarily a mysql/postgresql shop
- resu_nimda 7y ago
  How did you minimize roundtrips between server and DB, or did you find that they were not a big concern? I'm working on a project with a Postgres database, and as it gets more complex I'm moving more stuff into stored procedures, pretty much wherever a single action requires multiple statements in series (e.g. check if this thing exists, check a value, get the id of some other thing, on success update another table). Of course I would prefer to leave as much of it in the middle tier as possible because the ergonomics are better there but I don't want to sacrifice performance.
  - monocasa 7y ago
    I think they're saying that you profile your changes on an individual query. Yeah, if all of your queries are slow, you've got more work to do, but that doesn't change the overall process of optimization.
jayd16 7y ago
This is the exact fear of SQL that's being talked about. You can make a mess of anything but its not hard to have a well maintained relational DB with minimal cruft.
jtdev 7y ago
In my observations, ORM use has a perspicuous relationship to piles of arcane spaghetti code. Not to mention, 50%+ of ORM managed DB schemas that I've observed don't have proper constraints, indexes, relationships, etc. Because the developers using the ORM think it's a magical tool that makes understanding SQL and relational databases optional.
- oftenwrong 7y ago
  When you give an object the power to access to the db, you invariably end up with db access everywhere you can pass that object.
breischl 7y ago
But now you're filling your application with arcane and inscrutable logic, with an extra layer of abstraction via the ORM to make it even less scrutable. I think one should view a SQL DB like a microservice. Instead of REST endpoints (or gRPC or whatever), create stored procedures. These define a strong contract with your DB, the capabilities that it provides to your app(s). Now you know what the query and insert patterns are, and can tweak the table layout under the covers without screwing up your application code. Of course you can abuse this into a spaghetti monolith, just like you can evolve a microservice into a spaghetti monolith, but you shouldn't. There's no technology that will prevent you from making poor architectural decisions, you just have to not go down those dark paths.
- wenc 7y ago
  > Instead of REST endpoints (or gRPC or whatever), create > stored procedures. These define a strong contract with your DB Exactly. It took me years to grasp this, but when I did my code became much simpler. A REST API (that returns JSON) has to contort a tabular data structure into a loosely-typed hierarchical data structure (JSON) which has to be read back, reconstructed and in many instances type-checked (e.g. DATETIMEs are not native to JSON, nuanced datatypes like DECIMAL(18,3) are lost). Whereas a SQL interface returns data in its native tabular format with all the correct types.
- LouisSayers 7y ago
  I remember people I used to work with arguing against stored procedures for two main reasons. 1) Version Control - I guess a lot of the stored procedures were being put straight into the DB without recording a history of the changes. These days you could easily do this using DB migrations I guess. 2) Testing - is unit testing a thing for Stored Procedures? I guess again, you might be able to do this from code as well programatically adding a stored procedure, running a bunch of tests and removing it again. I do wonder - what do people generally do in practice for overcoming these objections? Does anyone have any other objections around using stored procedures?
  - Ma8ee 7y ago
    I'm using Red-gate tools for all of the above. They are covering source control and unit testing and a lot more, but only for SQL Server. My previous employer used some homespun systems for the same. A bit clunky, but easier to do than most people thinks.
wenc 7y ago
There's a middle way which is very powerful: SQL views (just SQL queries; no triggers or procedures) Here's a powerful mindset trick: think of SQL views as an sort of a REST API, but whose access language is SQL and not HTTP, and that returns data in a table rather than JSON (hierarchical). I once tried to build a REST API to a database, and someone told me I already had a battle-tested and highly performant API that outperformed REST at scale -- it's called SQL. A SQL view is a dynamic lens into the underlying tables, so even if the underlying tables/schemas were to change, your consumers don't care as long as they can access the SQL View. SQL views are also composable: you can build SQL views on top of other SQL views, and any changes made in the base views are propagated throughout. Need to add/transform a field? Do it in the view. Need pull in auxiliary data? Bring it in through a JOIN in the view. I've built many systems by composing SQL views and they're very maintainable and very flexible. They're kind of like function compositions but on tabular data. The rule of thumb is: always access a database through a view, never the underlying raw tables. In computer science, a great many maintainability issues are alleviated through a layer of abstraction/indirection, and SQL views provide exactly that. This centralization of the core logic becomes especially powerful if the database is accessed from multiple consumers (webapps, analytics backends, Tableau, ML tools, etc.) The "API" remains consistent throughout.
- jwdunne 7y ago
  This is a very interesting comment! Two questions: Do you have any example code that shows how this works? I get what you’re saying intuitively but example code will help me bring it to table. What about cross cutting concerns? I’ve found stored procedures to be a performant solution here. By version controlling them, and limiting to pure functions, I found them quite maintainable. Would you instead just define a new view, or extend an existing one, or refactor into a separate view that’s then joined into the existing views? I haven’t delved as far as views, admittedly. One app featured a bit of complicated logic and eschewing the ORM in favour of raw SQL helped (instead of getting tangled up in Demeter chains). Despite new developers, who have used purely ORM for years, shitting their pants at the raw SQL, both of us who worked on it felt it was the right call. We feel much better about leveraging more of the database in new projects. In fact, when we took our experience to a Django project, my colleague wrote a Manager method in such a way that an ORM favouring developer questioned because it looked too much like SQL. But it was the obvious implementation to us after using raw SQL. And, after benchmarking, the most performant.
pjmlp 7y ago
Given how strong typed PL/SQL is, based on Ada, graphical tooling like SQL Developer exists, I wonder why it is much worse than either Java or PHP. High performance SQL code should stay on the database, instead of doing wire round trips.

coliveira 7y ago

This is trying to avoid the reality that the data is in a database. A database is not just a flat storage area that can be poked by code from outside. It has a lot of relationships and integrity constraints that need to be maintained by the DB itself, and all this is expressed in SQL/stored procs. Trying to avoid this is basically removing the power of a DB and converting it into just an expensive and cumbersome storage area.

karatestomp 7y ago

But going too far the other way is how you end up with performance 100-1000x worse than it should be. Which is an actual thing that happens quite often in the wild. Also often ends up heavily dependent on some ORM or framework, making rewrites or multi-client DB access dangerous and painful.

eternauta3k 7y ago
You can create pretty complex SQL queries with SqlAlchemy's ORM, without going back and forth to the DB.

harikb 7y ago

There is an entire world between ORM and PL/SQL. Programmatically constructing SQL statements is also a thing. Just because someone writes SQL does not mean SQL needs to be spread through out code or that we need to have lot of logic in PL/SQL. Of course, there will be cases where a store procedure is desired (any kind of validation that cannot be expressed as fkeys, canonicalization of some core data components etc). I worked on DBs writing SQL for several years with only minimal code as stored procedures. Of course the database vendor and culture also plays a role. We were primarily a mysql/postgresql shop

resu_nimda 7y ago
How did you minimize roundtrips between server and DB, or did you find that they were not a big concern? I'm working on a project with a Postgres database, and as it gets more complex I'm moving more stuff into stored procedures, pretty much wherever a single action requires multiple statements in series (e.g. check if this thing exists, check a value, get the id of some other thing, on success update another table). Of course I would prefer to leave as much of it in the middle tier as possible because the ergonomics are better there but I don't want to sacrifice performance.
- monocasa 7y ago
  I think they're saying that you profile your changes on an individual query. Yeah, if all of your queries are slow, you've got more work to do, but that doesn't change the overall process of optimization.

jayd16 7y ago

This is the exact fear of SQL that's being talked about. You can make a mess of anything but its not hard to have a well maintained relational DB with minimal cruft.

jtdev 7y ago

In my observations, ORM use has a perspicuous relationship to piles of arcane spaghetti code. Not to mention, 50%+ of ORM managed DB schemas that I've observed don't have proper constraints, indexes, relationships, etc. Because the developers using the ORM think it's a magical tool that makes understanding SQL and relational databases optional.

oftenwrong 7y ago
When you give an object the power to access to the db, you invariably end up with db access everywhere you can pass that object.

breischl 7y ago

But now you're filling your application with arcane and inscrutable logic, with an extra layer of abstraction via the ORM to make it even less scrutable. I think one should view a SQL DB like a microservice. Instead of REST endpoints (or gRPC or whatever), create stored procedures. These define a strong contract with your DB, the capabilities that it provides to your app(s). Now you know what the query and insert patterns are, and can tweak the table layout under the covers without screwing up your application code. Of course you can abuse this into a spaghetti monolith, just like you can evolve a microservice into a spaghetti monolith, but you shouldn't. There's no technology that will prevent you from making poor architectural decisions, you just have to not go down those dark paths.

wenc 7y ago
> Instead of REST endpoints (or gRPC or whatever), create > stored procedures. These define a strong contract with your DB Exactly. It took me years to grasp this, but when I did my code became much simpler. A REST API (that returns JSON) has to contort a tabular data structure into a loosely-typed hierarchical data structure (JSON) which has to be read back, reconstructed and in many instances type-checked (e.g. DATETIMEs are not native to JSON, nuanced datatypes like DECIMAL(18,3) are lost). Whereas a SQL interface returns data in its native tabular format with all the correct types.
LouisSayers 7y ago
I remember people I used to work with arguing against stored procedures for two main reasons. 1) Version Control - I guess a lot of the stored procedures were being put straight into the DB without recording a history of the changes. These days you could easily do this using DB migrations I guess. 2) Testing - is unit testing a thing for Stored Procedures? I guess again, you might be able to do this from code as well programatically adding a stored procedure, running a bunch of tests and removing it again. I do wonder - what do people generally do in practice for overcoming these objections? Does anyone have any other objections around using stored procedures?
- Ma8ee 7y ago
  I'm using Red-gate tools for all of the above. They are covering source control and unit testing and a lot more, but only for SQL Server. My previous employer used some homespun systems for the same. A bit clunky, but easier to do than most people thinks.

wenc 7y ago

There's a middle way which is very powerful: SQL views (just SQL queries; no triggers or procedures) Here's a powerful mindset trick: think of SQL views as an sort of a REST API, but whose access language is SQL and not HTTP, and that returns data in a table rather than JSON (hierarchical). I once tried to build a REST API to a database, and someone told me I already had a battle-tested and highly performant API that outperformed REST at scale -- it's called SQL. A SQL view is a dynamic lens into the underlying tables, so even if the underlying tables/schemas were to change, your consumers don't care as long as they can access the SQL View. SQL views are also composable: you can build SQL views on top of other SQL views, and any changes made in the base views are propagated throughout. Need to add/transform a field? Do it in the view. Need pull in auxiliary data? Bring it in through a JOIN in the view. I've built many systems by composing SQL views and they're very maintainable and very flexible. They're kind of like function compositions but on tabular data. The rule of thumb is: always access a database through a view, never the underlying raw tables. In computer science, a great many maintainability issues are alleviated through a layer of abstraction/indirection, and SQL views provide exactly that. This centralization of the core logic becomes especially powerful if the database is accessed from multiple consumers (webapps, analytics backends, Tableau, ML tools, etc.) The "API" remains consistent throughout.

jwdunne 7y ago
This is a very interesting comment! Two questions: Do you have any example code that shows how this works? I get what you’re saying intuitively but example code will help me bring it to table. What about cross cutting concerns? I’ve found stored procedures to be a performant solution here. By version controlling them, and limiting to pure functions, I found them quite maintainable. Would you instead just define a new view, or extend an existing one, or refactor into a separate view that’s then joined into the existing views? I haven’t delved as far as views, admittedly. One app featured a bit of complicated logic and eschewing the ORM in favour of raw SQL helped (instead of getting tangled up in Demeter chains). Despite new developers, who have used purely ORM for years, shitting their pants at the raw SQL, both of us who worked on it felt it was the right call. We feel much better about leveraging more of the database in new projects. In fact, when we took our experience to a Django project, my colleague wrote a Manager method in such a way that an ORM favouring developer questioned because it looked too much like SQL. But it was the obvious implementation to us after using raw SQL. And, after benchmarking, the most performant.

pjmlp 7y ago

Given how strong typed PL/SQL is, based on Ada, graphical tooling like SQL Developer exists, I wonder why it is much worse than either Java or PHP. High performance SQL code should stay on the database, instead of doing wire round trips.