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
      • Token caching
      • Server-side prepared statement
  • 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
  • Transaction management
    • Transaction methods
    • Basic transaction usage
    • Error handling
    • Transaction isolation
  • Engine management
    • getByName
    • Engine
      • Start
      • Stop
      • Engine create
      • Attach to database
      • Engine delete
  • Database management
    • getByName
    • Database
    • Database create
    • Get attached engines
    • Database delete
• Recipes
  • Streaming results
  • Custom stream transformers
  • In-memory stream
• Special Considerations

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 PreparedStatementParamStyle = "native" | "fb_numeric";

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

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',
});

engineName

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

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
});

Server-side prepared statement

Driver has the option to use server-side prepared statements, so all parameters are set on the server side, preventing SQL injection attacks. This behavior can be enabled by setting preparedStatementParamStyle to fb_numeric in the connection options, otherwise, prepared statements will retain default behavior(same behavior if value is set to native) and queries will be formatted client side.

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

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

This approach is useful if you want to modify the execution of a particular query, for example setting a timeout or modifying a time zone for date calculations.

const statement = await connection.execute(query, {
  settings: { statement_timeout: 1000 }
});

Full list of flags that can be passed here is available on this documentation page.

Note: the flags set in this way will be active only for the duration of the query. If you require a session-level flag, which would apply to all the queries executed on this connection, use the SQL syntax to set it:

await connection.execute("SET statement_timeout=1000")

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

| Parameter | Required | Default | Description | |---------------|----------|--------------|-----------------------------------| | output_format | | JSON_COMPACT | Specifies format of selected data |

You can also use QuerySettings to specify set flags. For example: { statement_timeout: 1000 } Full list of available settings can be found on this page.

ResponseSettings

| Parameter | Required | Default | Description | |-------------------|----------|---------|-------------------------------------------------------| | normalizeData | | false | Maps each row in response from array format to object | | bigNumberAsString | | false | Hydrate BigNumber as String |

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:

| Category | SQL type | JavaScript type | Notes | |-------------|----------|-----------------|-----------------------------------------------------------------------------------------------------------------------------------| | Numeric | INT | Number | If value cannot be represented by JavaScript Number (determine using Number.isSafeInteger), BigNumber from "bignumber.js" is used | | | INTEGER | Number | | | | BIGINT | Number | | | | LONG | Number | | | | FLOAT | Number | | | | DOUBLE | Number | | | String | VARCHAR | String | | | | TEXT | String | | | | STRING | String | | | Date & Time | DATE | Date | |

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);

Transaction management

Firebolt's Node.js SDK supports database transactions, allowing you to group multiple operations into atomic units of work. Transactions ensure data consistency and provide the ability to rollback changes if needed.

Transaction methods

The SDK provides three main methods for transaction management:

await connection.begin();    // Start a new transaction
await connection.commit();   // Commit the current transaction
await connection.rollback(); // Rollback the current transaction

Basic transaction usage

The following example demonstrates a basic transaction that inserts data and commits the changes:

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
});

// Start a transaction
await connection.begin();

try {
  // Perform multiple operations
  await connection.execute(`
    INSERT INTO users (id, name, age) VALUES (1, 'Alice', 30)
  `);
  
  await connection.execute(`
    INSERT INTO users (id, name, age) VALUES (2, 'Bob', 25)
  `);
  
  // Commit the transaction
  await connection.commit();
  console.log('Transaction committed successfully');
} catch (error) {
  // Rollback on error
  await connection.rollback();
  console.error('Transaction rolled back due to error:', error);
}

Transaction with prepared statements

Transactions work seamlessly with prepared statements:

await connection.begin();

try {
  // Use prepared statements within transactions
  await connection.execute(
    'INSERT INTO users (id, name, age) VALUES (?, ?, ?)',
    { parameters: [4, 'Diana', 28] }
  );
  
  await connection.execute(
    'UPDATE users SET age = ? WHERE id = ?',
    { parameters: [29, 4] }
  );
  
  await connection.commit();
} catch (error) {
  await connection.rollback();
  throw error;
}

Error handling

Transaction state errors

The SDK will throw errors for invalid transaction operations:

try {
  // This will throw an error if no transaction is active
  await connection.commit();
} catch (error) {
  console.error('Cannot commit: no transaction in progress');
}

try {
  await connection.begin();
  // This will throw an error if a transaction is already active
  await connection.begin();
} catch (error) {
  console.error('Cannot begin: transaction already in progress');
}

Transaction isolation

Transactions in Firebolt provide isolation between concurrent operations. Changes made within a transaction are not visible to other connections until the transaction is committed:

// Connection 1 - Start transaction and insert data
const connection1 = await firebolt.connect(connectionOptions);
await connection1.begin();
await connection1.execute("INSERT INTO users (id, name) VALUES (5, 'Eve')");

// Connection 2 - Cannot see uncommitted data
const connection2 = await firebolt.connect(connectionOptions);
const statement = await connection2.execute('SELECT COUNT(*) FROM users WHERE id = 5');
const { data } = await statement.fetchResult();
console.log('Count from connection 2:', data[0][0]); // Should show 0

// Connection 1 - Commit transaction
await connection1.commit();

// Connection 2 - Now can see committed data
const statement2 = await connection2.execute('SELECT COUNT(*) FROM users WHERE id = 5');
const { data: data2 } = await statement2.fetchResult();
console.log('Count after commit:', data2[0][0]); // Should show 1

Server-side prepared statement

Firebolt supports server-side prepared statement execution. This feature allows for safer execution of parameterized queries by escaping parameters on the server side. This is useful for preventing SQL injection attacks.

Difference between client-side and server-side prepared statement

The main difference between client-side and server-side prepared statement is the way parameters appear in queries. In client-side prepared statement, parameters are inserted in place of ? symbols in the case of normal parameters, or :name in the case of named parameters. In server-side prepared statement, parameters are represented by $number tokens.

//client-side prepared statement with normal parameters
const statement = await connection.execute("select ?, ?", {
  parameters: ["foo", 1]
});

//client-side prepared statement with named parameters
const statement = await connection.execute("select :foo, :bar", {
  namedParameters: { foo: "foo", bar: 123 }
});

//server-side prepared statement via parameters field of ExecuteQueryOptions
const statement = await connection.execute("select $1, $2", {
  parameters: ["foo", 1]
});

//server-side prepared statement via namedParameters field of ExecuteQueryOptions
const statement = await connection.execute("select $1, $2", {
  namedParameters: { $1: "foo", $2: 123 }
});

Usage with parameters field

When using the parameters field, the driver will automatically set the number value to the corresponding $number token in the query.

// Even though the query contains $1 twice, we only need to set it once
const statement = await connection.execute("select $1, $1", {
  parameters: ["foo"]
});

// The order is important, so the first parameter will be set to $1 and the second to $2
const statement1 = await connection.execute("select $2, $1", {
  parameters: ["foo", 1]
});

const statement2 = await connection.execute("select $1, $2", {
  parameters: ["foo", 1]
});
// statement1 and statement2 will NOT produce the same query

Usage with namedParameters field

When using the namedParameters field, the driver will use the value provided as the name of the parameter when sending the query to the server. Considering this, we can more easily recognize the parameters in the query.

const statement = await connection.execute("select $1, $2", {
  namedParameters: { $1: "foo", $2: 123 }
});
// The order is not important, so we can set the parameters in any order
const statement1 = await connection.execute("select $2, $1", {
  namedParameters: { $2: "foo", $1: 123 }
});

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

| Property | Type | Notes | |--------------------------|-------------------------------------------|-------| | name | string | | | endpoint | string | | | current_status_summary | string | |

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

| Property | Type | Notes | |---------------|-------------------------------------------|-------| | name | string | | | description | string | |

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

Streaming can only be used with the executeStream method. The streamResult method for the normal execute method returns an in-memory stream of result, rather than dynamically fetching them from the response. This is further explained in the in-memory stream section.

The recommended way to consume query results is by using streams with standard events:

• data.on('meta')
• data.on('data')
• data.on('end')
• data.on('error')

const firebolt = Firebolt();

const connection = await firebolt.connect(connectionParams);

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

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

const rows: unknown[] = [];

const meta = await stream.once(data, "meta");

data.on("data", data => {;
  rows.push(data);
});

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

In case an errors occurs before streaming, or during the first packet, the error will be thrown by the executeStream method. If the error occurs during streaming, it will be emitted by the stream.

try {
  await connection.executeStream("select *1;");
} catch (error) {
  //error is thrown directly since this is a syntax error
}

const statement = await connection.executeStream(
  "select 1/(i-100000) as a from generate_series(1,100000) as i"
);
const { data } = await statement.streamResult();

data.on("error", error => {
  //error is emitted by the stream after first chunk of results
  console.log(error); 
});

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.executeStream("select * from generate_series(1, 1000)");

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


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

In-memory stream

When using the streamResult method on the object returned from a simple execute method, the driver will return an in-memory stream of the result. This is useful for small result sets, but not recommended for large result sets.

In this case the whole result will be first fetched in memory and then made available via streamResult. This is done for compatibility reasons and has no performance benefits compared to using fetchResult

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)

Special Considerations

When using the Firebolt Node.js SDK, keep the following considerations in mind:

• Avoid blocking the event loop: Node.js runs JavaScript code on a single-threaded event loop. Blocking operations can degrade performance and responsiveness and may lead to networking errors. For more details, see Don't Block the Event Loop.
• Streaming large result sets: Use the streamResult method instead of fetchResult for large datasets to avoid memory issues.
• Environment variables: Always store sensitive credentials (like client_id and client_secret) in environment variables rather than hardcoding them in your source code.

Development process

Actions before

Setup env variables

cp .env.example .env

Execute tests

  npm test

License

Released under Apache License.