# API

## Authentication

Helium does not have a direct authentication system, but rather creates database connections on behalf of the user. As a result, Helium is capable of authenticating with any database using the MySQL protocol. A connection is uniquely identified by the username, password, host domain, and port. Currently, multiple users using the same connection works, but is unsupported behavior.

Once a connection has been established, an API key will be provided to the consumer. Every endpoint besides `POST /api/v1/login` requires a `X-API-Key` header whose value is the API key generated by the login endpoint.

Here's an example of using the API to authenticate a MySQL user called "bob" identified by the password "hunter2" located on the host "example.com":

```bash
$ curl http://<helium-domain>:<port>/api/v1/login \
    --data "username=bob&password=hunter2&host=example.com"
{"apiKey":"my-api-key"}
```

Almost all other endpoints require an API key.

```bash
$ curl http://<helium-domain>:<port>/api/v1/schemas \
    -H "X-API-Key: my-api-key"
["foo", "bar", "baz"]
```

Sessions automatically expire after being out of use for a certain period of time (30 minutes by default). Attempting to use the API key after that will result in a 401 Unauthorized. Every API response using an API key will have a `X-Session-Expiration`header whose value is the Unix time (milliseconds) at which the API key expires.

## Login

<mark style="color:green;">`POST`</mark> `/api/v1/login`

Attempts to establish a connection to a MySQL database. Returns an API key if successful.

#### Request Body

| Name     | Type   | Description                                                                                 |
| -------- | ------ | ------------------------------------------------------------------------------------------- |
| username | string |                                                                                             |
| password | string |                                                                                             |
| host     | string | A domain name like google.com where the database is accessible from. Defaults to localhost. |
| port     | number | <p>Defaults to 3306, the default <br><br>MySQL port</p>                                     |

{% tabs %}
{% tab title="200 " %}

```javascript
{ "apiKey": "abcdefghijklmnopqrstuvwxyz" }
```

{% endtab %}
{% endtabs %}

## Ping

<mark style="color:blue;">`GET`</mark> `/api/v1/ping`

Tests the validity of the API specified by the X-API-Key header.\
\
Returns: `SessionPing`\
\
<https://github.com/mattbdean/Helium/blob/master/common/api/session-ping.ts>

{% tabs %}
{% tab title="200 " %}

```javascript
{
    "validApiKey": true,
    "expiresAt": 1531543523338
}
```

{% endtab %}
{% endtabs %}

## Post-Authentication

Any endpoint listed here requires a valid `X-API-Key` header to be specified in order to return a 200 OK response.

## Fetch Schemas

<mark style="color:blue;">`GET`</mark> `/api/v1/schemas`

Returns a list of schema names accessible to the currently authenticated user. Equivalent to running `SHOW SCHEMAS;` in the MySQL console.

#### Path Parameters

| Name | Type   | Description |
| ---- | ------ | ----------- |
|      | string |             |

{% tabs %}
{% tab title="200 " %}

```javascript
["foo", "bar", "baz"]
```

{% endtab %}
{% endtabs %}

## Fetch Tables

<mark style="color:blue;">`GET`</mark> `/api/v1/schemas/:schema`

Lists all tables in the given schema accessible to the current user. Returns: `BaseTableName[]`\
\
<https://github.com/mattbdean/Helium/blob/master/common/api/base-table-name.ts&#x20>;

#### Path Parameters

| Name   | Type   | Description                      |
| ------ | ------ | -------------------------------- |
| schema | string | The name of an accessible schema |

{% tabs %}
{% tab title="200 " %}

```javascript
[  
    {  
        "schema": "helium_compound_fk_test",
        "name": {  
            "raw": "fk_table",
            "clean": "FkTable"
        },
        "tier": "manual",
        "masterName": null
    },
    {  
        "schema": "helium_compound_fk_test",
        "name": {  
            "raw": "table_a",
            "clean": "TableA"
        },
        "tier": "manual",
        "masterName": null
    },
    {  
        "schema": "helium_compound_fk_test",
        "name": {  
            "raw": "table_b",
            "clean": "TableB"
        },
        "tier": "manual",
        "masterName": null
    }
]
```

{% endtab %}
{% endtabs %}

## Fetch Table Metadata

<mark style="color:blue;">`GET`</mark> `/api/v1/schemas/:schema/:table`

Gets metadata about a table, such as its headers, the total amount of rows, and constraints. Helium supports primary, foreign, and unique constraints as well as compound primary and foreign constraints.\
\
Returns: `TableMeta`\
\
<https://github.com/mattbdean/Helium/blob/master/common/api/table-meta.ts>

#### Path Parameters

| Name   | Type   | Description                        |
| ------ | ------ | ---------------------------------- |
| schema | string | An accessible schema               |
| table  | string | A table that belongs to the schema |

{% tabs %}
{% tab title="200 " %}

```javascript
{
    "schema": "helium_sample",
    "name": "big_table",
    "headers": [
        {
            "name": "pk",
            "type": "integer",
            "isNumerical": true,
            "isTextual": false,
            "signed": true,
            "ordinalPosition": 1,
            "rawType": "int(11)",
            "defaultValue": null,
            "nullable": false,
            "maxCharacters": null,
            "charset": null,
            "numericPrecision": 10,
            "numericScale": 0,
            "enumValues": null,
            "comment": "",
            "tableName": "big_table"
        }
    ],
    "totalRows":1000,
    "constraints":[
        {
            "type": "primary",
            "name": "PRIMARY",
            "constraints": [
                {
                    "localColumn": "pk",
                    "ref": null,
                    "type": "primary"
                }
            ]
        }
    ],
    "comment": "",
    "parts": []
}
```

{% endtab %}
{% endtabs %}

## Fetch Column Data

<mark style="color:blue;">`GET`</mark> `/api/v1/schemas/:schema/:table/data`

Fetches data from a specific table. Supports filtering and sorting, and is paginated data.\
\
Returns: `PaginatedResponse<SqlRow>`\
\
<https://github.com/mattbdean/Helium/blob/master/common/api/paginated-response.ts>

#### Path Parameters

| Name   | Type   | Description |
| ------ | ------ | ----------- |
| schema | string |             |
| table  | string |             |

#### Query Parameters

| Name    | Type   | Description                                                                                                          |
| ------- | ------ | -------------------------------------------------------------------------------------------------------------------- |
| sort    | string | The name of a column to by in ascending order. Prefixing the name with a hyphen reverses the sort direction.         |
| limit   | number | The maximum amount of items to return in a single page. Accepts values between 1 and 100, inclusive. Defaults to 25. |
| page    | number | Page number. Accepts values greater than or equal to 1.                                                              |
| filters | array  | A JSON array of Filter objects: <https://github.com/mattbdean/Helium/blob/master/common/api/filter.ts>               |

{% tabs %}
{% tab title="200 " %}

```javascript
{
    "size": 7,
    "data": [
        {
            "product_id": 20,
            "product_name": "Chicken breast (1 lb)",
            "price": 1.99
        },
        {
            "product_id": 21,
            "product_name": "Apples (1 lb)",
            "price": 1.99
        },
        {
            "product_id": 22,
            "product_name": "Soda",
            "price": 1.99
        },
        {
            "product_id": 23,
            "product_name": "Crackers",
            "price": 3.49
        },
        {
            "product_id": 24,
            "product_name": "Ice cream",
            "price": 3.99
        },
        {
            "product_id": 25,
            "product_name": "Oranges (1 lb)",
            "price": 2.99
        },
        {
            "product_id": 26,
            "product_name": "Bananas (1 lb)",
            "price": 0.79
        }
    ],
    "totalRows": 7
}
```

{% endtab %}
{% endtabs %}

## Pluck

<mark style="color:blue;">`GET`</mark> `/api/v1/schemas/:schema/:table/pluck`

Grabs all rows form all part tables belonging to the specified table. Assumes DataJoint creates part tables in the same schema as its master. The keys and values specified in the query are used to uniquely identify exactly one row from the master table. The keys of the query parameters should be column names, and the values should be specific values that exactly one column has. The most efficient way of specifying the selectors is by using only the primary keys of the master table.\
\
Returns: `TableInsert`\
\
<https://github.com/mattbdean/Helium/blob/master/common/api/table-insert.ts>

#### Path Parameters

| Name   | Type   | Description |
| ------ | ------ | ----------- |
| schema | string |             |
| table  | string |             |

{% tabs %}
{% tab title="200 " %}

```javascript
{
    "master": [
        { "pk": 1001 }
    ],
    "master__part": [
        { "part_pk": 100, "master": 1001, "default_test": 12345 }
    ]
}
```

{% endtab %}
{% endtabs %}

## Insert Data

<mark style="color:green;">`POST`</mark> `/api/v1/schemas/:schema/data`

Inserts data into a table. Multiple tables are allowed if one of the tables is a master table and the rest are part tables belonging to that master table. Accepts a `TableInsert` in the HTTP request payload.\
\
<https://github.com/mattbdean/Helium/blob/master/common/api/table-insert.ts>

#### Path Parameters

| Name   | Type   | Description |
| ------ | ------ | ----------- |
| schema | string |             |

{% tabs %}
{% tab title="200 " %}

```
```

{% endtab %}
{% endtabs %}

## Table Defaults

<mark style="color:blue;">`GET`</mark> `/api/v1/schemas/:schema/:table/defaults`

Fetches the default values for the given table at the time of the call. Every field will be resolved to a string, number, or `null`. Any auto increment fields will be resolved to their next value and `CURRENT_TIMESTAMP` will be resolved to a datetime string in the default MySQL format.

#### Path Parameters

| Name   | Type   | Description |
| ------ | ------ | ----------- |
| schema | string |             |
| table  | string |             |

{% tabs %}
{% tab title="200 " %}

```javascript
{
    "pk": 9,
    "int": 5,
    "float": 10,
    "date": "2017-01-01",
    "datetime": "2017-01-01 12:00:00",
    "datetime_now": "2018-07-19 09:37:00",
    "boolean": true,
    "enum": "a",
    "no_default": null
}
```

{% endtab %}
{% endtabs %}