lancedb/docs/web/basics/sql.mdx

---
title: "SQL"
sidebarTitle: "SQL"
description: "The same tables, reached with SQL from any language with a Flight SQL client, and where the SQL dialect is specified."
icon: table-list
keywords: ["sql", "flight sql", "datafusion", "reference", "dialect"]
availability:
  oss: unavailable
  enterprise: varies
  summary: >-
    SQL runs on an Enterprise deployment whose Flight SQL endpoint is enabled
    and reachable; embedded connections raise instead.
---

SQL is a second way to reach the same tables the [SDKs](/basics/sdk) do --
not a different product, a different interface onto one engine. A table
written from Python is queried from SQL without an export step, and a table
created from SQL is visible to every SDK immediately.

## Any language, via Flight SQL {#flight-sql}

SQL is served over an
[Arrow Flight SQL](https://arrow.apache.org/docs/format/FlightSql.html)
endpoint, so any Flight SQL client reaches it -- which makes SQL the way in
for a language LanceDB has no SDK for. You don't need a LanceDB-specific
client at all, just a Flight SQL driver for your language.

The TypeScript SDK itself has no SQL method, for the same reason a Go or C#
application would reach for SQL first: connect a Flight SQL client instead of
waiting on a native binding. See [SDKs](/basics/sdk) for the officially
supported and community clients, and reach for Flight SQL whenever yours
isn't among them.

## SDK operations in SQL {#sdk-parity}

Many SDK operations have a SQL statement that does the same thing -- creating a
table is `CREATE TABLE`, inserting rows is `INSERT INTO`, building an index is
`CREATE INDEX`. They are not interchangeable, though: each side has operations
the other lacks.

| | SDK | SQL |
| --- | :---: | :---: |
| Create and manage catalog objects | yes | partly |
| Insert, merge, update, delete | yes | yes |
| Filter, project | yes | yes |
| Aggregate, join | no | yes |
| Vector search | yes | version-dependent |
| Full-text search | yes | yes |
| Feature engineering, jobs | yes | yes |
| Runs against a local database | yes | **no** |

On a deployment, feature engineering must also be enabled by the deployment,
whether it is declared in SQL or through an SDK -- see
[Computed columns](/feature-engineering/add-computed-column).

## Vector search depends on the deployment {#no-vector-sql}

The SDK query builder provides vector search across embedded and deployed
databases. SQL support depends on the deployment version and supported query
shape. Some versions recognize `ORDER BY distance(vector, query_vector) LIMIT k`
over a table scan and plan vector search, including indexed and flat search.
Confirm support on your deployment and use SQL
[`EXPLAIN`](/sql/dql/explain) to check the chosen plan; this is not a guarantee
that every deployed release supports that form.

Full-text search is available through the [`fts()`](/sql/functions#fts) table
function and the [`contains_tokens`](/sql/scalar-functions#contains-tokens)
predicate.

A [view](/management/view) is a saved SQL query. On a deployment you can
create one with `CREATE VIEW` or by passing the query to an SDK's
`create_view`; an embedded database has no views.

## SQL needs a deployment {#sql-is-remote}

A connection to a directory or a bucket has no server to plan and execute a
statement, and asking it to raises `NotImplementedError`. This is the largest
behavioral difference between the embedded and deployed forms, and the reason
the SDKs are the portable choice: their core table and search code moves
between the two by changing the URI, while SQL needs the deployment.

```python
db = lancedb.connect("./.lancedb")        # no SQL
db = lancedb.connect("db://my-database")  # SQL
```

A deployment serves SQL only when its Flight SQL endpoint is enabled and
reachable, which is part of the deployment's configuration. The SDK also has to
know where that endpoint is: pass `sql_host_override` when connecting, or
connect through an `http://` `host_override`, from which the SDK derives it
(the next port up, or 10025 when the override names no port).

## Running SQL from Python {#sql-from-python}

```python
reader = db.execute_query("SELECT id, text FROM my_docs LIMIT 10")
table = reader.read_all()
```

`execute_query` returns a blocking Arrow reader once results start arriving; it
does not wait for the whole query. `execute_query_async` returns a handle
instead -- see [Queries](/basics/query).

## Choosing between them {#choosing}

Reach for **the SDKs** when you are writing data, managing the catalog, running
vector search, or building an application that should work the same embedded and
deployed.

Reach for **SQL** when the question is analytical -- joins across tables,
aggregates, window functions -- when the thing asking is a BI tool, a
notebook, or a person who already thinks in SQL, or when it's the only client
available for your language.

The two compose. A common shape is SDK code to build and maintain tables, and
SQL for the analysis on top.

## The complete reference {#reference}

This page is the concept; [SQL Reference](/sql) is the specification of the
SQL dialect, with one page per statement, syntax first. It does not define the
SDKs: for a language's methods, their arguments and what they return, see that
SDK's reference -- [Python](https://lancedb.github.io/lancedb/python/python/),
[TypeScript](https://lancedb.github.io/lancedb/js/) or
[Rust](https://docs.rs/lancedb/latest/lancedb/index.html).

<Card title="SQL Reference" icon="table-list" href="/sql">
  DDL by object, then DML, then DQL -- the detailed technical documentation
  that defines the behavior of every operation and SDK API.
</Card>

## What to read next {#next}

<CardGroup cols={2}>
  <Card title="SDKs" icon="code-compare" href="/basics/sdk">
    The language clients, and what stays true across all of them.
  </Card>
  <Card title="Agentic Skills" icon="robot" href="/basics/agentic-skills">
    Give a coding agent a current reference to this same API surface.
  </Card>
</CardGroup>