SingleStore DB

Enabling the HTTP API

SingleStore’s HTTP APIs are accessible via a built-in HTTP server. The HTTP API is disabled by default and can be easily enabled using engine variables. When the HTTP API is enabled, it will be hosted on a port that is different from the SingleStore DB port. Following are the steps to enable the HTTP API service.

Step 1: Enable the WebSocket Proxy

To enable the WebSocket proxy on the required nodes, you can use SingleStore DB Toolbox commands on a host where SingleStore DB Toolbox is installed and configured. 

Before enabling WebSocket proxy, you need to obtain the MemSQL ID of the nodes for which you want to set the https_proxy_port or the http_proxy_port variable. SingleStore recommends enabling the HTTP API on every aggregator node in the cluster, so you will likely need the MemSQL ID of all aggregator nodes. To perform this action, run the following command.

sdb-admin list-nodes --role aggregator -q

Then use sdb-admin update config to set either the https_proxy_port or the http_proxy_port variable to any free port available and specify the MemSQL ID obtained for the aggregator nodes in the previous command.

For HTTPS:

sdb-admin update-config --memsql-id <nodeID> --set-global --key https_proxy_port --value <port>

For HTTP:

sdb-admin update-config --memsql-id <nodeID> --set-global --key http_proxy_port --value <port> 

Note that https_proxy_port and http_proxy_port variables are mutually exclusive and the proxy will fail to start if both are set. The https_proxy_port variable requires SSL to be enabled.

Step 2: Enable the API Endpoints

To make the API endpoints accessible on the HTTP server, run the following SingleStore DB Toolbox command. SingleStore recommends enabling the HTTP API on every aggregator node in the cluster, so you will likely need to specify the ID of each aggregator node.

sdb-admin update-config --memsql-id <nodeID> --set-global --key "http_api" --value "ON" 

Important

  • The variables used for enabling the HTTP API must be configured on individual nodes. SingleStore recommends enabling the HTTP API on every aggregator node in the cluster.

  • SingleStore recommends using sdb-admin update-config to set the variables involved in HTTP API configuration to make sure the variable values persist through server restart. This command when used with the --set-global flag allows the variable values to take effect immediately (assuming the node is running) in addition to when the node starts. For more information, refer to the sdb-admin update-config command reference.

  • SingleStore recommends setting the https_proxy_port or the http_proxy_port variable to the same value across the cluster.

  • The HTTP APIs should NOT be enabled on leaf nodes.

Optimizing the HTTP API Configuration

After enabling the API, you can tweak the following connection-related settings for optimum performance of the database. 

  • Use the http_api_pool_capacity limits the maximum impact of the HTTP server on the database server by limiting the maximum number of connections per user.

  • Use the http_api_pool_max_idle variable to specify the maximum number of connections that can be open in each pool at any point in time. This setting helps reduce latency by holding database connections open.

For more information, refer to Non-Sync Variables List.

Testing the HTTP API

After HTTP API is enabled, you can call the GET /ping API request to verify that the HTTP service is running and connectable. To perform this action using cURL, invoke a GET request to the /ping endpoint with no parameters, as shown below.

curl https://localhost:8080/ping

If the HTTP API service is available and is able to respond to requests, you will receive the following response along with a 200 OK status code, indicating a successful HTTP response.

pong

If an error occurs, you will receive one of the HTTP response status codes listed here along with the error description in the response body. For more information, refer to the HTTP API Endpoint Reference.

The GET /ping request can only verify whether the HTTP service is running and connectable. To verify that the database can receive queries, use the /exec and /query endpoints as shown below.

The following cURL command invokes a POST API request to the /query/rows endpoint. (Note that you need to replace root:password in the HTTP request with your SingleStore username:password string.)

curl -H "Content-Type: application/json" --data '{"sql": "select 1+1"}' https://root:password@localhost:8080/api/v1/query/rows

If the API call is successful, you should see the following results along with a 200 OK status line.

{"results":[{"rows":[{"1+1":2
}]}]}

If an error occurs, you will receive one of the HTTP response status codes listed here along with the error description in the response body. For more information, refer to the HTTP API Endpoint Reference.

If you format the JSON output with a formatting tool, the results would appear as follows. (Recommended JSON formatting tools: jq, JSONLint)

{
  "results": [
    {
      "rows": [
        {
          "1+1": 2
        }
      ]
    }
  ]
}

Note: The GET /ping request should be used in automated health checks. To verify specific health metrics, use the /exec and /query endpoints.