8 ms·

Simplify: move code into database functions

81 points by oskarth 11y ago

sitkack 11y ago
The reasoning always offered for treating the database as a dumb store with no intelligence has been about portability. Which is fine if you are selling third party software meant to be installed onto an existing customer's database. But for all other use cases, not using the features of the platform is a mistake. PostgreSQL offers so much! * Stored procedures in Java, Python, Lua, Perl, JavaScript etc [1] * Multicorn [2], query external data sources * Common Table Expressions [3] [1] https://wiki.postgresql.org/wiki/PL_Matrix https://wiki.postgresql.org/wiki/PL_Matrix [2] http://multicorn.org/ http://multicorn.org/ [3] http://en.wikipedia.org/wiki/Hierarchical_and_recursive_queries_in_SQL#Common_table_expression http://en.wikipedia.org/wiki/Hierarchical_and_recursive_quer...
- woah 11y ago
  Yea, I've never really understood the portability argument. How often do people switch between sql dbs?
  - tl 11y ago
    One of the advantages is the ability to run a different database during local development / testing from your production systems (say H2 locally vs. DB2 running in production). Personally, I view this to be an anti-pattern, but some people have to work with systems like this.
    - calpaterson 11y ago
      Yes, this is a very bad idea. Due to the way that query planners work, it's not even a good idea to vary the shape of the dataset between testing and production. If you have a different (bigger, higher stddev, etc) distribution of data in column x, the planner will often complete a query differently
      - ams6110 11y ago
        Agree, unless your need for a DB is quite trivial, then perhaps something like SQLLite for the devs and whatever else for production. However this breaks down quickly once you grow beyond needing very simple INSERT and SELECT type operations.
        sitkack 11y ago
        At which point one should just run SQLite everywhere, It is often excellent for datasets up to about a gig.
  - PaulHoule 11y ago
    It is pretty common that organization X uses a pastiche of different tools for a complicated set of reasons (the most senior of which is that it has grown through acquisition.) Often organization X has a large number of small applications and often big applications contain many small applications. Certainly some libraries and tools that work with different databases can make life easier in this case.
  - mathgeek 11y ago
    It usually refers to portability for multiple deployments. I.e. your fist client runs MSSQL, your second client runs Postgre, and then your next client runs MySQL. Very important in the enterprise, education, etc.
  - sitkack 11y ago
    Back in the dawn of time, customers bought big machines to run all the databases for an organization. So software sold into those orgs needed to support Oracle or MSSQL. Now with machines being much faster and whole app stacks being virtualized the impetus for having an application support multiple databases is greatly reduced.
  - spacemanmatt 11y ago
    At my first major PostgreSQL job, I got there shortly after they had abandoned MySQL. So there's that.
- dorfsmay 11y ago
  Aren't CTE part of the SQL standard now? I'm pretty sure most modern relational servers using SQL (MS SQL, MySQL, Oracle, DB2) support CTE. PS: even SQLite: https://www.sqlite.org/lang_with.html https://www.sqlite.org/lang_with.html
- snaky 11y ago
  and transforms! http://www.depesz.com/2015/05/03/waiting-for-9-5-add-transforms-feature/ http://www.depesz.com/2015/05/03/waiting-for-9-5-add-transfo...
webnrrd2k 11y ago
The biggest reason to use stored procedures to me is that db developers tend to stick around around a business lot longer than web developers, so putting most of the business logic into the db is better for the business in the long run. Like the many developers, I generally develop custom apps specifically for a particular business. When I develop new apps I start by by putting all the intelligence in the serer-side code, so it's easier to change things without needing to go through someone else. Once the apps stable then much of the business intelligence gets moved into stored procedures in the db level.
AdrianRossouw 11y ago
I've gone down this road before, and one of my biggest complaints was around managing and deploying the code built this way. I just found plain server side code easier to reason about.
SchizoDuckie 11y ago
Historyically, I've been against putting logic inside (MySQL) database since it's a bitch to version and maintain (especially cross-database-user) Maybe Postgres is a lot better, but for MySQL I'm staying the hell away from it
- vinceguidry 11y ago
  I think the idea is that you don't have to version and maintain it, your database layer changes far less frequently than your code does. Kind of like how servers used to be managed back in the day. Rebuilding one of those was a major life event, and much of your experience revolved around managing servers through scripts and whatnot, so you didn't sweat the lack of versioning much. Of course nowadays with easy cloud provisioning and tools like Docker, you can version server configurations, so you don't have to put up with it if you don't want to. But databases, I think are at that same in that you should really understand the one you're working with such that you could replicate your current usage from scratch if you really had to, but honestly the chances of you being called upon to do it are minuscule, and the costs of such are going to be borne by the organization you work for anyway.
- olefoo 11y ago
  It does mean you need to treat your data schema and functions as code. My short checklist for doing database centric development (postgresql centered): * Database schema is kept under version control ( essentially all the DDL for a given database is kept in one place and managed as a unit ). You can use `pg_dump -s` to extract the schema from an extant database. * data types and functions are kept in separate files under version control * the database is built and filled automatically for tests, for migrations and for upgrades. Shell script, Makefile, Chef; the tool doesn't matter so long as the process is repeatable and automatic. * data fixtures for testing are kept separate from the code ( own repo usually, since you want to be able to add edge cases and errors to the test database ). * migrations on the production database are run immediately after a backup. Nothing too fancy, just basic hygiene. But staying on top of that means you're ready to do things like build your application onto a new set of machines; or make drastic changes to how your data is structured. There are a number of tools that do much of the scutwork for you ( South for Django, ActiveRecord::Migration for Rails, etc.) but the tools are not a substitute for understanding what your database is doing.
BurningFrog 11y ago
A serious problem with this approach is scaling. When your code is in Ruby/JS/whatever on separate servers, and your traffic increases, you can easily scale up by adding more stateless servers. Going from 1 to 2 DB servers is much harder, and can easily mean a major rewrite, during which your site may not really work.
- falcolas 11y ago
  Absolutely this. Not to mention the need to prematurely scale up DBs due to the added load. Expensive and hard.
  - radicalbyte 11y ago
    The other side of the coin is that you probably won't need to scale. The performance differences can be staggering: I've replaced Java-based procedures which took more than an hour to execute with Stored Procedures which completed within milliseconds. That's an extreme example - the original code was simply incompetent - but I regularly see significant (100x) performance increase in core functions through the switch. Still, nowadays I prefer to have as much logic as possible in code because it's much easier to test and is more flexible than SPs; and it's always possible to move logic to SPs later..
    - hobs 11y ago
      I see databases in general get a lot of flack from developers for this reason, when on the other side of the fence I see a lot of really smart people misunderstanding the tool (as your example alludes to.) I have often come to a stored procedure and not returned 100x, but more like 50,000x by simply changing a few lines of code. tl;dr anyone can write slow code in any language.
      - radicalbyte 11y ago
        > I have often come to a stored procedure and not returned > 100x, but more like 50,000x by simply changing a few > lines of code. Oh, those beautiful nested cursors :)
michaelmcmillan 11y ago
I always enjoy reading about new ways of doing things, so thanks! How would you test your logic in isolation with unit tests using an architecture like this?
- vp89 11y ago
  The same way you test any other piece of code. A stored procedure is just an encapsulation of logic, with inputs and possibly state changes and/or outputs. Expected/actual asserts can be coded in SQL. The code tends to be verbose, but some RDBMS have evolved to include more powerful language features. It's probably possible in almost all RDBMS, but certainly easier in some than others.
jconley 11y ago
Stored procedures, functions, triggers, etc, are very useful tools. They are used regularly in Microsoft SQL Server and Oracle systems. But, one has to be very careful to use them when warranted. The out-of-the-box tooling for debugging, maintaining, versioning, and testing code that lives in your database is not nearly as robust as what you have for most other development environments. The key here is to use the tool when it is most useful. SPROC's are good when: 1. You need extreme data security and you are able to pass authentication tokens from application to database. You can apply authorization rules when you read/write the data, rather than transporting more than you need over the network. This eliminates a number of potential attack vectors. I have worked on HIPAA compliant systems for government agencies that held sensitive data that required all data access to be through stored procedures with proper access permissions, in addition to an authorization tier in the application logic. Very common use case. 2. You need to churn through a bunch of data and need very close data locality to pull that off and meet your performance requirements. 2a. Your database nodes are network IO bound and this is due to applications requesting more data than it needs, and that logic can be performed in the database. 3. You have a very strong DBA/Developer that can manage your data access layer that your application uses as well as code the database. This can abstract the "how" of data storage from the application developer, which can be very useful in a data-centric large application. However, SPROCs are bad if: 1. You have to switch DB providers due to scalability, or cost issues, etc. The switching cost can be huge if there is a ton of logic buried in your database. 2. Your database nodes are CPU bound. In this case you'd want to do as little logic as possible in your DB. This is more rare nowadays, but used to happen. 3. SQL (or whatever-sproc-language) is not a core language you want your team to have to become experts in. 4. You don't like adding more code. You'll end up generating and writing yet another layer of code, this time for stuff that lives inside your database. This code has to be maintained, versioned, etc.
- PaulHoule 11y ago
  You commonly encounter "legacy" code that uses this design and often people who have cut their teeth on currently fashionable techniques put it down. If you want to construct high performance applications, however, moving code close to data solves a lot of problems, particular those involving the reconciliation of durability and scalability. (i.e. transactions work a lot better if you can get all the coordinated parts as close together as possible) This doesn't just go for SQL, but it will go for NoSQL systems that support advanced functionality. Practically it is not too different from the fashion for developing a REST or otherwise "web service" APIs that are consumed by the rest of the system. The only trouble with it is that it is "one more thing" which needs to be managed, which you need to train for, etc. I spent one summer doing a major upgrade to a line of business app and we checked in over 200 database migrations to version control. I don't know if anybody is working without version control in 2015, but 7 years ago that was a common practice and could lead to a lot of pain. Often in the name of encapsulation architects put in a lot of layers, to the effect that if you want to make some small change (say add a second phone number to a user record) you have to make the change in multiple places. I talked with a Rubyist about this and he's like, "it's no problem, just add another phone number" and it's a deep point that systems that are declarative as possible and do code generation are the real way out of the morass.
jalfresi 11y ago
In most of the example he gives, these solutions are ensuring the integrity of the data stored, which I personally have no problem with; that is the job of the database in my view. If I try and store crap the database should refuse, be that via constraints, triggers or stored procedures. As for returning the data in JSON, again, I see no problem with that; you were going to get the data out in rows and columns, what difference does JSON make, especially if it simplifies things, as he is arguing. My main concern is business logic; should business logic be stored in the database. At this point things become a little grey, primarily due to the difficulty in maintaining and managing SQL code. I suppose that PostGres is better that most of the DBs I have had to deal with in the past in this respect in that it supports a wider range of languages for this purpose, but I'd be interested to hear others opinions and experience in this regard.
- calpaterson 11y ago
  Sadly, to some extent the data integrity is part of the business logic. For example, if products can or cannot be sourced from multiple suppliers
- mistermann 11y ago
  I would agree, at least on SQL Server, in my experience writing logic is just brutally painful, weak language and poor debugging.
dorfsmay 11y ago
It depends what you do... If you need to manipulate a lot of data that ends up travelling on the wire, you can save a lot of time by running it on the server. On the other hand, the language on the DB servers are often a bit harder to reason with.
- yawboakye 11y ago
  > the language on the DB servers are often a bit harder to reason with But can't we say that about every other language? I've been writing a lot of PL/pgSQL lately and I've enjoyed every bit of it. For PostgreSQL there's also PL/Python, PL/V8, etc. Those are packages for writing the server code in python and JavaScript respectively and out the window goes the obscure language excuse. But that's if you're on Postgres.
erichmond 11y ago
It's incredible to me that he watched Simple Made Easy (which is an incredible talk), and his mind went to "stick more stuff in the database". Not because that approach wouldn't work, but to me a clear sign of complection would be conflating the medium of data storage (a database) and the business rules that govern the data itself. But I guess that's the the sign of a good talk. It's open to interpretation. It also complicates your scaling story, your caching story, your ability to change databases and a great number of other things. If you're building an app that has no chance of ever possibly needing to scale, or use a different database for caching, searching, or analytic reasons, then this approach may be optimal, but if any of those levers need to move at some point, I think you'd be in for a world of hurt.
- straws 11y ago
  Kind of makes you wish he watched a couple more Rich Hickey talks, like any of the ones around Datomic. The solution there is not "stick more business logic in the database", it's "split up the part responsible for writes from the part that queries it".
- jessaustin 11y ago
  I think we're talking relational DBs here, right? In fairness you already have business rules in the DB, implicitly, just from the layout of your tables. You probably also have foreign keys. At that point, a stored proc that translates business needs like "at customer location X, stop service A on this date and replace it with service B" to the multiple table reads and inserts required, seems like a good idea. It's actually not that weird to keep most of your devs out of the database entirely, in which case they probably appreciate being able to just call a stored proc to get something done.
- wging 11y ago
  He didn't watch 'Simple Made Easy'. He watched a different Hickey talk, 'Simplicity Matters': https://www.youtube.com/watch?v=rI8tNMsozo0 https://www.youtube.com/watch?v=rI8tNMsozo0
jakejake 11y ago
Before the days of GUIs when regular office workers used the command line, the database was an all-in-one application. Stored procedures were created by the DB admin so that the non-techies could run reports with simple commands. I've found it interesting that all of this functionality has remained in databases, but is almost never used in modern web applications.
alkonaut 11y ago
> "In 1997, I started in Perl. In 1998, I switched to PHP. In 2004, a rewrite in Rails. In 2007, back to PHP. In 2009, minimalist Ruby. In 2012, client-side JavaScript. Each time I’d have to re-write all of the logic around the database: how to add a new person into the database, how to verify an invoice is correct, how to mark an order as paid, etc. But that whole time, my trusty PostgreSQL database stayed the same." And what if during that time instead of switching programming languages, it was the storage system that had to be switched? You could easily invert the argument "Don't keep the logic in the DB! I had to switch from Postgres to MSSQL which meant I painstakingly had to convert all my db-logic, I wish it had been in the code instead!"
- devdas 11y ago
  Switching programming languages is far more common than switching databases.
  - alkonaut 11y ago
    *Citation needed You wouldn't even need to switch the database, just add a cache between the storage an the app, or add/switch ORM etc. There is a huge impedance mismatch between table storage and code, but I don't see how it gets more responsive to changed requirements by using logic in the DB. And don't even get me started on the whole debugging/unit testing issue with logic-in-databases.
lesingerouge 11y ago
I'm currently reading Martin Fowler's "Patterns of Enterprise Application Architecture"[0] and I think that one of the main points that stuck with me is his recommendation for separation between the various layers and functions of a software system. Basically the whole system should be a layer cake of smaller modules/systems, as "opaque" as needed to one another, but interacting with each other solely through a well defined "internal API". I think the approach proposed in the article is good when starting or prototyping projects, but the object oriented and layering approach, even if it's a bit more challenging to visualize correctly at the start of the development of an app, is worth its weight in gold as features start piling up. [0] http://www.amazon.com/Enterprise-Application-Architecture-Addison-Wesley-Signature-ebook/dp/B008OHVDFM/ref=tmm_kin_title_0?_encoding=UTF8&sr=8-1&qid=1424636059 http://www.amazon.com/Enterprise-Application-Architecture-Ad... later edit: for clarity.
- sitkack 11y ago
  Putting more intelligence in the data layer doesn't break the modularity of the system, it also doesn't obviate the need for middle layers. The biggest thing it does enforce is a semantically clean gateway to the underlying data store. Databases can do a whole lot declaratively in way that is correct by inspection. Six lines of stored procedure can save hundreds in the middle tier while allowing other tools to access the database directly, w/o forcing everyone through the middle tiers.
deleted 11y ago
[deleted]
alfiedotwtf 11y ago
And now you have two problems.
jos 11y ago
I find myself a bit thrown by encrypting passwords with a stored database function... which involves communicating with the database server with the unencrypted password. The Microsoft SQL Server and Database Management Certifications and associated course work strongly discourage sending a password in that manner -- no matter how secure the connection is. I'm unable to agree with the "simplify" aspect of the article title. My own personal experience tells me data should be validated where it is accepted rather than sent on to the next step -- the contrary may raise unintended additional security concerns or exploits. Granted, my view stems from the article author's directive given towards "web or api developers." There are many situations where software makes a connection directly to a database rather than indirectly.
tunesmith 11y ago
I think it can be a good idea for certain very predictable applications - predictable in terms of scaling and future feature enhancements. But beyond that this strikes me as a bit too complicated. In particular, coupling business logic with database choice is risky. Martin Fowler, Bob Martin, and others underscore that the real cost of development is not the inability to predict the future, but in how difficult or time-consuming it is to change, when reality changes unexpectedly. As soon as something happens that would bring repository choice into question, or would suggest the need to split parts of the business logic up into different locations, the choice to couple business logic with database implementation would start to feel really limiting.
- y0ghur7_xxx 11y ago
  > As soon as something happens that would bring repository choice into question I don't know. Once a project is started with postgres/mysql/orcale, how often do you change the DB? We have some projects that started with struts, then we changed the fe to jsp, the asp.net, then HTML/JS + MVC.net. But the DB was always Postgres. It never changed. In my experience what changes often is the frontend, not the stuff that keeps your data. I would optimize for fe/middleware changes, not db changes.
  - tunesmith 11y ago
    Plenty of examples, although they all tend to be higher-scale considerations that might not apply for your small business or side project: 1) You have "friends" or "friends of friends" modeled in Postgres, but then you realize that you want/need to do more social graph stuff in particular, so it makes more sense to use a graph database like Neo4J 2) You're saving a ton of activity data in postgres but your site is slowing down, and you realize you don't need the archived data, so it makes more sense to use something like Redis for that chunk of data 3) You're saving a ton of log data but the storage of it is going way up, and you're only querying it for big data / data warehouse purposes, so it makes more sense to use something like Cassandra 4) You're wanting to write a new feature set that relies on existing normalized data but would require new complicated queries and joins and business logic to deal with it, plus you're running into scaling considerations, so it makes more sense to convert to a reactive event-based system, such as micro services consuming a kafka topic, where each micro service has its own dedicated repository In each of those cases, if you have used Postgres combined with sprocs, it's much harder to transition than if you had coded to interfaces and relied on your db in a dumb sense.
y0ghur7_xxx 11y ago
It's 2004 all over again: https://web.archive.org/web/20060525094651/http://www.oracle.com/technology/pub/articles/odtug_award.pdf https://web.archive.org/web/20060525094651/http://www.oracle... I love this approach and I used it whenever I could in the last 10 years. It's just so much easier when all you have is a DB and a JS/HTML/CSS frontend.
tbarbugli 11y ago
been there done, killed productivity of a team and then stopped doing it :)
- tbarbugli 11y ago
  to expand, DB functions are usually: * hard to test * code is harder to maintain * vendor specific * often written in very weird programming languages
HarrietJones 11y ago
So, at one end, we're moving stuff out of middleware and into the database, and at the other end we're moving all views into the client. Imagine if SQL returned javascript readable sets of tabulated data... If it did, then you could completely remove all processing on the web-server.
ak39 11y ago
It takes guts to write like this. And I fully agree with Derek. The sheer momentum created by the pontiffs, academics and pundits on this subject who exhorted the virtues of layering architectures moved us into a blind alley. The OO crowd (barbarians) were at the gates first. After two solid decades of this slow exodus of logic from RDBM systems to Java, .NET and whatever other middle-tier architecture we dreamed up, we were inevitably back to the same problems we thought we were avoiding: remove complexity, improve maintainability and portability. And we gave up a lot to get that: we gave up reliability, we gave up performance, we gave up security and we gave up simplicity. Ah, but no worries, we thought, we could write faster iterating loops, introduce threads, write custom access control and authorisation, custom caching solutions ... yeah, we will do that but do everything to eschew the simplest of set-based operations on SQL databases! :-( But there was no lying about how we missed the database in our business tier. Oh we missed it. We missed the database so much outside the RDBMS that we REcreated it! And we christened this compromise of an ingrown toe-nail an "ORM". Two decades have come and gone and we are still agonising this unwinnable war that Ted Neward infamously called the "Vietnam of Computer Science". So, while we jettisoned the relational database onto the scrap heap of software development tools, ironically the makers of the RDBMS software (both open source and commercial) continued to advance and mature it. Patiently. We have CTEs now. We have parameterised tables. It takes all of 5 minutes to show a developer who has never seen the impact of doing a set-based business rule implementation vs one in Java etc that we are too far gone in our craft! Derek is spot on. Respect.
picardo 11y ago
Have we learned nothing in the last 10 years? Aside from the siren song of better performance, there are few other reasons to model your domain in the database. Throwing business logic into stored procedures wily-nilly is a terrible idea for a lot of reasons. First, there is no standard method for testing stored procedures functions; you have to put a lot more effort into devising a test strategy for your database functions than your controller functions. In addition, you've now coupled the business logic with the database, and you cannot move your data around easily. And, lastly, if you have a large database, adding new functions and triggers will involve some downtime, too. To sum up, don't replace your Rails app with a database unless you really know what you're doing.
- ams6110 11y ago
  Most SQL databases that support stored procedures have some kind of unit testing framework available these days, in fact have had for many years. Possibly as a third-party product or project, but the tools exist. If you design your stored procedures right, they can be an abstraction over your data model. You then can change the data model without the stored procedure interface necessarily having to change (though in most cases, changes to the data model are usually done to support new requirements, which typically involve new or changing business logic as well).
  - picardo 11y ago
    That's good to know. The question then becomes whether it makes sense to support yet another testing framework. The pros of performance gains have to outweigh the cons of additional complexity by a considerable margin to make database functions a viable choice over application level functions.
pw 11y ago
Is anyone working on better tooling for debugging, maintaining, versioning and testing code that lives in the database?
ams6110 11y ago
I like most of this thinking, and it's the way I've built systems for years. Data model and an API for it in the database. Apps built to use that API, never access tables directly (or at least, that is the ideal). This lets me build systems using different front-end platforms and languages as needed and appropriate for the task, calling one tested API to interact with the data. One caution I would have is with triggers. Triggers are a way to make things happen as if "by magic" and I avoid them. I prefer to keep things more obvious and easier to reason about. If inserting a row into some table should have a side-effect, I code that explicitly and don't make it happen via a trigger. One exception might be to auto-generate IDs if the database doesn't have a type that does that for you.
A_COMPUTER 11y ago
Having maintained a giant codebase in Oracle PL/SQL, I'm going to have to respectfully disagree and run screaming in the other direction.
deleted 11y ago
[deleted]
emmab 11y ago
I wrote a gem to help test that unique indices match uniqueness validations in rails https://github.com/emma-borhanian/schema_expectations https://github.com/emma-borhanian/schema_expectations If you only do rails-side validation, you have an (exploitable) race condition.
jqm 11y ago
The article makes some valid points but anyone who thinks databases should be used for everything and the kitchen sink should come fill out an application where I work. They could then spend the next 10 years debugging hard to troubleshoot database issues instead of easy to troubleshoot code issues. Jamming too much logic in the database is a recipe for disaster in my experience.

81 points by oskarth 11y ago

The reasoning always offered for treating the database as a dumb store with no intelligence has been about portability. Which is fine if you are selling third party software meant to be installed onto an existing customer's database. But for all other use cases, not using the features of the platform is a mistake. PostgreSQL offers so much! * Stored procedures in Java, Python, Lua, Perl, JavaScript etc [1] * Multicorn [2], query external data sources * Common Table Expressions [3] [1] https://wiki.postgresql.org/wiki/PL_Matrix https://wiki.postgresql.org/wiki/PL_Matrix [2] http://multicorn.org/ http://multicorn.org/ [3] http://en.wikipedia.org/wiki/Hierarchical_and_recursive_queries_in_SQL#Common_table_expression http://en.wikipedia.org/wiki/Hierarchical_and_recursive_quer...

woah 11y ago
Yea, I've never really understood the portability argument. How often do people switch between sql dbs?
- tl 11y ago
  One of the advantages is the ability to run a different database during local development / testing from your production systems (say H2 locally vs. DB2 running in production). Personally, I view this to be an anti-pattern, but some people have to work with systems like this.
  - calpaterson 11y ago
    Yes, this is a very bad idea. Due to the way that query planners work, it's not even a good idea to vary the shape of the dataset between testing and production. If you have a different (bigger, higher stddev, etc) distribution of data in column x, the planner will often complete a query differently
    - ams6110 11y ago
      Agree, unless your need for a DB is quite trivial, then perhaps something like SQLLite for the devs and whatever else for production. However this breaks down quickly once you grow beyond needing very simple INSERT and SELECT type operations.
      - sitkack 11y ago
        At which point one should just run SQLite everywhere, It is often excellent for datasets up to about a gig.
- PaulHoule 11y ago
  It is pretty common that organization X uses a pastiche of different tools for a complicated set of reasons (the most senior of which is that it has grown through acquisition.) Often organization X has a large number of small applications and often big applications contain many small applications. Certainly some libraries and tools that work with different databases can make life easier in this case.
- mathgeek 11y ago
  It usually refers to portability for multiple deployments. I.e. your fist client runs MSSQL, your second client runs Postgre, and then your next client runs MySQL. Very important in the enterprise, education, etc.
- sitkack 11y ago
  Back in the dawn of time, customers bought big machines to run all the databases for an organization. So software sold into those orgs needed to support Oracle or MSSQL. Now with machines being much faster and whole app stacks being virtualized the impetus for having an application support multiple databases is greatly reduced.
- spacemanmatt 11y ago
  At my first major PostgreSQL job, I got there shortly after they had abandoned MySQL. So there's that.
dorfsmay 11y ago
Aren't CTE part of the SQL standard now? I'm pretty sure most modern relational servers using SQL (MS SQL, MySQL, Oracle, DB2) support CTE. PS: even SQLite: https://www.sqlite.org/lang_with.html https://www.sqlite.org/lang_with.html
snaky 11y ago
and transforms! http://www.depesz.com/2015/05/03/waiting-for-9-5-add-transforms-feature/ http://www.depesz.com/2015/05/03/waiting-for-9-5-add-transfo...

webnrrd2k 11y ago

The biggest reason to use stored procedures to me is that db developers tend to stick around around a business lot longer than web developers, so putting most of the business logic into the db is better for the business in the long run. Like the many developers, I generally develop custom apps specifically for a particular business. When I develop new apps I start by by putting all the intelligence in the serer-side code, so it's easier to change things without needing to go through someone else. Once the apps stable then much of the business intelligence gets moved into stored procedures in the db level.

AdrianRossouw 11y ago

I've gone down this road before, and one of my biggest complaints was around managing and deploying the code built this way. I just found plain server side code easier to reason about.

SchizoDuckie 11y ago

Historyically, I've been against putting logic inside (MySQL) database since it's a bitch to version and maintain (especially cross-database-user) Maybe Postgres is a lot better, but for MySQL I'm staying the hell away from it

vinceguidry 11y ago
I think the idea is that you don't have to version and maintain it, your database layer changes far less frequently than your code does. Kind of like how servers used to be managed back in the day. Rebuilding one of those was a major life event, and much of your experience revolved around managing servers through scripts and whatnot, so you didn't sweat the lack of versioning much. Of course nowadays with easy cloud provisioning and tools like Docker, you can version server configurations, so you don't have to put up with it if you don't want to. But databases, I think are at that same in that you should really understand the one you're working with such that you could replicate your current usage from scratch if you really had to, but honestly the chances of you being called upon to do it are minuscule, and the costs of such are going to be borne by the organization you work for anyway.
olefoo 11y ago
It does mean you need to treat your data schema and functions as code. My short checklist for doing database centric development (postgresql centered): * Database schema is kept under version control ( essentially all the DDL for a given database is kept in one place and managed as a unit ). You can use `pg_dump -s` to extract the schema from an extant database. * data types and functions are kept in separate files under version control * the database is built and filled automatically for tests, for migrations and for upgrades. Shell script, Makefile, Chef; the tool doesn't matter so long as the process is repeatable and automatic. * data fixtures for testing are kept separate from the code ( own repo usually, since you want to be able to add edge cases and errors to the test database ). * migrations on the production database are run immediately after a backup. Nothing too fancy, just basic hygiene. But staying on top of that means you're ready to do things like build your application onto a new set of machines; or make drastic changes to how your data is structured. There are a number of tools that do much of the scutwork for you ( South for Django, ActiveRecord::Migration for Rails, etc.) but the tools are not a substitute for understanding what your database is doing.

BurningFrog 11y ago

A serious problem with this approach is scaling. When your code is in Ruby/JS/whatever on separate servers, and your traffic increases, you can easily scale up by adding more stateless servers. Going from 1 to 2 DB servers is much harder, and can easily mean a major rewrite, during which your site may not really work.

falcolas 11y ago
Absolutely this. Not to mention the need to prematurely scale up DBs due to the added load. Expensive and hard.
- radicalbyte 11y ago
  The other side of the coin is that you probably won't need to scale. The performance differences can be staggering: I've replaced Java-based procedures which took more than an hour to execute with Stored Procedures which completed within milliseconds. That's an extreme example - the original code was simply incompetent - but I regularly see significant (100x) performance increase in core functions through the switch. Still, nowadays I prefer to have as much logic as possible in code because it's much easier to test and is more flexible than SPs; and it's always possible to move logic to SPs later..
  - hobs 11y ago
    I see databases in general get a lot of flack from developers for this reason, when on the other side of the fence I see a lot of really smart people misunderstanding the tool (as your example alludes to.) I have often come to a stored procedure and not returned 100x, but more like 50,000x by simply changing a few lines of code. tl;dr anyone can write slow code in any language.
    - radicalbyte 11y ago
      > I have often come to a stored procedure and not returned > 100x, but more like 50,000x by simply changing a few > lines of code. Oh, those beautiful nested cursors :)

michaelmcmillan 11y ago

I always enjoy reading about new ways of doing things, so thanks! How would you test your logic in isolation with unit tests using an architecture like this?

vp89 11y ago
The same way you test any other piece of code. A stored procedure is just an encapsulation of logic, with inputs and possibly state changes and/or outputs. Expected/actual asserts can be coded in SQL. The code tends to be verbose, but some RDBMS have evolved to include more powerful language features. It's probably possible in almost all RDBMS, but certainly easier in some than others.

jconley 11y ago

Stored procedures, functions, triggers, etc, are very useful tools. They are used regularly in Microsoft SQL Server and Oracle systems. But, one has to be very careful to use them when warranted. The out-of-the-box tooling for debugging, maintaining, versioning, and testing code that lives in your database is not nearly as robust as what you have for most other development environments. The key here is to use the tool when it is most useful. SPROC's are good when: 1. You need extreme data security and you are able to pass authentication tokens from application to database. You can apply authorization rules when you read/write the data, rather than transporting more than you need over the network. This eliminates a number of potential attack vectors. I have worked on HIPAA compliant systems for government agencies that held sensitive data that required all data access to be through stored procedures with proper access permissions, in addition to an authorization tier in the application logic. Very common use case. 2. You need to churn through a bunch of data and need very close data locality to pull that off and meet your performance requirements. 2a. Your database nodes are network IO bound and this is due to applications requesting more data than it needs, and that logic can be performed in the database. 3. You have a very strong DBA/Developer that can manage your data access layer that your application uses as well as code the database. This can abstract the "how" of data storage from the application developer, which can be very useful in a data-centric large application. However, SPROCs are bad if: 1. You have to switch DB providers due to scalability, or cost issues, etc. The switching cost can be huge if there is a ton of logic buried in your database. 2. Your database nodes are CPU bound. In this case you'd want to do as little logic as possible in your DB. This is more rare nowadays, but used to happen. 3. SQL (or whatever-sproc-language) is not a core language you want your team to have to become experts in. 4. You don't like adding more code. You'll end up generating and writing yet another layer of code, this time for stuff that lives inside your database. This code has to be maintained, versioned, etc.

PaulHoule 11y ago
You commonly encounter "legacy" code that uses this design and often people who have cut their teeth on currently fashionable techniques put it down. If you want to construct high performance applications, however, moving code close to data solves a lot of problems, particular those involving the reconciliation of durability and scalability. (i.e. transactions work a lot better if you can get all the coordinated parts as close together as possible) This doesn't just go for SQL, but it will go for NoSQL systems that support advanced functionality. Practically it is not too different from the fashion for developing a REST or otherwise "web service" APIs that are consumed by the rest of the system. The only trouble with it is that it is "one more thing" which needs to be managed, which you need to train for, etc. I spent one summer doing a major upgrade to a line of business app and we checked in over 200 database migrations to version control. I don't know if anybody is working without version control in 2015, but 7 years ago that was a common practice and could lead to a lot of pain. Often in the name of encapsulation architects put in a lot of layers, to the effect that if you want to make some small change (say add a second phone number to a user record) you have to make the change in multiple places. I talked with a Rubyist about this and he's like, "it's no problem, just add another phone number" and it's a deep point that systems that are declarative as possible and do code generation are the real way out of the morass.

jalfresi 11y ago

In most of the example he gives, these solutions are ensuring the integrity of the data stored, which I personally have no problem with; that is the job of the database in my view. If I try and store crap the database should refuse, be that via constraints, triggers or stored procedures. As for returning the data in JSON, again, I see no problem with that; you were going to get the data out in rows and columns, what difference does JSON make, especially if it simplifies things, as he is arguing. My main concern is business logic; should business logic be stored in the database. At this point things become a little grey, primarily due to the difficulty in maintaining and managing SQL code. I suppose that PostGres is better that most of the DBs I have had to deal with in the past in this respect in that it supports a wider range of languages for this purpose, but I'd be interested to hear others opinions and experience in this regard.

calpaterson 11y ago
Sadly, to some extent the data integrity is part of the business logic. For example, if products can or cannot be sourced from multiple suppliers
mistermann 11y ago
I would agree, at least on SQL Server, in my experience writing logic is just brutally painful, weak language and poor debugging.

dorfsmay 11y ago

It depends what you do... If you need to manipulate a lot of data that ends up travelling on the wire, you can save a lot of time by running it on the server. On the other hand, the language on the DB servers are often a bit harder to reason with.

yawboakye 11y ago
> the language on the DB servers are often a bit harder to reason with But can't we say that about every other language? I've been writing a lot of PL/pgSQL lately and I've enjoyed every bit of it. For PostgreSQL there's also PL/Python, PL/V8, etc. Those are packages for writing the server code in python and JavaScript respectively and out the window goes the obscure language excuse. But that's if you're on Postgres.

erichmond 11y ago

It's incredible to me that he watched Simple Made Easy (which is an incredible talk), and his mind went to "stick more stuff in the database". Not because that approach wouldn't work, but to me a clear sign of complection would be conflating the medium of data storage (a database) and the business rules that govern the data itself. But I guess that's the the sign of a good talk. It's open to interpretation. It also complicates your scaling story, your caching story, your ability to change databases and a great number of other things. If you're building an app that has no chance of ever possibly needing to scale, or use a different database for caching, searching, or analytic reasons, then this approach may be optimal, but if any of those levers need to move at some point, I think you'd be in for a world of hurt.

straws 11y ago
Kind of makes you wish he watched a couple more Rich Hickey talks, like any of the ones around Datomic. The solution there is not "stick more business logic in the database", it's "split up the part responsible for writes from the part that queries it".
jessaustin 11y ago
I think we're talking relational DBs here, right? In fairness you already have business rules in the DB, implicitly, just from the layout of your tables. You probably also have foreign keys. At that point, a stored proc that translates business needs like "at customer location X, stop service A on this date and replace it with service B" to the multiple table reads and inserts required, seems like a good idea. It's actually not that weird to keep most of your devs out of the database entirely, in which case they probably appreciate being able to just call a stored proc to get something done.
wging 11y ago
He didn't watch 'Simple Made Easy'. He watched a different Hickey talk, 'Simplicity Matters': https://www.youtube.com/watch?v=rI8tNMsozo0 https://www.youtube.com/watch?v=rI8tNMsozo0

jakejake 11y ago

Before the days of GUIs when regular office workers used the command line, the database was an all-in-one application. Stored procedures were created by the DB admin so that the non-techies could run reports with simple commands. I've found it interesting that all of this functionality has remained in databases, but is almost never used in modern web applications.

alkonaut 11y ago

> "In 1997, I started in Perl. In 1998, I switched to PHP. In 2004, a rewrite in Rails. In 2007, back to PHP. In 2009, minimalist Ruby. In 2012, client-side JavaScript. Each time I’d have to re-write all of the logic around the database: how to add a new person into the database, how to verify an invoice is correct, how to mark an order as paid, etc. But that whole time, my trusty PostgreSQL database stayed the same." And what if during that time instead of switching programming languages, it was the storage system that had to be switched? You could easily invert the argument "Don't keep the logic in the DB! I had to switch from Postgres to MSSQL which meant I painstakingly had to convert all my db-logic, I wish it had been in the code instead!"

devdas 11y ago
Switching programming languages is far more common than switching databases.
- alkonaut 11y ago
  *Citation needed You wouldn't even need to switch the database, just add a cache between the storage an the app, or add/switch ORM etc. There is a huge impedance mismatch between table storage and code, but I don't see how it gets more responsive to changed requirements by using logic in the DB. And don't even get me started on the whole debugging/unit testing issue with logic-in-databases.

lesingerouge 11y ago

I'm currently reading Martin Fowler's "Patterns of Enterprise Application Architecture"[0] and I think that one of the main points that stuck with me is his recommendation for separation between the various layers and functions of a software system. Basically the whole system should be a layer cake of smaller modules/systems, as "opaque" as needed to one another, but interacting with each other solely through a well defined "internal API". I think the approach proposed in the article is good when starting or prototyping projects, but the object oriented and layering approach, even if it's a bit more challenging to visualize correctly at the start of the development of an app, is worth its weight in gold as features start piling up. [0] http://www.amazon.com/Enterprise-Application-Architecture-Addison-Wesley-Signature-ebook/dp/B008OHVDFM/ref=tmm_kin_title_0?_encoding=UTF8&sr=8-1&qid=1424636059 http://www.amazon.com/Enterprise-Application-Architecture-Ad... later edit: for clarity.

sitkack 11y ago
Putting more intelligence in the data layer doesn't break the modularity of the system, it also doesn't obviate the need for middle layers. The biggest thing it does enforce is a semantically clean gateway to the underlying data store. Databases can do a whole lot declaratively in way that is correct by inspection. Six lines of stored procedure can save hundreds in the middle tier while allowing other tools to access the database directly, w/o forcing everyone through the middle tiers.

deleted 11y ago

[deleted]

alfiedotwtf 11y ago

And now you have two problems.

jos 11y ago

I find myself a bit thrown by encrypting passwords with a stored database function... which involves communicating with the database server with the unencrypted password. The Microsoft SQL Server and Database Management Certifications and associated course work strongly discourage sending a password in that manner -- no matter how secure the connection is. I'm unable to agree with the "simplify" aspect of the article title. My own personal experience tells me data should be validated where it is accepted rather than sent on to the next step -- the contrary may raise unintended additional security concerns or exploits. Granted, my view stems from the article author's directive given towards "web or api developers." There are many situations where software makes a connection directly to a database rather than indirectly.

tunesmith 11y ago

I think it can be a good idea for certain very predictable applications - predictable in terms of scaling and future feature enhancements. But beyond that this strikes me as a bit too complicated. In particular, coupling business logic with database choice is risky. Martin Fowler, Bob Martin, and others underscore that the real cost of development is not the inability to predict the future, but in how difficult or time-consuming it is to change, when reality changes unexpectedly. As soon as something happens that would bring repository choice into question, or would suggest the need to split parts of the business logic up into different locations, the choice to couple business logic with database implementation would start to feel really limiting.

y0ghur7_xxx 11y ago
> As soon as something happens that would bring repository choice into question I don't know. Once a project is started with postgres/mysql/orcale, how often do you change the DB? We have some projects that started with struts, then we changed the fe to jsp, the asp.net, then HTML/JS + MVC.net. But the DB was always Postgres. It never changed. In my experience what changes often is the frontend, not the stuff that keeps your data. I would optimize for fe/middleware changes, not db changes.
- tunesmith 11y ago
  Plenty of examples, although they all tend to be higher-scale considerations that might not apply for your small business or side project: 1) You have "friends" or "friends of friends" modeled in Postgres, but then you realize that you want/need to do more social graph stuff in particular, so it makes more sense to use a graph database like Neo4J 2) You're saving a ton of activity data in postgres but your site is slowing down, and you realize you don't need the archived data, so it makes more sense to use something like Redis for that chunk of data 3) You're saving a ton of log data but the storage of it is going way up, and you're only querying it for big data / data warehouse purposes, so it makes more sense to use something like Cassandra 4) You're wanting to write a new feature set that relies on existing normalized data but would require new complicated queries and joins and business logic to deal with it, plus you're running into scaling considerations, so it makes more sense to convert to a reactive event-based system, such as micro services consuming a kafka topic, where each micro service has its own dedicated repository In each of those cases, if you have used Postgres combined with sprocs, it's much harder to transition than if you had coded to interfaces and relied on your db in a dumb sense.

y0ghur7_xxx 11y ago

It's 2004 all over again: https://web.archive.org/web/20060525094651/http://www.oracle.com/technology/pub/articles/odtug_award.pdf https://web.archive.org/web/20060525094651/http://www.oracle... I love this approach and I used it whenever I could in the last 10 years. It's just so much easier when all you have is a DB and a JS/HTML/CSS frontend.

tbarbugli 11y ago

been there done, killed productivity of a team and then stopped doing it :)

tbarbugli 11y ago
to expand, DB functions are usually: * hard to test * code is harder to maintain * vendor specific * often written in very weird programming languages

HarrietJones 11y ago

So, at one end, we're moving stuff out of middleware and into the database, and at the other end we're moving all views into the client. Imagine if SQL returned javascript readable sets of tabulated data... If it did, then you could completely remove all processing on the web-server.

ak39 11y ago

It takes guts to write like this. And I fully agree with Derek. The sheer momentum created by the pontiffs, academics and pundits on this subject who exhorted the virtues of layering architectures moved us into a blind alley. The OO crowd (barbarians) were at the gates first. After two solid decades of this slow exodus of logic from RDBM systems to Java, .NET and whatever other middle-tier architecture we dreamed up, we were inevitably back to the same problems we thought we were avoiding: remove complexity, improve maintainability and portability. And we gave up a lot to get that: we gave up reliability, we gave up performance, we gave up security and we gave up simplicity. Ah, but no worries, we thought, we could write faster iterating loops, introduce threads, write custom access control and authorisation, custom caching solutions ... yeah, we will do that but do everything to eschew the simplest of set-based operations on SQL databases! :-( But there was no lying about how we missed the database in our business tier. Oh we missed it. We missed the database so much outside the RDBMS that we REcreated it! And we christened this compromise of an ingrown toe-nail an "ORM". Two decades have come and gone and we are still agonising this unwinnable war that Ted Neward infamously called the "Vietnam of Computer Science". So, while we jettisoned the relational database onto the scrap heap of software development tools, ironically the makers of the RDBMS software (both open source and commercial) continued to advance and mature it. Patiently. We have CTEs now. We have parameterised tables. It takes all of 5 minutes to show a developer who has never seen the impact of doing a set-based business rule implementation vs one in Java etc that we are too far gone in our craft! Derek is spot on. Respect.

picardo 11y ago

Have we learned nothing in the last 10 years? Aside from the siren song of better performance, there are few other reasons to model your domain in the database. Throwing business logic into stored procedures wily-nilly is a terrible idea for a lot of reasons. First, there is no standard method for testing stored procedures functions; you have to put a lot more effort into devising a test strategy for your database functions than your controller functions. In addition, you've now coupled the business logic with the database, and you cannot move your data around easily. And, lastly, if you have a large database, adding new functions and triggers will involve some downtime, too. To sum up, don't replace your Rails app with a database unless you really know what you're doing.

ams6110 11y ago
Most SQL databases that support stored procedures have some kind of unit testing framework available these days, in fact have had for many years. Possibly as a third-party product or project, but the tools exist. If you design your stored procedures right, they can be an abstraction over your data model. You then can change the data model without the stored procedure interface necessarily having to change (though in most cases, changes to the data model are usually done to support new requirements, which typically involve new or changing business logic as well).
- picardo 11y ago
  That's good to know. The question then becomes whether it makes sense to support yet another testing framework. The pros of performance gains have to outweigh the cons of additional complexity by a considerable margin to make database functions a viable choice over application level functions.

pw 11y ago

Is anyone working on better tooling for debugging, maintaining, versioning and testing code that lives in the database?

ams6110 11y ago

I like most of this thinking, and it's the way I've built systems for years. Data model and an API for it in the database. Apps built to use that API, never access tables directly (or at least, that is the ideal). This lets me build systems using different front-end platforms and languages as needed and appropriate for the task, calling one tested API to interact with the data. One caution I would have is with triggers. Triggers are a way to make things happen as if "by magic" and I avoid them. I prefer to keep things more obvious and easier to reason about. If inserting a row into some table should have a side-effect, I code that explicitly and don't make it happen via a trigger. One exception might be to auto-generate IDs if the database doesn't have a type that does that for you.

A_COMPUTER 11y ago

Having maintained a giant codebase in Oracle PL/SQL, I'm going to have to respectfully disagree and run screaming in the other direction.

deleted 11y ago

[deleted]

emmab 11y ago

I wrote a gem to help test that unique indices match uniqueness validations in rails https://github.com/emma-borhanian/schema_expectations https://github.com/emma-borhanian/schema_expectations If you only do rails-side validation, you have an (exploitable) race condition.

jqm 11y ago

The article makes some valid points but anyone who thinks databases should be used for everything and the kitchen sink should come fill out an application where I work. They could then spend the next 10 years debugging hard to troubleshoot database issues instead of easy to troubleshoot code issues. Jamming too much logic in the database is a recipe for disaster in my experience.