Datastore API Reference

A standalone, CKAN-compatible datastore service: tabular CRUD + search over a pluggable storage backend. Every action lives under /api/3/action/ and returns the CKAN envelope, so existing CKAN datastore clients work unchanged — whether this runs alongside CKAN or independently.

Interactive docs: GET /datastore/api/docs (Swagger UI) · GET /datastore/api/redoc · GET /datastore/api/openapi.json
Postman: import postman/collection.json — one worked request per endpoint.

Conventions

Response envelope

Every response is a CKAN envelope. On success:

{ "help": "<request URL>", "success": true, "result": { ... } }

On failure:

{
  "help": "<request URL>",
  "success": false,
  "error": {
    "__type": "Validation Error",
    "message": "human-readable explanation",
    "fields": { "field": ["..."] }
  }
}

error.fields is present only on validation errors. null values are never serialised — absent fields are simply omitted.

Error types

`__type`	HTTP	When
`Validation Error`	400	Bad input — shape, types, unknown column, read-only resource
`Authorization Error`	403	Caller may not perform the action
`Not Found Error`	404	Resource not declared
`Conflict Error`	409	Unsupported in-place change (e.g. narrowing a column type)
`Internal Error`	500	Backend/transport failure

Authentication

Send the token in the Authorization header. The active provider is set by AUTH_TYPE:

`AUTH_TYPE`	Behaviour	Token
`ckan`	Delegates to CKAN `datastore_authorize` (TTL-cached)	CKAN API key
`jwt`	Verifies signature + `aud`/`iss`/`exp` locally	signed JWT
`anonymous`	Allows everything; no identity	none

Read actions (datastore_search, datastore_search_sql, datastore_info) may be attempted without a token; the provider decides. All write actions require a token (except under anonymous).

Endpoints at a glance

Method	Path	Summary
POST	`/api/3/action/datastore_create`	Declare a resource (and optionally seed rows)
POST	`/api/3/action/datastore_upsert`	Insert / update / upsert rows
POST	`/api/3/action/datastore_delete`	Delete rows, drop columns, or drop the table
GET	`/api/3/action/datastore_search`	Search a resource (streaming)
GET	`/api/3/action/datastore_search_sql`	Run a read-only SQL `SELECT` (streaming)
GET	`/datastore/dump/query`	Download the result of a SQL `SELECT` as a file
GET	`/api/3/action/datastore_info`	Schema + row stats for a resource
GET	`/datastore/dump/{resource_id}`	Download a whole resource (CSV/NDJSON/Parquet)
GET	`/` · `/health` · `/ready`	Welcome / liveness / readiness

`POST /api/3/action/datastore_create`

Declare a resource (table) and optionally seed it with rows. Re-declaring an existing resource adds columns and widens types (see below).

Two input shapes:

resource_id — table name only. Works under any AUTH_TYPE.
resource (dict) — creates a CKAN resource first (with url_type="datastore"), then writes the table. AUTH_TYPE=ckan only; rejected otherwise.

Body

Field	Type	Notes
`resource_id`	string	Target table. Provide this or `resource`.
`resource`	object	CKAN resource dict (ckan auth only).
`schema`	object	Frictionless Table Schema — the native column shape.
`fields`	array	Deprecated legacy `[{id, type, info}]`. Use `schema`.
`primary_key`	string \| array	Deprecated. Use `schema.primaryKey`.
`records`	array	Optional rows to seed.
`include_records`	bool	Echo written rows back in `result.records`.
`include_total`	bool	Run `COUNT(*)` and return `result.total`.
`force`	bool	Required to write a datastore-managed resource (see read-only guard).

Field types accept Frictionless canonical names (integer, number, string, boolean, date, datetime, time, object, array, geopoint, geojson, any) or SQL aliases (int4, bigint, varchar, text, float, numeric, bool, timestamp, json, …), normalised to canonical on storage. Each field may carry an info data dictionary (title, description, comment, example, unit, plus custom keys), stored verbatim and round-tripped by datastore_info.

Request

{
  "resource_id": "c6153a74-43cb-4edf-8bdf-bb664feca937",
  "schema": {
    "fields": [
      {"name": "auction_id", "type": "integer", "info": {"title": "Auction ID"}},
      {"name": "product_code", "type": "string"},
      {"name": "delivery_start", "type": "datetime"},
      {"name": "clearing_price_gbp_per_mwh", "type": "number", "info": {"unit": "GBP/MWh"}},
      {"name": "accepted", "type": "boolean"},
      {"name": "bidder_metadata", "type": "object"}
    ],
    "primaryKey": ["auction_id", "product_code"]
  },
  "records": [
    {"auction_id": 144, "product_code": "DCL", "delivery_start": "2025-11-04T16:00:00Z",
     "clearing_price_gbp_per_mwh": 47.82, "accepted": true,
     "bidder_metadata": {"unit_id": "DRAX-1"}}
  ]
}

Response — 200

{
  "help": "...",
  "success": true,
  "result": {
    "resource_id": "c6153a74-43cb-4edf-8bdf-bb664feca937",
    "fields": [{"id": "auction_id", "type": "integer", "info": {"...": "..."}}, "..."],
    "schema": {"fields": ["..."], "primaryKey": ["auction_id", "product_code"]},
    "primary_key": ["auction_id", "product_code"]
  }
}

records and total appear only when include_records / include_total are set.

`POST /api/3/action/datastore_upsert`

Write rows into an existing resource (declare it with datastore_create first).

Body

Field	Type	Notes
`resource_id`	string	Target table (required).
`records`	array	Rows to write.
`method`	string	`upsert` (default) · `insert` · `update`.
`include_records`	bool	Echo written rows in `result.records`.
`include_total`	bool	Return `result.total`.
`force`	bool	Required for a datastore-managed resource (see read-only guard).

upsert — MERGE on the table's stored primaryKey: match → update, miss → insert.
insert — append rows; no key check.
update — every row must match an existing key, else Not Found Error.

The table's unique_key (set at create) decides matching — the request body never carries it.

Request

{
  "resource_id": "c6153a74-43cb-4edf-8bdf-bb664feca937",
  "method": "upsert",
  "records": [
    {"auction_id": 144, "product_code": "DCL", "clearing_price_gbp_per_mwh": 48.05, "accepted": true}
  ]
}

Response — 200

{ "help": "...", "success": true,
  "result": {"resource_id": "c6153a74-43cb-4edf-8bdf-bb664feca937", "method": "upsert"} }

`POST /api/3/action/datastore_delete`

Three modes (filters and fields are mutually exclusive):

Drop table — omit both filters and fields.
Delete rows — filters (only rows matching every column: value).
Drop columns — fields (list of column names).

Body

Field	Type	Notes
`resource_id` / `id`	string	Target table (one required; `id` is a CKAN alias).
`filters`	object	Row filter. Omit (with no `fields`) → drop the table.
`fields`	array	Columns to drop. Mutually exclusive with `filters`.
`force`	bool	Required for a datastore-managed resource (see read-only guard).

Request

{ "resource_id": "c6153a74-43cb-4edf-8bdf-bb664feca937",
  "filters": {"auction_id": 144, "accepted": false} }

Response — 200

{ "help": "...", "success": true,
  "result": {"resource_id": "c6153a74-43cb-4edf-8bdf-bb664feca937"} }

On a column drop, result also carries schema — the Frictionless schema after the columns were removed — so you can confirm the new shape without a follow-up datastore_info:

{ "help": "...", "success": true,
  "result": {
    "resource_id": "c6153a74-43cb-4edf-8bdf-bb664feca937",
    "fields": ["bidder_metadata"],
    "schema": {"fields": [{"name": "auction_id", "type": "integer"}, "..."],
               "primaryKey": ["auction_id", "product_code"]}
  } }

`GET /api/3/action/datastore_search`

Parameterised search; the response is streamed (peak memory ≈ one row).

Query parameters

Name	Type	Default	Notes
`resource_id`	string	—	required
`filters`	JSON object	—	`{"col": value}` or `{"col": [v1, v2]}` (IN)
`q`	string \| JSON	—	full-text (string = all columns; object = per column)
`distinct`	bool	`false`
`plain`	bool	`true`	reserved (CKAN-compat)
`language`	string	`"english"`	reserved (CKAN-compat)
`limit`	int	`100`	capped by `SEARCH_RESULT_ROWS_MAX`
`offset`	int	`0`
`fields`	CSV	all	comma-separated columns to project
`sort`	string	—	`"col asc, col2 desc"`
`include_total`	bool	`true`	runs `COUNT(*)` when needed
`records_format`	string	`"objects"`	`objects` · `lists` · `csv` · `tsv`

Example

GET /api/3/action/datastore_search
    ?resource_id=c6153a74-43cb-4edf-8bdf-bb664feca937
    &filters={"product_code":"DCL","accepted":true}
    &sort=delivery_start desc
    &limit=100

Response (records_format=objects)

{
  "help": "...",
  "success": true,
  "result": {
    "fields": [{"id": "auction_id", "type": "integer"}, "..."],
    "records": [
      {"auction_id": 144, "product_code": "DCL", "clearing_price_gbp_per_mwh": 47.82}
    ],
    "total": 2,
    "_links": {
      "start": ".../datastore_search?resource_id=...&limit=100",
      "next":  ".../datastore_search?resource_id=...&limit=100&offset=100"
    }
  }
}

records_format=lists → each record is a positional array (column order = fields).
records_format=csv / tsv → records is a single text body (header row first), still inside the JSON envelope.
Paginate by following _links.next; end-of-data is an empty records array.

`GET /api/3/action/datastore_search_sql`

Run a single read-only SELECT / WITH statement and stream the result. Tables are referenced by resource_id; each is authorized individually, and functions are checked against the engine's allow-list. Include a LIMIT (required). To export the result as a file instead, use GET /datastore/dump/query.

Query parameters

Name	Type	Notes
`sql`	string	A single `SELECT`/`WITH`; no multi-statement, no DML/DDL.

Example

GET /api/3/action/datastore_search_sql?sql=
  SELECT product_code, AVG(clearing_price_gbp_per_mwh) AS avg_price
  FROM "c6153a74-43cb-4edf-8bdf-bb664feca937"
  WHERE accepted = true
  GROUP BY product_code
  LIMIT 1000

Response

{
  "help": "...",
  "success": true,
  "result": {
    "fields": [{"id": "product_code", "type": "string"}, {"id": "avg_price", "type": "number"}],
    "records": [{"product_code": "DCL", "avg_price": 47.82}]
  }
}

Safety: the schema rejects non-SELECT / multi-statement / unparseable SQL; the load-bearing guard is a read-only backend credential that physically refuses DML/DDL.

`GET /api/3/action/datastore_info`

Returns the column schema (including the info data dictionary, verbatim) plus row stats — a column-level metadata catalog without a side store.

Query parameters

Name	Type	Notes
`resource_id` / `id`	string	One required (`id` is a CKAN alias).

Response

{
  "help": "...",
  "success": true,
  "result": {
    "fields": [
      {"id": "auction_id", "type": "integer",
       "info": {"title": "Auction ID", "comment": "MANDATORY"}}
    ],
    "schema": {"fields": ["..."], "primaryKey": ["auction_id", "product_code"]},
    "meta": {
      "resource_id": "c6153a74-43cb-4edf-8bdf-bb664feca937",
      "primary_key": ["auction_id", "product_code"],
      "total": 18420
    }
  }
}

`GET /datastore/dump/{resource_id}`

Download an entire resource. Pick the format with ?format=csv (default), gzip, ndjson, or parquet.

csv / gzip / ndjson — 302 redirect to a signed GCS URL, at any size. Shards from a large export are stitched into one object server-side, so the bytes go straight from storage to the client (resumable, no server bandwidth, no server CPU). gzip is compressed by BigQuery at export time.
parquet — 302 when the export is a single file. Parquet shards can't be merged (footer + magic bytes), so a sharded export instead returns 200 with a zip of the parts (Content-Type: application/zip, members named <resource_id>_NN.parquet). Entries are stored, not deflated — parquet is already compressed. This is the one download the server streams itself, so it has no Content-Length and can't be resumed; unzip before querying.

Requires read permission on the resource and a configured export bucket (BIGQUERY_EXPORT_BUCKET).

query is a reserved name on this route — /datastore/dump/query is the SQL download endpoint below, so a resource literally named query can't be dumped by this URL.

`GET /datastore/dump/query`

Download the result of a SQL SELECT as a single file — filtered downloads at any size. Same validation as datastore_search_sql (single SELECT/WITH, per-table auth, function allow-list); the response is the file itself, not the CKAN envelope.

Query parameters

Name	Type	Notes
`sql`	string	A single `SELECT`/`WITH`; no multi-statement, no DML/DDL. `LIMIT` optional.
`format`	string	`csv` (default) \| `gzip` (gzipped CSV) \| `ndjson` \| `parquet`.

Example

GET /datastore/dump/query
    ?sql=SELECT * FROM "c6153a74-43cb-4edf-8bdf-bb664feca937" WHERE accepted = true
    &format=csv

Response

Identical to /datastore/dump/{resource_id} above:

csv / gzip / ndjson — 302 to a signed GCS URL at any size (shards are composed into one object). The URL expires after BIGQUERY_EXPORT_URL_EXPIRY_HOURS (default 1h).
parquet — 302 for one file; a sharded export returns 200 with a streamed zip of the parts, members named query_NN.parquet.

Rules (vs the JSON API)

LIMIT is optional — absent exports the full result set; present it is honored as written, and the SEARCH_RESULT_ROWS_MAX cap does not apply (OFFSET without LIMIT is rejected).
Repeated downloads of the same SQL are served from a GCS cache until any referenced table changes; SQL calling non-deterministic functions (now(), current_date, …) re-exports on every request.
Row order in the file follows the SQL's ORDER BY when it sorts on output columns (BigQuery preserves it across shards, which are composed in order). Without one, results fall back to _id order when the query outputs _id (e.g. SELECT *) — the same order the JSON API pages in; otherwise order is undefined.
Requires a configured export bucket (BIGQUERY_EXPORT_BUCKET).

Health

All return the CKAN envelope.

Method	Path	Result
GET	`/`	`{"message": "<APP_MESSAGE>"}`
GET	`/health`	`{"status": "ok"}` — liveness; always 200 while the process runs
GET	`/ready`	`{"status": "ready"}` — 200 when both engines pass `healthcheck()`; `503` (`{"status": "not_ready"}`) otherwise

Read-only resource guard

Under AUTH_TYPE=ckan, datastore_create, datastore_upsert, and datastore_delete refuse to write a resource whose CKAN record carries url_type="datastore" unless the request sets force: true:

{ "help": "...", "success": false,
  "error": {"__type": "Validation Error",
            "message": "Cannot update a read-only resource. Use \"force\" to force update."} }

This mirrors CKAN's protection against clobbering datastore-managed data. It is gated on AUTH_TYPE=ckan and skipped entirely under other providers.

Navigation Menu

Uh oh!

Datastore API Reference

Conventions

Response envelope

Error types

Authentication

Endpoints at a glance

`POST /api/3/action/datastore_create`

Body

Request

Response — 200

`POST /api/3/action/datastore_upsert`

Body

Request

Response — 200

`POST /api/3/action/datastore_delete`

Body

Request

Response — 200

`GET /api/3/action/datastore_search`

Query parameters

Example

Response (records_format=objects)

`GET /api/3/action/datastore_search_sql`

Query parameters

Example

Response

`GET /api/3/action/datastore_info`

Query parameters

Response

`GET /datastore/dump/{resource_id}`

`GET /datastore/dump/query`

Query parameters

Example

Response

Rules (vs the JSON API)

Health

Read-only resource guard

Uh oh!

FilesExpand file tree

API.md

Latest commit

History

API.md

File metadata and controls

Datastore API Reference

Conventions

Response envelope

Error types

Authentication

Endpoints at a glance

POST /api/3/action/datastore_create

Body

Request

Response — 200

POST /api/3/action/datastore_upsert

Body

Request

Response — 200

POST /api/3/action/datastore_delete

Body

Request

Response — 200

GET /api/3/action/datastore_search

Query parameters

Example

Response (records_format=objects)

GET /api/3/action/datastore_search_sql

Query parameters

Example

Response

GET /api/3/action/datastore_info

Query parameters

Response

GET /datastore/dump/{resource_id}

GET /datastore/dump/query

Query parameters

Example

Response

Rules (vs the JSON API)

Health

Read-only resource guard

`POST /api/3/action/datastore_create`

`POST /api/3/action/datastore_upsert`

`POST /api/3/action/datastore_delete`

`GET /api/3/action/datastore_search`

`GET /api/3/action/datastore_search_sql`

`GET /api/3/action/datastore_info`

`GET /datastore/dump/{resource_id}`

`GET /datastore/dump/query`