As far as I know, most relational databases do not offer any driver-level API for queries, except a query function which takes an SQL string as an argument.
I’m thinking how easier it would be if one could do:
var result = mysql.select('article', {id: 3})
For joined tables, it would be slightly more complex, but still possible. For example:
var tables = mysql.join({tables: ['article', 'category'], on: 'categoryID'});
mysql.select(tables, {'article.id': 3}, ['article.title', 'article.body', 'category.categoryID'])
Cleaner code, no string parsing overhead, no injection problems, easier reuse of query elements… I can see a lot of advantages.
Is there a specific reason why it was chosen to only provide access to queries through SQL?
15
Databases are out of process – they run on a different server usually. So even if you had an API, it would need to send something across the wire that represents your query and all of its projections, filters, groups, subqueries, expressions, joins, aggregate functions etc. That something could be XML or JSON or some proprietary format, but it may as well be SQL because that is tried, tested and supported.
It is less common these days to build up SQL commands yourself – many people use some sort of ORM. Even though these ultimately translate into SQL statements, they may provide the API you are after.
8
Because SQL provides a common API. You can write an ANSI 92 SQL compliant driver that emits SQL and exposes the API you desire. As a special bonus, it will work with almost any SQL database without rewriting.
If it was done your way, every SQL database would have a different API. Unless, of course, we all standardized on your API. But then, we’d have SQL again, more or less, wouldn’t we? Except that your API appears to be programming language-specific, whereas SQL is not.
4
There’s more to do on the database for administrative purposes, so being able to script and submit text to add users, run backups, load data, change the schema etc. is important. Most DBA’s won’t want to do this inside some other programming language.
If DBA’s want to hang on to SQL, you have to have another language, the database would have the burden of processing both.
There are many new features in databases, so I don’t think they’re getting stagnant. They’re just not doing what you propose for some reason.
SQL Server has the ability to execute .NET code from the inside through SQL CLR. This is helpful for some of those tasks that don’t fit into a relational model, but want to maintain performance. I realize this isn’t what you’re looking for. It’s an example of the many things databases are doing.
It’s not going to go away any time soon. One of the more recent databases to hit the market is NuoDB. They kept SQL, provide ACID while adding the ability to distribute servers and run it in a cloud. You may want to look into why they went to all that trouble to promote the continuation of SQL (Not their only reason, but it’s a huge selling point.).
2
SQL DBMS provide substantially optimised access to the store through the native language and many, as you note provide no other API.
The observation that the database is out of process does not apply in a number of cases and is not really directly relevant.
Even databases that require the use of the SQL DML often provide a cursor library to provide iterator access to a result set, and the well known Microsoft Access and Btrieve SQL DBMS both provide a direct record interface to the individual tables in a database as a mechanism for very high performance access under specific circumstances.
As noted, complex queries using such a syntax would reproduce the behaviour of network databases from the late ’70s.
The alternate access mechanisms are less attractive to the mainstream users due to the unfamiliarity, but the growth in popularity of the NoSQL databases could increase interest in other APIs to achieve specific performance gains. There seems little else to recommend such an approach.