Connecting to CrateDB with Node.js

Tutorials
1

This article describes how to connect to CrateDB from Node.js using the node-postgres driver.

Prerequisites

Install the node package to use the driver

npm install pg

Connecting to CrateDB

Launch a free trial cluster on CrateDB Cloud or start CrateDB locally (e.g. launch with Docker) to have a CrateDB cluster to connect to.

Afterwards start the following script to connect and execute a simple test query against CrateDB:

const { Pool } = require('pg');

const CRATE_CONNECTION_STRING = 'postgres://crate@localhost:5432';
const statement = 'SELECT id, hostname FROM sys.nodes LIMIT 1';

const cratePool = new Pool({
    connectionString: CRATE_CONNECTION_STRING,
    idleTimeoutMillis: 15000,
    connectionTimeoutMillis: 5000,
    query_timeout: 30000,
});

cratePool.connect((err, client, release) => {
    if (err) {
        return console.error('Error acquiring client', err.stack);
    }
    client.query(statement, (err, result) => {
        release();
        if (err) {
            return console.error('Error executing query', err.stack);
        }
        console.log(result.rows);
    })
})

cratePool.end();

Ingesting into CrateDB with UNNEST

For achieving high ingest performance it’s suggested to use the UNNEST approach with node-postgres. The following example shows this method:

const { Pool } = require('pg');

// create test table with following query:
// CREATE TABLE events(ts TIMESTAMP, payload OBJECT);

const CRATE_CONNECTION_STRING = 'postgres://crate@localhost:5432/doc';
const TABLE = 'doc.events';
const COLUMNS = ['ts', 'payload'];

const cratePool = new Pool({
    connectionString: CRATE_CONNECTION_STRING,
    idleTimeoutMillis: 15000,
    connectionTimeoutMillis: 5000,
    database: 'doc',
    query_timeout: 30000,
});

// generate sample data
let ts = Date.now();
let timestamps = [];
let payloads = [];
for (let i = 0; i < 200; i++) {
    timestamps.push(ts + i*1000);
    payloads.push(`{"element": ${i}}`);
}

const statement = `INSERT INTO ${TABLE} ("${COLUMNS.join('","')}") 
    (SELECT * FROM UNNEST ( $1::array(long), $2::array(object) ));`;
const values = [timestamps, payloads];

cratePool.connect((err, client, release) => {
    if (err) {
        return console.error('Error acquiring client', err.stack);
    }
    client.query(statement, values, (err, result) => {
        release();
        if (err) {
            return console.error('Error executing query', err.stack);
        }
        console.log(`Successfully inserted ${result.rowCount} rows`);
    })
})

cratePool.end();

Please note that with this approach CrateDB will drop rows which would produce an error e.g. due to an incorrect data type.

Ingesting into CrateDB with multiple value expression

An alternative to the UNNEST approach above is to use multiple value expression.

const { Pool } = require('pg');

const CRATE_CONNECTION_STRING = 'postgres://crate@localhost:5432';
const TABLE = 'doc.events';
const COLUMNS = ['ts', 'payload'];

const cratePool = new Pool({
    connectionString: CRATE_CONNECTION_STRING,
    idleTimeoutMillis: 15000,
    connectionTimeoutMillis: 5000,
    query_timeout: 30000,
});

let ts = Date.now();
let query = `INSERT INTO ${TABLE} ("${COLUMNS.join('","')}") VALUES `;
for (let i = 0; i < 200; i++) {
    if (i > 0) query += `,`;
    query += `('${ts + i*1000}','{"element": ${i}}')`;
}
query += ';';

cratePool.connect((err, client, release) => {
    if (err) {
        return console.error('Error acquiring client', err.stack);
    }
    client.query(query, (err, result) => {
        release();
        if (err) {
            return console.error('Error executing query', err.stack);
        }
        console.log(`Successfully inserted ${result.rowCount} rows`);
    });
});

cratePool.end();

Notice

When working with JavaScript, pay attention to integers outside the safe range (smaller than -9007199254740991 or larger than 9007199254740991). Outside this range, JavaScript cannot represent integers reliably due to the internal representation as 64-bit floating-point value and you will lose precision.

1 Like