sqlite-utils has a fun feature that allows you to easily create sql schema from json or csv.
First we should make sure that we have sqlite3 and sqlite-utils installed.
brew install sqlite3 sqlite-utils
Lets say we have an external service that we want to store the data that comes back from an api request.
{
"id": "E3A2E012-2BCB-4236-8D4B-98E1D0C7DE73",
"first_name": "Shepard",
"last_name": "Balshen",
"email": "sbalshen0@feedburner.com",
"update_at": 1648051756
}
Now we can use sqlite-utils to generate a new table to store the data from this request
cat response.json | sqlite-utils insert api-responses.db users -
Then we can easily check the schema to see how the table was created
sqlite-utils schema api-responses.db
CREATE TABLE [users] (
[id] TEXT,
[first_name] TEXT,
[last_name] TEXT,
[email] TEXT,
[update_at] INTEGER
);
You might want to modify the schema to add, remove, or change columns or types but this should give a good starting point.
If you want to get even fancier you can pipe the api response directly from curl into sqlite-utils to make this even easier. For example if we had a slightly more complicated json response.
{
"id": "E3A2E012-2BCB-4236-8D4B-98E1D0C7DE73",
"first_name": "Shepard",
"last_name": "Balshen",
"email": "sbalshen0@feedburner.com",
"activities": [
{
"id": "4988998C-24C2-4874-932C-80B9B24CCD7C",
"type": "bike",
"time": 1648051756,
"distance": 20
},
{
"id": "5013FDC9-B6CF-41AF-9E0C-84A9D3F8092D",
"type": "run",
"time": 1648051756,
"distance": 8
}
],
"update_at": 1648051756
}
We can then pipe the response from the api request directly into jq and then into sqlite-utils to easily create a schema that can be used to store the data
curl -X GET -s localhost:8000 | \
jq '.activities[0]' | \
sqlite-utils insert activities.db activities -
sqlite-utils schema activities.db
CREATE TABLE [activities] (
[id] TEXT,
[type] TEXT,
[time] INTEGER,
[distance] INTEGER
);