Serving Tables and Queries over HTTP - Pixeltable Documentation

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 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: 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:

pip install 'fastapi[standard]'

Quickstart (TOML)

This is the primary config-file path for Approach 3: Declarative Serving. Add a [tool.pixeltable] section to your pyproject.toml:

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

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 at /docs.

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.

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 for examples.

Quickstart (single-endpoint CLI)

This is a single-endpoint CLI path for Approach 3: Declarative Serving. For quick experiments and one-off endpoints, you can skip the TOML file and configure a single route directly on the command line:

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

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.

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.

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

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

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:

{"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.

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

File uploads: when uploadfile_inputs is set, the request uses multipart/form-data instead of JSON. All other inputs become form fields.

[[service.routes]]
type = "insert"
table = "my_dir/images"
path = "/resize"
inputs = ["width", "height"]
uploadfile_inputs = ["image"]
outputs = ["resized"]
return_fileresponse = true

curl -X POST http://localhost:8000/resize \
  -F [email protected] -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).

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

The request body carries the primary key values (to identify the row) plus the values to update, as JSON fields:

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.

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

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:

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

[[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):

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

[[service.routes]]
type = "query"
path = "/lookup"
query = "myapp.queries.lookup_by_id"
one_row = true
method = "get"

curl 'http://localhost:8000/lookup?id=42'
# {"id": 42, "name": "Alice", "email": "[email protected]"}

Full example

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

# 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):

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:

{"id": "abc123", "job_url": "http://localhost:8000/jobs/abc123"}

Poll the job URL:

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 page. Quick examples:

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

​Overview

​Quickstart (TOML)

​Quickstart (Python)

​Quickstart (single-endpoint CLI)

​Decorator-style routes

​Exporting rows to an external database

​TOML Service File Reference

​[service] (optional)

​modules (optional)

​[[service.routes]]

​Common fields

​Insert routes

​Update routes

​Delete routes

​Query routes

​Full example

​Reading Back Computed Columns After Insert

​Background jobs

​CLI Reference

Overview

Quickstart (TOML)

Quickstart (Python)

Quickstart (single-endpoint CLI)

Decorator-style routes

Exporting rows to an external database

TOML Service File Reference

`[service]` (optional)

`modules` (optional)

`[[service.routes]]`

Common fields

Insert routes

Update routes

Delete routes

Query routes

Full example

Reading Back Computed Columns After Insert

Background jobs

CLI Reference