INSERT doesn't trigger right away

Question

What I do:

1. SELECT from database,
2. if row does not exist
3. INSERT row
4. repeat step 1, SELECT the row from step 3

Expected result: after 4 steps there is a result in any case

Result: there is no row in the select, but the row exists (see through phpMyAdmin)

mysqlPool.getConnection(function(err, connection) {
        connection.query('SELECT * FROM x WHERE key = ? LIMIT 1', [ data.key ], function(err, result) {
            if (result.length == 0) {
                // this works, I verified through phpMyAdmin:
                connection.query('INSERT INTO x SET ?', { key: data.key });
            }
        });

        connection.query('SELECT * FROM x WHERE key = ? LIMIT 1', [ data.key ], function(err, result) {
            if (result.length != 0) {
                // Expecting to be here, but result[0] is undefined?!
            } else {
                // I END UP HERE? wtf
            } 
        });
        connection.release();
    });

Does node-mysql need kind of commit; or something?

What am I doing wrong?

Answer 1

I believe this is due to the asynchronous nature of Node, you will need to call the 2nd select in the callback after inserting. Something like the following

mysqlPool.getConnection(function(err, connection) {
    connection.query('SELECT * FROM x WHERE key = ? LIMIT 1', [ data.key ], function(err, result) {
        if (result.length == 0) {
            // this works, I verified through phpMyAdmin:
            connection.query('INSERT INTO x SET ?', { key: data.key }, function(err, result){
                secondSelect();
            });
        }
        else
            secondSelect();
    });
});