Nine Pedestrians Mesmerized

    node-firebird
    TypeScript icon, indicating that this package has built-in type declarations

    1.0.0 • Public • Published

    Pure JavaScript and Asynchronous Firebird client for Node.js

    Firebird Logo

    NPM version NPM downloads Mozilla License Build Status

    NPM

    Firebird forum on Google Groups.

    Firebird database on social networks

    Changelog for version v0.2.x

    • added auto-reconnect
    • added sequentially selects
    • events for connection (attach, detach, row, result, transaction, commit, rollback, error, etc.)
    • performance improvements
    • supports inserting/updating buffers and streams
    • reading blobs (sequentially)
    • pooling
    • database.detach() waits for last command
    • better unit-test

    Installation

    npm install node-firebird

    Usage

    var Firebird = require('node-firebird');

    Methods

    • Firebird.escape(value) -> return {String} - prevent for SQL Injections
    • Firebird.attach(options, function(err, db)) attach a database
    • Firebird.create(options, function(err, db)) create a database
    • Firebird.attachOrCreate(options, function(err, db)) attach or create database
    • Firebird.pool(max, options) -> return {Object} create a connection pooling

    Connection types

    Connection options

    var options = {};
    
    options.host = '127.0.0.1';
    options.port = 3050;
    options.database = 'database.fdb';
    options.user = 'SYSDBA';
    options.password = 'masterkey';
    options.lowercase_keys = false; // set to true to lowercase keys
    options.role = null;            // default
    options.pageSize = 4096;        // default when creating database
    options.pageSize = 4096;        // default when creating database
    options.retryConnectionInterval = 1000; // reconnect interval in case of connection drop

    Classic

    Firebird.attach(options, function(err, db) {
    
        if (err)
            throw err;
    
        // db = DATABASE
        db.query('SELECT * FROM TABLE', function(err, result) {
            // IMPORTANT: close the connection
            db.detach();
        });
    
    });

    Pooling

    // 5 = the number is count of opened sockets
    var pool = Firebird.pool(5, options);
    
    // Get a free pool
    pool.get(function(err, db) {
    
        if (err)
            throw err;
    
        // db = DATABASE
        db.query('SELECT * FROM TABLE', function(err, result) {
            // IMPORTANT: release the pool connection
            db.detach();
        });
    });
    
    // Destroy pool
    pool.destroy();

    Database object (db)

    Database Methods

    • db.query(query, [params], function(err, result)) - classic query, returns Array of Object
    • db.execute(query, [params], function(err, result)) - classic query, returns Array of Array
    • db.sequentially(query, [params], function(row, index), function(err)) - sequentially query
    • db.detach(function(err)) detach a database
    • db.transaction(isolation, function(err, transaction)) create transaction

    Transaction methods

    • transaction.query(query, [params], function(err, result)) - classic query, returns Array of Object
    • transaction.execute(query, [params], function(err, result)) - classic query, returns Array of Array
    • transaction.commit(function(err)) commit current transaction
    • transaction.rollback(function(err)) rollback current transaction

    Examples

    Parametrized Queries

    Parameters

    Firebird.attach(options, function(err, db) {
    
        if (err)
            throw err;
    
        // db = DATABASE
        db.query('INSERT INTO USERS (ID, ALIAS, CREATED) VALUES(?, ?, ?) RETURNING ID', [1, 'Pe\'ter', new Date()], function(err, result) {
            console.log(result[0].id);
            db.query('SELECT * FROM USERS WHERE Alias=?', ['Peter'], function(err, result) {
                console.log(result);
                db.detach();
            });
        });
    });

    BLOB (stream)

    Firebird.attach(options, function(err, db) {
    
        if (err)
            throw err;
    
        // db = DATABASE
        // INSERT STREAM as BLOB
        db.query('INSERT INTO USERS (ID, ALIAS, FILE) VALUES(?, ?, ?)', [1, 'Peter', fs.createReadStream('/users/image.jpg')], function(err, result) {
            // IMPORTANT: close the connection
            db.detach();
        });
    });

    BLOB (buffer)

    Firebird.attach(options, function(err, db) {
    
        if (err)
            throw err;
    
        // db = DATABASE
        // INSERT BUFFER as BLOB
        db.query('INSERT INTO USERS (ID, ALIAS, FILE) VALUES(?, ?, ?)', [1, 'Peter', fs.readFileSync('/users/image.jpg')], function(err, result) {
            // IMPORTANT: close the connection
            db.detach();
        });
    });

    Reading Blobs (Asynchronous)

    Firebird.attach(options, function(err, db) {
    
        if (err)
            throw err;
    
        // db = DATABASE
        db.query('SELECT ID, ALIAS, USERPICTURE FROM USER', function(err, rows) {
    
            if (err)
                throw err;
    
            // first row
            rows[0].userpicture(function(err, name, e) {
    
                if (err)
                    throw err;
    
                // +v0.2.4
                // e.pipe(writeStream/Response);
    
                // e === EventEmitter
                e.on('data', function(chunk) {
                    // reading data
                });
    
                e.on('end', function() {
                    // end reading
                    // IMPORTANT: close the connection
                    db.detach();
                });
            });
    
        });
    });

    Streaming a big data

    Firebird.attach(options, function(err, db) {
    
        if (err)
            throw err;
    
        // db = DATABASE
        db.sequentially('SELECT * FROM BIGTABLE', function(row, index) {
    
            // EXAMPLE
            stream.write(JSON.stringify(row));
    
        }, function(err) {
            // END
            // IMPORTANT: close the connection
            db.detach();
        });
    });

    Transactions

    Transaction types:

    • Firebird.ISOLATION_READ_UNCOMMITTED
    • Firebird.ISOLATION_READ_COMMITED
    • Firebird.ISOLATION_REPEATABLE_READ
    • Firebird.ISOLATION_SERIALIZABLE
    • Firebird.ISOLATION_READ_COMMITED_READ_ONLY
    Firebird.attach(options, function(err, db) {
    
        if (err)
            throw err;
    
        // db = DATABASE
        db.transaction(Firebird.ISOLATION_READ_COMMITED, function(err, transaction) {
            transaction.query('INSERT INTO users VALUE(?,?)', [1, 'Janko'], function(err, result) {
    
                if (err) {
                    transaction.rollback();
                    return;
                }
    
                transaction.commit(function(err) {
                    if (err)
                        transaction.rollback();
                    else
                        db.detach();
                });
            });
        });
    });

    Events

    Firebird.attach(options, function(err, db) {
    
        if (err)
            throw err;
    
        db.on('row', function(row, index, isObject) {
            // index === Number
            // isObject === is row object or array?
        });
    
        db.on('result', function(result) {
            // result === Array
        });
    
        db.on('attach', function() {
    
        });
    
        db.on('detach', function(isPoolConnection) {
            // isPoolConnection == Boolean
        });
    
        db.on('reconnect', function() {
    
        });
    
        db.on('error', function(err) {
    
        });
    
        db.on('transaction', function(isolation) {
            // isolation === Number
        });
    
        db.on('commit', function() {
    
        });
    
        db.on('rollback', function() {
    
        });
    
        db.detach();
    });

    Escaping Query values

    var sql1 = 'SELECT * FROM TBL_USER WHERE ID>' + Firebird.escape(1);
    var sql2 = 'SELECT * FROM TBL_USER WHERE NAME=' + Firebird.escape('Pe\'er');
    var sql3 = 'SELECT * FROM TBL_USER WHERE CREATED<=' + Firebird.escape(new Date());
    var sql4 = 'SELECT * FROM TBL_USER WHERE NEWSLETTER=' + Firebird.escape(true);
    
    // or db.escape()
    
    console.log(sql1);
    console.log(sql2);
    console.log(sql3);
    console.log(sql4);

    Using GDS codes

    var { GDSCode } = require('node-firebird/lib/gdscodes');
    /*...*/
    db.query('insert into my_table(id, name) values (?, ?)', [1, 'John Doe'],
        function (err) {
            if(err.gdscode == GDSCode.UNIQUE_KEY_VIOLATION){
                console.log('constraint name:'+ err.gdsparams[0]);
                console.log('table name:'+ err.gdsparams[0]);
                /*...*/
            }
            /*...*/
        });

    Service Manager functions

    • backup
    • restore
    • fixproperties
    • serverinfo
    • database validation
    • commit transaction
    • rollback transaction
    • recover transaction
    • database stats
    • users infos
    • user actions (add modify remove)
    • get firebird file log
    • tracing
    // each row : fctname : [params], typeofreturn
    var fbsvc = {
        "backup" : { [ "options"], "stream" },
        "nbackup" : { [ "options"], "stream" },
        "restore" : { [ "options"], "stream" },
        "nrestore" : { [ "options"], "stream" },
        "setDialect": { [ "database","dialect"], "stream" },
        "setSweepinterval": { [ "database","sweepinterval"], "stream" },
        "setCachebuffer" : { [ "database","nbpagebuffers"], "stream" },
        "BringOnline" : { [ "database"], "stream" },
        "Shutdown" : { [ "database","shutdown","shutdowndelay","shutdownmode"], "stream" },
        "setShadow" : { [ "database","activateshadow"], "stream" },
        "setForcewrite" : { [ "database","forcewrite"], "stream" },
        "setReservespace" : { [ "database","reservespace"], "stream" },
        "setReadonlyMode" : { [ "database"], "stream" },
        "setReadwriteMode" : { [ "database"], "stream" },
        "validate" : { [ "options"], "stream" },
        "commit" : { [ "database", "transactid"], "stream" },
        "rollback" : { [ "database", "transactid"], "stream" },
        "recover" : { [ "database", "transactid"], "stream" },
        "getStats" : { [ "options"], "stream" },
        "getLog" : { [ "options"], "stream" },
        "getUsers" : { [ "username"], "object" },
        "addUser" : { [ "username", "password", "options"], "stream" },
        "editUser" : { [ "username", "options"], "stream" },
        "removeUser" : { [ "username","rolename"], "stream" },
        "getFbserverInfos" : { [ "options", "options"], "object" },
        "startTrace" : { [ "options"], "stream" },
        "suspendTrace" : { [ "options"], "stream" },
        "resumeTrace" : { [ "options"], "stream" },
        "stopTrace" : { [ "options"], "stream" },
        "getTraceList" : { [ "options"], "stream" },
        "hasActionRunning" : { [ "options"], "object"}
    }

    Backup Service example

    const options = {...}; // Classic configuration with manager = true
    Firebird.attach(options, function(err, svc) {
        if (err)
            return;
        svc.backup(
            {
                database:'/DB/MYDB.FDB',
                files: [
                        {
                         filename:'/DB/MYDB.FBK',
                         sizefile:'0'
                        }
                       ]
            },
            function(err, data) {
                data.on('data', line => console.log(line));
                data.on('end', () => svc.detach());
            }
        );
    });

    Restore Service example

    const config = {...}; // Classic configuration with manager = true
    const RESTORE_OPTS = {
        database: 'database.fdb',
        files: ['backup.fbk']
    };
    
    Firebird.attach(config, (err, srv) => {
        srv.restore(RESTORE_OPTS, (err, data) => {
            data.on('data', () => {});
            data.on('end', () =>
                srv.detach();
            });
        });
    });

    getLog and getFbserverInfos Service examples with use of stream and object return

    fb.attach(_connection, function(err, svc) {
        if (err)
            return;
        // all function that return a stream take two optional parameter
        // optread => byline or buffer  byline use isc_info_svc_line and buffer use isc_info_svc_to_eof
        // buffersize => is the buffer for service manager it can't exceed 8ko (i'm not sure)
    
        svc.getLog({optread:'buffer', buffersize:2048}, function (err, data) {
                // data is a readablestream that contain the firebird.log file
                console.log(err);
                data.on('data', function (data) {
                    console.log(data.toString());
                });
                data.on('end', function() {
                    console.log('finish');
                });
            });
    
        // an other exemple to use function that return object
        svc.getFbserverInfos(
                {
                "dbinfo" : true,
                "fbconfig" : true,
                "svcversion" : true,
                "fbversion" : true,
                "fbimplementation" : true,
                "fbcapatibilities" : true,
                "pathsecuritydb" : true,
                "fbenv" : true,
                "fbenvlock" : true,
                "fbenvmsg" : true
            }, {}, function (err, data) {
                console.log(err);
                console.log(data);
            });
    });

    Charset for database connection is always UTF-8

    node-firebird doesn't let you choose the charset connection, it will always use UTF-8. Node is unicode, no matter if your database is using another charset to store string or blob, Firebird will transliterate automatically.

    This is why you should use Firebird 2.5 server at least.

    Firebird 3.0 Support

    Firebird new wire protocol is not supported yet so for Firebird 3.0 you need to add the following in firebird.conf according to Firebird 3 release notes https://firebirdsql.org/file/documentation/release_notes/html/en/3_0/rnfb30-security-new-authentication.html

    AuthServer = Srp, Legacy_Auth
    WireCrypt = Disabled
    UserManager = Legacy_UserManager

    Firebird 4 wire protocol is not supported yet so for Firebird 4.0 you need to add the following in firebird.conf according to Firebird release notes https://firebirdsql.org/file/documentation/release_notes/html/en/4_0/rlsnotes40.html#rnfb40-config-srp256

    AuthServer = Srp256, Srp, Legacy_Auth
    WireCrypt = Disabled
    UserManager = Legacy_UserManager

    Please read also Authorization with Firebird 2.5 client library from Firebird 4 migration guide https://ib-aid.com/download/docs/fb4migrationguide.html#_authorization_with_firebird_2_5_client_library_fbclient_dll

    Contributors

    Install

    npm i node-firebird

    DownloadsWeekly Downloads

    3,352

    Version

    1.0.0

    License

    none

    Unpacked Size

    511 kB

    Total Files

    15

    Last publish

    Collaborators

    • hgourvest
    • petersirka
    • mariuz