Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

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?

share|improve this question
10  
What does your first example do that an ORM doesn't already provide? –  Robert Harvey Mar 21 '13 at 15:00
2  
Your way would work just fine if the only thing anyone ever did was simple queries. –  Blrfl Mar 21 '13 at 15:05
3  
@RobertHarvey Nothing. But it needs to be converted to SQL. The point of my question is why can't we have driver-level access to data manipulation operations. –  lortabac Mar 21 '13 at 15:10
1  
I would like to see a complex query with that sintaxys. –  user61852 Mar 21 '13 at 15:15
10  
To me this is like asking why don't toasters accept Ice cream. –  HLGEM Mar 21 '13 at 21:30

4 Answers 4

up vote 26 down vote accepted

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.

share|improve this answer
10  
I disagree about building SQL commands by hand. ORM are fine for very simplistic data models. Anything beyond trivial you are writing your own SQL layer. –  Loki Astari Mar 21 '13 at 18:36
1  
I'll play devils advocate and note than any reasonable ORM should be configurable to meet the needs of an application. –  bunglestink Mar 21 '13 at 20:56
4  
@LokiAstari: True, but the trivial CRUD stuff can make up 80% or more of your application. –  Robert Harvey Mar 21 '13 at 20:57
    
@Tim, excellent point. In fact, the hypothetical syntax proposed in the question looks an awful lot like JSON. –  John M Gant Mar 21 '13 at 21:08

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.

share|improve this answer

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

share|improve this answer

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.