sqlite

.schema and then optionally the table name will show the command to create a table with the same schema. some people seem to say it will be incorrect if the schema has been modified after the original table creation, but this looks to not be true

.tables lists tables

sqlite's default |-delimited output is mildly unreadable

.headers on will display headers

.mode column will display output in nice columns, you can also use box to output in fancy unicode boxes

to enfore a dependency on another table, foreign keys can be used.

CREATE TABLE example
( ...
, FOREIGN KEY(columnname) REFERENCES othertable(othercolumn)
);

unlike other sql engines, sqlite is cool and allows bare columns when aggregating stuff, which lets you get other columns from the same row that was chosen by an aggreate function. it does weird stuff if you have multiple aggreate functions though

.mode json

will make sqlite output in json format

INSERT INTO table (beep, boop) SELECT json_extract(value, '$.foo'),
json_extract(value, '$.bar') FROM json_each(readfile('/dev/stdin'));

will take a list of json objects from stdin and put the values from the 'foo' and 'bar' keys into the 'beep' and 'boop' columns.

<vulpine> sad, with sqlite 3.46.1 readfile('/dev/stdin') seems to no longer work, giving an Error: stepping, out of memory (7) error. guess i'll have to use a normal file...

sqlite can have special columns with calculated values, called generated columns. we can use them to derive column values from json.

CREATE TABLE noises
( raw TEXT
, species AS (json_extract(raw, '$.species')) STORED
, noise AS (json_extract(raw, '$.noise')) STORED
);
INSERT INTO noises (raw) SELECT value FROM
json_each('[{"species":"wah","noise":"wah"},{"species":"fox","noise":"aaa"}]');
SELECT species,noise FROM noises;
+---------+-------+
| species | noise |
+---------+-------+
| wah     | wah   |
| fox     | aaa   |
+---------+-------+

STORED means the data will be stored in the table rather than calculated on the fly every time it is viewed, though sadly it still cannot be a PRIMARY KEY.

to automatically create a simple table with generated columns for the top level of a json object (it will not recurse), we can use jq and awk:

jq 'keys_unsorted[]' -r |
awk 'BEGIN {print "CREATE TABLE hmm";print "( raw TEXT"}
{print ",",$1,"TEXT AS (json_extract(raw, '"'"'$." $1 "'"'"'))"}
END {print ");"}'