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.
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.