db-drizzle: Node.js Drizzle driver

May 22, 2011 / Posted by mariano /

Install

Before proceeding with installation, you need to have the Drizzle libraries and include files. In order for the installation script to locate them properly, you’ll need to set the DRIZZLE_INCLUDE_DIR and DRIZZLE_LIB_DIR environment variables. For example:

$ export DRIZZLE_INCLUDE_DIR=/usr/include/libdrizzle
$ export DRIZZLE_LIB_DIR=/usr/lib

Once the environment variables are set, install with npm:

$ npm install db-drizzle

Connecting

var drizzle = require('db-drizzle');
new drizzle.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 drizzle = require('db-drizzle');
new drizzle.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 drizzle = require('db-drizzle');
new drizzle.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 drizzle = require('db-drizzle');
new drizzle.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 drizzle = require('db-drizzle');
new drizzle.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 drizzle = require('db-drizzle');
new drizzle.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.