> ## Documentation Index
> Fetch the complete documentation index at: https://docs.pixeltable.com/llms.txt
> Use this file to discover all available pages before exploring further.
# Serving Tables and Queries over HTTP
> Serve Pixeltable tables and queries as HTTP API endpoints using TOML service definitions or Python code with FastAPIRouter integration.
## Overview
Pixeltable can expose table operations and queries as HTTP endpoints via
`pixeltable.serving.FastAPIRouter`. You can configure endpoints either
programmatically in Python or declaratively with a TOML service file.
This page builds on the HTTP-based paths from the
[Deployment Strategies](/howto/deployment/overview#deployment-strategies)
overview:
* [Approach 2: Pixeltable as Full Backend](/howto/deployment/overview#approach-2-pixeltable-as-full-backend):
use the Pixeltable SDK's `FastAPIRouter` when you already have, or are
building, a FastAPI app.
* [Approach 3: Declarative Serving](/howto/deployment/overview#approach-3-declarative-serving-pxt-serve):
use the Pixeltable CLI command `pxt serve` when you want Pixeltable to create
and run the FastAPI app from TOML or CLI flags.
FastAPI and uvicorn are optional dependencies. Install them before using
`pxt serve` or the Python serving API:
```bash theme={"theme":{"light":"light-plus","dark":"dark-plus"}}
pip install 'fastapi[standard]'
```
## Quickstart (TOML)
This is the primary config-file path for [Approach 3: Declarative Serving](/howto/deployment/overview#approach-3-declarative-serving-pxt-serve).
Add a `[tool.pixeltable]` section to your `pyproject.toml`:
```toml theme={"theme":{"light":"light-plus","dark":"dark-plus"}}
# pyproject.toml
[[tool.pixeltable.service]]
name = "my-service"
port = 8000
modules = ["schema"] # Python modules to import (creates tables/views)
[[tool.pixeltable.service.routes]]
type = "insert"
table = "my_dir.my_table"
path = "/insert"
inputs = ["prompt"]
outputs = ["prompt", "result"]
```
Start the service:
```bash theme={"theme":{"light":"light-plus","dark":"dark-plus"}}
pxt serve my-service
```
You can also use a standalone TOML file (`pxt serve my-service --config service.toml`) with the same route syntax but `[[service]]` and `[[service.routes]]` as top-level keys. The `pyproject.toml` format is preferred for new projects.
The service is now running at `http://localhost:8000` with auto-generated
[OpenAPI docs](https://fastapi.tiangolo.com/features/#automatic-docs) at `/docs`.
```bash theme={"theme":{"light":"light-plus","dark":"dark-plus"}}
curl -X POST http://localhost:8000/insert \
-H 'Content-Type: application/json' \
-d '{"prompt": "a sunset over the ocean"}'
# {"prompt": "a sunset over the ocean", "result": "..."}
```
## Quickstart (Python)
This is the SDK path for [Approach 2: Pixeltable as Full Backend](/howto/deployment/overview#approach-2-pixeltable-as-full-backend).
```python theme={"theme":{"light":"light-plus","dark":"dark-plus"}}
import fastapi
import uvicorn
import pixeltable as pxt
from pixeltable.serving import FastAPIRouter
t = pxt.get_table('my_dir.my_table')
app = fastapi.FastAPI()
router = FastAPIRouter()
router.add_insert_route(t, path='/insert', inputs=['prompt'], outputs=['prompt', 'result'])
router.add_update_route(t, path='/update', inputs=['prompt'], outputs=['id', 'prompt', 'result'])
app.include_router(router)
uvicorn.run(app, host='0.0.0.0', port=8000)
```
`@pxt.query` eagerly evaluates the function body at decoration time. Create tables
**before** defining queries that reference them. Use `if_exists='ignore'` to keep
table creation idempotent. See the
[migration guide](/migrate/from-hand-written-endpoints) for examples.
## Quickstart (single-endpoint CLI)
This is a single-endpoint CLI path for [Approach 3: Declarative Serving](/howto/deployment/overview#approach-3-declarative-serving-pxt-serve).
For quick experiments and one-off endpoints, you can skip the TOML file and
configure a single route directly on the command line:
```bash theme={"theme":{"light":"light-plus","dark":"dark-plus"}}
# one insert endpoint
pxt serve insert --table my_dir.my_table --path /generate \
--inputs prompt --outputs prompt result --port 8000
# one update endpoint
pxt serve update --table my_dir.my_table --path /update \
--inputs prompt --outputs id prompt result
# one delete endpoint
pxt serve delete --table my_dir.my_table --path /delete
# one query endpoint
pxt serve query --query myapp.queries.search_docs --path /search
```
Single-endpoint mode accepts the same fields as the equivalent `[[service.routes]]`
TOML entry. It is meant for development and ad-hoc serving; for anything
beyond that use the TOML file (it is the only way to expose more than one
endpoint).
## Decorator-style routes
`add_insert_route()` / `add_update_route()` build a response model automatically
from the column schema. When you need a *different* response shape -- e.g. a
richer API envelope, derived fields, or a stripped-down payload -- use the
`@router.insert_route` / `@router.update_route` decorators. The decorated
function receives the requested output columns as keyword arguments and returns
a `pydantic.BaseModel` subclass that defines the HTTP response body.
```python theme={"theme":{"light":"light-plus","dark":"dark-plus"}}
import pydantic
from pixeltable.serving import FastAPIRouter
router = FastAPIRouter()
class GenerateResponse(pydantic.BaseModel):
caption: str
score: float
@router.insert_route(t, path='/generate', inputs=['prompt'], outputs=['caption', 'score'])
def format_insert(*, caption: str, score: float) -> GenerateResponse:
return GenerateResponse(caption=caption.strip(), score=round(score, 3))
@router.update_route(t, path='/update', inputs=['prompt'], outputs=['id', 'caption', 'score'])
def format_update(*, id: int, caption: str, score: float) -> GenerateResponse:
return GenerateResponse(caption=caption.strip(), score=round(score, 3))
```
Rules the framework enforces at registration time:
* Every parameter must be keyword-only and have a type annotation.
* Every parameter name must appear in `outputs`, and every `outputs` entry must
be a parameter.
* Parameter annotations must match the column types (strict nullability: a
nullable column requires `T | None`). Media columns are delivered as URL
strings, so annotate them as `str`.
* The return annotation must be a `pydantic.BaseModel` subclass.
Decorator routes support the same `background=True` flag as the non-decorator
forms; when enabled, the decorated function's return value is delivered as the
background-job result.
The decorator forms are Python-only -- there is no equivalent in the TOML
service file.
## Exporting rows to an external database
Insert and update routes can export each successful request as a row in an
external SQL database. Configure it via the Python API, the TOML service file,
or the CLI; all three forms route through the same `SqlExport` specification.
Each request performs the Pixeltable insert/update first; only if it succeeds
does the row get written to the external table.
**Python API.** Pass an `export_sql=SqlExport(...)` argument to
`add_insert_route`, `add_update_route`, or the decorator forms.
```python theme={"theme":{"light":"light-plus","dark":"dark-plus"}}
from pixeltable.serving import FastAPIRouter, SqlExport
router = FastAPIRouter()
router.add_insert_route(
t,
path='/generate',
inputs=['prompt'],
outputs=['prompt', 'result'],
export_sql=SqlExport(
db_connect='postgresql+psycopg://user:pw@host/analytics',
table='generations',
),
)
```
**TOML.** Add a nested `[routes.export_sql]` table under any insert or update
route. The same fields apply.
```toml theme={"theme":{"light":"light-plus","dark":"dark-plus"}}
[[routes]]
type = "insert"
table = "my_dir.my_table"
path = "/generate"
inputs = ["prompt"]
outputs = ["prompt", "result"]
[routes.export_sql]
db_connect = "postgresql+psycopg://user:pw@host/analytics"
table = "generations"
# db_schema = "public" # optional
# method = "insert" # default; alternatives: "update", "merge" (not yet supported)
```
**CLI.** Single-endpoint mode supports the same fields via `--export-sql-*`
flags on `pxt serve insert` and `pxt serve update`.
```bash theme={"theme":{"light":"light-plus","dark":"dark-plus"}}
pxt serve insert --table my_dir.my_table --path /generate \
--inputs prompt --outputs prompt result \
--export-sql-db-connect 'postgresql+psycopg://user:pw@host/analytics' \
--export-sql-table generations
```
The row written to the target is the response body: the same columns as
`outputs`, with media-typed columns rendered as URL strings (so the
corresponding target columns must be string-typed). Schema compatibility is
validated once at registration; the target table must already exist or
registration fails. The engine and connection pool are cached per
`db_connect`, so multiple routes pointing at the same database share one pool.
`SqlExport.method` controls how each row is written:
* `'insert'` (default): append the row via `INSERT ... VALUES`. The target
acts as an audit log -- replaying the same request produces a duplicate.
* `'update'`: update the row by primary-key match. The target is treated as a
current-state view keyed on its primary key. The response columns must
include all primary-key columns of the target plus at least one non-PK
column to set; the target table itself must declare a primary key. This is a
strict update, not an upsert -- if no row matches, the request fails with
HTTP 500.
* `'merge'` (upsert): not yet supported.
When paired with `add_insert_route` and `method='update'`, a Pixeltable insert
triggers a target-side update. This is intentional: it supports the pattern
where the Pixeltable table is append-only (e.g. an event log) but the target
is a deduplicated, current-state view.
If the external write fails after the Pixeltable insert/update has already
committed, the request returns HTTP 500; no rollback is performed.
`export_sql=` is mutually exclusive with `return_fileresponse=True` and is
compatible with `background=True` (the SQL write runs in the worker thread).
See [`SqlExport`](https://docs.pixeltable.com/sdk/latest/pixeltable/serving/SqlExport)
for the full target specification.
Connection strings with embedded passwords land in plaintext on disk (TOML)
or in process listings (CLI). Don't commit `service.toml`; consider a
connection string that pulls credentials from the environment or a
`.pgpass`-style file. Env-var substitution within the TOML is not yet
supported.
## TOML Service File Reference
### `[service]` (optional)
Top-level settings for the FastAPI application and server.
| Field | Type | Default | Description |
| -------- | ------- | -------------- | ------------------------------------------------------------------------- |
| `title` | string | `"Pixeltable"` | Title shown in the OpenAPI docs |
| `prefix` | string | `""` | URL prefix prepended to all route paths (must be empty or start with `/`) |
| `host` | string | `"0.0.0.0"` | Server bind address |
| `port` | integer | `8000` | Server bind port |
### `modules` (optional)
A list of additional Python modules to import at startup, for their
registration side effects. The module that hosts a query route's dotted
path is imported automatically, so this is only needed when you depend
on `@pxt.query` / `@pxt.udf` definitions in *other* modules that wouldn't
otherwise be loaded.
```toml theme={"theme":{"light":"light-plus","dark":"dark-plus"}}
modules = ["myapp.queries", "myapp.udfs"]
```
### `[[service.routes]]`
Each `[[service.routes]]` entry defines one HTTP endpoint. The `type` field determines
the route kind and which additional fields are valid.
#### Common fields
| Field | Type | Required | Default | Description |
| ------------ | ------------------------------------------------ | -------- | ------- | ---------------------------------------------------------------- |
| `type` | `"insert"` / `"update"` / `"delete"` / `"query"` | yes | -- | Route kind |
| `path` | string | yes | -- | URL path (e.g., `"/generate"`) |
| `background` | bool | no | `false` | Run the operation in a background thread and return a job handle |
When `background = true`, the endpoint returns immediately with:
```json theme={"theme":{"light":"light-plus","dark":"dark-plus"}}
{"id": "abc123", "job_url": "http://localhost:8000/jobs/abc123"}
```
Poll `job_url` until `status` is `"done"` or `"error"`.
***
### Insert routes
Insert a single row into a table and return the resulting column values.
```toml theme={"theme":{"light":"light-plus","dark":"dark-plus"}}
[[service.routes]]
type = "insert"
table = "my_dir/my_table"
path = "/generate"
inputs = ["prompt"]
outputs = ["prompt", "result"]
```
| Field | Type | Required | Default | Description |
| --------------------- | --------------- | -------- | ------------------------ | -------------------------------------------------------------------------------------------------------------------------------------------------- |
| `table` | string | yes | -- | Pixeltable table path |
| `inputs` | list of strings | no | all non-computed columns | Columns to accept as request fields |
| `uploadfile_inputs` | list of strings | no | -- | Columns to accept as file uploads (multipart) |
| `outputs` | list of strings | no | all columns | Columns to include in the response |
| `return_fileresponse` | bool | no | `false` | Return the single media-typed output as a raw file download |
| `export_sql` | nested table | no | -- | Export each request as a row into an external SQL database. See [Exporting rows to an external database](#exporting-rows-to-an-external-database). |
**File uploads:** when `uploadfile_inputs` is set, the request uses `multipart/form-data`
instead of JSON. All other inputs become form fields.
```toml theme={"theme":{"light":"light-plus","dark":"dark-plus"}}
[[service.routes]]
type = "insert"
table = "my_dir/images"
path = "/resize"
inputs = ["width", "height"]
uploadfile_inputs = ["image"]
outputs = ["resized"]
return_fileresponse = true
```
```bash theme={"theme":{"light":"light-plus","dark":"dark-plus"}}
curl -X POST http://localhost:8000/resize \
-F image=@photo.jpg -F width=640 -F height=480 \
--output resized.jpg
```
***
### Update routes
Update a single row identified by its primary key values, and return the updated
columns (including any computed columns that depend on them).
```toml theme={"theme":{"light":"light-plus","dark":"dark-plus"}}
[[service.routes]]
type = "update"
table = "my_dir/my_table"
path = "/update"
inputs = ["prompt"]
outputs = ["id", "prompt", "result"]
```
| Field | Type | Required | Default | Description |
| --------------------- | --------------- | -------- | ------------------------------------------- | -------------------------------------------------------------------------------------------------------------------------------------------------- |
| `table` | string | yes | -- | Pixeltable table path (must have a primary key) |
| `inputs` | list of strings | no | all non-PK, non-computed, non-media columns | Columns to update (PK columns are always in the request body but cannot appear here) |
| `outputs` | list of strings | no | all columns | Columns to include in the response |
| `return_fileresponse` | bool | no | `false` | Return the single media-typed output as a raw file download |
| `export_sql` | nested table | no | -- | Export each request as a row into an external SQL database. See [Exporting rows to an external database](#exporting-rows-to-an-external-database). |
The request body carries the primary key values (to identify the row) plus the
values to update, as JSON fields:
```bash theme={"theme":{"light":"light-plus","dark":"dark-plus"}}
curl -X POST http://localhost:8000/update \
-H 'Content-Type: application/json' \
-d '{"id": 42, "prompt": "updated text"}'
# {"id": 42, "prompt": "updated text", "result": "..."}
```
If the identified row does not exist, the endpoint returns HTTP 404. Computed
columns that depend on any updated column are automatically recomputed and
appear in the response.
Media-typed columns (image, video, audio, document) cannot currently be
updated. To replace a media value, delete the row and insert a new one.
***
Delete routes use HTTP **POST**, not the HTTP DELETE method. Update client-side fetch calls accordingly.
### Delete routes
Delete rows matching the given column values and return the count of rows affected.
```toml theme={"theme":{"light":"light-plus","dark":"dark-plus"}}
[[service.routes]]
type = "delete"
table = "my_dir/my_table"
path = "/delete"
```
| Field | Type | Required | Default | Description |
| --------------- | --------------- | -------- | ------------------- | ------------------------------------- |
| `table` | string | yes | -- | Pixeltable table path |
| `match_columns` | list of strings | no | primary key columns | Columns to match on (AND-ed equality) |
```bash theme={"theme":{"light":"light-plus","dark":"dark-plus"}}
curl -X POST http://localhost:8000/delete \
-H 'Content-Type: application/json' \
-d '{"id": 42}'
# {"num_rows": 1}
```
To delete by a non-primary-key column:
```toml theme={"theme":{"light":"light-plus","dark":"dark-plus"}}
[[service.routes]]
type = "delete"
table = "my_dir/my_table"
path = "/delete-by-tag"
match_columns = ["tag"]
```
***
Media columns (`pxt.Image`, `pxt.Video`, `pxt.Audio`, `pxt.Document`) serialize as URL strings (e.g., `/media/path/to/file.pdf`). The client receives a string, not binary data.
### Query routes
Execute a `@pxt.query` or `retrieval_udf` function and return the results.
```toml theme={"theme":{"light":"light-plus","dark":"dark-plus"}}
[[service.routes]]
type = "query"
path = "/search"
query = "myapp.queries.search_docs"
```
| Field | Type | Required | Default | Description |
| --------------------- | ------------------ | -------- | -------------------- | -------------------------------------------------------------------------- |
| `query` | string | yes | -- | Dotted Python path to a `@pxt.query` or `retrieval_udf` function |
| `inputs` | list of strings | no | all query parameters | Parameters to accept as request fields |
| `uploadfile_inputs` | list of strings | no | -- | Parameters to accept as file uploads (not supported with `method = "get"`) |
| `one_row` | bool | no | `false` | Expect exactly one result row (404 on 0, 409 on >1) |
| `return_fileresponse` | bool | no | `false` | Return the single media-typed result as a raw file |
| `method` | `"get"` / `"post"` | no | `"post"` | HTTP method for the endpoint |
The `query` field is a dotted Python path (e.g., `"myapp.queries.search_docs"`);
the module portion is imported automatically when the route is resolved.
**Multi-row response** (default):
```bash theme={"theme":{"light":"light-plus","dark":"dark-plus"}}
curl -X POST http://localhost:8000/search \
-H 'Content-Type: application/json' \
-d '{"query_text": "hello"}'
# {"rows": [{"id": 1, "text": "hello world", "score": 0.95}, ...]}
```
**Single-row lookup** (`one_row = true`):
```toml theme={"theme":{"light":"light-plus","dark":"dark-plus"}}
[[service.routes]]
type = "query"
path = "/lookup"
query = "myapp.queries.lookup_by_id"
one_row = true
method = "get"
```
```bash theme={"theme":{"light":"light-plus","dark":"dark-plus"}}
curl 'http://localhost:8000/lookup?id=42'
# {"id": 42, "name": "Alice", "email": "alice@example.com"}
```
***
## Full example
```toml theme={"theme":{"light":"light-plus","dark":"dark-plus"}}
[[service]]
name = "image-processing-service"
port = 8080
modules = ["myapp.queries"]
# Insert an image and get back processed outputs
[[service.routes]]
type = "insert"
table = "myapp/images"
path = "/process"
inputs = ["width", "height"]
uploadfile_inputs = ["image"]
outputs = ["thumbnail", "embedding"]
# Insert with background processing for slow pipelines
[[service.routes]]
type = "insert"
table = "myapp/videos"
path = "/ingest"
background = true
# Update a row by primary key (returns the updated columns incl. computed ones)
[[service.routes]]
type = "update"
table = "myapp/images"
path = "/images/update"
inputs = ["tag"]
outputs = ["id", "tag", "thumbnail"]
# Delete by primary key
[[service.routes]]
type = "delete"
table = "myapp/images"
path = "/images/delete"
# Delete by a non-PK column
[[service.routes]]
type = "delete"
table = "myapp/images"
path = "/images/delete-by-tag"
match_columns = ["tag"]
# Search via a @pxt.query function
[[service.routes]]
type = "query"
path = "/search"
query = "myapp.queries.search_images"
# Single-row lookup via GET
[[service.routes]]
type = "query"
path = "/lookup"
query = "myapp.queries.lookup_by_id"
one_row = true
method = "get"
# Return a raw image file from a query
[[service.routes]]
type = "query"
path = "/thumbnail"
query = "myapp.queries.get_thumbnail"
return_fileresponse = true
```
## Reading Back Computed Columns After Insert
Use `return_rows=True` to get all column values (including computed columns)
directly from `insert()`, `update()`, or `batch_update()` without a follow-up
query.
```python theme={"theme":{"light":"light-plus","dark":"dark-plus"}}
# Anti-pattern: insert then query
table.insert([row])
result = table.where(table.id == value).select(...).collect()
data = result[0]
# Correct: return_rows=True
status = table.insert([row], return_rows=True)
data = status.rows[0] # dict with ALL columns including computed
```
`status.rows` is a list of plain dicts. For typed access, use Pydantic's
`model_validate()` with `extra="ignore"` (row dicts contain every column;
ignore the ones you don't need):
```python theme={"theme":{"light":"light-plus","dark":"dark-plus"}}
from pydantic import BaseModel
class AgentResult(BaseModel):
model_config = {"extra": "ignore"}
answer: str | None = None
tool_output: Any = None
status = agent_table.insert([{"prompt": user_input}], return_rows=True)
result = AgentResult.model_validate(status.rows[0])
```
**When to use which:**
* `return_rows=True` -- any time you insert a row with computed columns and need the results back
* `to_pydantic()` -- when reading from a `ResultSet` (after `.collect()`)
* `model_validate()` -- when reading from `status.rows` (plain dicts from `return_rows=True`)
## Background jobs
Any route can set `background = true`. The endpoint returns immediately with a
job handle:
```json theme={"theme":{"light":"light-plus","dark":"dark-plus"}}
{"id": "abc123", "job_url": "http://localhost:8000/jobs/abc123"}
```
Poll the job URL:
```bash theme={"theme":{"light":"light-plus","dark":"dark-plus"}}
curl http://localhost:8000/jobs/abc123
# {"status": "pending"}
# ... later ...
# {"status": "done", "result": {...}}
# or
# {"status": "error", "error": "..."}
```
`background` is mutually exclusive with `return_fileresponse`.
## CLI Reference
For the full `pxt` command-line reference (all subcommands, flags, and usage patterns), see the dedicated [CLI Reference](/platform/cli) page.
Quick examples:
```bash theme={"theme":{"light":"light-plus","dark":"dark-plus"}}
pxt serve my-service --config service.toml # named service
pxt serve insert --table my_dir.my_table --path /gen --inputs prompt # single endpoint
pxt serve my-service --config service.toml --dry-run # validate without starting
```