> ## Documentation Index
> Fetch the complete documentation index at: https://lightdash-mintlify-9f84ba80.mintlify.site/llms.txt
> Use this file to discover all available pages before exploring further.

# 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.

<Info>
  **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](https://www.lightdash.com/pricing).
</Info>

## 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](/references/workspace/personal-tokens) (`ldpat_...`)                                                                              |

As a connection URI:

```bash theme={null}
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:

```sql theme={null}
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:

```sql theme={null}
SELECT
  orders_status,
  orders_total_order_amount
FROM orders
ORDER BY orders_total_order_amount DESC;
```

### Filter and limit

```sql theme={null}
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:

```sql theme={null}
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:

```python theme={null}
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,
)
```

<Tip>
  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.
</Tip>

## Self-hosting

If you self-host Lightdash, the Metrics SQL API server is disabled until you set the `PGWIRE_PORT` [environment variable](/self-host/customize-deployment/environment-variables#metrics-sql-api). TLS is required by default, so you also need to provide a certificate and key (PEM format) for the hostname clients connect to:

```bash theme={null}
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).

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](/self-host/customize-deployment/enterprise-license-keys).

## 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.

<Accordion title="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.
</Accordion>

<Tip>
  If you're working in Python, the [Lightdash Python SDK](/sdk/python-sdk) offers a typed, dataframe-native way to query the semantic layer without writing SQL.
</Tip>