bminer / node-mysql-queues

Wraps 'node-mysql' to provide mulitple query queues, allowing support for multiple statements and transactions.
MIT License
92 stars 11 forks source link

node-mysql-queues

Add your own node-mysql query queues to support transactions and multiple statements.

For use with Node.js and node-mysql: https://github.com/felixge/node-mysql

DEPRECATION NOTICE

This library is now pointless because node-mysql supports transactions and connection pooling. Thank you and goodbye.

Background

node-mysql does not provide an API for MySQL transactions (yet).

There are a few problems with this:

Fortunately, there are a few solutions:

All that being said, this project is still being actively maintained. It has also been tested with node-mysql 2.0.

Install

npm install mysql-queues

Usage

var mysql = require('mysql');
var client = mysql.createConnection({ //Use `mysql.createClient` in older versions of node-mysql
    user: 'root',
    password: 'root'
});
//Enable mysql-queues
var queues = require('mysql-queues');
const DEBUG = true;
queues(client, DEBUG);
//Start running queries as normal...
client.query(...);

//Now you want a separate queue?
var q = client.createQueue();
q.query(...); 
q.query(...);
q.execute();

client.query(...); //Will not execute until all queued queries (and their callbacks) completed.

//Now you want a transaction?
var trans = client.startTransaction();
trans.query("INSERT...", [x, y, z], function(err, info) {
    if(err)
        trans.rollback();
    else
        trans.query("UPDATE...", [a, b, c, info.insertId], function(err) {
            if(err)
                trans.rollback();
            else
                trans.commit();
        });
});
trans.execute();
//No other queries will get executed until the transaction completes
client.query("SELECT ...") //This won't execute until the transaction is COMPLETELY done (including callbacks)

//Or... as of version 0.3.0, you can do this...
var trans = client.startTransaction();
function error(err) {
    if(err && trans.rollback) {trans.rollback(); throw err;}
}
trans.query("DELETE...", [x], error);
for(var i = 0; i < n; i++)
    trans.query("INSERT...", [ y[i] ], error);
trans.commit(); //Implictly calls resume(), which calls execute()
/* In the case written above, COMMIT is placed at the end of the Queue, yet the
entire transaction can be rolled back if an error occurs. Nesting these queries
was not required. */

Even multiple Queues work! They get executed in the order that execute() is called.

How it works

API

client.query(sql, [params, cb])

Use normally. Same as node-mysql, except that if a Queue is still pending completion, this query may be queued for later execution.

client.createQueue()

Creates a new query Queue.

client.startTransaction()

Creates a new query Queue with "START TRANSACTION" as the first queued query. The Queue object will also have commit() and rollback() methods.

Queue.query(sql, [params, cb])

Same as node-mysql. This query will be queued for execution until execute() is called on the Queue.

Queue.execute()

Executes all queries that were queued using Queue.query. Until all query callbacks complete, it is guaranteed that all queries in this Queue will be executed in order, with no other queries intermixed. That is, during execution of this query Queue, all queries executed using client.query will be queued until this Queue is empty and all callbacks of this Queue have finished executing. That means that a query added to a Queue can also queue a query using Queue.query, and it will be executed before any client.query call. Thus, nested query queueing is supported in query callbacks, allowing support for transactions and more. See the source code for further documentation.

Calling execute() on an already executing Queue has no effect. Calling execute() on a paused Queue has no effect. (see pause() below)

Note: Once execute() is called and all queries have completed, the Queue will be empty again, returning control to either: (a) another Queue that has been queued for execution; or (b) the main node-mysql queue (a.k.a. queries executed with client.query). Once a Queue is empty and has finished executing, you may continue to use Queue.query and Queue.execute to queue and execute more queries; however, as noted below, you should never reuse a Queue created by client.startTransaction

Queue.commit([cb])

Available only if this Queue was created with client.startTransaction. Calls cb(err, info) when the COMMIT has completed.

As of version 0.3.0, the behavior of commit() is:

Calling commit() also implicitly calls resume() on the Queue.

You may only call commit() once. Once you call commit() on this Queue, you should discard it. To avoid calling commit() twice, you can check to see if it exists; once you call commit(), in most circumstances, the function is deleted from the Queue object after it is called.

As of version 0.3.0, it is sometimes possible to call rollback() even after commit() has been called. If 'COMMIT' is queued for execution (i.e. if the queue is not empty when commit() is called), then you may call rollback() on this Queue, as long as rollback() occurs before the 'COMMIT' is executed (i.e. when the Queue is empty and all query callbacks have completed). You might use the functionality in a scenario where you only want your query callbacks to call rollback() if an error occurred (i.e. a foreign key constraint was violated). If no error occurs, you want to call commit(). Rather than nesting all of these queries to determine whether or not to call commit() or rollback(), you can simply queue up all of your queries, call commit() to queue up a 'COMMIT', and call rollback() in your query callbacks if an error occurs.

Important Note!

If you do not call commit() or rollback() and the Queue has completed execution, commit() will be called automatically to end the transaction; however, one should NOT rely on this behavior. In fact, mysql-queues will print nasty warning messages if you do not explicitly commit() or rollback() a transaction.

Queue.rollback([cb])

Available only if this Queue was created with client.startTransaction. This executes 'ROLLBACK' immediately, purges the remaining queries in the queue, and immediately returns control to the main queue. Finally, the callback cb(err, info) is called when the ROLLBACK has completed.

You may only call rollback() once. To avoid calling it twice, you can check to see if it exists; once you call rollback(), the function is deleted from the Queue object. Also, once you call rollback(), you cannot call commit().

Note: Before 0.2.3, rollback() would add the 'ROLLBACK' query to the Queue and the Queue would continue executing. This was changed in 0.2.3 because it is more natural for a ROLLBACK operation to abort the remaining Queue, since it will be rolled back anyway. As mentioned above, this also allows you to queue the COMMIT query at the bottom of the queue, and if an error occurs before the COMMIT, you can safely rollback() the entire transaction.

Queue.pause([maxWaitDuration])

Pauses the Queue, preventing it from returning control to the next Queue or to the main node-mysql Queue. You can call resume() to resume the Queue, or if the Queue is a transaction, commit() or rollback() will automatically resume the Queue.

By default, the Queue will remain paused until you call resume() or end the transaction; however, you may set an optional maximum wait duration, which will prevent the Queue from pausing for too long.

CAUTION: A paused Queue will block all queries for this connection. Use with care.

Pausing a Queue is useful to make additional asynchronous calls within a query callback. An example of this is shown below.

Queue.resume()

Resumes Queue execution. This function basically unpauses the Queue and calls execute().

require('mysql-queues')(client, debug)

Attaches mysql-queues to the mysql client. When debug mode is enabled, debugging messages are printed to standard error when certain exceptions occur. When you queue a query, the call stack becomes somewhat useless, and it can become difficult to determine which query is causing a problem. The debug feature allows you to more easily determine which query that caused a problem.

Don't do this...

//You may be tempted to do this...
var fs = require('fs');
var trans = db.startTransaction();
trans.query("INSERT ...", [...], function(err, info) {
    fs.readFile("foobar.txt", function(err, data) {
        //By now, it's too late to use `trans`
        if(data == "something")
            trans.commit();
        else
            trans.rollback();
    });
    //The query callback is now done!! This is your last chance
    //to call `commit` or `rollback`
}).execute();

In the case above, an asynchronous call was placed in the query callback. This won't work as expected. The query callback completes and automatically executes commit() before the asychronous filesystem call completes. In this example, you will get a warning message, your transaction will be committed no matter what, and your program may throw an exception after the I/O operation completes (because neither commit() nor rollback() can be called more than once).

To be clear, the scope of this problem is not limited by asynchronous file I/O operations; any asychronous call can cause this problem - even a query to another database will cause this problem (i.e. if you execute a series of MySQL queries and then update Redis, for example)

Fortunately, there are a few solutions...

Possible solutions include: (in order of personal preference)

And finally, to be clear, you are allowed to do asynchronous calls within the query callback of a transaction. You just need to commit() or rollback() or pause() beforehand because the Queue will be empty by the time the asynchronous operation completes.

Questions / Comments / Bugs

Please feel free to contact me via GitHub, send pull requests, open issues, etc.

I am open to suggestions and criticisms.