# Airsequel Documentation

This is the complete Airsequel documentation in a single file.

---


# Getting Started

**Welcome to Airsequel! 🎉**

Airsequel is a powerful platform to bring your data management to the next level.

Our core features are:

- Automatic GraphQL API generation for each of your databases
  (including GraphQL playground)
- SQL playground
- Spreadsheet UI for all tables and views
- Dashboards with several chart types
- Cloud functions for extending the functionality of your databases (currently in closed beta)

Check out the corresponding documentation sections for more details to those features.


## User Interface

After creating a database, you're welcomed by our database UI:

![Database UI](/assets/getting-started/Screenshot_2023-11-12_at_09.15.25.png)

It includes core information and lets you jump to the most important sections:

- **Tables** - Our spreadsheet UI which lets you edit the schema (tables, views, columns) and enter / edit your data.
- **SQL Queries** - Execute any SQL query (AI assisted)
- **GraphiQL** - Use the [GraphiQL](https://github.com/graphql/graphiql) playground to inspect your GraphQL schema and to ry out GraphQL queries
- **Dashboards** - Create dashboards with several chart types (bar, line, …)


## Conceptual Overview

Airsequel aims to help you make the most out of SQLite. We want to make all of SQLite's features easily accessible through our app/GUI.

When you create or upload a SQLite database to Airsequel, we take great care to keep the file intact and modify the data in only the most straight forward way. You can export the database at any time, work on it using your favorite tools, and then upload it again later.

However, some important features are not supported directly by SQLite, such as:

- Changing the order of tables or columns
- Storing meta information about tables or columns

Additionally, we need to store extra data for our charts, dashboards, apps, and cloud function features.

For this reason, we create another database called `metadata.sqlite` for each user database to hold all this information.


## Users

Airsequel is designed to be easily accessible and does not require an account to use. However, databases will automatically expire after ten days, unless they are assigned to a team. Expired databases can still be assigned to a team to regain access.


## Teams

- Teams own databases. Users can not own databases.
- A default team is created for new users.
- The default team can be deleted by users who are members of another team.
- Users who are removed from their only team will become members of a new default team.
- Team admins can add and remove users from a team and manage their privileges.
- Admins can remove themselves from a team if there is at least one other admin remaining.
- Deleting a team will delete all databases in them.


## Databases

- Anonymously created databases:
  - Are public and expire in 10 days
  - Can be assigned to a team
- Databases created by authenticated users are private by default, but can be made public subsequently


## Templates

Databases marked as templates can be cloned with a single click! Here's how you can make use of this feature:

1. Open the database you want to use as a template, and go to Settings > General > Database Environment and select “Template”, then hit “Apply”:

   ![Untitled](/assets/getting-started/Untitled.png)

2. To obtain the read-only ID of your database, click on the “Database” tab and then copy the ID from the table:

   ![Untitled](/assets/getting-started/Untitled%201.png)

3. The database can now easily be duplicated from
   `www.airsequel.com/readonly/<readonly-id>/duplicate`


## Inserting / Uploading Data

In addition to the web GUI, there is also the CLI tool [Airput]
to upload data / files to Airsequel.

[Airput]: https://github.com/Airsequel/Airput

E.g.:

```shell
airput upload \
  --domain https://www.airsequel.com \
  --dbid 01hmxvnd7fdyk22zc5nn6319je \
  --tablename files \
  ./tree.png \
  ./flower.jpeg
```

---

# Databases

Databases are at the very heart of Airsequel. This section has the goal of familiarizing you with the way databases are handled inside Airsequel.

## Why we picked SQLite

SQLite is the most deployed database worldwide for good reason:

- **Fast:** [35% Faster Than The Filesystem](https://www.sqlite.org/fasterthanfs.html)
  - Especially when mapping GraphQL queries to SQL queries:
    [Many Small Queries Are Efficient In SQLite](https://www.sqlite.org/np1queryprob.html)
- **Stable:**
  - [Quality Management](https://www.sqlite.org/qmplan.html)
  - [How SQLite Is Tested](https://www.sqlite.org/testing.html)
- **Highly reliable:** [High Reliability](https://www.sqlite.org/hirely.html)
- **Fully-featured:** [SQL As Understood By SQLite](https://www.sqlite.org/lang.html)
- **Supported until at least 2050:** [Long Term Support](https://www.sqlite.org/lts.html)
- **Legal backing:**
  [Only database recommended for long-term archival by the US Library of Congress](https://www.sqlite.org/locrsf.html)
- **Widely used in enterprise:** [Well-Known Users of SQLite](https://www.sqlite.org/famous.html)
- **Fully open source:** [SQLite Is Public Domain](https://www.sqlite.org/copyright.html)
- **Self-contained:** [SQLite is a Self Contained System](https://www.sqlite.org/selfcontained.html)

Check out the official documentation for even more details: [sqlite.org/whentouse](https://www.sqlite.org/whentouse.html#serversidedb)

## Database Lifecycle

Our primary goal is to help you make the most out of SQLite. We want to make all of SQLite's features easily accessible through our app/GUI.

When you create or upload a SQLite database to Airsequel, we take great care to keep the file intact and modify the data in only the most straight forward way. You can export the database at any time, work on it using your favorite local tools, and then upload it again later.

However, some important features we need for Airsequel are not supported directly by SQLite:

- Changing the order of tables or columns
- Storing meta information about tables or columns
- Storing extra data for our charts, dashboards, apps, and cloud function features.

For this reason, we create another database called `metadata.sqlite` for each user database to hold all this information.

## Visibility of Databases

- Anonymously created databases:
  - Are public and expire in 10 days
  - Can be assigned to a team
- Databases created by authenticated users are private by default, but can be made public subsequently


---

# SQL

Airsequel's SQL workbench lets you execute arbitrary SQL queries on your SQLite database.

![Untitled](/assets/sql/Untitled.png)

As seen in the screenshot above, airsequel provides OpenAI integration for query generation. Moreover, you can work on multiple queries simultaneously by creating multiple tabs:

![Untitled](/assets/sql/Untitled%201.png)

As seen in the pictures above, the query can be saved as a new or existing view using the buttons on the bottom right. By default, the view will now appear in the “tables” tab, but a special “views” tab can be enabled in the settings:

![Untitled](/assets/sql/Untitled%202.png)

Check out the [pricing page](https://www.airsequel.com/pricing) for all limitations of the Free edition.


## Extra Types for SQLite

In order to support more elaborate types than just the basic SQL types, we came up with a specification on how to extend SQLite with extra datatypes in a standard conformant way.

The basic idea is to use suffixes like e.g. `TEXT_EMAIL` to specify more refined types. Since SQLite's type affinity will treat this as a `TEXT` column, it works in a backwards compatible way.

For more details check out the full specification in following repo: https://github.com/Airsequel/SQLite-Extra-Types

When editing entries via the GUI, Airsequel will provide specialized input fields for the given type. For instance, “color” columns can be edited using a color picker:

![Untitled](/assets/sql/Untitled%203.png)


## Interactive Editing

Airsequel provides comprehensive interactive editing capabilities. The most common operations are presented in the list below:

- Change the name or description of a database
  The name and description for a database can be changed by clicking on the respective “edit” icon in the “Database” tab:
  ![Untitled](/assets/sql/Untitled%204.png)
  Furthermore, the description can be automatically generated using the built in OpenAI integration:
  ![Untitled](/assets/sql/Untitled%205.png)
  Database descriptions can make use of certain markdown features (italic and bold text, lists, links, headings, …):
  ![Untitled](/assets/sql/Untitled%206.png)
- Create a table
  New tables can be created by using the “+” to the right of the list of existing tables from the tables tab:
  ![Untitled](/assets/sql/Untitled%207.png)
- Rename / Delete / Export a table
  Click on the arrow next to the name of the table in the “tables” tab:
  ![Untitled](/assets/sql/Untitled%208.png)
- Change table description
  Click on the arrow next to the name of the table in the “tables” tab.
  ![Untitled](/assets/sql/Untitled%208.png)
  Airsequel also provides OpenAI integration for generating table descriptions:
  ![Untitled](/assets/sql/Untitled%209.png)
- Create a column
  Click on the “+” icon at the end of the list of columns on one of the tables in the “tables” tab:
  ![Untitled](/assets/sql/Untitled%2010.png)
  Alternatively, columns can be created at arbitrary positions by clicking on the arrow icon next to an existing column and choosing “insert left/right”:
  ![Untitled](/assets/sql/Untitled%2011.png)
- Delete / Rename / Hide (collapse) column
  Click on the arrow icon next to the column in “tables” tab:
  ![Untitled](/assets/sql/Untitled%2012.png)
  In the case of the “Collapse” option, the process can be reversed by clicking the “Expand” option which appears in it's place.
- Set all entries of column to the default value
  Click on the arrow icon next to the column in the “tables” tab and then choose “Empty all cells” (in the case of columns which can be NULL) or “Default all cells” (in the case of NOT NULL columns):
  ![Untitled](/assets/sql/Untitled%2013.png)
- Reorder columns
  The order of a table's columns can be changed by dragging and dropping the two sets of dots at the top of a column in the “tables” tab:
  ![Untitled](/assets/sql/Untitled%2014.png)
- Delete an arbitrary set of rows
  Rows under a table in the “tables” view can be selected by checking the box on their left:
  ![Untitled](/assets/sql/Untitled%2015.png)
  Alternatively, all rows within a table can be selected by checking the box at top-left corner of the table:
  ![Untitled](/assets/sql/Untitled%2016.png)
  The aforementioned checkboxes can be unchecked to unselect the rows in question. Once at least one row is selected, the “delete” icon at the top-left corner of the table can be clicked to delete the selected rows.
- Create a row
  Rows can be created by clicking the “+” button at the bottom of their table:
  ![Untitled](/assets/sql/Untitled%2017.png)
- Edit a cell
  Editing a cell is as simple as clicking on it and then typing it's new value!
  Certain types offer specialized inputs. For example, colors can be chosen using a color picker:
  ![Untitled](/assets/sql/Untitled%203.png)

## SQL API Usage

The SQL API is only available in the Enterprise Edition.

```bash
curl \
  --data '{ "query": "select name, height_in_cm from characters" }' \
  --header 'Content-Type: application/json' \
  http://localhost:4185/dbs/avatar_characters/sql
```

yields:

```json
{
  "rows": [
    { "name": "Aang", "height_in_cm": 137 },
    { "name": "Katara", "height_in_cm": 145 }
  ],
  "runtime": {
    "real": 0.0,
    "user": 0.000214,
    "sys": 0.00009
  }
}
```

## Useful SQL Snippets

### Basics

- Rename a column
  ```sql
  ALTER TABLE table_name
  RENAME COLUMN current_name TO new_name;
  ```
- Count lines of text in a column
  ```sql
  LENGTH(col) - LENGTH(REPLACE(col, X'0A', '')) + 1
  ```
- Check if all values in a group are the same
  ```sql
  iif(
    count(DISTINCT filetype) = 1,
    filetype,
    NULL
  )
  ```
- Get ISO 8601 timestamp with millisecond precision
  ```sql
  SELECT strftime('%Y-%m-%dT%H:%M:%fZ')
  ```
- Create table with an automatically set timestamp column
  ```sql
  CREATE TABLE "logs" (
    "created_utc" TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ')),
  	…
  )
  ```
- Select all rows of a table except the first one
  SQLite does not allow `OFFSET` without a `LIMIT` clause. To work around this restriction, simply set the `LIMIT` to `-1`:
  ```sql
  SELECT *
  FROM table_name
  LIMIT -1
  OFFSET 1
  ```
- Only update value if it's not `NULL`
  This is especially useful if you're passing the value from the code calling your SQL query. For example here it only updates the name value (passed in via `:name`) if it is not `NULL`.
  ```sql
  UPDATE users
  SET name = COALESCE(:name, name)
  WHERE rowid == 2
  ```
- Include example data in an SQL query
  You can combine SQLite's CTE (common table expression) with the `VALUES` syntax to create a temporary table that you can then use in a subsequent query:
  ```sql
  WITH users(name, color, size_cm) AS (
    VALUES
      ('John', 'red', 175),
      ('Anna', 'green', 168)
  )
  SELECT * FROM users
  ```
- Delete duplicate rows
  ```sql
  DELETE
  FROM "users"
  WHERE rowid NOT IN (
    SELECT min(rowid)
    FROM "users"
    GROUP BY ""
  )
  ```
- Update several specific fields in a column at once
  ```
  UPDATE "items"
  SET "color" =
    CASE rowid
      WHEN 1 THEN  'red'
      WHEN 2 THEN  'turqoise'
      WHEN 3 THEN  'yellow'
    END
  ```
  For a better performance on large tables add `WHERE rowid IN (1, 2, 3)` to the end.

### JSON

- Extract value from JSON object
  ```sql
  SELECT id, userObject->'birthday'
  FROM users
  WHERE json_valid(userObject) = TRUE
  ```
- Merge a column of JSON objects
  Table `changes`

  | id  | json_patch         |
  | --- | ------------------ |
  | 1   | { "a": 1, "b": 2 } |
  | 1   | { "a": 2 }         |
  | 2   | { "c": 3 }         |

  ```sql
  SELECT DISTINCT
    "key",
  	last_value("value") OVER (PARTITION BY "id" || "key") AS "value"
  FROM
  	"changes", json_each("changes"."json_patch")
  ```

  Yields:

  | id  | key | value |
  | --- | --- | ----- |
  | 1   | a   | 2     |
  | 1   | b   | 2     |
  | 2   | c   | 3     |

  To better work with deeply nested objects and arrays, it's recommended to flatten the JSON before inserting it into a cell. E.g. with [github.com/hughsk/flat](https://github.com/hughsk/flat).

- Extract a URL from a column and store it in another JSON column
    <aside>
    ⚠️ This uses a space character to find the end of the URL. It does not work with other whitespace characters like newlines and tabs.

    </aside>

  ```sql
  CREATE TABLE notes (
    body TEXT,
    metadata TEXT
  );

  INSERT INTO notes(body, metadata)
  VALUES
  ('https://github.com/user/repoA', NULL),
  ('Check out https://github.com/user/repoB for more information', NULL),
  ('Where is https://github.com/user/repoC hosted?', '{"size": "S"}');

  UPDATE notes
  SET metadata = json_insert(
    ifnull(metadata, '{}'),
    '$.github_url',
    substr(body, instr(body, 'https://github.com'),
      CASE
        WHEN instr(substr(body, instr(body, 'https://github.com')), ' ') == 0
        THEN length(substr(body, instr(body, 'https://github.com')))
        ELSE instr(substr(body, instr(body, 'https://github.com')), ' ') - 1
      END
    )
  )
  WHERE body LIKE '%https://github.com%';

  SELECT * FROM notes;
  ```

### Generation

- Get a random number between 0 and 1 with 2 decimal places
  ```sql
  SELECT ABS(RANDOM() % 1E2) / 1E2
  ```
  Note: `ABS(RANDOM()) % 1E2` would cause an uneven distribution.
- Generate a table with numbers from 1 to 10
  ```sql
  WITH RECURSIVE ten(x) AS (
  	SELECT 1
  	UNION ALL
  	SELECT x + 1 FROM ten WHERE x < 10
  )
  SELECT * FROM ten;
  ```
- Generate a table `random_data` with x rows of random entries
  ```sql
  CREATE TABLE random_data AS
  WITH RECURSIVE tmp(n, txt) AS (
    SELECT random(), hex(randomblob(10))
    UNION ALL
    SELECT random(), hex(randomblob(10))
    FROM tmp
    LIMIT 1000000
  )
  SELECT * FROM tmp;
  ```
- Add a “row number” column (starting from 1)
  **Example table `sales`:**

  | year | amount |
  | ---- | ------ |
  | 2021 | 1348   |
  | 2022 | 544    |
  | 2023 | 1312   |
  | 2024 | 1176   |

  **Steps:**
  Use window function `ROW_NUMBER` ([sqlite.org/windowfunctions](https://www.sqlite.org/windowfunctions.html#built_in_window_functions))

  ```sql
  SELECT
    ROW_NUMBER() OVER (ORDER BY "year") AS row_number,
    *
  FROM "sales"
  ```

  **Result:**

  | row_number | year | amount |
  | ---------- | ---- | ------ |
  | 1          | 2021 | 1348   |
  | 2          | 2022 | 544    |
  | 3          | 2023 | 1312   |
  | 4          | 2024 | 1176   |

- Set a random date time between `2000-01-01` and `2024-01-20` for first 10 items
  ```sql
  UPDATE "items"
  SET "datetime" =
    datetime(
      (julianday('2024-01-20') - julianday('2000-01-01'))
  			* (SELECT ABS(RANDOM() % 1E4) / 1E4)
  		+ julianday('2000-01-01')
    )
  WHERE rowid IN (
    SELECT rowid
    FROM "items"
    LIMIT 10
  )
  ```

### Advanced

- Get all possible sub-paths for a column with paths / urls
  E.g for this table:

  | name      | path  |
  | --------- | ----- |
  | image.png | a/b   |
  | story.txt | c/d/e |

  You would receive following result:

  | sub_path |
  | -------- |
  | a        |
  | a/b      |
  | c        |
  | c/d      |
  | c/d/e    |

  ```sql
  WITH RECURSIVE Paths(segment, remaining) AS (
    SELECT DISTINCT '', path || '/'
    FROM files
    UNION ALL
    SELECT DISTINCT
      CASE
        WHEN segment = '' THEN SUBSTR(remaining, 0, INSTR(remaining, '/'))
        ELSE segment || '/' || SUBSTR(remaining, 0, INSTR(remaining, '/'))
      END,
      SUBSTR(remaining, INSTR(remaining, '/') + 1)
    FROM Paths
    WHERE INSTR(remaining, '/') > 0
  )
  SELECT DISTINCT segment AS sub_path
  FROM Paths
  WHERE segment != ''
  ```

- Split a multiline cell into one line per row
  ```sql
  WITH RECURSIVE split(id, csv_line, rest)
  AS (
    SELECT id, '', csv || x'0a'
    FROM csvs
    UNION ALL SELECT
    id,
    substr(rest, 0, instr(rest, x'0a')),
    substr(rest, instr(rest, x'0a') + 1)
    FROM split WHERE rest != ''
  )
  SELECT id, csv_line
  FROM split
  WHERE csv_line != '';
  ```
- Get the last `NOT NULL` value in a group

  ```sql
  CREATE TABLE test(id, val);

  INSERT INTO test(id, val)
  VALUES
    ('a', NULL),
    ('a', 123),
    ('a', 456),
    ('a', NULL),
    ('b', 789),
    ('b', 12),
    ('b', NULL);

  SELECT id, val FROM (
    SELECT
      id,
      val,
      ROW_NUMBER() OVER (PARTITION BY id ORDER BY ROWID DESC)
        AS row_number
    FROM test
    WHERE val IS NOT NULL
  )
  WHERE row_number = 1;
  ```

  Or with an additional aggregate function:

  ```sql
  SELECT * FROM (
  	SELECT
  	  id,
  	  FIRST_VALUE(val) OVER (PARTITION BY id ORDER BY ROWID DESC)
        AS row_number
  	FROM test
  	WHERE val IS NOT NULL
  )
  GROUP BY id;
  ```

  Or only with an aggregate function instead of a window function:

  ```sql
  WITH
    test_grouped(id, val_array) AS (
      SELECT id, json_group_array(val) FILTER (WHERE val IS NOT NULL)
      FROM test
      GROUP BY id
    )
  SELECT
    id,
    val_array ->> (json_array_length(val_array) - 1) AS val
  FROM test_grouped;
  ```

  yields

  | id  | val |
  | --- | --- |
  | a   | 456 |
  | b   | 12  |

- Automatically set a random id after inserting a row
  ```sql
  CREATE TRIGGER insert_random_id_after_insert_users
  AFTER INSERT ON users
  BEGIN
    UPDATE users
    -- 32 hex characters long random id
    SET random_id = lower(hex(randomblob(16)))
    WHERE rowid == NEW.rowid;
  END
  ```
  If it should be possible to set the random ID during insert, check if `NEW.random_id` is empty before overwriting it.
- Convert table of cumulative values to delta values
  **Example table `sales`:**

  | year | total_amount |
  | ---- | ------------ |
  | 2021 | 1348         |
  | 2022 | 1544         |
  | 2023 | 1612         |
  | 2024 | 1876         |

  **Steps:**

  1. `SELECT` previous row with `LAG` ([sqlite.org/windowfunctions](https://www.sqlite.org/windowfunctions.html#built_in_window_functions))
  2. As we can't calculate a delta amount for the first year, we must omit it from the result table. SQLite requires a `LIMIT` clause when using `OFFSET`. To work around this limitation, we can set the `LIMIT` to `-1`.

  ```sql
  SELECT
    "year",
    "total_amount" - LAG("total_amount", 1, 0)
      OVER (ORDER BY "year") AS "amount"
  FROM "sales"
  LIMIT -1
  OFFSET 1
  ```

  **Result:**

  | year | amount |
  | ---- | ------ |
  | 2022 | 196    |
  | 2023 | 68     |
  | 2024 | 264    |


---

# 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](https://github.com/graphql/graphiql/tree/main/packages/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](https://www.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](/assets/graphql-api/main.sqlite)

The queries are also available as an Insomnia collection:

[insomnia_collection_for_airsequel.json](/assets/graphql-api/airsequel.json)

### 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
    }
    users_by_pk(email: "john@example.com") {
      name
    }
    songs {
      name
      duration_seconds
    }
    songs_by_pk(rowid: 2) {
      name
      duration_seconds
    }
  }
  ```

### Update

- UpdateUsers

  ```graphql
  mutation UpdateUsers {
    update_users(
      where: { email: { _eq: "john@example.com" } }
      _set: { name: "John Doe" }
    ) {
      affected_rows
      returning {
        rowid
      }
    }
  }
  ```

- UpdateUsersByPK
  ```graphql
  mutation UpdateUsersByPK {
    update_users_by_pk(email: "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(where: { email: { _eq: "john@example.com" } }) {
      affected_rows
      returning {
        rowid
      }
    }
  }
  ```

- DeleteUserByPK
  ```graphql
  mutation DeleteUserByPK {
    delete_users_by_pk(email: "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 `PUT`ting them into their respective cells:

```bash
# 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:

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

Or its read only URL:

```bash
$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`✝                        | `Boolean`                                                         |
| `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:
[sqlite.org/quirks#no_separate_boolean_datatype](
  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**

```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 }]
    }
  ]
}
```

<aside>
💡 To avoid problems with incompatible types, it's recommended to always use `STRICT` SQLite tables when working with Airsequel: [sqlite.org/stricttables](https://www.sqlite.org/stricttables.html)

</aside>

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

---

# Dashboards

Our dashboard feature allows you to easily visualize and highlight your data in one convenient location. You can easily create custom dashboards to track the metrics that matter most to you.

![Untitled](/assets/dashboards/Untitled.png)

For advanced charts make sure to create necessary table views first. They will allow you to extract the relevant datapoints from your existing tables.

E.g. for a table `episodes` with a field `directed_by` we could extract the number of times each director has directed an episode and plot it in a bar chart.

First, create the view:

```sql
CREATE VIEW directors AS
SELECT
  directed_by AS name,
  COUNT(directed_by) AS count
FROM episodes
GROUP BY directed_by
ORDER BY count DESC
```

Which results in following table:

![Untitled](/assets/dashboards/Untitled%201.png)

This table can now be visualized with following chart:

![Untitled](/assets/dashboards/Untitled%202.png)

Happy dashboarding!


---

# Admin API

Our admin JSON API lets you do everything you can do in the GUI
via the corresponding JSON endpoints.
While the [GraphQL API](graphql-api) is for modifying the data in your tables,
this API is for managing the databases and all related entities itself.
For historic reasons it's a common JSON REST API and not a GraphQL API.

> **⚠️ The API is not stable yet and might change without notice at any time.**
>
> USE AT YOUR OWN RISK!


## Highlights

- CRUD for
  - Databases, tables, cells
  - Dashboards, charts
  - Functions, tokens, environment variables
  - Teams, users
  - Settings
  - …
- Execute SQL queries
- Check API status via `/api/status`

For accessing the API you must provide a session token, or API token via the HTTP header:

`Authorization: Bearer <token>`

For the full documentation check out the automatically generated documentation page:
[Airsequel](https://www.airsequel.com/api/docs)

---

# Functions

Functions enable you to create custom code callable using the Airsequel API. Functions are written in TypeScript (executed with [Deno](https://deno.com/)) or Python (version 3.10).

Functions generally follow the same basic structure:

**Deno:**

```tsx
export default function(context) {
  return { data: ... }
}
```

**Python:**

```python
async def main(context):
  return { 'data': ... }
```

## The Context Argument

The context argument contains many useful pieces of information:

- `functionId` contains the id of the functions being called
- `databaseId` contains the id of the database the function is attached to
- `db` contains an instance of connection to the current database
  - Deno
    In deno, a connection created using [the deno sqlite library](https://deno.land/x/sqlite3@0.9.1) is provided.
  - Python
    In python, a connection created using [the built in sqlite library](https://docs.python.org/3/library/sqlite3.html) is provided. Moreover, the `main` function is run inside a `with db` block, automatically commiting changes.

  The provided connection is configured with a busy timeout of 5 seconds, so writes wait for concurrent writers (e.g. GraphQL mutations) instead of failing immediately with "database is locked". If you open additional connections to the database yourself, set the timeout on them as well:

  ```ts
  // Deno
  const conn = new sqlite.Database(context.databasePath)
  conn.exec("PRAGMA busy_timeout = 5000")
  ```

  ```python
  # Python (`timeout` defaults to 5 seconds)
  conn = sqlite3.connect(database_path, timeout=5)
  ```
- `graphql` contains a GraphQL client for the current database
  - Deno
    In Deno, a client provided by the [graphql_request](https://deno.land/x/graphql_request@v4.1.0) library is created
  - Python
    In Python, a client provided by the [python-graphql-client](https://pypi.org/project/python-graphql-client/) library is provided
- `method` the HTTP method the function got called with
- `headers` the HTTP headers the function got called with
- `data` the body of the HTTP request the function got called with

## Calling Functions

Functions can be called by making requests to its `/fns/<function-id>` endpoint as shown on the function overview page.

## Runtime System

Unlike other FaaS offerings, our functions are not directly handling the HTTP requests and responses. Rather, our runtime system is handling the HTTP request, and your function is called with a simple request JSON object and can then return a response JSON object. This allows you to focus on your business logic, and not worry about the details of the HTTP request or how to implement a HTTP server.

## Resource Limits

| Number of Functions                         | Fair-use policy         |
| ------------------------------------------- | ----------------------- |
| RAM per Function                            | 256 MB                  |
| Maximum Execution Time                      | 10 s                    |
| Log Retention                               | Last 500 function calls |
| (Maximum 100 log entries per function call) |

## View Function Invocations

Currently there is no GUI for viewing a log of function invocations. However, it can be accessed via our [Admin JSON API](Admin%20API%20f6b0bf68e3a9440787f9a62051a5e78c.md) (using [HTTPie](https://httpie.io) here):

```bash
http GET \
  https://www.airsequel.com/api/dbs/<dbId>/functions/<funcId>/invocations \
  Authorization:"Bearer <tokenId>"
```

Returns:

```json
{
  "invocations": [
    {
      "createdUtc": "2023-12-19T23:01:26.039Z",
      "logs": [{ "stream": "stdout", "message": "…", "utc": "…" }]
    }
  ]
}
```

It only stores the last **500 invocations** for each function.

## A Hands-On Example

We start by creating a simple todos table (via the SQL queries tab):

```sql
CREATE TABLE todos (
  name TEXT NOT NULL PRIMARY KEY,
  completed BOOLEAN NOT NULL DEFAULT FALSE
)
```

Functions can than be created from the “functions” tab in the database view.

![Functions UI](assets/screenshots/2023-12-22t2029_functions.png)

Let's create a simple todo-list function:

- TypeScript

  ```tsx
  import type { Database } from "https://deno.land/x/sqlite3@0.10.0/mod.ts"

  export default async function (context: {
    method: "GET" | "POST" | "PATCH" | "PUT" | "DELETE"
    databasePath: string
    data: {
      name?: string
    }
    db: Database
  }) {
    switch (context.method) {
      case "GET":
        return {
          data: context.db
            .prepare("SELECT name from todos where completed = 0")
            .all()
            .map(({ name }) => name),
        }

      case "POST":
        context.db.exec(
          `INSERT INTO todos(name) VALUES('${context.data.name}')`,
        )

        return { data: "Todo created succesfully" }

      case "DELETE":
        context.db.exec(`DELETE FROM todos WHERE name = '${context.data.name}'`)

        return { data: "Todo deleted succesfully" }

      case "PATCH":
        context.db.exec(
          `UPDATE todos SET completed = 1 where name = '${context.data.name}'`,
        )

        return { data: "Todo completed succesfully" }
    }
  }
  ```

- Python

  ```python
  async def main(context):
      if context.method == "GET":
          res = context.db.execute(
              "SELECT name from todos where completed = 0"
          ).fetchall()

          return {"data": list(map(lambda r: r[0], res))}
      elif context.method == "POST":
          context.db.execute(
              f"""INSERT INTO todos(name) VALUES('{
                  context.data["name"]
              }')"""
          )

          return {"data": "Todo created succesfully"}
      elif context.method == "DELETE":
          context.db.execute(
              f"""DELETE FROM todos WHERE name = '{
                  context.data["name"]
              }'"""
          )

          return {"data": "Todo deleted succesfully"}
      elif context.method == "PATCH":
          context.db.execute(
              f"""UPDATE todos SET completed = 1 where name = '{
                  context.data["name"]
              }'"""
          )

          return {"data": "Todo completed succesfully"}
  ```

> ⚠️ **This code is not production ready!**
>
> Everything is bundled up in a single function, there's no input validation, etc.

We can start by attempting to call our function. We will use [HTTPie](https://httpie.io/) for all examples.

```bash
$ http GET https://www.airsequel.com/fns/01hbv54akc0zpejb7f59d63qpa
{
  "data": [],
  "extensions": { "logs": [] }
}
```

There's nothing there! Let's change that by creating a todo:

```bash
$ http POST https://www.airsequel.com/fns/01hbv54akc0zpejb7f59d63qpa name=hello
{
  "data": "Todo created succesfully",
  "extensions": { "logs": [] }
}
```

Running the `GET` method will now yield:

```json
{
  "data": ["hello"],
  "extensions": { "logs": [] }
}
```

We can run the previous command with different arguments in order to create even more entries:

```json
$ http POST http://localhost:4185/fns/01hbv54akc0zpejb7f59d63qpa name=world
$ http POST http://localhost:4185/fns/01hbv54akc0zpejb7f59d63qpa name=oops
$ http GET http://localhost:4185/fns/01hbv54akc0zpejb7f59d63qpa
{
  "data": ["hello", "world", "oops"],
  "extensions": { "logs": []}
}
```

Let's delete the `oops` todo by using the `DELETE` method, and complete the `hello` todo using the `PATCH` method:

```bash
$ http DELETE http://localhost:4185/fns/01hbv54akc0zpejb7f59d63qpa name=oops
{
  "data": "Todo delete succesfully",
  "extensions": { "logs": [] }
}

$ http PATCH http://localhost:4185/fns/01hbv54akc0zpejb7f59d63qpa name=hello
{
  "data": "Todo completed succesfully",
  "extensions": { "logs": [] }
}
```

We can now run the `GET` method one last time in order to ensure everything is working as expected:

```bash
$ http GET http://localhost:4185/fns/01hbv54akc0zpejb7f59d63qpa
{
  "data": ["world"],
  "extensions": { "logs": [] }
}
```

## Available packages

The following common packages are included in the environment:

- Python

  | Package       | Version |
  | ------------- | ------- |
  | numpy         | 1.26.4  |
  | scipy         | 1.12.0  |
  | sympy         | 1.12    |
  | pandas        | 2.2.1   |
  | matplotlib    | 3.8.3   |
  | seaborn       | 0.13.2  |
  | sqlalchemy    | 2.0.27  |
  | requests      | 2.31.0  |
  | requests-html | 0.10.0  |
  | openpyxl      | 3.1.2   |


<!-- TODO: Check if this already works
## Executing GraphQL queries

We can achieve the same functionality by using the provided GraphQL API.
For instance, the `GET` branch of our previous implementation can be rewritten as follows:

- Typescript

  ```tsx
  import type { GraphQLClient } from "https://deno.land/x/graphql_request/mod.ts"

  export default async function (
    context: {
      method: "GET" | "POST" |  "PATCH" | "PUT" | "DELETE",
      databasePath: string,
      data: {
        name?: string
      },
      graphql: GraphQLClient
    },
  ) {
    switch (context.method) {
      case "GET":
        const response = await context.graphql.request(`
          query todos {
            todos(filter: { completed: { eq: false }}) {
              name
            }
          }
        `)

        return {
          data: response.todos.map(t => t.name)
        }
      ...
    }
  }
  ```

- Python

  ```tsx
  async def main(context):
    if context.method == "GET":
      query = """
        query todos {
          todos(filter: { completed: { eq: false }}) {
            name
          }
        }
      """
      res = context.graphql.execute(query = query)

      return {"data": list(map(lambda r: r['name'], res['data']['todos']))}
    ...
  ```
-->


## Troubleshooting

### "database is locked" errors

SQLite allows only one writer at a time. If your function writes to the database while another write is in progress (e.g. a GraphQL mutation or another function invocation), the write has to wait. The connection provided via `context.db` waits up to 5 seconds before giving up, but connections you open yourself fail immediately unless you set a busy timeout (see [The Context Argument](#the-context-argument) above).

Long-running write transactions block all other writers, so keep them short.

### Network requests

If you get an error like this when making a network request (e.g. with `fetch()`):

```json
error: Uncaught PermissionDenied:
Requires net access to "example.com", run again with the --allow-net flag
…
```

Please contact us at [support@feram.io](mailto:support@feram.io)
and request that the used URL should be allowed.
We will then check if the URL is safe and allowlist it if applicable.

---

# Tutorials

## General Concepts

[User Management](/tutorials/user-management)


## App Development

### Simple Todo App with React

[reactjs.org](https://reactjs.org/) - A JavaScript library for building user interfaces.

[How to build a todo app with Airsequel and React](/tutorials/react-todo-app)


### Simple Todo App with Elm

[elm-lang.org](https://elm-lang.org/) - Programming language for reliable web applications.

[How to build a todo app with Airsequel and Elm](/tutorials/elm-todo-app)


### Lowdefy

[lowdefy.com](https://lowdefy.com/) - An open-source low-code framework to build web apps, admin panels, BI dashboards, workflows, and CRUD apps.


#### Simple Dashboard with Lowdefy

[**How to build a dashboard with Airsequel and Lowdefy**](/tutorials/lowdefy-dashboard)


#### Simple Todo App with Lowdefy

[How to build a todo app with Airsequel and Lowdefy](/tutorials/lowdefy-todo)

---

# Keyboard Shortcuts

## Tables

### Grid

- `Enter` - Submit your input
- `Shift + Enter` - Insert a newline
- `Tab` - Jump horizontally to next cell

### Focus View

- `Enter` and `Shift + Enter` - Insert a newline
- `Cmd + Enter` and `Ctrl + Enter` - Submit your input
- `Tab` - Jump vertically to next input field

---

2023-12-28 10:13 - JSON API Docs
![2023-12-28t1013_json_api_docs.png](/assets/screenshots/2023-12-28t1013_json_api_docs.png)

2023-12-28 10:10 - SQL Docs Search
![2023-12-28t1010_sql_docs_search.png](/assets/screenshots/2023-12-28t1010_sql_docs_search.png)

2023-12-22 21:19 - Generated Column
![2023-12-22t2119_generated_column.png](/assets/screenshots/2023-12-22t2119_generated_column.png)

2023-12-22 20:29 - Functions
![2023-12-22t2029_functions.png](/assets/screenshots/2023-12-22t2029_functions.png)

2023-11-18 06:47 - Runtime Selector
![2023-11-18t0647_runtime_selector.png](/assets/screenshots/2023-11-18t0647_runtime_selector.png)

2023-11-12 09:15 - Database Overview
![2023-11-12t0915_database_overview.png](/assets/screenshots/2023-11-12t0915_database_overview.png)

2023-09-23 12:11 - Chart Editor
![2023-09-23t1211_chart_editor.png](/assets/screenshots/2023-09-23t1211_chart_editor.png)

2023-09-23 10:11 - AI Powered Query Generation
![2023-09-23t1011_ai_powered_query_generation.png](/assets/screenshots/2023-09-23t1011_ai_powered_query_generation.png)

2023-07-20 20:06 - Team Overview
![2023-07-20t2006_team_overview.png](/assets/screenshots/2023-07-20t2006_team_overview.png)

2023-05-24 14:01 - Database Settings
![2023-05-24t1401_database_settings.png](/assets/screenshots/2023-05-24t1401_database_settings.png)

2023-05-02 22:13 - AI SQL Query
![2023-05-02t2213_ai_sql_query.png](/assets/screenshots/2023-05-02t2213_ai_sql_query.png)

2023-04-14 16:35 - Functions
![2023-04-14t1635_functions.png](/assets/screenshots/2023-04-14t1635_functions.png)

2023-04-14 16:26 - Dashboards
![2023-04-14t1626_dashboards.png](/assets/screenshots/2023-04-14t1626_dashboards.png)

2023-04-14 16:22 - SQL Queries
![2023-04-14t1622_sql_queries.png](/assets/screenshots/2023-04-14t1622_sql_queries.png)

2023-04-14 16:14 - GraphiQL
![2023-04-14t1614_graphiql.png](/assets/screenshots/2023-04-14t1614_graphiql.png)

2023-03-28 15:46 - Landing page
![2023-03-28t154614_landing_page.png](/assets/screenshots/2023-03-28t154614_landing_page.png)

2023-03-21 12:59 - Add chart
![2023-03-21t125909_add_chart.png](/assets/screenshots/2023-03-21t125909_add_chart.png)

2023-02-22 14:42 - Table menu
![2023-02-22t144200_table_menu.png](/assets/screenshots/2023-02-22t144200_table_menu.png)

2023-02-13 15:08 - AI prompt
![2023-02-13t150830_ai_prompt.png](/assets/screenshots/2023-02-13t150830_ai_prompt.png)

2023-02-09 07:52 - SQL queries
![2023-02-09t075249_sql_queries.png](/assets/screenshots/2023-02-09t075249_sql_queries.png)

2022-12-20 16:15 - Header
![2022-12-20t161534_header.png](/assets/screenshots/2022-12-20t161534_header.png)

2022-12-14 15:56 - Upload section
![2022-12-14t162126_sql_queries.png](/assets/screenshots/2022-12-14t162126_sql_queries.png)

2022-12-12 17:12 - Header
![2022-12-12t171200_header.png](/assets/screenshots/2022-12-12t171200_header.png)

2022-12-12 17:05 - Double X encoding
![2022-12-12t170534_double_x_encoding.png](/assets/screenshots/2022-12-12t170534_double_x_encoding.png)

2022-12-12 17:01 - Emoji column names
![2022-12-12t170121_emoji_column_names.png](/assets/screenshots/2022-12-12t170121_emoji_column_names.png)

2022-12-12 16:30 - Hidden columns
![2022-12-12t163037_hidden_columns.png](/assets/screenshots/2022-12-12t163037_hidden_columns.png)

2022-12-12 16:28 - Single select
![2022-12-12t162818_single_select.png](/assets/screenshots/2022-12-12t162818_single_select.png)

2022-12-12 16:15 - Table menu
![2022-12-12t161550_table_menu.png](/assets/screenshots/2022-12-12t161550_table_menu.png)

2022-12-11 22:43 - Add chart
![2022-12-11t224348_add_chart.png](/assets/screenshots/2022-12-11t224348_add_chart.png)

2022-12-11 22:42 - View
![2022-12-11t224249_view.png](/assets/screenshots/2022-12-11t224249_view.png)

2022-12-11 22:37 - Dashboards
![2022-12-11t223713_dashboards.png](/assets/screenshots/2022-12-11t223713_dashboards.png)

2022-12-11 22:31 - SQL queries
![2022-12-11t223139_sql_queries.png](/assets/screenshots/2022-12-11t223139_sql_queries.png)

2022-12-11 21:35 - TODOs table
![2022-12-11t213528_todos_table.png](/assets/screenshots/2022-12-11t213528_todos_table.png)

2022-12-11 00:10 - Disk full
![2022-12-11t001041_disk_full.png](/assets/screenshots/2022-12-11t001041_disk_full.png)

2022-12-07 18:11 - Settings general
![2022-12-07t181156_settings_general.png](/assets/screenshots/2022-12-07t181156_settings_general.png)

2022-12-07 18:09 - Settings database
![2022-12-07t180928_settings_database.png](/assets/screenshots/2022-12-07t180928_settings_database.png)

2022-12-05 20:51 - Confirm dialog
![2022-12-05t205124_confirm_dialog.png](/assets/screenshots/2022-12-05t205124_confirm_dialog.png)

2022-12-05 14:53 - Single select
![2022-12-05t145319_single_select.png](/assets/screenshots/2022-12-05t145319_single_select.png)

2022-12-05 10:39 - Multi type
![2022-12-05t103913_multi_type.png](/assets/screenshots/2022-12-05t103913_multi_type.png)

2022-11-30 16:26 - Unusual column names
![2022-11-30t162635_unusual_column_names.png](/assets/screenshots/2022-11-30t162635_unusual_column_names.png)

2022-11-22 11:34 - Function settings
![2022-11-22t113429_function_settings.png](/assets/screenshots/2022-11-22t113429_function_settings.png)

2022-11-16 14:43 - Data headers
![2022-11-16t144319_data_headers.png](/assets/screenshots/2022-11-16t144319_data_headers.png)

2022-11-14 10:21 - Database name
![2022-11-14t102156_database_name.png](/assets/screenshots/2022-11-14t102156_database_name.png)

2022-11-11 16:02 - Add chart
![2022-11-11t160207_add_chart.png](/assets/screenshots/2022-11-11t160207_add_chart.png)

2022-11-11 11:09 - Bar chart
![2022-11-11t110912_bar_chart.png](/assets/screenshots/2022-11-11t110912_bar_chart.png)

2022-11-03 15:51 - Create view
![2022-11-03t155144_create_view.png](/assets/screenshots/2022-11-03t155144_create_view.png)

2022-10-28 20:30 - Functions call
![2022-10-28t203045_functions_call.png](/assets/screenshots/2022-10-28t203045_functions_call.png)

2022-10-20 17:59 - Add chart
![2022-10-20t175934_add_chart.png](/assets/screenshots/2022-10-20t175934_add_chart.png)

2022-10-12 18:33 - SQL queries
![2022-10-12t183331_sql_queries.png](/assets/screenshots/2022-10-12t183331_sql_queries.png)

2022-10-12 18:25 - Boolean
![2022-10-12t182554_boolean.png](/assets/screenshots/2022-10-12t182554_boolean.png)

2022-10-11 09:42 - Landing page
![2022-10-11t0942_landing_page.png](/assets/screenshots/2022-10-11t0942_landing_page.png)

2022-08-20 15:51 - Overview
![2022-08-20t155158_overview.png](/assets/screenshots/2022-08-20t155158_overview.png)

2022-08-20 14:46 - SQL queries
![2022-08-20t144606_sql_queries.png](/assets/screenshots/2022-08-20t144606_sql_queries.png)

2022-08-20 14:37 - Tables
![2022-08-20t143741_tables.png](/assets/screenshots/2022-08-20t143741_tables.png)

2022-08-17 22:21 - SQL queries
![2022-08-17t222112_sql_queries.png](/assets/screenshots/2022-08-17t222112_sql_queries.png)

2022-07-27 18:16 - Cell menu
![2022-07-27t181623_cell_menu.png](/assets/screenshots/2022-07-27t181623_cell_menu.png)

2022-07-27 07:31 - Cell menu
![2022-07-27t073134_cell_menu.png](/assets/screenshots/2022-07-27t073134_cell_menu.png)

2022-07-15 07:48 - Tables
![2022-07-15t0748_tables.png](/assets/screenshots/2022-07-15t0748_tables.png)

2022-07-15 07:48 - Overview
![2022-07-15t0748_overview.png](/assets/screenshots/2022-07-15t0748_overview.png)

2022-07-15 07:48 - Landing page
![2022-07-15t0748_landing_page.png](/assets/screenshots/2022-07-15t0748_landing_page.png)

2022-06-26 19:11 - Delete popup new
![2022-06-26t1911_delete_popup_new.png](/assets/screenshots/2022-06-26t1911_delete_popup_new.png)

2022-06-26 19:10 - Delete popup
![2022-06-26t1910_delete_popup.png](/assets/screenshots/2022-06-26t1910_delete_popup.png)

2022-06-26 19:09 - Tables
![2022-06-26t1909_tables_new.png](/assets/screenshots/2022-06-26t1909_tables_new.png)

2022-06-26 19:08 - Tables
![2022-06-26t1908_tables_old.png](/assets/screenshots/2022-06-26t1908_tables_old.png)

2022-05-10 22:29 - Landing Page
![2022-05-10_landing_page.png](/assets/screenshots/2022-05-10_landing_page.png)

---

# Templates

On following pages you'll find some template databases to help you get started with your own projects.


## Airsequel Sheet Music

![Airsequel Sheet Music Website](/assets/templates/airsequel-sheet-music.png)

- Official website: [sheet-music.airsequel.app](https://sheet-music.airsequel.app/)
- Link to create a copy of the template database:
    [airsequel.com/readonly/6mcw4qrv1wvtwn65/duplicate](https://www.airsequel.com/readonly/6mcw4qrv1wvtwn65/duplicate)

---

# Apps Powered By Airsequel


## 🎓 Official Examples

Collection of simple example apps to get you started with app development for Airsequel.

- Repository: [https://github.com/Airsequel/Examples](https://github.com/Airsequel/Examples)
- Technologies:
    [Elm](https://elm-lang.org/),
    [React](react.dev),
    [PureScript](https://www.purescript.org/),
    [Lowdefy](https://lowdefy.com/)


## 🎼 Airsequel Sheet Music

Simple sheet music management app built with Elm Land.

- Website: [sheet-music.airsequel.app](https://sheet-music.airsequel.app/)
- Repository: [github.com/Airsequel/Airsequel-Sheet-Music](https://github.com/Airsequel/Airsequel-Sheet-Music)


## 🔁 RepeatGPT

RepeatGPT lets you schedule recurring ChatGPT executions and sends you the result via email.

- Website: **[repeatgpt.com](https://repeatgpt.com/)**
- Repository: [github.com/Airsequel/RepeatGPT](https://github.com/Airsequel/RepeatGPT)


## ✅ TaskLite Webapp

TaskLite is a powerful CLI first task manager built with [Haskell](https://haskell.org/) and [SQLite](https://sqlite.org/). The TaskLite Webapp makes it possible to manage the tasks via the web if the SQLite database is uploaded to Airsequel.

- Website: [tasklite.org](https://tasklite.org)
- Repository: [github.com/ad-si/TaskLite](https://github.com/ad-si/TaskLite)


## ⏱ Task Tracker

Simple time tracker that enables fast and frictionless tracking, by letting users enter data with few clicks right when they change tasks.

- Repository: [github.com/mbornstein/task-tracker](https://github.com/mbornstein/task-tracker)


## 📖 Blog

Simple blogging platform with CMS powered by Airsequel.

- Website: [blog.airsequel.com](https://blog.airsequel.com/)
- Repository: _soon to be released_

---

# Changelog

This document lists all notable public changes to the functionality of Airsequel.


### Unreleased

- **GraphQL API**
  - Add `_by_pk` queries, updates, and deletes for single-row access by primary key
  - Make error responses spec-compliant and support partial errors
  - Report the number of affected rows for `INSERT`/`UPDATE`/`DELETE` SQL queries
  - Align the schema more closely with Hasura's (several fields renamed)
  - Add insert-only API tokens
- **Spreadsheet UI**
  - Add a standalone edit page for table entries and an "Add entry" form
  - Support file uploads directly in the add-entry form
  - Raise the cell display limit to 10,000
  - Constrain cell sizing and wrap overflowing content in grid views
- **SQL Workbench**
  - Display the number of rows in the query result
  - Allow `replace`, `like`, and `glob` as scalar functions
  - Skip the deletion warning for `ON DELETE CASCADE` queries
- **Admin & Account**
  - Add a user settings page to delete your own account
  - Let admins override the maximum size of an individual database from the
    admin databases table, taking precedence over the plan default
  - Enforce the database size limit on CSV imports (previously unenforced)
  - Sortable, server-side paginated databases table; unassigned databases
    are no longer shown by default
  - Scope API tokens to the current database and show the last 4 characters
    of each key
  - Show table and view counts separately on the database page
- **Appearance**
  - Light / dark / automatic color scheme support
- **Documentation**
  - Add an FAQ page and an Airsequel-vs-Hasura comparison page
  - Download all documentation as a single Markdown file
- **Pro plan**
  - Raise the row limit to 1,000,000 rows for Pro accounts
- **Functions**
  - Upgrade the bundled runtimes: Deno 2.8, Python 3.14, and SQLite 3.45
- **Security & reliability**
  - Harden authentication and authorization
  - Prevent migrations from cascade-deleting child tables
  - Use `BEGIN IMMEDIATE` for write transactions and enforce the foreign-key
    pragma everywhere


### 2024-01-03 - v0.7.1

- **GraphQL**
  - Add `in` and `nin` filters
  - Return `NULL` entries for `neq` filter
  - Floats don't change precision during retrieval from database anymore
- Fields in SQL query result are sorted as requested
- Upgrade to OpenAI's GTP 3.5 Turbo for faster and better suggestions
- More data included in [function invocations](/functions) endpoint
- Numerous bug fixes and UI improvements


## 2023-12-19 - v0.7.0

- **Add support for cloud functions**
  ![Untitled](/assets/changelog/Untitled.png)
  - GUI for creating, editing, testing, and deleting functions
  - Support for TypeScript ([Deno](https://deno.com) 1.39)
  - Support for Python 3.10 (checked by [Ruff](https://docs.astral.sh/ruff/))
  - Check out the [documentation](/functions) for more information
- **SQLite**
  - Add support for [generated columns](https://www.sqlite.org/gencol.html)
    ![2023-12-22t2119_generated_column.png](/assets/changelog/2023-12-22t2119_generated_column.png)
  - Add support for [math functions](https://www.sqlite.org/lang_mathfunc.html)
  - Add support for [`GLOB`](https://www.sqlite.org/lang_expr.html#glob) and `DROP INDEX`
  - Add search field to "Docs" tab
    ![2023-12-28t1010_sql_docs_search.png](/assets/changelog/2023-12-28t1010_sql_docs_search.png)
  - Use the same SQLite binary for the GraphQL API and SQL workbench
    (eliminates any differences in behavior)
- **GraphQL**
  - Add support for [upsert mutations](/graphql-api#fc215fbad2124c2084620b82c69b76ee)
  - Add support for multi-column ordering
  - Improve loading speed of GraphiQL playground
- Admin JSON API Documentation at [airsequel.com/api/docs](https://www.airsequel.com/api/docs)
  ![Screenshot 2023-12-28 at 10.13.51.png](/assets/changelog/Screenshot_2023-12-28_at_10.13.51.png)
- Add an affiliate page at [airsequel.com/affiliate](https://www.airsequel.com/affiliate)
- Add a password reset page at [airsequel.com/forgot-password](https://www.airsequel.com/forgot-password)
- Add a "Vacuum Database" button to database settings
- Show database environment and visibility in team databases overview
- Numerous bug fixes and styling improvements


### 2023-10-02 - v0.6.1

- Various UI bug fixes
- Option to reassign a database to another team
  ![2023-10-02t155235_reassign_db.png](/assets/changelog/2023-10-02t155235_reassign_db.png)
- Make default AI prompt for descriptions editable
  ![2023-10-02t160421_description_prompt.png](/assets/changelog/2023-10-02t160421_description_prompt.png)


## 2023-09-06 - v0.6

- Full fledged user and team management
  - Teams own databases
    - Databases are either private or public (unlisted)
  - Existing public databases can be assigned to teams
  - Check out the [documentation](/conceptual-overview#users) for more details
- New paid Pro plan with less restrictions than the free plan.
  Check out the detailed feature comparison at [airsequel.com/pricing](https://airsequel.com/pricing).
- API access tokens for fine grained control on how a database can be accessed via its API
- GraphQL
  - Add support for comparison operators `like` and `ilike`
  - Support filters over several columns in mutations and queries
  - Support `limit` and `offset` arguments to only show a subset of the result rows
  - Support `order_by` argument in queries
  - Results are no longer reversed
- Database Creation
  - "Open Demo Database" opens a fully editable copy of our demo database
  - Add button to create databases from an AI prompt
  - Display a progress bar when uploading databases
- Database Overview
  - Dedicated read-only endpoint to duplicate databases at
    `/readonly/<readonly-id>/duplicate`
  - Add a description field for databases and tables
    - Add input fields to let AI write descriptions
  - Show [user version](https://www.sqlite.org/pragma.html#pragma_user_version) and [schema version](https://www.sqlite.org/pragma.html#pragma_schema_version) of SQLite database
- Spreadsheet View
  - Align numeric column content right
  - Destructive buttons require a long click for activation
  - Display a progress bar when uploading files
  - Shift-Click to select ranges of rows
- SQL Workbench
  - Selector for different text wrapping style in result table
  - Display runtime stats in ms for execution times < 10 sec
- Add a blog at [blog.airsequel.com](https://blog.airsequel.com/)
- Add support chat to every page (powered by Crisp)
- Add page to request an Enterprise trial instance at [airsequel.com/trial](https://airsequel.com/trial)
- Lots of bug fixes and minor improvements


### 2023-04-01 - v0.5.1.0

- Enable AI powered SQL query generation for Free edition


## 2023-03-31 - v0.5

- Spreadsheet UI
  - Add more column data types:
    - File
    - Date
    - URL
    - Color
    - Phone
    - Email
    - Time
    - Duration, Duration ISO
    - JSON, Array, Object
    - Code
    - Location, Alpha 2, Alpha 3
      ![2023-03-28t154614_landing_page copy.png](/assets/changelog/2023-03-28t154614_landing_page_copy.png)
  - Support re-ordering columns and tables
  - Support inserting new columns in specific locations
  - Export tables as CSV or JSON via column drop-down menu
    ![2023-02-22t144200_table_menu.png](/assets/changelog/2023-02-22t144200_table_menu.png)
  - Add "Clear Cells" option for columns
  - Support creating `UNIQUE` columns
  - Make markdown style links clickable in views
  - Highlight selected rows
- SQL Workbench
  - AI powered SQL query generation (via OpenAI)
    ![2023-02-13t150830_ai_prompt.png](/assets/changelog/2023-02-13t150830_ai_prompt.png)
  - Tabs for working on several SQL queries simultaneously
  - Support editing views
  - Upgrade SQLite from **3.38.5** to **3.41.0**.
    Notable new features are the support for [`RIGHT` and `FULL OUTER JOIN`](https://sqlite.org/lang_select.html#rjoin) and the addition of the built-in [`unhex()`](https://sqlite.org/lang_corefunc.html#unhex) function. Check out the [SQLite release history](https://sqlite.org/changes.html) for a list of all changes.
  - Enforce SQLite's foreign key constraints by default
- Additional settings
  - Show separate tabs for "Tables" and "Views"
  - Button to regenerate read-only ID
  - “Template” database environment (can be cloned via its readonly URL)
- Overview page
  - Show database name in header
  - Show database size in a human readable way
  - Show last modified timestamp
- GraphQL API
  - Support requesting changed data with `returning` keyword
  - Add support for more comparison operators (`neq`, `gt`, `gte`, `lt`, `lte`)
- `rowid` is used for stable links to rows
- `rowid` can be omitted when creating a new row and will be set automatically
- Upgrade to GraphiQL 2.2 and speed up initial load
- Use Write-Ahead Log for all databases


#### 2022-12-16 - v0.4.0.1

- Handle columns with double quotes in their name correctly
- Show number of filled cells in delete column dialog
- Show correct version number via status API endpoint
- Show spinner while SQL query is executing


## 2022-12-12 - v0.4

- Edit database schema via UI
  - Button on landing page to create new database from scratch
  - Support adding, renaming and deleting tables
    ![Screen Shot 2022-12-12 at 16.15.50Z.png](/assets/changelog/Screen_Shot_2022-12-12_at_16.15.50Z.png)
  - Support adding, renaming, and deleting columns
  - Support renaming a database
- Allow SQL write queries in Free edition
- Add dashboards with 3 chart types
  - Bar charts
  - Line charts
  - Single value
    ![Screen Shot 2022-12-11 at 22.37.13Z.png](/assets/changelog/Screen_Shot_2022-12-11_at_22.37.13Z.png)
- Improve spreadsheet UI
  - Add new column type “single-select”
    ![Screen Shot 2022-12-12 at 16.28.18Z.png](/assets/changelog/Screen_Shot_2022-12-12_at_16.28.18Z.png)
  - Collapsible columns for better overview in big tables
    ![Screen Shot 2022-12-12 at 16.30.37Z.png](/assets/changelog/Screen_Shot_2022-12-12_at_16.30.37Z.png)
  - Automatically picks unused rowid name (one of `rowid`, `_rowid_`, or `oid`)
- Improve SQL editor
  - Add button to create a view from executed query
  - Show documentation for SQLite functions
  - Replace tab character in SQL query editor with 2 spaces
  - Allow more functions and prevent executing unavailable functions
- Encode column names with Double-X-Encoding to allow any Unicode string as name.
  ![combined.jpg](/assets/changelog/combined.jpg)
  Check out our [announcement blog post for the Double-X-Encoding](https://buttondown.email/Airsequel/archive/announcing-double-x-encoding-encode-any-utf-8/) for more information.
- New and overhauled documentation page at [docs.airsequel.com](https://docs.airsequel.com)
- New database settings page
  - Add environment setting and show badge next to Airsequel logo with current environment
    ![Screen Shot 2022-12-12 at 17.12.00Z.png](/assets/changelog/Screen_Shot_2022-12-12_at_17.12.00Z.png)
  - Add button to regenerate the database ID
- Extend [example database](https://www.airsequel.com/dbs/avatar_the_last_airbender) with views and dashboards
- Confirmation dialog for all destructive actions
- Better error handling and error popups
- Wrap table column tabs
- More details on [pricing page](https://www.airsequel.com/pricing)


### 2022-10-13 - v0.3.1

- Add titles to buttons in focus view toolbar
- Treat unknown data types per default as text
- Add trial information to pricing page


## 2022-10-12 - v0.3

- Add tab to execute SQL queries (read-only in Free edition)
  ![Screen Shot 2022-10-12 at 18.33.31.png](/assets/changelog/Screen_Shot_2022-10-12_at_18.33.31.png)
- Add dedicated GraphiQL (v2) tab for exploring the GraphQL API
- Numerous improvements for spreadsheet GUI
  - Add support for Booleans
    ![Screen Shot 2022-10-12 at 18.25.54.png](/assets/changelog/Screen_Shot_2022-10-12_at_18.25.54.png)
  - Increase / decrease numbers with arrow up / down
  - Load only active table
  - New icons
  - Make views read-only
  - Toolbar for focus view (previous, next, delete, export as JSON, send via email)
  - Better handling of edits, errors, and edge cases
- Display available columns of tables/views on database overview page
- Improvements for GraphQL API
  - Extend equality filter to work on all data types
  - Add missing introspections results
  - Support column names with spaces
- Fix import of small CSV files
- Add a screenshot to landing page and improve slogan
- Upgrade SQLite to 3.38.5


### 2022-09-05

- Fast deployment of Airsequel Enterprise demo instances via [fly.io](https://fly.io). \
  ([Contact us](mailto:sales@feram.io) if you're interested!)


### 2022-09-02

- Tutorial on how to write a simple Todo app with Lowdefy


### 2022-08-04

- Allow special characters in import file name
- Fix GUI issues in Firefox


## 2022-07-28 - v0.2 (Public Beta)

- [New landing page](https://www.airsequel.com) with additional information
- Add spreadsheet GUI to edit data in tables
  - Focus view to edit one row at a time
  - Multiline text cells
  - Export cell content as a file
  - Set cells to NULL or empty string
  - Add new rows and delete existing rows
- Support upload of CSV files
- Enterprise edition with more features
  (compare the different editions at [airsequel.com/pricing](https://www.airsequel.com/pricing))
- Make GraphQL API fully compliant with official spec
  - Return number of affected rows after any mutation
- Build an Elm SPA frontend for better responsiveness
- Add action buttons to overview page
  (Download / Duplicate / Replace / Delete database)
- Validate uploaded SQLite databases
- Limit upload file size of free version to 5 MB
- Set up an [uptime status page](https://status.airsequel.com/)
- Extend documentation and move it to [Notion](https://www.notion.so/Airsequel-Documentation-v0-3-04fbee48928a4303b6911a7dfa431e98?pvs=21)
- Add [pricing page](https://www.airsequel.com/pricing)
- Add endpoint “api/status” for monitoring
- Show hosting location of database on overview page


## 2022-02-27 - v0.1 (Private Beta)

- Automatically create a simple GraphQL API for uploaded databases
  - Including a read-only endpoint for each database
- Support for GraphQL introspection
- Simple overview page for each hosted database
  - Support for downloading the database
  - Playground page to try out GraphQL queries
- Store an additional metadata database for each user database
- Example database with all characters from “Avatar: The Last Airbender”
- Minimal documentation
  - How to use GraphQL API
  - How to write a simple todo app with Elm
- Set up several social media accounts

---

# Frequently Asked Questions

## General

### What is Airsequel?

Airsequel automatically generates a complete GraphQL API and provides a
spreadsheet UI, a SQL workbench, cloud functions, and a dashboard builder
for your SQLite databases and CSV files.
You upload or create a database, and Airsequel gives you a backend
plus a UI to work with it — without writing any boilerplate.

### Do I need an account to try it?

No. You can create a database and start using Airsequel without signing up.
Anonymously created databases are public and expire after 10 days.
To keep a database permanently and make it private, assign it to a team —
which does require an account.

### Why is Airsequel built on SQLite?

SQLite is the most widely deployed database in the world.
It's fast, extremely well tested, fully featured, public domain, and
[supported until at least 2050](https://www.sqlite.org/lts.html).
Because each Airsequel database is a single, standard SQLite file, you're
never locked in: you can export it at any time, work on it with your own
local tools, and upload it again later.
See [Databases](/docs/databases) for the full reasoning.

### Who makes Airsequel?

Airsequel is developed by [Feram](https://feram.io).


## Editions & Pricing

### Which editions are available?

| Edition              | Deployment       | URL                          |
| -------------------- | ---------------- | ---------------------------- |
| **Free**             | Shared server    | airsequel.com                |
| **Pro**              | Shared server    | airsequel.com                |
| **Enterprise Cloud** | Dedicated server | `<your-name>`.airsequel.net  |
| **Enterprise Server**| Self-hosted      | Your own custom domain       |

See the [pricing page](https://www.airsequel.com/pricing) for the full
feature-by-feature comparison and current limits.

### Is there a free plan?

Yes. The Free edition includes the GraphQL API, the SQL workbench,
CSV uploads, SQLite views, and daily backups, with limits such as
10 tables per database, 2000 rows per table, and a 10 s SQL timeout.
Cloud functions and SQLite-file uploads require Pro or higher.

### How much does Pro cost?

Pro is **$4.10 / month / team** when paid annually
(**$4.50 / month / team** when paid monthly).
It raises the limits (e.g. up to 100 tables and 1 000 000 rows per table)
and unlocks cloud functions and SQLite uploads.

### Can I run Airsequel on my own infrastructure?

Yes — that's the **Enterprise Server** edition. It is self-hosted on your
own infrastructure under your own domain, with configurable limits and no
dependency on the shared cloud.
Contact [sales@feram.io](mailto:sales@feram.io?subject=Request%20Offer) for
an offer or to request a trial instance.

### Is Airsequel open source?

The core engine, **AirGQL** — the library that generates a GraphQL API from
any SQLite database — is developed in the open at
[github.com/Airsequel/AirGQL](https://github.com/Airsequel/AirGQL).
It can be used as a Haskell library or as a standalone CLI server.


## Databases & Data

### Where is my data stored?

For the shared Free and Pro editions, data is hosted in Germany
(Nuremberg). Enterprise Cloud can be deployed to any
[fly.io region](https://fly.io/docs/reference/regions/), and Enterprise
Server runs wherever you choose to host it.

### Can I get my data out?

Always. An Airsequel database is a regular SQLite file that you can export
at any moment. We keep your file intact and modify it in the most
straightforward way possible, so there is no proprietary format and no
lock-in.

### What is the `metadata.sqlite` file?

Some features Airsequel needs aren't part of SQLite itself — table/column
ordering, descriptions, and data for charts, dashboards, and cloud
functions. We store these in a separate `metadata.sqlite` file per database
so that your actual data file stays clean and portable.

### How do I upload data?

Through the web UI, or with the [Airput](https://github.com/Airsequel/Airput)
CLI tool for uploading data and files from the command line.
The Free edition accepts CSV uploads; SQLite-file uploads require Pro or
higher.

### Does Airsequel support more than SQLite's basic types?

Yes, via a backwards-compatible convention: column-type suffixes such as
`TEXT_EMAIL` or `TEXT_COLOR` refine the type while still using standard
SQLite type affinity. The UI then shows specialized inputs (e.g. a color
picker). See the
[SQLite-Extra-Types specification](https://github.com/Airsequel/SQLite-Extra-Types).


## API & Functions

### How is the GraphQL API generated?

Airsequel analyses your database schema and automatically builds the
matching GraphQL schema and resolvers, supporting CRUD operations as well
as filtering and pagination. A GraphiQL playground is included for every
database. See [GraphQL API](/docs/graphql-api).

### How does it compare to Hasura?

The query/mutation shapes are similar, with some intentional differences
(e.g. how primary-key updates and naming conflicts are handled). See the
[comparison with Hasura](/docs/graphql-api/comparison-with-hasura).

### What languages can cloud functions be written in?

TypeScript (executed with [Deno](https://deno.com/)) or Python 3.10.
Each function gets a JSON request and returns a JSON response, so you don't
have to implement an HTTP server.
Limits are 256 MB RAM and a 10 s execution time per call, with the last
500 invocations retained for logs. See [Functions](/docs/functions).


## Support & Privacy

### How do I get help?

Free-edition users have the self-serve documentation. Pro includes email
support, and the Enterprise editions add phone and on-site options.
You can always reach us at
[support@feram.io](mailto:support@feram.io).

### A cloud function can't reach an external URL — what do I do?

Outbound network access from functions is allowlisted. If you hit a
`PermissionDenied` / `Requires net access` error, email
[support@feram.io](mailto:support@feram.io) with the URL and we'll review
and allowlist it if it's safe.

---

# Comparison with [Hasura](https://hasura.io/)

### General differences

- Airsequel does not currently support nested boolean expressions for filtering (i.e. expressions involving `or` operators and the like). When querying, this can be worked around using views. Moreover, Airsequel does not currently have a dedicated `is_null` filtering field (although the same functionality can be replicated using `_eq: null`).
- Hasura will fail when attempting to create tables `<name>` and `<name>_by_pk` (it is not clear what happens when attempting to import a database which already contains two such tables). Airsequel will simply append underscores to the name of queries/mutations until one that doesn't conflict with a table name has been found. This has the advantage of supporting imports of as many existing databases as possible.

### Reads

- The `order_by` parameter consists of an array of records in both Airsequel and Hasura. While Hasura offers more granular control over where the nulls should be placed among the results (at the start/end), the main two options (`asc` and `desc`) work the same way as they do in Airsequel (although in Airsequel the aforementioned lowercase enum variants have been deprecated in favour of `ASC` and `DESC` respectively).
- While our GraphQL API does not offer a direct way to perform joins, the `distinct_on` parameter, and aggregate queries, the functionality can be replicated using views.

### Inserts

- The `on_conflict` parameter is similar across engines, although Airsequel allows passing in an array containing more than one such clause (while Hasura allows at most one).
- While our GraphQL API does not support `insert_one` mutations, the basic `insert` mutations can be used instead.

### Updates

- In `update_<name>_by_pk` mutations, Hasura's `pk_columns` record exists instead as a flattened set of arguments in Airsequel. That is, the following two mutations are equivalent in their respective engines:

  ```graphql
  mutation Hasura {
    update_users_by_pk(
      pk_columns: { email: "john@example.com" }
      _set: { name: "John Doe" }
    ) {
      affected_rows
    }
  }

  mutation Airsequel {
    update_users_by_pk(email: "john@example.com", _set: { name: "John Doe" }) {
      affected_rows
    }
  }
  ```

  When a column named `_set` exists in the table, the `_set` parameter will have underscores appended to its name until the name is free to use.

- While our GraphQL API does not support `update_<name>_many` mutations (nor their `_by_pk` variation) directly, one can perform the individual mutations sequentially instead.
- Similarly, one can replicate the not currently supported `_inc` parameter (for incrementing numeric values) by performing the updates using the REST query api instead.

### Deletes

There are no delete-specific differences between the two engines.

---

# User Management

User management logic can be implemented in 3 ways:

1. The logic is implemented in the frontend / webapp and a cloud function acts as an authorization proxy to the automatically created GraphQL Backend

   - Example Code

     ```tsx
     import { gql } from "https://deno.land/x/graphql_tag@0.0.1/mod.ts"
     import * as bcrypt from "https://deno.land/x/bcrypt@v0.4.1/mod.ts"

     const gqlApi = "http://localhost:4185/dbs/<TODO>/graphql"

     interface User {
       id: number
       email: string
       name: string
       password_hash?: string
     }

     async function saveUserWithToken(user: User, token: string) {
       const response = await fetch(gqlApi, {
         method: "POST",
         headers: { "Content-Type": "application/json" },
         body: JSON.stringify({
           query: `
             mutation UpdateSessionToken(
               $user: users_set_input!,
               $usersFilter: users_filter!
             ) {
               update_users(filter: $usersFilter, set: $user) {
                 affected_rows
               }
             }
           `,
           variables: {
             usersFilter: {
               email: {
                 eq: user.email,
               },
             },
             user: {
               email: user.email,
               session_token: token,
             },
           },
         }),
       })

       if (response.ok) {
         return { data: { sessionToken: token } }
       } else {
         return { errors: [{ message: "Failed to save session" }] }
       }
     }

     async function loadUserFromToken(
       bearerToken: string,
     ): Promise<User | null> {
       const token = bearerToken.replace(/^Bearer /, "")
       const response = await fetch(gqlApi, {
         method: "POST",
         headers: { "Content-Type": "application/json" },
         body: JSON.stringify({
           query: `
               query GetUserForToken($userFilter: users_filter) {
                 users(filter: $userFilter) {
                   rowid
                   email
                   first_name
                   last_name
                   session_token
                 }
               }
             `,
           variables: {
             userFilter: { session_token: { eq: token } },
           },
         }),
       })

       if (response.ok) {
         const jsonResponse = await response.json()
         const user = jsonResponse.data?.users?.[0]
         if (user) {
           user.id = user.rowid
           user.name = user.first_name + " " + user.last_name
           delete user.session_token
           return user
         }
       } else {
         console.error(response)
       }

       return null
     }

     async function loadUsers(): Promise<User[]> {
       // Password hash: BCrypt
       // https://gchq.github.io/CyberChef/#recipe=Bcrypt(10)

       const response = await fetch(gqlApi, {
         method: "POST",
         headers: { "Content-Type": "application/json" },
         body: JSON.stringify({
           query: `
           query GetUsers {
             users {
               email
               password_hash
             }
           }
         `,
         }),
       })

       if (response.ok) {
         const jsonResponse = await response.json()
         return jsonResponse?.data?.users
       } else {
         console.error(response)
         return []
       }
     }

     interface GQLResponse {
       data?: Record<string, any>
       errors?: { message: string }[]
     }

     export default async function (context): Promise<GQLResponse> {
       const gqlQuery = context.data.query
       const queryName = gql`
         ${gqlQuery}
       `?.definitions[0]?.name?.value

       if (queryName === "LoginRequest") {
         const argsObj = gql`
           ${gqlQuery}
         `?.definitions[0]?.selectionSet?.selections[0]?.arguments
         const email = argsObj?.find((arg) => arg.name.value === "email")?.value
           ?.value
         const password = argsObj?.find((arg) => arg.name.value === "password")
           ?.value?.value
         // const rememberMe = argsObj
         //  ?.find(arg => arg.name.value === "rememberMe")
         //  ?.value
         //  ?.value

         if (email && password) {
           const users = await loadUsers()
           const user = users.find((user) => user.email === email)

           if (user && bcrypt.compareSync(password, user?.password_hash)) {
             user.name = ""
             const randomToken = Math.random().toString(36).substring(2)
             const saveResult = await saveUserWithToken(user, randomToken)
             return saveResult
           } else {
             return {
               errors: [
                 {
                   message:
                     "User does not exist or an incorrect password was specified",
                 },
               ],
             }
           }
         }
       }

       if (queryName === "UserRequest") {
         return { data: await loadUserFromToken(context.headers.Authorization) }
       }

       const response = await fetch(gqlApi, {
         method: "POST",
         headers: {
           "Content-Type": "application/json",
         },
         body: JSON.stringify({ query: gqlQuery }),
       })

       if (response.ok) {
         const jsonResponse = await response.json()
         return jsonResponse
       } else {
         return {
           errors: [{ message: "Failed to fetch data" }],
         }
       }
     }
     ```

2. The whole logic is implemented in cloud functions which provide their own JSON API. The database access can then either be implemented by making GraphQL requests to the GraphQL resolver or by directly accessing the SQLite database.
   (Use the example code from 1. and extend it with business logic functions that write directly to the database as seen in the [functions documentation page](../Functions%2016b9d5bb71cf45c3acf0ac161c029d61.md))
3. The logic is implemented in an additional external server (written in any language) which provides a JSON or GraphQL API to your APP and calls Airsequel's GraphQL API to access the database.

Here is an architecture diagram of the involved components:

![Screenshot 2023-12-26 at 11.39.47.png](/assets/tutorials/user-management/Screenshot_2023-12-26_at_11.39.47.png)


---

# How to Build a Todo App with Airsequel and React

<aside>
⚠️ This tutorial is still a work in progress, but for the finished todo app, please check out the code at [github.com/Airsequel/Examples/tree/main/react-simple-todo-app](https://github.com/Airsequel/Examples/tree/main/react-simple-todo-app)

</aside>

In this tutorial we will be building a very simple todo app with React. It will load the todos via GraphQL from a SQLite database hosted on Airsequel. To simplify the setup we will use [Create React App](https://create-react-app.dev/) to generate a bare bones React app.

This is what the final app will look like. Not pretty, but functional!

![Screen Shot 2022-02-27 at 11.30.08Z.png](/assets/tutorials/react-todo/Screen_Shot_2022-02-27_at_11.30.08Z.png)

If you find any problems in this tutorial, please feel free to leave a comment!

## Setting up the Airsequel Database

Things to know:

- As the primary key we use the automatically generated `rowid` column.

First, let's create the todo database in Airsequel's UI. If you're on Airsequel Enterprise you can alternatively use following steps:

- Alternative way for Airsequel Enterprise: Upload an existing SQLite database
    <aside>
    💡 The next steps describe how to create the database with the `sqlite3` cli tool. You can of course also use one of the many graphical database management apps like e.g. [DB Browser for SQLite](https://sqlitebrowser.org) or [Beekeeper Studio](https://www.beekeeperstudio.io/).

    </aside>

  1. Create a `schema.sql` file for creating the SQLite database:

     - `schema.sql`

       ```sql
       CREATE TABLE todos (
         title TEXT NOT NULL,
         completed BOOLEAN DEFAULT (FALSE) NOT NULL
       );

       -- Add some example todos
       INSERT INTO todos (title, completed)
       VALUES
         ('Buy milk', 0),
         ('Go hiking', 1);
       ```

  2. Execute SQL query to create the database:

     ```bash
     sqlite3 todos.sqlite < schema.sql
     ```

  3. Go to [airsequel.com](https://www.airsequel.com/) and upload the `todos.sqlite` file.

Or build it up from scratch:

1. Go to [airsequel.com](https://www.airsequel.com/) (or your custom Enterprise instance)
2. Click on “Create New SQLite Database”
3. Rename database to “todos”
4. Go to the “Tables” tab
5. Rename “table_1” to “todos”
6. Rename column “name” to “title”
7. Add a new column “completed” with type “Boolean” and activate “Not Null”
8. Create a few todos like “Buy milk” and “Go hiking” to make working with it easier

🎉 Your database and the corresponding GraphQL endpoint are now ready!

It should look something like this:

![Table view](/assets/tutorials/react-todo/Screen_Shot_2022-12-11_at_21.35.28Z.png)


## Creating the React app

1. Create the boilerplate code (press `y` on all prompts)

   ```bash
   npx create-react-app@latest react-todo-app
   ```

> 🚧 To be continued …

---

# How To Build a Todo App With Airsequel and Elm

In this tutorial we will be building a very simple todo app with Elm's core library and the https://github.com/dillonkearns/elm-graphql library. It will load the todos via GraphQL from a SQLite database hosted on [Airsequel](https://www.airsequel.com).

This is what the final app will look like. Not pretty, but functional!

![Screen Shot 2022-10-19 at 09.48.10Z.png](/assets/tutorials/elm-todo/Screen_Shot_2022-10-19_at_09.48.10Z.png)

If you find any problems in this tutorial, please feel free to leave a comment!


## Setting up the Airsequel Database

Things to know:

- As the primary key we use the automatically generated `rowid` column.

First, let's create the todo database in Airsequel's UI. If you're on Airsequel Enterprise you can alternatively use following steps:

- Alternative way for Airsequel Enterprise: Upload an existing SQLite database
    <aside>
    💡 The next steps describe how to create the database with the `sqlite3` cli tool. You can of course also use one of the many graphical database management apps like e.g. [DB Browser for SQLite](https://sqlitebrowser.org) or [Beekeeper Studio](https://www.beekeeperstudio.io/).

    </aside>

  1. Create a `schema.sql` file for creating the SQLite database:

     - `schema.sql`

       ```sql
       CREATE TABLE todos (
         title TEXT NOT NULL,
         completed BOOLEAN DEFAULT (FALSE) NOT NULL
       );

       -- Add some example todos
       INSERT INTO todos (title, completed)
       VALUES
         ('Buy milk', 0),
         ('Go hiking', 1);
       ```

  2. Execute SQL query to create the database:

     ```bash
     sqlite3 todos.sqlite < schema.sql
     ```

  3. Go to [airsequel.com](https://www.airsequel.com/) and upload the `todos.sqlite` file.

Or build it up from scratch:

1. Go to [airsequel.com](https://www.airsequel.com/) (or your custom Enterprise instance)
2. Click on “Create New SQLite Database”
3. Rename database to “todos”
4. Go to the “Tables” tab
5. Rename “table_1” to “todos”
6. Rename column “name” to “title”
7. Add a new column “completed” with type “Boolean” and activate “Not Null”
8. Create a few todos like “Buy milk” and “Go hiking” to make working with it easier

🎉 Your database and the corresponding GraphQL endpoint are now ready!

It should look something like this:

![Screen Shot 2022-12-11 at 21.35.28Z.png](/assets/tutorials/react-todo/Screen_Shot_2022-12-11_at_21.35.28Z.png)


## Creating the Elm app with `elm-graphql`

1.  Create a new directory for the app:

    ```bash
    mkdir todo-app && cd todo-app
    ```

2.  Initialize a new Elm project (press enter on all following prompts):

    ```bash
    npm install elm elm-format
    npx elm init
    ```

3.  Install necessary dependencies:

    ```bash
    elm install \
      dillonkearns/elm-graphql \
      elm/json \
      krisajenkins/remotedata && \
    npm install --save-dev @dillonkearns/elm-graphql
    ```

4.  Create code for accessing the GraphQL endpoint
    (Don't forget to replace the placeholder `<db-id>` with your database id)!

    ```bash
    npx elm-graphql --skip-elm-format \
     https://<name>.airsequel.com/dbs/<db-id>/graphql
    ```

    <aside>
    ⚠️ Due to following errors in the package, you might encounter some problems when generating the code:
    - https://github.com/dillonkearns/elm-graphql/issues/634
    - https://github.com/dillonkearns/elm-graphql/issues/335
    </aside>

5.  Create the main Elm file which will include the code your app: `touch src/Main.elm`
6.  Copy the file content from [github.com/Airsequel/Examples/blob/main/elm-simple-todo-app/src/A_List.elm](https://github.com/Airsequel/Examples/blob/main/elm-simple-todo-app/src/A_List.elm) into the `src/Main.elm` file.
7.  Assign your database id from the previous steps to the `dbId` variable
8.  Finally run `npx elm reactor` to start the development server and host your website at [localhost:8000/src/Main.elm](http://localhost:8000/src/Main.elm).
    🎉 Our little todo app can now list all our todos including their completion status!
9.  To add also support for writing, updating, and deleting todos you can now replace the `Main.elm` file with the corresponding files from here [github.com/Airsequel/Examples/tree/main/elm-simple-todo-app/src](https://github.com/Airsequel/Examples/tree/main/elm-simple-todo-app/src).
10. Finally, feel free to use the code (MIT licensed) as a template for your own CRUD apps.

**Happy coding!**

---

# How To Build a Dashboard With Airsequel and Lowdefy

In this tutorial we will be building a very simple dashboard with [**Lowdefy**](https://lowdefy.com/). It will load the data via GraphQL from an example SQLite database hosted on Airsequel.

This is what the final dashboard will look like:

![Screenshot](/assets/tutorials/lowdefy-dashboard/2022-12-16t2106_dashboard.png)

Since we're using the example database at [airsequel.com/dbs/avatar_the_last_airbender](https://www.airsequel.com/dbs/avatar_the_last_airbender), there is no initial database setup necessary. But feel free to use your own database since the concepts should be straight forward to apply.


## Creating the Lowdefy App

The final code is also available in our [example apps repsoitory](https://github.com/Airsequel/Examples).

1. Create a new directory with the boilerplate code:

   ```bash
   mkdir lowdefy-todo-app
   cd lowdefy-todo-app
   npx lowdefy@latest init
   ```

2. Start the development server:

   ```bash
   npx lowdefy@latest dev
   ```

You should now see the welcome screen running at [localhost:3000/welcome](http://localhost:3000/welcome):

![Screenshot](/assets/tutorials/lowdefy-dashboard/2022-12-16t2105_welcome_screen.png)

Since Lowdefy does not have a dedicated GraphQL connector yet (https://github.com/lowdefy/lowdefy/issues/298), we simply use the more generic `AxiosHttp` module to make the necessary HTTP POST request.

Therefore add following block right below the `name: …` line:

```yaml
connections:
  - id: graphql_api
    type: AxiosHttp
    properties:
      baseURL: https://<your-company>.airsequel.com
```

In order to render our dashboard, replace now the `pages:` block with following code:

```yaml
pages:
  - id: characters_page
    type: PageHeaderMenu
    properties:
      menu:
        links:
          - pageId: characters_page
            type: MenuLink
            properties:
              title: Characters
              icon: UserOutlined
    requests:
      - id: characters_request
        type: AxiosHttp
        connectionId: graphql_api
        properties:
          url: /dbs/avatar_the_last_airbender/graphql
          method: post
          data:
            query: |
              query GetCharacters {
                characters {
                  name,
                  height_in_cm,
                  wikipedia
                }
              }

    events:
      onEnter:
        - id: fetch_characters
          type: Request
          params: characters_request

        - id: set_characters
          type: SetState
          params:
            character_list:
              _request: characters_request.data.data.characters

    blocks:
      - id: title_main
        type: Title
        properties:
          content: Characters

      - id: block_id_$
        type: EChart
        properties:
          option:
            dataset:
              source:
                _state: character_list
            xAxis:
              type: category
            yAxis:
              type: value
            series:
              - type: bar
                encode:
                  x: name
                  y: height_in_cm

      - id: character_list
        type: List
        blocks:
          - id: character_container_$
            type: Box
            style:
              background: white
              margin: 10
              padding: 10
            blocks:
              - id: character_title_$
                type: Title
                properties:
                  level: 3
                  content:
                    _state: character_list.$.name

              - id: character_anchor_$
                type: Anchor
                properties:
                  newTab: true
                  title:
                    _string.replace:
                      on:
                        _state: character_list.$.wikipedia
                      regex: "https://"
                      newSubstr: ""
                  href:
                    _state: character_list.$.wikipedia
```

And voila, you have a simple, yet effective dashboard! 🥳

You can now check out the [Lowdefy Documentation](https://docs.lowdefy.com/) for more blocks to spruce it up!

---

# How To Build a Todo App With Airsequel and Lowdefy

In this tutorial we will be building a very simple todo app with [Lowdefy](https://lowdefy.com). It will load the todos via GraphQL from a SQLite database hosted on Airsequel.

This is what the final app will look like. Not pretty, but functional!

![App Screenshot](/assets/tutorials/lowdefy-todo/2022-09-02t1736_screenshot.png)

If you find any problems in this tutorial, please feel free to leave a comment!


## Setting up the Airsequel Database

Things to know:

- As the primary key we use the automatically generated `rowid` column.

First, let's create the todo database in Airsequel's UI. If you're on Airsequel Enterprise you can alternatively use following steps:

- Alternative way for Airsequel Enterprise: Upload an existing SQLite database
    <aside>
    💡 The next steps describe how to create the database with the `sqlite3` cli tool. You can of course also use one of the many graphical database management apps like e.g. [DB Browser for SQLite](https://sqlitebrowser.org) or [Beekeeper Studio](https://www.beekeeperstudio.io/).

    </aside>

  1. Create a `schema.sql` file for creating the SQLite database:

     - `schema.sql`

       ```sql
       CREATE TABLE todos (
         title TEXT NOT NULL,
         completed BOOLEAN DEFAULT (FALSE) NOT NULL
       );

       -- Add some example todos
       INSERT INTO todos (title, completed)
       VALUES
         ('Buy milk', 0),
         ('Go hiking', 1);
       ```

  2. Execute SQL query to create the database:

     ```shell
     sqlite3 todos.sqlite < schema.sql
     ```

  3. Go to [airsequel.com](https://www.airsequel.com/) and upload the `todos.sqlite` file.

Or build it up from scratch:

1. Go to [airsequel.com](https://www.airsequel.com/) (or your custom Enterprise instance)
2. Click on “Create New SQLite Database”
3. Rename database to “todos”
4. Go to the “Tables” tab
5. Rename “table_1” to “todos”
6. Rename column “name” to “title”
7. Add a new column “completed” with type “Boolean” and activate “Not Null”
8. Create a few todos like “Buy milk” and “Go hiking” to make working with it easier

🎉 Your database and the corresponding GraphQL endpoint are now ready!


## Creating the Lowdefy App

1. Create the directory and the boilerplate code:

   ```shell
   mkdir lowdefy-todo-app
   cd lowdefy-todo-app
   npx lowdefy@latest init
   ```

2. Start development server:

   ```
   npx lowdefy@latest dev
   ```

You should now see the welcome screen running at [localhost:3000/welcome](http://localhost:3000/welcome):

![Welcome Screen](/assets/tutorials/lowdefy-dashboard/2022-12-16t2105_welcome_screen.png)

Since Lowdefy does not have a dedicated GraphQL connector yet (https://github.com/lowdefy/lowdefy/issues/298),
we simply use the more generic `AxiosHttp` module to make the necessary HTTP POST request.

Therefore add following block right below the `name: …` line:

```yaml
connections:
  - id: graphql_api
    type: AxiosHttp
    properties:
      baseURL: https://<your-company>.airsequel.com
```

In order to render our todos, replace now the `pages:` block with following code and replace `<database-id>`with your database id from database setup step.

```yaml
pages:
  - id: todos
    type: PageHeaderMenu
    requests:
      - id: todos_request
        type: AxiosHttp
        connectionId: graphql_api
        properties:
          url: /dbs/<database-id>/graphql
          method: post
          data:
            query: "{ todos { title, completed } }"

    events:
      onEnter:
        - id: fetch_todos
          type: Request
          params: todos_request

        - id: set_todos
          type: SetState
          params:
            todo_list:
              _request: todos_request.data.data.todos

    blocks:
      - id: title_main
        type: Title
        properties:
          content: Todos

      - id: todo_list
        type: List
        blocks:
          - id: todo_container_$
            type: Box
            style:
              background: LightGray
              margin: 0.5rem
              padding: 1rem 2rem
            blocks:
              - id: todo_list.$.completed
                type: CheckboxSwitch
                layout:
                  size: 2em
                  align: middle
                properties:
                  label:
                    disabled: true

              - id: todo_title_$
                type: Span
                layout:
                  grow: 1
                  align: middle
                properties:
                  content:
                    _state: todo_list.$.title
```

Now, the complete file looks like this:

<!-- TODO: Fix this link to really open the YAML file -->
[lowdefy.yaml](/assets/tutorials/lowdefy-todo/lowdefy.yaml)

> 🚧 The next steps in this tutorial will be added soon!
> In particular: Writing, updating, and deleting todos

---