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.

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

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

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

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

Name

Type

Description

string

["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

Name

Type

Description

schema

string

The name of an accessible schema

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

Name

Type

Description

schema

string

An accessible schema

table

string

A table that belongs to the schema

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

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

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

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

Name

Type

Description

schema

string

table

string

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

Name

Type

Description

schema

string

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

Name

Type

Description

schema

string

table

string

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

PreviousCreating Data

Last updated 5 years ago

Authentication

Login

Request Body

Ping

Post-Authentication

Fetch Schemas

Path Parameters

Fetch Tables

Path Parameters

Fetch Table Metadata

Path Parameters

Fetch Column Data

Path Parameters

Query Parameters

Pluck

Path Parameters

Insert Data

Path Parameters

Table Defaults

Path Parameters