Upgrade to actions/checkout@v7

Pin to httpx<1.0

.github/workflows/publish.yml

@@ -14,7 +14,7 @@ jobs:
       matrix:
         python-version: ["3.10", "3.11", "3.12", "3.13", "3.14"]
     steps:
-    - uses: actions/checkout@v6
+    - uses: actions/checkout@v7
     - name: Set up Python ${{ matrix.python-version }}
       uses: actions/setup-python@v6
       with:
@@ -34,16 +34,16 @@ jobs:
     permissions:
       id-token: write
     steps:
-    - uses: actions/checkout@v6
+    - uses: actions/checkout@v7
     - name: Set up Python
       uses: actions/setup-python@v6
       with:
-        python-version: "3.14"
+        python-version: "3.13"
         cache: pip
         cache-dependency-path: pyproject.toml
     - name: Install dependencies
       run: |
-        pip install setuptools wheel build
+        pip install build
     - name: Build
       run: |
         python -m build

.github/workflows/test.yml

@@ -12,7 +12,7 @@ jobs:
       matrix:
         python-version: ["3.10", "3.11", "3.12", "3.13", "3.14"]
     steps:
-    - uses: actions/checkout@v6
+    - uses: actions/checkout@v7
     - name: Set up Python ${{ matrix.python-version }}
       uses: actions/setup-python@v6
       with:

.gitignore

@@ -1,4 +1,5 @@
 .venv
+uv.lock
 __pycache__/
 *.py[cod]
 *$py.class
@@ -7,5 +8,3 @@ venv
 .pytest_cache
 *.egg-info
 .DS_Store
-uv.lock
-dist/

Justfile

@@ -1,37 +1,29 @@
 # Run tests and linters
 @default: test lint
 
-# Install dependencies and test dependencies
-@init:
-  pipenv run pip install -e '.[test]'
-
 # Run pytest with supplied options
 @test *options:
-  pipenv run pytest {{options}}
+  uv run pytest {{options}}
 
 # Run linters
 @lint:
   echo "Linters..."
-  echo "  Black"
-  pipenv run black . --check
   echo "  cog"
-  pipenv run cog --check README.md docs/*.md
-  echo "  ruff"
-  pipenv run ruff .
+  uv run cog --check README.md docs/*.md
+  echo "  ruff check"
+  uv run ruff check .
+  echo "  ruff format"
+  uv run ruff format . --check
 
 # Rebuild docs with cog
 @cog:
-  pipenv run cog -r docs/*.md
+  uv run cog -r docs/*.md
 
 # Serve live docs on localhost:8000
 @docs: cog
-  cd docs && pipenv run make livehtml
+  cd docs && uv run make livehtml
 
-# Apply Black
-@black:
-  pipenv run black .
-
-# Run automatic fixes
+# Apply ruff fixes and formatting
 @fix: cog
-  pipenv run ruff . --fix
-  pipenv run black .
+  uv run ruff check . --fix
+  uv run ruff format .

PLAN.md

@@ -1,529 +0,0 @@
-# dclient Improvement Plan
-
-## Feature Gap Analysis: dclient vs Datasette
-
-### What dclient currently supports
-
-| Feature | Status |
-|---------|--------|
-| SQL query execution (`query`) | Supported |
-| Bulk data insertion (`insert`) | Supported (CSV, TSV, JSON, JSONL) |
-| Actor/token introspection (`actor`) | Supported |
-| URL aliases (`alias add/list/remove`) | Supported |
-| Token storage (`auth add/list/remove`) | Supported |
-| Bearer token authentication | Supported |
-| Progress bar for inserts | Supported |
-| Verbose/debug mode | Supported |
-| Datasette plugin (`datasette dc`) | Supported |
-
-### What Datasette exposes that dclient does NOT cover
-
-#### Read Operations (high value)
-
-| Datasette Feature | API Endpoint | dclient Status |
-|-------------------|-------------|----------------|
-| List databases | `GET /-/databases.json` | **Missing** |
-| List tables/views in a database | `GET /{db}.json` | **Missing** |
-| Browse/filter table rows | `GET /{db}/{table}.json?filters...` | **Missing** |
-| Get single row by PK | `GET /{db}/{table}/{pk}.json` | **Missing** |
-| Table schema inspection | `GET /{db}/{table}/-/schema.json` | **Missing** |
-| Database schema inspection | `GET /{db}/-/schema.json` | **Missing** |
-| Full-text search | `?_search=term` | **Missing** |
-| Column filtering (exact, gt, lt, contains, etc.) | `?col__op=val` | **Missing** |
-| Faceted browsing | `?_facet=col` | **Missing** |
-| Sorting | `?_sort=col` / `?_sort_desc=col` | **Missing** |
-| Pagination | `?_next=cursor&_size=N` | **Missing** |
-| Column selection | `?_col=a&_col=b` / `?_nocol=x` | **Missing** |
-| CSV/TSV export of table data | `GET /{db}/{table}.csv` | **Missing** |
-| Canned queries listing | metadata in `GET /{db}.json` | **Missing** |
-
-#### Write Operations (high value)
-
-| Datasette Feature | API Endpoint | dclient Status |
-|-------------------|-------------|----------------|
-| Row upsert (insert-or-update) | `POST /{db}/{table}/-/upsert` | **Missing** |
-| Update a single row | `POST /{db}/{table}/{pk}/-/update` | **Missing** |
-| Delete a single row | `POST /{db}/{table}/{pk}/-/delete` | **Missing** |
-| Drop a table | `POST /{db}/{table}/-/drop` | **Missing** |
-| Create empty table with schema | `POST /{db}/-/create` (columns only) | **Missing** |
-
-#### Metadata/Admin (medium value)
-
-| Datasette Feature | API Endpoint | dclient Status |
-|-------------------|-------------|----------------|
-| Instance version info | `GET /-/versions.json` | **Missing** |
-| Installed plugins | `GET /-/plugins.json` | **Missing** |
-| Instance settings | `GET /-/settings.json` | **Missing** |
-| Token creation | `POST /-/create-token` | **Missing** |
-| Permission checks | `GET /-/allowed.json` | **Missing** |
-
----
-
-## Proposed Plan
-
-The plan is organized into tiers by impact and complexity, with the most
-valuable additions first.
-
----
-
-### Tier 1 -- Core Table Browsing & Schema Exploration
-
-These commands turn dclient from "a way to run raw SQL" into a genuine
-database exploration tool. They make up the biggest gap today: a user who
-wants to see what tables exist or browse rows must either open a browser
-or hand-craft SQL.
-
-#### 1.1 `dclient databases` -- List databases
-
-```
-dclient databases <instance_url_or_alias>
-```
-
-Hits `GET {url}/-/databases.json`. Prints a table of database names, sizes,
-and whether they are mutable. Add `--json` for raw JSON output.
-
-#### 1.2 `dclient tables` -- List tables and views
-
-```
-dclient tables <db_url_or_alias>          # e.g. https://latest.datasette.io/fixtures
-dclient tables <db_url_or_alias> --views  # include views
-dclient tables <db_url_or_alias> --counts # include row counts
-dclient tables <db_url_or_alias> --schema # include CREATE TABLE SQL
-```
-
-Hits `GET {db}.json`. Prints table names, row counts, and column lists.
-`--json` for raw JSON.
-
-#### 1.3 `dclient schema` -- Show table/database schema
-
-```
-dclient schema <db_url_or_alias>                     # full database schema
-dclient schema <db_url_or_alias>/<table>             # single table schema
-```
-
-Hits `GET {db}/-/schema.json` or `GET {db}/{table}/-/schema.json`.
-Outputs the `CREATE TABLE` / `CREATE VIEW` SQL.
-
-#### 1.4 `dclient rows` -- Browse table rows with filtering
-
-This is the biggest single feature addition. It exposes Datasette's
-powerful table filtering API without requiring the user to write SQL.
-
-```
-dclient rows <table_url_or_alias> [OPTIONS]
-```
-
-**Filter flags:**
-
-```
---where 'column = value'        # maps to ?column__exact=value
---where 'column > 100'          # maps to ?column__gt=100
---where 'column contains foo'   # maps to ?column__contains=foo
---where 'column is null'        # maps to ?column__isnull
---where-sql 'age > 30'          # maps to ?_where=age > 30  (raw SQL)
-```
-
-Or a simpler column-value approach:
-
-```
--w column=value                 # exact
--w column__gt=100               # pass Datasette filter operators directly
-```
-
-**Other flags:**
-
-```
---sort column                   # ascending sort
---sort-desc column              # descending sort
---search 'full text query'      # FTS search (?_search=...)
---col name --col email          # select specific columns
---nocol description             # exclude columns
---facet column                  # facet counts
---size 50                       # page size (default 100)
---all                           # auto-paginate through all pages
---limit N                       # stop after N total rows
---csv                           # output as CSV
---tsv                           # output as TSV
---nl                            # output as newline-delimited JSON
---json                          # output as JSON (default)
---table                         # human-readable table format
-```
-
-**Pagination:**
-By default, print one page of results. `--all` follows `next_url` to
-fetch every page. `--limit N` caps total rows returned.
-
-#### 1.5 `dclient get` -- Fetch a single row by primary key
-
-```
-dclient get <table_url_or_alias> <pk_value>
-dclient get <table_url_or_alias> <pk1>,<pk2>   # compound key
-```
-
-Hits `GET {db}/{table}/{pk}.json`. Prints the row as JSON.
-
----
-
-### Tier 2 -- Write Operations
-
-dclient already supports `insert` and `create`. These additions round out
-the full CRUD lifecycle.
-
-#### 2.1 `dclient upsert` -- Insert or update rows
-
-```
-dclient upsert <db_url_or_alias> <table> <filepath> [OPTIONS]
-```
-
-Same interface as `insert` but hits the `/-/upsert` endpoint. Requires
-rows to contain primary key columns.
-
-Shares most code with `insert`; the main difference is the endpoint URL
-and the absence of `--replace`/`--ignore` flags.
-
-#### 2.2 `dclient update` -- Update a single row
-
-```
-dclient update <table_url_or_alias> <pk_value> <key=value> [<key=value> ...]
-dclient update <table_url_or_alias> <pk_value> --input file.json
-```
-
-Hits `POST {db}/{table}/{pk}/-/update` with `{"update": {...}}`.
-
-#### 2.3 `dclient delete` -- Delete rows
-
-```
-dclient delete <table_url_or_alias> <pk_value>
-dclient delete <table_url_or_alias> <pk_value> --yes   # skip confirmation
-```
-
-Hits `POST {db}/{table}/{pk}/-/delete`. Prompts for confirmation unless
-`--yes` is passed.
-
-#### 2.4 `dclient drop` -- Drop a table
-
-```
-dclient drop <table_url_or_alias>
-dclient drop <table_url_or_alias> --yes   # skip confirmation
-```
-
-Hits `POST {db}/{table}/-/drop` with `{"confirm": true}`. Shows row count
-and asks for confirmation unless `--yes`.
-
-#### 2.5 `dclient create-table` -- Create an empty table with explicit schema
-
-```
-dclient create-table <db_url_or_alias> <table_name> \
-  --column id integer \
-  --column name text \
-  --column score float \
-  --pk id
-```
-
-Hits `POST {db}/-/create` with a `columns` array instead of `rows`.
-The existing `insert --create` handles creation-with-data; this handles
-the schema-only case.
-
----
-
-### Tier 3 -- Output & UX Improvements
-
-#### 3.1 Multiple output formats for `query` and `rows`
-
-Currently `query` only outputs JSON. Add:
-
-```
---csv          # CSV output
---tsv          # TSV output
---nl           # newline-delimited JSON
---table        # human-readable ASCII table (like sqlite-utils)
---yaml         # YAML output (optional, low priority)
-```
-
-For `--table` output, use a simple column-aligned format or integrate
-with `tabulate` / `rich` (consider keeping dependencies minimal).
-
-A lightweight approach: `sqlite-utils` already has table-formatting
-utilities that could be reused.
-
-#### 3.2 Pipe-friendly defaults
-
-Detect whether stdout is a TTY:
-- **TTY**: default to `--table` (human-readable) output
-- **Pipe**: default to JSON (machine-readable) output
-
-This matches the UX convention of tools like `gh` and `jq`.
-
-#### 3.3 `--output` / `-o` flag for writing to a file
-
-```
-dclient query ... -o results.csv --csv
-dclient rows ... -o dump.json
-```
-
-#### 3.4 Streaming output for large result sets
-
-For `--all` pagination mode and `--csv`/`--nl` formats, stream rows as
-they arrive rather than buffering everything in memory. Write each page
-to stdout immediately.
-
----
-
-### Tier 4 -- Authentication Improvements
-
-The current auth system works but has friction points. These changes
-make it smoother.
-
-#### 4.1 `dclient auth login` -- Browser-based authentication
-
-```
-dclient auth login <instance_url_or_alias>
-```
-
-Flow:
-1. Open the user's browser to `{instance}/-/create-token`
-2. Start a temporary local HTTP server to receive the token callback
-3. User creates a token in the Datasette UI and pastes it, OR:
-4. If the instance supports it, redirect back to the local server with the token
-5. Store the token automatically via the existing auth system
-
-For instances that don't support redirect, fall back to:
-1. Open the browser to `/-/create-token`
-2. Prompt the user to paste the token into the terminal
-
-This is similar to how `gh auth login`, `gcloud auth login`, and
-`heroku login` work.
-
-#### 4.2 `dclient auth token` -- Create a token via the API
-
-```
-dclient auth token <instance_url_or_alias>
-dclient auth token <instance_url_or_alias> --expires-after 3600
-dclient auth token <instance_url_or_alias> --read-only
-```
-
-Hits `POST /-/create-token`. Requires an existing root token or session.
-Prints the new token and optionally stores it.
-
-#### 4.3 `dclient auth status` -- Validate stored credentials
-
-```
-dclient auth status <instance_url_or_alias>
-```
-
-Hits `/-/actor.json` with the stored token and prints:
-- Whether the token is valid
-- The actor identity (id, permissions)
-- Token expiry if available
-
-This is just a friendlier wrapper around the existing `actor` command,
-integrated into the `auth` subgroup.
-
-#### 4.4 Environment variable support
-
-```
-export DCLIENT_TOKEN=dstok_xxx
-export DCLIENT_URL=https://my-datasette.example.com/mydb
-```
-
-Token resolution order (highest to lowest priority):
-1. `--token` CLI flag
-2. `DCLIENT_TOKEN` environment variable
-3. Stored token in `auth.json` (existing behavior)
-
-The `DCLIENT_URL` variable provides a default instance so users don't
-have to type it every time:
-
-```
-export DCLIENT_URL=https://my-datasette.example.com/mydb
-dclient tables                    # uses DCLIENT_URL
-dclient query 'select 1'         # uses DCLIENT_URL
-dclient rows mytable --limit 10  # uses DCLIENT_URL
-```
-
-#### 4.5 Keyring integration (optional, lower priority)
-
-Instead of storing tokens in plaintext `auth.json`, optionally use the
-system keyring via the `keyring` Python package. This keeps tokens
-encrypted at rest on macOS (Keychain), Windows (Credential Vault), and
-Linux (Secret Service / KWallet).
-
-Make this opt-in: `dclient auth add --keyring <url>`. Fall back to the
-current file-based storage if `keyring` is not installed.
-
----
-
-### Tier 5 -- Discoverability & Convenience
-
-#### 5.1 `dclient info` -- Instance overview
-
-```
-dclient info <instance_url_or_alias>
-```
-
-Hits `/-/versions.json`, `/-/plugins.json`, `/-/databases.json` in
-parallel. Prints a summary:
-
-```
-Datasette 1.0.1
-Python 3.12.1
-SQLite 3.45.0
-Databases: 3 (fixtures, content, ephemeral)
-Plugins: 12 installed
-```
-
-#### 5.2 URL inference and shortcuts
-
-Allow shorthand for common patterns:
-
-```
-# These should all work equivalently:
-dclient rows https://latest.datasette.io/fixtures/facetable
-dclient rows latest fixtures/facetable    # if 'latest' is an alias
-dclient rows latest facetable             # if alias points to a database URL
-```
-
-The key insight: if an alias points to `https://x.com/dbname`, then
-`dclient rows <alias> <table>` should construct
-`https://x.com/dbname/table.json`.
-
-Currently the alias is just a URL prefix that gets used verbatim. Making
-the alias system slightly smarter about joining paths would reduce typing
-significantly.
-
-#### 5.3 `dclient config` -- Manage configuration
-
-```
-dclient config show              # print config directory and contents
-dclient config path              # print config directory path
-dclient config edit              # open config in $EDITOR
-```
-
-Useful for debugging when tokens or aliases aren't working as expected.
-
-#### 5.4 Shell completions
-
-Use Click's built-in shell completion support to generate completions
-for bash, zsh, and fish:
-
-```
-dclient --install-completion bash
-dclient --install-completion zsh
-dclient --install-completion fish
-```
-
-Click 8.x supports this natively via `shell_complete`. This could also
-dynamically complete alias names, database names, and table names by
-querying the stored aliases.
-
----
-
-### Tier 6 -- Advanced Features (lower priority)
-
-#### 6.1 `dclient export` -- Bulk export table data
-
-```
-dclient export <table_url> -o data.csv --csv
-dclient export <table_url> -o data.json --json
-dclient export <table_url> -o data.db   # export to local SQLite
-```
-
-This is essentially `dclient rows --all` but optimized for bulk export:
-- Streams using `?_stream=1` (CSV format) when available
-- For JSON, auto-paginates using `_next` cursor
-- For SQLite output, pipes into `sqlite-utils insert`
-
-#### 6.2 Parallel insert for large files
-
-When inserting very large files, optionally send batches in parallel:
-
-```
-dclient insert <url> <table> huge.csv --parallel 4
-```
-
-Uses a thread pool to send N batches concurrently. Would require
-switching from synchronous `httpx` to `httpx.AsyncClient` or using
-`concurrent.futures.ThreadPoolExecutor`.
-
-#### 6.3 `dclient diff` -- Compare local and remote data
-
-```
-dclient diff local.db remote_alias/tablename
-```
-
-Compare a local SQLite table with a remote Datasette table. Show added,
-removed, and modified rows. Useful for sync workflows.
-
----
-
-## Implementation Notes
-
-### Shared infrastructure to build first
-
-Several of the proposed commands share common patterns. Before
-implementing individual commands, refactor these into shared utilities:
-
-1. **URL resolution helper**: Centralize the alias-lookup + URL-construction
-   logic that is currently duplicated in every command. A single function
-   like `resolve_url(url_or_alias, suffix=None)` that handles aliases,
-   adds `.json` where appropriate, and joins path segments.
-
-2. **Authenticated HTTP client helper**: A function or context manager
-   that creates an `httpx.Client` with the right `Authorization` header
-   and timeout, resolved from `--token` / env var / auth.json. This
-   eliminates the repeated token-resolution boilerplate in every command.
-
-3. **Output formatter**: A shared output module that handles `--json`,
-   `--csv`, `--tsv`, `--nl`, `--table` flags consistently across all
-   commands that return data.
-
-4. **Pagination helper**: A generator that follows `next_url` links to
-   yield all pages of results, used by `rows --all` and `export`.
-
-### Dependency considerations
-
-The current dependencies are minimal: `click`, `httpx`, `sqlite-utils`.
-The proposed changes mostly stay within these. Potential additions:
-
-- `tabulate` or `rich` for `--table` output (or implement a minimal
-  version to avoid new deps)
-- `keyring` for optional encrypted token storage (optional dependency)
-
-### Backward compatibility
-
-All proposed changes are additive (new commands and new flags). No
-existing command signatures or behaviors change. The only subtle change
-would be if TTY-detection changes the default output format of `query`,
-which should be gated behind a major version bump or opt-in flag.
-
-### Testing strategy
-
-Each new command should follow the existing test patterns:
-- Unit tests with `pytest-httpx` for HTTP mocking
-- Integration tests against a real in-memory Datasette instance
-  (as done in `test_insert.py`)
-- `cogapp` for keeping `--help` output in docs in sync
-
----
-
-## Priority Summary
-
-| Priority | Feature | Rationale |
-|----------|---------|-----------|
-| **P0** | `databases`, `tables`, `schema` | Basic exploration; most-requested gap |
-| **P0** | `rows` with filtering/sorting/pagination | Replaces need for raw SQL for common tasks |
-| **P0** | Environment variable support (`DCLIENT_TOKEN`, `DCLIENT_URL`) | Near-zero effort, big UX win |
-| **P1** | `get`, `update`, `delete`, `drop` | Completes CRUD lifecycle |
-| **P1** | `upsert` | Natural complement to existing `insert` |
-| **P1** | Multiple output formats (CSV, table, NL) | Makes `query`/`rows` output usable in pipelines |
-| **P1** | `auth login` (browser-based) | Biggest auth UX improvement |
-| **P2** | `create-table` (schema only) | Useful but `insert --create` covers most cases |
-| **P2** | TTY-aware default output | Polish |
-| **P2** | `auth status`, `auth token` | Convenience wrappers |
-| **P2** | `info` | Nice discoverability |
-| **P2** | URL inference / smarter aliases | Reduces typing |
-| **P3** | `export` (bulk) | Covered by `rows --all` for most cases |
-| **P3** | Shell completions | Polish |
-| **P3** | Keyring integration | Security hardening |
-| **P3** | Parallel insert | Performance optimization |
-| **P3** | `diff` | Advanced workflow |

README.md

@@ -11,15 +11,12 @@ Much of the functionality requires Datasette 1.0a2 or higher.
 
 ## Things you can do with dclient
 
-- **Explore** databases, tables, schemas, and rows without writing SQL
-- **Query** with SQL and get results as JSON, CSV, TSV, or formatted tables
-- **Insert** data from CSV, TSV, JSON, or newline-delimited JSON files
-- **Upsert** data (insert or update based on primary key)
-- **Update** individual rows by primary key
-- **Delete** rows or **drop** entire tables
-- **Create tables** with explicit schemas
+- Browse table data with filtering, sorting, and pagination — no SQL required
+- Run SQL queries against Datasette and return the results as JSON, CSV, TSV, or an ASCII table
+- Introspect databases, tables, plugins, and schema
 - Run queries against authenticated Datasette instances
-- Create aliases and store authentication tokens for convenient access
+- Create aliases and set default instances/databases for convenient access
+- Insert and upsert data using the [write API](https://docs.datasette.io/en/latest/json_api.html#the-json-write-api) (Datasette 1.0 alpha or higher)
 
 ## Installation
 
@@ -31,53 +28,38 @@ If you want to install it in the same virtual environment as Datasette (to use i
 ```bash
 datasette install dclient
 ```
-
 ## Quick start
 
+Add an alias for a Datasette instance:
 ```bash
-# Explore a Datasette instance
-dclient databases https://latest.datasette.io
-dclient tables https://latest.datasette.io/fixtures
-dclient schema https://latest.datasette.io/fixtures
-
-# Browse rows with filtering and sorting
-dclient rows https://latest.datasette.io/fixtures/facetable --table
-dclient rows https://latest.datasette.io/fixtures/facetable -w state=CA --sort city
-
-# Fetch a single row by primary key
-dclient get https://latest.datasette.io/fixtures/facetable 1
-
-# Run a SQL query
-dclient query https://latest.datasette.io/fixtures "select * from facetable limit 1"
+dclient alias add latest https://latest.datasette.io
+dclient default instance latest
+dclient default database latest fixtures
+```
+Now run queries directly:
+```bash
+dclient "select * from facetable limit 1"
+```
+Or be explicit:
+```bash
+dclient query fixtures "select * from facetable limit 1" -i latest
+```
+Output as a table with `-t`, or use `--csv`, `--tsv`, `--nl`:
+```bash
+dclient "select pk, state from facetable limit 3" -t
+```
+Browse table rows without SQL:
+```bash
+dclient rows facetable -f state eq CA --sort _city_id -t
 ```
 
-For write operations (requires authentication):
+## Introspection
 
 ```bash
-# Create a table
-dclient create-table https://example.com/db mytable \
-  --column id integer --column name text --pk id
-
-# Insert data from a CSV file
-dclient insert https://example.com/db mytable data.csv --create
-
-# Update a row
-dclient update https://example.com/db/mytable 42 name=Alice age=30
-
-# Upsert from a JSON file
-dclient upsert https://example.com/db mytable data.json --json
-
-# Delete a row
-dclient delete https://example.com/db/mytable 42 --yes
-
-# Drop a table
-dclient drop https://example.com/db/mytable --yes
-```
-
-To shorten URLs, create an alias:
-```bash
-dclient alias add fixtures https://latest.datasette.io/fixtures
-dclient query fixtures "select * from facetable limit 1"
+dclient databases
+dclient tables
+dclient plugins
+dclient schema facetable
 ```
 
 ## Documentation
@@ -86,12 +68,17 @@ Visit **[dclient.datasette.io](https://dclient.datasette.io)** for full document
 
 ## Development
 
-To contribute to this tool, first checkout the code. Then install dependencies and run tests using [uv](https://docs.astral.sh/uv/):
+To contribute to this tool, first checkout the code. Then create a new virtual environment:
 ```bash
 cd dclient
-uv run pytest
+python -m venv venv
+source venv/bin/activate
 ```
-To build the package:
+Now install the dependencies and test dependencies:
 ```bash
-uv build
+pip install -e '.[test]'
+```
+To run the tests:
+```bash
+pytest
 ```

demos/exploring-writing.md

@@ -1,322 +0,0 @@
-# Exploring and Writing Data with dclient
-
-*2026-02-12T01:33:47Z*
-
-This demo walks through dclient's data exploration and write commands against a live Datasette instance running on localhost with a mutable in-memory database called `demo`.
-
-## Creating a table
-
-`create-table` creates an empty table with an explicit schema. We define columns and a primary key up front.
-
-```bash
-uv run dclient create-table http://127.0.0.1:8042/demo dogs --column id integer --column name text --column age integer --column breed text --pk id --token dstok_eyJhIjoicm9vdCIsInQiOjE3NzA4NTk5ODJ9.1KDQjswLBzm5-R-MLmopOIHRZls
-```
-
-```output
-{
-  "ok": true,
-  "database": "demo",
-  "table": "dogs",
-  "table_url": "http://127.0.0.1:8042/demo/dogs",
-  "table_api_url": "http://127.0.0.1:8042/demo/dogs.json",
-  "schema": "CREATE TABLE [dogs] (\n   [id] INTEGER PRIMARY KEY,\n   [name] TEXT,\n   [age] INTEGER,\n   [breed] TEXT\n)"
-}
-```
-
-## Inserting rows from CSV
-
-`insert` reads CSV (or TSV, JSON, newline-delimited JSON) from a file or stdin. Here we pipe a CSV file in.
-
-```bash
-cat /tmp/dogs.csv | uv run dclient insert http://127.0.0.1:8042/demo dogs - --csv --token dstok_eyJhIjoicm9vdCIsInQiOjE3NzA4NTk5ODJ9.1KDQjswLBzm5-R-MLmopOIHRZls
-```
-
-```output
-```
-
-## Listing databases
-
-`databases` lists every database on the instance. The `--table` flag formats the output as a text table.
-
-```bash
-uv run dclient databases http://127.0.0.1:8042 --table --token dstok_eyJhIjoicm9vdCIsInQiOjE3NzA4NTk5ODJ9.1KDQjswLBzm5-R-MLmopOIHRZls
-```
-
-```output
-name     path  is_mutable
--------  ----  ----------
-_memory        False     
-demo           True      
-```
-
-## Listing tables
-
-`tables` shows all tables in a database with row counts and column names.
-
-```bash
-uv run dclient tables http://127.0.0.1:8042/demo --table --token dstok_eyJhIjoicm9vdCIsInQiOjE3NzA4NTk5ODJ9.1KDQjswLBzm5-R-MLmopOIHRZls
-```
-
-```output
-name  rows  columns             
-----  ----  --------------------
-dogs  5     id, name, age, breed
-```
-
-## Viewing the schema
-
-`schema` prints the CREATE TABLE SQL for every table in the database.
-
-```bash
-uv run dclient schema http://127.0.0.1:8042/demo --token dstok_eyJhIjoicm9vdCIsInQiOjE3NzA4NTk5ODJ9.1KDQjswLBzm5-R-MLmopOIHRZls
-```
-
-```output
-CREATE TABLE [dogs] (
-   [id] INTEGER PRIMARY KEY,
-   [name] TEXT,
-   [age] INTEGER,
-   [breed] TEXT
-);
-```
-
-## Browsing rows
-
-`rows` lets you explore table data without writing SQL. The default output is JSON; `--table` renders a text table.
-
-```bash
-uv run dclient rows http://127.0.0.1:8042/demo/dogs --table --token dstok_eyJhIjoicm9vdCIsInQiOjE3NzA4NTk5ODJ9.1KDQjswLBzm5-R-MLmopOIHRZls
-```
-
-```output
-id  name      age  breed           
---  --------  ---  ----------------
-1   Cleo      5    Golden Retriever
-2   Pancakes  3    Poodle          
-3   Fido      7    Labrador        
-4   Muffin    2    Corgi           
-5   Rex       4    German Shepherd 
-```
-
-### Filtering
-
-Use `-w` to filter using Datasette's column operators (`__gt`, `__contains`, `__exact`, etc.).
-
-```bash
-uv run dclient rows http://127.0.0.1:8042/demo/dogs --table -w age__gt=4 --token dstok_eyJhIjoicm9vdCIsInQiOjE3NzA4NTk5ODJ9.1KDQjswLBzm5-R-MLmopOIHRZls
-```
-
-```output
-id  name  age  breed           
---  ----  ---  ----------------
-1   Cleo  5    Golden Retriever
-3   Fido  7    Labrador        
-```
-
-### Sorting
-
-`--sort` and `--sort-desc` control row order.
-
-```bash
-uv run dclient rows http://127.0.0.1:8042/demo/dogs --table --sort-desc age --token dstok_eyJhIjoicm9vdCIsInQiOjE3NzA4NTk5ODJ9.1KDQjswLBzm5-R-MLmopOIHRZls
-```
-
-```output
-id  name      age  breed           
---  --------  ---  ----------------
-3   Fido      7    Labrador        
-1   Cleo      5    Golden Retriever
-5   Rex       4    German Shepherd 
-2   Pancakes  3    Poodle          
-4   Muffin    2    Corgi           
-```
-
-### Selecting columns
-
-`--col` picks specific columns. Here we also output as CSV, which is handy for piping into other tools.
-
-```bash
-uv run dclient rows http://127.0.0.1:8042/demo/dogs --csv --col name --col breed --token dstok_eyJhIjoicm9vdCIsInQiOjE3NzA4NTk5ODJ9.1KDQjswLBzm5-R-MLmopOIHRZls
-```
-
-```output
-id,name,breed
-1,Cleo,Golden Retriever
-2,Pancakes,Poodle
-3,Fido,Labrador
-4,Muffin,Corgi
-5,Rex,German Shepherd
-```
-
-### Limiting results
-
-`--limit` caps the total number of rows returned.
-
-```bash
-uv run dclient rows http://127.0.0.1:8042/demo/dogs --table --limit 2 --sort age --token dstok_eyJhIjoicm9vdCIsInQiOjE3NzA4NTk5ODJ9.1KDQjswLBzm5-R-MLmopOIHRZls
-```
-
-```output
-id  name      age  breed 
---  --------  ---  ------
-4   Muffin    2    Corgi 
-2   Pancakes  3    Poodle
-```
-
-## Fetching a single row
-
-`get` retrieves one row by its primary key.
-
-```bash
-uv run dclient get http://127.0.0.1:8042/demo/dogs 3 --token dstok_eyJhIjoicm9vdCIsInQiOjE3NzA4NTk5ODJ9.1KDQjswLBzm5-R-MLmopOIHRZls
-```
-
-```output
-{
-  "id": 3,
-  "name": "Fido",
-  "age": 7,
-  "breed": "Labrador"
-}
-```
-
-## Running SQL queries
-
-`query` executes arbitrary SQL and returns results. Here we use `--table` for a readable summary.
-
-```bash
-uv run dclient query http://127.0.0.1:8042/demo 'select breed, count(*) as count from dogs group by breed order by count desc' --table --token dstok_eyJhIjoicm9vdCIsInQiOjE3NzA4NTk5ODJ9.1KDQjswLBzm5-R-MLmopOIHRZls
-```
-
-```output
-breed             count
-----------------  -----
-Poodle            1    
-Labrador          1    
-Golden Retriever  1    
-German Shepherd   1    
-Corgi             1    
-```
-
-The same query as newline-delimited JSON, useful for streaming into `jq` or other line-oriented tools.
-
-```bash
-uv run dclient query http://127.0.0.1:8042/demo 'select name, age from dogs order by age desc limit 3' --nl --token dstok_eyJhIjoicm9vdCIsInQiOjE3NzA4NTk5ODJ9.1KDQjswLBzm5-R-MLmopOIHRZls
-```
-
-```output
-{"name": "Fido", "age": 7}
-{"name": "Cleo", "age": 5}
-{"name": "Rex", "age": 4}
-```
-
-## Updating a row
-
-`update` modifies a single row by primary key. Pass column=value pairs as arguments. Numeric values are auto-detected.
-
-```bash
-uv run dclient update http://127.0.0.1:8042/demo/dogs 3 name=Buddy age=8 --token dstok_eyJhIjoicm9vdCIsInQiOjE3NzA4NTk5ODJ9.1KDQjswLBzm5-R-MLmopOIHRZls
-```
-
-```output
-{
-  "ok": true
-}
-```
-
-Verify the update:
-
-```bash
-uv run dclient get http://127.0.0.1:8042/demo/dogs 3 --token dstok_eyJhIjoicm9vdCIsInQiOjE3NzA4NTk5ODJ9.1KDQjswLBzm5-R-MLmopOIHRZls
-```
-
-```output
-{
-  "id": 3,
-  "name": "Buddy",
-  "age": 8,
-  "breed": "Labrador"
-}
-```
-
-## Upserting rows
-
-`upsert` inserts new rows and updates existing ones that match by primary key. Here we update Cleo's age to 6 and add a new dog Luna, all in one call.
-
-```bash
-cat /tmp/upsert_dogs.json | uv run dclient upsert http://127.0.0.1:8042/demo dogs - --json --token dstok_eyJhIjoicm9vdCIsInQiOjE3NzA4NTk5ODJ9.1KDQjswLBzm5-R-MLmopOIHRZls
-```
-
-```output
-```
-
-The table now shows Cleo at age 6 and the new row for Luna:
-
-```bash
-uv run dclient rows http://127.0.0.1:8042/demo/dogs --table --sort id --token dstok_eyJhIjoicm9vdCIsInQiOjE3NzA4NTk5ODJ9.1KDQjswLBzm5-R-MLmopOIHRZls
-```
-
-```output
-id  name      age  breed           
---  --------  ---  ----------------
-1   Cleo      6    Golden Retriever
-2   Pancakes  3    Poodle          
-3   Buddy     8    Labrador        
-4   Muffin    2    Corgi           
-5   Rex       4    German Shepherd 
-6   Luna      1    Husky           
-```
-
-## Deleting a row
-
-`delete` removes a row by primary key. Use `--yes` to skip the confirmation prompt.
-
-```bash
-uv run dclient delete http://127.0.0.1:8042/demo/dogs 4 --yes --token dstok_eyJhIjoicm9vdCIsInQiOjE3NzA4NTk5ODJ9.1KDQjswLBzm5-R-MLmopOIHRZls
-```
-
-```output
-{
-  "ok": true
-}
-```
-
-Muffin (id=4) is gone:
-
-```bash
-uv run dclient rows http://127.0.0.1:8042/demo/dogs --table --sort id --token dstok_eyJhIjoicm9vdCIsInQiOjE3NzA4NTk5ODJ9.1KDQjswLBzm5-R-MLmopOIHRZls
-```
-
-```output
-id  name      age  breed           
---  --------  ---  ----------------
-1   Cleo      6    Golden Retriever
-2   Pancakes  3    Poodle          
-3   Buddy     8    Labrador        
-5   Rex       4    German Shepherd 
-6   Luna      1    Husky           
-```
-
-## Dropping a table
-
-`drop` removes an entire table. Without `--yes` it shows the row count and asks for confirmation.
-
-```bash
-uv run dclient drop http://127.0.0.1:8042/demo/dogs --yes --token dstok_eyJhIjoicm9vdCIsInQiOjE3NzA4NTk5ODJ9.1KDQjswLBzm5-R-MLmopOIHRZls
-```
-
-```output
-{
-  "ok": true
-}
-```
-
-The table is gone:
-
-```bash
-uv run dclient tables http://127.0.0.1:8042/demo --json --token dstok_eyJhIjoicm9vdCIsInQiOjE3NzA4NTk5ODJ9.1KDQjswLBzm5-R-MLmopOIHRZls
-```
-
-```output
-[]
-```

docs/aliases.md

@@ -1,17 +1,19 @@
 # Aliases
 
-You can assign an alias to a Datasette database using the `dclient alias` command:
+You can assign an alias to a Datasette instance using the `dclient alias add` command:
 
-    dclient alias add content https://datasette.io/content
+    dclient alias add latest https://latest.datasette.io
 
 You can list aliases with `dclient alias list`:
 
     $ dclient alias list
-    content = https://datasette.io/content
+      latest = https://latest.datasette.io
 
-Once registered, you can pass an alias to commands such as `dclient query`:
+Once registered, you can pass an alias to commands using the `-i` flag:
 
-    dclient query content "select * from news limit 1"
+    dclient query fixtures "select * from news limit 1" -i latest
+
+See [Defaults](defaults.md) for default instance and default database settings.
 
 ## dclient alias --help
 <!-- [[[cog
@@ -34,7 +36,7 @@ Options:
   --help  Show this message and exit.
 
 Commands:
-  add     Add an alias
+  add     Add an alias for a Datasette instance
   list    List aliases
   remove  Remove an alias
 
@@ -56,10 +58,6 @@ Usage: dclient alias list [OPTIONS]
 
   List aliases
 
-  Example usage:
-
-      dclient aliases list
-
 Options:
   --json  Output raw JSON
   --help  Show this message and exit.
@@ -80,15 +78,11 @@ cog.out(
 ```
 Usage: dclient alias add [OPTIONS] NAME URL
 
-  Add an alias
+  Add an alias for a Datasette instance
 
   Example usage:
 
-      dclient alias add content https://datasette.io/content
-
-  Then:
-
-      dclient query content 'select * from news limit 3'
+      dclient alias add prod https://myapp.datasette.cloud
 
 Options:
   --help  Show this message and exit.
@@ -113,7 +107,7 @@ Usage: dclient alias remove [OPTIONS] NAME
 
   Example usage:
 
-      dclient alias remove content
+      dclient alias remove prod
 
 Options:
   --help  Show this message and exit.

docs/authentication.md

@@ -4,35 +4,159 @@
 
 `dclient` can handle API tokens for Datasette instances that require authentication.
 
-You can pass an API token to `query` using `-t/--token` like this:
+You can pass an API token to any command using `--token` like this:
 
 ```bash
-dclient query https://latest.datasette.io/fixtures "select * from facetable" -t dstok_mytoken
+dclient query fixtures "select * from facetable" --token dstok_mytoken -i latest
 ```
 
-A more convenient way to handle this is to store tokens to be used with different URL prefixes.
+A more convenient way to handle this is to store tokens for your aliases.
+
+## Logging in with OAuth
+
+The easiest way to authenticate is using the `dclient login` command, which uses the OAuth device flow to obtain and store a token.
+
+This requires the Datasette instance to be running the [datasette-oauth](https://github.com/datasette/datasette-oauth) plugin with [device flow enabled](https://github.com/datasette/datasette-oauth?tab=readme-ov-file#plugin-configuration).
+
+```bash
+dclient login https://my-datasette.example.com/
+dclient login myalias
+dclient login
+```
+
+This will display a URL and a code. Open the URL in your browser, enter the code to approve access, and the resulting API token will be saved automatically. If you run `dclient login` without an argument, you will be prompted for the instance URL or alias.
+
+### Requesting scoped tokens
+
+By default, `dclient login` requests an unrestricted token. You can request a token with limited permissions using the shorthand options:
+
+```bash
+# Instance-wide read or write access
+dclient login --read-all
+dclient login --write-all
+
+# Database-level access
+dclient login --read db1
+dclient login --write db3
+
+# Table-level access
+dclient login --read db1/dogs
+dclient login --write db3/submissions
+
+# Mixed
+dclient login --read db1 --write db3/dogs
+```
+
+`--read-all` grants: `view-instance`, `view-table`, `view-database`, `view-query`, `execute-sql`.
+
+`--write-all` grants all read scopes plus: `insert-row`, `delete-row`, `update-row`, `create-table`, `alter-table`, `drop-table`.
+
+`--read` and `--write` accept a database name or `database/table` and can be specified multiple times. `--write` implies read access for the same target.
+
+For advanced use, you can also pass raw scope JSON with `--scope`:
+
+```bash
+dclient login myalias --scope '[["view-instance"]]'
+```
+
+All scope options can be combined — the shorthand options append to whatever `--scope` provides.
+
+### Outputting the token directly
+
+Use `--token-only` to print the token to stdout instead of saving it. This is useful for creating debug tokens or piping them into other tools:
+
+```bash
+dclient login --token-only --read mydb
+dclient login --token-only --write-all
+```
+
+## dclient login --help
+
+<!-- [[[cog
+import cog
+from dclient import cli
+from click.testing import CliRunner
+runner = CliRunner()
+result = runner.invoke(cli.cli, ["login", "--help"])
+help = result.output.replace("Usage: cli", "Usage: dclient")
+cog.out(
+    "```\n{}\n```".format(help)
+)
+]]] -->
+```
+Usage: dclient login [OPTIONS] [ALIAS_OR_URL]
+
+  Authenticate with a Datasette instance using OAuth
+
+  Uses the OAuth device flow: opens a URL in your browser where you approve
+  access, then saves the resulting API token.
+
+  Example usage:
+
+      dclient login https://simon.datasette.cloud/
+      dclient login myalias
+      dclient login
+      dclient login --read-all
+      dclient login --write-all
+      dclient login --read db1
+      dclient login --write db3/submissions
+      dclient login --read db1 --write db3/dogs
+
+Options:
+  --scope TEXT  JSON scope array
+  --read-all    Request instance-wide read access
+  --write-all   Request instance-wide write access
+  --read TEXT   Request read access for a database or database/table
+  --write TEXT  Request write access for a database or database/table
+  --token-only  Output the token to stdout instead of saving it
+  --help        Show this message and exit.
+
+```
+<!-- [[[end]]] -->
 
 ## Using stored tokens
 
-To always use `dstok_mytoken` for any URL on the `https://latest.datasette.io/` instance you can run this:
+To store a token for an alias:
 ```bash
-dclient auth add https://latest.datasette.io/
+dclient auth add latest
 ```
 Then paste in the token and hit enter when prompted to do so.
 
-To list which URLs you have set tokens for, run the `auth list` command:
+Tokens can also be stored for direct URLs:
+```bash
+dclient auth add https://latest.datasette.io
+```
+
+To list which aliases/URLs you have set tokens for, run the `auth list` command:
 ```bash
 dclient auth list
 ```
-To delete the token for a specific URL, run `auth remove`:
+To delete the token for a specific alias or URL, run `auth remove`:
 ```bash
-dclient auth remove https://latest.datasette.io/
+dclient auth remove latest
 ```
+
+## Token resolution order
+
+When making a request, dclient resolves the token in this order:
+
+1. `--token` CLI flag (highest priority)
+2. Token stored by alias name in `auth.json`
+3. Token stored by URL prefix in `auth.json`
+4. `DATASETTE_TOKEN` environment variable (lowest priority)
+
 ## Testing a token
 
-The `dclient actor` command can be used to test a token, retrieving the actor that the token represents.
+The `dclient auth status` command can be used to verify authentication by calling `/-/actor.json`:
 ```bash
-dclient actor https://latest.datasette.io/content
+dclient auth status
+dclient auth status -i prod
+```
+
+The `dclient actor` command also shows the actor:
+```bash
+dclient actor
+dclient actor -i prod
 ```
 The output looks like this:
 ```json
@@ -68,6 +192,7 @@ Commands:
   add     Add an authentication token for an alias or URL
   list    List stored API tokens
   remove  Remove the API token for an alias or URL
+  status  Verify authentication by calling /-/actor.json
 
 ```
 <!-- [[[end]]] -->
@@ -89,7 +214,8 @@ Usage: dclient auth add [OPTIONS] ALIAS_OR_URL
 
   Example usage:
 
-      dclient auth add https://datasette.io/content
+      dclient auth add prod
+      dclient auth add https://datasette.io
 
   Paste in the token when prompted.
 
@@ -142,7 +268,7 @@ Usage: dclient auth remove [OPTIONS] ALIAS_OR_URL
 
   Example usage:
 
-      dclient auth remove https://datasette.io/content
+      dclient auth remove prod
 
 Options:
   --help  Show this message and exit.
@@ -150,6 +276,34 @@ Options:
 ```
 <!-- [[[end]]] -->
 
+## dclient auth status --help
+
+<!-- [[[cog
+import cog
+result = runner.invoke(cli.cli, ["auth", "status", "--help"])
+help = result.output.replace("Usage: cli", "Usage: dclient")
+cog.out(
+    "```\n{}\n```".format(help)
+)
+]]] -->
+```
+Usage: dclient auth status [OPTIONS]
+
+  Verify authentication by calling /-/actor.json
+
+  Example usage:
+
+      dclient auth status
+      dclient auth status -i prod
+
+Options:
+  -i, --instance TEXT  Datasette instance URL or alias
+  --token TEXT         API token
+  --help               Show this message and exit.
+
+```
+<!-- [[[end]]] -->
+
 ## dclient actor --help
 
 <!-- [[[cog
@@ -161,17 +315,19 @@ cog.out(
 )
 ]]] -->
 ```
-Usage: dclient actor [OPTIONS] [URL_OR_ALIAS]
+Usage: dclient actor [OPTIONS]
 
   Show the actor represented by an API token
 
   Example usage:
 
-      dclient actor https://latest.datasette.io/fixtures
+      dclient actor
+      dclient actor -i prod
 
 Options:
-  --token TEXT  API token
-  --help        Show this message and exit.
+  -i, --instance TEXT  Datasette instance URL or alias
+  --token TEXT         API token
+  --help               Show this message and exit.
 
 ```
-<!-- [[[end]]] -->
+<!-- [[[end]]] -->

docs/defaults.md

@@ -0,0 +1,106 @@
+# Defaults
+
+Set a default instance so you don't need `-i` every time:
+
+    dclient default instance latest
+
+Now commands will use `latest` automatically:
+
+    dclient databases
+    dclient tables -d fixtures
+
+Set a default database for an alias or instance URL:
+
+    dclient default database latest fixtures
+    dclient default database https://latest.datasette.io fixtures
+
+Now you can run bare SQL queries directly:
+
+    dclient "select * from facetable limit 5"
+
+This uses the default instance and default database.
+
+## dclient default --help
+<!-- [[[cog
+import cog
+from dclient import cli
+from click.testing import CliRunner
+runner = CliRunner()
+result = runner.invoke(cli.cli, ["default", "--help"])
+help = result.output.replace("Usage: cli", "Usage: dclient")
+cog.out(
+    "```\n{}\n```".format(help)
+)
+]]] -->
+```
+Usage: dclient default [OPTIONS] COMMAND [ARGS]...
+
+  Manage default instance and database
+
+Options:
+  --help  Show this message and exit.
+
+Commands:
+  database  Set or show the default database for an instance
+  instance  Set or show the default instance
+
+```
+<!-- [[[end]]] -->
+
+## dclient default instance --help
+
+<!-- [[[cog
+import cog
+result = runner.invoke(cli.cli, ["default", "instance", "--help"])
+help = result.output.replace("Usage: cli", "Usage: dclient")
+cog.out(
+    "```\n{}\n```".format(help)
+)
+]]] -->
+```
+Usage: dclient default instance [OPTIONS] [ALIAS_OR_URL]
+
+  Set or show the default instance
+
+  Example usage:
+
+      dclient default instance prod
+      dclient default instance https://myapp.datasette.cloud
+      dclient default instance
+      dclient default instance --clear
+
+Options:
+  --clear  Clear default instance
+  --help   Show this message and exit.
+
+```
+<!-- [[[end]]] -->
+
+## dclient default database --help
+
+<!-- [[[cog
+import cog
+result = runner.invoke(cli.cli, ["default", "database", "--help"])
+help = result.output.replace("Usage: cli", "Usage: dclient")
+cog.out(
+    "```\n{}\n```".format(help)
+)
+]]] -->
+```
+Usage: dclient default database [OPTIONS] ALIAS_OR_URL [DB]
+
+  Set or show the default database for an instance
+
+  Example usage:
+
+      dclient default database prod main
+      dclient default database https://myapp.datasette.cloud main
+      dclient default database prod
+      dclient default database prod --clear
+
+Options:
+  --clear  Clear default database for this instance
+  --help   Show this message and exit.
+
+```
+<!-- [[[end]]] -->

docs/environment.md

@@ -0,0 +1,81 @@
+(environment-variables)=
+
+# Environment variables
+
+`dclient` supports several environment variables for convenient access to Datasette instances.
+
+## DATASETTE_URL
+
+Set this to the base URL of your Datasette instance. It is used as a fallback when no instance is specified via `-i` and no default instance is configured:
+
+```bash
+export DATASETTE_URL=https://my-instance.datasette.cloud
+```
+
+Then you can omit the `-i` flag:
+
+```bash
+dclient databases
+dclient query data "select * from my_table limit 10"
+```
+
+Aliases and the `-i` flag always take priority over `DATASETTE_URL`.
+
+## DATASETTE_DATABASE
+
+Set this to a default database name. It is used as a fallback when no database is specified via `-d` and the current instance has no `default_database` configured:
+
+```bash
+export DATASETTE_DATABASE=data
+```
+
+Then you can use the bare SQL shortcut:
+
+```bash
+dclient "select * from my_table limit 10"
+```
+
+## DATASETTE_TOKEN
+
+Set this to an API token:
+
+```bash
+export DATASETTE_TOKEN=dstok_abc123
+```
+
+The token will be used automatically for any request that doesn't have a more specific token configured.
+
+The precedence order for tokens is:
+
+1. `--token` CLI flag (highest priority)
+2. Stored token from `auth.json` (matched by alias name, then URL prefix)
+3. `DATASETTE_TOKEN` environment variable (lowest priority)
+
+## DCLIENT_CONFIG_DIR
+
+Override the config directory (default `~/.config/io.datasette.dclient` or platform equivalent):
+
+```bash
+export DCLIENT_CONFIG_DIR=/path/to/config
+```
+
+This is useful for testing or running multiple configurations side by side.
+
+## Using them together
+
+These variables work well together for quick access to a single instance:
+
+```bash
+export DATASETTE_URL=https://my-instance.datasette.cloud
+export DATASETTE_DATABASE=data
+export DATASETTE_TOKEN=dstok_abc123
+
+# Query
+dclient "select * from my_table"
+
+# Insert
+cat records.json | dclient insert data my_table - --json
+
+# Check your actor identity
+dclient actor
+```

docs/exploring.md

@@ -1,347 +0,0 @@
-# Exploring data
-
-dclient provides several commands for exploring Datasette instances without writing SQL.
-
-## Listing databases
-
-Use `dclient databases` to list all databases on a Datasette instance:
-
-```bash
-dclient databases https://latest.datasette.io
-```
-
-Use `--table` for a formatted text table:
-```bash
-dclient databases https://latest.datasette.io --table
-```
-Output:
-```
-name      path      is_mutable
---------  --------  ----------
-fixtures  fixtures  False
-```
-
-## Listing tables
-
-Use `dclient tables` to list tables in a specific database:
-
-```bash
-dclient tables https://latest.datasette.io/fixtures
-```
-
-Use `--table` for formatted output:
-```bash
-dclient tables https://latest.datasette.io/fixtures --table
-```
-
-Use `--schema` to include the CREATE TABLE SQL for each table:
-```bash
-dclient tables https://latest.datasette.io/fixtures --schema
-```
-
-Use `--views` to include views in the output (hidden by default).
-
-## Viewing schemas
-
-Use `dclient schema` to show the SQL schema for a database or an individual table:
-
-```bash
-# Schema for all tables in a database
-dclient schema https://latest.datasette.io/fixtures
-
-# Schema for a specific table
-dclient schema https://latest.datasette.io/fixtures/facetable
-```
-
-## Browsing rows
-
-Use `dclient rows` to browse table data with filtering, sorting, and pagination — no SQL required:
-
-```bash
-dclient rows https://latest.datasette.io/fixtures/facetable --table
-```
-
-### Filtering
-
-Use `-w` / `--where` to filter rows. Supports [Datasette filter operators](https://docs.datasette.io/en/stable/json_api.html#table-arguments) like `__gt`, `__contains`, `__exact`, etc.:
-
-```bash
-# Exact match
-dclient rows https://example.com/db/dogs -w breed=Poodle
-
-# Greater than
-dclient rows https://example.com/db/dogs -w age__gt=4
-
-# Multiple filters (AND)
-dclient rows https://example.com/db/dogs -w age__gte=3 -w breed=Labrador
-```
-
-For raw SQL WHERE clauses, use `--where-sql`:
-```bash
-dclient rows https://example.com/db/dogs --where-sql "age > 3 and breed != 'Poodle'"
-```
-
-### Sorting
-
-```bash
-# Sort ascending
-dclient rows https://example.com/db/dogs --sort name
-
-# Sort descending
-dclient rows https://example.com/db/dogs --sort-desc age
-```
-
-### Selecting columns
-
-```bash
-# Include only specific columns
-dclient rows https://example.com/db/dogs --col name --col breed
-
-# Exclude specific columns
-dclient rows https://example.com/db/dogs --nocol id
-```
-
-### Full-text search
-
-```bash
-dclient rows https://example.com/db/articles --search "python tutorial"
-```
-
-### Faceting
-
-```bash
-dclient rows https://example.com/db/dogs --facet breed
-```
-Facet results are printed to stderr, with the rows on stdout.
-
-### Pagination
-
-By default, only the first page of results is returned. Use `--all` to fetch every page:
-
-```bash
-dclient rows https://example.com/db/dogs --all
-```
-
-Control page size with `--size` and limit total rows with `--limit`:
-
-```bash
-# 10 rows per page, max 50 rows total
-dclient rows https://example.com/db/dogs --size 10 --limit 50
-```
-
-## Fetching a single row
-
-Use `dclient get` to fetch a single row by its primary key:
-
-```bash
-dclient get https://latest.datasette.io/fixtures/facetable 1
-```
-Output:
-```json
-{
-  "pk": 1,
-  "created": "2019-01-14 08:00:00",
-  "planet_int": 1,
-  "on_earth": 1,
-  "state": "CA",
-  ...
-}
-```
-
-For compound primary keys, separate values with a comma:
-```bash
-dclient get https://example.com/db/compound_pk a,b
-```
-
-## Output formats
-
-The `databases`, `tables`, `rows`, and `query` commands all support multiple output formats:
-
-- `--json` — JSON (default)
-- `--csv` — CSV
-- `--tsv` — TSV
-- `--nl` — Newline-delimited JSON
-- `--table` — Formatted text table
-
-```bash
-dclient rows https://example.com/db/dogs --csv > dogs.csv
-dclient rows https://example.com/db/dogs --table
-```
-
-## dclient databases --help
-<!-- [[[cog
-import cog
-from dclient import cli
-from click.testing import CliRunner
-runner = CliRunner()
-result = runner.invoke(cli.cli, ["databases", "--help"])
-help = result.output.replace("Usage: cli", "Usage: dclient")
-cog.out(
-    "```\n{}\n```".format(help)
-)
-]]] -->
-```
-Usage: dclient databases [OPTIONS] [URL_OR_ALIAS]
-
-  List databases on a Datasette instance
-
-  Example usage:
-
-      dclient databases https://latest.datasette.io
-
-Options:
-  --token TEXT   API token
-  -v, --verbose  Verbose output
-  --csv          Output as CSV
-  --tsv          Output as TSV
-  --json         Output as JSON (default)
-  --nl           Output as newline-delimited JSON
-  --table        Output as text table
-  --help         Show this message and exit.
-
-```
-<!-- [[[end]]] -->
-
-## dclient tables --help
-<!-- [[[cog
-import cog
-from dclient import cli
-from click.testing import CliRunner
-runner = CliRunner()
-result = runner.invoke(cli.cli, ["tables", "--help"])
-help = result.output.replace("Usage: cli", "Usage: dclient")
-cog.out(
-    "```\n{}\n```".format(help)
-)
-]]] -->
-```
-Usage: dclient tables [OPTIONS] [URL_OR_ALIAS]
-
-  List tables in a Datasette database
-
-  Example usage:
-
-      dclient tables https://latest.datasette.io/fixtures
-
-Options:
-  --token TEXT   API token
-  --views        Include views
-  --schema       Show CREATE TABLE SQL
-  -v, --verbose  Verbose output
-  --csv          Output as CSV
-  --tsv          Output as TSV
-  --json         Output as JSON (default)
-  --nl           Output as newline-delimited JSON
-  --table        Output as text table
-  --help         Show this message and exit.
-
-```
-<!-- [[[end]]] -->
-
-## dclient schema --help
-<!-- [[[cog
-import cog
-from dclient import cli
-from click.testing import CliRunner
-runner = CliRunner()
-result = runner.invoke(cli.cli, ["schema", "--help"])
-help = result.output.replace("Usage: cli", "Usage: dclient")
-cog.out(
-    "```\n{}\n```".format(help)
-)
-]]] -->
-```
-Usage: dclient schema [OPTIONS] [URL_OR_ALIAS]
-
-  Show the SQL schema for a database or table
-
-  Example usage:
-
-      dclient schema https://latest.datasette.io/fixtures
-      dclient schema https://latest.datasette.io/fixtures/facetable
-
-Options:
-  --token TEXT   API token
-  -v, --verbose  Verbose output
-  --help         Show this message and exit.
-
-```
-<!-- [[[end]]] -->
-
-## dclient rows --help
-<!-- [[[cog
-import cog
-from dclient import cli
-from click.testing import CliRunner
-runner = CliRunner()
-result = runner.invoke(cli.cli, ["rows", "--help"])
-help = result.output.replace("Usage: cli", "Usage: dclient")
-cog.out(
-    "```\n{}\n```".format(help)
-)
-]]] -->
-```
-Usage: dclient rows [OPTIONS] [URL_OR_ALIAS]
-
-  Browse rows in a Datasette table with filtering and sorting
-
-  Example usage:
-
-      dclient rows https://latest.datasette.io/fixtures/facetable
-      dclient rows https://latest.datasette.io/fixtures/facetable -w state=CA
-      dclient rows https://latest.datasette.io/fixtures/facetable --sort city
-      dclient rows https://latest.datasette.io/fixtures/facetable --search text
-
-Options:
-  --token TEXT      API token
-  -w, --where TEXT  Filter: column__op=value (e.g. age__gt=30)
-  --where-sql TEXT  Raw SQL WHERE clause
-  --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
-  --facet TEXT      Facet by column
-  --size INTEGER    Number of rows per page
-  --all             Fetch all pages
-  --limit INTEGER   Maximum total rows to return
-  -v, --verbose     Verbose output
-  --csv             Output as CSV
-  --tsv             Output as TSV
-  --json            Output as JSON (default)
-  --nl              Output as newline-delimited JSON
-  --table           Output as text table
-  --help            Show this message and exit.
-
-```
-<!-- [[[end]]] -->
-
-## dclient get --help
-<!-- [[[cog
-import cog
-from dclient import cli
-from click.testing import CliRunner
-runner = CliRunner()
-result = runner.invoke(cli.cli, ["get", "--help"])
-help = result.output.replace("Usage: cli", "Usage: dclient")
-cog.out(
-    "```\n{}\n```".format(help)
-)
-]]] -->
-```
-Usage: dclient get [OPTIONS] [URL_OR_ALIAS] PK_VALUES
-
-  Fetch a single row by primary key
-
-  Example usage:
-
-      dclient get https://latest.datasette.io/fixtures/facetable 1
-      dclient get https://latest.datasette.io/fixtures/compound_pk a,b
-
-Options:
-  --token TEXT   API token
-  -v, --verbose  Verbose output
-  --help         Show this message and exit.
-
-```
-<!-- [[[end]]] -->

docs/index.md

@@ -9,7 +9,7 @@ A client CLI utility for [Datasette](https://datasette.io/) instances
 
 ## Installation
 
-Install `dclient` using `pip` (or [pipx](https://pipxproject.github.io/pipx/)):
+Install `dclient` using `pip` (or [pipx](https://pipxproject.github.io/pipx/):
 
 ```bash
 pip install dclient
@@ -24,7 +24,7 @@ datasette install dclient
 This means you can run any of these commands using `datasette dc` instead, like this:
 ```bash
 datasette dc --help
-datasette dc query https://latest.datasette.io/fixtures "select * from facetable limit 1"
+datasette dc query fixtures "select * from facetable limit 1" -i latest
 ```
 
 ## Contents
@@ -33,10 +33,10 @@ datasette dc query https://latest.datasette.io/fixtures "select * from facetable
 ---
 maxdepth: 3
 ---
-exploring
 queries
-inserting
-writing
 aliases
+defaults
 authentication
+inserting
+environment
 ```

docs/inserting.md

@@ -1,24 +1,64 @@
 # Inserting data
 
+## Creating tables
+
+The `dclient create-table` command creates a new empty table with an explicit schema. Define columns with `--column name type` and optionally set primary keys with `--pk`:
+
+```bash
+dclient create-table mydb dogs \
+  --column id integer \
+  --column name text \
+  --column age integer \
+  --pk id \
+  -i myapp
+```
+
+This hits the Datasette [create API](https://docs.datasette.io/en/latest/json_api.html#the-json-write-api) with a `columns` array. The response includes the generated schema:
+
+```json
+{
+  "ok": true,
+  "database": "mydb",
+  "table": "dogs",
+  "schema": "CREATE TABLE [dogs] (\n   [id] INTEGER PRIMARY KEY,\n   [name] TEXT,\n   [age] INTEGER\n)"
+}
+```
+
+Compound primary keys are supported by passing `--pk` multiple times:
+
+```bash
+dclient create-table mydb events \
+  --column user_id integer \
+  --column event_id integer \
+  --column data text \
+  --pk user_id --pk event_id
+```
+
+If you want to create a table and populate it with data in one step, use `dclient insert --create` instead.
+
+## Inserting rows
+
 The `dclient insert` command can be used to insert data from a local file directly into a Datasette instance, via the [Write API](https://docs.datasette.io/en/latest/json_api.html#the-json-write-api) introduced in the Datasette 1.0 alphas.
 
 First you'll need to {ref}`authenticate <authentication>` with the instance.
 
-See also {doc}`writing` for related commands: `upsert`, `update`, `delete`, `drop`, and `create-table`.
-
-To insert data from a `data.csv` file into a table called `my_table`, creating that table if it does not exist:
+To insert data from a `data.csv` file into a table called `my_table` in the `data` database:
 
 ```bash
-dclient insert \
-  https://my-private-space.datasette.cloud/data \
-  my_table data.csv --create
+dclient insert data my_table data.csv --create -i myapp
 ```
 You can also pipe data into standard input:
 ```bash
 curl -s 'https://api.github.com/repos/simonw/dclient/issues' | \
-  dclient insert \
-    https://my-private-space.datasette.cloud/data \
-    issues - --create
+  dclient insert data issues - --create -i myapp
+```
+
+## Upserting data
+
+The `dclient upsert` command works exactly like `insert` but uses the upsert endpoint, which will update existing rows with matching primary keys rather than raising an error.
+
+```bash
+dclient upsert data my_table data.csv --csv -i myapp
 ```
 
 ## Streaming data
@@ -28,7 +68,7 @@ curl -s 'https://api.github.com/repos/simonw/dclient/issues' | \
 If you have a log file containing newline-delimited JSON you can tail it and send it to a Datasette instance like this:
 ```bash
 tail -f log.jsonl | \
-  dclient insert https://my-private-space.datasette.cloud/data logs - --nl
+  dclient insert data logs - --nl -i myapp
 ```
 When reading from standard input (filename `-`) you are required to specify the format. In this example that's `--nl` for newline-delimited JSON. `--csv` and `--tsv` are supported for streaming as well, but `--json` is not.
 
@@ -36,7 +76,7 @@ In streaming mode records default to being sent to the server every 100 records
 
 ```bash
 tail -f log.jsonl | dclient insert \
-  https://my-private-space.datasette.cloud/data logs - --nl --create \
+  data logs - --nl --create -i myapp \
   --batch-size 10 \
   --interval 5
 ```
@@ -108,32 +148,71 @@ cog.out(
 )
 ]]] -->
 ```
-Usage: dclient insert [OPTIONS] URL_OR_ALIAS TABLE FILEPATH
+Usage: dclient insert [OPTIONS] DATABASE TABLE FILEPATH
 
   Insert data into a remote Datasette instance
 
   Example usage:
 
-      dclient insert \
-        https://private.datasette.cloud/data \
-        mytable data.csv --pk id --create
+      dclient insert main mytable data.csv --csv -i myapp
+      dclient insert main mytable data.csv --csv --create --pk id
 
 Options:
+  -i, --instance TEXT   Datasette instance URL or alias
   --csv                 Input is CSV
   --tsv                 Input is TSV
   --json                Input is JSON
   --nl                  Input is newline-delimited JSON
   --encoding TEXT       Character encoding for CSV/TSV
   --no-detect-types     Don't detect column types for CSV/TSV
-  --replace             Replace rows with a matching primary key
-  --ignore              Ignore rows with a matching primary key
-  --create              Create table if it does not exist
   --alter               Alter table to add any missing columns
   --pk TEXT             Columns to use as the primary key when creating the
                         table
   --batch-size INTEGER  Send rows in batches of this size
   --interval FLOAT      Send batch at least every X seconds
-  -t, --token TEXT      API token
+  --token TEXT          API token
+  --silent              Don't output progress
+  -v, --verbose         Verbose output: show HTTP request and response
+  --replace             Replace rows with a matching primary key
+  --ignore              Ignore rows with a matching primary key
+  --create              Create table if it does not exist
+  --help                Show this message and exit.
+
+```
+<!-- [[[end]]] -->
+
+## dclient upsert --help
+<!-- [[[cog
+import cog
+result = runner.invoke(cli.cli, ["upsert", "--help"])
+help = result.output.replace("Usage: cli", "Usage: dclient")
+cog.out(
+    "```\n{}\n```".format(help)
+)
+]]] -->
+```
+Usage: dclient upsert [OPTIONS] DATABASE TABLE FILEPATH
+
+  Upsert data into a remote Datasette instance
+
+  Example usage:
+
+      dclient upsert main mytable data.csv --csv -i myapp
+
+Options:
+  -i, --instance TEXT   Datasette instance URL or alias
+  --csv                 Input is CSV
+  --tsv                 Input is TSV
+  --json                Input is JSON
+  --nl                  Input is newline-delimited JSON
+  --encoding TEXT       Character encoding for CSV/TSV
+  --no-detect-types     Don't detect column types for CSV/TSV
+  --alter               Alter table to add any missing columns
+  --pk TEXT             Columns to use as the primary key when creating the
+                        table
+  --batch-size INTEGER  Send rows in batches of this size
+  --interval FLOAT      Send batch at least every X seconds
+  --token TEXT          API token
   --silent              Don't output progress
   -v, --verbose         Verbose output: show HTTP request and response
   --help                Show this message and exit.
@@ -141,6 +220,33 @@ Options:
 ```
 <!-- [[[end]]] -->
 
-:::{note}
-To update existing rows rather than inserting new ones, see {doc}`writing` for the `upsert` and `update` commands.
-:::
+## dclient create-table --help
+<!-- [[[cog
+import cog
+result = runner.invoke(cli.cli, ["create-table", "--help"])
+help = result.output.replace("Usage: cli", "Usage: dclient")
+cog.out(
+    "```\n{}\n```".format(help)
+)
+]]] -->
+```
+Usage: dclient create-table [OPTIONS] DATABASE TABLE_NAME
+
+  Create a new empty table with an explicit schema
+
+  Example usage:
+
+      dclient create-table mydb dogs \
+        --column id integer --column name text --pk id
+
+Options:
+  -c, --column TEXT...  Column definition: name type (e.g. --column id integer
+                        --column name text)
+  --pk TEXT             Column(s) to use as primary key
+  -i, --instance TEXT   Datasette instance URL or alias
+  --token TEXT          API token
+  -v, --verbose         Verbose output: show HTTP request and response
+  --help                Show this message and exit.
+
+```
+<!-- [[[end]]] -->

docs/queries.md

@@ -3,7 +3,7 @@
 You can run SQL queries against a Datasette instance like this:
 
 ```bash
-dclient query https://latest.datasette.io/fixtures "select * from facetable limit 1"
+dclient query fixtures "select * from facetable limit 1" -i https://latest.datasette.io
 ```
 Output:
 ```json
@@ -24,32 +24,176 @@ Output:
 ]
 ```
 
+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:
+
+```bash
+dclient "select * from facetable limit 1"
+```
+
+You can override just the database with `-d`:
+
+```bash
+dclient "select * from counters" -d counters
+```
+
+## Browsing rows
+
+The `dclient rows` command lets you browse table data without writing SQL:
+
+```bash
+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:
+
+```bash
+dclient rows facet_cities -t
+```
+
+### Filtering
+
+Use `-f` / `--filter` with three arguments: column, operation, value:
+
+```bash
+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
+
+```bash
+dclient rows dogs --sort age
+dclient rows dogs --sort-desc age
+```
+
+### Column selection
+
+```bash
+dclient rows dogs --col name --col age
+dclient rows dogs --nocol id
+```
+
+### Search
+
+Full-text search (requires an FTS index on the table):
+
+```bash
+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:
+
+```bash
+dclient rows dogs --all
+dclient rows dogs --all --limit 500
+dclient rows dogs --size 50
+```
+
+### dclient rows --help
+<!-- [[[cog
+import cog
+from dclient import cli
+from click.testing import CliRunner
+runner = CliRunner()
+result = runner.invoke(cli.cli, ["rows", "--help"])
+help = result.output.replace("Usage: cli", "Usage: dclient")
+cog.out(
+    "```\n{}\n```".format(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.
+
+```
+<!-- [[[end]]] -->
+
 ## Output formats
 
-Query results default to JSON. Use these flags for other formats:
+By default, results are returned as JSON. Use these flags to change the output format:
 
 - `--csv` — CSV
 - `--tsv` — TSV
-- `--nl` — Newline-delimited JSON (one JSON object per line)
-- `--table` — Formatted text table
+- `-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:
 
 ```bash
-dclient query https://latest.datasette.io/fixtures \
-  "select state, count(*) as n from facetable group by state" --table
+dclient query fixtures "select * from facetable limit 2" -i latest --csv
 ```
-Output:
 ```
-state  n
------  --
-CA     10
-MI     4
-MO     1
+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
 ```
 
-Export as CSV:
+ASCII table output:
+
 ```bash
-dclient query https://latest.datasette.io/fixtures \
-  "select * from facetable limit 5" --csv > results.csv
+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:
+
+```bash
+dclient query fixtures "select pk, state from facetable limit 2" -i latest --nl
+```
+```
+{"pk": 1, "state": "CA"}
+{"pk": 2, "state": "CA"}
 ```
 
 ## dclient query --help
@@ -65,27 +209,26 @@ cog.out(
 )
 ]]] -->
 ```
-Usage: dclient query [OPTIONS] URL_OR_ALIAS SQL
+Usage: dclient query [OPTIONS] DATABASE SQL
 
-  Run a SQL query against a Datasette database URL
+  Run a SQL query against a Datasette database
 
-  Returns a JSON array of objects
+  Requires both a database name and a SQL string.
 
   Example usage:
 
-      dclient query \
-        https://datasette.io/content \
-        'select * from news limit 10'
+      dclient query fixtures "select * from facetable limit 5"
+      dclient query analytics "select count(*) from events" -i staging
 
 Options:
-  --token TEXT   API token
-  -v, --verbose  Verbose output: show HTTP request
-  --csv          Output as CSV
-  --tsv          Output as TSV
-  --json         Output as JSON (default)
-  --nl           Output as newline-delimited JSON
-  --table        Output as text table
-  --help         Show this message and exit.
+  -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.
 
 ```
 <!-- [[[end]]] -->

docs/writing.md

@@ -1,265 +0,0 @@
-# Writing data
-
-In addition to `insert` (documented in {doc}`inserting`), dclient provides several commands for modifying data in a Datasette instance via the [Write API](https://docs.datasette.io/en/latest/json_api.html#the-json-write-api).
-
-All write commands require authentication. See {doc}`authentication` for how to set up tokens.
-
-## Creating tables
-
-Use `dclient create-table` to create a new empty table with an explicit schema:
-
-```bash
-dclient create-table https://example.com/db mytable \
-  --column id integer \
-  --column name text \
-  --column age integer \
-  --pk id
-```
-Output:
-```json
-{
-  "ok": true,
-  "database": "db",
-  "table": "mytable",
-  "schema": "CREATE TABLE [mytable] (\n   [id] INTEGER PRIMARY KEY,\n   [name] TEXT,\n   [age] INTEGER\n)"
-}
-```
-
-Use `--pk` one or more times to set compound primary keys:
-```bash
-dclient create-table https://example.com/db events \
-  --column date text --column venue text --column title text \
-  --pk date --pk venue
-```
-
-## Upserting data
-
-Use `dclient upsert` to insert rows or update existing ones based on primary key. This works like `insert` but uses the [upsert API endpoint](https://docs.datasette.io/en/latest/json_api.html#upserting-rows):
-
-```bash
-dclient upsert https://example.com/db mytable data.csv
-```
-
-The same file format options as `insert` are supported: `--csv`, `--tsv`, `--json`, `--nl`, `--encoding`, `--no-detect-types`.
-
-Upsert from standard input:
-```bash
-echo '[{"id": 1, "name": "Updated"}]' | \
-  dclient upsert https://example.com/db mytable - --json
-```
-
-Use `--alter` to add any missing columns automatically.
-
-## Updating a single row
-
-Use `dclient update` to update specific columns of a row by its primary key:
-
-```bash
-dclient update https://example.com/db/mytable 42 name=Alice age=30
-```
-
-Values are automatically parsed — numbers become integers/floats, `true`/`false` become booleans, `null` becomes null. Everything else is treated as a string.
-
-Use `--alter` to allow adding new columns that don't exist yet.
-
-## Deleting a row
-
-Use `dclient delete` to delete a single row by primary key:
-
-```bash
-dclient delete https://example.com/db/mytable 42
-```
-
-You'll be prompted for confirmation. Use `--yes` to skip the prompt:
-```bash
-dclient delete https://example.com/db/mytable 42 --yes
-```
-
-## Dropping a table
-
-Use `dclient drop` to drop an entire table:
-
-```bash
-dclient drop https://example.com/db/mytable
-```
-
-Without `--yes`, dclient will first query the table and show you the row count before asking for confirmation:
-```
-Drop table mytable (150 rows)? [y/N]:
-```
-
-Use `--yes` to skip confirmation:
-```bash
-dclient drop https://example.com/db/mytable --yes
-```
-
-## dclient create-table --help
-<!-- [[[cog
-import cog
-from dclient import cli
-from click.testing import CliRunner
-runner = CliRunner()
-result = runner.invoke(cli.cli, ["create-table", "--help"])
-help = result.output.replace("Usage: cli", "Usage: dclient")
-cog.out(
-    "```\n{}\n```".format(help)
-)
-]]] -->
-```
-Usage: dclient create-table [OPTIONS] URL_OR_ALIAS TABLE_NAME
-
-  Create a new empty table with explicit schema
-
-  Example usage:
-
-      dclient create-table https://example.com/db mytable \
-        --column id integer --column name text --pk id
-
-Options:
-  --column TEXT...  Column definition: name type (e.g. --column id integer
-                    --column name text)
-  --pk TEXT         Column(s) to use as primary key
-  --token TEXT      API token
-  -v, --verbose     Verbose output
-  --help            Show this message and exit.
-
-```
-<!-- [[[end]]] -->
-
-## dclient upsert --help
-<!-- [[[cog
-import cog
-from dclient import cli
-from click.testing import CliRunner
-runner = CliRunner()
-result = runner.invoke(cli.cli, ["upsert", "--help"])
-help = result.output.replace("Usage: cli", "Usage: dclient")
-cog.out(
-    "```\n{}\n```".format(help)
-)
-]]] -->
-```
-Usage: dclient upsert [OPTIONS] URL_OR_ALIAS TABLE FILEPATH
-
-  Upsert data into a remote Datasette table
-
-  Rows with matching primary keys will be updated; others will be inserted. Each
-  row must include the primary key column(s).
-
-  Example usage:
-
-      dclient upsert \
-        https://private.datasette.cloud/data \
-        mytable data.csv
-
-Options:
-  --csv                 Input is CSV
-  --tsv                 Input is TSV
-  --json                Input is JSON
-  --nl                  Input is newline-delimited JSON
-  --encoding TEXT       Character encoding for CSV/TSV
-  --no-detect-types     Don't detect column types for CSV/TSV
-  --alter               Alter table to add any missing columns
-  --batch-size INTEGER  Send rows in batches of this size
-  --interval FLOAT      Send batch at least every X seconds
-  -t, --token TEXT      API token
-  --silent              Don't output progress
-  -v, --verbose         Verbose output: show HTTP request and response
-  --help                Show this message and exit.
-
-```
-<!-- [[[end]]] -->
-
-## dclient update --help
-<!-- [[[cog
-import cog
-from dclient import cli
-from click.testing import CliRunner
-runner = CliRunner()
-result = runner.invoke(cli.cli, ["update", "--help"])
-help = result.output.replace("Usage: cli", "Usage: dclient")
-cog.out(
-    "```\n{}\n```".format(help)
-)
-]]] -->
-```
-Usage: dclient update [OPTIONS] URL_OR_ALIAS PK_VALUES [UPDATES]...
-
-  Update a row by primary key
-
-  Pass key=value pairs to set column values.
-
-  Example usage:
-
-      dclient update https://example.com/db/table 42 name=Alice age=30
-
-Options:
-  --token TEXT   API token
-  --alter        Alter table to add any missing columns
-  -v, --verbose  Verbose output
-  --help         Show this message and exit.
-
-```
-<!-- [[[end]]] -->
-
-## dclient delete --help
-<!-- [[[cog
-import cog
-from dclient import cli
-from click.testing import CliRunner
-runner = CliRunner()
-result = runner.invoke(cli.cli, ["delete", "--help"])
-help = result.output.replace("Usage: cli", "Usage: dclient")
-cog.out(
-    "```\n{}\n```".format(help)
-)
-]]] -->
-```
-Usage: dclient delete [OPTIONS] URL_OR_ALIAS PK_VALUES
-
-  Delete a row by primary key
-
-  Example usage:
-
-      dclient delete https://example.com/db/table 42
-      dclient delete https://example.com/db/table 42 --yes
-
-Options:
-  --token TEXT   API token
-  --yes          Skip confirmation
-  -v, --verbose  Verbose output
-  --help         Show this message and exit.
-
-```
-<!-- [[[end]]] -->
-
-## dclient drop --help
-<!-- [[[cog
-import cog
-from dclient import cli
-from click.testing import CliRunner
-runner = CliRunner()
-result = runner.invoke(cli.cli, ["drop", "--help"])
-help = result.output.replace("Usage: cli", "Usage: dclient")
-cog.out(
-    "```\n{}\n```".format(help)
-)
-]]] -->
-```
-Usage: dclient drop [OPTIONS] URL_OR_ALIAS
-
-  Drop a table
-
-  Example usage:
-
-      dclient drop https://example.com/db/table
-      dclient drop https://example.com/db/table --yes
-
-Options:
-  --token TEXT   API token
-  --yes          Skip confirmation
-  -v, --verbose  Verbose output
-  --help         Show this message and exit.
-
-```
-<!-- [[[end]]] -->

pyproject.toml

@@ -1,14 +1,15 @@
 [project]
 name = "dclient"
-version = "0.4"
+version = "0.5a3"
 description = "A client CLI utility for Datasette instances"
 readme = "README.md"
-requires-python = ">=3.8"
-license = "Apache-2.0"
 authors = [{name = "Simon Willison"}]
+license = "Apache-2.0"
+requires-python = ">=3.10"
 dependencies = [
     "click",
-    "httpx",
+    "click-default-group",
+    "httpx<1.0",
     "sqlite-utils",
 ]
 
@@ -25,18 +26,29 @@ dclient = "dclient.cli:cli"
 client = "dclient.plugin"
 
 [dependency-groups]
-dev = [
+test = [
     "pytest",
     "pytest-asyncio",
     "pytest-httpx",
     "cogapp",
     "pytest-mock",
-    "datasette>=1.0a2",
+    "datasette>=1.0a25",
 ]
+docs = [
+    "furo",
+    "sphinx-autobuild",
+    "sphinx-copybutton",
+    "myst-parser",
+    "cogapp",
+]
+dev = [
+    {include-group = "test"},
+    {include-group = "docs"},
+]
+
+[tool.uv.build-backend]
+module-root = ""
 
 [build-system]
 requires = ["uv_build>=0.9.18,<0.10.0"]
 build-backend = "uv_build"
-
-[tool.uv.build-backend]
-module-root = ""

specs/dclient-v2.md

@@ -0,0 +1,408 @@
+# 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)
+
+```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:
+
+```json
+{
+  "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.
+
+```bash
+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.
+
+```bash
+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
+
+```python
+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.
+
+```bash
+$ 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.
+
+```bash
+$ 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.
+
+```bash
+$ 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.
+
+```bash
+$ 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`
+
+```bash
+# 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`
+
+```bash
+# 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`
+
+```bash
+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:
+
+```bash
+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`
+
+```bash
+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:
+
+```bash
+dclient actor                  # default instance
+dclient actor -i prod
+```
+
+---
+
+## Worked examples for the three usage modes
+
+### Mode 1: Zero config, public instance
+
+```bash
+# 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
+
+```bash
+# 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
+
+```bash
+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

tests/test_alias_v2.py

@@ -0,0 +1,139 @@
+"""Tests for v2 defaults command: instance and database subcommands."""
+
+from click.testing import CliRunner
+from dclient.cli import cli
+import json
+import pathlib
+
+
+def test_alias_default_workflow(mocker, tmpdir):
+    mocker.patch("dclient.cli.get_config_dir", return_value=pathlib.Path(tmpdir))
+    runner = CliRunner()
+
+    # Add an alias
+    result = runner.invoke(cli, ["alias", "add", "prod", "https://prod.example.com"])
+    assert result.exit_code == 0
+
+    # No default yet
+    result = runner.invoke(cli, ["default", "instance"])
+    assert result.exit_code == 0
+    assert "No default instance set" in result.output
+
+    # Set default
+    result = runner.invoke(cli, ["default", "instance", "prod"])
+    assert result.exit_code == 0
+
+    # Show default
+    result = runner.invoke(cli, ["default", "instance"])
+    assert result.exit_code == 0
+    assert result.output.strip() == "prod"
+
+    # List should show * marker
+    result = runner.invoke(cli, ["alias", "list"])
+    assert result.exit_code == 0
+    assert "* prod" in result.output
+
+    # Clear default
+    result = runner.invoke(cli, ["default", "instance", "--clear"])
+    assert result.exit_code == 0
+
+    result = runner.invoke(cli, ["default", "instance"])
+    assert "No default instance set" in result.output
+
+
+def test_alias_default_unknown_alias(mocker, tmpdir):
+    mocker.patch("dclient.cli.get_config_dir", return_value=pathlib.Path(tmpdir))
+    runner = CliRunner()
+    result = runner.invoke(cli, ["default", "instance", "nonexistent"])
+    assert result.exit_code == 1
+    assert "No such alias" in result.output
+
+
+def test_alias_default_db_workflow(mocker, tmpdir):
+    mocker.patch("dclient.cli.get_config_dir", return_value=pathlib.Path(tmpdir))
+    runner = CliRunner()
+
+    # Add an alias
+    result = runner.invoke(cli, ["alias", "add", "prod", "https://prod.example.com"])
+    assert result.exit_code == 0
+
+    # No default database yet
+    result = runner.invoke(cli, ["default", "database", "prod"])
+    assert result.exit_code == 0
+    assert "No default database set" in result.output
+
+    # Set default database
+    result = runner.invoke(cli, ["default", "database", "prod", "main"])
+    assert result.exit_code == 0
+
+    # Show default database
+    result = runner.invoke(cli, ["default", "database", "prod"])
+    assert result.exit_code == 0
+    assert result.output.strip() == "main"
+
+    # List should show db info
+    result = runner.invoke(cli, ["alias", "list"])
+    assert "(db: main)" in result.output
+
+    # Clear default database
+    result = runner.invoke(cli, ["default", "database", "prod", "--clear"])
+    assert result.exit_code == 0
+
+    result = runner.invoke(cli, ["default", "database", "prod"])
+    assert "No default database set" in result.output
+
+
+def test_alias_default_db_unknown_alias(mocker, tmpdir):
+    mocker.patch("dclient.cli.get_config_dir", return_value=pathlib.Path(tmpdir))
+    runner = CliRunner()
+    result = runner.invoke(cli, ["default", "database", "nonexistent", "main"])
+    assert result.exit_code == 1
+    assert "No such alias" in result.output
+
+
+def test_alias_remove_clears_default(mocker, tmpdir):
+    """Removing the default alias also clears default_instance."""
+    mocker.patch("dclient.cli.get_config_dir", return_value=pathlib.Path(tmpdir))
+    runner = CliRunner()
+
+    runner.invoke(cli, ["alias", "add", "prod", "https://prod.example.com"])
+    runner.invoke(cli, ["default", "instance", "prod"])
+
+    # Verify it's set
+    config = json.loads((pathlib.Path(tmpdir) / "config.json").read_text())
+    assert config["default_instance"] == "prod"
+
+    # Remove alias
+    runner.invoke(cli, ["alias", "remove", "prod"])
+
+    config = json.loads((pathlib.Path(tmpdir) / "config.json").read_text())
+    assert config["default_instance"] is None
+    assert "prod" not in config["instances"]
+
+
+def test_default_instance_accepts_url(mocker, tmpdir):
+    mocker.patch("dclient.cli.get_config_dir", return_value=pathlib.Path(tmpdir))
+    runner = CliRunner()
+
+    runner.invoke(cli, ["alias", "add", "prod", "https://prod.example.com"])
+    result = runner.invoke(cli, ["default", "instance", "https://prod.example.com"])
+    assert result.exit_code == 0
+
+    result = runner.invoke(cli, ["default", "instance"])
+    assert result.exit_code == 0
+    assert result.output.strip() == "prod"
+
+
+def test_default_database_accepts_url(mocker, tmpdir):
+    mocker.patch("dclient.cli.get_config_dir", return_value=pathlib.Path(tmpdir))
+    runner = CliRunner()
+
+    runner.invoke(cli, ["alias", "add", "prod", "https://prod.example.com"])
+    result = runner.invoke(
+        cli, ["default", "database", "https://prod.example.com", "main"]
+    )
+    assert result.exit_code == 0
+
+    result = runner.invoke(cli, ["default", "database", "prod"])
+    assert result.exit_code == 0
+    assert result.output.strip() == "main"

tests/test_cli_auth.py

@@ -2,6 +2,7 @@ from click.testing import CliRunner
 from dclient.cli import cli
 import pathlib
 import json
+import pytest
 
 
 def test_auth(mocker, tmpdir):
@@ -13,29 +14,510 @@ def test_auth(mocker, tmpdir):
     # Should only have one line
     assert len([line for line in result.output.split("\n") if line.strip()]) == 1
 
-    # Now add a token
-    result2 = runner.invoke(cli, ["auth", "add", "https://example.com"], input="xyz\n")
+    # Now add a token (keys are now alias names or URLs)
+    result2 = runner.invoke(cli, ["auth", "add", "prod"], input="xyz\n")
     assert result2.exit_code == 0
 
     # Check the tokens file
     auth_file = pathlib.Path(tmpdir) / "auth.json"
-    assert json.loads(auth_file.read_text()) == {"https://example.com": "xyz"}
+    assert json.loads(auth_file.read_text()) == {"prod": "xyz"}
 
     # auth list should show that now
     result3 = runner.invoke(cli, ["auth", "list"])
     assert result3.output.startswith("Tokens file:")
-    assert "https://example.com" in result3.output
+    assert "prod" in result3.output
 
-    # Remove should fail with an incorrect URL
-    result4 = runner.invoke(cli, ["auth", "remove", "https://example.com/foo"])
+    # Remove should fail with an incorrect key
+    result4 = runner.invoke(cli, ["auth", "remove", "nonexistent"])
     assert result4.exit_code == 1
     assert result4.output == "Error: No such URL or alias\n"
 
-    # Remove should work with the correct URL
-    result5 = runner.invoke(cli, ["auth", "remove", "https://example.com"])
+    # Remove should work with the correct key
+    result5 = runner.invoke(cli, ["auth", "remove", "prod"])
     assert result5.exit_code == 0
     assert result5.output == ""
 
     # Check the tokens file
     auth_file = pathlib.Path(tmpdir) / "auth.json"
     assert json.loads(auth_file.read_text()) == {}
+
+
+# -- login command (OAuth device flow) --
+
+DEVICE_RESPONSE = {
+    "device_code": "devcode123",
+    "user_code": "ABCD-EFGH",
+    "verification_uri": "https://example.com/-/oauth/device/verify",
+    "expires_in": 900,
+    "interval": 0,
+}
+
+TOKEN_SUCCESS = {
+    "access_token": "dstok_abc123",
+    "token_type": "bearer",
+    "expires_in": 3600,
+}
+
+
+def test_login_with_url(httpx_mock, mocker, tmpdir):
+    mocker.patch("dclient.cli.get_config_dir", return_value=pathlib.Path(tmpdir))
+    mocker.patch("dclient.cli.time.sleep")
+    httpx_mock.add_response(json=DEVICE_RESPONSE, status_code=200)
+    httpx_mock.add_response(json=TOKEN_SUCCESS, status_code=200)
+    httpx_mock.add_response(json=[{"name": "data"}], status_code=200)
+    runner = CliRunner()
+    result = runner.invoke(cli, ["login", "https://example.com/"])
+    assert result.exit_code == 0
+    assert "ABCD-EFGH" in result.output
+    assert "Login successful" in result.output
+    # Token should be saved
+    auth_file = pathlib.Path(tmpdir) / "auth.json"
+    auths = json.loads(auth_file.read_text())
+    assert auths["https://example.com/"] == "dstok_abc123"
+
+
+def test_login_adds_trailing_slash(httpx_mock, mocker, tmpdir):
+    mocker.patch("dclient.cli.get_config_dir", return_value=pathlib.Path(tmpdir))
+    mocker.patch("dclient.cli.time.sleep")
+    httpx_mock.add_response(json=DEVICE_RESPONSE, status_code=200)
+    httpx_mock.add_response(json=TOKEN_SUCCESS, status_code=200)
+    httpx_mock.add_response(json=[{"name": "data"}], status_code=200)
+    runner = CliRunner()
+    result = runner.invoke(cli, ["login", "https://example.com"])
+    assert result.exit_code == 0
+    # Check that the device request went to the right URL
+    requests = httpx_mock.get_requests()
+    assert str(requests[0].url) == "https://example.com/-/oauth/device"
+
+
+def test_login_with_alias(httpx_mock, mocker, tmpdir):
+    mocker.patch("dclient.cli.get_config_dir", return_value=pathlib.Path(tmpdir))
+    mocker.patch("dclient.cli.time.sleep")
+    # Set up an alias first
+    config_file = pathlib.Path(tmpdir) / "config.json"
+    config_file.write_text(
+        json.dumps(
+            {
+                "default_instance": None,
+                "instances": {
+                    "prod": {
+                        "url": "https://prod.example.com",
+                        "default_database": None,
+                    }
+                },
+            }
+        )
+    )
+    httpx_mock.add_response(json=DEVICE_RESPONSE, status_code=200)
+    httpx_mock.add_response(json=TOKEN_SUCCESS, status_code=200)
+    httpx_mock.add_response(json=[{"name": "data"}], status_code=200)
+    runner = CliRunner()
+    result = runner.invoke(cli, ["login", "prod"])
+    assert result.exit_code == 0
+    assert "Login successful" in result.output
+    # Token should be saved by alias name
+    auth_file = pathlib.Path(tmpdir) / "auth.json"
+    auths = json.loads(auth_file.read_text())
+    assert auths["prod"] == "dstok_abc123"
+
+
+def test_login_interactive_prompt(httpx_mock, mocker, tmpdir):
+    mocker.patch("dclient.cli.get_config_dir", return_value=pathlib.Path(tmpdir))
+    mocker.patch("dclient.cli.time.sleep")
+    httpx_mock.add_response(json=DEVICE_RESPONSE, status_code=200)
+    httpx_mock.add_response(json=TOKEN_SUCCESS, status_code=200)
+    httpx_mock.add_response(json=[{"name": "data"}], status_code=200)
+    runner = CliRunner()
+    result = runner.invoke(cli, ["login"], input="https://example.com/\n")
+    assert result.exit_code == 0
+    assert "Instance URL or alias" in result.output
+    assert "Login successful" in result.output
+
+
+def test_login_access_denied(httpx_mock, mocker, tmpdir):
+    mocker.patch("dclient.cli.get_config_dir", return_value=pathlib.Path(tmpdir))
+    mocker.patch("dclient.cli.time.sleep")
+    httpx_mock.add_response(json=DEVICE_RESPONSE, status_code=200)
+    httpx_mock.add_response(json={"error": "access_denied"}, status_code=400)
+    runner = CliRunner()
+    result = runner.invoke(cli, ["login", "https://example.com/"])
+    assert result.exit_code == 1
+    assert "Authorization denied" in result.output
+
+
+def test_login_expired_token(httpx_mock, mocker, tmpdir):
+    mocker.patch("dclient.cli.get_config_dir", return_value=pathlib.Path(tmpdir))
+    mocker.patch("dclient.cli.time.sleep")
+    httpx_mock.add_response(json=DEVICE_RESPONSE, status_code=200)
+    httpx_mock.add_response(json={"error": "expired_token"}, status_code=400)
+    runner = CliRunner()
+    result = runner.invoke(cli, ["login", "https://example.com/"])
+    assert result.exit_code == 1
+    assert "expired" in result.output
+
+
+def test_login_pending_then_success(httpx_mock, mocker, tmpdir):
+    mocker.patch("dclient.cli.get_config_dir", return_value=pathlib.Path(tmpdir))
+    mocker.patch("dclient.cli.time.sleep")
+    httpx_mock.add_response(json=DEVICE_RESPONSE, status_code=200)
+    # First poll: pending
+    httpx_mock.add_response(json={"error": "authorization_pending"}, status_code=400)
+    # Second poll: success
+    httpx_mock.add_response(json=TOKEN_SUCCESS, status_code=200)
+    httpx_mock.add_response(json=[{"name": "data"}], status_code=200)
+    runner = CliRunner()
+    result = runner.invoke(cli, ["login", "https://example.com/"])
+    assert result.exit_code == 0
+    assert "Login successful" in result.output
+    auth_file = pathlib.Path(tmpdir) / "auth.json"
+    auths = json.loads(auth_file.read_text())
+    assert auths["https://example.com/"] == "dstok_abc123"
+
+
+def test_login_device_endpoint_error(httpx_mock, mocker, tmpdir):
+    mocker.patch("dclient.cli.get_config_dir", return_value=pathlib.Path(tmpdir))
+    httpx_mock.add_response(status_code=403)
+    runner = CliRunner()
+    result = runner.invoke(cli, ["login", "https://example.com/"])
+    assert result.exit_code == 1
+    assert "Failed to start login flow" in result.output
+
+
+# -- login sets defaults --
+
+
+@pytest.mark.parametrize(
+    "databases_response,expected_db",
+    [
+        ([{"name": "mydata", "is_mutable": True}], "mydata"),
+        (
+            [
+                {"name": "fixtures", "is_mutable": False},
+                {"name": "data", "is_mutable": True},
+                {"name": "extra", "is_mutable": True},
+            ],
+            "data",
+        ),
+        (
+            [
+                {"name": "alpha", "is_mutable": True},
+                {"name": "beta", "is_mutable": True},
+                {"name": "gamma", "is_mutable": False},
+            ],
+            "alpha",
+        ),
+    ],
+    ids=["single_db", "prefers_data", "first_when_no_data"],
+)
+def test_login_sets_defaults(
+    httpx_mock, mocker, tmpdir, databases_response, expected_db
+):
+    mocker.patch("dclient.cli.get_config_dir", return_value=pathlib.Path(tmpdir))
+    mocker.patch("dclient.cli.time.sleep")
+    httpx_mock.add_response(json=DEVICE_RESPONSE, status_code=200)
+    httpx_mock.add_response(json=TOKEN_SUCCESS, status_code=200)
+    httpx_mock.add_response(json=databases_response, status_code=200)
+    runner = CliRunner()
+    result = runner.invoke(cli, ["login", "https://example.com/"])
+    assert result.exit_code == 0, result.output
+    config = json.loads((pathlib.Path(tmpdir) / "config.json").read_text())
+    assert config["default_instance"] == "https://example.com/"
+    assert (
+        config["instances"]["https://example.com/"]["default_database"] == expected_db
+    )
+
+
+def test_login_does_not_override_existing_defaults(httpx_mock, mocker, tmpdir):
+    """When defaults are already configured, login should not change them."""
+    mocker.patch("dclient.cli.get_config_dir", return_value=pathlib.Path(tmpdir))
+    mocker.patch("dclient.cli.time.sleep")
+    config_file = pathlib.Path(tmpdir) / "config.json"
+    config_file.write_text(
+        json.dumps(
+            {
+                "default_instance": "prod",
+                "instances": {
+                    "prod": {
+                        "url": "https://prod.example.com",
+                        "default_database": "main",
+                    }
+                },
+            }
+        )
+    )
+    httpx_mock.add_response(json=DEVICE_RESPONSE, status_code=200)
+    httpx_mock.add_response(json=TOKEN_SUCCESS, status_code=200)
+    runner = CliRunner()
+    result = runner.invoke(cli, ["login", "https://other.example.com/"])
+    assert result.exit_code == 0, result.output
+    config = json.loads(config_file.read_text())
+    assert config["default_instance"] == "prod"
+
+
+def test_login_with_alias_sets_defaults(httpx_mock, mocker, tmpdir):
+    """When logging in with an alias that has no default_database, set it."""
+    mocker.patch("dclient.cli.get_config_dir", return_value=pathlib.Path(tmpdir))
+    mocker.patch("dclient.cli.time.sleep")
+    config_file = pathlib.Path(tmpdir) / "config.json"
+    config_file.write_text(
+        json.dumps(
+            {
+                "default_instance": None,
+                "instances": {
+                    "prod": {
+                        "url": "https://prod.example.com",
+                        "default_database": None,
+                    }
+                },
+            }
+        )
+    )
+    httpx_mock.add_response(json=DEVICE_RESPONSE, status_code=200)
+    httpx_mock.add_response(json=TOKEN_SUCCESS, status_code=200)
+    httpx_mock.add_response(
+        json=[
+            {"name": "fixtures", "is_mutable": False},
+            {"name": "data", "is_mutable": True},
+            {"name": "extra", "is_mutable": True},
+        ],
+        status_code=200,
+    )
+    runner = CliRunner()
+    result = runner.invoke(cli, ["login", "prod"])
+    assert result.exit_code == 0, result.output
+    config = json.loads(config_file.read_text())
+    assert config["default_instance"] == "prod"
+    assert config["instances"]["prod"]["default_database"] == "data"
+
+
+def test_login_read_all(httpx_mock, mocker, tmpdir):
+    mocker.patch("dclient.cli.get_config_dir", return_value=pathlib.Path(tmpdir))
+    mocker.patch("dclient.cli.time.sleep")
+    httpx_mock.add_response(json=DEVICE_RESPONSE, status_code=200)
+    httpx_mock.add_response(json=TOKEN_SUCCESS, status_code=200)
+    httpx_mock.add_response(json=[{"name": "data"}], status_code=200)
+    runner = CliRunner()
+    result = runner.invoke(cli, ["login", "https://example.com/", "--read-all"])
+    assert result.exit_code == 0, result.output
+    from urllib.parse import parse_qs
+
+    request = httpx_mock.get_requests()[0]
+    body = parse_qs(request.content.decode())
+    scope = json.loads(body["scope"][0])
+    assert scope == [
+        ["view-instance"],
+        ["view-table"],
+        ["view-database"],
+        ["view-query"],
+        ["execute-sql"],
+    ]
+
+
+def test_login_write_all(httpx_mock, mocker, tmpdir):
+    mocker.patch("dclient.cli.get_config_dir", return_value=pathlib.Path(tmpdir))
+    mocker.patch("dclient.cli.time.sleep")
+    httpx_mock.add_response(json=DEVICE_RESPONSE, status_code=200)
+    httpx_mock.add_response(json=TOKEN_SUCCESS, status_code=200)
+    httpx_mock.add_response(json=[{"name": "data"}], status_code=200)
+    runner = CliRunner()
+    result = runner.invoke(cli, ["login", "https://example.com/", "--write-all"])
+    assert result.exit_code == 0, result.output
+    from urllib.parse import parse_qs
+
+    request = httpx_mock.get_requests()[0]
+    body = parse_qs(request.content.decode())
+    scope = json.loads(body["scope"][0])
+    assert scope == [
+        ["view-instance"],
+        ["view-table"],
+        ["view-database"],
+        ["view-query"],
+        ["execute-sql"],
+        ["insert-row"],
+        ["delete-row"],
+        ["update-row"],
+        ["create-table"],
+        ["alter-table"],
+        ["drop-table"],
+    ]
+
+
+def test_login_read_database(httpx_mock, mocker, tmpdir):
+    mocker.patch("dclient.cli.get_config_dir", return_value=pathlib.Path(tmpdir))
+    mocker.patch("dclient.cli.time.sleep")
+    httpx_mock.add_response(json=DEVICE_RESPONSE, status_code=200)
+    httpx_mock.add_response(json=TOKEN_SUCCESS, status_code=200)
+    httpx_mock.add_response(json=[{"name": "data"}], status_code=200)
+    runner = CliRunner()
+    result = runner.invoke(cli, ["login", "https://example.com/", "--read", "db1"])
+    assert result.exit_code == 0, result.output
+    from urllib.parse import parse_qs
+
+    request = httpx_mock.get_requests()[0]
+    body = parse_qs(request.content.decode())
+    scope = json.loads(body["scope"][0])
+    assert scope == [
+        ["view-instance", "db1"],
+        ["view-table", "db1"],
+        ["view-database", "db1"],
+        ["view-query", "db1"],
+        ["execute-sql", "db1"],
+    ]
+
+
+def test_login_write_table(httpx_mock, mocker, tmpdir):
+    mocker.patch("dclient.cli.get_config_dir", return_value=pathlib.Path(tmpdir))
+    mocker.patch("dclient.cli.time.sleep")
+    httpx_mock.add_response(json=DEVICE_RESPONSE, status_code=200)
+    httpx_mock.add_response(json=TOKEN_SUCCESS, status_code=200)
+    httpx_mock.add_response(json=[{"name": "data"}], status_code=200)
+    runner = CliRunner()
+    result = runner.invoke(
+        cli, ["login", "https://example.com/", "--write", "db3/submissions"]
+    )
+    assert result.exit_code == 0, result.output
+    from urllib.parse import parse_qs
+
+    request = httpx_mock.get_requests()[0]
+    body = parse_qs(request.content.decode())
+    scope = json.loads(body["scope"][0])
+    assert scope == [
+        ["view-instance", "db3", "submissions"],
+        ["view-table", "db3", "submissions"],
+        ["view-database", "db3", "submissions"],
+        ["view-query", "db3", "submissions"],
+        ["execute-sql", "db3", "submissions"],
+        ["insert-row", "db3", "submissions"],
+        ["delete-row", "db3", "submissions"],
+        ["update-row", "db3", "submissions"],
+        ["create-table", "db3", "submissions"],
+        ["alter-table", "db3", "submissions"],
+        ["drop-table", "db3", "submissions"],
+    ]
+
+
+def test_login_mixed_read_write(httpx_mock, mocker, tmpdir):
+    mocker.patch("dclient.cli.get_config_dir", return_value=pathlib.Path(tmpdir))
+    mocker.patch("dclient.cli.time.sleep")
+    httpx_mock.add_response(json=DEVICE_RESPONSE, status_code=200)
+    httpx_mock.add_response(json=TOKEN_SUCCESS, status_code=200)
+    httpx_mock.add_response(json=[{"name": "data"}], status_code=200)
+    runner = CliRunner()
+    result = runner.invoke(
+        cli,
+        ["login", "https://example.com/", "--read", "db1", "--write", "db3/dogs"],
+    )
+    assert result.exit_code == 0, result.output
+    from urllib.parse import parse_qs
+
+    request = httpx_mock.get_requests()[0]
+    body = parse_qs(request.content.decode())
+    scope = json.loads(body["scope"][0])
+    assert scope == [
+        ["view-instance", "db1"],
+        ["view-table", "db1"],
+        ["view-database", "db1"],
+        ["view-query", "db1"],
+        ["execute-sql", "db1"],
+        ["view-instance", "db3", "dogs"],
+        ["view-table", "db3", "dogs"],
+        ["view-database", "db3", "dogs"],
+        ["view-query", "db3", "dogs"],
+        ["execute-sql", "db3", "dogs"],
+        ["insert-row", "db3", "dogs"],
+        ["delete-row", "db3", "dogs"],
+        ["update-row", "db3", "dogs"],
+        ["create-table", "db3", "dogs"],
+        ["alter-table", "db3", "dogs"],
+        ["drop-table", "db3", "dogs"],
+    ]
+
+
+def test_login_scope_combined_with_shortcuts(httpx_mock, mocker, tmpdir):
+    mocker.patch("dclient.cli.get_config_dir", return_value=pathlib.Path(tmpdir))
+    mocker.patch("dclient.cli.time.sleep")
+    httpx_mock.add_response(json=DEVICE_RESPONSE, status_code=200)
+    httpx_mock.add_response(json=TOKEN_SUCCESS, status_code=200)
+    httpx_mock.add_response(json=[{"name": "data"}], status_code=200)
+    runner = CliRunner()
+    result = runner.invoke(
+        cli,
+        [
+            "login",
+            "https://example.com/",
+            "--scope",
+            '[["view-instance"]]',
+            "--write",
+            "db1/dogs",
+        ],
+    )
+    assert result.exit_code == 0, result.output
+    from urllib.parse import parse_qs
+
+    request = httpx_mock.get_requests()[0]
+    body = parse_qs(request.content.decode())
+    scope = json.loads(body["scope"][0])
+    assert scope == [
+        ["view-instance"],
+        ["view-instance", "db1", "dogs"],
+        ["view-table", "db1", "dogs"],
+        ["view-database", "db1", "dogs"],
+        ["view-query", "db1", "dogs"],
+        ["execute-sql", "db1", "dogs"],
+        ["insert-row", "db1", "dogs"],
+        ["delete-row", "db1", "dogs"],
+        ["update-row", "db1", "dogs"],
+        ["create-table", "db1", "dogs"],
+        ["alter-table", "db1", "dogs"],
+        ["drop-table", "db1", "dogs"],
+    ]
+
+
+def test_login_no_scope_sends_no_scope(httpx_mock, mocker, tmpdir):
+    """Without any scope options, no scope field should be sent."""
+    mocker.patch("dclient.cli.get_config_dir", return_value=pathlib.Path(tmpdir))
+    mocker.patch("dclient.cli.time.sleep")
+    httpx_mock.add_response(json=DEVICE_RESPONSE, status_code=200)
+    httpx_mock.add_response(json=TOKEN_SUCCESS, status_code=200)
+    httpx_mock.add_response(json=[{"name": "data"}], status_code=200)
+    runner = CliRunner()
+    result = runner.invoke(cli, ["login", "https://example.com/"])
+    assert result.exit_code == 0, result.output
+    request = httpx_mock.get_requests()[0]
+    assert request.content == b""
+
+
+def test_login_token_only(httpx_mock, mocker, tmpdir):
+    """--token-only prints the token to stdout and does not save it."""
+    mocker.patch("dclient.cli.get_config_dir", return_value=pathlib.Path(tmpdir))
+    mocker.patch("dclient.cli.time.sleep")
+    httpx_mock.add_response(json=DEVICE_RESPONSE, status_code=200)
+    httpx_mock.add_response(json=TOKEN_SUCCESS, status_code=200)
+    runner = CliRunner()
+    result = runner.invoke(
+        cli, ["login", "https://example.com/", "--token-only", "--read", "foo/bar"]
+    )
+    assert result.exit_code == 0, result.output
+    # Last line of output should be the raw token
+    assert result.output.strip().endswith("dstok_abc123")
+    # Should NOT have "Login successful" message
+    assert "Login successful" not in result.output
+    # auth.json should not exist
+    auth_file = pathlib.Path(tmpdir) / "auth.json"
+    assert not auth_file.exists()
+    # config.json should not exist (no defaults set)
+    config_file = pathlib.Path(tmpdir) / "config.json"
+    assert not config_file.exists()
+
+
+def test_login_databases_error_still_succeeds(httpx_mock, mocker, tmpdir):
+    """If the databases check fails, login should still succeed."""
+    mocker.patch("dclient.cli.get_config_dir", return_value=pathlib.Path(tmpdir))
+    mocker.patch("dclient.cli.time.sleep")
+    httpx_mock.add_response(json=DEVICE_RESPONSE, status_code=200)
+    httpx_mock.add_response(json=TOKEN_SUCCESS, status_code=200)
+    httpx_mock.add_response(status_code=500)
+    runner = CliRunner()
+    result = runner.invoke(cli, ["login", "https://example.com/"])
+    assert result.exit_code == 0, result.output
+    assert "Login successful" in result.output

tests/test_commands_v2.py

@@ -0,0 +1,585 @@
+"""Tests for v2 commands: databases, tables, plugins, schema, default_query, upsert."""
+
+from click.testing import CliRunner
+from dclient.cli import cli
+import json
+import pathlib
+
+# -- databases command --
+
+
+def test_databases_json(httpx_mock, mocker, tmpdir):
+    mocker.patch("dclient.cli.get_config_dir", return_value=pathlib.Path(tmpdir))
+    httpx_mock.add_response(
+        json={
+            "databases": [
+                {"name": "main", "tables_count": 12},
+                {"name": "extra", "tables_count": 3},
+            ]
+        },
+        status_code=200,
+    )
+    runner = CliRunner()
+    result = runner.invoke(cli, ["databases", "-i", "https://example.com", "--json"])
+    assert result.exit_code == 0
+    data = json.loads(result.output)
+    assert len(data) == 2
+    assert data[0]["name"] == "main"
+
+
+def test_databases_plain(httpx_mock, mocker, tmpdir):
+    mocker.patch("dclient.cli.get_config_dir", return_value=pathlib.Path(tmpdir))
+    httpx_mock.add_response(
+        json={
+            "databases": [
+                {"name": "main", "tables_count": 12},
+                {"name": "extra", "tables_count": 3},
+            ]
+        },
+        status_code=200,
+    )
+    runner = CliRunner()
+    result = runner.invoke(cli, ["databases", "-i", "https://example.com"])
+    assert result.exit_code == 0
+    assert "main\n" in result.output
+    assert "extra\n" in result.output
+
+
+def test_databases_url(httpx_mock, mocker, tmpdir):
+    """databases command hits /.json on the instance."""
+    mocker.patch("dclient.cli.get_config_dir", return_value=pathlib.Path(tmpdir))
+    httpx_mock.add_response(
+        json={"databases": [{"name": "db1"}]},
+        status_code=200,
+    )
+    runner = CliRunner()
+    result = runner.invoke(cli, ["databases", "-i", "https://example.com"])
+    assert result.exit_code == 0
+    request = httpx_mock.get_request()
+    assert request.url.path == "/.json"
+
+
+# -- tables command --
+
+
+def test_tables_json(httpx_mock, mocker, tmpdir):
+    mocker.patch("dclient.cli.get_config_dir", return_value=pathlib.Path(tmpdir))
+    httpx_mock.add_response(
+        json={
+            "tables": [
+                {"name": "facetable", "count": 15, "hidden": False},
+                {"name": "facet_cities", "count": 4, "hidden": False},
+            ],
+            "views": [],
+        },
+        status_code=200,
+    )
+    runner = CliRunner()
+    result = runner.invoke(
+        cli,
+        ["tables", "-i", "https://example.com", "-d", "fixtures", "--json"],
+    )
+    assert result.exit_code == 0
+    data = json.loads(result.output)
+    assert len(data) == 2
+
+
+def test_tables_plain(httpx_mock, mocker, tmpdir):
+    mocker.patch("dclient.cli.get_config_dir", return_value=pathlib.Path(tmpdir))
+    httpx_mock.add_response(
+        json={
+            "tables": [
+                {"name": "facetable", "count": 15, "hidden": False},
+            ],
+            "views": [],
+        },
+        status_code=200,
+    )
+    runner = CliRunner()
+    result = runner.invoke(
+        cli, ["tables", "-i", "https://example.com", "-d", "fixtures"]
+    )
+    assert result.exit_code == 0
+    assert "facetable" in result.output
+    assert "15 rows" in result.output
+
+
+def test_tables_url(httpx_mock, mocker, tmpdir):
+    """tables command hits /<database>.json."""
+    mocker.patch("dclient.cli.get_config_dir", return_value=pathlib.Path(tmpdir))
+    httpx_mock.add_response(
+        json={"tables": [], "views": []},
+        status_code=200,
+    )
+    runner = CliRunner()
+    result = runner.invoke(
+        cli, ["tables", "-i", "https://example.com", "-d", "fixtures"]
+    )
+    assert result.exit_code == 0
+    request = httpx_mock.get_request()
+    assert request.url.path == "/fixtures.json"
+
+
+def test_tables_with_views(httpx_mock, mocker, tmpdir):
+    mocker.patch("dclient.cli.get_config_dir", return_value=pathlib.Path(tmpdir))
+    httpx_mock.add_response(
+        json={
+            "tables": [{"name": "t1", "count": 5, "hidden": False}],
+            "views": [{"name": "v1"}],
+        },
+        status_code=200,
+    )
+    runner = CliRunner()
+    result = runner.invoke(
+        cli, ["tables", "-i", "https://example.com", "-d", "db", "--views"]
+    )
+    assert result.exit_code == 0
+    assert "t1" in result.output
+    assert "v1" in result.output
+
+
+def test_tables_views_only(httpx_mock, mocker, tmpdir):
+    mocker.patch("dclient.cli.get_config_dir", return_value=pathlib.Path(tmpdir))
+    httpx_mock.add_response(
+        json={
+            "tables": [{"name": "t1", "count": 5, "hidden": False}],
+            "views": [{"name": "v1"}],
+        },
+        status_code=200,
+    )
+    runner = CliRunner()
+    result = runner.invoke(
+        cli,
+        ["tables", "-i", "https://example.com", "-d", "db", "--views-only"],
+    )
+    assert result.exit_code == 0
+    assert "t1" not in result.output
+    assert "v1" in result.output
+
+
+def test_tables_hidden(httpx_mock, mocker, tmpdir):
+    mocker.patch("dclient.cli.get_config_dir", return_value=pathlib.Path(tmpdir))
+    httpx_mock.add_response(
+        json={
+            "tables": [
+                {"name": "visible", "count": 5, "hidden": False},
+                {"name": "hidden_t", "count": 2, "hidden": True},
+            ],
+            "views": [],
+        },
+        status_code=200,
+    )
+    runner = CliRunner()
+    # Without --hidden
+    result = runner.invoke(cli, ["tables", "-i", "https://example.com", "-d", "db"])
+    assert "visible" in result.output
+    assert "hidden_t" not in result.output
+
+    # With --hidden
+    httpx_mock.add_response(
+        json={
+            "tables": [
+                {"name": "visible", "count": 5, "hidden": False},
+                {"name": "hidden_t", "count": 2, "hidden": True},
+            ],
+            "views": [],
+        },
+        status_code=200,
+    )
+    result = runner.invoke(
+        cli,
+        ["tables", "-i", "https://example.com", "-d", "db", "--hidden"],
+    )
+    assert "visible" in result.output
+    assert "hidden_t" in result.output
+
+
+# -- plugins command --
+
+
+def test_plugins_json(httpx_mock, mocker, tmpdir):
+    mocker.patch("dclient.cli.get_config_dir", return_value=pathlib.Path(tmpdir))
+    httpx_mock.add_response(
+        json=[
+            {"name": "datasette-files", "version": "0.3.1"},
+            {"name": "datasette-auth-tokens", "version": "0.4"},
+        ],
+        status_code=200,
+    )
+    runner = CliRunner()
+    result = runner.invoke(cli, ["plugins", "-i", "https://example.com", "--json"])
+    assert result.exit_code == 0
+    data = json.loads(result.output)
+    assert len(data) == 2
+
+
+def test_plugins_plain(httpx_mock, mocker, tmpdir):
+    mocker.patch("dclient.cli.get_config_dir", return_value=pathlib.Path(tmpdir))
+    httpx_mock.add_response(
+        json=[
+            {"name": "datasette-files", "version": "0.3.1"},
+            {"name": "datasette-auth-tokens", "version": "0.4"},
+        ],
+        status_code=200,
+    )
+    runner = CliRunner()
+    result = runner.invoke(cli, ["plugins", "-i", "https://example.com"])
+    assert result.exit_code == 0
+    assert "datasette-files\n" in result.output
+    assert "datasette-auth-tokens\n" in result.output
+
+
+def test_plugins_url(httpx_mock, mocker, tmpdir):
+    """plugins command hits /-/plugins.json."""
+    mocker.patch("dclient.cli.get_config_dir", return_value=pathlib.Path(tmpdir))
+    httpx_mock.add_response(json=[], status_code=200)
+    runner = CliRunner()
+    result = runner.invoke(cli, ["plugins", "-i", "https://example.com"])
+    assert result.exit_code == 0
+    request = httpx_mock.get_request()
+    assert request.url.path == "/-/plugins.json"
+
+
+# -- schema command --
+
+
+def test_schema_all_tables(httpx_mock, mocker, tmpdir):
+    mocker.patch("dclient.cli.get_config_dir", return_value=pathlib.Path(tmpdir))
+    full_schema = (
+        "CREATE TABLE users (id integer primary key, name text);\n"
+        "CREATE VIEW user_count AS SELECT count(*) FROM users;"
+    )
+    httpx_mock.add_response(
+        json={"database": "main", "schema": full_schema},
+        status_code=200,
+    )
+    runner = CliRunner()
+    result = runner.invoke(cli, ["schema", "-i", "https://example.com", "-d", "main"])
+    assert result.exit_code == 0
+    assert "CREATE TABLE users" in result.output
+    assert "CREATE VIEW user_count" in result.output
+    request = httpx_mock.get_request()
+    assert request.url.path == "/main/-/schema.json"
+
+
+def test_schema_specific_table(httpx_mock, mocker, tmpdir):
+    mocker.patch("dclient.cli.get_config_dir", return_value=pathlib.Path(tmpdir))
+    httpx_mock.add_response(
+        json={
+            "database": "main",
+            "table": "users",
+            "schema": "CREATE TABLE users (id integer primary key, name text)",
+        },
+        status_code=200,
+    )
+    runner = CliRunner()
+    result = runner.invoke(
+        cli, ["schema", "users", "-i", "https://example.com", "-d", "main"]
+    )
+    assert result.exit_code == 0
+    assert "CREATE TABLE users" in result.output
+    request = httpx_mock.get_request()
+    assert request.url.path == "/main/users/-/schema.json"
+
+
+def test_schema_json(httpx_mock, mocker, tmpdir):
+    mocker.patch("dclient.cli.get_config_dir", return_value=pathlib.Path(tmpdir))
+    full_schema = "CREATE TABLE users (id integer primary key);"
+    httpx_mock.add_response(
+        json={"database": "main", "schema": full_schema},
+        status_code=200,
+    )
+    runner = CliRunner()
+    result = runner.invoke(
+        cli,
+        ["schema", "-i", "https://example.com", "-d", "main", "--json"],
+    )
+    assert result.exit_code == 0
+    data = json.loads(result.output)
+    assert "schema" in data
+
+
+# -- default_query (bare SQL shortcut) --
+
+
+def test_default_query_with_defaults(httpx_mock, mocker, tmpdir):
+    """Bare SQL uses default instance + default database."""
+    mocker.patch("dclient.cli.get_config_dir", return_value=pathlib.Path(tmpdir))
+    config_file = pathlib.Path(tmpdir) / "config.json"
+    config_file.write_text(
+        json.dumps(
+            {
+                "default_instance": "prod",
+                "instances": {
+                    "prod": {
+                        "url": "https://prod.example.com",
+                        "default_database": "main",
+                    }
+                },
+            }
+        )
+    )
+    httpx_mock.add_response(
+        json={
+            "ok": True,
+            "rows": [{"count(*)": 42}],
+            "columns": ["count(*)"],
+        },
+        status_code=200,
+    )
+    runner = CliRunner()
+    result = runner.invoke(cli, ["select count(*) from users"])
+    assert result.exit_code == 0
+    assert json.loads(result.output) == [{"count(*)": 42}]
+    request = httpx_mock.get_request()
+    assert request.url.host == "prod.example.com"
+    assert request.url.path == "/main.json"
+
+
+def test_default_query_with_database_override(httpx_mock, mocker, tmpdir):
+    """Bare SQL with -d flag overrides the database."""
+    mocker.patch("dclient.cli.get_config_dir", return_value=pathlib.Path(tmpdir))
+    config_file = pathlib.Path(tmpdir) / "config.json"
+    config_file.write_text(
+        json.dumps(
+            {
+                "default_instance": "prod",
+                "instances": {
+                    "prod": {
+                        "url": "https://prod.example.com",
+                        "default_database": "main",
+                    }
+                },
+            }
+        )
+    )
+    httpx_mock.add_response(
+        json={
+            "ok": True,
+            "rows": [{"count(*)": 100}],
+            "columns": ["count(*)"],
+        },
+        status_code=200,
+    )
+    runner = CliRunner()
+    result = runner.invoke(cli, ["select count(*) from events", "-d", "analytics"])
+    assert result.exit_code == 0
+    request = httpx_mock.get_request()
+    assert request.url.path == "/analytics.json"
+
+
+def test_default_query_with_instance_override(httpx_mock, mocker, tmpdir):
+    """Bare SQL with -i flag overrides the instance."""
+    mocker.patch("dclient.cli.get_config_dir", return_value=pathlib.Path(tmpdir))
+    config_file = pathlib.Path(tmpdir) / "config.json"
+    config_file.write_text(
+        json.dumps(
+            {
+                "default_instance": "prod",
+                "instances": {
+                    "prod": {
+                        "url": "https://prod.example.com",
+                        "default_database": "main",
+                    },
+                    "staging": {
+                        "url": "https://staging.example.com",
+                        "default_database": "main",
+                    },
+                },
+            }
+        )
+    )
+    httpx_mock.add_response(
+        json={
+            "ok": True,
+            "rows": [{"count(*)": 5}],
+            "columns": ["count(*)"],
+        },
+        status_code=200,
+    )
+    runner = CliRunner()
+    result = runner.invoke(cli, ["select count(*) from users", "-i", "staging"])
+    assert result.exit_code == 0
+    request = httpx_mock.get_request()
+    assert request.url.host == "staging.example.com"
+
+
+def test_default_query_no_database_error(mocker, tmpdir):
+    """Bare SQL without a default database gives an error."""
+    mocker.patch("dclient.cli.get_config_dir", return_value=pathlib.Path(tmpdir))
+    config_file = pathlib.Path(tmpdir) / "config.json"
+    config_file.write_text(
+        json.dumps(
+            {
+                "default_instance": "prod",
+                "instances": {
+                    "prod": {
+                        "url": "https://prod.example.com",
+                        "default_database": None,
+                    }
+                },
+            }
+        )
+    )
+    runner = CliRunner()
+    result = runner.invoke(cli, ["select 1"])
+    assert result.exit_code == 1
+    assert "No database specified" in result.output
+
+
+# -- upsert command --
+
+
+def test_upsert_mocked(httpx_mock, tmpdir, mocker):
+    mocker.patch("dclient.cli.get_config_dir", return_value=pathlib.Path(tmpdir))
+    httpx_mock.add_response(
+        json={
+            "ok": True,
+        }
+    )
+    path = pathlib.Path(tmpdir) / "data.csv"
+    path.write_text("a,b,c\n1,2,3\n")
+    runner = CliRunner()
+    result = runner.invoke(
+        cli,
+        [
+            "upsert",
+            "data",
+            "table1",
+            str(path),
+            "--csv",
+            "--token",
+            "x",
+            "-i",
+            "https://datasette.example.com",
+        ],
+        catch_exceptions=False,
+    )
+    assert result.exit_code == 0
+    request = httpx_mock.get_request()
+    assert request.headers["authorization"] == "Bearer x"
+    # Should hit /-/upsert endpoint
+    assert "/table1/-/upsert" in str(request.url)
+    assert json.loads(request.read()) == {"rows": [{"a": 1, "b": 2, "c": 3}]}
+
+
+# -- auth status command --
+
+
+def test_auth_status(httpx_mock, mocker, tmpdir):
+    mocker.patch("dclient.cli.get_config_dir", return_value=pathlib.Path(tmpdir))
+    httpx_mock.add_response(
+        json={"actor": {"id": "root"}},
+        status_code=200,
+    )
+    runner = CliRunner()
+    result = runner.invoke(
+        cli, ["auth", "status", "-i", "https://example.com", "--token", "tok"]
+    )
+    assert result.exit_code == 0
+    data = json.loads(result.output)
+    assert data["actor"]["id"] == "root"
+    request = httpx_mock.get_request()
+    assert request.url.path == "/-/actor.json"
+
+
+# -- actor command --
+
+
+def test_actor_with_instance_flag(httpx_mock, mocker, tmpdir):
+    mocker.patch("dclient.cli.get_config_dir", return_value=pathlib.Path(tmpdir))
+    httpx_mock.add_response(
+        json={"actor": {"id": "root"}},
+        status_code=200,
+    )
+    runner = CliRunner()
+    result = runner.invoke(
+        cli, ["actor", "-i", "https://example.com", "--token", "tok"]
+    )
+    assert result.exit_code == 0
+    data = json.loads(result.output)
+    assert data["actor"]["id"] == "root"
+    request = httpx_mock.get_request()
+    assert request.url.path == "/-/actor.json"
+    assert request.headers["authorization"] == "Bearer tok"
+
+
+# -- get command --
+
+
+def test_get_command(httpx_mock, mocker, tmpdir):
+    mocker.patch("dclient.cli.get_config_dir", return_value=pathlib.Path(tmpdir))
+    httpx_mock.add_response(
+        json={"hello": "world"},
+        status_code=200,
+    )
+    runner = CliRunner()
+    result = runner.invoke(cli, ["get", "/-/plugins.json", "-i", "https://example.com"])
+    assert result.exit_code == 0
+    data = json.loads(result.output)
+    assert data == {"hello": "world"}
+    request = httpx_mock.get_request()
+    assert request.url.path == "/-/plugins.json"
+
+
+# -- instances command --
+
+
+def test_instances_plain(mocker, tmpdir):
+    config_dir = pathlib.Path(tmpdir)
+    mocker.patch("dclient.cli.get_config_dir", return_value=config_dir)
+    (config_dir / "config.json").write_text(
+        json.dumps(
+            {
+                "default_instance": "prod",
+                "instances": {
+                    "prod": {
+                        "url": "https://prod.example.com",
+                        "default_database": "main",
+                    },
+                    "staging": {
+                        "url": "https://staging.example.com",
+                        "default_database": None,
+                    },
+                },
+            }
+        )
+    )
+    runner = CliRunner()
+    result = runner.invoke(cli, ["instances"])
+    assert result.exit_code == 0
+    assert "* prod = https://prod.example.com (db: main)" in result.output
+    assert "  staging = https://staging.example.com" in result.output
+
+
+def test_instances_json(mocker, tmpdir):
+    config_dir = pathlib.Path(tmpdir)
+    mocker.patch("dclient.cli.get_config_dir", return_value=config_dir)
+    (config_dir / "config.json").write_text(
+        json.dumps(
+            {
+                "default_instance": "prod",
+                "instances": {
+                    "prod": {
+                        "url": "https://prod.example.com",
+                        "default_database": "main",
+                    },
+                },
+            }
+        )
+    )
+    runner = CliRunner()
+    result = runner.invoke(cli, ["instances", "--json"])
+    assert result.exit_code == 0
+    data = json.loads(result.output)
+    assert "prod" in data["instances"]
+    assert data["default_instance"] == "prod"
+
+
+def test_instances_empty(mocker, tmpdir):
+    config_dir = pathlib.Path(tmpdir)
+    mocker.patch("dclient.cli.get_config_dir", return_value=config_dir)
+    runner = CliRunner()
+    result = runner.invoke(cli, ["instances"])
+    assert result.exit_code == 0
+    assert result.output.strip() == ""

tests/test_config.py

@@ -0,0 +1,251 @@
+"""Tests for the v2 config system: config.json format, instance resolution, database resolution."""
+
+from dclient.cli import (
+    _load_config,
+    _save_config,
+    _resolve_instance,
+    _resolve_database,
+    _resolve_token,
+)
+import json
+import pathlib
+import pytest
+
+# -- Config loading/saving --
+
+
+def test_load_config_empty(tmpdir):
+    """Loading config when no file exists returns empty defaults."""
+    config_file = pathlib.Path(tmpdir) / "config.json"
+    config = _load_config(config_file)
+    assert config == {"default_instance": None, "instances": {}}
+
+
+def test_load_config_existing(tmpdir):
+    """Loading config reads the JSON file."""
+    config_file = pathlib.Path(tmpdir) / "config.json"
+    config_file.write_text(
+        json.dumps(
+            {
+                "default_instance": "prod",
+                "instances": {
+                    "prod": {
+                        "url": "https://myapp.datasette.cloud",
+                        "default_database": "main",
+                    }
+                },
+            }
+        )
+    )
+    config = _load_config(config_file)
+    assert config["default_instance"] == "prod"
+    assert config["instances"]["prod"]["url"] == "https://myapp.datasette.cloud"
+    assert config["instances"]["prod"]["default_database"] == "main"
+
+
+def test_save_config(tmpdir):
+    """Saving config writes JSON to disk."""
+    config_file = pathlib.Path(tmpdir) / "config.json"
+    config = {
+        "default_instance": "local",
+        "instances": {
+            "local": {
+                "url": "http://localhost:8001",
+                "default_database": None,
+            }
+        },
+    }
+    _save_config(config_file, config)
+    assert json.loads(config_file.read_text()) == config
+
+
+# -- Instance resolution --
+
+
+def test_resolve_instance_from_flag(tmpdir):
+    """An explicit -i flag with a URL is used directly."""
+    config_file = pathlib.Path(tmpdir) / "config.json"
+    url = _resolve_instance("https://example.com", config_file)
+    assert url == "https://example.com"
+
+
+def test_resolve_instance_from_flag_alias(tmpdir):
+    """An explicit -i flag with an alias name resolves via config."""
+    config_file = pathlib.Path(tmpdir) / "config.json"
+    config_file.write_text(
+        json.dumps(
+            {
+                "default_instance": None,
+                "instances": {
+                    "prod": {
+                        "url": "https://myapp.datasette.cloud",
+                        "default_database": None,
+                    }
+                },
+            }
+        )
+    )
+    url = _resolve_instance("prod", config_file)
+    assert url == "https://myapp.datasette.cloud"
+
+
+def test_resolve_instance_from_config_default(tmpdir):
+    """When no -i flag, uses config.default_instance."""
+    config_file = pathlib.Path(tmpdir) / "config.json"
+    config_file.write_text(
+        json.dumps(
+            {
+                "default_instance": "prod",
+                "instances": {
+                    "prod": {
+                        "url": "https://myapp.datasette.cloud",
+                        "default_database": None,
+                    }
+                },
+            }
+        )
+    )
+    url = _resolve_instance(None, config_file)
+    assert url == "https://myapp.datasette.cloud"
+
+
+def test_resolve_instance_from_env(tmpdir, monkeypatch):
+    """When no -i flag and no config default, falls back to DATASETTE_URL."""
+    config_file = pathlib.Path(tmpdir) / "config.json"
+    monkeypatch.setenv("DATASETTE_URL", "https://env.example.com")
+    url = _resolve_instance(None, config_file)
+    assert url == "https://env.example.com"
+
+
+def test_resolve_instance_from_env_strips_trailing_slash(tmpdir, monkeypatch):
+    """DATASETTE_URL trailing slash is stripped."""
+    config_file = pathlib.Path(tmpdir) / "config.json"
+    monkeypatch.setenv("DATASETTE_URL", "https://env.example.com/")
+    url = _resolve_instance(None, config_file)
+    assert url == "https://env.example.com"
+
+
+def test_resolve_instance_error(tmpdir, monkeypatch):
+    """When nothing is configured, raises an error."""
+    config_file = pathlib.Path(tmpdir) / "config.json"
+    monkeypatch.delenv("DATASETTE_URL", raising=False)
+    with pytest.raises(Exception, match="No instance specified"):
+        _resolve_instance(None, config_file)
+
+
+def test_resolve_instance_unknown_alias(tmpdir):
+    """An -i flag with an unknown alias name raises an error."""
+    config_file = pathlib.Path(tmpdir) / "config.json"
+    config_file.write_text(json.dumps({"default_instance": None, "instances": {}}))
+    with pytest.raises(Exception, match="Unknown instance"):
+        _resolve_instance("nonexistent", config_file)
+
+
+# -- Database resolution (optional -d flag mode) --
+
+
+def test_resolve_database_from_flag(tmpdir):
+    """An explicit -d flag is used directly."""
+    config_file = pathlib.Path(tmpdir) / "config.json"
+    db = _resolve_database("mydb", None, config_file)
+    assert db == "mydb"
+
+
+def test_resolve_database_from_instance_default(tmpdir):
+    """When no -d flag, uses the instance's default_database from config."""
+    config_file = pathlib.Path(tmpdir) / "config.json"
+    config_file.write_text(
+        json.dumps(
+            {
+                "default_instance": "prod",
+                "instances": {
+                    "prod": {
+                        "url": "https://myapp.datasette.cloud",
+                        "default_database": "main",
+                    }
+                },
+            }
+        )
+    )
+    db = _resolve_database(None, "prod", config_file)
+    assert db == "main"
+
+
+def test_resolve_database_from_env(tmpdir, monkeypatch):
+    """When no -d flag and no instance default, falls back to DATASETTE_DATABASE."""
+    config_file = pathlib.Path(tmpdir) / "config.json"
+    monkeypatch.setenv("DATASETTE_DATABASE", "envdb")
+    db = _resolve_database(None, None, config_file)
+    assert db == "envdb"
+
+
+def test_resolve_database_error(tmpdir, monkeypatch):
+    """When nothing is configured, raises an error."""
+    config_file = pathlib.Path(tmpdir) / "config.json"
+    monkeypatch.delenv("DATASETTE_DATABASE", raising=False)
+    with pytest.raises(Exception, match="No database specified"):
+        _resolve_database(None, None, config_file)
+
+
+# -- Token resolution --
+
+
+def test_resolve_token_from_flag(tmpdir):
+    """An explicit --token flag is used directly."""
+    auth_file = pathlib.Path(tmpdir) / "auth.json"
+    config_file = pathlib.Path(tmpdir) / "config.json"
+    token = _resolve_token(
+        "explicit-token", "https://example.com", auth_file, config_file
+    )
+    assert token == "explicit-token"
+
+
+def test_resolve_token_from_auth_by_alias(tmpdir):
+    """Auth token looked up by alias name."""
+    auth_file = pathlib.Path(tmpdir) / "auth.json"
+    auth_file.write_text(json.dumps({"prod": "tok123"}))
+    config_file = pathlib.Path(tmpdir) / "config.json"
+    config_file.write_text(
+        json.dumps(
+            {
+                "default_instance": None,
+                "instances": {
+                    "prod": {
+                        "url": "https://myapp.datasette.cloud",
+                        "default_database": None,
+                    }
+                },
+            }
+        )
+    )
+    token = _resolve_token(
+        None, "https://myapp.datasette.cloud", auth_file, config_file
+    )
+    assert token == "tok123"
+
+
+def test_resolve_token_from_auth_by_url_fallback(tmpdir):
+    """Auth token falls back to URL prefix matching when no alias match."""
+    auth_file = pathlib.Path(tmpdir) / "auth.json"
+    auth_file.write_text(json.dumps({"https://example.com": "url-tok"}))
+    config_file = pathlib.Path(tmpdir) / "config.json"
+    token = _resolve_token(None, "https://example.com/db", auth_file, config_file)
+    assert token == "url-tok"
+
+
+def test_resolve_token_from_env(tmpdir, monkeypatch):
+    """Falls back to DATASETTE_TOKEN env var."""
+    auth_file = pathlib.Path(tmpdir) / "auth.json"
+    config_file = pathlib.Path(tmpdir) / "config.json"
+    monkeypatch.setenv("DATASETTE_TOKEN", "env-tok")
+    token = _resolve_token(None, "https://example.com", auth_file, config_file)
+    assert token == "env-tok"
+
+
+def test_resolve_token_none(tmpdir, monkeypatch):
+    """Returns None when nothing is configured."""
+    auth_file = pathlib.Path(tmpdir) / "auth.json"
+    config_file = pathlib.Path(tmpdir) / "config.json"
+    monkeypatch.delenv("DATASETTE_TOKEN", raising=False)
+    token = _resolve_token(None, "https://example.com", auth_file, config_file)
+    assert token is None

tests/test_create_table.py

@@ -0,0 +1,203 @@
+"""Tests for the create-table command."""
+
+from click.testing import CliRunner
+from dclient.cli import cli
+import json
+import pathlib
+
+
+def test_create_table_basic(httpx_mock, mocker, tmpdir):
+    mocker.patch("dclient.cli.get_config_dir", return_value=pathlib.Path(tmpdir))
+    httpx_mock.add_response(
+        json={
+            "ok": True,
+            "database": "mydb",
+            "table": "dogs",
+            "table_url": "http://example.com/mydb/dogs",
+            "table_api_url": "http://example.com/mydb/dogs.json",
+            "schema": "CREATE TABLE [dogs] (\n   [id] INTEGER PRIMARY KEY,\n   [name] TEXT\n)",
+        },
+        status_code=201,
+    )
+    runner = CliRunner()
+    result = runner.invoke(
+        cli,
+        [
+            "create-table",
+            "mydb",
+            "dogs",
+            "--column",
+            "id",
+            "integer",
+            "--column",
+            "name",
+            "text",
+            "--pk",
+            "id",
+            "-i",
+            "https://example.com",
+            "--token",
+            "tok",
+        ],
+    )
+    assert result.exit_code == 0, result.output
+    data = json.loads(result.output)
+    assert data["ok"] is True
+    assert data["table"] == "dogs"
+
+    # Verify request
+    request = httpx_mock.get_request()
+    assert request.url.path == "/mydb/-/create"
+    assert request.headers["authorization"] == "Bearer tok"
+    body = json.loads(request.read())
+    assert body["table"] == "dogs"
+    assert body["columns"] == [
+        {"name": "id", "type": "integer"},
+        {"name": "name", "type": "text"},
+    ]
+    assert body["pk"] == "id"
+
+
+def test_create_table_compound_pk(httpx_mock, mocker, tmpdir):
+    mocker.patch("dclient.cli.get_config_dir", return_value=pathlib.Path(tmpdir))
+    httpx_mock.add_response(json={"ok": True}, status_code=201)
+    runner = CliRunner()
+    result = runner.invoke(
+        cli,
+        [
+            "create-table",
+            "mydb",
+            "events",
+            "--column",
+            "user_id",
+            "integer",
+            "-c",
+            "event_id",
+            "integer",
+            "--column",
+            "data",
+            "text",
+            "--pk",
+            "user_id",
+            "--pk",
+            "event_id",
+            "-i",
+            "https://example.com",
+            "--token",
+            "tok",
+        ],
+    )
+    assert result.exit_code == 0, result.output
+    body = json.loads(httpx_mock.get_request().read())
+    assert body["pks"] == ["user_id", "event_id"]
+    assert "pk" not in body
+
+
+def test_create_table_no_pk(httpx_mock, mocker, tmpdir):
+    mocker.patch("dclient.cli.get_config_dir", return_value=pathlib.Path(tmpdir))
+    httpx_mock.add_response(json={"ok": True}, status_code=201)
+    runner = CliRunner()
+    result = runner.invoke(
+        cli,
+        [
+            "create-table",
+            "mydb",
+            "logs",
+            "--column",
+            "message",
+            "text",
+            "--column",
+            "level",
+            "integer",
+            "-i",
+            "https://example.com",
+            "--token",
+            "tok",
+        ],
+    )
+    assert result.exit_code == 0, result.output
+    body = json.loads(httpx_mock.get_request().read())
+    assert "pk" not in body
+    assert "pks" not in body
+
+
+def test_create_table_no_columns_error(mocker, tmpdir):
+    mocker.patch("dclient.cli.get_config_dir", return_value=pathlib.Path(tmpdir))
+    runner = CliRunner()
+    result = runner.invoke(
+        cli,
+        [
+            "create-table",
+            "mydb",
+            "empty",
+            "-i",
+            "https://example.com",
+            "--token",
+            "tok",
+        ],
+    )
+    assert result.exit_code == 1
+    assert "at least one --column" in result.output
+
+
+def test_create_table_api_error(httpx_mock, mocker, tmpdir):
+    mocker.patch("dclient.cli.get_config_dir", return_value=pathlib.Path(tmpdir))
+    httpx_mock.add_response(
+        json={"ok": False, "errors": ["Table already exists: dogs"]},
+        status_code=400,
+    )
+    runner = CliRunner()
+    result = runner.invoke(
+        cli,
+        [
+            "create-table",
+            "mydb",
+            "dogs",
+            "--column",
+            "id",
+            "integer",
+            "-i",
+            "https://example.com",
+            "--token",
+            "tok",
+        ],
+    )
+    assert result.exit_code == 1
+    assert "Table already exists" in result.output
+
+
+def test_create_table_uses_default_instance(httpx_mock, mocker, tmpdir):
+    mocker.patch("dclient.cli.get_config_dir", return_value=pathlib.Path(tmpdir))
+    config_file = pathlib.Path(tmpdir) / "config.json"
+    config_file.write_text(
+        json.dumps(
+            {
+                "default_instance": "prod",
+                "instances": {
+                    "prod": {
+                        "url": "https://prod.example.com",
+                        "default_database": "main",
+                    }
+                },
+            }
+        )
+    )
+    httpx_mock.add_response(json={"ok": True}, status_code=201)
+    runner = CliRunner()
+    result = runner.invoke(
+        cli,
+        [
+            "create-table",
+            "mydb",
+            "t1",
+            "--column",
+            "id",
+            "integer",
+            "--token",
+            "tok",
+        ],
+    )
+    assert result.exit_code == 0, result.output
+    request = httpx_mock.get_request()
+    assert request.url.host == "prod.example.com"
+    assert request.url.path == "/mydb/-/create"

tests/test_env.py

@@ -0,0 +1,224 @@
+from click.testing import CliRunner
+from dclient.cli import cli
+import json
+import pathlib
+
+QUERY_RESPONSE = {
+    "ok": True,
+    "database": "data",
+    "query_name": None,
+    "rows": [{"id": 1}],
+    "truncated": False,
+    "columns": ["id"],
+    "query": {"sql": "select 1", "params": {}},
+    "error": None,
+    "private": False,
+    "allow_execute_sql": True,
+}
+
+
+# -- DATASETTE_TOKEN tests --
+
+
+def test_datasette_token_used_as_fallback(httpx_mock, mocker, tmpdir):
+    """DATASETTE_TOKEN is used when no --token flag and no auth.json match."""
+    mocker.patch("dclient.cli.get_config_dir", return_value=pathlib.Path(tmpdir))
+    httpx_mock.add_response(json=QUERY_RESPONSE, status_code=200)
+    runner = CliRunner(env={"DATASETTE_TOKEN": "env-token-123"})
+    result = runner.invoke(
+        cli, ["query", "data", "select 1", "-i", "https://example.com"]
+    )
+    assert result.exit_code == 0
+    request = httpx_mock.get_request()
+    assert request.headers["authorization"] == "Bearer env-token-123"
+
+
+def test_token_flag_overrides_datasette_token(httpx_mock, mocker, tmpdir):
+    """--token flag takes priority over DATASETTE_TOKEN."""
+    mocker.patch("dclient.cli.get_config_dir", return_value=pathlib.Path(tmpdir))
+    httpx_mock.add_response(json=QUERY_RESPONSE, status_code=200)
+    runner = CliRunner(env={"DATASETTE_TOKEN": "env-token"})
+    result = runner.invoke(
+        cli,
+        [
+            "query",
+            "data",
+            "select 1",
+            "-i",
+            "https://example.com",
+            "--token",
+            "flag-token",
+        ],
+    )
+    assert result.exit_code == 0
+    request = httpx_mock.get_request()
+    assert request.headers["authorization"] == "Bearer flag-token"
+
+
+def test_auth_json_overrides_datasette_token(httpx_mock, mocker, tmpdir):
+    """Stored auth.json token takes priority over DATASETTE_TOKEN."""
+    mocker.patch("dclient.cli.get_config_dir", return_value=pathlib.Path(tmpdir))
+    auth_file = pathlib.Path(tmpdir) / "auth.json"
+    auth_file.write_text(json.dumps({"https://example.com": "stored-token"}))
+    httpx_mock.add_response(json=QUERY_RESPONSE, status_code=200)
+    runner = CliRunner(env={"DATASETTE_TOKEN": "env-token"})
+    result = runner.invoke(
+        cli, ["query", "data", "select 1", "-i", "https://example.com"]
+    )
+    assert result.exit_code == 0
+    request = httpx_mock.get_request()
+    assert request.headers["authorization"] == "Bearer stored-token"
+
+
+# -- DATASETTE_URL tests --
+
+
+def test_datasette_url_used_as_instance(httpx_mock, mocker, tmpdir):
+    """DATASETTE_URL provides the instance when no -i flag."""
+    mocker.patch("dclient.cli.get_config_dir", return_value=pathlib.Path(tmpdir))
+    httpx_mock.add_response(json=QUERY_RESPONSE, status_code=200)
+    runner = CliRunner(env={"DATASETTE_URL": "https://my-instance.datasette.cloud"})
+    result = runner.invoke(cli, ["query", "data", "select 1"])
+    assert result.exit_code == 0
+    request = httpx_mock.get_request()
+    assert request.url.host == "my-instance.datasette.cloud"
+    assert request.url.path == "/data.json"
+
+
+def test_datasette_url_with_trailing_slash(httpx_mock, mocker, tmpdir):
+    """DATASETTE_URL with trailing slash still works correctly."""
+    mocker.patch("dclient.cli.get_config_dir", return_value=pathlib.Path(tmpdir))
+    httpx_mock.add_response(json=QUERY_RESPONSE, status_code=200)
+    runner = CliRunner(env={"DATASETTE_URL": "https://my-instance.datasette.cloud/"})
+    result = runner.invoke(cli, ["query", "data", "select 1"])
+    assert result.exit_code == 0
+    request = httpx_mock.get_request()
+    assert request.url.path == "/data.json"
+
+
+def test_explicit_instance_ignores_datasette_url(httpx_mock, mocker, tmpdir):
+    """An explicit -i flag is used, ignoring DATASETTE_URL."""
+    mocker.patch("dclient.cli.get_config_dir", return_value=pathlib.Path(tmpdir))
+    httpx_mock.add_response(json=QUERY_RESPONSE, status_code=200)
+    runner = CliRunner(env={"DATASETTE_URL": "https://should-be-ignored.com"})
+    result = runner.invoke(
+        cli, ["query", "db", "select 1", "-i", "https://other.example.com"]
+    )
+    assert result.exit_code == 0
+    request = httpx_mock.get_request()
+    assert request.url.host == "other.example.com"
+    assert request.url.path == "/db.json"
+
+
+def test_alias_takes_priority_over_datasette_url(httpx_mock, mocker, tmpdir):
+    """Alias match via -i takes priority over DATASETTE_URL."""
+    mocker.patch("dclient.cli.get_config_dir", return_value=pathlib.Path(tmpdir))
+    config_file = pathlib.Path(tmpdir) / "config.json"
+    config_file.write_text(
+        json.dumps(
+            {
+                "default_instance": None,
+                "instances": {
+                    "myalias": {
+                        "url": "https://aliased.example.com",
+                        "default_database": None,
+                    }
+                },
+            }
+        )
+    )
+    httpx_mock.add_response(json=QUERY_RESPONSE, status_code=200)
+    runner = CliRunner(env={"DATASETTE_URL": "https://should-be-ignored.com"})
+    result = runner.invoke(cli, ["query", "db", "select 1", "-i", "myalias"])
+    assert result.exit_code == 0
+    request = httpx_mock.get_request()
+    assert request.url.host == "aliased.example.com"
+    assert request.url.path == "/db.json"
+
+
+# -- DATASETTE_URL with other commands --
+
+
+def test_datasette_url_with_insert(httpx_mock, mocker, tmpdir):
+    """DATASETTE_URL works with the insert command."""
+    mocker.patch("dclient.cli.get_config_dir", return_value=pathlib.Path(tmpdir))
+    httpx_mock.add_response(json={"ok": True}, status_code=200)
+    csv_path = pathlib.Path(tmpdir) / "data.csv"
+    csv_path.write_text("id,name\n1,hello\n")
+    runner = CliRunner(
+        env={
+            "DATASETTE_URL": "https://my-instance.datasette.cloud",
+            "DATASETTE_TOKEN": "env-token",
+        }
+    )
+    result = runner.invoke(
+        cli,
+        ["insert", "data", "my_table", str(csv_path), "--csv", "--create"],
+        catch_exceptions=False,
+    )
+    assert result.exit_code == 0
+    request = httpx_mock.get_request()
+    assert request.url.host == "my-instance.datasette.cloud"
+    assert "/-/create" in str(request.url.path)
+    assert request.headers["authorization"] == "Bearer env-token"
+
+
+def test_datasette_url_with_actor(httpx_mock, mocker, tmpdir):
+    """DATASETTE_URL works with the actor command."""
+    mocker.patch("dclient.cli.get_config_dir", return_value=pathlib.Path(tmpdir))
+    httpx_mock.add_response(
+        json={"actor": {"id": "root"}},
+        status_code=200,
+    )
+    runner = CliRunner(
+        env={
+            "DATASETTE_URL": "https://my-instance.datasette.cloud",
+            "DATASETTE_TOKEN": "env-token",
+        }
+    )
+    result = runner.invoke(cli, ["actor"])
+    assert result.exit_code == 0
+    request = httpx_mock.get_request()
+    assert request.url.host == "my-instance.datasette.cloud"
+    assert request.headers["authorization"] == "Bearer env-token"
+
+
+# -- Both together --
+
+
+def test_datasette_url_and_token_together(httpx_mock, mocker, tmpdir):
+    """DATASETTE_URL and DATASETTE_TOKEN work together for a complete config."""
+    mocker.patch("dclient.cli.get_config_dir", return_value=pathlib.Path(tmpdir))
+    httpx_mock.add_response(json=QUERY_RESPONSE, status_code=200)
+    runner = CliRunner(
+        env={
+            "DATASETTE_URL": "https://my-instance.datasette.cloud",
+            "DATASETTE_TOKEN": "env-token-456",
+        }
+    )
+    result = runner.invoke(cli, ["query", "mydb", "select 1"])
+    assert result.exit_code == 0
+    request = httpx_mock.get_request()
+    assert request.url.host == "my-instance.datasette.cloud"
+    assert request.url.path == "/mydb.json"
+    assert request.headers["authorization"] == "Bearer env-token-456"
+
+
+# -- DATASETTE_DATABASE tests --
+
+
+def test_datasette_database_with_default_query(httpx_mock, mocker, tmpdir):
+    """DATASETTE_DATABASE is used by default_query shortcut."""
+    mocker.patch("dclient.cli.get_config_dir", return_value=pathlib.Path(tmpdir))
+    httpx_mock.add_response(json=QUERY_RESPONSE, status_code=200)
+    runner = CliRunner(
+        env={
+            "DATASETTE_URL": "https://my-instance.datasette.cloud",
+            "DATASETTE_DATABASE": "mydb",
+        }
+    )
+    result = runner.invoke(cli, ["select 1"])
+    assert result.exit_code == 0
+    request = httpx_mock.get_request()
+    assert request.url.host == "my-instance.datasette.cloud"
+    assert request.url.path == "/mydb.json"

tests/test_insert.py

@@ -1,5 +1,6 @@
 import asyncio
 from collections import namedtuple
+from concurrent.futures import ThreadPoolExecutor
 from click.testing import CliRunner
 from datasette.app import Datasette
 from dclient.cli import cli
@@ -14,6 +15,9 @@ def assert_all_responses_were_requested() -> bool:
     return False
 
 
+pytestmark = pytest.mark.httpx_mock(assert_all_responses_were_requested=False)
+
+
 @pytest.fixture
 def non_mocked_hosts():
     # This ensures httpx-mock will not affect Datasette's own
@@ -40,12 +44,14 @@ def test_insert_mocked(httpx_mock, tmpdir):
         cli,
         [
             "insert",
-            "https://datasette.example.com/data",
+            "data",
             "table1",
             str(path),
             "--csv",
             "--token",
             "x",
+            "-i",
+            "https://datasette.example.com",
         ],
         catch_exceptions=False,
     )
@@ -229,7 +235,8 @@ def make_format_test(content, arg):
         ),
     ),
 )
-def test_insert_against_datasette(
+@pytest.mark.asyncio
+async def test_insert_against_datasette(
     httpx_mock,
     tmpdir,
     input_data,
@@ -250,33 +257,30 @@ def test_insert_against_datasette(
         }
     )
     db = ds.add_memory_database("data")
-    loop = asyncio.get_event_loop()
 
     # Drop all tables in the database each time, because in-memory
     # databases persist in between test runs
-    drop_all_tables(db, loop)
+    for table in await db.table_names():
+        await db.execute_write("drop table {}".format(table))
 
     if table_exists:
+        await db.execute_write(
+            "create table table1 (a integer primary key, b integer, c integer)"
+        )
+        await db.execute_write(
+            "insert into table1 (a, b, c) values (1, 2, 3), (4, 5, 6)"
+        )
 
-        async def run_table_exists():
-            await db.execute_write(
-                "create table table1 (a integer primary key, b integer, c integer)"
-            )
-            await db.execute_write(
-                "insert into table1 (a, b, c) values (1, 2, 3), (4, 5, 6)"
-            )
-
-        loop.run_until_complete(run_table_exists())
-
-    token = ds.create_token("actor")
+    token = await ds.create_token("actor")
 
     # These are useful with pytest --pdb to see what happened
     datasette_requests = []
     datasette_responses = []
 
     def custom_response(request: httpx.Request):
-        # Need to run this in async loop, because dclient itself uses
-        # sync HTTPX and not async HTTPX
+        # Need to run this in a new event loop, because dclient itself uses
+        # sync HTTPX and not async HTTPX, and the test's event loop is
+        # already running
         async def run():
             datasette_requests.append(request)
             response = await ds.client.request(
@@ -294,9 +298,10 @@ def test_insert_against_datasette(
             datasette_responses.append(response)
             return response
 
-        return loop.run_until_complete(run())
+        with ThreadPoolExecutor(max_workers=1) as pool:
+            return pool.submit(asyncio.run, run()).result()
 
-    httpx_mock.add_callback(custom_response, is_optional=should_error)
+    httpx_mock.add_callback(custom_response)
 
     path = pathlib.Path(tmpdir) / "data.txt"
     if isinstance(input_data, str):
@@ -308,11 +313,13 @@ def test_insert_against_datasette(
         cli,
         [
             "insert",
-            "http://datasette.example.com/data",
+            "data",
             "table1",
             str(path),
             "--token",
             token,
+            "-i",
+            "http://datasette.example.com",
         ]
         + cmd_args,
     )
@@ -323,18 +330,5 @@ def test_insert_against_datasette(
     assert result.output == expected_output
 
     if expected_table_json:
-
-        async def fetch_table():
-            response = await ds.client.get("/data/table1.json?_shape=array")
-            return response
-
-        response = loop.run_until_complete(fetch_table())
+        response = await ds.client.get("/data/table1.json?_shape=array")
         assert response.json() == expected_table_json
-
-
-def drop_all_tables(db, loop):
-    async def run():
-        for table in await db.table_names():
-            await db.execute_write("drop table {}".format(table))
-
-    loop.run_until_complete(run())

tests/test_migration.py

@@ -0,0 +1,104 @@
+"""Tests for v1 → v2 config migration."""
+
+from dclient.cli import _migrate_v1_to_v2
+import json
+import pathlib
+
+
+def test_migration_simple(tmpdir):
+    """Migrate a simple aliases.json with a database-in-URL alias."""
+    config_dir = pathlib.Path(tmpdir)
+    aliases_file = config_dir / "aliases.json"
+    aliases_file.write_text(json.dumps({"content": "https://datasette.io/content"}))
+
+    _migrate_v1_to_v2(config_dir)
+
+    config = json.loads((config_dir / "config.json").read_text())
+    assert config["instances"]["content"]["url"] == "https://datasette.io"
+    assert config["instances"]["content"]["default_database"] == "content"
+    assert config["default_instance"] is None
+
+    # Original should be renamed
+    assert (config_dir / "aliases.json.bak").exists()
+    assert not (config_dir / "aliases.json").exists()
+
+
+def test_migration_no_path_segment(tmpdir):
+    """Migrate an alias that has no database in the URL."""
+    config_dir = pathlib.Path(tmpdir)
+    aliases_file = config_dir / "aliases.json"
+    aliases_file.write_text(json.dumps({"local": "http://localhost:8001"}))
+
+    _migrate_v1_to_v2(config_dir)
+
+    config = json.loads((config_dir / "config.json").read_text())
+    assert config["instances"]["local"]["url"] == "http://localhost:8001"
+    assert config["instances"]["local"]["default_database"] is None
+
+
+def test_migration_with_auth(tmpdir):
+    """Auth keys are migrated from URLs to alias names."""
+    config_dir = pathlib.Path(tmpdir)
+    aliases_file = config_dir / "aliases.json"
+    aliases_file.write_text(json.dumps({"content": "https://datasette.io/content"}))
+    auth_file = config_dir / "auth.json"
+    auth_file.write_text(json.dumps({"https://datasette.io/content": "tok123"}))
+
+    _migrate_v1_to_v2(config_dir)
+
+    new_auths = json.loads((config_dir / "auth.json").read_text())
+    assert "content" in new_auths
+    assert new_auths["content"] == "tok123"
+
+    # Old auth should be backed up
+    assert (config_dir / "auth.json.bak").exists()
+
+
+def test_migration_auth_url_fallback(tmpdir):
+    """Auth entries without matching aliases are kept as URL keys."""
+    config_dir = pathlib.Path(tmpdir)
+    aliases_file = config_dir / "aliases.json"
+    aliases_file.write_text(json.dumps({}))
+    auth_file = config_dir / "auth.json"
+    auth_file.write_text(json.dumps({"https://other.example.com": "tok456"}))
+
+    _migrate_v1_to_v2(config_dir)
+
+    new_auths = json.loads((config_dir / "auth.json").read_text())
+    assert new_auths["https://other.example.com"] == "tok456"
+
+
+def test_migration_skips_if_config_exists(tmpdir):
+    """If config.json already exists, migration is skipped."""
+    config_dir = pathlib.Path(tmpdir)
+    (config_dir / "config.json").write_text(json.dumps({"existing": True}))
+    (config_dir / "aliases.json").write_text(json.dumps({"foo": "https://bar.com"}))
+
+    _migrate_v1_to_v2(config_dir)
+
+    # config.json should be untouched
+    assert json.loads((config_dir / "config.json").read_text()) == {"existing": True}
+    # aliases.json should NOT be renamed
+    assert (config_dir / "aliases.json").exists()
+
+
+def test_migration_skips_if_no_aliases(tmpdir):
+    """If aliases.json doesn't exist, migration is skipped."""
+    config_dir = pathlib.Path(tmpdir)
+
+    _migrate_v1_to_v2(config_dir)
+
+    assert not (config_dir / "config.json").exists()
+
+
+def test_migration_multi_path_segments(tmpdir):
+    """URL with multiple path segments stores URL as-is."""
+    config_dir = pathlib.Path(tmpdir)
+    aliases_file = config_dir / "aliases.json"
+    aliases_file.write_text(json.dumps({"deep": "https://example.com/a/b/c"}))
+
+    _migrate_v1_to_v2(config_dir)
+
+    config = json.loads((config_dir / "config.json").read_text())
+    assert config["instances"]["deep"]["url"] == "https://example.com/a/b/c"
+    assert config["instances"]["deep"]["default_database"] is None

tests/test_new_commands.py

@@ -1,506 +0,0 @@
-"""Tests for new T1 and T2 commands: databases, tables, schema, rows, get,
-upsert, update, delete, drop, create-table."""
-
-import asyncio
-import json
-import pathlib
-
-import pytest
-from click.testing import CliRunner
-from datasette.app import Datasette
-
-from dclient.cli import cli
-
-
-@pytest.fixture
-def ds():
-    """Create a Datasette instance with a memory database and sample data."""
-    datasette = Datasette(
-        config={
-            "permissions": {
-                "create-table": {"id": "*"},
-                "insert-row": {"id": "*"},
-                "update-row": {"id": "*"},
-                "delete-row": {"id": "*"},
-                "drop-table": {"id": "*"},
-                "alter-table": {"id": "*"},
-            }
-        }
-    )
-    db = datasette.add_memory_database("data")
-    loop = asyncio.get_event_loop()
-
-    async def setup():
-        # Drop all tables first
-        for table in await db.table_names():
-            await db.execute_write("drop table [{}]".format(table))
-        await db.execute_write(
-            "create table dogs (id integer primary key, name text, age integer)"
-        )
-        await db.execute_write(
-            "insert into dogs (id, name, age) values (1, 'Cleo', 5), (2, 'Pancakes', 3), (3, 'Fido', 7)"
-        )
-
-    loop.run_until_complete(setup())
-    return datasette, db, loop
-
-
-@pytest.fixture
-def token(ds):
-    datasette, _, _ = ds
-    return datasette.create_token("actor")
-
-
-def _mock_datasette(httpx_mock, ds):
-    """Set up httpx_mock to route requests through the Datasette instance."""
-    datasette, _, loop = ds
-
-    def custom_response(request):
-        async def run():
-            method = request.method
-            url_path = request.url.raw_path.decode()
-            if method == "GET":
-                response = await datasette.client.get(
-                    url_path, headers=request.headers
-                )
-            else:
-                content = request.read()
-                try:
-                    body = json.loads(content) if content else {}
-                except json.JSONDecodeError:
-                    body = {}
-                response = await datasette.client.request(
-                    method, url_path, json=body, headers=request.headers
-                )
-            import httpx as httpx_lib
-
-            return httpx_lib.Response(
-                status_code=response.status_code,
-                headers=response.headers,
-                content=response.content,
-            )
-
-        return loop.run_until_complete(run())
-
-    httpx_mock.add_callback(custom_response, is_optional=True)
-
-
-# --- databases command ---
-
-
-def test_databases(httpx_mock, ds, token):
-    _mock_datasette(httpx_mock, ds)
-    runner = CliRunner()
-    result = runner.invoke(cli, ["databases", "http://localhost/", "--token", token])
-    assert result.exit_code == 0
-    data = json.loads(result.output)
-    names = [d["name"] for d in data]
-    assert "data" in names
-
-
-def test_databases_table_format(httpx_mock, ds, token):
-    _mock_datasette(httpx_mock, ds)
-    runner = CliRunner()
-    result = runner.invoke(
-        cli, ["databases", "http://localhost/", "--token", token, "--table"]
-    )
-    assert result.exit_code == 0
-    assert "name" in result.output
-    assert "data" in result.output
-
-
-# --- tables command ---
-
-
-def test_tables(httpx_mock, ds, token):
-    _mock_datasette(httpx_mock, ds)
-    runner = CliRunner()
-    result = runner.invoke(cli, ["tables", "http://localhost/data", "--token", token])
-    assert result.exit_code == 0
-    data = json.loads(result.output)
-    table_names = [t["name"] for t in data]
-    assert "dogs" in table_names
-
-
-def test_tables_table_format(httpx_mock, ds, token):
-    _mock_datasette(httpx_mock, ds)
-    runner = CliRunner()
-    result = runner.invoke(
-        cli, ["tables", "http://localhost/data", "--token", token, "--table"]
-    )
-    assert result.exit_code == 0
-    assert "dogs" in result.output
-
-
-# --- schema command ---
-
-
-def test_schema(httpx_mock, ds, token):
-    _mock_datasette(httpx_mock, ds)
-    runner = CliRunner()
-    result = runner.invoke(cli, ["schema", "http://localhost/data", "--token", token])
-    assert result.exit_code == 0
-    assert "CREATE TABLE" in result.output
-    assert "dogs" in result.output
-
-
-# --- rows command ---
-
-
-def test_rows_basic(httpx_mock, ds, token):
-    _mock_datasette(httpx_mock, ds)
-    runner = CliRunner()
-    result = runner.invoke(
-        cli, ["rows", "http://localhost/data/dogs", "--token", token]
-    )
-    assert result.exit_code == 0
-    data = json.loads(result.output)
-    assert len(data) == 3
-    names = [r["name"] for r in data]
-    assert "Cleo" in names
-
-
-def test_rows_with_filter(httpx_mock, ds, token):
-    _mock_datasette(httpx_mock, ds)
-    runner = CliRunner()
-    result = runner.invoke(
-        cli,
-        ["rows", "http://localhost/data/dogs", "--token", token, "-w", "age__gt=4"],
-    )
-    assert result.exit_code == 0
-    data = json.loads(result.output)
-    for row in data:
-        assert row["age"] > 4
-
-
-def test_rows_with_sort(httpx_mock, ds, token):
-    _mock_datasette(httpx_mock, ds)
-    runner = CliRunner()
-    result = runner.invoke(
-        cli,
-        ["rows", "http://localhost/data/dogs", "--token", token, "--sort", "name"],
-    )
-    assert result.exit_code == 0
-    data = json.loads(result.output)
-    names = [r["name"] for r in data]
-    assert names == sorted(names)
-
-
-def test_rows_with_sort_desc(httpx_mock, ds, token):
-    _mock_datasette(httpx_mock, ds)
-    runner = CliRunner()
-    result = runner.invoke(
-        cli,
-        [
-            "rows",
-            "http://localhost/data/dogs",
-            "--token",
-            token,
-            "--sort-desc",
-            "age",
-        ],
-    )
-    assert result.exit_code == 0
-    data = json.loads(result.output)
-    ages = [r["age"] for r in data]
-    assert ages == sorted(ages, reverse=True)
-
-
-def test_rows_csv(httpx_mock, ds, token):
-    _mock_datasette(httpx_mock, ds)
-    runner = CliRunner()
-    result = runner.invoke(
-        cli, ["rows", "http://localhost/data/dogs", "--token", token, "--csv"]
-    )
-    assert result.exit_code == 0
-    assert "id,name,age" in result.output
-    assert "Cleo" in result.output
-
-
-def test_rows_nl(httpx_mock, ds, token):
-    _mock_datasette(httpx_mock, ds)
-    runner = CliRunner()
-    result = runner.invoke(
-        cli, ["rows", "http://localhost/data/dogs", "--token", token, "--nl"]
-    )
-    assert result.exit_code == 0
-    lines = [l for l in result.output.strip().split("\n") if l]
-    for line in lines:
-        parsed = json.loads(line)
-        assert "name" in parsed
-
-
-def test_rows_table_format(httpx_mock, ds, token):
-    _mock_datasette(httpx_mock, ds)
-    runner = CliRunner()
-    result = runner.invoke(
-        cli, ["rows", "http://localhost/data/dogs", "--token", token, "--table"]
-    )
-    assert result.exit_code == 0
-    assert "id" in result.output
-    assert "name" in result.output
-    assert "Cleo" in result.output
-    # Should have header separator
-    assert "---" in result.output
-
-
-def test_rows_with_limit(httpx_mock, ds, token):
-    _mock_datasette(httpx_mock, ds)
-    runner = CliRunner()
-    result = runner.invoke(
-        cli,
-        ["rows", "http://localhost/data/dogs", "--token", token, "--limit", "2"],
-    )
-    assert result.exit_code == 0
-    data = json.loads(result.output)
-    assert len(data) == 2
-
-
-def test_rows_with_columns(httpx_mock, ds, token):
-    _mock_datasette(httpx_mock, ds)
-    runner = CliRunner()
-    result = runner.invoke(
-        cli,
-        ["rows", "http://localhost/data/dogs", "--token", token, "--col", "name"],
-    )
-    assert result.exit_code == 0
-    data = json.loads(result.output)
-    for row in data:
-        assert "name" in row
-
-
-# --- get command ---
-
-
-def test_get(httpx_mock, ds, token):
-    _mock_datasette(httpx_mock, ds)
-    runner = CliRunner()
-    result = runner.invoke(
-        cli, ["get", "http://localhost/data/dogs", "1", "--token", token]
-    )
-    assert result.exit_code == 0
-    data = json.loads(result.output)
-    assert data["name"] == "Cleo"
-    assert data["id"] == 1
-
-
-# --- upsert command ---
-
-
-def test_upsert(httpx_mock, ds, token, tmpdir):
-    _mock_datasette(httpx_mock, ds)
-    # Upsert: update Cleo's age and add a new dog
-    data = json.dumps([
-        {"id": 1, "name": "Cleo", "age": 6},
-        {"id": 4, "name": "Muffin", "age": 2},
-    ])
-    path = pathlib.Path(tmpdir) / "upsert.json"
-    path.write_text(data)
-    runner = CliRunner()
-    result = runner.invoke(
-        cli,
-        [
-            "upsert",
-            "http://localhost/data",
-            "dogs",
-            str(path),
-            "--json",
-            "--token",
-            token,
-        ],
-    )
-    assert result.exit_code == 0, result.output
-
-    # Verify
-    datasette, _, loop = ds
-
-    async def check():
-        r = await datasette.client.get("/data/dogs.json?_shape=array")
-        return r.json()
-
-    rows = loop.run_until_complete(check())
-    names = {r["name"]: r["age"] for r in rows}
-    assert names["Cleo"] == 6  # updated
-    assert names["Muffin"] == 2  # inserted
-
-
-# --- update command ---
-
-
-def test_update(httpx_mock, ds, token):
-    _mock_datasette(httpx_mock, ds)
-    runner = CliRunner()
-    result = runner.invoke(
-        cli,
-        [
-            "update",
-            "http://localhost/data/dogs",
-            "1",
-            "name=Rex",
-            "age=10",
-            "--token",
-            token,
-        ],
-    )
-    assert result.exit_code == 0, result.output
-    data = json.loads(result.output)
-    assert data.get("ok") is True
-
-    # Verify the update
-    datasette, _, loop = ds
-
-    async def check():
-        r = await datasette.client.get("/data/dogs/1.json?_shape=objects")
-        return r.json()
-
-    row_data = loop.run_until_complete(check())
-    row = row_data["rows"][0]
-    assert row["name"] == "Rex"
-    assert row["age"] == 10
-
-
-# --- delete command ---
-
-
-def test_delete(httpx_mock, ds, token):
-    _mock_datasette(httpx_mock, ds)
-    runner = CliRunner()
-    result = runner.invoke(
-        cli,
-        [
-            "delete",
-            "http://localhost/data/dogs",
-            "3",
-            "--yes",
-            "--token",
-            token,
-        ],
-    )
-    assert result.exit_code == 0, result.output
-    data = json.loads(result.output)
-    assert data.get("ok") is True
-
-    # Verify deletion
-    datasette, _, loop = ds
-
-    async def check():
-        r = await datasette.client.get("/data/dogs.json?_shape=array")
-        return r.json()
-
-    rows = loop.run_until_complete(check())
-    ids = [r["id"] for r in rows]
-    assert 3 not in ids
-    assert len(rows) == 2
-
-
-# --- drop command ---
-
-
-def test_drop(httpx_mock, ds, token):
-    _mock_datasette(httpx_mock, ds)
-    runner = CliRunner()
-    result = runner.invoke(
-        cli,
-        ["drop", "http://localhost/data/dogs", "--yes", "--token", token],
-    )
-    assert result.exit_code == 0, result.output
-    data = json.loads(result.output)
-    assert data.get("ok") is True
-
-    # Verify table is gone
-    datasette, db, loop = ds
-
-    async def check():
-        return await db.table_names()
-
-    tables = loop.run_until_complete(check())
-    assert "dogs" not in tables
-
-
-# --- create-table command ---
-
-
-def test_create_table(httpx_mock, ds, token):
-    _mock_datasette(httpx_mock, ds)
-    runner = CliRunner()
-    result = runner.invoke(
-        cli,
-        [
-            "create-table",
-            "http://localhost/data",
-            "cats",
-            "--column",
-            "id",
-            "integer",
-            "--column",
-            "name",
-            "text",
-            "--pk",
-            "id",
-            "--token",
-            token,
-        ],
-    )
-    assert result.exit_code == 0, result.output
-    data = json.loads(result.output)
-    assert data.get("ok") is True
-
-    # Verify table exists
-    datasette, db, loop = ds
-
-    async def check():
-        return await db.table_names()
-
-    tables = loop.run_until_complete(check())
-    assert "cats" in tables
-
-
-# --- output format tests for query ---
-
-
-SAMPLE_QUERY_RESPONSE = {
-    "ok": True,
-    "database": "data",
-    "rows": [
-        {"id": 1, "name": "Cleo", "age": 5},
-        {"id": 2, "name": "Pancakes", "age": 3},
-        {"id": 3, "name": "Fido", "age": 7},
-    ],
-    "truncated": False,
-    "columns": ["id", "name", "age"],
-}
-
-
-def test_query_csv(httpx_mock):
-    httpx_mock.add_response(json=SAMPLE_QUERY_RESPONSE, status_code=200)
-    runner = CliRunner()
-    result = runner.invoke(
-        cli, ["query", "https://example.com/data", "select * from dogs", "--csv"]
-    )
-    assert result.exit_code == 0
-    assert "id,name,age" in result.output
-    assert "Cleo" in result.output
-
-
-def test_query_nl(httpx_mock):
-    httpx_mock.add_response(json=SAMPLE_QUERY_RESPONSE, status_code=200)
-    runner = CliRunner()
-    result = runner.invoke(
-        cli, ["query", "https://example.com/data", "select * from dogs", "--nl"]
-    )
-    assert result.exit_code == 0
-    lines = [l for l in result.output.strip().split("\n") if l]
-    assert len(lines) == 3
-    first = json.loads(lines[0])
-    assert first["name"] == "Cleo"
-
-
-def test_query_table_format(httpx_mock):
-    httpx_mock.add_response(json=SAMPLE_QUERY_RESPONSE, status_code=200)
-    runner = CliRunner()
-    result = runner.invoke(
-        cli, ["query", "https://example.com/data", "select * from dogs", "--table"]
-    )
-    assert result.exit_code == 0
-    assert "Cleo" in result.output
-    assert "---" in result.output

tests/test_output_formats.py

@@ -0,0 +1,267 @@
+"""Tests for multiple output formats on the query and default_query commands."""
+
+from click.testing import CliRunner
+from dclient.cli import cli
+import json
+import pathlib
+
+QUERY_RESPONSE = {
+    "ok": True,
+    "database": "fixtures",
+    "query_name": None,
+    "rows": [
+        {"id": 1, "name": "Cleo", "age": 5},
+        {"id": 2, "name": "Pancakes", "age": 3},
+    ],
+    "truncated": False,
+    "columns": ["id", "name", "age"],
+    "query": {"sql": "select * from dogs", "params": {}},
+    "error": None,
+    "private": False,
+    "allow_execute_sql": True,
+}
+
+
+def _mock_and_invoke(httpx_mock, extra_args=None):
+    httpx_mock.add_response(json=QUERY_RESPONSE, status_code=200)
+    runner = CliRunner()
+    args = ["query", "fixtures", "select * from dogs", "-i", "https://example.com"]
+    if extra_args:
+        args.extend(extra_args)
+    return runner.invoke(cli, args)
+
+
+# -- query --csv --
+
+
+def test_query_csv(httpx_mock):
+    result = _mock_and_invoke(httpx_mock, ["--csv"])
+    assert result.exit_code == 0
+    lines = result.output.strip().split("\n")
+    assert lines[0] == "id,name,age"
+    assert lines[1] == "1,Cleo,5"
+    assert lines[2] == "2,Pancakes,3"
+
+
+# -- query --tsv --
+
+
+def test_query_tsv(httpx_mock):
+    result = _mock_and_invoke(httpx_mock, ["--tsv"])
+    assert result.exit_code == 0
+    lines = result.output.strip().split("\n")
+    assert lines[0] == "id\tname\tage"
+    assert lines[1] == "1\tCleo\t5"
+    assert lines[2] == "2\tPancakes\t3"
+
+
+# -- query --nl --
+
+
+def test_query_nl(httpx_mock):
+    result = _mock_and_invoke(httpx_mock, ["--nl"])
+    assert result.exit_code == 0
+    lines = result.output.strip().split("\n")
+    assert len(lines) == 2
+    assert json.loads(lines[0]) == {"id": 1, "name": "Cleo", "age": 5}
+    assert json.loads(lines[1]) == {"id": 2, "name": "Pancakes", "age": 3}
+
+
+# -- query --table --
+
+
+def test_query_table(httpx_mock):
+    result = _mock_and_invoke(httpx_mock, ["--table"])
+    assert result.exit_code == 0
+    lines = result.output.strip().split("\n")
+    # Should have a header row, a separator row, and 2 data rows
+    assert len(lines) == 4
+    # Header should contain column names
+    assert "id" in lines[0]
+    assert "name" in lines[0]
+    assert "age" in lines[0]
+    # Data rows should contain values
+    assert "Cleo" in lines[2]
+    assert "Pancakes" in lines[3]
+
+
+# -- query -t shortcut for --table --
+
+
+def test_query_table_shortcut(httpx_mock):
+    result = _mock_and_invoke(httpx_mock, ["-t"])
+    assert result.exit_code == 0
+    lines = result.output.strip().split("\n")
+    assert len(lines) == 4
+    assert "Cleo" in lines[2]
+
+
+# -- default JSON (no flag) stays the same --
+
+
+def test_query_default_json(httpx_mock):
+    result = _mock_and_invoke(httpx_mock)
+    assert result.exit_code == 0
+    data = json.loads(result.output)
+    assert data == [
+        {"id": 1, "name": "Cleo", "age": 5},
+        {"id": 2, "name": "Pancakes", "age": 3},
+    ]
+
+
+# -- default_query also supports output formats --
+
+
+def _mock_default_query(httpx_mock, mocker, tmpdir, extra_args=None):
+    mocker.patch("dclient.cli.get_config_dir", return_value=pathlib.Path(tmpdir))
+    config_file = pathlib.Path(tmpdir) / "config.json"
+    config_file.write_text(
+        json.dumps(
+            {
+                "default_instance": "prod",
+                "instances": {
+                    "prod": {
+                        "url": "https://prod.example.com",
+                        "default_database": "main",
+                    }
+                },
+            }
+        )
+    )
+    httpx_mock.add_response(json=QUERY_RESPONSE, status_code=200)
+    runner = CliRunner()
+    args = ["select * from dogs"]
+    if extra_args:
+        args.extend(extra_args)
+    return runner.invoke(cli, args)
+
+
+def test_default_query_csv(httpx_mock, mocker, tmpdir):
+    result = _mock_default_query(httpx_mock, mocker, tmpdir, ["--csv"])
+    assert result.exit_code == 0
+    lines = result.output.strip().split("\n")
+    assert lines[0] == "id,name,age"
+    assert lines[1] == "1,Cleo,5"
+
+
+def test_default_query_table(httpx_mock, mocker, tmpdir):
+    result = _mock_default_query(httpx_mock, mocker, tmpdir, ["--table"])
+    assert result.exit_code == 0
+    assert "Cleo" in result.output
+    assert "Pancakes" in result.output
+    lines = result.output.strip().split("\n")
+    assert len(lines) == 4
+
+
+def test_default_query_nl(httpx_mock, mocker, tmpdir):
+    result = _mock_default_query(httpx_mock, mocker, tmpdir, ["--nl"])
+    assert result.exit_code == 0
+    lines = result.output.strip().split("\n")
+    assert json.loads(lines[0]) == {"id": 1, "name": "Cleo", "age": 5}
+
+
+# -- edge cases --
+
+
+def test_query_csv_with_commas_in_values(httpx_mock):
+    httpx_mock.add_response(
+        json={
+            "ok": True,
+            "rows": [{"name": "Smith, John", "note": 'He said "hi"'}],
+            "columns": ["name", "note"],
+        },
+        status_code=200,
+    )
+    runner = CliRunner()
+    result = runner.invoke(
+        cli,
+        [
+            "query",
+            "db",
+            "select * from t",
+            "-i",
+            "https://example.com",
+            "--csv",
+        ],
+    )
+    assert result.exit_code == 0
+    lines = result.output.strip().split("\n")
+    assert lines[0] == "name,note"
+    # CSV should properly quote fields with commas/quotes
+    assert '"Smith, John"' in lines[1]
+
+
+def test_query_table_empty_results(httpx_mock):
+    httpx_mock.add_response(
+        json={
+            "ok": True,
+            "rows": [],
+            "columns": ["id", "name"],
+        },
+        status_code=200,
+    )
+    runner = CliRunner()
+    result = runner.invoke(
+        cli,
+        [
+            "query",
+            "db",
+            "select * from t",
+            "-i",
+            "https://example.com",
+            "--table",
+        ],
+    )
+    assert result.exit_code == 0
+
+
+def test_query_csv_empty_results(httpx_mock):
+    httpx_mock.add_response(
+        json={
+            "ok": True,
+            "rows": [],
+            "columns": ["id", "name"],
+        },
+        status_code=200,
+    )
+    runner = CliRunner()
+    result = runner.invoke(
+        cli,
+        [
+            "query",
+            "db",
+            "select * from t",
+            "-i",
+            "https://example.com",
+            "--csv",
+        ],
+    )
+    assert result.exit_code == 0
+    lines = result.output.strip().split("\n")
+    assert lines[0] == "id,name"
+    assert len(lines) == 1  # header only, no data rows
+
+
+def test_query_nl_empty_results(httpx_mock):
+    httpx_mock.add_response(
+        json={
+            "ok": True,
+            "rows": [],
+            "columns": ["id", "name"],
+        },
+        status_code=200,
+    )
+    runner = CliRunner()
+    result = runner.invoke(
+        cli,
+        [
+            "query",
+            "db",
+            "select * from t",
+            "-i",
+            "https://example.com",
+            "--nl",
+        ],
+    )
+    assert result.exit_code == 0
+    assert result.output.strip() == ""

tests/test_query.py

@@ -16,7 +16,9 @@ def test_query_error(httpx_mock):
         status_code=400,
     )
     runner = CliRunner()
-    result = runner.invoke(cli, ["query", "https://example.com", "hello"])
+    result = runner.invoke(
+        cli, ["query", "content", "hello", "-i", "https://example.com"]
+    )
     assert result.exit_code == 1
     assert (
         result.output
@@ -42,17 +44,18 @@ def test_query(httpx_mock, with_token):
         status_code=200,
     )
     runner = CliRunner()
-    args = ["query", "https://example.com", "hello"]
+    args = ["query", "content", "hello", "-i", "https://example.com"]
     if with_token:
-        args.append("--token")
-        args.append("xyz")
+        args.extend(["--token", "xyz"])
     result = runner.invoke(cli, args)
     assert result.exit_code == 0
     assert json.loads(result.output) == [{"5 * 2": 10}]
 
     # Check the request
     request = httpx_mock.get_request()
-    assert str(request.url) == "https://example.com.json?sql=hello&_shape=objects"
+    assert (
+        str(request.url) == "https://example.com/content.json?sql=hello&_shape=objects"
+    )
     if with_token:
         assert request.headers["authorization"] == "Bearer xyz"
     else:
@@ -66,28 +69,30 @@ def test_aliases(mocker, tmpdir, httpx_mock):
     assert result.exit_code == 0
     assert result.output == ""
 
-    result = runner.invoke(cli, ["alias", "add", "foo", "https://example.com/foo"])
+    result = runner.invoke(cli, ["alias", "add", "foo", "https://example.com"])
     assert result.exit_code == 0
     assert result.output == ""
 
     result = runner.invoke(cli, ["alias", "list"])
     assert result.exit_code == 0
-    assert result.output == "foo = https://example.com/foo\n"
+    assert "foo = https://example.com" in result.output
 
     # --json mode:
     result = runner.invoke(cli, ["alias", "list", "--json"])
     assert result.exit_code == 0
-    assert json.loads(result.output) == {"foo": "https://example.com/foo"}
+    data = json.loads(result.output)
+    assert data["instances"]["foo"]["url"] == "https://example.com"
 
-    # Check the aliases file
-    aliases_file = pathlib.Path(tmpdir) / "aliases.json"
-    assert json.loads(aliases_file.read_text()) == {"foo": "https://example.com/foo"}
+    # Check the config file
+    config_file = pathlib.Path(tmpdir) / "config.json"
+    config = json.loads(config_file.read_text())
+    assert config["instances"]["foo"]["url"] == "https://example.com"
 
     # Try a query against that alias
     httpx_mock.add_response(
         json={
             "ok": True,
-            "database": "foo",
+            "database": "mydb",
             "query_name": None,
             "rows": [{"11 * 3": 33}],
             "truncated": False,
@@ -99,14 +104,14 @@ def test_aliases(mocker, tmpdir, httpx_mock):
         },
         status_code=200,
     )
-    result = runner.invoke(cli, ["query", "foo", "select 11 * 3"])
+    result = runner.invoke(cli, ["query", "mydb", "select 11 * 3", "-i", "foo"])
     assert result.exit_code == 0
     assert json.loads(result.output) == [{"11 * 3": 33}]
 
-    # Should have hit https://example.com/foo.json
+    # Should have hit https://example.com/mydb.json
     url = httpx_mock.get_request().url
     assert url.host == "example.com"
-    assert url.path == "/foo.json"
+    assert url.path == "/mydb.json"
     assert dict(url.params) == {"sql": "select 11 * 3", "_shape": "objects"}
 
     # Remove alias
@@ -117,4 +122,5 @@ def test_aliases(mocker, tmpdir, httpx_mock):
     result = runner.invoke(cli, ["alias", "remove", "foo"])
     assert result.exit_code == 0
     assert result.output == ""
-    assert json.loads(aliases_file.read_text()) == {}
+    config = json.loads(config_file.read_text())
+    assert config["instances"] == {}

tests/test_rows.py

@@ -0,0 +1,410 @@
+"""Tests for the rows command."""
+
+from click.testing import CliRunner
+from dclient.cli import cli
+import json
+import pathlib
+
+TABLE_RESPONSE = {
+    "ok": True,
+    "rows": [
+        {"id": 1, "name": "Cleo", "age": 5},
+        {"id": 2, "name": "Pancakes", "age": 3},
+        {"id": 3, "name": "Fido", "age": 7},
+    ],
+    "columns": ["id", "name", "age"],
+    "next": None,
+    "next_url": None,
+}
+
+
+def _invoke(httpx_mock, extra_args=None, response=None):
+    httpx_mock.add_response(json=response or TABLE_RESPONSE, status_code=200)
+    runner = CliRunner()
+    args = ["rows", "fixtures", "dogs", "-i", "https://example.com"]
+    if extra_args:
+        args.extend(extra_args)
+    return runner.invoke(cli, args)
+
+
+# -- basic usage --
+
+
+def test_rows_default_json(httpx_mock):
+    result = _invoke(httpx_mock)
+    assert result.exit_code == 0, result.output
+    data = json.loads(result.output)
+    assert len(data) == 3
+    assert data[0]["name"] == "Cleo"
+    # Verify request URL
+    request = httpx_mock.get_request()
+    assert request.url.path == "/fixtures/dogs.json"
+    assert "_shape" in dict(request.url.params)
+    assert dict(request.url.params)["_shape"] == "objects"
+
+
+def test_rows_table_format(httpx_mock):
+    result = _invoke(httpx_mock, ["-t"])
+    assert result.exit_code == 0
+    lines = result.output.strip().split("\n")
+    assert len(lines) == 5  # header + separator + 3 data rows
+    assert "Cleo" in lines[2]
+    assert "Pancakes" in lines[3]
+
+
+def test_rows_csv(httpx_mock):
+    result = _invoke(httpx_mock, ["--csv"])
+    assert result.exit_code == 0
+    lines = result.output.strip().split("\n")
+    assert lines[0] == "id,name,age"
+    assert lines[1] == "1,Cleo,5"
+
+
+def test_rows_nl(httpx_mock):
+    result = _invoke(httpx_mock, ["--nl"])
+    assert result.exit_code == 0
+    lines = result.output.strip().split("\n")
+    assert json.loads(lines[0]) == {"id": 1, "name": "Cleo", "age": 5}
+
+
+# -- single argument uses default database --
+
+
+def test_rows_single_arg_uses_default_database(httpx_mock, mocker, tmpdir):
+    """dclient rows tablename uses default database."""
+    mocker.patch("dclient.cli.get_config_dir", return_value=pathlib.Path(tmpdir))
+    config_file = pathlib.Path(tmpdir) / "config.json"
+    config_file.write_text(
+        json.dumps(
+            {
+                "default_instance": "prod",
+                "instances": {
+                    "prod": {
+                        "url": "https://prod.example.com",
+                        "default_database": "data",
+                    }
+                },
+            }
+        )
+    )
+    httpx_mock.add_response(json=TABLE_RESPONSE, status_code=200)
+    runner = CliRunner()
+    result = runner.invoke(cli, ["rows", "dogs"])
+    assert result.exit_code == 0, result.output
+    request = httpx_mock.get_request()
+    assert request.url.host == "prod.example.com"
+    assert request.url.path == "/data/dogs.json"
+
+
+def test_rows_single_arg_no_default_database_errors(mocker, tmpdir):
+    """dclient rows tablename without default database gives error."""
+    mocker.patch("dclient.cli.get_config_dir", return_value=pathlib.Path(tmpdir))
+    config_file = pathlib.Path(tmpdir) / "config.json"
+    config_file.write_text(
+        json.dumps(
+            {
+                "default_instance": "prod",
+                "instances": {
+                    "prod": {
+                        "url": "https://prod.example.com",
+                        "default_database": None,
+                    }
+                },
+            }
+        )
+    )
+    runner = CliRunner()
+    result = runner.invoke(cli, ["rows", "dogs"])
+    assert result.exit_code == 1
+    assert "No database specified" in result.output
+
+
+# -- filters --
+
+
+def test_rows_filter_eq(httpx_mock):
+    result = _invoke(httpx_mock, ["-f", "name", "eq", "Cleo"])
+    assert result.exit_code == 0
+    request = httpx_mock.get_request()
+    params = dict(request.url.params)
+    assert params["name__exact"] == "Cleo"
+
+
+def test_rows_filter_gt(httpx_mock):
+    result = _invoke(httpx_mock, ["--filter", "age", "gt", "3"])
+    assert result.exit_code == 0
+    assert dict(httpx_mock.get_request().url.params)["age__gt"] == "3"
+
+
+def test_rows_filter_gte(httpx_mock):
+    result = _invoke(httpx_mock, ["-f", "age", "gte", "5"])
+    assert result.exit_code == 0
+    assert dict(httpx_mock.get_request().url.params)["age__gte"] == "5"
+
+
+def test_rows_filter_lt(httpx_mock):
+    result = _invoke(httpx_mock, ["-f", "age", "lt", "5"])
+    assert result.exit_code == 0
+    assert dict(httpx_mock.get_request().url.params)["age__lt"] == "5"
+
+
+def test_rows_filter_lte(httpx_mock):
+    result = _invoke(httpx_mock, ["-f", "age", "lte", "5"])
+    assert result.exit_code == 0
+    assert dict(httpx_mock.get_request().url.params)["age__lte"] == "5"
+
+
+def test_rows_filter_not(httpx_mock):
+    result = _invoke(httpx_mock, ["-f", "name", "not", "Cleo"])
+    assert result.exit_code == 0
+    assert dict(httpx_mock.get_request().url.params)["name__not"] == "Cleo"
+
+
+def test_rows_filter_contains(httpx_mock):
+    result = _invoke(httpx_mock, ["-f", "name", "contains", "leo"])
+    assert result.exit_code == 0
+    assert dict(httpx_mock.get_request().url.params)["name__contains"] == "leo"
+
+
+def test_rows_filter_like(httpx_mock):
+    result = _invoke(httpx_mock, ["-f", "name", "like", "%leo%"])
+    assert result.exit_code == 0
+    assert dict(httpx_mock.get_request().url.params)["name__like"] == "%leo%"
+
+
+def test_rows_filter_startswith(httpx_mock):
+    result = _invoke(httpx_mock, ["-f", "name", "startswith", "Cl"])
+    assert result.exit_code == 0
+    assert dict(httpx_mock.get_request().url.params)["name__startswith"] == "Cl"
+
+
+def test_rows_filter_endswith(httpx_mock):
+    result = _invoke(httpx_mock, ["-f", "name", "endswith", "eo"])
+    assert result.exit_code == 0
+    assert dict(httpx_mock.get_request().url.params)["name__endswith"] == "eo"
+
+
+def test_rows_filter_glob(httpx_mock):
+    result = _invoke(httpx_mock, ["-f", "name", "glob", "C*"])
+    assert result.exit_code == 0
+    assert dict(httpx_mock.get_request().url.params)["name__glob"] == "C*"
+
+
+def test_rows_filter_isnull(httpx_mock):
+    result = _invoke(httpx_mock, ["-f", "name", "isnull", "1"])
+    assert result.exit_code == 0
+    assert dict(httpx_mock.get_request().url.params)["name__isnull"] == "1"
+
+
+def test_rows_filter_notnull(httpx_mock):
+    result = _invoke(httpx_mock, ["-f", "name", "notnull", "1"])
+    assert result.exit_code == 0
+    assert dict(httpx_mock.get_request().url.params)["name__notnull"] == "1"
+
+
+def test_rows_multiple_filters(httpx_mock):
+    result = _invoke(httpx_mock, ["-f", "name", "eq", "Cleo", "-f", "age", "gte", "3"])
+    assert result.exit_code == 0
+    params = list(httpx_mock.get_request().url.params.multi_items())
+    param_dict = {k: v for k, v in params}
+    assert param_dict["name__exact"] == "Cleo"
+    assert param_dict["age__gte"] == "3"
+
+
+def test_rows_custom_filter_op_passthrough(httpx_mock):
+    """Unknown ops are passed through to Datasette, supporting plugin-added filters."""
+    result = _invoke(httpx_mock, ["-f", "name", "custom_plugin_op", "x"])
+    assert result.exit_code == 0
+    assert dict(httpx_mock.get_request().url.params)["name__custom_plugin_op"] == "x"
+
+
+# -- sorting --
+
+
+def test_rows_sort(httpx_mock):
+    result = _invoke(httpx_mock, ["--sort", "age"])
+    assert result.exit_code == 0
+    assert dict(httpx_mock.get_request().url.params)["_sort"] == "age"
+
+
+def test_rows_sort_desc(httpx_mock):
+    result = _invoke(httpx_mock, ["--sort-desc", "age"])
+    assert result.exit_code == 0
+    assert dict(httpx_mock.get_request().url.params)["_sort_desc"] == "age"
+
+
+# -- column selection --
+
+
+def test_rows_col(httpx_mock):
+    result = _invoke(httpx_mock, ["--col", "name", "--col", "age"])
+    assert result.exit_code == 0
+    params = list(httpx_mock.get_request().url.params.multi_items())
+    col_params = [v for k, v in params if k == "_col"]
+    assert col_params == ["name", "age"]
+
+
+def test_rows_nocol(httpx_mock):
+    result = _invoke(httpx_mock, ["--nocol", "id"])
+    assert result.exit_code == 0
+    assert dict(httpx_mock.get_request().url.params)["_nocol"] == "id"
+
+
+# -- search --
+
+
+def test_rows_search(httpx_mock):
+    result = _invoke(httpx_mock, ["--search", "pancakes"])
+    assert result.exit_code == 0
+    assert dict(httpx_mock.get_request().url.params)["_search"] == "pancakes"
+
+
+# -- size --
+
+
+def test_rows_size(httpx_mock):
+    result = _invoke(httpx_mock, ["--size", "10"])
+    assert result.exit_code == 0
+    assert dict(httpx_mock.get_request().url.params)["_size"] == "10"
+
+
+# -- limit --
+
+
+def test_rows_limit(httpx_mock):
+    response = {
+        "ok": True,
+        "rows": [{"id": 1}, {"id": 2}, {"id": 3}],
+        "columns": ["id"],
+        "next": None,
+    }
+    result = _invoke(httpx_mock, ["--limit", "2"], response=response)
+    assert result.exit_code == 0
+    data = json.loads(result.output)
+    assert len(data) == 2
+
+
+# -- pagination with --all --
+
+
+def test_rows_all_pagination(httpx_mock):
+    page1 = {
+        "ok": True,
+        "rows": [{"id": 1}, {"id": 2}],
+        "columns": ["id"],
+        "next": "2",
+        "next_url": "https://example.com/fixtures/dogs.json?_next=2&_shape=objects",
+    }
+    page2 = {
+        "ok": True,
+        "rows": [{"id": 3}],
+        "columns": ["id"],
+        "next": None,
+        "next_url": None,
+    }
+    httpx_mock.add_response(json=page1, status_code=200)
+    httpx_mock.add_response(json=page2, status_code=200)
+    runner = CliRunner()
+    result = runner.invoke(
+        cli,
+        ["rows", "fixtures", "dogs", "-i", "https://example.com", "--all"],
+    )
+    assert result.exit_code == 0
+    data = json.loads(result.output)
+    assert len(data) == 3
+    assert [r["id"] for r in data] == [1, 2, 3]
+
+
+def test_rows_all_with_limit(httpx_mock):
+    page1 = {
+        "ok": True,
+        "rows": [{"id": 1}, {"id": 2}],
+        "columns": ["id"],
+        "next": "2",
+        "next_url": "https://example.com/fixtures/dogs.json?_next=2&_shape=objects",
+    }
+    page2 = {
+        "ok": True,
+        "rows": [{"id": 3}, {"id": 4}],
+        "columns": ["id"],
+        "next": None,
+    }
+    httpx_mock.add_response(json=page1, status_code=200)
+    httpx_mock.add_response(json=page2, status_code=200)
+    runner = CliRunner()
+    result = runner.invoke(
+        cli,
+        [
+            "rows",
+            "fixtures",
+            "dogs",
+            "-i",
+            "https://example.com",
+            "--all",
+            "--limit",
+            "3",
+        ],
+    )
+    assert result.exit_code == 0
+    data = json.loads(result.output)
+    assert len(data) == 3
+
+
+def test_rows_no_all_ignores_next(httpx_mock):
+    """Without --all, pagination is not followed even if next is present."""
+    response = {
+        "ok": True,
+        "rows": [{"id": 1}, {"id": 2}],
+        "columns": ["id"],
+        "next": "2",
+        "next_url": "https://example.com/fixtures/dogs.json?_next=2&_shape=objects",
+    }
+    result = _invoke(httpx_mock, response=response)
+    assert result.exit_code == 0
+    data = json.loads(result.output)
+    assert len(data) == 2  # only first page
+
+
+# -- error handling --
+
+
+def test_rows_api_error(httpx_mock):
+    httpx_mock.add_response(
+        json={"ok": False, "error": "Table not found: dogs"},
+        status_code=404,
+    )
+    runner = CliRunner()
+    result = runner.invoke(
+        cli,
+        ["rows", "fixtures", "dogs", "-i", "https://example.com"],
+    )
+    assert result.exit_code == 1
+    assert "Table not found" in result.output
+
+
+# -- uses default instance --
+
+
+def test_rows_default_instance(httpx_mock, mocker, tmpdir):
+    mocker.patch("dclient.cli.get_config_dir", return_value=pathlib.Path(tmpdir))
+    config_file = pathlib.Path(tmpdir) / "config.json"
+    config_file.write_text(
+        json.dumps(
+            {
+                "default_instance": "prod",
+                "instances": {
+                    "prod": {
+                        "url": "https://prod.example.com",
+                        "default_database": "main",
+                    }
+                },
+            }
+        )
+    )
+    httpx_mock.add_response(json=TABLE_RESPONSE, status_code=200)
+    runner = CliRunner()
+    result = runner.invoke(cli, ["rows", "data", "dogs"])
+    assert result.exit_code == 0
+    request = httpx_mock.get_request()
+    assert request.url.host == "prod.example.com"
+    assert request.url.path == "/data/dogs.json"