Insights JavaScript SDK

JavaScript SDK for Snowflake REST API, a Node.js API that provides a clean interface for querying Snowflake data. Designed to simplify frontend data visualization with charts and tables, including support for filtering, sorting, and aggregations.

Installation

Insights-js is a private package hosted on GitHub packages. Make sure to configure your npm to use GitHub packages before installing. Your .npmrc should include:

//npm.pkg.github.com/:_authToken=YOUR_GITHUB_TOKEN

example:

# Private packages
//npm.pkg.github.com/:_authToken=${GITHUB_TOKEN}
@xplor:registry=https://npm.pkg.github.com
always-auth=true

and then you can install the package via npm (or pnpm/yarn):

npm install @xplortech/insights-js

Usage

Basic Usage

import insights from "@xplortech/insights-js";
import jwt from "jsonwebtoken";
import "dotenv/config";

// Sign a JWT token with API AUTH_
const payload = {
  sub: "1234567890",
  iat: Math.floor(Date.now() / 1000),
  exp: Math.floor(Date.now() / 1000) + 60 * 15, // 15 minutes
};
const token = jwt.sign(payload, process.env.AUTH_SECRET);

// Initialize the client with an options object
const client = insights({
  apiUrl: "https://insights.example.com",
  token: token,
});

// Or without a token (when auth is handled elsewhere)
const clientWithoutToken = insights({
  apiUrl: "https://insights.example.com",
});

// Get a report
const result = await client.getReport("sales-summary", {
  columns: ["first_name", "last_name"],
  filters: [
    {
      member: "status",
      operator: "equals",
      values: ["active"],
    },
  ],
  order: {
    last_name: "asc",
    first_name: "asc",
  },
  limit: 10,
  offset: 0,
});

// Access the data
const data = result.getData();
console.log("Total records:", result.getCount());
console.log("Pagination:", result.getPagination());

Working with Result Sets

The SDK provides a ResultSet class for working with report data:

// Transform data for tabular display
const tableData = result.tablePivot(["date", "region", "revenue"]);

// Transform data for charts
const chartData = result.chartPivot("date", ["revenue", "orders"]);

Calculating Totals

// Get a report with totals calculation
const totalsResult = await client.getReport("sales-summary", {
  totals: [
    {
      fn: "sum",
      member: "amount",
    },
    {
      fn: "count",
      member: "*",
    },
    {
      fn: "avg",
      member: "amount",
    },
  ],
  filters: [
    {
      member: "status",
      operator: "equals",
      values: ["active"],
    },
  ],
});

// Access the calculated totals
const totals = totalsResult.getTotals();
// { sum_amount: 15000.0, count: 150, avg_amount: 100.0 }

Filtering Data

The API supports various filter operators:

| Operator | Description | Value Requirements | Compatible Types | | ---------------- | ------------------------------------ | -------------------- | ------------------------------ | | equals | Equal to (=) | Any number of values | All types | | notEquals | Not equal to (<>) | Any number of values | All types | | contains | Case-insensitive substring match | Any number of values | String types only | | notContains | Case-insensitive substring non-match | Any number of values | String types only | | startsWith | String starts with | Any number of values | String types only | | notStartsWith | String doesn't start with | Single value | String types only | | endsWith | String ends with | Any number of values | String types only | | notEndsWith | String doesn't end with | Single value | String types only | | gt | Greater than (>) | Single value | Numeric, Date, Time, Timestamp | | gte | Greater than or equal to (>=) | Single value | Numeric, Date, Time, Timestamp | | lt | Less than (<) | Single value | Numeric, Date, Time, Timestamp | | lte | Less than or equal to (<=) | Single value | Numeric, Date, Time, Timestamp | | set | IS NOT NULL | No values needed | All types | | notSet | IS NULL | No values needed | All types | | inDateRange | BETWEEN for dates | 1-2 values | Date, Timestamp | | notInDateRange | NOT BETWEEN for dates | 1-2 values | Date, Timestamp | | beforeDate | Before date (<) | Single value | Date, Timestamp | | afterDate | After date (>) | Single value | Date, Timestamp |

Logical Operators

By default, filters are combined with AND logic. For more complex filtering, you can use logical operators that can be nested:

const result = await client.getReport("employees", {
  columns: ["name", "department", "experience_years"],
  filters: [
    {
      or: [
        {
          member: "department",
          operator: "equals",
          values: ["sales"],
        },
        {
          and: [
            {
              member: "department",
              operator: "equals",
              values: ["marketing"],
            },
            {
              member: "experience_years",
              operator: "gte",
              values: [5],
            },
          ],
        },
      ],
    },
  ],
});

Aggregating Data

When using totals requests, the following aggregation functions are available:

| Function | Description | Compatible Types | | --------------- | ----------------------------- | ------------------------------- | | count | Counts rows | All types (allows wildcard *) | | countDistinct | Counts distinct values | Most types | | sum | Sums values | Numeric only | | avg | Calculates average | Numeric only | | min | Gets minimum value | Numeric, string, date/time | | max | Gets maximum value | Numeric, string, date/time | | median | Calculates median | Numeric only | | mode | Gets most frequent value | Most types | | stddev | Calculates standard deviation | Numeric only | | variance | Calculates variance | Numeric only |

Multiple result sets in a single call

The API can handle multiple query params in a single call.

This feature allows users to batch query requests, reducing the number of HTTP round-trips required when fetching data with different filter criteria, for instance.

// Request multiple queries example
const queryActive = {
  totals: [
    { fn: "sum", member: "revenue" },
    {
      fn: "count",
      member: "*",
    },
    {
      fn: "avg",
      member: "revenue",
    },
  ],
  filters: [
    {
      member: "status",
      operator: "equals",
      values: ["active"],
    },
  ],
};

const queryInactive = {
  totals: [
    { fn: "sum", member: "revenue" },
    {
      fn: "count",
      member: "*",
    },
    {
      fn: "avg",
      member: "revenue",
    },
  ],
  filters: [
    {
      member: "status",
      operator: "equals",
      values: ["inactive"],
    },
  ],
};

// Request both queries in a single call
const [activeTotalsResult, inactiveTotalsResult] = await client.getReport(
  reportName,
  [queryActive, queryInactive],
);

// Get calculated totals
console.log("Active totals:", activeTotalsResult.getTotals());
console.log("Inactive totals:", inactiveTotalsResult.getTotals());

API Reference

Client Methods

• getReport(reportName, query, options) - Get a report by name with query parameters
• getReport(reportName, [query1, query2], options) - Get multiple result sets from a report in a single call
• getReportMeta(reportName) - Get metadata for a specific report
• listReports() - Get a list of all available reports

Options

The getReport method accepts an optional options object as the third parameter:

• sql - When true, includes the generated SQL in the response

// Get a report with the generated SQL included
const result = await client.getReport(
  "sales-summary",
  { columns: ["first_name", "last_name"] },
  { sql: true },
);

// The ResultSet will include the SQL that was executed
console.log(result.getRawSQL());

Query Parameters

• columns - Array of column names to include
• filters - Array of filter criteria
• order - Object with field names as keys and sort direction as values (asc or desc)
• limit - Maximum number of records to return
• offset - Number of records to skip
• totals - Array of aggregation specifications (each with fn and member properties)

ResultSet Methods

• getData() - Get all data rows as an array
• getCount() - Get total count of records
• getTotals() - Get calculated totals
• getPagination() - Get pagination information
• getRawData() - Get raw response data
• getRawSQL() - Get the generated SQL query (if requested)
• tablePivot(columns) - Convert data to a tabular format
• chartPivot(xAxis, yAxes) - Convert data to series format for charting
• exportReport(reportName, query, type) - Export a report in a specific format (default is CSV)

DEVELOPMENT AND PUBLISHING

See README_DEV.md for development and publishing instructions.

License

Xpl License