Sign inTry Free

query/tuples

On this page

Endpoint URL

/api/v2/query/tuples

HTTP Method

POST

Description

Executes a SQL statement and returns result sets along with the schema; typically used for the SELECT statement for which result sets are expected. The result sets contain rows and columns in separate fields with the schema displayed for each column.

Request Headers

Header

Description

Authorization

Specifies the authentication information of the user agent. For more information, see Data API Authentication.

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. The request body size must not exceed 1MB.

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].columns

Array

An array of objects describing each column.

results[x].columns[y]

Object

An object describing a single column.

results[x].columns[y].name

String

The name of the column.

results[x].columns[y].dataType

String

The data type of the column.

results[x].columns[y].nullable

Boolean

For nullable values, "true" is returned; otherwise, "false".

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 dynamically converted into a JSON type that best fits the source data type.

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 Data 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"}' https://root:password@localhost:8080/api/v2/query/tuples

HTTP Response

{
  "results": [
    {
      "columns": [
        {
          "author_name": "title",
          "dataType": "VARCHAR",
          "nullable": false
        },
        {
          "author_name": "genre",
          "dataType": "VARCHAR",
          "nullable": false
        }
      ],
      "rows": [
        [
          "Dangerous Spindle",
          "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"}' https://root:password@localhost:8080/api/v2/query/tuples

HTTP Response

{
  "results": [
    {
      "columns": [
        {
          "name": "title",
          "dataType": "VARCHAR",
          "nullable": false
        },
        {
          "name": "author_name",
          "dataType": "VARCHAR",
          "nullable": false
        },
        {
          "name": "genre",
          "dataType": "VARCHAR",
          "nullable": false
        },
        {
          "name": "rating",
          "dataType": "JSON",
          "nullable": false
        }
      ],
      "rows": [
        [
          "Dangerous Spindle",
          "Lacey Desmond",
          "Crime Thriller",
          {
            "AllBooksWeb": "4.4/5",
            "Noveliu": "4.5/5"
          }
        ]
      ]
    }
  ]
}

Supplementary Reference

When using cURL to invoke SingleStore’s Data API, 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-tuples.json file */


curl -H "Content-Type: application/json" --data '@./query-tuples.json' https://root:password@localhost:8080/api/v2/query/tuples

HTTP response

{
  "results": [
    {
      "columns": [
        {
          "name": "title",
          "dataType": "VARCHAR",
          "nullable": false
        },
        {
          "name": "genre",
          "dataType": "VARCHAR",
          "nullable": false
        },
        {
          "name": "rating",
          "dataType": "JSON",
          "nullable": false
        }
      ],
      "rows": [
        [
          "Dangerous Spindle",
          "Crime Thriller",
          {
            "AllBooksWeb": "4.4/5",
            "Noveliu": "4.5/5"
          }
        ]
      ]
    }
  ]
}

Last modified: May 5, 2023

Was this article helpful?

On this page

Was this article helpful?