Simplify: move code into database functions

"Scaling is a nice problem to have"

Don't write code to handle 1 million users if you've only got 100. Usually just getting something up and running is enough of a problem for most.

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.

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.

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.

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.