Query

Create and manage queries.

Part of the API reference collection

This page is part of the Crunchy Bridge API reference, and primarily meant to act as an exhaustive guide for technical integrations which are already in progress. To understand the basics of using the API, see API concepts and getting started.

Create query

Create (start executing) a query on a cluster.

The functionality of this endpoint is currently limited to performing in-band queries which are constrained by a 25 second statement timeout, and an API request's normal timeout of 30 second beyond that.

Queries default to readonly to avoid accidental mutation. Set allow_write to true to make queries read/write.

Callers can set async to true to have the query run asynchronously instead. Asynchronous queries are allowed more time to run, but their status must be polled on the get endpoint to check for completion.

POST /queries

Request body schema

Content type: application/json

Name	Required	Type	Description
`sql`	✔	`string`	SQL being executed as part of the query.
`allow_write`		`boolean`	Set to `true` to allow a query to be read/write. Queries default to readonly to avoid accidental mutation.
`async`		`boolean`	Specify `true` to run the query asynchronously. No results are available immediately, but its completion or error status can be polled via the get API until they're available.
`cluster_id`		`string` in EID format	Unique ID of the cluster to query.
`created_at`		`string` of date/time formatted as RFC 3339
`database_name`		`string`	Name of the database to query.
`expires_at`		`string` of date/time formatted as RFC 3339
`is_insight_query`		`boolean`	Whether this query is a DB insights query. DB insights queries require fewer permissions to perform as they're defined internally by us. Using this parameter requires that the request be signed by Dashboard's private key.
`skip_tx`		`boolean`	Skips opening a transaction when running the query. This should generally not be used, but is required for certain Postgres operations that can't be run in a transaction like creating an index concurrent or creating a logical replication subscription. This option should be considered dangerous because the query API's normal constrain in making queries read-only can't be set. All queries run with this option are granted full read/write capabilities. This option is not supported in conjunction with `async`. All queries that skip use of transaction must run synchronously.

Example request body

{
    "allow_write": false,
    "async": false,
    "cluster_id": "rvf73a77ozfsvcttryebfrnlem",
    "created_at": null,
    "database_name": null,
    "expires_at": null,
    "is_insight_query": false,
    "skip_tx": false,
    "sql": "SELECT 1;"
}

cURL example

curl -X POST https://api.crunchybridge.com/queries
    -H "Authorization: Bearer $CRUNCHY_API_KEY"
    -H "Content-Type: application/json"
    -d '{"allow_write":false,"async":false,"cluster_id":"rvf73a77ozfsvcttryebfrnlem","created_at":null,"database_name":null,"expires_at":null,"is_insight_query":false,"skip_tx":false,"sql":"SELECT 1;"}'

Response

Status: 201

A query and its result.

Content type: application/json

Name	Nullable	Type	Description
`id`		`string` in EID format	Unique ID of the query.
`allow_write`		`boolean`	Whether the query is allowed to write to the database.
`async`		`boolean`	Indicates whether this is a query running asynchronously (created by specifying the `async` parameter).
`cluster_id`		`string` in EID format	The ID of cluster that the query is being executed on.
`database_name`	✔	`string`	Name of the database to query. Only populated if not default.
`finished_at`	✔	`string` of date/time formatted as RFC 3339	Time at which the query finished, either successfully or in error.
`has_extended_result`	✔	`boolean`	Indicates that the query has an extended result set available that can be requested separately from the results endpoint. Returns `null` unless the query has succeeded.
`is_blocked`		`boolean`	Set to true when a `pending` query will not be scheduled for run because either its account or cluster is already at its simultaneously running query limit, or another query or its associated saved query is already running.
`last_error_message`	✔	`string`	In case of a failure, the message of the last error that occurred. If the query's `status` is set to `failure` then the failure is permanent. If `status` is `pending` or `running` then it may be set to the message of the last error that occurred, but which is considered intermittent so the query's been scheduled to run again (`pending`) or is already running again (`running`). Always `nil` for synchronous queries -- errors on synchronous queries are returned as non-`200`` API errors instead.
`last_run_duration_seconds`	✔	`number`	The total number of seconds that it took to perform the query's last run. Includes fractional seconds as well. This will be populated for either a query that succeeded or one that failed, so a caller may also want to check `status`.
`next_run_at`	✔	`string` of date/time formatted as RFC 3339	The next time at which an asynchronous query is scheduled to run. Queries are eligible to be run immediately when they're first created, but may be rescheduled in the future in case of failure. Next run time indicates only when a query is next eligible to be run, and queries aren't guaranteed to start running immediately at this time -- their actual start time may depend on worker capacity and scheduler run timing. `null` for synchronous queries and asynchronous queries that are finished.
`num_failures`	✔	`integer`	The number of failures in the case of an asynchronous query. Always `null` for a synchronous query.
`result`		`array` of `array`	The result of the query as a two-dimensional array of the returned rows and each field value within each. This field is always `null` when listing asynchronous queries through the query list endpoint.
`result_fields`		`array` of `array`	Metadata on each field returned in the results.
`sql`		`string`	SQL being executed as part of the query.
`status`		enum `string`	The status of the query. Clients should wait until this field reads `failed` or `succeeded`, interpret the result, and stop polling. Enum `canceled`, `failed`, `pending`, `running`, or `succeeded`.

Example

{
    "allow_write": false,
    "async": false,
    "cluster_id": "rvf73a77ozfsvcttryebfrnlem",
    "database_name": null,
    "finished_at": "2021-07-11T01:02:03Z",
    "has_extended_result": false,
    "id": "agbk2tous2pmi2ehnjbvkxo7gi",
    "is_blocked": false,
    "last_error_message": null,
    "last_run_duration_seconds": 0.1,
    "next_run_at": null,
    "num_failures": null,
    "result": [
        [
            1
        ],
        [
            2
        ],
        [
            3
        ]
    ],
    "result_fields": [
        {
            "name": "unnest"
        }
    ],
    "sql": "SELECT unnest(array[1,2,3]);",
    "status": "succeeded"
}