Firebird forum on Google Groups.
- 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
npm install node-firebird
var Firebird = require('node-firebird');
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) -> return {Object}
create a connection pooling
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.retryConnectionInterval = 1000; // reconnect interval in case of connection drop
options.blobAsText = false; // set to true to get blob as text, only affects blob subtype 1
options.encoding = 'UTF8'; // default encoding for connection is UTF-8
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();
});
});
// 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();
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), function(err))
- sequentially querydb.detach(function(err))
detach a databasedb.transaction(isolation, function(err, transaction))
create transaction
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.commit(function(err))
commit current transactiontransaction.rollback(function(err))
rollback current transaction
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();
}
);
}
);
});
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();
}
);
});
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();
}
);
});
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();
});
});
});
});
Firebird.attach(options, (err, db) => {
if (err) throw err;
db.transaction(Firebird.ISOLATION_READ_COMMITTED, (err, transaction) => {
if (err) {
throw err;
}
transaction.query('SELECT FIRST 10 * FROM JOB', (err, result) => {
if (err) {
transaction.rollback();
return;
}
const arrBlob = [];
for (const item of result) {
const fields = Object.keys(item);
for (const key of fields) {
if (typeof item[key] === 'function') {
item[key] = new Promise((resolve, reject) => {
// the same transaction is used (better performance)
// this is optional
item[key](transaction, (error, name, event, row) => {
if (error) {
return reject(error);
}
// reading data
let value = '';
event.on('data', (chunk) => {
value += chunk.toString('binary');
});
event.on('end', () => {
resolve({ value, column: name, row });
});
});
});
arrBlob.push(item[key]);
}
}
}
Promise.all(arrBlob)
.then((blobs) => {
for (const blob of blobs) {
result[blob.row][blob.column] = blob.value;
}
transaction.commit((err) => {
if (err) {
transaction.rollback();
return;
}
db.detach();
console.log(result);
});
})
.catch((err) => {
transaction.rollback();
});
});
});
});
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();
}
);
});
Transaction types:
Firebird.ISOLATION_READ_UNCOMMITTED
Firebird.ISOLATION_READ_COMMITTED
Firebird.ISOLATION_REPEATABLE_READ
Firebird.ISOLATION_SERIALIZABLE
Firebird.ISOLATION_READ_COMMITTED_READ_ONLY
Firebird.attach(options, function (err, db) {
if (err) throw err;
// db = DATABASE
db.transaction(
Firebird.ISOLATION_READ_COMMITTED,
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();
});
}
);
}
);
});
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();
});
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);
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]);
/*...*/
}
/*...*/
}
);
- 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"}
}
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());
}
);
});
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();})
});
});
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);
}
);
});
Node Firebird uses UTF-8 as the default charset. If you want a different one, such as Latin1, you will need to go into the library and modify the default_encoding in the index.js file
const default_encoding = 'latin1';
This is why you should use Firebird 2.5 server at least.
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
- Henri Gourvest, https://github.com/hgourvest
- Popa Marius Adrian, https://github.com/mariuz
- Peter Širka, https://github.com/petersirka