Running queries#

You can run SQL queries against a Datasette instance like this:

dclient query fixtures "select * from facetable limit 1" -i https://latest.datasette.io

Output:

[
  {
    "pk": 1,
    "created": "2019-01-14 08:00:00",
    "planet_int": 1,
    "on_earth": 1,
    "state": "CA",
    "_city_id": 1,
    "_neighborhood": "Mission",
    "tags": "[\"tag1\", \"tag2\"]",
    "complex_array": "[{\"foo\": \"bar\"}]",
    "distinct_some_null": "one",
    "n": "n1"
  }
]

The query command takes a database name and SQL string as positional arguments. Use -i to specify the instance (alias or URL). If you have a default instance and default database configured, you can use the bare SQL shortcut instead:

dclient "select * from facetable limit 1"

You can override just the database with -d:

dclient "select * from counters" -d counters

Browsing rows#

The dclient rows command lets you browse table data without writing SQL:

dclient rows fixtures facet_cities -i https://latest.datasette.io -t

id  name
--  -------------
3   Detroit
2   Los Angeles
4   Memnonia
1   San Francisco

If you have a default instance and database configured, you can just pass the table name:

dclient rows facet_cities -t

Filtering#

Use -f / --filter with three arguments: column, operation, value:

dclient rows facet_cities -f id gte 3 -t
dclient rows facet_cities -f name eq Detroit
dclient rows facet_cities -f name contains M -f id gt 2

The operation is passed directly to Datasette as a column filter suffix. Built-in Datasette operations include exact, not, gt, gte, lt, lte, contains, like, startswith, endswith, glob, isnull, notnull, and more. eq is a convenience alias for exact. Operations added by Datasette plugins will work too.

Sorting#

dclient rows dogs --sort age
dclient rows dogs --sort-desc age

Column selection#

dclient rows dogs --col name --col age
dclient rows dogs --nocol id

Search#

Full-text search (requires an FTS index on the table):

dclient rows dogs --search "retriever"

Pagination#

By default only one page of results is returned. Use --all to auto-paginate through all rows, and --limit to cap the total:

dclient rows dogs --all
dclient rows dogs --all --limit 500
dclient rows dogs --size 50

dclient rows –help#

Usage: dclient rows [OPTIONS] DB_OR_TABLE [TABLE]

  Browse rows in a table with filtering and sorting

  If only one positional argument is given, it is treated as the table name and
  the default database is used. Pass two arguments for database and table.

  Example usage:

      dclient rows facet_cities
      dclient rows fixtures facet_cities -i https://latest.datasette.io
      dclient rows facet_cities -f id gte 3 --sort name -t

Options:
  -i, --instance TEXT   Datasette instance URL or alias
  -d, --database TEXT   Database name
  --token TEXT          API token
  -f, --filter TEXT...  Filter: column operation value (e.g. -f age gte 3)
  --search TEXT         Full-text search query
  --sort TEXT           Sort by column (ascending)
  --sort-desc TEXT      Sort by column (descending)
  --col TEXT            Include only these columns
  --nocol TEXT          Exclude these columns
  --size INTEGER        Number of rows per page
  --limit INTEGER       Maximum total rows to return
  --all                 Fetch all pages
  -v, --verbose         Verbose output: show HTTP request
  --csv                 Output as CSV
  --tsv                 Output as TSV
  --nl                  Output as newline-delimited JSON
  -t, --table           Output as ASCII table
  --help                Show this message and exit.

Output formats#

By default, results are returned as JSON. Use these flags to change the output format:

--csv — CSV
--tsv — TSV
-t / --table — ASCII table
--nl — newline-delimited JSON (one JSON object per line)

These flags work with dclient query, dclient rows, and the bare SQL shortcut.

CSV output:

dclient query fixtures "select * from facetable limit 2" -i latest --csv

pk,created,planet_int,on_earth,state,_city_id,_neighborhood
1,2019-01-14 08:00:00,1,1,CA,1,Mission
2,2019-01-15 08:00:00,1,1,CA,1,Dogpatch

ASCII table output:

dclient query fixtures "select pk, state, _neighborhood from facetable limit 3" -i latest -t

pk  state  _neighborhood
--  -----  -------------
1   CA     Mission
2   CA     Dogpatch
3   CA     SOMA

Newline-delimited JSON, useful for piping into jq or other line-oriented tools:

dclient query fixtures "select pk, state from facetable limit 2" -i latest --nl

{"pk": 1, "state": "CA"}
{"pk": 2, "state": "CA"}

dclient query –help#

Usage: dclient query [OPTIONS] DATABASE SQL

  Run a SQL query against a Datasette database

  Requires both a database name and a SQL string.

  Example usage:

      dclient query fixtures "select * from facetable limit 5"
      dclient query analytics "select count(*) from events" -i staging

Options:
  -i, --instance TEXT  Datasette instance URL or alias
  --token TEXT         API token
  -v, --verbose        Verbose output: show HTTP request
  --csv                Output as CSV
  --tsv                Output as TSV
  --nl                 Output as newline-delimited JSON
  -t, --table          Output as ASCII table
  --help               Show this message and exit.