tessl/npm-lightdash--warehouses

Warehouse connectors providing unified interface for querying BigQuery, Databricks, PostgreSQL, Redshift, Snowflake, and Trino data warehouses

Overview

Eval results

Files

Lightdash Warehouses

Name: tessl/npm-lightdash--warehouses
Author: tessl

Lightdash Warehouses provides a unified interface for connecting to and querying multiple data warehouse platforms. It implements warehouse-specific clients for Google BigQuery, Clickhouse, Databricks, PostgreSQL, Amazon Redshift, Snowflake, and Trino, offering consistent APIs for query execution, metadata exploration, and connection management.

Quick Start

import { warehouseClientFromCredentials } from '@lightdash/warehouses';
import { WarehouseTypes } from '@lightdash/common';

// 1. Create client using factory function (recommended)
const client = warehouseClientFromCredentials({
  type: WarehouseTypes.POSTGRES,
  host: 'localhost',
  port: 5432,
  user: 'myuser',
  password: 'mypassword',
  dbname: 'mydb',
  schema: 'public',
  sslmode: 'disable',
});

// 2. Test connection (throws on failure)
await client.test();

// 3. Stream query results (memory-efficient)
await client.streamQuery(
  'SELECT * FROM users WHERE active = true',
  (data) => {
    console.log('Batch:', data.rows.length, 'rows');
    // Process data.rows and data.fields
  },
  { tags: { query_source: 'api' } }
);

// 4. Get warehouse metadata
const tables = await client.getAllTables();
console.log('Tables:', tables.map(t => t.table));

Package Information

Package Name: @lightdash/warehouses
Package Type: npm (scoped)
Language: TypeScript

Installation:

npm install @lightdash/warehouses
npm install lz4  # Optional peer dependency for Databricks support

Note: The lz4 package is an optional peer dependency that may be needed by the Databricks client (@databricks/sql). The package can be imported and used for all warehouse types without lz4. If you plan to use Databricks, you may need to install lz4. It is a native module that requires compilation during installation. Installation of lz4 requires:

Node.js development headers (usually included with Node.js)
C++ compiler (build-essential on Linux, Xcode Command Line Tools on macOS, Visual Studio Build Tools on Windows)
Python (for node-gyp)

Choosing the Right Warehouse

Decision Tree for Warehouse Selection:

Your Database	Warehouse Type	Key Considerations
Google BigQuery	`WarehouseTypes.BIGQUERY`	Native async queries, requires service account JSON
Clickhouse	`WarehouseTypes.CLICKHOUSE`	Named parameters with types, HTTP/HTTPS, columnar OLAP
Databricks Unity Catalog	`WarehouseTypes.DATABRICKS`	OAuth M2M/U2M or PAT, Unity Catalog 3-part naming
PostgreSQL	`WarehouseTypes.POSTGRES`	SSH tunnel support, SSL modes, streaming cursors
Amazon Redshift	`WarehouseTypes.REDSHIFT`	Inherits PostgreSQL, SSH tunnel, port 5439
Snowflake	`WarehouseTypes.SNOWFLAKE`	Native async + pagination, multiple auth methods
Trino/Presto	`WarehouseTypes.TRINO`	Cross-catalog queries, approximate percentiles

Authentication Method Decision Tree:

Warehouse	Available Auth Methods	Recommendation
BigQuery	Service Account, Application Default Credentials	Service Account for production
Clickhouse	HTTP Basic Auth (Username/Password)	Always use HTTPS (secure: true)
Databricks	PAT, OAuth M2M, OAuth U2M, External Browser	OAuth M2M for services, PAT for development
PostgreSQL	Password, SSL Certificates	Use SSL verify-full for AWS RDS
Redshift	Password, SSL Certificates	Use SSL require for AWS Redshift
Snowflake	Password, Key Pair, OAuth, External Browser	Key Pair for services, OAuth for users
Trino	BasicAuth (Username/Password)	Always use HTTPS

Critical Information for Agents

NOT Exported Types and Classes

The following warehouse components are NOT exported from the package:

ClickhouseWarehouseClient - Clickhouse client is only accessible via warehouseClientFromCredentials()
ClickhouseSqlBuilder - Clickhouse SQL builder is only accessible via warehouseSqlBuilderFromType()
ClickhouseTypes - Clickhouse types enum is only accessible via factory functions
DatabricksTypes - Databricks types enum is internal only

Deprecated Methods

// ⚠️ DEPRECATED - Loads all results into memory:
const results = await client.runQuery('SELECT * FROM large_table');

// ✅ PREFERRED - Streams results in batches:
await client.streamQuery(
  'SELECT * FROM large_table',
  (batch) => processBatch(batch),
  { tags: {} }
);

Parameter Types by Warehouse

Warehouse	Positional ($1, $2)	Named (:param or {param})	No Parameters
PostgreSQL	✅ via values array	❌	✅
Redshift	✅ via values array	❌	✅
BigQuery	✅ via values array	❌	✅
Clickhouse	❌	✅ {param: Type} via queryParams (type annotation required in SQL)	✅
Databricks	❌	❌	✅
Snowflake	✅ via values array	❌	✅
Trino	❌	❌	✅

Async Query Support

Warehouse	Native Async	Pagination	Notes
BigQuery	✅ Yes	❌ No	Returns query ID, streams all results via callback
Snowflake	✅ Yes	✅ Yes	Returns query ID, supports page/pageSize retrieval
Clickhouse	❌ No	❌ No	Uses streaming fallback
Databricks	❌ No	❌ No	Uses streaming fallback
PostgreSQL	❌ No	❌ No	Uses streaming fallback
Redshift	❌ No	❌ No	Uses streaming fallback
Trino	❌ No	❌ No	Uses streaming fallback

Common Pitfalls

1. Column Name Casing

// Trino normalizes all column names to lowercase
await trinoClient.streamQuery(
  'SELECT USER_ID, USER_NAME FROM users', // Snowflake returns uppercase
  (data) => {
    // ❌ WRONG - Will fail for Trino
    console.log(data.rows[0].USER_ID);

    // ✅ CORRECT - Trino normalizes to lowercase
    console.log(data.rows[0].user_id);
  }
);

// Snowflake: Use quotedIdentifiersIgnoreCase flag
const snowflakeClient = warehouseClientFromCredentials({
  type: WarehouseTypes.SNOWFLAKE,
  quotedIdentifiersIgnoreCase: false, // Enforces case sensitivity
  // ... other credentials
});

2. SSH Tunnel Setup

// ❌ WRONG - Trying to use SSH tunnel with unsupported warehouse
const databricksClient = warehouseClientFromCredentials({
  type: WarehouseTypes.DATABRICKS,
  useSshTunnel: true, // NOT supported for Databricks
  // ...
});

// ✅ CORRECT - SSH tunnels only supported for PostgreSQL and Redshift
const postgresClient = warehouseClientFromCredentials({
  type: WarehouseTypes.POSTGRES,
  useSshTunnel: true,
  sshTunnelHost: 'bastion.example.com',
  sshTunnelPrivateKey: '-----BEGIN RSA PRIVATE KEY-----\n...',
  // ...
});

3. Required Callback for executeAsyncQuery

// BigQuery requires callback (results streamed immediately)
// ❌ WRONG - Results will be lost
await client.executeAsyncQuery({ sql: 'SELECT ...' });

// ✅ CORRECT - Provide callback to capture results
await client.executeAsyncQuery(
  { sql: 'SELECT ...' },
  (rows, fields) => {
    // Process results as they arrive
    saveToDatabase(rows);
  }
);

// Snowflake supports pagination (callback optional for retrieval)
const result = await snowflakeClient.executeAsyncQuery(
  { sql: 'SELECT ...' },
  (rows, fields) => saveToDatabase(rows)
);
// Later: retrieve results with pagination
const page1 = await snowflakeClient.getAsyncQueryResults({
  queryId: result.queryId!,
  page: 1,
  pageSize: 1000,
});

4. Metric SQL Requirements

// ❌ WRONG - Incomplete Metric object
const sql = client.getMetricSql('revenue', {
  type: MetricType.SUM,
  sql: 'revenue',
  // Missing required Field properties!
});

// ✅ CORRECT - Complete Metric object
import { FieldType, MetricType } from '@lightdash/common';

const sql = client.getMetricSql('revenue', {
  fieldType: FieldType.METRIC,
  type: MetricType.SUM,
  sql: 'revenue',
  name: 'total_revenue',
  label: 'Total Revenue',
  table: 'orders',
  tableLabel: 'Orders',
  hidden: false,
});

5. Error Handling

import {
  WarehouseConnectionError,
  WarehouseQueryError
} from '@lightdash/common';

// ✅ CORRECT - Handle specific error types
try {
  await client.test();
} catch (error) {
  if (error instanceof WarehouseConnectionError) {
    // Connection failed - check credentials, network, firewall
    console.error('Connection failed:', error.message);
  } else if (error instanceof WarehouseQueryError) {
    // Query failed - check SQL syntax, permissions
    console.error('Query failed:', error.message);
    console.error('Line number:', error.lineNumber); // If available
  } else {
    // Unexpected error
    throw error;
  }
}

Core Imports

import {
  // Factory functions (RECOMMENDED - use these for all warehouses)
  warehouseClientFromCredentials,
  warehouseSqlBuilderFromType,

  // Warehouse client classes (can also use factory)
  BigqueryWarehouseClient,
  DatabricksWarehouseClient,
  PostgresClient, // Generic base class for PostgreSQL-compatible clients
  PostgresWarehouseClient,
  RedshiftWarehouseClient,
  SnowflakeWarehouseClient,
  TrinoWarehouseClient,

  // SQL Builder classes (can also use factory)
  BigquerySqlBuilder,
  DatabricksSqlBuilder,
  PostgresSqlBuilder,
  RedshiftSqlBuilder,
  SnowflakeSqlBuilder,
  TrinoSqlBuilder,

  // SSH Tunnel
  SshTunnel,

  // Databricks OAuth helpers
  exchangeDatabricksOAuthCredentials,
  refreshDatabricksOAuthToken,

  // Snowflake utility functions
  mapFieldType, // Exported from SnowflakeWarehouseClient module

  // Warehouse-specific type enums
  // Note: ClickhouseTypes is NOT exported - only accessible via factory functions
  // Note: DatabricksTypes is NOT exported - Databricks types are internal only
  BigqueryFieldType,
  PostgresTypes,
  SnowflakeTypes,
  TrinoTypes,

  // Some types are re-exported from @lightdash/common for convenience
  type WarehouseClient,
  type WarehouseCatalog,
  type WarehouseTableSchema,
  type WarehouseExecuteAsyncQuery,
  type WarehouseExecuteAsyncQueryArgs,
} from '@lightdash/warehouses';

// Additional types must be imported directly from @lightdash/common
import {
  type CreateWarehouseCredentials,
  type WarehouseResults,
  type WarehouseTables,
  type WarehouseGetAsyncQueryResultsArgs,
  type WarehouseGetAsyncQueryResults,
  WarehouseTypes,
  DimensionType,
  MetricType,
  SupportedDbtAdapter,
  WeekDay,
  WarehouseConnectionError,
  WarehouseQueryError,
  UnexpectedServerError,
  NotImplementedError,
} from '@lightdash/common';

For CommonJS:

const {
  warehouseClientFromCredentials,
  warehouseSqlBuilderFromType,
  BigqueryWarehouseClient,
  DatabricksWarehouseClient,
  PostgresClient, // Generic base class for PostgreSQL-compatible clients
  PostgresWarehouseClient,
  RedshiftWarehouseClient,
  SnowflakeWarehouseClient,
  TrinoWarehouseClient,
  SshTunnel,
  exchangeDatabricksOAuthCredentials,
  refreshDatabricksOAuthToken,
  BigqueryFieldType,
  // ClickhouseTypes - NOT exported, only accessible via factory functions
  // DatabricksTypes - NOT exported, Databricks types are internal only
  PostgresTypes,
  SnowflakeTypes,
  TrinoTypes,
  // Some types re-exported from @lightdash/common (if using TypeScript definitions)
  // WarehouseClient, WarehouseCatalog, WarehouseTableSchema, etc.
} = require('@lightdash/warehouses');

// Additional types from @lightdash/common
const {
  WarehouseTypes,
  DimensionType,
  MetricType,
  SupportedDbtAdapter,
  WeekDay,
  WarehouseConnectionError,
  WarehouseQueryError,
  UnexpectedServerError,
  NotImplementedError,
  // CreateWarehouseCredentials, WarehouseResults, WarehouseTables, etc.
} = require('@lightdash/common');

Complete Example with Error Handling

import { warehouseClientFromCredentials } from '@lightdash/warehouses';
import {
  WarehouseTypes,
  WarehouseConnectionError,
  WarehouseQueryError
} from '@lightdash/common';

async function queryWarehouse() {
  // 1. Create client with error handling
  const client = warehouseClientFromCredentials({
    type: WarehouseTypes.POSTGRES,
    host: 'localhost',
    port: 5432,
    user: 'myuser',
    password: 'mypassword',
    dbname: 'analytics',
    schema: 'public',
    sslmode: 'disable',
    timeoutSeconds: 60, // Set query timeout
  });

  try {
    // 2. Test connection (throws on failure)
    await client.test();
    console.log('✅ Connection successful');

    // 3. Stream query with error handling
    const results = [];
    let totalRows = 0;

    await client.streamQuery(
      'SELECT * FROM users WHERE active = $1 AND created_at >= $2',
      (batch) => {
        totalRows += batch.rows.length;
        results.push(...batch.rows);
        console.log(`Received batch: ${batch.rows.length} rows (total: ${totalRows})`);

        // Access field metadata
        console.log('Fields:', Object.keys(batch.fields));
        batch.fields.user_id?.type; // 'number' | 'string' | 'date' | 'timestamp' | 'boolean'
      },
      {
        values: [true, '2023-01-01'], // Positional parameters
        tags: {
          source: 'analytics_dashboard',
          user_id: '12345',
          query_context: 'user_report'
        },
        timezone: 'America/New_York',
      }
    );

    console.log(`✅ Query completed: ${totalRows} total rows`);

    // 4. Get warehouse metadata
    const tables = await client.getAllTables();
    console.log(`📊 Found ${tables.length} tables`);

    // 5. Get specific table schema
    const schema = await client.getFields('users', 'public');
    const userTable = schema[Object.keys(schema)[0]]['public']['users'];
    console.log('User table columns:', Object.keys(userTable));

  } catch (error) {
    if (error instanceof WarehouseConnectionError) {
      console.error('❌ Connection failed:', error.message);
      console.error('   Check: credentials, network, firewall, host reachability');
      // Handle connection retry logic here

    } else if (error instanceof WarehouseQueryError) {
      console.error('❌ Query failed:', error.message);
      if (error.lineNumber) {
        console.error(`   Error at line ${error.lineNumber}`);
      }
      // Handle query error (syntax, permissions, etc.)

    } else {
      console.error('❌ Unexpected error:', error);
      throw error;
    }
  }
}

queryWarehouse().catch(console.error);

Architecture

The package is built around several key components:

Unified Client Interface: WarehouseClient interface provides consistent API across all warehouse types
Warehouse-Specific Implementations: Each warehouse has a dedicated client class (e.g., BigqueryWarehouseClient, SnowflakeWarehouseClient) that handles platform-specific connection protocols and query execution
SQL Builders: Each warehouse has a corresponding SQL builder (e.g., BigquerySqlBuilder) for generating warehouse-specific SQL syntax (metric aggregations, string escaping, field quoting)
Factory Functions: warehouseClientFromCredentials() creates appropriate client based on credentials type
SSH Tunnel Support: SshTunnel class enables secure connections through SSH for PostgreSQL and Redshift
Streaming Architecture: All queries use streaming to handle large result sets efficiently
Type Safety: Full TypeScript support with generic types and strict credential typing

Capabilities Overview

Factory Functions

Create warehouse clients from credentials without needing to know the specific implementation class.

/**
 * Creates a warehouse client instance based on credentials type
 * @param credentials - Warehouse-specific credentials object
 * @returns WarehouseClient instance for the specified warehouse
 * @throws {UnexpectedServerError} If credentials type is not recognized
 */
function warehouseClientFromCredentials(
  credentials: CreateWarehouseCredentials
): WarehouseClient;

Factory Functions

Core Types and Interfaces

Core types and interfaces used across all warehouse clients.

/**
 * Unified interface for all warehouse client implementations
 * Extends WarehouseSqlBuilder, so all SQL builder methods are available on client instances
 */
interface WarehouseClient extends WarehouseSqlBuilder {
  credentials: CreateWarehouseCredentials;

  // Query execution
  streamQuery(
    query: string,
    streamCallback: (data: WarehouseResults) => void,
    options: {
      values?: AnyType[];
      tags: Record<string, string>; // REQUIRED in interface
      timezone?: string;
    }
  ): Promise<void>;

  /**
   * @deprecated Use streamQuery() instead to avoid loading all results into memory
   */
  runQuery(
    sql: string,
    tags: Record<string, string>, // REQUIRED in interface
    timezone?: string,
    values?: AnyType[]
  ): Promise<WarehouseResults>;

  executeAsyncQuery(
    args: WarehouseExecuteAsyncQueryArgs,
    resultsStreamCallback?: (
      rows: WarehouseResults['rows'],
      fields: WarehouseResults['fields']
    ) => void
  ): Promise<WarehouseExecuteAsyncQuery>;

  getAsyncQueryResults<T = Record<string, unknown>>(
    args: WarehouseGetAsyncQueryResultsArgs,
    rowFormatter?: (row: Record<string, unknown>) => T
  ): Promise<WarehouseGetAsyncQueryResults<T>>;

  // Metadata operations
  getCatalog(
    config: { database: string; schema: string; table: string }[]
  ): Promise<WarehouseCatalog>;

  getAllTables(): Promise<WarehouseTables>; // WarehouseTables includes optional partitionColumn (BigQuery only)

  getFields(
    tableName: string,
    schema?: string,
    database?: string,
    tags?: Record<string, string>
  ): Promise<WarehouseCatalog>;

  parseWarehouseCatalog(
    rows: Array<Record<string, AnyType>>,
    mapFieldType: (type: string) => DimensionType
  ): WarehouseCatalog;

  parseError(error: Error): Error;

  // Connection management
  test(): Promise<void>;

  // Inherited from WarehouseSqlBuilder:
  // getStartOfWeek(): WeekDay | null | undefined;
  // getAdapterType(): SupportedDbtAdapter;
  // getStringQuoteChar(): string;
  // getEscapeStringQuoteChar(): string;
  // getFieldQuoteChar(): string;
  // getFloatingType(): string;
  // getMetricSql(sql: string, metric: Metric): string;
  // concatString(...args: string[]): string;
  // escapeString(value: string): string;
}

type WarehouseCatalog = {
  [database: string]: {
    [schema: string]: {
      [table: string]: WarehouseTableSchema;
    };
  };
};

type WarehouseTableSchema = {
  [column: string]: DimensionType;
};

Core Types

BigQuery Client

Google Cloud BigQuery warehouse client with native async query support and OAuth integration.

class BigqueryWarehouseClient implements WarehouseClient {
  constructor(credentials: CreateBigqueryCredentials);

  streamQuery(
    query: string,
    streamCallback: (data: WarehouseResults) => void,
    options: QueryOptions
  ): Promise<void>;

  executeAsyncQuery(
    args: WarehouseExecuteAsyncQueryArgs,
    resultsStreamCallback: (rows, fields) => void
  ): Promise<WarehouseExecuteAsyncQuery>;
}

// Note: BigqueryFieldType is exported from @lightdash/warehouses
// (exported via 'export * from BigqueryWarehouseClient')

enum BigqueryFieldType {
  STRING = 'STRING',
  INTEGER = 'INTEGER',
  BYTES = 'BYTES',
  INT64 = 'INT64',
  FLOAT = 'FLOAT',
  FLOAT64 = 'FLOAT64',
  BOOLEAN = 'BOOLEAN',
  BOOL = 'BOOL',
  TIMESTAMP = 'TIMESTAMP',
  DATE = 'DATE',
  TIME = 'TIME',
  DATETIME = 'DATETIME',
  GEOGRAPHY = 'GEOGRAPHY',
  NUMERIC = 'NUMERIC',
  BIGNUMERIC = 'BIGNUMERIC',
  RECORD = 'RECORD',
  STRUCT = 'STRUCT',
  ARRAY = 'ARRAY',
}

BigQuery Client

Clickhouse Client

Clickhouse column-oriented OLAP database client with named parameter support and HTTP/HTTPS protocol.

⚠️ IMPORTANT: Clickhouse client and types are NOT directly importable. Use factory functions only:

Client: warehouseClientFromCredentials({ type: WarehouseTypes.CLICKHOUSE, ... })
SQL Builder: warehouseSqlBuilderFromType(SupportedDbtAdapter.CLICKHOUSE)

// Note: ClickhouseWarehouseClient is NOT exported from index.ts
// Only accessible via warehouseClientFromCredentials()
class ClickhouseWarehouseClient implements WarehouseClient {
  constructor(credentials: CreateClickhouseCredentials);

  streamQuery(
    query: string,
    streamCallback: (data: WarehouseResults) => void,
    options: {
      queryParams?: Record<string, AnyType>;
      tags?: Record<string, string>;
      timezone?: string;
    }
  ): Promise<void>;
}

// SQL Builder (NOT exported, only accessible via factory)
class ClickhouseSqlBuilder implements WarehouseSqlBuilder {
  readonly type = WarehouseTypes.CLICKHOUSE;
  getAdapterType(): SupportedDbtAdapter;
  getMetricSql(sql: string, metric: Metric): string;
  escapeString(value: string): string;
}

// Note: ClickhouseTypes is NOT exported - only accessible via factory functions
enum ClickhouseTypes {
  UINT8 = 'UInt8',
  UINT16 = 'UInt16',
  UINT32 = 'UInt32',
  UINT64 = 'UInt64',
  INT8 = 'Int8',
  INT16 = 'Int16',
  INT32 = 'Int32',
  INT64 = 'Int64',
  FLOAT32 = 'Float32',
  FLOAT64 = 'Float64',
  DECIMAL = 'Decimal',
  DECIMAL32 = 'Decimal32',
  DECIMAL64 = 'Decimal64',
  DECIMAL128 = 'Decimal128',
  DECIMAL256 = 'Decimal256',
  BOOL = 'Bool',
  STRING = 'String',
  FIXEDSTRING = 'FixedString',
  UUID = 'UUID',
  DATE = 'Date',
  DATE32 = 'Date32',
  DATETIME = 'DateTime',
  DATETIME64 = 'DateTime64',
  ENUM8 = 'Enum8',
  ENUM16 = 'Enum16',
  ARRAY = 'Array',
  TUPLE = 'Tuple',
  MAP = 'Map',
  NULLABLE = 'Nullable',
  LOWCARDINALITY = 'LowCardinality',
  IPV4 = 'IPv4',
  IPV6 = 'IPv6',
}

Clickhouse Client

Databricks Client

Databricks warehouse client with Unity Catalog support and multiple OAuth authentication methods.

class DatabricksWarehouseClient implements WarehouseClient {
  constructor(credentials: CreateDatabricksCredentials);

  streamQuery(
    query: string,
    streamCallback: (data: WarehouseResults) => void,
    options: QueryOptions
  ): Promise<void>;
}

// SQL Builder
class DatabricksSqlBuilder implements WarehouseSqlBuilder {
  readonly type = WarehouseTypes.DATABRICKS;
  getAdapterType(): SupportedDbtAdapter;
  getFieldQuoteChar(): string;
  getMetricSql(sql: string, metric: Metric): string;
  escapeString(value: string): string;
}

// OAuth helper functions
function exchangeDatabricksOAuthCredentials(
  host: string,
  clientId: string,
  clientSecret: string
): Promise<{ accessToken: string; refreshToken?: string }>;

function refreshDatabricksOAuthToken(
  host: string,
  clientId: string,
  refreshToken: string
): Promise<{ accessToken: string; refreshToken: string; expiresIn: number }>;

Databricks Client

PostgreSQL Client

PostgreSQL warehouse client with SSH tunnel support and streaming cursors.

// Import from @lightdash/warehouses and @lightdash/common
import {
  PostgresClient,
  PostgresWarehouseClient,
  PostgresTypes
} from '@lightdash/warehouses';
import {
  type CreatePostgresCredentials,
  type CreatePostgresLikeCredentials
} from '@lightdash/common';

// Base class for PostgreSQL-compatible databases
class PostgresClient<T extends CreatePostgresLikeCredentials> implements WarehouseClient {
  constructor(credentials: T);
}

// PostgreSQL-specific client extends the base class
class PostgresWarehouseClient extends PostgresClient<CreatePostgresCredentials> {
  constructor(credentials: CreatePostgresCredentials);
}

enum PostgresTypes {
  INTEGER = 'integer',
  INT = 'int',
  INT2 = 'int2',
  INT4 = 'int4',
  INT8 = 'int8',
  MONEY = 'money',
  SMALLSERIAL = 'smallserial',
  SERIAL = 'serial',
  SERIAL2 = 'serial2',
  SERIAL4 = 'serial4',
  SERIAL8 = 'serial8',
  BIGSERIAL = 'bigserial',
  BIGINT = 'bigint',
  SMALLINT = 'smallint',
  BOOLEAN = 'boolean',
  BOOL = 'bool',
  DATE = 'date',
  DOUBLE_PRECISION = 'double precision',
  FLOAT = 'float',
  FLOAT4 = 'float4',
  FLOAT8 = 'float8',
  JSON = 'json',
  JSONB = 'jsonb',
  NUMERIC = 'numeric',
  DECIMAL = 'decimal',
  REAL = 'real',
  CHAR = 'char',
  CHARACTER = 'character',
  NCHAR = 'nchar',
  BPCHAR = 'bpchar',
  VARCHAR = 'varchar',
  CHARACTER_VARYING = 'character varying',
  NVARCHAR = 'nvarchar',
  TEXT = 'text',
  TIME = 'time',
  TIME_TZ = 'timetz',
  TIME_WITHOUT_TIME_ZONE = 'time without time zone',
  TIMESTAMP = 'timestamp',
  TIMESTAMP_TZ = 'timestamptz',
  TIMESTAMP_WITHOUT_TIME_ZONE = 'timestamp without time zone',
}

PostgreSQL Client

Redshift Client

Amazon Redshift warehouse client with SSH tunnel support, built on PostgreSQL protocol.

// Import from @lightdash/warehouses
import { RedshiftWarehouseClient } from '@lightdash/warehouses';
import { type CreateRedshiftCredentials } from '@lightdash/common';

class RedshiftWarehouseClient implements WarehouseClient {
  constructor(credentials: CreateRedshiftCredentials);
}

Redshift Client

Snowflake Client

Snowflake warehouse client with multiple authentication methods, native async queries, and result pagination.

// Import from @lightdash/warehouses and @lightdash/common
import {
  SnowflakeWarehouseClient,
  SnowflakeTypes,
  mapFieldType,
} from '@lightdash/warehouses';
import {
  type CreateSnowflakeCredentials,
  type WarehouseClient,
  type WarehouseResults,
  type WarehouseExecuteAsyncQueryArgs,
  type WarehouseExecuteAsyncQuery,
  type WarehouseGetAsyncQueryResultsArgs,
  type WarehouseGetAsyncQueryResults,
} from '@lightdash/common';

class SnowflakeWarehouseClient implements WarehouseClient {
  constructor(credentials: CreateSnowflakeCredentials);

  streamQuery(
    query: string,
    streamCallback: (data: WarehouseResults) => void,
    options: QueryOptions
  ): Promise<void>;

  executeAsyncQuery(
    args: WarehouseExecuteAsyncQueryArgs,
    resultsStreamCallback: (rows, fields) => void
  ): Promise<WarehouseExecuteAsyncQuery>;

  getAsyncQueryResults<T>(
    args: WarehouseGetAsyncQueryResultsArgs,
    rowFormatter?: (row: Record<string, unknown>) => T
  ): Promise<WarehouseGetAsyncQueryResults<T>>;
}

enum SnowflakeTypes {
  NUMBER = 'NUMBER',
  DECIMAL = 'DECIMAL',
  NUMERIC = 'NUMERIC',
  INTEGER = 'INTEGER',
  INT = 'INT',
  BIGINT = 'BIGINT',
  SMALLINT = 'SMALLINT',
  FLOAT = 'FLOAT',
  FLOAT4 = 'FLOAT4',
  FLOAT8 = 'FLOAT8',
  DOUBLE = 'DOUBLE',
  DOUBLE_PRECISION = 'DOUBLE PRECISION',
  REAL = 'REAL',
  FIXED = 'FIXED',
  STRING = 'STRING',
  TEXT = 'TEXT',
  BOOLEAN = 'BOOLEAN',
  DATE = 'DATE',
  DATETIME = 'DATETIME',
  TIME = 'TIME',
  TIMESTAMP = 'TIMESTAMP',
  TIMESTAMP_LTZ = 'TIMESTAMP_LTZ',
  TIMESTAMP_NTZ = 'TIMESTAMP_NTZ',
  TIMESTAMP_TZ = 'TIMESTAMP_TZ',
  VARIANT = 'VARIANT',
  OBJECT = 'OBJECT',
  ARRAY = 'ARRAY',
  GEOGRAPHY = 'GEOGRAPHY',
}

Snowflake Client

Trino Client

Trino distributed SQL query engine client with BasicAuth and timezone support.

// Import from @lightdash/warehouses and @lightdash/common
import { TrinoWarehouseClient, TrinoTypes } from '@lightdash/warehouses';
import { type CreateTrinoCredentials } from '@lightdash/common';
// Note: TrinoTypes is exported from @lightdash/warehouses

class TrinoWarehouseClient implements WarehouseClient {
  constructor(credentials: CreateTrinoCredentials);

  streamQuery(
    query: string,
    streamCallback: (data: WarehouseResults) => void,
    options: QueryOptions
  ): Promise<void>;
}

enum TrinoTypes {
  BOOLEAN = 'boolean',
  TINYINT = 'tinyint',
  SMALLINT = 'smallint',
  INTEGER = 'integer',
  BIGINT = 'bigint',
  REAL = 'real',
  DOUBLE = 'double',
  DECIMAL = 'decimal',
  VARCHAR = 'varchar',
  CHAR = 'char',
  VARBINARY = 'varbinary',
  JSON = 'json',
  DATE = 'date',
  TIME = 'time',
  TIME_TZ = 'time with time zone',
  TIMESTAMP = 'timestamp',
  TIMESTAMP_TZ = 'timestamp with time zone',
  INTERVAL_YEAR_MONTH = 'interval year to month',
  INTERVAL_DAY_TIME = 'interval day to second',
  ARRAY = 'array',
  MAP = 'map',
  ROW = 'row',
  IPADDRESS = 'ipaddress',
  UUID = 'uuid',
}

Trino Client

SSH Tunnel Support

Secure SSH tunnel connections for PostgreSQL and Redshift warehouses.

// Import from @lightdash/warehouses and @lightdash/common
import { SshTunnel } from '@lightdash/warehouses';
import { type CreateWarehouseCredentials } from '@lightdash/common';

class SshTunnel<T extends CreateWarehouseCredentials> {
  constructor(credentials: T);

  connect(): Promise<T>;
  disconnect(): Promise<void>;
}

SSH Tunnel

SQL Builders

Warehouse-specific SQL builders for generating platform-specific SQL syntax.

// Import from @lightdash/warehouses and @lightdash/common
import { warehouseSqlBuilderFromType } from '@lightdash/warehouses';
import {
  SupportedDbtAdapter,
  WeekDay,
  type Metric,
} from '@lightdash/common';

/**
 * Creates a warehouse-specific SQL builder instance
 * @param adapterType - The warehouse adapter type (BIGQUERY, POSTGRES, etc.)
 * @param startOfWeek - Optional start of week for date calculations (0=Monday, 6=Sunday)
 * @returns SQL builder instance for the specified warehouse type
 */
function warehouseSqlBuilderFromType(
  adapterType: string | SupportedDbtAdapter,
  startOfWeek?: WeekDay | null
): BigquerySqlBuilder | ClickhouseSqlBuilder | DatabricksSqlBuilder | PostgresSqlBuilder | RedshiftSqlBuilder | SnowflakeSqlBuilder | TrinoSqlBuilder;

/**
 * Common SQL builder interface (not exported - base class for all SQL builders)
 * All SQL builders implement these methods
 */
interface WarehouseSqlBuilder {
  getStartOfWeek(): WeekDay | null | undefined;
  getAdapterType(): SupportedDbtAdapter;
  getStringQuoteChar(): string;
  getEscapeStringQuoteChar(): string;
  getFieldQuoteChar(): string;
  getFloatingType(): string;
  getMetricSql(sql: string, metric: Metric): string;
  concatString(...args: string[]): string;
  escapeString(value: string): string;
}

SQL Builders

Performance Considerations

Query Timeouts

Set appropriate timeouts to prevent queries from running indefinitely:

const client = warehouseClientFromCredentials({
  type: WarehouseTypes.POSTGRES,
  timeoutSeconds: 300, // 5 minutes
  // ... other credentials
});

Default Timeouts:

PostgreSQL/Redshift: 5 minutes (300 seconds)
BigQuery: Configurable via timeoutSeconds
Snowflake: Warehouse-level timeout settings
Databricks: 5 minutes when DATABRICKS_ENABLE_TIMEOUTS is set

Batch Size Recommendations

When streaming large result sets, adjust batch size based on warehouse:

Warehouse	Default Batch Size	Recommendation
PostgreSQL	1000 rows	Increase for large numeric datasets
BigQuery	10000 rows	Good default for most cases
Snowflake	10000 rows	Increase for wide tables

Connection Pooling

PostgreSQL and Redshift use connection pooling automatically:

const credentials = {
  type: WarehouseTypes.POSTGRES,
  keepalivesIdle: 30, // TCP keepalive interval
  threads: 4, // Number of concurrent threads
  // ...
};

Troubleshooting Guide

Connection Errors

Problem: Connection timeout or refused

WarehouseConnectionError: Connection timeout

Solutions:

Verify host and port are correct
Check firewall rules
Verify network connectivity
For PostgreSQL/Redshift: Check if SSH tunnel is needed
For cloud warehouses: Verify API endpoints are accessible

Authentication Errors

Problem: Invalid credentials

WarehouseConnectionError: Authentication failed

Solutions:

Verify username and password
For BigQuery: Check service account JSON is valid
For Snowflake: Verify account identifier format
For Databricks: Verify token or OAuth credentials
Check if credentials have expired

Query Errors

Problem: Query syntax error

WarehouseQueryError: Syntax error at line 5

Solutions:

Check SQL syntax for warehouse-specific dialect
Verify table/column names exist
Check parameter placeholders match warehouse type
Verify permissions for queried tables

Performance Issues

Problem: Slow queries

Solutions:

Use streamQuery() instead of deprecated runQuery()
Add appropriate indexes on queried columns
Limit result sets with WHERE clauses
Use warehouse-specific optimizations (partitioning, clustering)
Monitor query execution plans

Memory Issues

Problem: Out of memory errors

Solutions:

Always use streamQuery() for large result sets
Process batches incrementally instead of accumulating
Increase Node.js heap size if necessary
Consider pagination for very large datasets (Snowflake)

Install with Tessl CLI

npx tessl i tessl/npm-lightdash--warehouses

docs

tessl/npm-lightdash--warehouses

index.md.css-3qkkll{font-size:var(--chakra-font-sizes-sm);font-weight:var(--chakra-font-weights-normal);color:var(--chakra-colors-gray-300);}docs/

Lightdash Warehouses

Quick Start

Package Information

Choosing the Right Warehouse

Critical Information for Agents

NOT Exported Types and Classes

Deprecated Methods

Parameter Types by Warehouse

Async Query Support

Common Pitfalls

1. Column Name Casing

2. SSH Tunnel Setup

3. Required Callback for executeAsyncQuery

4. Metric SQL Requirements

5. Error Handling

Core Imports

Complete Example with Error Handling

Architecture

Capabilities Overview

Factory Functions

Core Types and Interfaces

BigQuery Client

Clickhouse Client

Databricks Client

PostgreSQL Client

Redshift Client

Snowflake Client

Trino Client

SSH Tunnel Support

SQL Builders

Performance Considerations

Query Timeouts

Batch Size Recommendations

Connection Pooling

Troubleshooting Guide

Connection Errors

Authentication Errors

Query Errors

Performance Issues

Memory Issues

index.mddocs/