Connect to Databend Using Node.js

Databend enables you to develop Node.js programs that interact with Databend using Databend Driver Node.js Binding. This driver provides an interface for connecting to Databend and performing operations such as executing SQL queries and retrieving results. With the Databend driver, you can take advantage of the powerful distributed computing capabilities of Databend and build scalable data processing applications. Visit https://www.npmjs.com/package/databend-driver for more information about the driver.

To install the Databend driver for Node.js:

npm install --save databend-driver

note

Before installing the driver, make sure to fulfill the following prerequisites:

• Node.js must already be installed on the environment where you want to install the driver.
• Ensure that you can run the node and npm commands.
• Depending on your environment, you may require sudo privileges to install the driver.

Data Type Mappings

This table illustrates the correspondence between Databend general data types and their corresponding Node.js equivalents:

Databend	Node.js
BOOLEAN	Boolean
TINYINT	Number
SMALLINT	Number
INT	Number
BIGINT	Number
FLOAT	Number
DOUBLE	Number
DECIMAL	String
DATE	Date
TIMESTAMP	Date
VARCHAR	String
BINARY	Buffer

This table illustrates the correspondence between Databend semi-structured data types and their corresponding Node.js equivalents:

Databend	Node.js
ARRAY	Array
TUPLE	Array
MAP	Object
VARIANT	String
BITMAP	String
GEOMETRY	String

Databend Node.js Driver Behavior Summary

The Node.js driver offers similar functionalities as a binding of the Rust Driver, with identically named functions having the same logic and capabilities.

The table below summarizes the main behaviors and functions of the Node.js Driver and their purposes:

Function Name	Description
info	Returns the client's connection information.
version	Returns the result of executing the SELECT VERSION() statement.
exec	Executes an SQL statement and returns the number of rows affected.
query_iter	Executes an SQL query and returns an iterator for processing results row by row.
query_iter_ext	Executes an SQL query and returns an iterator that includes statistical information about the results.
query_row	Executes an SQL query and returns a single row result.
stream_load	Uploads data to a built-in Stage and executes insert/replace with stage attachment.

Tutorial-1: Integrating with Databend using Node.js

Before you start, make sure you have successfully installed a local Databend. For detailed instructions, see Local and Docker Deployments.

Step 1. Prepare a SQL User Account

To connect your program to Databend and execute SQL operations, you must provide a SQL user account with appropriate privileges in your code. Create one in Databend if needed, and ensure that the SQL user has only the necessary privileges for security.

This tutorial uses a SQL user named 'user1' with password 'abc123' as an example. As the program will write data into Databend, the user needs ALL privileges. For how to manage SQL users and their privileges, see User & Role.

CREATE USER user1 IDENTIFIED BY 'abc123';
GRANT ALL on *.* TO user1;

Step 2. Write a Node.js Program

1

Copy and paste the following code to a file named databend.js:

databend.js

const { Client } = require("databend-driver");

// Connecting to a local Databend with a SQL user named 'user1' and password 'abc123' as an example.
// Feel free to use your own values while maintaining the same format.
const dsn = process.env.DATABEND_DSN ? process.env.DATABEND_DSN : "databend://user1:abc123@localhost:8000/default?sslmode=disable";

async function create_conn() {
  this.client = new Client(dsn);
  this.conn = await this.client.getConn();
  console.log("Connected to Databend Server!");
}

async function select_books() {
  var sql = "CREATE DATABASE IF NOT EXISTS book_db";
  await this.conn.exec(sql);
  console.log("Database created");

  var sql = "USE book_db";
  await this.conn.exec(sql);
  console.log("Database used");

  var sql = "CREATE TABLE IF NOT EXISTS books(title VARCHAR, author VARCHAR, date VARCHAR)";
  await this.conn.exec(sql);
  console.log("Table created");

  var sql = "INSERT INTO books VALUES('Readings in Database Systems', 'Michael Stonebraker', '2004')";
  await this.conn.exec(sql);
  console.log("1 record inserted");

  var sql = "SELECT * FROM books";
  const rows = await this.conn.queryIter(sql);
  const ret = [];
  let row = await rows.next();
  while (row) {
    ret.push(row.values());
    row = await rows.next();
  }
  console.log(ret);
}

create_conn().then(conn => {
  select_books();
});

2

Run node databend.js

Connected to Databend Server!
Database created
Database used
Table created
1 record inserted
[ [ 'Readings in Database Systems', 'Michael Stonebraker', '2004' ] ]

Tutorial-2: Integrating with Databend Cloud using Node.js

Before you start, make sure you have successfully created a warehouse and obtained the connection information. For how to do that, see Connecting to a Warehouse.

Step 1. Create a Node.js Package

$ mkdir databend-sample
$ cd databend-sample
$ npm init -y

Step 2. Add Dependencies

Install the Databend driver for Node.js:

$ npm install --save databend-driver

Add a new NPM script to package.json :

 "scripts": {
+  "run-example": "node index.js",
   "test": "echo \"Error: no test specified\" && exit 1"
 },

Step 3. Connect with databend-driver

Create a file named index.js with the following code:

const { Client } = require("databend-driver");

const dsn = process.env.DATABEND_DSN ? process.env.DATABEND_DSN : "databend://{USER}:{PASSWORD}@${HOST}:443/{DATABASE}?&warehouse={WAREHOUSE_NAME}";

async function create_conn() {
  this.client = new Client(dsn);
  this.conn = await this.client.getConn();
  console.log("Connected to Databend Server!");
}

async function select_data() {
  let sql_table_create = `CREATE TABLE IF NOT EXISTS data (
		i64 Int64,
		u64 UInt64,
		f64 Float64,
		s   String,
		s2  String,
		d   Date,
		t   DateTime)`;

  await this.conn.exec(sql_table_create);

  let sql_insert = "INSERT INTO data VALUES ('1234', '2345', '3.1415', 'test', 'test2', '2021-01-01', '2021-01-01 00:00:00');";
  await this.conn.exec(sql_insert);

  let sql_select = "SELECT * FROM data";
  const rows = await this.conn.queryIter(sql_select);
  const ret = [];
  let row = await rows.next();
  while (row) {
    ret.push(row.values());
    row = await rows.next();
  }
  console.log(ret);
}

create_conn().then(conn => {
  select_data();
});

tip

Replace {USER}, {PASSWORD}, {HOST}, {WAREHOUSE_NAME} and {DATABASE} in the code with your connection information. For how to obtain the connection information, see Connecting to a Warehouse.

Step 4. Run sample with NPM

$ npm run run-example