In sqlite, all rows have a 64-bit ROWID (with some exceptions). Putting those aside, if you have a single-column integer primary key and use the magic words PRIMARY KEY INTEGER then the pkey becomes an alias for rowid, greatly speeding up lookups. I’m not sure how this affects insertions, though.
https://sqlite.org/lang_createtable.html#rowids_and_the_integer_primary_key
That you can extract sensitive output values from terraform, you can terraform output $output_name
– see https://learn.hashicorp.com/tutorials/terraform/outputs#redact-sensitive-outputs
Also, sqlite provides a PRAGMA user_version=<number> to let applications store
and retrieve a versioning number.
See https://sqlite.org/pragma.html#pragma_user_version
About creating json values using sqlite3’s built-in json value support.
Example:
SELECT json_object('foo', json_object('bar', 'baz'));
-- {"foo":{"bar":"baz"}}
SELECT json_object('foo', json_array('bar', 'baz'));
-- {"foo":["bar","baz"]}
-- works with nested queries too
SELECT json_object('foo', (SELECT json_array('bar', 'baz')));
-- {"foo":["bar","baz"]}
CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c);
INSERT INTO t1 VALUES (1, 'A', 'one' ),
(2, 'B', 'two' ),
(3, 'C', 'three'),
(4, 'D', 'one' ),
(5, 'A', 'two' ),
(6, 'B', 'three'),
(7, 'C', 'one' );
SELECT json_group_array(b)
FROM t1
GROUP BY c;
-- ["A","D","C"]
-- ["C","B"]
-- ["B","A"]
Caveat: you sometimes have to wrap nested values from nested queries in json(), else they’ll be escaped as json string literals. See https://stackoverflow.com/questions/55421128/how-do-i-get-nested-json-data-out-of-sqlite-with-a-multi-level-group-by.
sqlite3 has a .import [--csv] [--skip 1] FILE [TABLE] meta-command (docs).
Also, for some reason my version of sqlite3 doesn’t respect any flags.
Also, sqlite3 has a .dump meta-command that out puts pure SQL (plus a few sqlite PRAGMAs): docs