sq: swiss-army knife for data

sq is a command line tool that provides jq-style access to structured data sources such as SQL databases, or document formats like CSV or Excel.

sq can perform cross-source joins, execute database-native SQL, and output to a multitude of formats including JSON, Excel, CSV, HTML, Markdown and XML, or insert directly to a SQL database. sq can also inspect sources to view metadata about the source structure (tables, columns, size) and has commands for common database operations such as copying or dropping tables.

Install

For other installation options, see here.

It is strongly advised to install shell completion.

macOS

brew install neilotoole/sq/sq

Windows

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

Linux

apt

curl -fsSLO https://github.com/neilotoole/sq/releases/latest/download/sq-linux-amd64.deb && sudo apt install -y ./sq-linux-amd64.deb && rm ./sq-linux-amd64.deb

rpm

sudo rpm -i https://github.com/neilotoole/sq/releases/latest/download/sq-linux-amd64.rpm

yum

yum localinstall -y https://github.com/neilotoole/sq/releases/latest/download/sq-linux-amd64.rpm

Shell completion

Shell completion is available for bash, zsh, fish, and powershell. It is strongly recommended to install.

Execute sq completion --help for installation instructions.

Quickstart

Use sq help to see command help. The tutorial is the best place to start. The cookbook has recipes for common actions.

The major concept is: sq operates on data sources, which are treated as SQL databases (even if the source is really a CSV or XLSX file etc).

In a nutshell, you sq add a source (giving it a handle), and then execute commands against the source.

Sources

Initially there are no sources.

$ sq ls

Let’s add a source. First we’ll add a SQLite database, but this could also be Postgres, SQL Server, Excel, etc. Download the sample DB, and sq add the source. We use -h to specify a handle to use.

$ wget https://sq.io/testdata/sakila.db

$ sq add ./sakila.db -h @sakila_sl3
@sakila_sl3  sqlite3  sakila.db

$ sq ls -v
HANDLE       DRIVER   LOCATION                 OPTIONS
@sakila_sl3* sqlite3  sqlite3:/root/sakila.db

$ sq ping @sakila_sl3
@sakila_sl3  1ms  pong

$ sq src
@sakila_sl3  sqlite3  sakila.db

The sq ping command simply pings the source to verify that it’s available.

sq src lists the active source, which in our case is @sakila_sl3. You can change the active source using sq src @other_src. When there’s an active source specified, you can usually omit the handle from sq commands. Thus you could instead do:

$ sq ping
@sakila_sl3  1ms  pong

Query

Fundamentally, sq is for querying data. Using our jq-style syntax:

<div class="highlight highlight-source-shell position-relative" data-snippet-clipboard-copy-content="$ sq '.actor | .actor_id

$ sq '.actor | .actor_id < 100 | .[0:3]'
actor_id  first_name  last_name     last_update
1         PENELOPE    GUINESS       2020-02-15T06:59:28Z
2         NICK        WAHLBERG      2020-02-15T06:59:28Z
3         ED          CHASE         2020-02-15T06:59:28Z