Serving Tables and Queries over HTTP

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. FastAPI and uvicorn are optional dependencies. Install them before using pxt serve or the Python serving API:

pip install 'fastapi[standard]'

Quickstart (TOML)

Create a file service.toml:

[[service]]
name = "my-service"
port = 8000

[[service.routes]]
type = "insert"
table = "my_dir/my_table"
path = "/insert"
inputs = ["prompt"]
outputs = ["prompt", "result"]

Start the service:

pxt serve my-service --config service.toml

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)

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)

Quickstart (single-endpoint CLI)

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

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"]

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

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

The pxt command-line entry point is installed alongside the pixeltable package. All serving subcommands live under pxt serve.

pxt --version   # print the installed version
pxt --help      # list available commands

All pxt serve subcommands accept two additional flags:

Flag	Description
`--dry-run`	Print the fully-resolved config (after all CLI overrides) and exit without starting the server
`--json`	Emit machine-readable JSON: a startup record on stdout, or an error record on stderr

When --json is set, a successful start emits:

{"status": "starting", "host": "0.0.0.0", "port": 8000, "url": "http://localhost:8000", "docs_url": "http://localhost:8000/docs", "routes": 2}

Errors (including port conflicts) emit to stderr:

{"status": "error", "code": "EADDRINUSE", "port": 8000, "message": "port 8000 is already in use"}

`pxt serve <service-name>`

Load a TOML service file and start the server.

Flag	Type	Description
`--host`	string	Bind address (overrides `[[service]].host`)
`--port`	integer	Bind port (overrides `[[service]].port`)
`--prefix`	string	URL prefix (overrides `[[service]].prefix`; must be empty or start with `/`)
`--config`	string	Path to an additional TOML config file (will be merged into existing config)

`pxt serve insert`

Start a service exposing exactly one insert route.

Flag	Type	Required	Description
`--table`	string	yes	Pixeltable table path
`--path`	string	yes	URL path
`--inputs`	strings	no	Columns accepted from the request body (space-separated)
`--uploadfile-inputs`	strings	no	Columns accepted as multipart file uploads
`--outputs`	strings	no	Columns returned in the response
`--return-fileresponse`	flag	no	Return the single media output as a raw file
`--background`	flag	no	Run the insert in a background thread

Plus the shared --host, --port, --title, --prefix, --dry-run, and --json flags.

`pxt serve update`

Start a service exposing exactly one update route.

Flag	Type	Required	Description
`--table`	string	yes	Pixeltable table path (must have a primary key)
`--path`	string	yes	URL path
`--inputs`	strings	no	Non-PK columns accepted from the request body (PK columns are always accepted)
`--outputs`	strings	no	Columns returned in the response
`--return-fileresponse`	flag	no	Return the single media output as a raw file
`--background`	flag	no	Run the update in a background thread

Plus the shared --host, --port, --title, --prefix, --dry-run, and --json flags.

`pxt serve delete`

Start a service exposing exactly one delete route.

Flag	Type	Required	Description
`--table`	string	yes	Pixeltable table path
`--path`	string	yes	URL path
`--match-columns`	strings	no	Columns to match on (defaults to primary key)
`--background`	flag	no	Run the delete in a background thread

Plus the shared --host, --port, --title, --prefix, --dry-run, and --json flags.

`pxt serve query`

Start a service exposing exactly one query route.

Flag	Type	Required	Description
`--query`	string	yes	Dotted Python path to a `@pxt.query` or `retrieval_udf`
`--path`	string	yes	URL path
`--inputs`	strings	no	Parameters accepted from the request
`--uploadfile-inputs`	strings	no	Parameters accepted as multipart file uploads
`--one-row`	flag	no	Expect exactly one result row
`--return-fileresponse`	flag	no	Return the single media result as a raw file
`--background`	flag	no	Run the query in a background thread
`--method`	`get` / `post`	no	HTTP method (default `post`)

Plus the shared --host, --port, --title, --prefix, --dry-run, and --json flags. The dotted path is resolved at startup; the module portion is imported automatically, so no separate --module flag is needed.

Use Cases

Deployment

Pixeltable Cloud

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

Background jobs

CLI Reference

`pxt serve <service-name>`

`pxt serve insert`

`pxt serve update`

`pxt serve delete`

`pxt serve query`

Use Cases

Deployment

Pixeltable Cloud

Documentation Index

​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

​Background jobs

​CLI Reference

​pxt serve <service-name>

​pxt serve insert

​pxt serve update

​pxt serve delete

​pxt serve query

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

Background jobs

CLI Reference

`pxt serve <service-name>`

`pxt serve insert`

`pxt serve update`

`pxt serve delete`

`pxt serve query`