jsonsql
Interpolates JSON data into SQL strings from the command line. For generating
SQL statements to pass to DB client programs like psql, mysql, and
sqlite3 via Unix pipelines or shell scripts. A faster, lighter-weight
alternative to writing ad-hoc, monolithic programs with database and ORM
libraries.
A template file with this interpolation syntax:
INSERT into titles (title, year, rating, created)
VALUES (:title, :year, :ratings.imdb, DEFAULT);
combined with this JSON stream on STDIN
{
"title": "Terminator 2: 'Judgment Day'",
"year": 1991,
"stars": [
{"name": "Arnold Schwarzenegger"},
{"name": "Linda Hamilton"}
],
"ratings": {
"imdb": 8.5
},
"created": "2014-12-04T10:10:10Z"
}
{
"title": "Interstellar",
"year": 2014,
"stars": [
{"name":"Matthew McConaughey"},
{"name":"Anne Hathaway"}
],
"ratings": {
"imdb": 8.9
}
}
generates this output:
INSERT into titles (title, year, rating, created)
VALUES ('Terminator 2: ''Judgment Day''', 1991, 8.5, DEFAULT);
INSERT into titles (title, year, rating, created)
VALUES ('Interstellar', 2014, 8.9, DEFAULT);
Install
cabal install jsonsql
Usage
jsonsql
Usage: jsonsql (TEMPLATE | -f FILE)
Inject JSON into SQL template strings
Available options:
-h,--help Show this help text
-f FILE Template file
Array joining
If a key path evaluates to an array of values, specify a <delimiter> to have
the the values converted into strings, joined by the delimiter, and then
output as a string.
INSERT into titles (title, year, rating, stars<,>, created)
VALUES (:title, :year, :ratings.imdb, :stars.name, DEFAULT);
INSERT into titles (title, year, rating, stars, created)
VALUES ('Terminator 2: ''Judgment Day''', 1991, 8.5, 'Arnold Schwarzenegger,Linda Hamilton', DEFAULT);
INSERT into titles (title, year, rating, stars, created)
VALUES ('Interstellar', 2014, 8.9, 'Matthew McConaughey,Anne Hathaway', DEFAULT);
JSON literal interpolation
If a key path terminates in a JSON object or array (without a array delimiter
specified), a JSON literal will be interpolated:
template:
INSERT into titles (title, stars)
VALUES (:title, :stars);
output:
INSERT into titles (title, stars)
VALUES ('Terminator 2: ''Judgment Day''', '[{"name":"Arnold Schwarzenegger"},{"name":"Linda Hamilton"}]');
INSERT into titles (title, stars)
VALUES ('Interstellar', '[{"name":"Matthew McConaughey"},{"name":"Anne Hathaway"}]');
To interpolate the base object as a literal JSON string, use :. as the keypath.
Do NOT put quotes around the placeholder for a literal JSON interpolation.
Author
