Metrics SQL API

Query your Lightdash semantic layer over the Postgres wire protocol from any SQL client or BI tool.

The Metrics SQL API is a Postgres wire protocol compatible interface for querying your Lightdash semantic layer. Any tool that can connect to a PostgreSQL database — psql, BI tools, notebooks, database drivers — can connect to Lightdash and query your metrics and dimensions with SQL.

Your explores are exposed as tables, and their dimensions and metrics as columns. When you run a query, Lightdash compiles it through the semantic layer, so metric calculations, joins, and access controls are handled for you — you get the same numbers as in the Lightdash UI.

The Metrics SQL API is currently in beta and only available on Lightdash Enterprise plans. It’s disabled by default and must be enabled for your organization by an admin.

For more information on our plans, visit our pricing page.

Connect

You can find copy-paste connection details in your project settings under Semantic Layer API. Connect with any Postgres-compatible client using:

Field	Value
Host	pg.<your_instance_name>.lightdash.cloud
Port	5432
Database	Your project UUID. The slugified project name also works, but changes if the project is renamed — the UUID is stable. You can find it in the Lightdash URL when viewing a project (/projects/<projectUuid>/...)
User	Your Lightdash account email. This value is informational — authentication is via the token
Password	A service account token (ldsvc_..., recommended) or a personal access token (ldpat_...)

As a connection URI:

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

Queries run with the permissions of the token, so you can only query explores and fields that the token’s account has access to.

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.

Supported SQL includes WHERE (=, !=, <, <=, >, >=, IN, LIKE/ILIKE, BETWEEN, IS NULL, AND/OR), HAVING, ORDER BY, LIMIT, and calculated expressions in the SELECT list (arithmetic, functions, CASE, and window functions).

Examples

Discover explores and fields

The catalog is exposed through information_schema. The field_type column on information_schema.columns tells you whether a field is a dimension or a metric:

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 the dimensions and metrics you want — no GROUP BY or aggregate functions needed:

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

Combine metrics and dimensions with expressions in the SELECT list, like table calculations in the Lightdash UI:

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

Query from Python

Because the interface speaks the Postgres wire protocol, existing Postgres drivers work:

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

If a query isn’t valid, error messages include hints — an unknown column error lists the available fields, and using an aggregate function suggests the matching metric instead.

Self-hosting

If you self-host Lightdash, the Metrics SQL API server is disabled until you set the PGWIRE_PORT environment variable. TLS is required by default, so you also need to provide a certificate and key (PEM format) for the hostname clients connect to:

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 PGWIRE_PORT is set without certificate paths and without PGWIRE_SSL_MODE=disabled, Lightdash fails fast at boot with a config error — this is intentional, so a misconfigured deployment can’t accidentally accept Lightdash tokens in plaintext.

• 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).

The certificate and key files are watched by modification time and reloaded on change, so cert-manager renewals apply without a restart. A failed reload keeps the previously loaded certificate in place and logs an error.

The server starts a separate TCP listener on that port, so you’ll also need to expose it through your load balancer or network configuration alongside the main Lightdash port. It requires a valid LIGHTDASH_LICENSE_KEY — see Enterprise License Keys.

The Postgres wire protocol handshake is StartTLS-style — the client sends a plaintext SSLRequest before upgrading — so a generic TLS-terminating load balancer (e.g. an L7 HTTPS proxy) cannot front this endpoint. Terminate TLS at Lightdash by giving the backend the cert and key. If you must front it, use a Postgres-aware proxy (e.g. PgBouncer, HAProxy in mode tcp) and set PGWIRE_SSL_MODE=disabled on the Lightdash side so it accepts plaintext from the proxy on a trusted network.

Rejecting plaintext clients

With TLS required (the default), a client that connects with sslmode=disable is rejected before authentication — no password is ever prompted for, so Lightdash tokens cannot be leaked over cleartext:

psql: error: connection to server ... failed:
ERROR:  connection requires TLS
SQLSTATE: 28000

Update client connection strings to sslmode=require (or stronger) to fix this.

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.

Using sslmode=verify-full

sslmode=require encrypts the connection; verify-full additionally verifies the server’s certificate and hostname, protecting against impersonation. On Lightdash Cloud the certificate is publicly trusted (Let’s Encrypt), but some Postgres clients don’t check the system trust store by default:

• psql / psycopg (libpq 16+): add sslrootcert=system to use the OS trust store. On older libpq, point sslrootcert at your system CA bundle (e.g. /etc/ssl/certs/ca-certificates.crt).
• JDBC (DBeaver, DataGrip): add sslfactory=org.postgresql.ssl.DefaultJavaSSLFactory to use Java’s built-in trust store.
• node-postgres, Go, Rust, .NET: use the runtime’s trust store natively — no extra configuration.

Validate via the CA chain as above — don’t pin the certificate itself, as it’s rotated automatically every ~60 days.

If you’re working in Python, the Lightdash Python SDK offers a typed, dataframe-native way to query the semantic layer without writing SQL.