API

Learn how to use Helium's JSON 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":

$ 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.

$ 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-Expirationheader whose value is the Unix time (milliseconds) at which the API key expires.

Login

POST /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	Defaults to 3306, the default MySQL port

200

{ "apiKey": "abcdefghijklmnopqrstuvwxyz" }

Ping

GET /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

200

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

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

GET /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

200

["foo", "bar", "baz"]

Fetch Tables

GET /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

Path Parameters

Name	Type	Description
schema	string	The name of an accessible schema

200

[  
    {  
        "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
    }
]

Fetch Table Metadata

GET /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

200

{
    "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": []
}

Fetch Column Data

GET /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

200

{
    "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
}

Pluck

GET /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

200

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

Insert Data

POST /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

200

Table Defaults

GET /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

200

{
    "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
}