Examples Transactions

Database-js supports transactions if the underlying driver supports them. The transaction code on the API has been written to be backwards compatible with existing drivers. The followng have been added to the API:

connection.isTransactionSupported()

Returns a boolean value, true if transactions are supported by the driver, false if they are not.

connection.inTransaction()

Returns a boolean value, true if the connection is currently in a transaction state, false if it is not or if the driver does not support transactions.

connection.beginTransaction()

Returns a Promise. Resolves to true if a transaction was started.

connection.commit()

Returns a Promise. Resolves to true if a active transaction was committed.

connection.rollback()

Returns a Promise. Resolves to true if a transaction was rolled back.

Example

This example will use the Promise syntax. We will assume the user has already determined that connection.isTransactionSupported() returns true.

function addAdmin(username, password) {
    var statement1 = connection.prepareStatement("INSERT INTO roles (username, role) VALUES (?, ?)");
    var statement2 = connection.prepareStatement("INSERT INTO users (username, password) VALUES (?, crypt(?, gen_salt('bf', 8)))");    connection.beginTransaction()
    .then(() => { // we can ignore the resolved value, it's always true here
        statement1.execute(username, 'admin')
        .then(() => {

            statement2.execute(username, password)
            .then(() => {
                // all done and safe to commit
                connection.commit()
                .then(() => {
                    // handle any clean up
                })
                .catch((error) => {
                    // handle an error on commit
                });
            })
            .catch((error) => {
                // we need to rollback to remove the role 
                connection.rollback()
                .then(() => {
                    // report the error?
                })
                .catch((error) => {
                    // this is bad
                });
            })
        })
        .catch((error) => {
            // roll back to be safe
            connection.rollback()
            .then(() => {
                // report an error.
            })
            .catch((error) => {
                // we screwed up
            });
        })
    })
    .catch((error) => {
        // no transaction has been started here.
    });
}

That is a mess of promises. Let's redo that as an async function:

async function addAdmin(user, password) {
    let statement1, statement2;
    statement1 = connection.prepareStatement("INSERT INTO roles (username, role) VALUES (?, ?)");
    statement2 = connection.prepareStatement("INSERT INTO users (username, password) VALUES (?, crypt(?, gen_salt('bf', 8)))");
    try {
        await connection.beginTransaction();
        await statement1.execute(user, 'admin');
        await statement2.execute(user, password);
        await connection.commit();
    
        return true;
    } catch (error) {
        if (connection.inTransaction()) {
            await connection.rollback();
        }
        return false;
    }
}