node-firebird2
v1.3.10
Published
Pure JavaScript and Asynchronous Firebird client for Node.js.
Downloads
8
Readme
Pure JavaScript Firebird client for Node.js.
Pure JavaScript and Asynchronous Firebird client for Node.js. Firebird forum on Google Groups.
*********************************************
This lib exposes a promise API around the implementation of sdnetwork (https://www.npmjs.com/package/node-firebird-dev)
import {promises: fb} from 'node-firebird2'
await fb.attach(config)
await fb.query(query)
const tx = await fb.transaction(ISOLATION_XXXX)
const rows = await tx.query('SELECT 1 1 FROM RDB$DATABASE');
console.log(rows) // []
await tx.restart('auto') // restart the transaction using the same connection and isolation strategy
await tx.restart(SIOLATION_XXX) // restarts the transaction using a different strategy
const query = 'INSERT INTO CONFIGURATION (CONF_ID, CONF_CLE, CONF_VALEUR, CONF_INTERNE, CONF_CENTRE) ' +
' VALUES (NEXT VALUE FOR IDGENERIQUE, \'TEST_\' || CURRENT_TIMESTAMP, \'-1\', 0, null) RETURNING CONF_ID';
const res = await tx.query(query);
console.log(res); // {CONF_ID: 127312312 };
const rows = await tx.query('SELECT 1 1 FROM RDB$DATABASE');c
console.log(rows) // []
....
Events: (requires local database)
const evtmgr = await fb.attachEvent();
evtmgr.on("post_event", function (name, count) {
console.log("Event fired: ", name, count);
});
// register events
evtmgr.registerEvent(['evt1', 'evt2'], async function (err) {
console.log('ready to receive evt1 and evt2')
})
// stop tracking a particular event
evtmgr.unregisterEvent(["evt1"], function (err) {
console.log('remove evt1, after that you only receive evt2')
})
*********************************************
Samples
node test/mr/01_02-connect_and_start_tx.js vm1
vm1
is a conf key in config.js and stands for the target execution environement.
NOTE: setup the environment variables with your own settings
Firebird database on social networks
Changelog for version v0.9.x
- Better blob management (warning API Breaking Changes)
- Much unit-test
- Better sequentially select (warning API Breaking Changes)
- Added transation.sequentially
- Bugs correction
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
- best of use with total.js - web application framework for node.js
Installation
npm install node-firebird2
Usage
var Firebird = require('node-firebird2');
Usage (Promises)
Usage (Legacy)
Methods
Firebird.escape(value) -> return {String}
- prevent for SQL InjectionsFirebird.attach(options, function(err, db))
attach a databaseFirebird.create(options, function(err, db))
create a databaseFirebird.attachOrCreate(options, function(err, db))
attach or create databaseFirebird.pool(max, options, function(err, db)) -> 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
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)
Methods
db.query(query, [params], function(err, result))
- classic query, returns Array of Objectdb.execute(query, [params], function(err, result))
- classic query, returns Array of Arraydb.sequentially(query, [params], function(row, index, next), function(err), asArray)
- sequentially querydb.detach(function(err))
detach a databasedb.transaction(isolation, function(err, transaction))
create transaction
Transaction methods
transaction.query(query, [params], function(err, result))
- classic query, returns Array of Objecttransaction.execute(query, [params], function(err, result))
- classic query, returns Array of Arraytransaction.sequentially(query, [params], function(row, index, next), function(err), asArray)
- sequentially querytransaction.commit(function(err))
commit current transactiontransaction.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)
// async (only with transaction)
Firebird.attach(options, function(err, db) {
if (err)
throw err;
db.transaction(function(err, transaction) {
// 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();
});
});
});
});
});
// sync blob are fetched automatically
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
// userpicture is a Buffer that contain the blob data
rows[0].userpicture;
});
});
STREAMING A BIG DATA
Firebird.attach(options, function(err, db) {
if (err)
throw err;
// db = DATABASE
db.sequentially('SELECT * FROM BIGTABLE', function(row, index, next) {
// EXAMPLE
stream.write(JSON.stringify(row));
next()
}, 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) {
// console.log(isolation) ---> Isolation { mode: 'write', ..... }
});
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);
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
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) {
console.log(data);
});
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 chose the charset connection, it will always use UTF8. 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
AuthServer = Legacy_Auth
WireCrypt = Disabled
## Contributors
- Henri Gourvest, https://github.com/hgourvest
- Popa Marius Adrian, https://github.com/mariuz
- Peter Širka, https://github.com/petersirka
- Arnaud Le Roy https://github.com/sdnetwork
- Marcio Reis https://github.com/mreis1