SQL

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

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:

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:

Check out the pricing page for all limitations of the Free edition.

Extra Types for SQLite Interactive Editing SQL API Usage Useful SQL Snippets Basics JSON Generation Advanced

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

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:

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

Create a table

Rename / Delete / Export a table

Change table description

Create a column

Delete / Rename / Hide (collapse) column

Set all entries of column to the default value

Reorder columns

Delete an arbitrary set of rows

Create a row

Edit a cell

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

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

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.

Generation

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)

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

Advanced

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:

SELECT previous row with LAG (sqlite.org/windowfunctions)

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