sq add

Use sq add to add a data source. The source can be a SQL database, or a document such as a CSV or Excel file. This action will add an entry to sq’s config file.

If you later want to change the source, generally the easiest solution is to sq rm @handle and then sq add again. However, you can also directly edit the config file (e.g. vi ~/.config/sq/sq.yml).

A data source has three main elements:

  • driver type: such as postgres, or csv. You may also see this referred to as the source type or simply type.
  • handle: such as @sakila_pg. A handle always starts with @. The handle is used to refer to the data source.
  • location: such as postgres://user:p_ssW0rd@@localhost/sakila. For a document source, location may just be a file path, e.g. /Users/neilotoole/sakila.csv.

The format of the command is:

sq add [--handle HANDLE] [--driver DRIVER] [--active] [--opts OPTS] LOCATION

For example, to add a postgres data source:

$ sq add postgres://sakila:p_ssW0rd@localhost/sakila
@sakila_pg  postgres  sakila@localhost/sakila

Note that flags can generally be omitted. If --handle is omitted, sq will generate a handle. In the example above, the generated handle is @sakila_pg. Usually --driver can also be omitted, and sq will determine the driver type. The --active flag immediately sets the newly-added source as the active source (this also happens regardless if there is not currently an active source).

To add a document source, you can generally just add the file path:

sq add ~/customers.csv

Password visibility

In the Postgres example above, the location string includes the database password. This is a security hazard, as the password value is visible on the command line, and in shell history etc. You can use the --password / -p flag to be prompted for the password.

$ sq add 'postgres://user@localhost/sakila' -p
Password: ****

You can also read the password from a file or a shell variable. For example:

# Add a source, but read password from an environment variable
$ export PASSWORD='open:;"_Ses@me'
$ sq add 'postgres://user@localhost/sakila' -p <<< $PASSWORD

# Same as above, but instead read password from file
$ echo 'open:;"_Ses@me' > password.txt
$ sq add 'postgres://user@localhost/sakila' -p < password.txt

Reference 

Add data source specified by LOCATION, optionally identified by @HANDLE.

Usage:
  sq add [--handle @HANDLE] LOCATION [flags]

Examples:

When adding a data source, LOCATION is the only required arg.

  # Add a postgres source with handle "@sakila_pg"
  $ sq add -h @sakila_pg 'postgres://user:pass@localhost/sakila'

The format of LOCATION varies, but is generally a DB connection string, a
file path, or a URL.

  DRIVER://USER:PASS@HOST:PORT/DBNAME
  /path/to/local/file.ext
  https://sq.io/data/test1.xlsx

If flag --handle is omitted, sq will generate a handle based
on LOCATION and the source driver type.

It's a security hazard to expose the data source password via
the LOCATION string. If flag --password (-p) is set, sq prompt the
user for the password:

  $ sq add 'postgres://user@localhost/sakila' -p
  Password: ****

However, if there's input on stdin, sq will read the password from
there instead of prompting the user:

  # Add a source, but read password from an environment variable
  $ export PASSWD='open:;"_Ses@me'
  $ sq add 'postgres://user@localhost/sakila' -p <<< $PASSWD

  # Same as above, but instead read password from file
  $ echo 'open:;"_Ses@me' > password.txt
  $ sq add 'postgres://user@localhost/sakila' -p < password.txt

Flag --opts sets source-specific options. Generally, opts are relevant
to document source types (such as a CSV file). The most common
use is to specify that the document has a header row:

  $ sq add actor.csv --opts=header=true

Use query string encoding for multiple options, e.g. "--opts a=b&x=y".

If flag --driver is omitted, sq will attempt to determine the
type from LOCATION via file suffix, content type, etc.. If the result
is ambiguous, explicitly specify the driver type.
  
  $ sq add --driver=tsv ./mystery.data

Available source driver types can be listed via "sq drivers". At a
minimum, the following drivers are bundled:

  sqlite3    SQLite                               
  postgres   PostgreSQL                           
  sqlserver  Microsoft SQL Server / Azure SQL Edge                 
  mysql      MySQL                                
  csv        Comma-Separated Values               
  tsv        Tab-Separated Values                 
  json       JSON                                 
  jsona      JSON Array: LF-delimited JSON arrays 
  jsonl      JSON Lines: LF-delimited JSON objects
  xlsx       Microsoft Excel XLSX 

More examples:

  # Add a source, but prompt user for password
  $ sq add 'postgres://user@localhost/sakila' -p
  Password: ****

  # Explicitly set flags
  $ sq add --handle=@sakila_pg --driver=postgres 'postgres://user:pass@localhost/sakila'

  # Same as above, but with short flags
  $ sq add -h @sakila_pg --d postgres 'postgres://user:pass@localhost/sakila'

  # Add a SQL Server source; will have generated handle @sakila_mssql or similar
  $ sq add 'sqlserver://user:pass@localhost?database=sakila' 
  
  # Add a sqlite db
  $ sq add ./testdata/sqlite1.db

  # Add an Excel spreadsheet, with options
  $ sq add ./testdata/test1.xlsx --opts=header=true
  
  # Add a CSV source, with options
  $ sq add ./testdata/person.csv --opts=header=true

  # Add a CSV source from a URL (will be downloaded)
  $ sq add https://sq.io/testdata/actor.csv

Flags:
  -d, --driver string   Explicitly specify the data source driver to use
      --opts string     Driver-dependent data source options
  -h, --handle string   Handle for the source
  -p, --password        Read password from stdin or prompt
      --skip-verify     Don't ping source before adding it
  -j, --json            Output JSON
      --help            help for add

Global Flags:
  -M, --monochrome   Don't colorize output
  -v, --verbose      Print verbose output, if applicable
Edit this page on GitHub
← sq
sq completion →