Relational Database Interface

Though there are many kinds of persistence engines available, relational databases are the most commonly deployed. Relational databases have a consistent data model and a reasonably consistent set of operations. This has allowed for the creation of a standard interface in many different languages.

If it's possible to interface with other types of databases using the same kind of interface, that is a win. However, the primary goal with this API is to provide access to relational databases.

Prior Art

• JSDB is JavaScript for databases http://www.jsdb.org/
• SQueaL is an XML-based SQL Schema designed for defining databases http://alt.cellosoft.com/squeal.html
• Ejscript has a low level database access API and a high level "Rails like" ORM http://www.ejscript.org/products/ejs/doc/api/ejscript/ejs.db-classes.html The Record class is based on the Rails ActiveRecord class.
• Jester, REST in Javascript, http://www.thoughtbot.com/projects/jester
• JSONSQL http://www.trentrichardson.com/jsonsql/
• jslibs jssqlite module http://code.google.com/p/jslibs/wiki/jssqlite
• TaffyDB, a JS database for your browser http://taffydb.com/index.cfm
• JSMemCached JS memcached client http://code.google.com/p/jsmemcached-client/
• v8cgi has a MySQL class
• JDBC
• Python DBAPI
• HTML5 defines a local database API http://dev.w3.org/html5/webstorage/#databases (supported by Safari)
• Google Gears database API http://code.google.com/intl/fr/apis/gears/api_database.html

Other notes

I second that proposal.

I particuarly like the model CodeIgniter uses for the dbms interface: A common interface or abstract class that is then subclassed for each database implementation. This would allow some RDBMS implementations to be done by this project, but should allow for me (a third party) to use my own driver.

What are we gonna call our interface for connecting to a database? Options: RDBMS, DB, Connection, Database? 'DB' is short and rather to the point.

   DB : Class {
   
   DB : function (driver, host, username, password, database) // or the following constructor
   
   DB : function (driver, connectionString) // mcoquet: I rather like better passing in an object with the arguments instead of a string like so:
   
   DB : function (driver, connectionConfigObj) // ie: method ("mysql", {host:"bla.com",port:"3306",user:"username",pass:"mypass"})
   
   query : function (sql) // returns a JS object
   
   execute : function (sql)
   
   version : function ()
   
   close : function()
   
   }

I haven't defined any sort of ResultSet object because I'm left to understand that a ResultSet class merely (or often) provides a pretty interface for looping through the results and getting some other minor meta data. Most of this functionality can be achieved by providing a simple structured Javascript object. For example:

   myResultSet: {
   
   meta: ['id', 'name', 'age'],
   
   data: [
   
   { id: 1, name: 'Yunero', age: 22 },
   
   { id: 2, name: 'Abbadon', age: 45 },
   
   { id: 3, name: 'Luna', age: 31 }
   
   ]
   
   }

Is there much else a recordset needs?

The DB interface is slim to say the least; Often drivers and classes have all sorts of helper methods; In the case of an RDBMS we could add all sorts of methods for update, select, delete, creating/manipulating database schema,.. but often is the case that the more that gets added - the more politics and ideals are gonna slow it down. Should there be more to this design? Perhaps the DB interface above should be more function overloaded?

The prepared statement style of querying (passing a string with "?" placeholders, and additional arguments filling in the placeholders) is a good way of preventing SQL injection. Should it support that syntax?

ORMs

Object relational mappers would use the interface defined here, but are still an area of active exploration and not one to standardize at this time.

• ActiveRecord.js, ORM in Javascript http://www.activerecordjs.org/
• Ejscript Record ORM http://www.ejscript.org/products/ejs/doc/api/ejscript/ejs.db-classes.html