Tutorial on operating Firebird database with Node.js_node.js

Firebird is a cross-platform relational database system that can currently run on Windows, Linux and various Unix operating systems and provides most of the SQL-99 standard functions. It can run as a database server in a multi-user environment and also provides the implementation of an embedded database.

Firebird was born out of Borland's open source database Interbase6.0. It is a completely non-commercial product developed in C and C++.

A firebird database server can manage multiple independent databases, and each database can support multiple client connections at the same time. In short: it is an open source, powerful, and freely usable database (even for commercial use).

Features

Firebird is a true relational database that supports all features of large-scale relational databases such as stored procedures, views, triggers, transactions, etc.;

Firebird supports most SQL92 commands and supports most SQL99 commands. The new version of Firebird 2.0 has more complete support for SQL99;

Firebird source code is based on the mature commercial database Interbase, which has good stability and good compatibility with Interbase;
There is no need to consider licensing fees (free), and there is no need to worry that one day you or your customers will be sued by the database developer for using piracy;

Easy to publish, the installation file is only a few M, and it is highly customizable. The distribution of the client is also very simple, only one DLL file is needed;
An embedded server version of Firebird, no need to install, just run directly, the first choice for stand-alone development;

Firebird operates very efficiently;

It is highly portable and can run under Linux, Unix, MacOS, Windows, and Solaris systems, and the database format is exactly the same without modification;

The development environment is well supported. Delphi and C++Builder do not need to be connected through ODBC, but can directly use the native development interface to develop Firebird-based programs.

Why use FireBird?

For small business users, current open source databases have two shortcomings: they are either too large (such as MySQL, PostgreSQL); or they are too small and lack functionality and documentation (such as HypersonicSQL and McKoi). In many application environments, users need a database of moderate size and full functionality.

Firebird is relatively small, its RPM version is only 2.6MB. This makes it an ideal "embedded database" for bundling with other application servers and applications. Firebird has most of the features of mature databases, such as supporting stored procedures, SQL compatibility, etc. If users have experience using DB2 or PostgreSQL, they will find that the syntax of Firebird is very similar to theirs, and the data types and data processing methods are also very similar.

Installation

I have talked so much about FireBird before, now I will explain how to use Node to operate FireBird.

If you want to operate FireBird through Node, you must first install the module to operate FireBird - node-firebird. The following code:

npm install node-firebird

After the installation is completed, we can use node to operate FireBird in the program, as shown in the following code:

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

Connection options

When we operate FireBird through node, we must first set up the database connection, including the connected IP address, port number, database name (can be the full name including the path), connected user name, and password. The following code:

 var options = {};
 options.host = '127.0.0.1';          // 设置主机地址
 options.port = 3050;              // 端口号
 options.database = '/home/user/test.fdb';   // 数据库名称
 options.user = 'SYSDBA';            // 用户名
 options.password = 'masterkey';        // 密码

In this way, the database connection is set up.

escape

There is a method named escape in the node-firebird module, and its return value is of type String. Use this method to prevent SQL injection, as shown in the following code:

FireBird.escape(value) -> return {String}

Examples are as follows:

var FireBird = require('node-firebird');
var sql1 = 'SELECT * FROM a WHERE ID='+Firebird.escape(2) ;
console.log(sql1);

create

The create method is used to create a database, its syntax format is as follows:

FireBird.create(options, function(err, db));

The following code:

FireBird.create(options,function(err,db){
  if(err)
    throw err;
  console.log('创建数据库成功');
  db.detach(); //关闭数据库连接
});

Note: When the database exists, using this method to create a database will overwrite the original database, resulting in data loss.

query

The usage of db.query method has been introduced before. Here I will continue to tell you the usage of db.query(). When the query statement is a query statement, the returned result is an array of object type (that is, the value of result). We can use db.query() to add, modify, delete, and update tables in the database. There are two usages of db.query(), one is query without parameters, and the other is query with parameters.

Query with parameters:

db.query(query, [params], function(err, result))

The example code is as follows to insert data into table a:

FireBird.attach(options, function(err, db) {
  if (err)
    throw err;
  // db = DATABASE
  db.query('insert into a (id,name) values(?,?)',[1,'Tom'], function(err, result) {
    if (err)
      throw err;
    console.log("insert success!");
    db.detach();
  });
});

Example code updates data in table a:

FireBird.attach(options, function(err, db) {
  if (err)
  throw err;
  // db = DATABASE
  db.query("update a set name='Ann' where id=1", function(err, result) {
    if (err)
      throw err;
    console.log("update success!");
    db.detach();
  });
});

The above is the entire content of this article, which teaches you how to use Node.js to operate Firebird database. I hope you like it.