Working with Files and S3

Use this page to explore, query, and modify files (CSV, JSON, JSONL, Parquet) and S3 datasets through the DBConvert Streams API.

Files are treated as queryable datasets in DBConvert Streams, so you can inspect and run SQL against CSV, JSON, JSONL, and Parquet files without creating streams first.

This page covers how to:

browse local filesystem roots and folders
verify a local folder is writable
inspect file metadata, preview rows, and get exact counts
run SQL against CSV, JSON, JSONL, and Parquet files
configure and browse S3-compatible storage
validate S3 paths and manifests
apply row-level edits, inserts, and deletes to files

Common workflows

Explore a local file

Call /files/meta to confirm format and schema.
Call /files/data to preview rows.
Call /files/count if you need an exact total.

Validate an S3 dataset before using it

Call /files/s3/configure to establish credentials.
Call /files/s3/validate to confirm the path and recommended read mode.
Call /files/s3/manifest if the dataset is manifest-driven.

Edit rows in a file

Call /files/data and keep the returned __rowid values.
Call /files/rows/apply with edits, inserts, or deletes.
Call /files/data again to confirm the rewritten file contains the expected rows.

Prerequisites

A running DBConvert Streams API server
Your API base URL
Access to the authentication headers described in API Reference

Path format depends on where DBConvert Streams is running:

Desktop app on Windows: use an absolute Windows path such as C:\Users\alice\Downloads\customers.csv
Desktop app on macOS or Linux: use an absolute local path such as /Users/alice/Downloads/customers.csv
Docker or server deployment: use the path that exists inside the running container or host, such as /data/imports/customers.csv
S3-compatible storage: use an S3 URI such as s3://analytics-bucket/exports/customers.parquet

The commands below use ABSOLUTE_FILE_PATH as a placeholder. Replace it with the correct path for your environment.

Use a common request pattern

API_URL="http://127.0.0.1:8020/api/v1"
API_KEY="YOUR_API_KEY"
INSTALL_ID="YOUR_INSTALL_ID"

All routes on this page use the same authenticated header pattern:

-H "X-API-Key: $API_KEY" \
-H "X-Install-ID: $INSTALL_ID"

Local filesystem helpers

Use the filesystem helper routes when building file pickers or validating paths before stream or SQL work.

These endpoints are typically used by UIs or automation scripts before reading or writing files.

Routes:

GET /fs/roots
GET /fs/list
POST /fs/writable

Get common roots for quick navigation:

curl -X GET "$API_URL/fs/roots" \
  -H "X-API-Key: $API_KEY" \
  -H "X-Install-ID: $INSTALL_ID"

List a folder:

curl -G "$API_URL/fs/list" \
  -H "X-API-Key: $API_KEY" \
  -H "X-Install-ID: $INSTALL_ID" \
  --data-urlencode "path=ABSOLUTE_FOLDER_PATH"

Check whether a folder is writable:

curl -X POST "$API_URL/fs/writable" \
  -H "X-API-Key: $API_KEY" \
  -H "X-Install-ID: $INSTALL_ID" \
  -H "Content-Type: application/json" \
  -d '{
    "path": "ABSOLUTE_FOLDER_PATH"
  }'

Verify by checking:

/fs/roots returns at least one directory you can navigate from your deployment environment
/fs/list echoes the requested path and returns entries for that location
/fs/writable returns "ok": true before you use the folder as an output location

File inspection

Use these routes when you need file structure, preview data, CSV dialect details, or exact row counts.

Routes:

GET /files/meta
GET /files/data
GET /files/count
POST /files/csv/sniff
POST /files/summary

Read metadata for a file:

curl -G "$API_URL/files/meta" \
  -H "X-API-Key: $API_KEY" \
  -H "X-Install-ID: $INSTALL_ID" \
  --data-urlencode "path=ABSOLUTE_FILE_PATH" \
  --data-urlencode "format=csv"

Preview rows from a file:

curl -G "$API_URL/files/data" \
  -H "X-API-Key: $API_KEY" \
  -H "X-Install-ID: $INSTALL_ID" \
  --data-urlencode "path=ABSOLUTE_FILE_PATH" \
  --data-urlencode "format=csv" \
  --data-urlencode "limit=50"

Get an exact count for the same file:

curl -G "$API_URL/files/count" \
  -H "X-API-Key: $API_KEY" \
  -H "X-Install-ID: $INSTALL_ID" \
  --data-urlencode "path=ABSOLUTE_FILE_PATH" \
  --data-urlencode "format=csv"

Sniff CSV dialect and inferred column options:

curl -X POST "$API_URL/files/csv/sniff" \
  -H "X-API-Key: $API_KEY" \
  -H "X-Install-ID: $INSTALL_ID" \
  -H "Content-Type: application/json" \
  -d '{
    "path": "ABSOLUTE_FILE_PATH"
  }'

Get column-level summary statistics:

curl -X POST "$API_URL/files/summary" \
  -H "X-API-Key: $API_KEY" \
  -H "X-Install-ID: $INSTALL_ID" \
  -H "Content-Type: application/json" \
  -d '{
    "path": "ABSOLUTE_FILE_PATH",
    "format": "csv"
  }'

Verify by checking:

/files/meta returns a non-empty schema for a known file
/files/data returns rows, and rows you may edit later include __rowid
/files/count returns a stable numeric total for the same path and format
/files/csv/sniff reports a delimiter and header setting that match the real CSV file
/files/summary includes the same columns you saw in metadata or preview output

SQL on files

Use POST /files/query when you need to run a SELECT query over files without creating a stream first.

Supported file readers in the current contract include:

read_csv_auto(...)
read_json_auto(...)
read_parquet(...)

Example: inspect the first rows in a file

curl -X POST "$API_URL/files/query" \
  -H "X-API-Key: $API_KEY" \
  -H "X-Install-ID: $INSTALL_ID" \
  -H "Content-Type: application/json" \
  -d '{
    "query": "SELECT * FROM read_csv_auto('\''ABSOLUTE_FILE_PATH'\'') LIMIT 10"
  }'

Verify by checking:

the response contains status: "success"
columns match the selected query projection
rows contain only the records your SQL should return

Example: aggregate records in a Parquet dataset

curl -X POST "$API_URL/files/query" \
  -H "X-API-Key: $API_KEY" \
  -H "X-Install-ID: $INSTALL_ID" \
  -H "Content-Type: application/json" \
  -d '{
    "query": "SELECT country, count(*) AS row_count FROM read_parquet('\''ABSOLUTE_FILE_PATH'\'') GROUP BY country ORDER BY row_count DESC"
  }'

You can also join multiple files in one query. For mixed file and database SQL, see Federated Query Workflows. You can also join multiple files or combine file queries with database tables.

For example, you can join a Parquet dataset with a CSV file or a PostgreSQL table in a single query. See Federated Query Workflows for cross-source SQL examples. Only read-only SQL is allowed on this route, so treat it as a file query endpoint for SELECT-style inspection rather than file mutation.

S3-compatible storage

Use these routes when the file lives in AWS S3, MinIO, DigitalOcean Spaces, Wasabi, or another S3-compatible provider.

S3 storage is treated the same as local files once configured, so you can inspect and query objects through the same file APIs.

Routes:

POST /files/s3/configure
GET /files/s3/buckets
POST /files/s3/buckets
GET /files/s3/list

Configure an S3 session first:

curl -X POST "$API_URL/files/s3/configure" \
  -H "X-API-Key: $API_KEY" \
  -H "X-Install-ID: $INSTALL_ID" \
  -H "Content-Type: application/json" \
  -d '{
    "credentialSource": "aws",
    "region": "us-east-1"
  }'

List available buckets:

curl -G "$API_URL/files/s3/buckets" \
  -H "X-API-Key: $API_KEY" \
  -H "X-Install-ID: $INSTALL_ID"

List objects in a bucket:

curl -G "$API_URL/files/s3/list" \
  -H "X-API-Key: $API_KEY" \
  -H "X-Install-ID: $INSTALL_ID" \
  --data-urlencode "bucket=YOUR_BUCKET" \
  --data-urlencode "prefix=optional/path/"

Create a bucket when your credentials allow it:

curl -X POST "$API_URL/files/s3/buckets" \
  -H "X-API-Key: $API_KEY" \
  -H "X-Install-ID: $INSTALL_ID" \
  -H "Content-Type: application/json" \
  -d '{
    "bucket": "analytics-exports",
    "region": "us-east-1"
  }'

Verify by checking:

/files/s3/configure returns a configured session without credential errors
/files/s3/buckets includes a bucket you expect to access
/files/s3/list returns keys for the requested bucket or prefix

Validate S3 paths and manifests

Use these routes before large file reads or manifest-driven workflows.

Routes:

GET /files/s3/validate
GET /files/s3/manifest

Validate an S3 path or glob:

curl -G "$API_URL/files/s3/validate" \
  -H "X-API-Key: $API_KEY" \
  -H "X-Install-ID: $INSTALL_ID" \
  --data-urlencode "path=s3://YOUR_BUCKET/path/*.parquet"

Read a manifest file:

curl -G "$API_URL/files/s3/manifest" \
  -H "X-API-Key: $API_KEY" \
  -H "X-Install-ID: $INSTALL_ID" \
  --data-urlencode "path=s3://YOUR_BUCKET/path/manifest.json"

Verify by checking:

/files/s3/validate marks the path as valid and returns a plausible recommendedMode
/files/s3/manifest returns the manifest payload you expect, including listed files or metadata

File row mutations

The current Swagger contract also includes file row mutation routes:

POST /files/rows/apply
PATCH /files/rows/edit
POST /files/rows/insert
DELETE /files/rows/delete

Use POST /files/rows/apply when you want inserts, updates, and deletes in one call. Use the __rowid value from /files/data to target existing rows.

These operations rewrite the underlying file. Use them for interactive fixes and small or medium file updates, not as a substitute for large batch processing.

Example:

curl -X POST "$API_URL/files/rows/apply" \
  -H "X-API-Key: $API_KEY" \
  -H "X-Install-ID: $INSTALL_ID" \
  -H "Content-Type: application/json" \
  -d '{
    "path": "ABSOLUTE_FILE_PATH",
    "format": "csv",
    "edits": [
      {
        "rowId": 2,
        "changes": {
          "status": "active"
        }
      }
    ],
    "inserts": [
      {
        "values": {
          "name": "Alice",
          "status": "active"
        }
      }
    ],
    "deletes": [
      {
        "rowId": 5
      }
    ]
  }'

Verify by checking:

the response reports the row counts you expected in updated, inserted, and deleted
a follow-up /files/data call shows the changed rows for the same file

End-to-end sanity check

Use these checks after wiring a client or workflow:

Call /fs/roots or /fs/list and confirm the target folder is visible.
Call /files/meta on one known file and confirm the schema is returned.
Call /files/data with a small limit and confirm rows are returned.
If you use S3, call /files/s3/configure and then /files/s3/buckets.
If you use row edits, confirm the __rowid values from /files/data match the rows you intend to update.

Troubleshooting

`401 Unauthorized`

confirm X-API-Key and X-Install-ID are present on the request
verify the values against the Account page

`404 File not found`

make sure the path is absolute for local files
make sure the path exists in the same environment where the API is running
if you are on Docker, use the container-visible path rather than your host-only path

Invalid format or parse errors

make sure format matches the actual file type
use /files/csv/sniff before CSV reads if delimiter or header detection is uncertain

S3 session not configured

call /files/s3/configure before /files/s3/buckets, /files/s3/list, or S3-backed reads
confirm the credentials have the required bucket and object permissions