db-mysql: Node.js MySQL driver

May 17, 2011 / Posted by mariano /

Install

Before proceeding with installation, make sure you have the MySQL client libraries installed in your system. Specifically, you should have access to the mysql_config binary. If your MySQL binaries are not part of the path and for example are located in /usr/local/mysql/bin, make sure you specify the MYSQL_CONFIG environment variable:

$ export MYSQL_CONFIG=/usr/local/mysql/bin/mysql_config

Once you have mysql_config in your path or the MYSQL_CONFIG environment variable set, you can go ahead with the installation:

$ npm install db-mysql

Connecting

var mysql = require('db-mysql');
new mysql.Database({
    hostname: 'localhost',
    user: 'user',
    password: 'password',
    database: 'test'
}).on('error', function(error) {
    console.log('ERROR: ' + error);
}).on('ready', function(server) {
    console.log('Connected to ' + server.hostname + ' (' + server.version + ')');
}).connect();

Querying

var mysql = require('db-mysql');
new mysql.Database({
    hostname: 'localhost',
    user: 'user',
    password: 'password',
    database: 'test'
}).connect(function(error) {
    if (error) {
        return console.log('CONNECTION error: ' + error);
    }
    this.query().
        select('*').
        from('users').
        where('approved = ?', [ true ]).
        order({'created': false}).
        execute(function(error, rows, cols) {
                if (error) {
                        console.log('ERROR: ' + error);
                        return;
                }
                console.log(rows.length + ' ROWS found');
        });
});

Using manual queries

In the example below, usage of Database.name() and Database.escape() is not mandatory, but recommended.

var mysql = require('db-mysql');
new mysql.Database({
    hostname: 'localhost',
    user: 'user',
    password: 'password',
    database: 'test'
}).connect(function(error) {
    if (error) {
        return console.log('CONNECTION error: ' + error);
    }
    this.query('SELECT * FROM ' + this.name('users') + ' WHERE ' + this.name('name') + ' LIKE \'' + this.escape('%John%') + '\'').
        execute(function(error, rows, cols) {
                if (error) {
                        console.log('ERROR: ' + error);
                        return;
                }
                console.log(rows.length + ' ROWS found');
        });
});

Inserting

var mysql = require('db-mysql');
new mysql.Database({
    hostname: 'localhost',
    user: 'user',
    password: 'password',
    database: 'test'
}).connect(function(error) {
    if (error) {
        return console.log('CONNECTION error: ' + error);
    }
    this.query().
        insert('users',
            ['name', 'created', 'role', 'approved'],
            ['Test User', {value: 'NOW', escape: false}, 'user', true]
        ).
        execute(function(error, result) {
                if (error) {
                        console.log('ERROR: ' + error);
                        return;
                }
                console.log('GENERATED id: ' + result.id);
        });
});

Updating

var mysql = require('db-mysql');
new mysql.Database({
    hostname: 'localhost',
    user: 'user',
    password: 'password',
    database: 'test'
}).connect(function(error) {
    if (error) {
        return console.log('CONNECTION error: ' + error);
    }
    this.query().
        update('users').
        set({ 'name': 'New Name' }).
        where('id = ?', [ 1 ])
        execute(function(error, result) {
                if (error) {
                        console.log('ERROR: ' + error);
                        return;
                }
                console.log('RESULT: ', result);
        });
});

Deleting

var mysql = require('db-mysql');
new mysql.Database({
    hostname: 'localhost',
    user: 'user',
    password: 'password',
    database: 'test'
}).connect(function(error) {
    if (error) {
        return console.log('CONNECTION error: ' + error);
    }
    this.query().
        delete().
        from('users')
        where('id = ?', [ 1 ])
        execute(function(error, result) {
                if (error) {
                        console.log('ERROR: ' + error);
                        return;
                }
                console.log('RESULT: ', result);
        });
});

Documentation

Fore more in depth documentation check node-db’s documentation.

Bug reporting and source code

You can report bugs on the project issues page.
The source code for this driver is in github.