sq

sq is a free/libre open-source data wrangling swiss-army knife to inspect, query, join, import, and export data. You could think of sq as jq for databases and documents, facilitating one-liners like:

sq '@postgres_db | .actor | .first_name, .last_name | .[0:5]'

Installation #

brew install sq

/bin/sh -c "$(curl -fsSL https://sq.io/install.sh)"

scoop bucket add sq https://github.com/neilotoole/sq
scoop install sq

For help, sq help is your starting point. And then see the docs.

Let’s get this out of the way #

sq is pronounced like seek. Its query language, SLQ, is pronounced like sleek.

Feature Highlights #

Some feature highlights are shown below. For more, see the docs, including the query guide, tutorial and cookbook.

Diff database tables #

Use the diff command to compare source metadata or row values.

Import Excel worksheet into Postgres table #

Insert the contents of an Excel XLSX worksheet (from a sheet named actor) into a new Postgres table named xl_actor. Note that the import mechanism is reasonably sophisticated in that it tries to preserve data types.

View metadata for a database #

The --json flag to sq inspect outputs schema and other metadata in JSON. Typically the output is piped to jq to select the interesting elements.

Get names of all columns in a MySQL table #

Even easier, just get the metadata for the table you want:

sq inspect @sakila_my.actor -j | jq -r '.columns[] | .name'

Execute SQL query against SQL Server, insert results to SQLite #

This snippet adds a (pre-existing) SQL Server source, and creates a new SQLite source. Then, a raw native SQL query is executed against SQL Server, and the results are inserted into SQLite.

Export all database tables to CSV #

Get the (JSON) metadata for the active source; pipe that JSON to jq and extract the table names; pipe the table names to xargs, invoking sq once for each table, outputting a CSV file per table. This snippet was tested on macOS.

If you instead wanted to use sql mode:

sq inspect -j | jq -r '.tables[] | .name' | xargs -I % sq sql 'SELECT * FROM %' --csv --output %.csv

Source commands #

Commands to add, activate, move, list, group, ping or remove sources.

$ sq src                      # show active source
$ sq add ./actor.tsv          # add a source
$ sq src @actor_tsv           # set active source
$ sq ls                       # list sources
$ sq ls -v                    # list sources (verbose)
$ sq group                    # get active group
$ sq group prod               # set active group
$ sq mv @sales @prod/sales    # rename a source
$ sq ping --all               # ping all sources
$ sq rm @actor_tsv            # remove a source

Database table commands #

Convenient commands that act on database tables: copy, truncate, drop.

Note that sq tbl copy only applies within a single database. If you want to copy a table from one database to another, use the --insert mechanism.

$ sq tbl copy .actor .actor2  # copy table "actor" to "actor2", creating if necessary
$ sq tbl truncate .actor2     # truncate table "actor2"
$ sq tbl drop .actor2         # drop table "actor2"

Query JSONL (e.g. log files) #

JSONL output is a row of JSON per line (hence “JSON Lines”). Lots of log output is like this. We can use sq’s own log output as an example:

{"level":"debug","time":"00:07:48.799992","caller":"sqlserver/sqlserver.go:452:(*database).Close","msg":"Close database: @sakila_mssql | sqlserver | sqlserver://sakila:xxxxx@localhost?database=sakila"}
{"level":"debug","time":"00:07:48.800016","caller":"source/files.go:323:(*Files).Close","msg":"Files.Close invoked: has 1 clean funcs"}
{"level":"debug","time":"00:07:48.800031","caller":"source/files.go:61:NewFiles.func1","msg":"About to clean fscache from dir: /var/folders/68/qthwmfm93zl4mqdw_7wvsv7w0000gn/T/sq_files_fscache_2273841732"}