SingleStore Managed Service

query/rows

HTTP Method

Endpoint URL

Description

POST

/api/v1/query/rows

Executes a SQL statement and returns result sets; typically used for the SELECT statement for which result sets are expected. The result sets contain column names mapped to row values in a single field.

Request Headers

Header

Description

Authorization

Specifies the authentication information of the user agent. This must be set to Basic followed by a space and then by the Base-64 encoded username:password string. For example, the Authorization header of demo:Afu4XjzB1ns would appear as follows.

Authorization: Basic ZGVtbzpBZnU0WGp6QjFucw==

Content-Type

Represents the media type (MIME) of the body of the request and must be set to application/json.

Request Body

The request body is a JSON object that specifies the SQL statement to execute and the statement context.

Field

Type

Description

sql

String

The SQL statement to execute.

args

Array

(Optional) A list of arguments to be used in the SQL statement. The command uses question marks (?) for placeholders, which will be replaced by the specified arguments during execution. The command must have exactly as many placeholders as arguments, or the request will fail. Example:

{"sql": "SELECT * FROM tbl WHERE col1 = ? OR col2 = ?", "args": ["Hello", 2]}

args[x]

Any

(Optional) An argument to be used in the command. This argument will be parsed into a golang type based on the default JSON parsing rules and then passed to the go sql driver for safe interpolation.

database

String

(Optional) The database on which the SQL statement must be executed. The value is case-sensitive.

Response

For successful requests, the response will be a JSON object in the message body along with the HTTP status code 200 OK.

Field

Type

Description

results

Array

An array of objects describing each result set.

results[x]

Object

An object describing a single result set.

results[x].rows

Array

An array of tuples, one for each row in the result set.

results[x].rows[y]

Array

A single row in the result set, encoded as an array of column values, matching the column order as specified by results[x].columns.

results[x].rows[y].[z]

Any

A single value of a row converted dynamically into a JSON type that best fits the source data type. See HTTP API Data Type Conversion for more details.

error

Object

This key is specified if an error occurs while streaming the results. Because it is possible to encounter an error after sending the response headers, a valid client should always check this field before processing a response.

error.code

Integer

The error code.

error.message

String

The error message.

On request failure, an HTTP status code corresponding to the error will be returned. See HTTP API Error Handling for more information.

Examples (Shell)
Database Schema

Before getting started with the examples, you can optionally create the following database schema and populate the given data. This will allow you to copy and paste the code samples and emulate the responses demonstrated in this section.

CREATE DATABASE publications;
USE publications;
DROP TABLE IF EXISTS books;
CREATE TABLE books (book_id INT AUTO_INCREMENT PRIMARY KEY, title VARCHAR(255) NOT NULL, description VARCHAR(255), author_id INT NOT NULL, genre VARCHAR (20) NOT NULL, rating JSON NOT NULL, published_date DATE, standard_number CHAR(11) NOT NULL, KEY (title) USING HASH);
DROP TABLE IF EXISTS authors; 
CREATE TABLE authors(author_id INT AUTO_INCREMENT PRIMARY KEY, author_name VARCHAR(50) NOT NULL, bio VARCHAR(255));
INSERT INTO books (title, author_id, genre, rating, standard_number) VALUES ('Dangerous Spindle', 3, 'Crime Thriller', '{"Noveliu" : "4.5/5", "AllBooksWeb" : "4.4/5"}', '728-2397-903-3'), ('The Owl in the Abyss', 1, 'Fantasy', '{"Noveliu" : "4/5", "AllBooksWeb" : "4.2/5"}', '738-0831-223-8'), ('Judged for Error', 2, 'Historical Fiction', '{"Noveliu" : "4.5/5", "AllBooksWeb" : "4.5/5"}', '257-2684-239-3');
INSERT INTO publications.authors (author_name) VALUES ('Kegan Roslyn'), ('Toby Lynsey'), ('Lacey Desmond');
Query data from a table
Request Data
{"sql": "SELECT title, genre FROM books WHERE book_id = 1", "database": "publications"}
HTTP Request
curl -H "Content-Type: application/json" --data '{"sql": "SELECT title, genre FROM books WHERE book_id = 1", "database": "publications"}' http://svc-10444e3f-afeb-46be-b186-a8c64b99f726-dml.aws-ireland-1.svc.singlestore.com/api/v1/query/rows 
HTTP Response
{
  "results": [
    {
      "rows": [
        {
          "title": "Dangerous Spindle",
          "genre": "Crime Thriller"
        }
      ]
    }
  ]
}
Query two tables using INNER JOIN
Request Data
{"sql": "SELECT books.title, authors.author_name, books.genre, books.rating FROM books INNER JOIN authors ON books.author_id = authors.author_id AND books.genre = (?) ORDER BY books.rating DESC", "args": ["Crime Thriller"], "database": "publications"}
HTTP Request
curl -H "Content-Type: application/json" --data '{"sql": "SELECT books.title, authors.author_name, books.genre, books.rating FROM books INNER JOIN authors ON books.author_id = authors.author_id AND books.genre = (?) ORDER BY books.rating DESC", "args": ["Crime Thriller"], "database": "publications"}' http://svc-10444e3f-afeb-46be-b186-a8c64b99f726-dml.aws-ireland-1.svc.singlestore.com/api/v1/query/rows
HTTP Response
{
  "results": [
    {
      "rows": [
        {
          "title": "Dangerous Spindle",
          "author_name": "Lacey Desmond",
          "genre": "Crime Thriller",
          "rating": {
            "AllBooksWeb": "4.4/5",
            "Noveliu": "4.5/5"
          }
        }
      ]
    }
  ]
}
Supplementary Reference

When using cURL to invoke SingleStore’s HTTP APIs, you can issue the JSON payload of the HTTP POST request from a file instead of providing it directly in the request, as shown below.

Query a table by issuing the HTTP POST request’s JSON payload from a file
Request data saved in a JSON file
{
    "sql": "SELECT title, genre, rating FROM books WHERE genre = 'Crime Thriller' OR 'Sci-fi' ORDER BY rating DESC", 
    "database": "publications"
}
HTTP request with the JSON payload issued from the JSON file
/* The JSON payload for the HTTP request is supplied from the query-rows.json file */

curl -H "Content-Type: application/json" --data '@./query-rows.json' http://svc-10444e3f-afeb-46be-b186-a8c64b99f726-dml.aws-ireland-1.svc.singlestore.com/api/v1/query/rows
HTTP response
{
  "results": [
    {
      "rows": [
        {
          "title": "Dangerous Spindle",
          "genre": "Crime Thriller",
          "rating": {
            "AllBooksWeb": "4.4/5",
            "Noveliu": "4.5/5"
          }
        }
      ]
    }
  ]
}