Metrics SQL API

​

Connect

psql "postgresql://<your_email>:<token>@pg.<your_instance_name>.lightdash.cloud:5432/<project_uuid>?sslmode=require"

​

How queries work

• Tables are explores. Each explore in your project appears as a table, and FROM takes a single explore. Fields from tables joined in the explore are included as columns — joins are defined in the semantic layer, not in your SQL.
• Columns are field IDs. Dimensions and metrics use their Lightdash field IDs, e.g. orders_status, orders_total_order_amount.
• Metrics are pre-aggregated. Select a metric like any other column and Lightdash computes the aggregation for you. GROUP BY is optional — results are implicitly grouped by the dimensions you select. If you do write one, it’s validated against your SELECT list.
• Results are limited to 500 rows by default. Add a LIMIT to override it.

​

Examples

​

Discover explores and fields

SELECT table_name
FROM information_schema.tables;

SELECT column_name, data_type, field_type
FROM information_schema.columns
WHERE table_name = 'orders';

​

Query a metric by a dimension

SELECT
  orders_status,
  orders_total_order_amount
FROM orders
ORDER BY orders_total_order_amount DESC;

​

Filter and limit

SELECT
  orders_order_date_month,
  orders_total_order_amount,
  payments_unique_payment_count
FROM orders
WHERE orders_order_date >= '2026-01-01'
  AND orders_status IN ('completed', 'shipped')
ORDER BY 1
LIMIT 12;

​

Calculated expressions

SELECT
  orders_status,
  orders_total_order_amount,
  orders_total_order_amount / payments_unique_payment_count AS amount_per_payment
FROM orders;

​

Query from Python

import psycopg2
import pandas as pd

conn = psycopg2.connect(
    host="pg.<your_instance_name>.lightdash.cloud",
    port=5432,
    dbname="<project_uuid>",
    user="<your_email>",
    password="<token>",
    sslmode="require",
)

df = pd.read_sql(
    "SELECT orders_status, orders_total_order_amount FROM orders",
    conn,
)

​

Self-hosting

PGWIRE_PORT=5432
PGWIRE_SSL_CERT_PATH=/path/to/tls.crt   # server certificate (leaf first, then chain)
PGWIRE_SSL_KEY_PATH=/path/to/tls.key    # private key

• If your certificate is from a public CA (e.g. Let’s Encrypt), clients can use sslmode=require or verify-full.
• If you use a private CA or a self-signed certificate, clients either pass the CA with sslrootcert=/path/to/ca.pem for verification, or use sslmode=require (encrypted, but the server identity isn’t verified).

​

Limitations

• No explicit joins, subqueries, or CTEs. Queries select from a single explore; joins are defined in the semantic layer.
• No DML. The API is read-only — SELECT queries only.
• No custom metrics or period-over-period comparisons.
• Simple query protocol only. Prepared statements and bind parameters (the extended query protocol) aren’t supported. psql and drivers sending plain-text queries work; some GUI tools won’t.
• No pg_catalog emulation. Schema browsers in GUI clients (e.g. DBeaver) won’t populate their sidebar — query information_schema.tables and information_schema.columns instead.