SQL

Airsequel’s SQL workbench lets you execute arbitrary SQL queries on your SQLite database.
Image without caption
Check out the pricing page 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: github.com/Airsequel/SQLite-Extra-Types

SQL API Usage

The SQL API is only available in the Enterprise Edition.
shell
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.000, "user": 0.000214, "sys": 0.000090 } }

Useful SQL Snippets

Basics

Rename a column
Count lines of text in a column
Check if all values in a group are the same
Get ISO 8601 timestamp with millisecond precision
Create table with an automatically set timestamp column

JSON

Extract value from JSON object
Merge a column of JSON objects

Generation

Generate a table with numbers from 1 to 10
Generate a table random_data with x rows of random entries

Advanced

Split a multiline cell into one line per row
Get the last NOT NULL value in a group
Automatically set a random id after inserting a row