GraphQL API

Airsequel automatically generates a full fledged GraphQL API endpoint including introspection capabilities for every database at airsequel.com/dbs/<db-id>/graphql.

It also provides a GraphiQL playground where you can test your GraphQL queries at airsequel.com/dbs/<db-id>/graphiql. For example: airsequel.com/dbs/avatar_the_last_airbender/graphiql.

Available GraphQL Queries

Following examples apply to this example database with users and songs:

users_and_songs.sqlite

The queries are also available as an Insomnia collection:

insomnia_collection_for_airsequel.json

Insert

• InsertUsers

  mutation InsertUsers {
    insert_users(
      objects: [{ email: "john@example.com", created_utc: "2022-06-21T09:51Z" }]
    ) {
      affected_rows
      returning {
        rowid
      }
    }
  }
  

Read

• ReadUsersAndSongs

  query ReadUsersAndSongs {
    users {
      name
      email
    }
    songs {
      name
      duration_seconds
    }
  }
  

Update

• UpdateUsers

  mutation UpdateUsers {
    update_users(
      filter: { email: { eq: "john@example.com" } }
      set: { name: "John Doe" }
    ) {
      affected_rows
      returning {
        rowid
      }
    }
  }
  

Upsert

• UpsertUsers

  mutation UpsertUsers {
    insert_users(
      objects: [{ email: "john@example.com", created_utc: "2022-11-21T09:51Z" }]
      on_conflict: { constraint: [email], update_columns: [created_utc] }
    ) {
      affected_rows
      returning {
        name
      }
    }
  }
  

Delete

• DeleteUser

  mutation DeleteUser {
    delete_users(filter: { email: { eq: "john@example.com" } }) {
      affected_rows
      returning {
        rowid
      }
    }
  }
  

Files

Since binary content like files and blobs are not compatible with the JSON based GraphQL API, we provide extra REST API endpoints for uploading and loading files.

New files can be created by PUTting them into their respective cells:

# Replace the variables with your respective values
http --form PUT \
  $HOST/api/dbs/$DBID/tables/$TABLE/columns/$COL/files/rowid/$ROWID \
  file@image.jpeg

For loading the file you can then open this URL:

$HOST/api/dbs/$DBID/tables/$TABLE/columns/$COL/files/rowid/16

Or its read only URL:

$HOST/readonly/$READONLY_ID/tables/$TABLE/columns/$COL/files/rowid/16

Types

SQLite’s types are mapped to GraphQL types in following way (both are per default nullable):

✝ SQLite uses the integers 0 / 1 for boolean values under the hood: https://www.sqlite.org/quirks.html#no_separate_boolean_datatype

Since SQLite is dynamically typed, and GraphQL is strongly typed, it’s possible to create data in a SQLite database which can not faithfully be transmitted via GraphQL.

If a table column has values of another type than the specified schema type, the values will coerced to the specified type for the GraphQL API. If the values can not be coerced the GraphQL result will have an errors field with the error Unable to coerce result to x. and the values which could not be coerced will be null.

SQL Table - Mixed Types

GraphQL Result - Homogenous Types

CREATE TABLE test (
  name TEXT,
  val_int INTEGER,
  val_real REAL
);

INSERT INTO test
  (name, val_int, val_real)
VALUES
  ("1", "1", "1"),
  (1, 1, 1),
  (2.2, 2.2, 2.2);

{
  "data": {
    "test": [
      { "name": "1", "val_int": 1, "val_real": 1 },
      { "name": "1", "val_int": 1, "val_real": 1 },
      { "name": "2.2", "val_int": null, "val_real": 2.2 }
    ]
  },
  "errors": [
    {
      "message": "Unable to coerce result to Int.",
      "path": ["test", 0, "val_int"],
      "locations": [{ "line": 4, "column": 5 }]
    }
  ]
}

However, there is always the option to create mixed type results with SQL queries and table views (even in STRICT tables). E.g. by executing following SQL query:

CREATE VIEW mixed_types AS
SELECT 1 AS col UNION        -- INTEGER
SELECT 2.2 AS col UNION      -- REAL
SELECT 'three' AS col UNION  -- TEXT
SELECT NULL AS col           -- NULL

This yields following result (or similar, since the order of rows in a SQL query without a ORDER BY clause is non-deterministic):

In such a scenario Airsequel always converts all fields to nullable String (i.e. [null, "1", "2.2", "three"]) for the GraphQL API.