or run

npx @tessl/cli init

Version

Tile

Overview

Evals

Files

docs

guides

authorization.md building-queries.md compilation.md visualization.md working-with-data.md

quick-reference

common-apis.md imports.md types-at-a-glance.md

reference

architecture.md getting-started-full.md

index.md

tile.json

SQL Runner

SQL Runner provides a flexible interface for executing custom SQL queries with full visualization support, custom metrics, time granularity controls, filtering, sorting, and pivoting capabilities. It enables users to work with raw SQL while maintaining access to Lightdash's rich data transformation and visualization features.

Overview

The SQL Runner module enables direct SQL query execution with:

Custom SQL query building with dimensions, metrics, and time dimensions
Advanced filtering with string, time, and nested filter support
Multi-field sorting and result limiting
Pivot table generation for cross-tabulation
Saved SQL charts with visualization configurations
Virtual view creation for exposing SQL results as explorable datasets
GitHub integration for dbt model generation from SQL queries
Asynchronous job execution with status tracking

Capabilities

SQL Runner Query Building

Build and execute custom SQL queries with dimensions, time dimensions, metrics, filters, sorts, and pivot configurations.

import {
  type SqlRunnerQuery,
  type SqlRunnerBody,
  type SqlRunnerPayload,
} from '@lightdash/common';

/**
 * SQL Runner query configuration supporting custom SQL with advanced features
 *
 * Note: SqlRunnerTimeDimension and SqlRunnerPivot are internal types.
 * Use inline type definitions for timeDimensions and pivot properties.
 */
interface SqlRunnerQuery {
  /** Selected dimension fields */
  dimensions: Pick<SqlRunnerField, 'name'>[];
  /** Time-based dimensions with optional granularity (use string literals like 'DAY', 'MONTH') */
  timeDimensions: { name: string; granularity?: SqlRunnerTimeGranularityValue }[];
  /** Selected metric fields */
  metrics: Pick<SqlRunnerField, 'name'>[];
  /** Sort configuration */
  sortBy: SqlRunnerSortBy[];
  /** Result limit */
  limit?: number;
  /** Timezone for time-based operations */
  timezone?: string;
  /** Pivot configuration for cross-tabulation */
  pivot?: {
    pivotDimensions?: string[];
    values?: string[];
  };
  /** Filter conditions */
  filters: SqlRunnerFilter[];
  /** Raw SQL query */
  sql?: string;
  /** Custom metrics with aggregation */
  customMetrics?: (Pick<SqlRunnerField, 'name' | 'aggType'> & {
    baseDimension?: string;
  })[];
}

/**
 * Payload for executing SQL Runner queries
 */
type SqlRunnerPayload = TraceTaskBase & {
  sqlChartUuid?: string;
  context: QueryExecutionContext;
} & SqlRunnerBody;

/**
 * Basic SQL Runner request body
 */
interface SqlRunnerBody {
  /** SQL query to execute */
  sql: string;
  /** Maximum number of rows to return */
  limit?: number;
}

Usage Example:

import {
  type SqlRunnerQuery,
  type SqlRunnerBody,
  SqlRunnerFieldType,
  SqlRunnerFilterBaseOperator,
  SqlRunnerFilterRelativeTimeValue,
  SortByDirection,
  FieldType,
} from '@lightdash/common';

// Simple SQL execution
const basicQuery: SqlRunnerBody = {
  sql: 'SELECT customer_id, order_date, total FROM orders',
  limit: 100,
};

// Advanced query with aggregations and time granularity
const advancedQuery: SqlRunnerQuery = {
  dimensions: [{ name: 'customer_id' }],
  timeDimensions: [
    {
      name: 'order_date',
      granularity: 'DAY'
    }
  ],
  metrics: [{ name: 'total_revenue' }],
  sortBy: [
    {
      name: 'order_date',
      kind: FieldType.DIMENSION,
      direction: SortByDirection.DESC,
    }
  ],
  filters: [
    {
      uuid: 'filter_1',
      fieldRef: 'order_date',
      fieldKind: FieldType.DIMENSION,
      fieldType: SqlRunnerFieldType.TIME,
      operator: SqlRunnerFilterBaseOperator.IS,
      values: { relativeTime: SqlRunnerFilterRelativeTimeValue.LAST_30_DAYS },
    }
  ],
  limit: 500,
  timezone: 'UTC',
};

// Query with custom metrics
const customMetricQuery: SqlRunnerQuery = {
  dimensions: [{ name: 'product_category' }],
  timeDimensions: [],
  metrics: [],
  customMetrics: [
    {
      name: 'avg_order_value',
      aggType: VizAggregationOptions.AVERAGE,
      baseDimension: 'order_total',
    },
    {
      name: 'total_orders',
      aggType: VizAggregationOptions.COUNT,
    }
  ],
  sortBy: [],
  filters: [],
};

SQL Runner Field Types

Field type system for SQL Runner columns with metadata and capabilities.

import {
  SqlRunnerFieldType,
  type SqlRunnerField,
} from '@lightdash/common';

/**
 * Supported field types in SQL Runner
 */
enum SqlRunnerFieldType {
  TIME = 'time',
  NUMBER = 'number',
  STRING = 'string',
  BOOLEAN = 'boolean',
}

/**
 * Field definition with metadata and available operations
 */
interface SqlRunnerField {
  /** Internal field name */
  name: string;
  /** Display label */
  label: string;
  /** Field data type */
  type: SqlRunnerFieldType;
  /** Field kind (dimension or metric) */
  kind: FieldType;
  /** Optional description */
  description?: string;
  /** Visibility flag */
  visible: boolean;
  /** Aggregation type for metrics */
  aggType?: VizAggregationOptions;
  /** Available time granularities for time fields */
  availableGranularities: SqlRunnerTimeGranularity[];
  /** Available filter operators for this field */
  availableOperators: SqlRunnerFilter['operator'][];
}

/**
 * Note: SqlRunnerTimeDimension is an internal type not exported from the package.
 * Time dimensions in SqlRunnerQuery use inline type: { name: string; granularity?: SqlRunnerTimeGranularityValue }
 * Use string literals for granularity: 'DAY', 'WEEK', 'MONTH', 'QUARTER', 'YEAR', etc.
 */

Usage Example:

import {
  SqlRunnerFieldType,
  type SqlRunnerField,
  type SqlRunnerTimeDimension,
  FieldType,
} from '@lightdash/common';

// String dimension field
const statusField: SqlRunnerField = {
  name: 'status',
  label: 'Order Status',
  type: SqlRunnerFieldType.STRING,
  kind: FieldType.DIMENSION,
  description: 'Current status of the order',
  visible: true,
  availableGranularities: [],
  availableOperators: ['IS', 'IS_NOT'],
};

// Number metric field
const revenueField: SqlRunnerField = {
  name: 'total_revenue',
  label: 'Total Revenue',
  type: SqlRunnerFieldType.NUMBER,
  kind: FieldType.METRIC,
  visible: true,
  aggType: VizAggregationOptions.SUM,
  availableGranularities: [],
  availableOperators: ['IS', 'IS_NOT'],
};

// Time dimension with granularity (using string literals)
const timeDimension: SqlRunnerTimeDimension = {
  name: 'created_at',
  label: 'Created Date',
  type: SqlRunnerFieldType.TIME,
  kind: FieldType.DIMENSION,
  visible: true,
  granularity: 'MONTH',
  availableGranularities: [
    'DAY',
    'WEEK',
    'MONTH',
    'QUARTER',
    'YEAR',
  ],
  availableOperators: ['IS', 'IS_NOT'],
};

Time Granularity

Time-based grouping options for SQL Runner time dimensions.

Note: SqlRunnerTimeGranularity is an internal enum that is NOT exported from the package. Use string literals directly in your code.

/**
 * Time granularity levels for time dimension grouping (internal type, not exported)
 *
 * Valid string values (from finest to coarsest granularity):
 */
type SqlRunnerTimeGranularityValue =
  | 'NANOSECOND'
  | 'MICROSECOND'
  | 'MILLISECOND'
  | 'SECOND'
  | 'MINUTE'
  | 'HOUR'
  | 'DAY'
  | 'WEEK'
  | 'MONTH'
  | 'QUARTER'
  | 'YEAR';

Usage Example:

import {
  type SqlRunnerTimeDimension,
  SqlRunnerFieldType,
  FieldType,
} from '@lightdash/common';

// Time dimension grouped by month (using string literal)
const monthlyDimension: SqlRunnerTimeDimension = {
  name: 'created_at',
  label: 'Created Date',
  type: SqlRunnerFieldType.TIME,
  kind: FieldType.DIMENSION,
  visible: true,
  granularity: 'MONTH',
  availableGranularities: [
    'DAY',
    'WEEK',
    'MONTH',
    'QUARTER',
    'YEAR',
  ],
  availableOperators: ['IS', 'IS_NOT'],
};

// Time dimension grouped by week
const weeklyDimension: SqlRunnerTimeDimension = {
  name: 'order_date',
  label: 'Order Date',
  type: SqlRunnerFieldType.TIME,
  kind: FieldType.DIMENSION,
  visible: true,
  granularity: 'WEEK',
  availableGranularities: [
    'DAY',
    'WEEK',
    'MONTH',
  ],
  availableOperators: ['IS', 'IS_NOT'],
};

SQL Runner Filters

Comprehensive filter system for SQL Runner queries with string and time filter support.

import {
  SqlRunnerFilterBaseOperator,
  SqlRunnerFilterRelativeTimeValue,
  type SqlRunnerFilterBase,
  type SqlRunnerStringFilter,
  type SqlRunnerExactTimeFilter,
  type SqlRunnerRelativeTimeFilter,
  type SqlRunnerTimeFilter,
  type SqlRunnerFilter,
} from '@lightdash/common';

/**
 * Base filter operators
 */
enum SqlRunnerFilterBaseOperator {
  IS = 'IS',
  IS_NOT = 'IS_NOT',
}

/**
 * Relative time filter values for quick date ranges
 */
enum SqlRunnerFilterRelativeTimeValue {
  TODAY = 'TODAY',
  YESTERDAY = 'YESTERDAY',
  LAST_7_DAYS = 'LAST_7_DAYS',
  LAST_30_DAYS = 'LAST_30_DAYS',
}

/**
 * Base filter properties shared by all filter types
 */
interface SqlRunnerFilterBase {
  /** Unique filter identifier */
  uuid: string;
  /** Field reference being filtered */
  fieldRef: string;
  /** Field kind for UI state management */
  fieldKind: FieldType;
  /** Field data type */
  fieldType: SqlRunnerFieldType;
}

/**
 * String field filter
 */
interface SqlRunnerStringFilter extends SqlRunnerFilterBase {
  fieldType: SqlRunnerFieldType.STRING;
  operator: SqlRunnerFilterBaseOperator;
  /** Filter values */
  values: string[];
}

/**
 * Exact time filter with specific datetime
 */
interface SqlRunnerExactTimeFilter extends SqlRunnerFilterBase {
  fieldType: SqlRunnerFieldType.TIME;
  operator: SqlRunnerFilterBaseOperator;
  /** Exact time value in ISO 8601 format */
  values: { time: string };
}

/**
 * Relative time filter with predefined ranges
 */
interface SqlRunnerRelativeTimeFilter extends SqlRunnerFilterBase {
  fieldType: SqlRunnerFieldType.TIME;
  operator: SqlRunnerFilterBaseOperator;
  /** Relative time range */
  values: { relativeTime: SqlRunnerFilterRelativeTimeValue };
}

/**
 * Combined time filter type
 */
type SqlRunnerTimeFilter =
  | SqlRunnerExactTimeFilter
  | SqlRunnerRelativeTimeFilter;

/**
 * Filter with support for nested AND/OR groups
 */
type SqlRunnerFilter = (SqlRunnerStringFilter | SqlRunnerTimeFilter) & {
  /** Nested AND conditions */
  and?: SqlRunnerFilter[];
  /** Nested OR conditions */
  or?: SqlRunnerFilter[];
};

Usage Example:

import {
  SqlRunnerFilterBaseOperator,
  SqlRunnerFilterRelativeTimeValue,
  SqlRunnerFieldType,
  type SqlRunnerFilter,
  FieldType,
} from '@lightdash/common';

// String filter with multiple values
const statusFilter: SqlRunnerFilter = {
  uuid: 'filter_1',
  fieldRef: 'status',
  fieldKind: FieldType.DIMENSION,
  fieldType: SqlRunnerFieldType.STRING,
  operator: SqlRunnerFilterBaseOperator.IS,
  values: ['active', 'pending'],
};

// Relative time filter (last 30 days)
const dateFilter: SqlRunnerFilter = {
  uuid: 'filter_2',
  fieldRef: 'created_at',
  fieldKind: FieldType.DIMENSION,
  fieldType: SqlRunnerFieldType.TIME,
  operator: SqlRunnerFilterBaseOperator.IS,
  values: { relativeTime: SqlRunnerFilterRelativeTimeValue.LAST_30_DAYS },
};

// Exact time filter
const exactDateFilter: SqlRunnerFilter = {
  uuid: 'filter_3',
  fieldRef: 'order_date',
  fieldKind: FieldType.DIMENSION,
  fieldType: SqlRunnerFieldType.TIME,
  operator: SqlRunnerFilterBaseOperator.IS,
  values: { time: '2024-01-15T00:00:00Z' },
};

// Nested filter group with AND logic
const complexFilter: SqlRunnerFilter = {
  uuid: 'filter_4',
  fieldRef: 'status',
  fieldKind: FieldType.DIMENSION,
  fieldType: SqlRunnerFieldType.STRING,
  operator: SqlRunnerFilterBaseOperator.IS,
  values: ['active'],
  and: [
    {
      uuid: 'filter_5',
      fieldRef: 'amount',
      fieldKind: FieldType.METRIC,
      fieldType: SqlRunnerFieldType.NUMBER,
      operator: SqlRunnerFilterBaseOperator.IS,
      values: ['100'],
    }
  ],
};

// Complex nested filter with OR logic
const advancedFilter: SqlRunnerFilter = {
  uuid: 'filter_6',
  fieldRef: 'category',
  fieldKind: FieldType.DIMENSION,
  fieldType: SqlRunnerFieldType.STRING,
  operator: SqlRunnerFilterBaseOperator.IS,
  values: ['electronics'],
  or: [
    {
      uuid: 'filter_7',
      fieldRef: 'price',
      fieldKind: FieldType.METRIC,
      fieldType: SqlRunnerFieldType.NUMBER,
      operator: SqlRunnerFilterBaseOperator.IS,
      values: ['500'],
    }
  ],
};

// Exclude filter with IS_NOT operator
const excludeFilter: SqlRunnerFilter = {
  uuid: 'filter_8',
  fieldRef: 'status',
  fieldKind: FieldType.DIMENSION,
  fieldType: SqlRunnerFieldType.STRING,
  operator: SqlRunnerFilterBaseOperator.IS_NOT,
  values: ['cancelled', 'refunded'],
};

SQL Runner Sorting

Sorting configuration for SQL Runner results.

import {
  type SqlRunnerSortBy,
  SortByDirection,
} from '@lightdash/common';

/**
 * Sort configuration for SQL Runner queries
 */
interface SqlRunnerSortBy extends Pick<SqlRunnerField, 'name' | 'kind'> {
  /** Sort direction */
  direction: SortByDirection;
}

/**
 * Sort direction options
 */
enum SortByDirection {
  ASC = 'ASC',
  DESC = 'DESC',
}

Usage Example:

import {
  type SqlRunnerSortBy,
  SortByDirection,
  FieldType,
} from '@lightdash/common';

// Sort by single field descending
const singleSort: SqlRunnerSortBy = {
  name: 'total_revenue',
  kind: FieldType.METRIC,
  direction: SortByDirection.DESC,
};

// Multiple sort fields
const multiSort: SqlRunnerSortBy[] = [
  {
    name: 'order_date',
    kind: FieldType.DIMENSION,
    direction: SortByDirection.DESC,
  },
  {
    name: 'customer_name',
    kind: FieldType.DIMENSION,
    direction: SortByDirection.ASC,
  },
];

SQL Runner Pivot

Pivot configuration for creating cross-tabulated views of SQL Runner results.

import {
  type SqlRunnerPivot,
  type SqlRunnerPivotQueryPayload,
  type SqlRunnerPivotQueryBody,
  type ValuesColumn,
  type GroupByColumn,
  type SortBy,
} from '@lightdash/common';

/**
 * Pivot configuration for SQL Runner
 */
type SqlRunnerPivot = {
  /** Fields to pivot on (column headers) */
  on: string[];
  /** Fields to use as row indices */
  index: string[];
  /** Fields to aggregate as values */
  values: string[];
};

/**
 * Payload for pivot query execution
 */
type SqlRunnerPivotQueryPayload = SqlRunnerPayload &
  ApiSqlRunnerPivotQueryPayload;

type ApiSqlRunnerPivotQueryPayload = PivotConfiguration & {
  savedSqlUuid?: string;
};

/**
 * Body for pivot query requests
 */
type SqlRunnerPivotQueryBody = SqlRunnerBody &
  ApiSqlRunnerPivotQueryPayload;

/**
 * Values column with aggregation
 */
type ValuesColumn = {
  reference: string;
  aggregation: VizAggregationOptions;
};

/**
 * Group by column reference
 */
type GroupByColumn = {
  reference: string;
};

/**
 * Sort configuration alias from PivotChartLayout
 */
type SortBy = PivotChartLayout['sortBy'];

Usage Example:

import {
  type SqlRunnerPivot,
  type SqlRunnerPivotQueryBody,
  type ValuesColumn,
  VizAggregationOptions,
} from '@lightdash/common';

// Simple pivot configuration
const pivotConfig: SqlRunnerPivot = {
  index: ['product_category'],
  on: ['month'],
  values: ['total_revenue', 'order_count'],
};

// Advanced pivot query body
const pivotQuery: SqlRunnerPivotQueryBody = {
  sql: 'SELECT category, month, revenue, orders FROM sales',
  limit: 1000,
  savedSqlUuid: 'abc-123',
  indexColumn: {
    reference: 'category',
    type: VizIndexType.CATEGORY,
  },
  valuesColumns: [
    {
      reference: 'revenue',
      aggregation: VizAggregationOptions.SUM,
    },
    {
      reference: 'orders',
      aggregation: VizAggregationOptions.COUNT,
    }
  ],
  groupByColumns: [
    { reference: 'month' }
  ],
  sortBy: [
    {
      reference: 'revenue',
      direction: SortByDirection.DESC,
    }
  ],
};

// Values columns with different aggregations
const valueColumns: ValuesColumn[] = [
  {
    reference: 'price',
    aggregation: VizAggregationOptions.AVERAGE,
  },
  {
    reference: 'quantity',
    aggregation: VizAggregationOptions.SUM,
  },
  {
    reference: 'order_id',
    aggregation: VizAggregationOptions.COUNT,
  },
];

SQL Charts

Saved SQL queries with visualization configurations.

import {
  type SqlChart,
  type CreateSqlChart,
  type UpdateSqlChart,
  type UpdateUnversionedSqlChart,
  type UpdateVersionedSqlChart,
} from '@lightdash/common';

/**
 * Saved SQL chart with configuration and metadata
 */
interface SqlChart {
  /** Unique identifier */
  savedSqlUuid: string;
  /** Chart name */
  name: string;
  /** Optional description */
  description: string | null;
  /** URL-friendly slug */
  slug: string;
  /** SQL query */
  sql: string;
  /** Result limit */
  limit: number;
  /** Visualization configuration */
  config: VizBaseConfig &
    (VizCartesianChartConfig | VizPieChartConfig | VizTableConfig);
  /** Chart type */
  chartKind: ChartKind;
  /** Creation timestamp */
  createdAt: Date;
  /** Creator user */
  createdBy: Pick<LightdashUser, 'userUuid' | 'firstName' | 'lastName'> | null;
  /** Last update timestamp */
  lastUpdatedAt: Date;
  /** Last updater user */
  lastUpdatedBy: Pick<LightdashUser, 'userUuid' | 'firstName' | 'lastName'> | null;
  /** Parent space */
  space: Pick<SpaceSummary, 'uuid' | 'name' | 'isPrivate' | 'userAccess'>;
  /** Parent dashboard if any */
  dashboard: Pick<Dashboard, 'uuid' | 'name'> | null;
  /** Parent project */
  project: Pick<Project, 'projectUuid'>;
  /** Parent organization */
  organization: Pick<Organization, 'organizationUuid'>;
  /** View count */
  views: number;
  /** First viewed timestamp */
  firstViewedAt: Date;
  /** Last viewed timestamp */
  lastViewedAt: Date;
}

/**
 * Create new SQL chart
 */
interface CreateSqlChart {
  /** Chart name */
  name: string;
  /** Optional description */
  description: string | null;
  /** SQL query */
  sql: string;
  /** Result limit */
  limit: number;
  /** Visualization configuration */
  config: AllVizChartConfig;
  /** Space UUID to save in */
  spaceUuid: string;
}

/**
 * Update unversioned chart properties (name, description, space)
 *
 * These properties don't create new chart versions when updated
 */
interface UpdateUnversionedSqlChart {
  name: string;
  description: string | null;
  spaceUuid: string;
}

/**
 * Update versioned chart properties (SQL, config)
 *
 * These properties create new chart versions when updated
 */
interface UpdateVersionedSqlChart {
  sql: string;
  limit: number;
  config: AllVizChartConfig;
}

/**
 * Update SQL chart with optional versioned/unversioned changes
 */
interface UpdateSqlChart {
  unversionedData?: UpdateUnversionedSqlChart;
  versionedData?: UpdateVersionedSqlChart;
}

Usage Example:

import {
  type CreateSqlChart,
  type UpdateSqlChart,
  ChartKind,
} from '@lightdash/common';

// Create a new SQL chart with bar visualization
const newChart: CreateSqlChart = {
  name: 'Monthly Revenue',
  description: 'Revenue breakdown by month',
  sql: 'SELECT month, SUM(revenue) as total_revenue FROM sales GROUP BY month',
  limit: 500,
  config: {
    metadata: { version: 1 },
    type: ChartKind.VERTICAL_BAR,
    fieldConfig: {
      x: { reference: 'month', type: VizIndexType.TIME },
      y: [{ reference: 'total_revenue', aggregation: VizAggregationOptions.SUM }],
      groupBy: undefined,
    },
    display: undefined,
  },
  spaceUuid: 'space-123',
};

// Update chart name and description (unversioned)
const updateUnversioned: UpdateSqlChart = {
  unversionedData: {
    name: 'Monthly Revenue (Updated)',
    description: 'Updated revenue breakdown by month',
    spaceUuid: 'space-123',
  },
};

// Update chart SQL and config (versioned - creates new version)
const updateVersioned: UpdateSqlChart = {
  versionedData: {
    sql: 'SELECT month, SUM(revenue) as total_revenue FROM sales WHERE year = 2024 GROUP BY month',
    limit: 1000,
    config: {
      metadata: { version: 1 },
      type: ChartKind.LINE,
      fieldConfig: {
        x: { reference: 'month', type: VizIndexType.TIME },
        y: [{ reference: 'total_revenue', aggregation: VizAggregationOptions.SUM }],
        groupBy: undefined,
      },
      display: undefined,
    },
  },
};

// Update both versioned and unversioned properties
const fullUpdate: UpdateSqlChart = {
  unversionedData: {
    name: 'Yearly Revenue Trend',
    description: 'Revenue trend analysis',
    spaceUuid: 'space-456',
  },
  versionedData: {
    sql: 'SELECT year, SUM(revenue) as total FROM sales GROUP BY year',
    limit: 100,
    config: {
      metadata: { version: 1 },
      type: ChartKind.LINE,
      fieldConfig: {
        x: { reference: 'year', type: VizIndexType.CATEGORY },
        y: [{ reference: 'total', aggregation: VizAggregationOptions.SUM }],
        groupBy: undefined,
      },
      display: undefined,
    },
  },
};

SQL Runner Job Responses

Response types for SQL Runner job execution and status polling.

import {
  type SqlRunnerJobStatusSuccessDetails,
  type SqlRunnerPivotQueryJobStatusSuccessDetails,
  type SqlRunnerJobStatusErrorDetails,
  type ApiSqlRunnerJobStatusResponse,
  type ApiSqlRunnerJobSuccessResponse,
  type ApiSqlRunnerJobPivotQuerySuccessResponse,
  isErrorDetails,
  isApiSqlRunnerJobSuccessResponse,
  isApiSqlRunnerJobErrorResponse,
  isApiSqlRunnerJobPivotQuerySuccessResponse,
} from '@lightdash/common';

/**
 * Success details for completed SQL Runner job
 */
type SqlRunnerJobStatusSuccessDetails = {
  /** URL to result file */
  fileUrl: string;
  /** Column definitions */
  columns: VizColumn[];
};

/**
 * Success details for pivot query jobs
 */
type SqlRunnerPivotQueryJobStatusSuccessDetails =
  SqlRunnerJobStatusSuccessDetails & Omit<PivotChartData, 'results'>;

/**
 * Error details for failed SQL Runner jobs
 */
type SqlRunnerJobStatusErrorDetails = {
  /** Error message */
  error: string;
  /** Character position of error in SQL */
  charNumber?: number;
  /** Line number of error in SQL */
  lineNumber?: number;
  /** User who created the job */
  createdByUserUuid: string;
};

/**
 * Type guard for error details
 */
export function isErrorDetails(
  results?: ApiSqlRunnerJobStatusResponse['results']['details']
): results is SqlRunnerJobStatusErrorDetails;

/**
 * SQL Runner job status response
 */
interface ApiSqlRunnerJobStatusResponse {
  status: 'ok';
  results: {
    status: SchedulerJobStatus;
    details:
      | SqlRunnerJobStatusSuccessDetails
      | SqlRunnerJobStatusErrorDetails;
  };
}

/**
 * Successful SQL Runner job response
 */
interface ApiSqlRunnerJobSuccessResponse extends ApiSqlRunnerJobStatusResponse {
  results: {
    status: SchedulerJobStatus.COMPLETED;
    details: SqlRunnerJobStatusSuccessDetails;
  };
}

/**
 * Type guard for successful job response
 */
export function isApiSqlRunnerJobSuccessResponse(
  response: ApiSqlRunnerJobStatusResponse['results'] | ApiError
): response is ApiSqlRunnerJobSuccessResponse['results'];

/**
 * Type guard for error job response
 */
export function isApiSqlRunnerJobErrorResponse(
  response: ApiSqlRunnerJobStatusResponse['results'] | ApiError
): response is ApiError;

/**
 * Successful pivot query job response
 */
interface ApiSqlRunnerJobPivotQuerySuccessResponse {
  results: {
    status: SchedulerJobStatus.COMPLETED;
    details: SqlRunnerPivotQueryJobStatusSuccessDetails;
  };
}

/**
 * Type guard for successful pivot query response
 */
export function isApiSqlRunnerJobPivotQuerySuccessResponse(
  response: ApiSqlRunnerJobStatusResponse['results'] | ApiError
): response is ApiSqlRunnerJobPivotQuerySuccessResponse['results'];

Usage Example:

import {
  type ApiSqlRunnerJobStatusResponse,
  isApiSqlRunnerJobSuccessResponse,
  isApiSqlRunnerJobErrorResponse,
  isErrorDetails,
  SchedulerJobStatus,
} from '@lightdash/common';

// Poll job status
async function pollJobStatus(jobId: string): Promise<void> {
  const response = await fetch(`/api/v1/sql-runner/jobs/${jobId}/status`);
  const data: ApiSqlRunnerJobStatusResponse = await response.json();

  // Check job status
  if (data.results.status === SchedulerJobStatus.COMPLETED) {
    if (isApiSqlRunnerJobSuccessResponse(data.results)) {
      console.log('Job completed successfully');
      console.log('File URL:', data.results.details.fileUrl);
      console.log('Columns:', data.results.details.columns);
    }
  } else if (data.results.status === SchedulerJobStatus.ERROR) {
    if (isErrorDetails(data.results.details)) {
      console.error('Job failed:', data.results.details.error);
      if (data.results.details.lineNumber) {
        console.error(`Error at line ${data.results.details.lineNumber}`);
      }
    }
  } else if (data.results.status === SchedulerJobStatus.STARTED) {
    console.log('Job is running...');
    // Continue polling
  }
}

// Handle pivot query response
async function handlePivotQuery(
  response: ApiSqlRunnerJobStatusResponse['results']
): Promise<void> {
  if (isApiSqlRunnerJobPivotQuerySuccessResponse(response)) {
    console.log('Pivot query completed');
    console.log('Index column:', response.details.indexColumn);
    console.log('Values columns:', response.details.valuesColumns);
    console.log('File URL:', response.details.fileUrl);
  }
}

SQL Runner Results

Result types for SQL Runner query execution.

import {
  type SqlRunnerResults,
  type ApiSqlChart,
  type ApiCreateSqlChart,
  type ApiUpdateSqlChart,
} from '@lightdash/common';

/**
 * Raw result rows from SQL Runner execution
 */
type SqlRunnerResults = RawResultRow[];

/**
 * API response for SQL chart
 */
interface ApiSqlChart {
  status: 'ok';
  results: SqlChart;
}

/**
 * API response for creating SQL chart
 */
interface ApiCreateSqlChart {
  status: 'ok';
  results: {
    savedSqlUuid: string;
    slug: string;
  };
}

/**
 * API response for updating SQL chart
 */
interface ApiUpdateSqlChart {
  status: 'ok';
  results: {
    savedSqlUuid: string;
    savedSqlVersionUuid: string | null;
  };
}

Usage Example:

import {
  type SqlRunnerResults,
  type ApiCreateSqlChart,
  type ApiUpdateSqlChart,
  type RawResultRow,
} from '@lightdash/common';

// Process SQL Runner results
function processResults(results: SqlRunnerResults): void {
  results.forEach((row: RawResultRow) => {
    console.log('Row data:', row);
  });
}

// Create SQL chart via API
async function createChart(chart: CreateSqlChart): Promise<string> {
  const response = await fetch('/api/v1/sql-charts', {
    method: 'POST',
    body: JSON.stringify(chart),
  });

  const data: ApiCreateSqlChart = await response.json();
  console.log('Created chart:', data.results.savedSqlUuid);
  console.log('Chart URL:', `/charts/${data.results.slug}`);

  return data.results.savedSqlUuid;
}

// Update SQL chart via API
async function updateChart(
  chartUuid: string,
  updates: UpdateSqlChart
): Promise<void> {
  const response = await fetch(`/api/v1/sql-charts/${chartUuid}`, {
    method: 'PATCH',
    body: JSON.stringify(updates),
  });

  const data: ApiUpdateSqlChart = await response.json();

  if (data.results.savedSqlVersionUuid) {
    console.log('New version created:', data.results.savedSqlVersionUuid);
  } else {
    console.log('Chart updated without version change');
  }
}

Virtual Views

Create virtual views from SQL queries to expose them as explorable datasets.

import {
  type CreateVirtualViewPayload,
  type UpdateVirtualViewPayload,
  type ApiCreateVirtualView,
} from '@lightdash/common';

/**
 * Payload for creating a virtual view from SQL
 */
type CreateVirtualViewPayload = {
  /** Virtual view name */
  name: string;
  /** SQL query defining the view */
  sql: string;
  /** Column definitions from query results */
  columns: VizColumn[];
};

/**
 * Payload for updating a virtual view
 */
type UpdateVirtualViewPayload = CreateVirtualViewPayload;

/**
 * API response for creating virtual view
 */
interface ApiCreateVirtualView {
  status: 'ok';
  results: Pick<Explore, 'name'>;
}

Usage Example:

import {
  type CreateVirtualViewPayload,
  type UpdateVirtualViewPayload,
  type ApiCreateVirtualView,
  DimensionType,
} from '@lightdash/common';

// Create a virtual view from SQL query
const virtualView: CreateVirtualViewPayload = {
  name: 'customer_metrics',
  sql: `
    SELECT
      customer_id,
      customer_name,
      SUM(order_total) as total_spent,
      COUNT(*) as order_count
    FROM orders
    GROUP BY customer_id, customer_name
  `,
  columns: [
    {
      reference: 'customer_id',
      type: DimensionType.STRING,
    },
    {
      reference: 'customer_name',
      type: DimensionType.STRING,
    },
    {
      reference: 'total_spent',
      type: DimensionType.NUMBER,
    },
    {
      reference: 'order_count',
      type: DimensionType.NUMBER,
    },
  ],
};

// Create virtual view via API
async function createVirtualView(
  projectUuid: string,
  payload: CreateVirtualViewPayload
): Promise<string> {
  const response = await fetch(
    `/api/v1/projects/${projectUuid}/virtual-view`,
    {
      method: 'POST',
      body: JSON.stringify(payload),
    }
  );

  const data: ApiCreateVirtualView = await response.json();
  console.log('Created virtual view:', data.results.name);

  return data.results.name;
}

// Update existing virtual view
async function updateVirtualView(
  projectUuid: string,
  viewName: string,
  payload: UpdateVirtualViewPayload
): Promise<void> {
  await fetch(
    `/api/v1/projects/${projectUuid}/virtual-view/${viewName}`,
    {
      method: 'PATCH',
      body: JSON.stringify(payload),
    }
  );

  console.log('Updated virtual view:', viewName);
}

GitHub dbt Integration

Create dbt models from SQL queries and write them back to GitHub repositories.

import {
  type ApiGithubDbtWriteBack,
  type ApiGithubDbtWritePreview,
} from '@lightdash/common';

/**
 * API response for GitHub dbt write-back operation
 */
interface ApiGithubDbtWriteBack {
  status: 'ok';
  results: PullRequestCreated;
}

/**
 * API response for GitHub dbt write preview
 */
interface ApiGithubDbtWritePreview {
  status: 'ok';
  results: {
    /** GitHub repository URL */
    url: string;
    /** Repository name */
    repo: string;
    /** File path in repository */
    path: string;
    /** List of files to be created/modified */
    files: string[];
    /** Repository owner */
    owner: string;
  };
}

Usage Example:

import {
  type ApiGithubDbtWriteBack,
  type ApiGithubDbtWritePreview,
  type PullRequestCreated,
} from '@lightdash/common';

// Preview dbt model generation
async function previewDbtModel(
  projectUuid: string,
  sqlChartUuid: string
): Promise<void> {
  const response = await fetch(
    `/api/v1/projects/${projectUuid}/sql-charts/${sqlChartUuid}/github/preview`,
    { method: 'GET' }
  );

  const data: ApiGithubDbtWritePreview = await response.json();

  console.log('Repository:', data.results.repo);
  console.log('Owner:', data.results.owner);
  console.log('Path:', data.results.path);
  console.log('Files to be created:', data.results.files);
  console.log('Preview URL:', data.results.url);
}

// Create dbt model and write to GitHub
async function writeDbtModel(
  projectUuid: string,
  sqlChartUuid: string,
  modelName: string,
  description: string
): Promise<PullRequestCreated> {
  const response = await fetch(
    `/api/v1/projects/${projectUuid}/sql-charts/${sqlChartUuid}/github`,
    {
      method: 'POST',
      body: JSON.stringify({
        modelName,
        description,
      }),
    }
  );

  const data: ApiGithubDbtWriteBack = await response.json();

  console.log('Pull request created:', data.results.prUrl);
  console.log('PR number:', data.results.prNumber);
  console.log('PR title:', data.results.prTitle);

  return data.results;
}

// Example usage
async function exportSqlChartToDbt(): Promise<void> {
  const projectUuid = 'project-123';
  const sqlChartUuid = 'chart-456';

  // First preview the changes
  await previewDbtModel(projectUuid, sqlChartUuid);

  // Then create the dbt model
  const pr = await writeDbtModel(
    projectUuid,
    sqlChartUuid,
    'customer_revenue_metrics',
    'Customer revenue aggregations for reporting'
  );

  console.log(`Pull request created: ${pr.prUrl}`);
}

Constants

SQL Runner-related constants.

import { MAX_SAFE_INTEGER } from '@lightdash/common';

/**
 * Maximum safe integer for SQL Runner queries
 *
 * Set to 32-bit signed integer max (2,147,483,647) instead of
 * Number.MAX_SAFE_INTEGER to avoid issues with some warehouses like Redshift
 * when fetching "all results"
 */
const MAX_SAFE_INTEGER = 2_147_483_647;

Usage Example:

import { MAX_SAFE_INTEGER } from '@lightdash/common';

// Use when setting unlimited query limit
const query: SqlRunnerBody = {
  sql: 'SELECT * FROM large_table',
  limit: MAX_SAFE_INTEGER, // Fetch all results safely
};

Common Patterns

Building a Complete SQL Runner Query

import {
  type SqlRunnerQuery,
  SqlRunnerFieldType,
  SqlRunnerFilterBaseOperator,
  SqlRunnerFilterRelativeTimeValue,
  SortByDirection,
  VizAggregationOptions,
  FieldType,
} from '@lightdash/common';

function buildCompleteQuery(): SqlRunnerQuery {
  return {
    // Dimensions to group by
    dimensions: [
      { name: 'product_category' },
      { name: 'region' },
    ],

    // Time dimensions with granularity (using string literal)
    timeDimensions: [
      {
        name: 'order_date',
        granularity: 'MONTH',
      },
    ],

    // Metrics to aggregate
    metrics: [
      { name: 'total_revenue' },
    ],

    // Custom metrics with aggregation
    customMetrics: [
      {
        name: 'avg_order_value',
        aggType: VizAggregationOptions.AVERAGE,
        baseDimension: 'order_total',
      },
    ],

    // Filters
    filters: [
      {
        uuid: 'filter_1',
        fieldRef: 'order_date',
        fieldKind: FieldType.DIMENSION,
        fieldType: SqlRunnerFieldType.TIME,
        operator: SqlRunnerFilterBaseOperator.IS,
        values: { relativeTime: SqlRunnerFilterRelativeTimeValue.LAST_30_DAYS },
      },
      {
        uuid: 'filter_2',
        fieldRef: 'status',
        fieldKind: FieldType.DIMENSION,
        fieldType: SqlRunnerFieldType.STRING,
        operator: SqlRunnerFilterBaseOperator.IS,
        values: ['completed', 'shipped'],
      },
    ],

    // Sorting
    sortBy: [
      {
        name: 'order_date',
        kind: FieldType.DIMENSION,
        direction: SortByDirection.DESC,
      },
      {
        name: 'total_revenue',
        kind: FieldType.METRIC,
        direction: SortByDirection.DESC,
      },
    ],

    // Pivot configuration
    pivot: {
      index: ['product_category'],
      on: ['order_date'],
      values: ['total_revenue', 'avg_order_value'],
    },

    // Query settings
    limit: 500,
    timezone: 'UTC',
    sql: 'SELECT * FROM orders',
  };
}

Handling Job Status with Type Guards

import {
  type ApiSqlRunnerJobStatusResponse,
  isApiSqlRunnerJobSuccessResponse,
  isApiSqlRunnerJobErrorResponse,
  isErrorDetails,
  SchedulerJobStatus,
} from '@lightdash/common';

async function handleJobStatus(
  response: ApiSqlRunnerJobStatusResponse
): Promise<void> {
  const { results } = response;

  switch (results.status) {
    case SchedulerJobStatus.COMPLETED:
      if (isApiSqlRunnerJobSuccessResponse(results)) {
        // Handle success
        console.log('File URL:', results.details.fileUrl);
        console.log('Columns:', results.details.columns);
      }
      break;

    case SchedulerJobStatus.ERROR:
      if (isApiSqlRunnerJobErrorResponse(results)) {
        // Handle error
        if (isErrorDetails(results.details)) {
          console.error('Error:', results.details.error);
          if (results.details.lineNumber) {
            console.error(`At line ${results.details.lineNumber}`);
          }
        }
      }
      break;

    case SchedulerJobStatus.STARTED:
      console.log('Job is running...');
      break;

    case SchedulerJobStatus.SCHEDULED:
      console.log('Job is scheduled...');
      break;

    default:
      console.log('Unknown status');
  }
}

Creating and Managing SQL Charts

import {
  type CreateSqlChart,
  type UpdateSqlChart,
  type SqlChart,
  ChartKind,
  VizAggregationOptions,
  VizIndexType,
} from '@lightdash/common';

class SqlChartManager {
  async createBarChart(
    name: string,
    sql: string,
    spaceUuid: string
  ): Promise<string> {
    const chart: CreateSqlChart = {
      name,
      description: null,
      sql,
      limit: 500,
      config: {
        metadata: { version: 1 },
        type: ChartKind.VERTICAL_BAR,
        fieldConfig: {
          x: { reference: 'category', type: VizIndexType.CATEGORY },
          y: [{ reference: 'total', aggregation: VizAggregationOptions.SUM }],
          groupBy: undefined,
        },
        display: undefined,
      },
      spaceUuid,
    };

    const response = await fetch('/api/v1/sql-charts', {
      method: 'POST',
      body: JSON.stringify(chart),
    });

    const data = await response.json();
    return data.results.savedSqlUuid;
  }

  async updateChartName(
    chartUuid: string,
    newName: string
  ): Promise<void> {
    const update: UpdateSqlChart = {
      unversionedData: {
        name: newName,
        description: null,
        spaceUuid: 'current-space-uuid',
      },
    };

    await fetch(`/api/v1/sql-charts/${chartUuid}`, {
      method: 'PATCH',
      body: JSON.stringify(update),
    });
  }

  async updateChartQuery(
    chartUuid: string,
    newSql: string
  ): Promise<string | null> {
    const update: UpdateSqlChart = {
      versionedData: {
        sql: newSql,
        limit: 1000,
        config: {
          metadata: { version: 1 },
          type: ChartKind.TABLE,
          columns: {},
          display: undefined,
        },
      },
    };

    const response = await fetch(`/api/v1/sql-charts/${chartUuid}`, {
      method: 'PATCH',
      body: JSON.stringify(update),
    });

    const data = await response.json();
    return data.results.savedSqlVersionUuid;
  }
}

SQL Query Result Types

For custom SQL execution outside the standard MetricQuery flow, these simple types represent raw SQL query results:

/**
 * Raw row data from SQL queries with dynamic column names
 */
type SqlResultsRow = { [columnName: string]: unknown };

/**
 * Field metadata including name and warehouse-specific type
 */
type SqlResultsField = { name: string; type: string };

/**
 * Combined SQL query results structure
 */
type SqlQueryResults = {
  /** Array of field metadata describing columns */
  fields: SqlResultsField[];
  /** Array of result rows with dynamic columns */
  rows: SqlResultsRow[];
};

Usage Example:

import { type SqlQueryResults, type SqlResultsRow } from '@lightdash/common';

// Process raw SQL results
function processSqlResults(results: SqlQueryResults): void {
  console.log(`Columns: ${results.fields.map(f => f.name).join(', ')}`);
  console.log(`Rows: ${results.rows.length}`);

  // Access individual row data
  results.rows.forEach((row: SqlResultsRow) => {
    Object.entries(row).forEach(([columnName, value]) => {
      console.log(`${columnName}: ${value}`);
    });
  });
}

// Example results structure
const queryResults: SqlQueryResults = {
  fields: [
    { name: 'customer_id', type: 'INTEGER' },
    { name: 'total_orders', type: 'INTEGER' },
    { name: 'revenue', type: 'DECIMAL' },
  ],
  rows: [
    { customer_id: 1, total_orders: 5, revenue: 499.99 },
    { customer_id: 2, total_orders: 3, revenue: 299.99 },
  ],
};

Notes:

These types are simpler than the structured MetricQuery result types
Column types are warehouse-specific strings (e.g., "INTEGER", "VARCHAR", "TIMESTAMP")
Values in SqlResultsRow are unformatted raw values from the warehouse
For formatted results with metadata, use the standard MetricQuery result types instead

Version

Tile

Files

sql-runner.mddocs/api/features/

SQL Runner

Overview

Capabilities

SQL Runner Query Building

SQL Runner Field Types

Time Granularity

SQL Runner Filters

SQL Runner Sorting

SQL Runner Pivot

SQL Charts

SQL Runner Job Responses

SQL Runner Results

Virtual Views

GitHub dbt Integration

Constants

Common Patterns

Building a Complete SQL Runner Query

Handling Job Status with Type Guards

Creating and Managing SQL Charts

SQL Query Result Types

Related Documentation

Version

Tile

Files

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

SQL Runner

Overview

Capabilities

SQL Runner Query Building

SQL Runner Field Types

Time Granularity

SQL Runner Filters

SQL Runner Sorting

SQL Runner Pivot

SQL Charts

SQL Runner Job Responses

SQL Runner Results

Virtual Views

GitHub dbt Integration

Constants

Common Patterns

Building a Complete SQL Runner Query

Handling Job Status with Type Guards

Creating and Managing SQL Charts

SQL Query Result Types

Related Documentation

sql-runner.mddocs/api/features/