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

I have been postponing this announcement for a long time, but now it is his time.

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

MySQL and Node.js: history of relations

Node.JS was developed mainly for writing fast asynchronous servers (see [1]), for serving message queues, returning a small amount of statics and other tasks that operate with small amounts of data. Therefore, the first appeared connectors to NoSQL databases and Memcached. At that stage of development, Node.JS seemed to cover most of the possible applications of the system. In case you needed to work with relational databases, a daemon was written in another language that prepared the data for the Node.js server. As you understand, this slowed down the development of Node.js, because many people are used to supporting relational databases in standard libraries of common server programming languages. This was not denied by the developer of 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:

Yet another MySQL connector for Node.js?

I think they all had their flaws. Undoubtedly, it is convenient to write the connector directly to Javascript, as was done in node-mysql. However, this is a fairly large amount of code that needs to be thoroughly tested and meticulously updated as the Node.js API changes. Also, as it turned out, implementation in C / C ++ is 3-5 times faster than in JavaScript. To use node.dbslayer.js, you must use a DBsLayer layer 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 in 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 functions of the libmysqlclient library API concerning utilitarian functions, execution of queries and data retrieval. The connector also allows you to execute queries 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
Implementing prepared statements

Asynchronous 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 cause an error. There is, however, an undocumented mysql_send_query function that the mysql2 connector developer uses for Ruby, but I decided not to go this way.

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

Perhaps the current implementation will not stand the 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 use of a mutex will not be required.

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