> ## Documentation Index
> Fetch the complete documentation index at: https://docs.lancedb.com/llms.txt
> Use this file to discover all available pages before exploring further.

# Query with SQL

> SQL query capabilities in LanceDB Enterprise for analytical queries and data exploration.

Enterprise-only

[LanceDB Enterprise](https://docs.lancedb.com/enterprise.md) comes with an SQL endpoint that can be used for analytical queries and data exploration. The SQL endpoint is designed to be compatible with the
[Arrow FlightSQL protocol](https://arrow.apache.org/docs/format/FlightSql.html), which allows you to use any Arrow FlightSQL-compatible client to query your data.

## Installing the client

There are Flight SQL clients available for most languages and tools.  If you find that your
preferred language or tool is not listed here, please [reach out](https://www.lancedb.com/contact) to us and we can help you find a solution.  The following examples demonstrate how to install the Python and TypeScript
clients.

```bash Python icon="python" theme={"theme":{"light":"vitesse-light","dark":"catppuccin-mocha"}}
# The `flightsql-dbapi` package provides a Python DB API 2 interface to the
# LanceDB SQL endpoint. You can use it to connect to the SQL endpoint and
# execute queries directly and get back results in pyarrow format.

pip install flightsql-dbapi
```

```bash TypeScript icon="square-js" theme={"theme":{"light":"vitesse-light","dark":"catppuccin-mocha"}}
# LanceDB maintains a TypeScript client for the Arrow FlightSQL protocol.
# You can use it to connect to the SQL endpoint and execute queries directly.
# Results are returned in Arrow format or as plain JS/TS objects.

npm install --save @lancedb/flightsql-client
```

## Usage

LanceDB uses the powerful DataFusion query engine to execute SQL queries.  This means that
you can use a wide variety of SQL syntax and functions to query your data.  For more detailed
information on the SQL syntax and functions supported by DataFusion, please refer to the
[DataFusion documentation](https://datafusion.apache.org/user-guide/sql/index.html).

The FlightSQL endpoint executes one SQL statement per request and is intended for queries. Use the LanceDB SDKs for DDL and table-management operations such as creating tables, adding columns, or building indexes.

### Setting Up the Client

Establish a connection to your LanceDB Enterprise SQL endpoint using your preferred FlightSQL client:

```python Python icon="python" theme={"theme":{"light":"vitesse-light","dark":"catppuccin-mocha"}}
from flightsql import FlightSQLClient

client = FlightSQLClient(
    host="your-enterprise-endpoint",
    port=10025,
    insecure=True,
    token="DATABASE_TOKEN",
    metadata={"database": "your-project-slug"},
    features={"metadata-reflection": "true"},
)
```

```typescript TypeScript icon="square-js" theme={"theme":{"light":"vitesse-light","dark":"catppuccin-mocha"}}
import { Client } from "@lancedb/flightsql-client";

const client = await Client.connect({
    host: "your-enterprise-endpoint:10025",
    username: "lancedb",
    password: "password",
});
```

### Executing a Query

Run SQL queries against your LanceDB tables. Different clients may handle the FlightSQL protocol differently:

```bash Python icon="python" theme={"theme":{"light":"vitesse-light","dark":"catppuccin-mocha"}}
def run_query(query: str):
    """Simple method to fully materialize query results"""
    info = client.execute(query)
    if len(info.endpoints) != 1:
        raise Error("Expected exactly one endpoint")
    ticket = info.endpoints[0].ticket
    reader = client.do_get(ticket)
    return reader.read_all()

result = run_query("SELECT * FROM flights WHERE origin = 'SFO'")
```

```typescript TypeScript icon="square-js" theme={"theme":{"light":"vitesse-light","dark":"catppuccin-mocha"}}
const result = await client.query("SELECT * FROM flights WHERE origin = 'SFO'");
```

### Processing Results

Handle the query results returned by your FlightSQL client:

```bash Python icon="python" theme={"theme":{"light":"vitesse-light","dark":"catppuccin-mocha"}}
print(result)
```

```typescript TypeScript icon="square-js" theme={"theme":{"light":"vitesse-light","dark":"catppuccin-mocha"}}
// Results are returned as plain JS/TS objects and we create an interface
// here for our expected structure so we can have strong typing.  This is
// optional but recommended.
interface FlightRecord {
    origin: string;
    destination: string;
}

const flights = (await result.collectToObjects()) as FlightRecord[];
console.log(flights);
```

### Inspecting query plans

The SQL endpoint runs queries through DataFusion, which means DataFusion's `EXPLAIN` family of statements is available unchanged. They're the SQL counterpart of the Python/TypeScript [`explain_plan` and `analyze_plan` methods](https://docs.lancedb.com/search/optimize-queries) and are useful for the same things: confirming index usage, checking filter pushdown, and finding the slow operator in a query that's underperforming.

| Statement | What it returns |
| :- | :- |
| `EXPLAIN <query>` | Logical and physical plan, without executing the query. |
| `EXPLAIN ANALYZE <query>` | Executes the query and annotates each operator with runtime metrics (rows, timing). |
| `EXPLAIN VERBOSE <query>` | Adds intermediate optimizer plans on top of `EXPLAIN`. |

Run them through the same client you use for regular queries — the result is a small Arrow table with `plan_type` and `plan` columns:

```python Python icon="python" theme={"theme":{"light":"vitesse-light","dark":"catppuccin-mocha"}}
plan = run_query(
    "EXPLAIN ANALYZE SELECT origin, destination "
    "FROM flights WHERE origin = 'SFO' LIMIT 100"
)
for row in plan.to_pylist():
    print(row["plan_type"])
    print(row["plan"])
    print()
```

```typescript TypeScript icon="square-js" theme={"theme":{"light":"vitesse-light","dark":"catppuccin-mocha"}}
const plan = await client.query(
    "EXPLAIN ANALYZE SELECT origin, destination " +
    "FROM flights WHERE origin = 'SFO' LIMIT 100"
);
for (const row of (await plan.collectToObjects()) as Array<{ plan_type: string; plan: string }>) {
    console.log(row.plan_type);
    console.log(row.plan);
}
```

The operators that show up in the SQL plan are the same ones documented on the [Optimize Query Performance](https://docs.lancedb.com/search/optimize-queries.md) page (`LanceScan`, `ScalarIndexQuery`, `KNNVectorDistance`, `ANNIvfPartition`, and so on), so the same reasoning about index coverage and filter pushdown applies - just read the plan from a SQL client instead of a query builder.

For full-text search from SQL, use the dedicated [`fts()` table function](https://docs.lancedb.com/search/sql/fts-sql). It takes two string-literal arguments: the table name and the JSON-encoded FTS query, which can include operator-style terms, OR, phrase, and fuzzy query forms.


This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.