Sannis August 19, 2010 at 17:36

MySQL support in Node.js: node-mysql-libmysqlclient

I have been postponing this announcement for quite some time, but now its time has come.

Meet: node-mysql-libmysqlclient v0.0.7 , a MySQL connector for Node.js, supporting synchronous and asynchronous execution of database queries and having an API close to the API of similar connectors for PHP / Perl / Ruby etc.

MySQL and Node.js: Relationship History

Node.JS was developed mainly for writing fast asynchronous servers (see [1]), for servicing message queues, returning a small amount of static and other tasks that operate with small amounts of data. Therefore, the first connectors to NoSQL databases and Memcached appeared. At that stage in the development of Node.JS, it seemed like it covered most of the possible uses of the system. In case you needed to work with relational databases, a daemon was written in another language, which prepared the data for the Node.js server. As you know, this slowed down the development of Node.js, because many are used to supporting relational databases in standard libraries of common server programming languages. This was not denied by the developer Node.js, who planned to include such connectors in one of the later stable versions. However, so far this has not happened, and third-party connectors to relational databases have begun to appear. At the time of writing node-mysql-libmysqlclient, there were several of them:

Yet another MySQL connector for Node.js?

In my opinion, all of them had their drawbacks. Undoubtedly, it is convenient to write the connector directly in Javascript, as was done in node-mysql. However, this is a fairly large amount of code that must be thoroughly tested and carefully updated when the Node.js API changes. Also, as it turned out, the implementation in C / C ++ is 3-5 times faster than in JavaScript. To use node.dbslayer.js, you must use a layer in the form of DBsLayer to ensure asynchronous execution of queries. Node_postgres in its first implementations was very little functional. And none of these connectors came close to similar ones in other languages by the presence of utilitarian functions. This was the main reason why I started writing node-mysql-libmysqlclient.

Node-mysql-libmysqlclient: features

Bindings are currently implemented for all libmysqlclient API functions related to utility functions, query execution, and data retrieval. The connector also allows you to execute requests asynchronously, with or without callback. Example:

/* http://gist.github.com/537870 */
var mysql_libmysqlclient = require("mysql-libmysqlclient");
var conn = mysql_libmysqlclient.createConnection(host, user, password, database);
if (!conn.connected()) {
  sys.puts("Connection error: " + conn.connectErrno() + ", " + conn.connectError());
  process.exit(1);
}
var string = conn.escape("Sannis's code");
/* Sync queries */
var res = conn.query("CREATE TEMPORARY TABLE t1 (alpha INTEGER, beta VARCHAR(255), pi FLOAT);");
sys.puts("'CREATE TABLE' result: " + sys.inspect(res));
res = conn.query("INSERT INTO t1 VALUES (1, 'hello', 3.141);");
sys.puts("LastInsertId: " + sys.inspect(conn.lastInsertId()));
/* Async queries */
conn.queryAsync("INSERT INTO t1 VALUES (2, 'world', 2.718);", function (res) {
  conn.queryAsync("SELECT * FROM t1;", function (res) {
    sys.puts("NumRows: " + res.numRows());
    var rows = res.fetchAll();
    sys.puts("Rows: " + sys.inspect(rows));
    conn.queryAsync("DELETE * FROM t1;");
  });
});

Immediate plans

More tests, especially for asynchronous requests
Update API documentation and write usage examples for all functions
Writing benchmarks for all currently existing MySQL connectors
Implementation of prepared statements

Asynchronization implementation

The libmysqlclient library does not natively support asynchronous query execution. The second call to mysql_query () before getting all the results after mysql_use_result () or calling mysql_store_result () will throw an error. There is, however, an undocumented mysql_send_query function that the mysql2 connector developer for Ruby uses, but I decided not to go that route.

To support asynchronous I / O operations, Node.js includes the libeio library . It not only supports performing disk operations with callbacks for the result, but also with the help of eio_custom allows you to execute any function in a separate thread and return the result of its execution to the callback. In libeio, it uses threads-pool, so to avoid running parallel mysql_query, it is additionally wrapped in a mutex.

Perhaps the current implementation will not withstand competition in case of a high load on the event-loop or libeio threads-pool and then I think they will return to the system with the request queue, when the queue will be responsible for the sequential launch of mysql_query and the mutex will not be required.

The code snippet responsible for asynchronous requests is: github.com/Sannis/node-mysql-libmysqlclient/blob/v0.0.7/src/mysql_bindings_connection.cc#L825-946