Config

sq aims to work out of the box with sane defaults, but allows you to configure most everything. sq’s total configuration state consists of a collection of data sources and groups, and a plethora of configuration options. That’s what this section is about. There are two levels of options:

Base config, consisting of many options. Each option is a key-value pair, e.g. format=json, or conn.max-open=50
Source-specific config. Each source can have its own value for, say, conn.max-open. If an option is not explicitly set on a source, the source inherits that option value from base config.

Commands

sq provides commands to locate, list, get, set, and edit config. The config commands provide extensive shell-completion, so feel free to hit TAB while entering a command, and sq will guide you.

`location`

sq stores its main config in a sq.yml file in its config dir. You don’t usually need to edit the config directly: sq provides several mechanisms for managing config.

The location of sq's config dir is OS-dependent. On macOS, it’s here:

$ sq config location
/Users/neilotoole/.config/sq

You can specify an alternate location by setting envar SQ_CONFIG:

$ export SQ_CONFIG=/tmp/sq
$ sq config location
/tmp/sq

You can also specify the config dir via the --config flag:

$ sq --config=/tmp/sq2 config location
/tmp/sq2

`ls`

Use sq config ls to list the options that have been set.

# List config options
$ sq config ls

Well, there’s more. A lot more. Use sq config ls -v to also see unset options, along with their default values.

Note in the image above that some options don’t have a value. That is to say, the option is unset. When unset, an option takes on its default value.

👉

If you want a wall of text, try sq config ls -yv (the -y flag is for --yaml output). That’s the maximum amount of detail available.

As well as listing base config, you can view config options for a source.

$ sq config ls --src @actor_csv -v

`get`

sq config get is like the single-friend counterpart of sq ls. It gets the value of a single option.

# Get base value of "format" option
$ sq config get format
text

# Get the "conn.max-open" option value for a particular source
$ sq config get --src @actor_csv conn.max-open
10

`set`

Use sq config set or sq config set --src to set an option value.

# Set base option value
$ sq config set format json

# Set source-specific option value
$ sq config set --src @sakila_pg conn.max-open 20

To get help for a specific option, execute sq config set OPTION --help.

`edit`

In the spirit of kubectl edit, you can edit base config or source-specific config via the default editor, as defined in envar $EDITOR or $SQ_EDITOR.

# Edit base config
$ sq config edit

# Edit config for source
$ sq config edit @sakila

# Use a different editor
$ SQ_EDITOR=nano sq config edit

If you add the -v flag (sq config edit -v), the editor will show additional help for the options.

Logging

By default, logging is turned off. If you need to submit a sq bug report, you’ll likely want to include the sq log file.

# Enable logging
$ sq config set log true

# Default log level is DEBUG... you can change it if you want.
# But leave it on DEBUG if you're sending bug reports.
$ sq config set log.level WARN

# You can also change the log file location. The default location
# is OS-dependent.
$ sq config get log.file -v
KEY       VALUE  DEFAULT
log.file         /Users/neilotoole/Library/Logs/sq/sq.log

🤬️

If there’s a problem with sq’s bootstrap mechanism (e.g. corrupt config file), and logs aren’t being generated, it’s preferable to use envars to force logging.

export SQ_LOG=true; export SQ_LOG_LEVEL=DEBUG; export SQ_LOG_FILE=./sq.log

Options

Below, all available options are listed. Use sq config set OPTION to modify the option value.

Some config options apply only to base config. For example, format=json applies to the sq CLI itself, and not to a particular source such as @sakila. However, some options can apply to a source, and also have a base value. For example, conn.max-open controls the maximum number of connections that sq will open to a database.

CLI

`log`

Usage:
  sq config set log false

Enable logging.

`log.file`

Usage:
  sq config set log.file /Users/neilotoole/Library/Logs/sq/sq.log

Path to log file. Empty value disables logging.

`log.level`

Usage:
  sq config set log.level DEBUG

Log level, one of: DEBUG, INFO, WARN, ERROR.

`ping.timeout`

Usage:
  sq config set ping.timeout 10s

How long the ping command waits before timeout occurs. Example: 500ms or 2m10s.

`shell-completion.timeout`

Usage:
  sq config set shell-completion.timeout 500ms

How long shell completion should wait before giving up. This can
become relevant when shell completion inspects a source's metadata, e.g. to
offer a list of tables in a source.

Output

`compact`

Usage:
  sq config set compact false

Compact instead of pretty-printed output.

`format`

Usage:
  sq config set format text

Specify the output format. Some formats are only implemented for a subset of
sq's commands. If the specified format is not available for a particular
command, sq falls back to "text". Available formats:

  text, csv, tsv, xlsx,
  json, jsona, jsonl,
  markdown, html, xlsx, xml, yaml, raw

`format.datetime`

Usage:
  sq config set format.datetime RFC3339

Timestamp format. This can be one of several predefined constants such
as "RFC3339" or "Unix", or a strftime format such as "%Y-%m-%d %H:%M:%S".

Predefined values:
  ANSIC, DateOnly, DateTime, ISO8601, ISO8601Z, RFC1123, RFC1123Z,
  RFC3339, RFC3339Nano, RFC3339Z, RFC822, RFC822Z, RFC850,
  TimeOnly, Unix, UnixDate, UnixMicro, UnixMilli, UnixNano

`error.format`

Usage:
  sq config set error.format text

The format to output errors in. Allowed formats are "text" or "json".

`format.datetime.number`

Usage:
  sq config set format.datetime.number true

Render numeric datetime value as number instead of string, if possible.
If format.datetime renders a numeric value (e.g. a Unix timestamp such
as "1591843854"), that value is typically rendered as a string. For some output
formats, such as JSON, it can be useful to instead render the value as a naked
number instead of a string. Note that this option is no-op if the rendered value
is not an integer.

  format.datetime.number=false
  [{"first_name":"PENELOPE","last_update":"1591843854"}]
  format.datetime.number=true
  [{"first_name":"PENELOPE","last_update":1591843854}]

`format.date`

Usage:
  sq config set format.date DateOnly

Date format. This can be one of several predefined constants such
as "DateOnly" or "Unix", or a strftime format such as "%Y-%m-%d".
Note that date values are sometimes programmatically indistinguishable
from datetime values. In that situation, use format.datetime instead.

Predefined values:
  ANSIC, DateOnly, DateTime, ISO8601, ISO8601Z, RFC1123, RFC1123Z,
  RFC3339, RFC3339Nano, RFC3339Z, RFC822, RFC822Z, RFC850,
  TimeOnly, Unix, UnixDate, UnixMicro, UnixMilli, UnixNano

`format.date.number`

Usage:
  sq config set format.date.number true

Render numeric date value as number instead of string, if possible.
If format.date renders a numeric value (e.g. a year such as "1979"), that value
is typically rendered as a string. For some output formats, such as JSON, it can
be useful to instead render the value as a naked number instead of a string.
Note that this option is no-op if the rendered value is not an integer.

  format.date.number=false
  [{"first_name":"PENELOPE","birth_year":"1979"}]
  format.date.number=true
  [{"first_name":"PENELOPE","birth_year":1979}]

`format.time`

Usage:
  sq config set format.time TimeOnly

Time format. This can be one of several predefined constants such
as "TimeOnly" or "Unix", or a strftime format such as "%Y-%m-%d".
Note that time values are sometimes programmatically indistinguishable
from datetime values. In that situation, use format.datetime instead.

Predefined values:
  ANSIC, DateOnly, DateTime, ISO8601, ISO8601Z, RFC1123, RFC1123Z,
  RFC3339, RFC3339Nano, RFC3339Z, RFC822, RFC822Z, RFC850,
  TimeOnly, Unix, UnixDate, UnixMicro, UnixMilli, UnixNano

`format.time.number`

Usage:
  sq config set format.time.number true

Render numeric time value as number instead of string, if possible.
If format.time renders a numeric value (e.g. "59"), that value
is typically rendered as a string. For some output formats, such as JSON, it can
be useful to instead render the value as a naked number instead of a string.
Note that this option is no-op if the rendered value is not an integer.

  format.time.number=false
  [{"first_name":"PENELOPE","favorite_minute":"59"}]
  format.time.number=true
  [{"first_name":"PENELOPE","favorite_minute":59}]

`format.excel.datetime`

Usage:
  sq config set format.excel.datetime 'yyyy-mm-dd hh:mm'

Timestamp format for datetime values: that is, for values that have
both a date and time component. The exact format is specific to
Microsoft Excel, but is broadly similar to strftime.

Examples:

  "yyyy-mm-dd hh:mm"           1989-11-09 16:07
  "dd/mm/yy h:mm am/pm"        09/11/89 4:07 pm
  "dd-mmm-yy h:mm:ss AM/PM"    09-Nov-89 4:07:01 PM

`format.excel.date`

Usage:
  sq config set format.excel.date yyyy-mm-dd

Date format string for Microsoft Excel date-only values. The exact format
is specific to Excel, but is broadly similar to strftime.

Examples:

  "yyyy-mm-dd"	  1989-11-09
  "dd/mm/yy"      09/11/89
  "dd-mmm-yy"     09-Nov-89

`format.excel.time`

Usage:
  sq config set format.excel.time hh:mm:ss

Time format string for Microsoft Excel time-only values. The exact format is
specific to Excel, but is broadly similar to strftime.

Examples:

  "hh:mm:ss"         16:07:10
  "h:mm am/pm"	     4:07 pm
  "h:mm:ss AM/PM"    4:07:01 PM

Note that time-only values are sometimes programmatically indistinguishable
from datetime values. In that situation, use format.excel.datetime instead.

`header`

Usage:
  sq config set header true

Controls whether a header row is printed. This applies only
to certain formats, such as "text" or "csv".

`monochrome`

Usage:
  sq config set monochrome false

Don't print color output.

`verbose`

Usage:
  sq config set verbose false

Print verbose output.

`result.column.rename`

Usage:
  sq config set result.column.rename '{{.Name}}{{with .Recurrence}}_{{.}}{{end}}'

This Go text template is executed on the column names returned
from the DB. Its primary purpose is to rename duplicate column names. For
example, given a query that results in this SQL:

  SELECT * FROM actor JOIN film_actor ON actor.actor_id = film_actor.actor_id

The returned result set will have these column names:

  actor_id, first_name, last_name, last_update, actor_id, film_id, last_update
  |-              from "actor"               -| |-    from "film_actor"     -|

Note the duplicate "actor_id" and "last_update" column names. When output in a
format (such as JSON) that doesn't permit duplicate keys, only one of each
duplicate column could appear.

The fields available in the template are:

  .Name         column name
  .Index        zero-based index of the column in the result set
  .Alpha        alphabetical index of the column, i.e. e.g. [A, B ... Z, AA, AB]
  .Recurrence   nth recurrence of the colum name in the result set

For a unique column name, e.g. "first_name" above, ".Recurrence" will be 0.
For duplicate column names, ".Recurrence" will be 0 for the first instance,
then 1 for the next instance, and so on.

The default template renames the columns to:

  actor_id, first_name, last_name, last_update, actor_id_1, film_id, last_update_1

The result.column.rename option is rather arcane: it allows you to change the way sq de-duplicates column names. By default, a result set containing duplicate column names is renamed like this:

-- Columns returned from DB...
actor_id, first_name, last_name, last_update, actor_id, film_id, last_update

-- are renamed to
actor_id, first_name, last_name, last_update, actor_id_1, film_id, last_update_1

Thus, the second actor_id column becomes actor_id_1. Let’s say you instead wanted the column to be renamed to actor_id:1. Change the template value to use : instead of _.

$ sq config set result.column.rename '{{.Name}}{{with .Recurrence}}:{{.}}{{end}}'

The option value must be a valid Go text template. In addition to the standard Go functions, the sprig functions are available. Here’s an example of a template using the sprig upper function to rename each column to uppercase.

{{.Name | upper}}{{with .Recurrence}}:{{.}}{{end}}

The .AlphaIndex template fields maps the column index to A, B ... Y, Z, AA, AB..., similar to how Microsoft Excel names columns. To use this style:

$ sq config set result.column.rename '{{.AlphaIndex}}'
$ sq .actor

👉

Note that ingest.column.rename and result.column.rename are distinct options. The ingest option is applied to ingest data (e.g. a CSV file) column names before the data is sent to the database (pre-processing). The result option, by contrast, is applied to result set column names after the data is returned from the database (post-processing). It is possible (and normal) to use both options.

`diff.data.format`

Usage:
  sq config set diff.data.format text

Specify the output format to use when comparing table data.
Available formats:

  text, csv, tsv,
  json, jsona, jsonl,
  markdown, html, xml, yaml

`diff.lines`

Usage:
  sq config set diff.lines 3

Generate diffs with <n> lines of context, where n >= 0.

Tuning

`conn.max-idle`

Usage:
  sq config set conn.max-idle 2

Set the maximum number of connections in the idle connection pool.
If conn.max-open is greater than 0 but less than the new conn.max-idle,
then the new conn.max-idle will be reduced to match the conn.max-open limit.
If n <= 0, no idle connections are retained.

`conn.max-idle-time`

Usage:
  sq config set conn.max-idle-time 2s

Sets the maximum amount of time a connection may be idle.
Expired connections may be closed lazily before reuse. If n <= 0,
connections are not closed due to a connection's idle time.

`conn.max-lifetime`

Usage:
  sq config set conn.max-lifetime 10m0s

Set the maximum amount of time a connection may be reused.
Expired connections may be closed lazily before reuse.
If n <= 0, connections are not closed due to a connection's age.

`conn.max-open`

Usage:
  sq config set conn.max-open 0

Maximum number of open connections to the database.
A value of zero indicates no limit.

`conn.open-timeout`

Usage:
  sq config set conn.open-timeout 5s

Max time to wait before a connection open timeout occurs.

`retry.max-interval`

Usage:
  sq config set retry.max-interval 3s

The maximum interval to wait between retries.
If an operation is retryable (for example, if the DB has too many clients),
repeated retry operations back off, typically using a Fibonacci backoff.

`tuning.errgroup-limit`

Usage:
  sq config set tuning.errgroup-limit 16

Controls the maximum number of goroutines that can be spawned
by an errgroup. Note that this is the limit for any one errgroup, but not a
ceiling on the total number of goroutines spawned, as some errgroups may
themselves start an errgroup.

This knob is primarily for internal use. Ultimately it should go away
in favor of dynamic errgroup limit setting based on availability
of additional DB conns, etc.

`tuning.flush-threshold`

Usage:
  sq config set tuning.flush-threshold 1000

Size in bytes after which output writers should flush any internal buffer.
Generally, it is not necessary to fiddle this knob.

`tuning.record-buffer`

Usage:
  sq config set tuning.record-buffer 1024

Controls the size of the buffer channel for record insertion/writing.

Ingest

`ingest.column.rename`

Usage:
  sq config set ingest.column.rename '{{.Name}}{{with .Recurrence}}_{{.}}{{end}}'

This Go text template is executed on ingested column names.
Its primary purpose is to rename duplicate header column names in the
ingested data. For example, given a CSV file with header row:

  actor_id, first_name, actor_id

The default template renames the columns to:

  actor_id, first_name, actor_id_1

The fields available in the template are:

  .Name         column header name
  .Index        zero-based index of the column in the header row
  .Alpha        alphabetical index of the column, e.g. [A, B ... Z, AA, AB]
  .Recurrence   nth recurrence of the colum name in the header row

For a unique column name, e.g. "first_name" above, ".Recurrence" will be 0.
For duplicate column names, ".Recurrence" will be 0 for the first instance,
then 1 for the next instance, and so on.

👉

`ingest.header`

Usage:
  sq config set ingest.header false

Specifies whether ingested data has a header row or not.
If not set, the ingester *may* try to detect if the input has a header.
Generally it is best to leave this option unset and allow the ingester
to detect the header.

`ingest.sample-size`

Usage:
  sq config set ingest.sample-size 256

Specify the number of samples that a detector should take to determine type.

`driver.csv.delim`

Usage:
  sq config set driver.csv.delim comma

Delimiter to use for CSV files. Default is "comma".
Possible values are: comma, space, pipe, tab, colon, semi, period.

`driver.csv.empty-as-null`

Usage:
  sq config set driver.csv.empty-as-null true

When true, empty CSV fields are treated as NULL. When false,
the zero value for that type is used, e.g. empty string or 0.

Edit this page on GitHub

Config

Commands #

location #

ls #

get #

set #

edit #

Logging #

Options #

CLI #

log #

log.file #

log.level #

ping.timeout #

shell-completion.timeout #

Output #

compact #

format #

format.datetime #

error.format #

format.datetime.number #

format.date #

format.date.number #

format.time #

format.time.number #

format.excel.datetime #

format.excel.date #

format.excel.time #

header #

monochrome #

verbose #

result.column.rename #

diff.data.format #

diff.lines #

Tuning #

conn.max-idle #

conn.max-idle-time #

conn.max-lifetime #

conn.max-open #

conn.open-timeout #

retry.max-interval #

tuning.errgroup-limit #

tuning.flush-threshold #

tuning.record-buffer #

Ingest #

ingest.column.rename #

ingest.header #

ingest.sample-size #

driver.csv.delim #

driver.csv.empty-as-null #