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 TypeGraphQL Type
INTEGERInt
REALFloat
TEXTString
BLOBString (JSON with metadata)
NULLnull
ANYString
BOOL/BOOLEANBoolean
x TEXT CHECK (color IN ('a', 'b', 'c'))Custom Type x_String with allowed values "a", "b", or "c"

✝ 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):

col
NULL
1
three
2.2

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