dclient/specs/dclient-v2.md

dclient v2 — Changes from v1

Summary of changes

1. Aliases point at Datasette instances, not database URLs
2. Instance is always a flag (-i/--instance), never a positional argument
3. query and insert/upsert take database as a required positional argument
4. Introspection commands (tables, schema, etc.) use -d/--database with defaults
5. Default instance and default database reduce typing
6. Bare SQL defaults to query command via click-default-group, using defaults + -d override
7. New commands: databases, tables, schema, plugins
8. Config and auth remain separate files, with a new config format
9. New DATASETTE_DATABASE environment variable

---

Config changes

config.json (replaces aliases.json)

{
  "default_instance": "prod",
  "instances": {
    "prod": {
      "url": "https://myapp.datasette.cloud",
      "default_database": "main"
    },
    "local": {
      "url": "http://localhost:8001",
      "default_database": null
    }
  }
}

auth.json (same file, new key structure)

Keys are now alias names instead of URLs:

{
  "prod": "dstok_abc123",
  "local": "dstok_def456"
}

When no alias exists (using -i with a raw URL), tokens are looked up by URL with the existing prefix-matching logic as a fallback.

Migration

On first run, if config.json doesn't exist but aliases.json does:

• Parse each v1 alias URL. If it has a single path segment (e.g., https://example.com/mydb), split into instance URL + default database. Otherwise store the URL as-is.
• Migrate auth.json keys from URLs to alias names where a match exists, keep URL-keyed entries as fallbacks.
• Write new files, rename originals to .bak.

Environment variables

Variable	Purpose	Precedence
DATASETTE_URL	Default instance URL (existing)	Below config default, above nothing
DATASETTE_DATABASE	Default database name (new)	Below config default, above auto-detect
DATASETTE_TOKEN	Auth token (existing)	Below stored token, above nothing

---

Two ways to query: explicit and shortcut

The design provides two distinct interfaces for running queries, optimized for different workflows.

Explicit: dclient query <database> <sql>

Database is a required positional argument. No defaults involved. This is unambiguous and works well for zero-config usage and when switching between databases frequently.

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

Shortcut: dclient <sql>

When the first argument doesn't match any subcommand, click-default-group routes it to a default command that uses the configured default instance and default database. Override either with flags.

dclient "select count(*) from users"                # default instance + default db
dclient "select count(*) from users" -d analytics   # override database
dclient "select count(*) from users" -i staging     # override instance

This is the daily-driver mode for people with a configured default instance and database.

Implementation

from click_default_group import DefaultGroup

@click.group(cls=DefaultGroup, default="default_query", default_if_no_args=False)
@click.version_option()
def cli():
    "A client CLI utility for Datasette instances"

@cli.command()
@click.argument("database")
@click.argument("sql")
@click.option("-i", "--instance")
@click.option("--token")
@click.option("-v", "--verbose", is_flag=True)
# ... output format options ...
def query(database, sql, instance, token, verbose, **kwargs):
    """Run a SQL query against a Datasette database

    Requires both a database name and a SQL string.

    Example:

        dclient query fixtures "select * from facetable limit 5"
    """
    ...

@cli.command(name="default_query", hidden=True)
@click.argument("sql")
@click.option("-i", "--instance")
@click.option("-d", "--database")
@click.option("--token")
@click.option("-v", "--verbose", is_flag=True)
# ... output format options ...
def default_query(sql, instance, database, token, verbose, **kwargs):
    """Run a SQL query using default instance and database."""
    # Resolve instance and database from defaults/env vars
    # Error if no default database can be resolved
    ...

The default_query command is hidden from help output. Users see query in the help text; the bare-SQL shortcut just works without being documented as a separate command.

---

Instance resolution

Every command that talks to a Datasette server accepts -i, --instance TEXT. Resolution order:

1. -i flag (alias name or URL)
2. config.default_instance
3. DATASETTE_URL environment variable
4. Error

If the value starts with http:// or https://, use it as a URL directly. Otherwise look it up as an alias name in config.

---

Database resolution

There are two modes of database resolution depending on the command.

Commands with required positional database

query, insert, upsert: the database is always the first positional argument. No resolution logic, no defaults. You must name the database.

Commands with optional -d flag

tables, schema, and the default query shortcut: resolve in order:

1. -d flag
2. Instance's default_database from config
3. DATASETTE_DATABASE environment variable
4. Auto-detect if instance has exactly one (non-internal) database
5. Error with a helpful message listing available databases

Commands that don't need a database

databases, plugins, actor, alias, auth: no database argument or flag.

---

New commands

dclient databases

List databases on an instance.

$ dclient databases
main
extra

$ dclient databases --json
[{"name": "main", "tables_count": 12, ...}, ...]

$ dclient databases -i https://latest.datasette.io
fixtures

Options: -i, --json.

Implementation: GET <instance>/.json → databases key.

dclient tables

List tables (and optionally views) in a database.

$ dclient tables
facetable          15 rows
facet_cities        4 rows

$ dclient tables -d analytics
events             1503 rows

$ dclient tables --views --json
[{"name": "facetable", "columns": [...], "count": 15, ...}, ...]

Options: -i, -d, --views, --views-only, --hidden, --json.

Implementation: GET <instance>/<database>.json → tables and views keys.

dclient schema

Show SQL schema for a database or a specific table.

$ dclient schema
# all CREATE TABLE/VIEW statements for default database

$ dclient schema -d analytics
# all schemas for the analytics database

$ dclient schema facetable
# just that table's schema (in default database)

$ dclient schema facetable -d analytics
# that table in a specific database

The optional table name is a positional argument. Options: -i, -d, --json.

dclient plugins

List installed plugins on an instance.

$ dclient plugins
datasette-files
datasette-auth-tokens

$ dclient plugins --json
[{"name": "datasette-files", "version": "0.3.1", ...}, ...]

Options: -i, --json.

Implementation: GET <instance>/-/plugins.json.

---

Changed commands

dclient query

# v1
dclient query https://datasette.io/content "select * from news"

# v2: database is required positional, instance is a flag
dclient query content "select * from news" -i https://datasette.io
dclient query fixtures "select * from facetable"    # uses default instance

Signature: dclient query <database> <sql> [-i instance] [--csv|--tsv|--nl|--table] [-o file] [--token TOKEN] [-v]

dclient insert

# v1
dclient insert https://myapp.datasette.cloud/data mytable data.csv --csv

# v2: database and table are required positionals, instance is a flag
dclient insert main mytable data.csv --csv -i myapp
dclient insert main mytable data.csv --csv          # uses default instance

Signature: dclient insert <database> <table> <file> [-i instance] [--csv|--tsv|--json|--nl] [--create] [--replace] [--ignore] [--alter] [--pk col] [--batch-size N] [--interval N] [--token TOKEN] [-v] [--silent]

dclient upsert

New command. Same shape as insert but hits /-/upsert.

Signature: dclient upsert <database> <table> <file> [-i instance] [--csv|--tsv|--json|--nl] [--alter] [--pk col] [--batch-size N] [--interval N] [--token TOKEN] [-v] [--silent]

dclient alias

dclient alias add <name> <url>         # url is the instance root, stored as-is
dclient alias remove <name>
dclient alias list                     # shows * for default, (db: x) for default db
dclient alias default [name]           # set/show default instance
dclient alias default --clear
dclient alias default-db <alias> [db]  # set/show default database for an alias
dclient alias default-db <alias> --clear

Tip: multiple aliases for the same instance. You can create several aliases pointing at the same instance URL with different default databases. This gives you short names for databases you switch between frequently:

dclient alias add prod https://myapp.datasette.cloud
dclient alias add prod-analytics https://myapp.datasette.cloud
dclient alias default-db prod main
dclient alias default-db prod-analytics analytics

dclient tables -i prod              # → main
dclient tables -i prod-analytics    # → analytics

Auth tokens are resolved by alias name first, then by URL match as a fallback, so a token stored for either alias will work for both.

dclient auth

dclient auth add <alias-or-url>           # prompt for token
dclient auth add <alias-or-url> --token TOKEN
dclient auth remove <alias-or-url>
dclient auth list                         # shows which aliases have tokens, never values
dclient auth status [-i instance]         # calls /-/actor.json to verify

dclient actor

Uses -i flag instead of positional URL:

dclient actor                  # default instance
dclient actor -i prod

---

Worked examples for the three usage modes

Mode 1: Zero config, public instance

# Explicit database, instance as flag
dclient databases -i https://latest.datasette.io
dclient tables -i https://latest.datasette.io -d fixtures
dclient query fixtures "select * from facetable limit 3" -i https://latest.datasette.io

# Or set env vars for a session
export DATASETTE_URL=https://latest.datasette.io
export DATASETTE_DATABASE=fixtures
dclient tables
dclient "select * from facetable limit 3"

Mode 2: Single default instance

# One-time setup
dclient alias add work https://myapp.datasette.cloud
dclient alias default work
dclient alias default-db work main
dclient auth add work

# Daily use — bare SQL shortcut uses defaults
dclient tables
dclient "select count(*) from users"
dclient "select * from events" -d analytics

# Explicit query when switching databases frequently
dclient query main "select count(*) from users"
dclient query analytics "select count(*) from events"

# Insert always names the database
dclient insert main events data.csv --csv --create --pk id
dclient plugins

Mode 3: Multiple aliases

dclient alias add prod https://prod.datasette.cloud
dclient alias add staging https://staging.datasette.cloud
dclient alias default prod
dclient alias default-db prod main
dclient alias default-db staging main

# Bare shortcut hits prod/main
dclient "select count(*) from users"

# Explicit query — name the database, override instance with -i
dclient query main "select count(*) from users" -i staging
dclient query analytics "select * from events" -i staging

# Insert always explicit
dclient insert main events data.csv --csv -i staging

---

Not in this version (reserved)

These command names are reserved for future work:

• dclient cloud — Datasette Cloud integration
• dclient files — datasette-files management
• dclient get, dclient rows, dclient update, dclient delete — row-level CRUD
• dclient create-table, dclient drop-table — DDL
• dclient queries — canned queries