Simplify: move code into database functions
sivers.org
sivers.org
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.
This is the key point for me. I've seen SQL databases with 60k+ LOC in stored procedures, and functions (thankfully they avoided triggers). This code wasn't versioned, tested, or commented. While it's technically true that you can test and version SQL code that lives inside a database, I've never seen it done.
This is mostly a tools issue, although its also partly a cultural issue, where 'stuff that goes in the database' isn't seen to be subject to the same laws of entropy and software development that all other code is.
See my comment[1]. We're doing it and it works beautifully.
> This code has to be maintained, versioned, etc.
Specifically to a Microsoft stack this specific pain point is outright negated, in fact, Microsoft's answer (Visual Studio SQLPROJ/Data Dude) exposes migrations for the horrible metahistory hack that they are. SQL projects are stored in source control as though they were C++/C#/what-have-you. Just like the rest of our code, the 347kloc SQL portion is handled on by CI and the migration (as well as the creation) scripts are generated for us - based on the schema differences (that it determines for us) between our last release and the current release.
So far as SQL Server goes, maintenance and versioning woes are an outright myth.
More pros:
4. A policy of using SPROCs only is a good way to significantly reduce the risk of SQL-related vulnerabilities. If developers are required to justify why they are sending raw queries to SQL they will have a hard time introducing e.g. an injection vulnerability.
5. A lot of the overhead of query execution (parsing, resolving objects, etc.) is done when you CREATE or ALTER the procedure, this can results in a performance benefit (especially if the application is chatty).
More cons:
5. Query plans for SPROCs are aggressively cached, in extremely rare circumstances this can play havoc with performance (direct contradiction of my own pro 5).
6. Triggers+family are invisible logic. They can cause confusion during debugging.
I'm not sure I can agree. This is the appropriate venue to validate/copy/mutate data. An heavily constrained table should be documented as a matter of API instructions, because (as you mentioned) they can cause confusion, but let's not throw the baby out with the bathwater, eh?
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.
My current project went nuts with the Redis + Lua scripts. Trace statements and manual inspection. Feels like I'm developing on the Apple ][. It started innocently enough. Now it's a hydra. Nutty.
I found a HOWTO for debugging embedded Lua. Basically run Redis from within an IDE. Definitely not turnkey. Might be worth the setup effort, next time I visit that code.
This is generally how erp applications are implemented.
PostgreSQL offers so much!
* Stored procedures in Java, Python, Lua, Perl, JavaScript etc [1]
* Multicorn [2], query external data sources
* Common Table Expressions [3]
[3] http://en.wikipedia.org/wiki/Hierarchical_and_recursive_quer...
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.
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
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.
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.
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.
Stack overflow runs off one SqlServer. Do you think your app is going beyond that? If yes, maybe this approach will not work for you. If no, you are in the 99,99999% of all other sites/apps that are not as big as SO, FB or google and you can use this simple way to make your site or app.
It would be easy to exceed its DB load with an application that serves unique content to each user, is write-heavy, etc.
Just curious: did YOU ever reach that point? I mean where a well optimized db engine reached it's limit? Can I ask you on what website?
Normally people talk about that as a way to increase the throughput of requests that your site can respond to, but it has another benefit: more time spent performing data validation, string munging, date checking, etc adds up to less time spent in the database on the same thing.
Stack Overflow may run off of one relational database, but it solves the scaling problem through liberal application-level caching and reverse-proxy caching, all using about a dozen servers that connect to the one database.
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.
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..
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.
Oh, those beautiful nested cursors :)
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.
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.
Maybe Postgres is a lot better, but for MySQL I'm staying the hell away from it
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.
I wholehartedly agree that the schema should be versioned. But the schema dump is imho unreadable. I edit it manually instead, which has its own drawbacks.
Since I referr to the schema a lot, I think it is worth optimizing for readability.
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.
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.
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.
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).
I just found plain server side code easier to reason about.
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-Ad...
later edit: for clarity.
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.
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!"
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.
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.
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.
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.
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.
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.
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.
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.
I've found it interesting that all of this functionality has remained in databases, but is almost never used in modern web applications.
If you only do rails-side validation, you have an (exploitable) race condition.
Imagine if SQL returned javascript readable sets of tabulated data... If it did, then you could completely remove all processing on the web-server.
* hard to test
* code is harder to maintain
* vendor specific
* often written in very weird programming languages