Data API Reference: Miscellaneous queries¶

run_sql¶

run_sql is used to run arbitrary SQL statements. Multiple SQL statements can be separated by a ;, however, only the result of the last sql statement will be returned.

An example:

POST /v1/query HTTP/1.1
Content-Type: application/json
Authorization: Bearer <auth-token> # optional if cookie is set
X-Hasura-Role: admin

{
    "type": "run_sql",
    "args": {
        "sql": "CREATE UNIQUE INDEX ON films (title);"
    }
}

While run_sql lets you run any SQL, it tries to ensure that the data microservice’s state (relationships, permissions etc.) is consistent. i.e, you cannot drop a column on which any metadata is dependent on (say a permission or a relationship). The effects, however, can be cascaded.

For example, if we were to drop ‘bio’ column from the article table (let’s say the column is used in some permission), you would see an error.

POST /v1/query HTTP/1.1
Content-Type: application/json
Authorization: Bearer <auth-token> # optional if cookie is set
X-Hasura-Role: admin

{
    "type": "run_sql",
    "args": {
        "sql": "ALTER TABLE author DROP COLUMN name"
    }
}

HTTP/1.1 400 BAD REQUEST
Content-Type: application/json

{
    "path": "$.args",
    "error": "cannot drop due to the following dependent objects : permission author.user.select"
}

We can however, cascade these changes.

POST /v1/query HTTP/1.1
Content-Type: application/json
Authorization: Bearer <auth-token> # optional if cookie is set
X-Hasura-Role: admin

{
    "type": "run_sql",
    "args": {
        "sql": "ALTER TABLE author DROP COLUMN bio",
        "cascade" : true
    }
}

HTTP/1.1 200 OK
Content-Type: application/json

{
    "result_type": "CommandOk"
}

With the above query, the dependent permission is also dropped. In general, the SQL operations that will affect hasuradb objects are

Dropping columns
Dropping tables
Altering types of columns

In case of 1 and 2, the dependent objects (if any) can be dropped using cascade. However, when altering type, if any objects are affected, the change cannot be cascaded. So, those dependent objects have to be manually dropped before the sql statement.

run_sql can only be executed by a user with the admin role. This is deliberate as it is hard to enforce any sort of permissions on arbitrary sql. If you find yourselves in the need of using run_sql to run custom DML queries, consider creating a view. You can now define permissions on that particular view for various roles.

Note

Currently, renames of tables and columns are not allowed in the SQL statement.

Syntax¶

Key	Required	Schema	Description
sql	true	String	The sql to be executed
cascade	false	Boolean	When set to `true`, the effect (if possible) is cascaded to any hasuradb dependent objects (relationships, permissions, templates).

Response¶

The response is a JSON Object with the following structure.

Key	Always present	Schema	Description
result_type	true	String	One of “CommandOk” or “TuplesOk”
result	false	`[[Text]]` (An array of rows, each row an array of columns)	This is present only when the `result_type` is “TuplesOk”

Note

The first row in the result (when present) will be the names of the columns.

Use cases¶

To execute DDL operations that are not supported by the console (like indexes).
Run custom DML queries from backend microservices instead of installing libraries to speak to Postgres.

More examples¶

A query returning results.

POST /v1/query HTTP/1.1
Content-Type: application/json
Authorization: Bearer <auth-token> # optional if cookie is set
X-Hasura-Role: admin

{
    "type": "run_sql",
    "args": {
        "sql": "select user_id, first_name from author limit 2;"
    }
}

HTTP/1.1 200 OK
Content-Type: application/json

{
    "result_type": "TuplesOk",
    "result": [
        [
            "user_id",
            "first_name"
        ],
        [
            "1",
            "andre"
        ],
        [
            "2",
            "angela"
        ]
    ]
}

« previous | next »

Was this page helpful?