Quick tip: Using SingleStoreDB's Data API
Tags: singlestoredb, http, api
Published: 2022-10-04 11:48:14

## Abstract
SingleStoreDB provides a [Data API](https://docs.singlestore.com/managed-service/en/reference/data-api.html) that can be used to execute SQL statements over an HTTP connection. This short article will show examples of how to use this Data API.

## Create a SingleStoreDB Cloud account
A [previous article](https://dev.to/veryfatboy/quick-tip-using-dbt-with-singlestoredb-161g) showed the steps required to create a free SingleStoreDB Cloud account. We'll use **Iris Demo Group** as our Workspace Group Name and **iris-demo** as our Workspace Name. We'll make a note of our **password** and **host** name.

## Test the Data API
The Data API is enabled by default, and we can test it quite quickly from the command line, as follows:

```shell
curl https://<host>/ping
```

We'll replace the `<host>` with the value from our SingleStoreDB Cloud account.

The result should be:

```
pong
```

## Create a database

Let's now create a database:

```shell
curl -u "admin:<password>" \ 
     -H "Content-Type: application/json" \
     --data '{"sql" : "CREATE DATABASE iris_demo"}' \ 
     https://<host>/api/v2/exec
```

We'll replace the `<password>` and `<host>` with the values from our SingleStoreDB Cloud account.

The result should be:

```
{"lastInsertId":0,"rowsAffected":1}
```

## Create a table

Let's now create a table:

```shell
curl -u "admin:<password>" \
     -H "Content-Type: application/json" \
     --data '{"sql" : "CREATE TABLE iris (sepal_length FLOAT, sepal_width FLOAT, petal_length FLOAT, petal_width FLOAT, species VARCHAR(20))", "database" : "iris_demo"}' \
     https://<host>/api/v2/exec
```

The result should be:

```
{"lastInsertId":0,"rowsAffected":0}
```

## Load data into table

We'll now load some data into our database using a MySQL client.

First, we'll download a [CSV](https://gist.github.com/VeryFatBoy/9af771d443f5ec4dd6eec8d69a062638) file containing the [Iris flower data set](https://en.wikipedia.org/wiki/Iris_flower_data_set).

Next, we'll launch the client:

```shell
mysql --local-infile \
      -u admin \
      -h <host> \
      -P 3306 \
      --default-auth=mysql_native_password \
      -p
```

The `<host>` being replaced with the value obtained from SingleStoreDB Cloud.

Finally, we'll load the CSV file data into our database table, as follows:

```sql
USE iris_demo;

LOAD DATA LOCAL INFILE '/path/to/iris.csv'
INTO TABLE iris
IGNORE 1 LINES
COLUMNS TERMINATED BY ',';
```

We would replace `/path/to/` with the actual path to where the CSV file was located.

## Perform a query

We can then perform queries, such as:

```shell
curl -u "admin:<password>" \
     -H "Content-Type: application/json" \
     --data '{"sql" : "SELECT * FROM iris LIMIT 5", "database" : "iris_demo"}' \
     https://<host>/api/v2/query/rows
```

The result should be similar to the following:

```json
{
   "results":[
      {
         "rows":[
            {
               "sepal_length":5.7,
               "sepal_width":4.4,
               "petal_length":1.5,
               "petal_width":0.4,
               "species":"Iris-setosa\r"
            },
            {
               "sepal_length":4.4,
               "sepal_width":3,
               "petal_length":1.3,
               "petal_width":0.2,
               "species":"Iris-setosa\r"
            },
            {
               "sepal_length":5,
               "sepal_width":3.5,
               "petal_length":1.6,
               "petal_width":0.6,
               "species":"Iris-setosa\r"
            },
            {
               "sepal_length":6.7,
               "sepal_width":3.1,
               "petal_length":4.7,
               "petal_width":1.5,
               "species":"Iris-versicolor\r"
            },
            {
               "sepal_length":5.7,
               "sepal_width":3,
               "petal_length":4.2,
               "petal_width":1.2,
               "species":"Iris-versicolor\r"
            }
         ]
      }
   ]
}
```

## Summary
The SingleStoreDB Data API provides another way that we can work with SingleStoreDB. This method could be useful for certain types of applications and integrations. This is another example of the flexibility that SingleStoreDB provides.