InterruptibleRMI java.net mailing list archive

sq is a command-line tool that provides uniform access to your datasources, whether they be traditional SQL-style databases, or document formats such as JSON, XML, Excel. Get the latest: sq-0.11.3-darwin.tar.gz

In the simplest narrative, you use sq to query a datasource and output JSON.

> sq '.user | .uid, .username, .email'
[
  {
    "uid": 1,
    "username": "neilotoole",
    "email": "neilotoole@apache.org"
  },
  {
    "uid": 2,
    "username": "ksoze",
    "email": "kaiser@soze.org"
  },
  {
    "uid": 3,
    "username": "kubla",
    "email": "kubla@khan.mn"
  }
]

sq has its own query language (also know as sq), which takes much of its inspiration from the excellent jq utility. However, for SQL-based datasources, you can also revert to DB-native SQL if you prefer. And JSON is not your only output option.

> sq --sql --table --header 'SELECT uid, username, email FROM user'
uid  username    email
1    neilotoole  neilotoole@apache.org
2    ksoze       kaiser@soze.org
3    kubla       kubla@khan.mn

sq aims for terseness; in realistic usage, that command would use shorthand flags:

> sq -lth 'select uid, username, email from user'
uid  username    email
1    neilotoole  neilotoole@apache.org
2    ksoze       kaiser@soze.org
3    kubla       kubla@khan.mn

Use sq --help to see the available flags and commands.

Basic usage

# Add a datasource... yeah, the mysql driver URL could be prettier
> sq add 'mysql://root:root@tcp(localhost:33067)/sq_mydb1' mydb1

# Set the active datasource
> sq use mydb1

# List datasources
> sq ls
NAME      DRIVER    URL
pgdb1     postgres  postgres://pqgotest:password@localhost/pqgotest
mydb1     mysql     mysql://root:root@tcp(localhost:33067)/sq_mydb1

# Execute a query
> sq '.user'                  # get all rows and cols from table "user"
> sq '.user | .uid, .email'   # get cols "uid" and "email" from table "user"

Adding datasources

Note that the format of the database URL/DSN is driver-dependent. These examples should work tho.

sq add 'mysql://root:root@tcp(localhost:33067)/sq_mydb1' mydb1
sq add 'postgres://pqgotest:password@localhost/pqdb' pq1
sq add 'sqlite3:///Users/neilotoole/testdata/sqlite1.db' sl1
sq add /Users/neilotoole/testdata/test.xlsx excel1
sq add https://s3.amazonaws.com/sq.neilotoole.io/testdata/1.0/xslx/test.xlsx excelRemote1

join

> sq '.user, .address | join(.uid) | .username, .city, .zip
> sq '.user, .order | join(.uid == .order_uid) | .email, .order_id

range / row select

> sq '.user | .[0]'      # get first row
> sq '.user | .[3:7]     # get rows 3 thru 7
> sq '.user | .[5:]      # get all rows from 5 onwards

Busted

Do not try these at home.

tbl/col aliasing

> sq '.user | .uid:user_id, .username, .email:mail'  # rename "uid" to "user_id", and "email" to "mail".

where (conditional select)

> sq '.user | .uid > 100 && .username != "ksoze" | .uid, .username, .email'

cross-db operations

cross-db join

> sq '@mysql_db1.user u, @pqdb1.orders o | join(u.uid == o.user_id) | u.username, o.order_id, o.quantity'

copy table

> sq '@mysql_db1.user > @pgdb1.users'                                          # copy entire table
> sq '@mysql_db1.user | .username, .email  > @pgdb1.users | .uname, .email'    # copy certain fields