Skip to content
Neil O'Toole edited this page Feb 27, 2021 · 20 revisions

sq is a swiss army knife for data.

sq provides a jq-style syntax to query, join, migrate, and export data from a variety of data sources, such as Postgres, SQLite, SQL Server, MySQL, Excel or CSV, with the ability to fallback to actual SQL for trickier work. In essence, sq treats every data source as if it were a SQL database. sq also provides several handy commands such as inspect, ping, or tbl copy.

Read the tutorial and cookbook.

Concepts

  • sq is the command-line utility itself.
  • SLQ is the formal name of sq's language, similar to jq syntax.
  • source is a specific data source such as a database instance, or Excel or CSV file etc.. A source has a:
    • type: such as postgres, sqlserver, csv, or xlsx; effectively type means the source driver type.
    • location: the location of the source, such as postgres://sakila:****@localhost/sakila or /Users/neilotoole/sq/xl_demo.xlsx.
    • handle: which is how sq refers to that particular source, e.g. @sakila_pg or @xl_demo. A handle must begin with @.
  • active source is the source upon which sq acts if no other source is specified.
  • scratchdb means the scratch or temporary database that sq uses for under-the-hood activity such as converting a non-SQL source like XLSX to relational format.
  • joindb is similar to scratchdb, but is used for cross-source joins.
  • monotable means that the source type is really only a single table, such as CSV: that single table is named data.

Next, read the tutorial.

Install

sq can be installed from source, or via package managers for various platforms.

It is strongly advised to install shell completion.

From source

From the sq project dir:

$ go install

The simple go install does not populate the binary with build info that is output via the sq version command. To do so, use mage.

$ brew install mage
$ mage install

brew (macOS)

Requires brew.

$ brew install neilotoole/sq/sq

Or to upgrade:

$ brew upgrade sq

scoop (Windows)

Requires scoop.

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

To upgrade:

$ scoop upgrade sq

deb

The deb package is not currently published to a repository, so we'll need to directly reference the packages as published in releases.

Thus, download the .deb file and then install.

$ 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

As per deb, the .rpm is not published to a repository, so directly reference the package as published in releases.

Via yum:

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

Via rpm:

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

tarball

Download the appropriate .tar.gz or .zip file from GitHub releases, and extract the binary from the archive.

Shell completion

Shell completion is available for bash, zsh, fish, and powershell.

Execute sq completion --help for installation instructions.

Version

You can check the sq version like this:

$ sq --version
sq 0.14.5    #4faf1608    2020-12-30T19:38:47Z

Getting Started

After sq is installed, sq help and see the examples. And also see tutorial.

Issues

File any bug reports or other issues here. When filing a bug report, submit a log file. For example, this:

SQ_LOGFILE=./sq.log sq [some command]

will create sq.log in the current dir. That sq.log file should be submitted with the bug report.

Config

By default, sq stores its config in ~/.config/sq/sq.yml.

Logging

By default, sq debug logging is disabled. For one-time logging, this will generate a sq.log file in the current dir:

SQ_LOGFILE=./sq.log sq [some command]

To enable logging generally, add this line to your .bashrc or .zshrc etc..

export SQ_LOGFILE=~/.config/sq/sq.log

For Windows, set the SQ_LOGFILE environment variable per the usual mechanism.