Very true. I always chuckle when some NoSQLer tells me how much time he's saved by not having to define and update a schema. Then he tells me how all he has to do is check whether fields exist or not in his code, and enforce constraints, and provide default values, and so forth. He has to do this in the Ruby code that he's writing, and his colleague has to do the same in the PHP code she's writing, and then there's the analysis team who has to write the very same code in their Python scripts...
The cross-implementation schema constraints in SQL DBs are pretty weak. I can only express a small fraction of my application logic there unless I want some serious vendor lock-in.
And then regardless, once I'm referencing the same schema from multiple code bases, schema improvements are basically impossible.
If I need to get at the same data from multiple code bases, I think an API is a lot cleaner. And then I'd rather put the constraints in my API code. The tools are so much better, and programming languages are much more flexible.
So you want to avoid vendor lock-in, but you see NoSQL databases as being better in some way? They're far worse. If you initially target MongoDB, that's what you're going to be stuck with. You aren't going to be moving to CouchDB easily.
It's far easier and much more reliable to use a subset of SQL that's common to the major relational database systems commonly used today. This subset of SQL still ends up being extremely powerful.
I'm not sure exactly what you're getting at with the incorrect claim that "schema improvements are basically impossible" when accessing a single database from multiple code bases. There are many trivial ways to offer or retain compatibility. Using views is one common way. You then have much more freedom to change the underlying schema, while still supporting existing queries, for example. Stored procedures, functions and triggers offer other possibilities.
An API doesn't magically get rid of compatibility issues, either. You'll need to maintain compatibility within your API, or you'll have to run multiple versions of your API simultaneously, or you'll have to deal with it some other way. All that you've done when using an API is added yet another layer that you'll have to maintain. Duplicating the functionality offered by relational databases within your API's code will only end up taking more of your time.
At this point, I think that we've got enough experience as an industry and a craft to know that it's a good practice to use a schema, it's not difficult to change your schema while still maintaining compatibility, and it's better to keep your constraints as close to your data as possible. Relational databases enable this, while NoSQL databases make it much more difficult than it needs to be, if it's even actually possible.
"It's far easier and much more reliable to use a subset of SQL that's common to the major relational database systems commonly used today."
I think that is generally a mistake for non-trivial applications. MySQL and SQLite are so far from the standard that it's just not worth trying to find a subset of SQL that works the same on those systems as the others.
The similarities between the SQL implementations do make porting easier, however. But I consider it a port rather than using a common subset.
Where did I say I was in favor of using a NoSQL database instead?
Schema changes without simultaneous multiplatform code changes mean I'll have to maintain a consistent API somewhere. As you say, I could do it in the SQL database with (non-portable) views and stored procedures. Or I could maintain it in code. I prefer that. Not only are the tools better, but if I'm going to have my team spend a lot of time learning something, I'd rather it be the language than particular database quirks.
"I can only express a small fraction of my application logic there unless I want some serious vendor lock-in."
I can understand if you're worried about using Oracle or something. But if it's a free software database system, the code is open, and I don't really see that as "lock in". For comparison, if you write an application in ruby, you don't say that you are "locked in" to ruby.
"I think an API is a lot cleaner"
That can work very well, but it does have some weaknesses. For instance, you may see a check in the API code for out-of-bounds values, and assume your code doesn't need to account for it. But then you hit some out-of-bounds data at runtime, because the data was inserted before the check was added. That's why databases offer many declarative constraints, so that if you see it, you know it's true, and don't have to worry about when the constraint was created.
That being said, I think a lot of applications would be better developed using something like a web service to cleanly separate the human interface from the action being taken. I can't think of many ways to do that without a lot of boilerplate code though.
It's vendor lock-in either way, although I grant that proprietary lock-in is more problematic than open-source lock-in. Either way, I'm reluctant to place large bets on millions of lines of mystery code with complicated performance characteristics.
Actually I think the motivation is that the schema exists in code rather than in the storage engine. That gives the amazing productivity boost of not having to load the schema into the database engine - something you appreciate when you hit a big 2000 table / 45000 stored proc sized mess. It's another step towards statelessness.
Basically schema in code scales development-wise better than schema in storage engine.
Just because you got yourself into the mess, doesn't mean that database schema is suddenly bad. Data definition language (DDL) is a declarative and well thought-out DSL that can guarantee the business logic to a certain extent, at a very very low cost.
On the other hand, stored proc is a relic of the integration database [1], and is definitely less flexible compared to using your programming language of choice.
Instead of jumping from 2000 tables / 45000 stored procs straight to "schema in code", perhaps a saner tables design with no stored proc is the better way to go?
First (as the app is partioned into logical components), we pick a chunk and move it to schema generation with hibernate. Then we port the stored procedures to code with tests, then we delete all the stored procedures.
We've gone from 0 test cases to 82,000 test cases in 6 years (we have 80 people in our developer pool so this is not some one man effort).
For ref, I inherited this mess (mainly vb6/com) and am responsible for replacing it all.
What if the application doesn't require consistency or enforces it itself? Don't write consistency off straight away as a requirement. Look at how banks work for an example.
As for security, that belongs in your application layer/domain model or facade if you have one, not inside your storage engine. It won't scale in there.
You don't care about your security scaling.
You care about your security being secure.
Maybe after you have that, then you start caring about it scaling. (If you really need storage to scale, buy a TeraData RDBMS. It will scale beyond your wildest dreams.)
If your security is in your storage engine, it is easier to secure because you only have to get it right in that one place.
If your security is in the apps that uses the data stored in your engine, you have to get every single such app bulletproof. The attack surface is much larger and much worse in the second case.
As for security, that belongs in your application layer/domain model
We're talking web apps here and our security had to be at application level because of complexity. Name one DBMS which handles multitenant security with acls and can handle 22,000 requests a second.
If you're having 3 different codes reading the same DB you're doing something wrong
"check whether fields exist or not in his code, and enforce constraints, and provide default values,"
Well, you need to do that regardless of the DB type. What can be a non existing value in Mongo may be a NULL in SQL, or a many-to-many relationship that's currently empty. So you will get the data in a format or another and your code still has to work it out.
And I chuckle when I see SQLer saying 'how easy it is to add a column to Postgresql on runtime' that's like saying a car is faster than a plane because you can't go to the grocery store by plane.
> If you're having 3 different codes reading the same DB you're doing something wrong
Consider your average web app where the database is manipulated by a public-facing app, an API and a background worker, possibly written in different languages by different companies.
> non existing value in Mongo may be a NULL in SQL
An empty value is a lot healthier in any code than an undefined variable/key to reference, isn't it? In NoSQL you have three states: non-existing, NULL or set. There are no guarantees which one a specific value will be in. SQL promises the value will not be missing, and it can ensure it is not left to NULL either.
> you can't go to the grocery store by plane.
Maybe maintaining databases in production is not about `visiting the grocery store by plane' in the first place. When the system has built-in guarantees, of course you'll move slower, and that is rarely a bad thing in production.
"Consider your average web app where the database is manipulated by a public-facing app, an API and a background worker, possibly written in different languages by different companies."
I don't remember being in a situation where that would be the case. Sure, it exists, but it would not be the ideal case.
Usually I'd say you could have several different apps talking to your API (not directly to the DB)
"An empty value is a lot healthier in any code than an undefined variable/key to reference, isn't it?"
data.get('optional_field') in python, for example? Sure, there is some validation enforced by the DB in the relational case, but it is often not enough.
Of course, in most SQL usages you are expected to shift most of the validation to the DB, in NOSQL this is much more restricted, but it's not better or worse, but different.
Unless you are a bank or something like that, then for all means have all aspects in your Oracle DB
> Sure, it exists, but it would not be the ideal case.
It is still not a sign that "you're doing it wrong". I prefer a single entry point to my data, too, but I still want my databases to be consistent in the sense that schema-like details are not coded in the client-side.
> Sure, there is some validation enforced by the DB in the relational case, but it is often not enough.
Agreed. Why worry about the mere existence of important keys?
This was never a debate whether all storage logic belongs to the database or not. The article argues to make your schema visible and keep your data easy to query.
