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

20.0KB

The queries are also available as an Insomnia collection:

insomnia_collection_for_airsequel.json

4.6KB

Insert

InsertUsers

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

Read

ReadUsersAndSongs

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

Update

UpdateUsers

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

Upsert

UpsertUsers

graphql
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

graphql
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:

shell
# 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:

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

Or its read only URL:

shell
$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 Type	GraphQL Type
`INTEGER`	`Int`
`REAL`	`Float`
`TEXT`	`String`
`BLOB`	`String` (JSON with metadata)
`NULL`	`null`
`ANY`	`String`
`BOOL` / `BOOLEAN`(Not an official SQLite type, since it simply uses the integers `0` / `1` under the hood)	`Boolean`
`x TEXT CHECK (color IN ('a', 'b', 'c'))`	Custom Type `x_String` with allowed values `"a"`, `"b"`, or `"c"`

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

sql
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);

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

💡

To avoid problems with incompatible types, it’s recommended to always use STRICT SQLite tables when working with Airsequel: sqlite.org/stricttables

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:

sql
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

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.