firebolt-sdk v0.0.111

Firebolt Node.js SDK

This guide explains how to install, configure, and use the Firebolt Node.js SDK to connect to a Firebolt database from a Node.js application. You can use this SDK to programmatically connect to a Firebolt database, run queries, and manage database resources.

Installation

The Firebolt Node.js SDK is published on the NPM registry. Installing the SDK integrates Firebolt functionality into your application, allowing you to perform database operations and manage resources programmatically.

To install using npm, run the following command:

npm install firebolt-sdk --save

To install using Yarn, run the following command:

yarn add firebolt-sdk

Authentication

After installation, you must authenticate before you can use the SDK to establish connections, run queries, and manage database resources. The following code example sets up a connection using your Firebolt service account credentials:

const connection = await firebolt.connect({
  auth: {
    client_id: '12345678-90123-4567-8901-234567890123',
    client_secret: 'secret',
  },
  engineName: 'engine_name',
  account: 'account_name',
  database: 'database',
});

In the previous code example, the following apply:

• client_id and client_secret are your service account credentials. Follow the Firebolt's guide on how to create one and get its id and secret.
• engineName is the name of the engine which you want to run your queries on.
• database is the target databaset to store your tables.
• account is the account within your organisation. Your account is not the same as your user name.

Example

In the following code example, credentials are stored in environment variables. For bash and similar shells you can set them by running export FIREBOLT_CLIENT_ID=<your_client_id> where <your_client_id> is the id you want to set. This method prevents hardcoding sensitive information in your code so it can be safely commited to a version control system such as Git. Many IDEs, including IntelliJ IDEA, allow the configuration of environment variables in their run configurations.

import { Firebolt } from 'firebolt-sdk'

const firebolt = Firebolt();

const connection = await firebolt.connect({
  auth: {
    client_id: process.env.FIREBOLT_CLIENT_ID,
    client_secret: process.env.FIREBOLT_CLIENT_SECRET,
  },
  account: process.env.FIREBOLT_ACCOUNT,
  database: process.env.FIREBOLT_DATABASE,
  engineName: process.env.FIREBOLT_ENGINE_NAME
});

// Create table
await connection.execute(`
  CREATE TABLE IF NOT EXISTS users (
    id INT,
    name STRING,
    age INT
  )
`);

// Insert sample data
await connection.execute(`
  INSERT INTO users (id, name, age) VALUES
  (1, 'Alice', 30),
  (2, 'Bob', 25)
`);

// Update some rows
await connection.execute(`
  UPDATE users SET age = 31 WHERE id = 1
`);

// Fetch data
const statement = await connection.execute("SELECT * FROM users");

// fetch statement result
const { data, meta } = await statement.fetchResult();

console.log(meta)
// Outputs:
// [
//   Meta { type: 'int null', name: 'id' },
//   Meta { type: 'text null', name: 'name' },
//   Meta { type: 'int null', name: 'age' }
// ]

// or stream result
const { data } = await statement.streamResult();

data.on("metadata", metadata => {
  console.log(metadata);
});

data.on("error", error => {
  console.log(error);
});

const rows = []

for await (const row of data) {
  rows.push(row);
}

console.log(rows)
// Outputs:
// [ [ 1, 'Alice', 31 ], [ 2, 'Bob', 25 ] ]

Contents

• About
• Documentation
• Usage
  • Create connection
    • ConnectionOptions
    • AccessToken
    • Client credentials
    • engineName
  • Test connection
  • Engine URL
  • Execute query
    • ExecuteQueryOptions
    • parameters
    • Named parameters
    • QuerySettings
    • ResponseSettings
  • Fetch result
  • Stream result
  • Result hydration
  • Server-side async queries
    • Execute Async Query
    • Check Async Query Status
    • Cancel Async Query
  • Engine management
    • getByName
    • Engine
      • start
      • stop
  • Database management
    • getByName
    • Database
• Recipes
  • Streaming results
  • Custom stream transformers

About

The Firebolt client for Node.js. firebolt-sdk provides common methods for quering Firebolt databases, fetching and streaming results, and engine management.

firebolt-sdk supports Node.js > v16.

Documentation

Firebolt's Node.js documentation

Usage

Create connection

const connection = await firebolt.connect(connectionOptions);

ConnectionOptions

type AccessTokenAuth = {
  accessToken: string;
};

type ClientCredentialsAuth = {
  client_id: string;
  client_secret: string;
};

type ConnectionOptions = {
  auth: AccessTokenAuth | ServiceAccountAuth;
  database: string;
  engineName?: string;
  engineEndpoint?: string;
  account?: string;
};

engineName

You can omit engineName and execute AQL queries on such connection.

AccessToken

Instead of passing client id/secret directly, you can also manage authentication outside of node sdk and pass accessToken when creating the connection

const connection = await firebolt.connect({
  auth: {
    accessToken: "access_token",
  },
  engineName: 'engine_name',
  account: 'account_name',
  database: 'database',
});

Client credentials

Use client credentials to authenticate as follows:

const connection = await firebolt.connect({
  auth: {
    client_id: 'b1c4918c-e07e-4ab2-868b-9ae84f208d26',
    client_secret: 'secret',
  },
  engineName: 'engine_name',
  account: 'account_name',
  database: 'database',
});

Token caching

Driver implements a caching mechanism for access tokens. If you are using the same client id or secret for multiple connections, the driver will cache the access token and reuse it for subsequent connections. This behavior can be disabled by setting useCache to false in the connection options.

const connection = await firebolt.connect({
  auth: {
    client_id: 'b1c4918c-e07e-4ab2-868b-9ae84f208d26',
    client_secret: 'secret',
  },
  engineName: 'engine_name',
  account: 'account_name',
  database: 'database',
  useCache: false
});

Test connection

Test the connection using the following example script:

const firebolt = Firebolt();
await firebolt.testConnection(connectionOptions)

which will perform authentication and a simple select 1 query

Engine URL

Firebolt engine URLs use the following format:

<engine-name>.<account-name>.<region>.app.firebolt.io

For example: your-engine.your-account.us-east-1.app.firebolt.io. You can find and copy your engine endpoint name in the Firebolt web UI.

Execute Query

const statement = await connection.execute(query, executeQueryOptions);

Execute Query with set flags

const statement = await connection.execute(query, {
  settings: { query_id: 'hello' }
});

ExecuteQueryOptions

export type ExecuteQueryOptions = {
  parameters:? unknown[];
  settings?: QuerySettings;
  response?: ResponseSettings;
};

parameters

The parameters field is used to specify replacements for ? symbol in the query as follows:

For example:

const statement = await connection.execute("select ?, ?", {
  parameters: ["foo", 1]
});

The previous query produces: select 'foo', 1 query

Format Tuple:

import { Tuple } from 'firebolt-sdk'

const statement = await connection.execute("select ? where bar in ?", {
  parameters: [
    1,
    new Tuple(['foo'])
  ]
});

Named parameters

The namedParameters field is used to specify replacements for :name tokens in the query.

For example:

const statement = await connection.execute("select :foo, :bar", {
  namedParameters: { foo: "foo", bar: 123 }
});

The previous query will produce: select 'foo', 123 query

QuerySettings

You can also use QuerySettings to specify set flags. For example: { query_id: 'hello' }

ResponseSettings

Fetch result

const { data, meta, statistics } = await statement.fetchResult();

The Promise API is not recommended for SELECT queries with large result sets (greater than 10,000 rows). This is because it parses results synchronously, so will block the JS thread/event loop and may lead to memory leaks due to peak GC loads.

It is recommended to use LIMIT in your queries when using the Promise API.

Stream result

const { data } = await statement.streamResult();
const rows: unknown[] = [];

data.on("metadata", metadata => {
  console.log(metadata);
});

data.on("error", error => {
  console.log(error);
});

for await (const row of data) {
  rows.push(row);
}

Result hydration

firebolt-sdk maps SQL data types to their corresponding JavaScript equivalents. The mapping is described in the table below:

Server-side async query execution

Firebolt supports server-side asynchronous query execution. This feature allows you to run queries in the background and fetch the results later. This is especially useful for long-running queries that you don't want to wait for or maintain a persistent connection to the server.

Execute Async Query

Executes a query asynchronously. This is useful for long-running queries that you don't want to block the main thread. The resulting statement does not contain data and should only be used to receive an async query token. Token can be saved elsewhere and reused, even on a new connection to check on this query.

const statement = await connection.executeAsync(query, executeQueryOptions);
const token = statement.asyncQueryToken; // used to check query status and cancel it
// statement.fetchResult() -- not allowed as there's no result to fetch

Check Async Query Status

Checks the status of an asynchronous query. Use this to determine if the query is still running or has completed. isAsyncQueryRunning woudl return true or false if the query is running or has finished. isAsyncQuerySuccessful would return true if the query has completed successfully, false if it has failed and undefined if the query is still running.

const token = statement.asyncQueryToken; // can only be fetched for async query
const isRunning = await connection.isAsyncQueryRunning(token);
const isSuccessful = await connection.isAsyncQuerySuccessful(token);

Cancel Async Query

Cancels a running asynchronous query. Use this if you need to stop a long-running query, if its execution is no longer needed.

const token = statement.asyncQueryToken; // can only be fetched for async query
await connection.cancelAsyncQuery(token);

Engine management

Engines can be managed by using the resourceManager object.

import { Firebolt } from 'firebolt-sdk'
const firebolt = Firebolt();
await firebolt.connect(connectionOptions);
const enginesService = firebolt.resourceManager.engine

getByName

Returns engine using engine name.

import { Firebolt } from 'firebolt-sdk'
const firebolt = Firebolt();
await firebolt.connect(connectionOptions);
const engine = await firebolt.resourceManager.engine.getByName("engine_name")

Engine

Start

Starts an engine.

import { Firebolt } from 'firebolt-sdk'
const firebolt = Firebolt();
await firebolt.connect(connectionOptions);
const engine = await firebolt.resourceManager.engine.getByName("engine_name")
await engine.start()

Stop

Stops an engine.

import { Firebolt } from 'firebolt-sdk'
const firebolt = Firebolt();
await firebolt.connect(connectionOptions);
const engine = await firebolt.resourceManager.engine.getByName("engine_name")
await engine.stop()

Engine create

Creates an engine.

import { Firebolt } from 'firebolt-sdk'
const firebolt = Firebolt();
await firebolt.connect(connectionOptions);
const engine = await firebolt.resourceManager.engine.create("engine_name");

Attach to database

Attaches an engine to a database.

import { Firebolt } from 'firebolt-sdk'
const firebolt = Firebolt();
await firebolt.connect(connectionOptions);
const engine = await firebolt.resourceManager.engine.attachToDatabase("engine_name", "database_name");

Engine delete

Deletes an engine.

import { Firebolt } from 'firebolt-sdk'
const firebolt = Firebolt();
await firebolt.connect(connectionOptions);
const engine = await firebolt.resourceManager.engine.getByName("engine_name");
await engine.delete();

Database management

Databases can be managed by using the resourceManager object.

import { Firebolt } from 'firebolt-sdk'
const firebolt = Firebolt();
await firebolt.connect(connectionOptions);
const databaseService = firebolt.resourceManager.database

Database getByName

Returns database using database name.

import { Firebolt } from 'firebolt-sdk'
const firebolt = Firebolt();
await firebolt.connect(connectionOptions);
const database = await firebolt.resourceManager.database.getByName("database_name")

Database

Database create

Creates a database.

import { Firebolt } from 'firebolt-sdk'
const firebolt = Firebolt();
await firebolt.connect(connectionOptions);
const database = await firebolt.resourceManager.database.create("database_name");

Get attached engines

Get engines attached to a database.

import { Firebolt } from 'firebolt-sdk'
const firebolt = Firebolt();
await firebolt.connect(connectionOptions);
const database = await firebolt.resourceManager.database.getByName("database_name");
const engines = database.getAttachedEngines();

Database delete

Deletes a database.

import { Firebolt } from 'firebolt-sdk'
const firebolt = Firebolt();
await firebolt.connect(connectionOptions);
const database = await firebolt.resourceManager.database.getByName("database_name");
await database.delete();

Recipes

Streaming results

The recommended way to consume query results is by using streams.

For convenience, statement.streamResult also returns meta: Promise<Meta[]> and statistics: Promise<Statistics>, which are wrappers over data.on('metadata') and data.on('statistics').

const firebolt = Firebolt();

const connection = await firebolt.connect(connectionParams);

const statement = await connection.execute("SELECT 1");

const {
  data,
  meta: metaPromise,
  statistics: statisticsPromise
} = await statement.streamResult();

const rows: unknown[] = [];

const meta = await metaPromise;

for await (const row of data) {
  rows.push(row);
}

const statistics = await statisticsPromise

console.log(meta);
console.log(statistics);
console.log(rows)

Custom stream transformers

To achieve seamless stream pipes to fs or stdout, you can use the Transform stream.

import stream,  { TransformCallback } from 'stream';

class SerializeRowStream extends stream.Transform {
  public constructor() {
    super({
      objectMode: true,
      transform(
        row: any,
        encoding: BufferEncoding,
        callback: TransformCallback
      ) {
        const transformed = JSON.stringify(row);
        this.push(transformed);
        this.push('\n')
        callback();
      }
    });
  }
}

const serializedStream = new SerializeRowStream()

const firebolt = Firebolt();
const connection = await firebolt.connect(connectionParams);
const statement = await connection.execute("select 1 union all select 2");

const { data } = await statement.streamResult();


data.pipe(serializedStream).pipe(process.stdout);

Or use rowParser that returns strings or Buffer:

const { data } = await statement.streamResult({
  rowParser: (row: string) => `${row}\n`
});

data.pipe(process.stdout);

Development process

Actions before

Setup env variables

cp .env.example .env

Execute tests

  npm test

License

Released under Apache License.