SQL Resource

The SQL resource represents a database connection.

SQL Connection Interface

The following endpoints provide a simple REST API interface for executing SQL queries on servers and services in MaxScale.

This document uses the :id value in the URL to represent a connection ID and the :query_id to represent a query ID. These values do not need to be manually added as the relevant links are returned in the request body of each endpoint.

The endpoints use JSON Web Tokens to uniquely identify open SQL connections. A connection token can be acquired with a POST /v1/sql request and can be used with the POST /v1/sql/:id/query, GET /v1/sql/:id/results/:query_id and DELETE /v1/sql endpoints. All of these endpoints accept a connection token in the token parameter of the request:

POST /v1/sql/query?token=eyJhbGciOiJIUzI1NiJ9.eyJhdWQiOiJhZG1pbiIsImV4cCI6MTU4MzI1NDE1MSwiaWF0IjoxNTgzMjI1MzUxLCJpc3MiOiJtYXhzY2FsZSJ9.B1BqhjjKaCWKe3gVXLszpOPfeu8cLiwSb4CMIJAoyqw

In addition to request parameters, the token can be stored in cookies in which case they are automatically used by the REST API. For more information about token storage in cookies, see the documentation for POST /v1/sql.

Request Parameters

All of the endpoints that operate on a single connection support the following request parameters. The GET /v1/sql and GET /v1/sql/:id endpoints are an exception as they ignore the current connection token.

• token

• The connection token to use for the request. If provided, the value is unconditionally used even if a cookie with a valid token exists.

Get one SQL connection

GET /v1/sql/:id

Response

Response contains the requested resource.

Status: 200 OK

{
    "data": {
        "id": "5",
        "links": {
            "related": "http://localhost:8989/v1/sql/5/queries/",
            "self": "http://localhost:8989/v1/sql/5/"
        },
        "type": "sql"
    },
    "links": {
        "self": "http://localhost:8989/v1/sql/5/"
    },
    "meta": {
        "token": "eyJhbGciOiJIUzI1NiJ9.eyJhdWQiOiI1IiwiZXhwIjoxNjIwMjM1Mzc3LCJpYXQiOjE2MjAyMDY1NzcsImlzcyI6Im14cy1xdWVyeSJ9.2CJ8DsEPbGlvs2DrBUC6FJA64VMSU8kbX1U4FSu2-OY"
    }
}

Get all SQL connections

GET /v1/sql

Response

Response contains a resource collection with all the open SQL connections.

Status: 200 OK

{
    "data": [
        {
            "id": "10",
            "links": {
                "related": "http://localhost:8989/v1/sql/10/queries/",
                "self": "http://localhost:8989/v1/sql/10/"
            },
            "type": "sql"
        },
        {
            "id": "11",
            "links": {
                "related": "http://localhost:8989/v1/sql/11/queries/",
                "self": "http://localhost:8989/v1/sql/11/"
            },
            "type": "sql"
        }
    ],
    "links": {
        "self": "http://localhost:8989/v1/sql/"
    }
}

Open SQL connection to server

POST /v1/sql

The request body must be a JSON object consisting of the following fields:

• target

• The object in MaxScale to connect to. This is a mandatory value and the given value must be the name of a valid server, service or listener in MaxScale.

• user

• The username to use when creating the connection. This is a mandatory value.

• password

• The password for the user. This is a mandatory value.

• db

• The default database for the connection. By default the connection will have no default database.

• timeout

• Connection timeout in seconds. The default connection timeout is 10 seconds. This controls how long the SQL connection creation can take before an error is returned.

Here is an example request body:

{
    "user": "jdoe",
    "password": "my-s3cret",
    "target": "server1",
    "db": "test",
    "timeout": 15
}

The response will contain the new connection with the token stored at meta.token. If the request uses the persist=yes request parameter, the token is stored in cookies instead of the metadata object and the response body will not contain the token.

The location of the newly created connection will be stored at links.self in the response body as well as in the Location header.

The token must be given to all subsequent requests that use the connection. It must be either given in the token parameter of a request or it must be stored in the cookies. If both a token parameter and a cookie exist at the same time, the token parameter will be used instead of the cookie.

Request Parameters

This endpoint supports the following request parameters.

• persist

• Store the connection token in cookies instead of returning it as the response body.

  This parameter expects only one value, yes, as its argument. When persist=yes is set, the token is stored in the conn_id_sig_<id> cookie where the <id> part is replaced by the ID of the connection.

• max-age

• Sets the connection token maximum age in seconds. The default is max-age=28800. Only positive values are accepted and if a non-positive or a non-integer value is found, the parameter is ignored. Once the token age exceeds the configured maximum value, the token can no longer be used and a new connection must be created.

Response

Connection was opened:

Status: 201 Created

{
    "data": {
        "id": "5",
        "attributes": {
            "thread_id": 12,
            "seconds_idle": 5
        }
        "links": {
                 // The "related" endpoint is the URL to the query endpoint for this connection.
            "related": "http://localhost:8989/v1/sql/5/queries/",
            "self": "http://localhost:8989/v1/sql/5/"
        },
        "type": "sql"
    },
    "links": {
        "self": "http://localhost:8989/v1/sql/5/"
    },
    "meta": {
        "token": "eyJhbGciOiJIUzI1NiJ9.eyJhdWQiOiI1IiwiZXhwIjoxNjIwMjM1Mzc3LCJpYXQiOjE2MjAyMDY1NzcsImlzcyI6Im14cy1xdWVyeSJ9.2CJ8DsEPbGlvs2DrBUC6FJA64VMSU8kbX1U4FSu2-OY"
    }
}

Missing or invalid payload:

Status: 400 Bad Request

Close an opened SQL connection

DELETE /v1/sql/:id

Response

Connection was closed:

Status: 204 No Content

Missing or invalid connection token:

Status: 400 Bad Request

Reconnect an opened SQL connection

POST /v1/sql/:id/reconnect

Reconnects an existing connection. This can also be used if the connection to the backend server was lost due to a network error.

The connection will use the same credentials that were passed to the POST /v1/sql endpoint. The new connection will still have the same ID in the REST API but will be treated as a new connection by the database. A reconnection re-initializes the connection and resets the session state. Reconnections cannot take place while a transaction is open.

Response

Reconnection was successful:

Status: 204 No Content

Reconnection failed or connection is already in use:

Status: 503 Service Unavailable

Missing or invalid connection token:

Status: 400 Bad Request

Clone an existing SQL connection

POST /v1/sql/:id/clone

Clones an existing connection. This is done by opening a new connection using the credentials and configuration from the given connection.

Request Parameters

This endpoint supports the same request parameters as the POST /v1/sql endpoint.

Response

The response is identical to the one in the POST /v1/sql endpoint. In addition, this endpoint can return the following responses.

Connection is already in use:

Status: 503 Service Unavailable

Missing or invalid connection token:

Status: 400 Bad Request

Execute SQL query

POST /v1/sql/:id/queries

The request body must be a JSON object with the value of the sql field set to the SQL to be executed:

{
    "sql": "SELECT * FROM test.t1",
    "max_rows": 1000
}

The request body must be a JSON object consisting of the following fields:

• sql

• The SQL to be executed. If the SQL contain multiple statements, multiple results are returned in the response body.

• max_rows

• The maximum number of rows returned in the response. By default this is 1000 rows. Setting the value to 0 means no limit. Any extra rows in the result will be discarded.

By default, the complete result is returned in the response body. If the SQL query returns more than one result, the results array will contain all the results.

The results array can have three types of objects: resultsets, errors, and OK responses.

• A resultset consists of the data field with the result data stored as a two dimensional array. The names of the fields are stored in an array in the fields field. These types of results will be returned for any operation that returns rows (i.e. SELECT statements)

{
    "data": {
        "attributes": {
            "results": [
                {
                    "data": [
                        [
                            1
                        ],
                        [
                            2
                        ],
                        [
                            3
                        ]
                    ],
                    "fields": [
                        "id"
                    ]
                }
            ],
            "sql": "select * from t1"
        },
        "id": "9-1",
        "type": "queries"
    },
    "links": {
        "self": "http://localhost:8989/v1/sql/9/queries/9-1/"
    }
}

• An error consists of an object with the errno field set to the MariaDB error code, the message field set to the human-readable error message and the sqlstate field set to the current SQLSTATE of the connection.

{
    "data": {
        "attributes": {
            "results": [
                {
                    "errno": 1064,
                    "message": "You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'table t1' at line 1",
                    "sqlstate": "42000"
                }
            ],
            "sql": "select syntax_error from table t1"
        },
        "id": "4-1",
        "type": "queries"
    },
    "links": {
        "self": "http://localhost:8989/v1/sql/4/queries/4-1/"
    }
}

• An OK response is returned for any result that completes successfully but not return rows (e.g. an INSERT or UPDATE statement). The affected_rows field contains the number of rows affected by the operation, the last_insert_id contains the auto-generated ID and the warnings field contains the number of warnings raised by the operation.

{
    "data": {
        "attributes": {
            "results": [
                {
                    "affected_rows": 0,
                    "last_insert_id": 0,
                    "warnings": 0
                }
            ],
            "sql": "drop table t1"
        },
        "id": "6-1",
        "type": "queries"
    },
    "links": {
        "self": "http://localhost:8989/v1/sql/6/queries/6-1/"
    }
}

It is also possible for the fields of the error response to be present in the resultset response if the result ended with an error but still generated some data. Usually this happens when query execution is interrupted but a partial result was generated by the server.

Response

Query successfully executed:

Status: 201 Created

{
    "data": {
        "attributes": {
            "results": [
                {
                    "affected_rows": 0,
                    "last_insert_id": 0,
                    "warnings": 0
                }
            ],
            "sql": "drop table t1"
        },
        "id": "6-1",
        "type": "queries"
    },
    "links": {
        "self": "http://localhost:8989/v1/sql/6/queries/6-1/"
    }
}

Invalid payload or missing connection token:

Status: 400 Bad Request

Fatal connection error:

Status: 503 Service Unavailable

• If the API returns this response, the connection to the database server was lost. The only valid action to take at this point is to close it with the DELETE /v1/sql/:id endpoint.