json-sql

Ever wanted to dump a sql DB as json? Me too. In this post, we’ll go over a powerful tool, json-sql to help you work with sql data directly as json.

Why json over raw sql?

json is faster and easier to work with than raw sql data:

• Raw json can be fed directly into other programs, raw sql cannot

• Your favoriate language is faster to write than sql. You know your favorite language better than sql and you know how to debug it much faster.

• json can be edited with any text editor, unlike raw sql which is binary and therefore requires special editors to manipulate.

• json solves a much wider range of problems than sql, so the more json experience we get, the faster we get at json and therefore we get faster at a wider range of problems than the sql experience.

Why json-sql?

Dumping a database as json to stdout is surprisingly hard to do without a special tool. Dumping a single table is doable, but involves some magic, plus clever uses of sed and jq to clean the raw output up. All summed up, we could use this one-liner:

psql -d postgres -U postgres -h localhost -c '\t on' -c '\pset format unaligned' -c 'select json_agg(table) from table;' | sed 1d | jq -s

Getting this to work for multiple tables is even nastier as it involves using a query to generate the real query. I’m sure someone who knows psql really well knows how to do it, I don’t. All of this screams, build a tool!

Dumping a sql DB to json

Install the json-toolkit

json-sql is part of the json-toolkit which is easy to install:

git clone https://github.com/tyleradams/json-toolkit
cd json-toolkit
make
sudo make install

Using json-sql

json-sql supports:

• psql

• mysql

• sqlite3

Here, we’ll just go over how to use it for psql and the --help message explains how to use it for other DBs.

$ json-sql read psql user password localhost 5432 db | jq
{
  "table1": [
    { 
      "field1": "a",
      "field2": 0,
    }
  ],
  "table2": [],
}

That’s it. json-sql automatically grabs all of the tables and fields and turns each row into a self-described json object. Now that the data is in json, we can pipe its output to a tool like jq to perform queries and transformations. For example, to query table1, use the '.table1' filter:

$ json-sql read psql user password localhost 5432 db \
> | jq .table1
[
  { 
    "field1": "a",
    "field2": 0,
  }
]

While such a command could be easily performed using sql, select * from table1, complicated transforms are much easier to write in your favorite language than sql.

Conclusion

In this post, we saw:

• its faster to code against json data than sql data

• json-sql quickly dumps a sql database as json, enabling us to code against the data as json rather than sql

• how to query the json data using jq

Next week, we’ll show how to use json-sql to write data to a database. If you don’t want to miss out, just hit Subscribe now below.