kubectl-sql

README

kubectl-sql

Query any Kubernetes resource using SQL — directly from your terminal.

kubectl sql "SELECT name, namespace, status->phase FROM pods WHERE status->phase != 'Running'"

kubectl-sql is a kubectl plugin that brings SQL semantics to Kubernetes. Instead of chaining kubectl get, grep, jq, and awk, you write a single declarative query and get back a clean table, JSON, or CSV.

Features

• Full SQL subset — SELECT, WHERE, ORDER BY, LIMIT, GROUP BY, aggregates (COUNT, SUM, …), DISTINCT
• Delete or update - DELETE Kubernetes resources with SQL query
• Dynamic schema — columns are inferred from the OpenAPI spec (with sample-object fallback), so SELECT * returns real resource fields like status, spec, metadata
• Nested field access — use -> for struct traversal (metadata->labels->app)
• Array indexing — array_get(spec->volumes, 0)->configMap resolves the first volume's ConfigMap name
• All resource types — built-ins, CRDs, short names, plural forms all accepted
• Cross-namespace — queries all namespaces by default; scope with -n
• Multiple output formats — aligned table, JSON, CSV
• Introspection — SHOW TABLES and DESCRIBE TABLE <resource>

Installation

From release binaries

Download the archive for your platform from the releases page (Linux amd64, macOS amd64/arm64), then:

tar -xzf kubectl-sql_*.tar.gz
mv kubectl-sql ~/bin/   # or anywhere on your PATH

From source

git clone https://github.com/ebuildy/kubectl-sql
cd kubectl-sql
make build    # produces ./bin/kubectl-sql
make install  # copies to ~/bin — ensure ~/bin is on your PATH

As a kubectl plugin

Once kubectl-sql is on your PATH, kubectl picks it up automatically:

kubectl sql "SELECT name FROM pods"

Usage

kubectl-sql [query] [flags]

Pass a query directly, or run with no query to drop into the interactive REPL:

$ kubectl-sql
sql> SELECT name, namespace FROM pods WHERE status->phase != 'Running'
... results ...
sql> /quit

At the sql> prompt, type a query and press Enter to run it. Use the up/down arrows to recall previous queries, and press Tab to autocomplete SQL keywords, table names (after FROM), column names of the table in your FROM clause, and slash commands (any word starting with /).

REPL slash commands:

Command	Action
/quit	exit the REPL (quit, exit, or Ctrl-C also work)
/clear	clear the screen (history is kept)
/history-clear	clear the recall history (screen is kept)
/help	list the slash commands
/version	print the version and project URL
/tables	list tables (same as SHOW TABLES)

Breaking change: the old backslash commands \q, \help, and ? have been removed. Use /quit and /help instead.

Queries can be piped in too — they run in batch mode, one per line:

echo "SELECT name FROM pods LIMIT 5" | kubectl-sql

Logging

By default only errors are logged. Increase verbosity with -v (info) or -vv (debug, including per-step timings in ms). All logs are written to stderr, so query results on stdout stay clean and pipeable:

kubectl-sql -vv --output json "SELECT name FROM pods" 2>debug.log | jq .

Flags

Flag	Short	Default	Description
--output	-o	table	Output format: table, json, csv
--repl	-i	false	Open the interactive SQL REPL (default when no query is given)
--watch	-w	false	Re-run the query every 5s, refreshing the table
--verbose	-v	error	Increase log verbosity: -v=info, -vv=debug. Logs go to stderr
--namespace	-n	all namespaces	Restrict query to a single namespace
--context		current context	kubeconfig context to use
--kubeconfig		~/.kube/config	Path to kubeconfig
--page-size		500	Kubernetes LIST page size
--timeout		30s	Per-request timeout
--explain		false	Print the execution plan without running the query
--dry-run		false	Validate SQL without hitting the API
--no-color		false	Disable ANSI colors
--disable-beauty		false	Render struct values as compact single-line JSON (no pretty-printing or key colors)

Exit codes

Code	Meaning
0	Success
1	Query or parse error
2	Kubernetes API error

SQL Reference

Basic queries

-- List all pods across all namespaces
SELECT name, namespace FROM pods

-- Filter by field value
SELECT name, namespace, status->phase FROM pods WHERE status->phase = 'Running'

-- Sort and limit
SELECT name, namespace FROM pods ORDER BY name LIMIT 20

-- Wildcard — returns all inferred columns
SELECT * FROM deployments LIMIT 5

Nested fields

Use -> to traverse struct fields.

-- Arrow notation
SELECT metadata->labels->app FROM pods

-- Array index access via array_get()
SELECT name, array_get(spec->volumes, 0)->configMap FROM pods WHERE name = 'nginx'

Aggregates

-- Count pods per namespace
SELECT namespace, COUNT(*) FROM pods GROUP BY namespace

-- Total replicas across all deployments
SELECT SUM(status->replicas) FROM deployments

Label and annotation selectors

SELECT * FROM pods WHERE LABEL 'app' = 'nginx'
SELECT * FROM pods WHERE ANNOTATION 'team' = 'platform'

Introspection

-- List all queryable resource types
SHOW TABLES

-- List columns and types for a resource
DESCRIBE TABLE pods
DESCRIBE TABLE deployments

JSON file sources

A FROM reference whose table name ends in .json, .jsonl, or .ndjson is read as a local JSON Lines file (one JSON object per line, not a pretty-printed JSON document). The column schema is inferred by sampling the file, and the result supports the same SELECT column lists, *, WHERE, ORDER BY, LIMIT, and output formats as Kubernetes-backed tables.

-- Read every line of notes.json as a row
SELECT * FROM notes.json

-- .jsonl and .ndjson are read the same way
SELECT * FROM notes.jsonl
SELECT * FROM notes.ndjson

-- Column selection and filtering work like any other table
SELECT pod, note FROM notes.json WHERE pod = 'nginx-1'

-- JOIN two JSON file tables together
SELECT n.pod, n.note, s.status
FROM notes.json n JOIN status.json s ON n.pod = s.pod

Paths may be relative to the current directory (fixtures/notes.json), explicitly relative (./notes.json), or absolute (/tmp/notes.json).

Each output column is rendered with a <table>.<field> prefix, the same convention used for Kubernetes tables (pods.name). For a .json file the prefix defaults to the file's basename (e.g. notes.pod); for .jsonl/.ndjson files the prefix defaults to the literal extension (jsonl.pod/ndjson.pod) instead, a quirk of how json is recognized as a SQL keyword but jsonl/ndjson are not. Use AS <alias> (e.g. FROM notes.jsonl AS notes) for a predictable, consistent prefix across all three extensions.

kubectl-sql registers a Kubernetes database under the name k8s, so a file literally named k8s.json (or k8s.jsonl/k8s.ndjson) would otherwise be interpreted as resource json in the k8s database. Reference such a file with a leading ./: FROM ./k8s.json.

Note: JOIN between a Kubernetes-backed table (e.g. pods) and a .json/ .jsonl/.ndjson file is not yet supported — tracked as a follow-up.

Tips

Turn kubectl get -o json output into JSON Lines

kubectl get <resource> -o json returns a single JSON document with the matching objects nested under .items. Pipe it through jq -c '.items[]' to flatten that array into one compact JSON object per line — exactly the JSON Lines format the JSON file datasource expects:

# Snapshot all pods (cluster-wide) to a JSON Lines file
kubectl get pods -A -o json | jq -c '.items[]' > pods.jsonl

# Query the snapshot — no live cluster access needed
kubectl sql "SELECT metadata->name AS name, metadata->namespace AS namespace, status->phase FROM pods.jsonl"

This is handy for querying a point-in-time snapshot offline, or for re-running queries against it without hitting the API server again.

Note: the name/namespace/labels/annotations shortcut columns available on k8s.* tables (e.g. pods.name) are synthesized by kubectl-sql's Kubernetes adapter and aren't present in raw kubectl get -o json output. Use the underlying metadata->name / metadata->namespace paths (with AS to rename) when querying a JSON Lines snapshot instead.

Flatten fields with jq before JOINing snapshots

For JOINs — e.g. diffing two snapshots taken at different times — project the fields you need into a flat top-level shape with jq so the join key (and any compared columns) are plain fields, not -> expressions:

snapshot() {
  kubectl get pods -A -o json |
    jq -c '.items[] | {name: .metadata.name, namespace: .metadata.namespace, phase: .status.phase}'
}

snapshot > before.jsonl
# ... time passes, or changes are rolled out ...
snapshot > after.jsonl

kubectl sql "SELECT b.name, b.namespace, b.phase AS before_phase, a.phase AS after_phase
              FROM before.jsonl b JOIN after.jsonl a ON b.name = a.name
              WHERE b.phase != a.phase"

Recipes

# Pods not Running
kubectl sql "SELECT name, namespace, status->phase FROM pods WHERE status->phase != 'Running'"

# Recent warning events
kubectl sql "SELECT name, namespace, reason, message FROM events WHERE type = 'Warning' ORDER BY lastTimestamp DESC LIMIT 50"

# CrashLoopBackOff containers
kubectl sql "SELECT name, namespace FROM pods WHERE status->containerStatuses->0->state->waiting->reason = 'CrashLoopBackOff'"

# Deployments with unavailable replicas
kubectl sql "SELECT name, namespace, status->replicas, status->availableReplicas FROM deployments WHERE status->availableReplicas < status->replicas"

# Pods in a specific namespace
kubectl sql -n kube-system "SELECT name, status->phase FROM pods"

# Count pods per namespace
kubectl sql "SELECT namespace, COUNT(*) FROM pods GROUP BY namespace"

# JSON output for scripting
kubectl sql -o json "SELECT name, namespace FROM pods WHERE status->phase = 'Failed'"

# Dry-run to validate SQL before hitting the cluster
kubectl sql --dry-run "SELECT name FROM doesnotexist"

# Show execution plan
kubectl sql --explain "SELECT name FROM pods WHERE status->phase = 'Pending'"

# Delete every Pending pod (previews the set, then asks for confirmation)
kubectl sql "DELETE pod WHERE status->phase = 'Pending'"

# Force-delete with delete options via a MySQL-style hint comment
kubectl sql "DELETE /* force, grace-period=0 */ FROM pod WHERE status->phase = 'Pending'"

# Orphan a deployment's children instead of cascading the delete
kubectl sql "DELETE /* cascade=orphan */ deployment WHERE name = 'web'"

# Skip the confirmation prompt for scripted use (required when non-interactive)
kubectl sql -y "DELETE pod WHERE status->phase = 'Succeeded'"

# Preview the deletion set without deleting anything
kubectl sql --dry-run "DELETE pod WHERE status->phase = 'Pending'"

Note: DELETE is the only mutating statement and requires the delete RBAC verb on the target resource. It always previews the matched objects and asks for confirmation (default no); pass -y/--yes to skip the prompt. DELETE cannot be combined with --watch.

How it works

kubectl-sql is built on octosql, a streaming SQL engine. At query time it:

1. Infers the schema from the cluster's OpenAPI v3 spec (primary) or a 1-item LIST sample (fallback), exposing all real resource fields as typed columns
2. Rewrites the SQL — bare table names in FROM/JOIN are qualified with k8s. so octosql routes them to the Kubernetes datasource
3. Streams results — resources are fetched with paginated LIST calls and streamed through the SQL engine; no full cluster load into memory
4. Renders output — results are written as an aligned table, JSON array, or CSV

Schema inference uses a hexagonal architecture: OpenAPIInferrer → SampleInferrer → CompositeInferrer, so any resource type — including CRDs without a formal schema — works out of the box.

Built with Claude Code + OpenSpec

This project was built entirely with Claude Code using a spec-driven workflow called OpenSpec.

Every non-trivial feature followed this cycle:

1. Propose — describe the change in plain language; Claude generates proposal.md, design.md, and behavioral specs (specs/*.md)
2. Apply — Claude implements the tasks in tasks.md one by one, guided by the specs
3. Archive — completed changes are archived and their specs are merged into the long-lived openspec/specs/ source of truth

The specs live in openspec/ alongside the code. They document what the system does and why decisions were made — independently of any AI session. See docs/adr-001-schema-inference-strategy.md for an example of an Architecture Decision Record produced during this process.

[!NOTE] The entire codebase — from project scaffold to schema inference to the SQL rewriter — was produced through conversational iteration with Claude Code, with humans reviewing and steering at each step.

Specs

Long-lived behavioral specs live in openspec/specs/ and are the source of truth for how each feature works.

Spec	Description
DELETE Statement	Defines DELETE: grammar with hint-comment options, deletion-set preview, confirmation/--yes, bounded-parallel delete, and exit codes.
DESCRIBE TABLE	Lists all columns and types for a resource via DESCRIBE TABLE <resource>, inferred from OpenAPI or a sample object.
Dynamic Schema Inference	Defines how resource schemas are inferred at query time, driving column discovery for SELECT *, DESCRIBE TABLE, and typed filtering.
envtest Integration Tests	Behavioral contract for the envtest-backed integration suite that exercises the full SQL query path without a live cluster.
JSON File Datasource	Defines querying local JSON Lines files (.json/.jsonl/.ndjson) via FROM <path>, including JOINs between files.
Kubernetes Datasource	Defines how resource kinds are resolved, fetched, mapped to rows, and namespace-scoped by the Kubernetes datasource layer.
Kubernetes Data-Source Port	Defines the hexagonal port/adapter boundary that isolates client-go/apimachinery and exposes listing, schema, discovery, and a single delete operation.
Logging	Defines leveled -v/-vv logging to stderr, shared via context, behind a port/adapter boundary, with timed debug/info traces.
Output Renderer	Defines internal/output.Render, the TTY-independent renderer that drives execution and writes query results.
Project Scaffold	Baseline structural requirements: Go module setup, CLI entrypoint, flags, package layout, and Makefile targets.
Query Typo Suggestion	Turns a failed query into a high-confidence single-token correction (keyword, table, field, dotted access, or unterminated quote) by string similarity.
SHOW TABLES	Defines SHOW TABLES, which lists all Kubernetes API resource types queryable via kubectl-sql.
SQL Engine Port	Defines the hexagonal port/adapter boundary that confines the octosql engine and keeps it swappable.
SQL Execution	End-to-end SQL query execution contract: CLI input, SELECT/WHERE/LIMIT semantics, DELETE routing to the mutator adapter, and flag forwarding.
SQL Mutator Adapter	Defines the mutator adapter that owns mutating statements, resolving targets via octosql and deleting through the DataSource port with bounded parallelism.
SQL REPL	Defines the interactive REPL: prompt loop, slash commands (/quit, /clear, /history-clear, /help, /version, /tables), history, batch fallback, and Tab autocomplete.
Swagger Schema Provider	Embeds a generated Kubernetes OpenAPI snapshot so spec/status field structure is available without a cluster round trip.
Watch Mode	Defines the --watch/-w flag, which re-executes the query every 5 seconds and reprints the result table until Ctrl-C or --timeout.

Development

# Run unit tests
make test

# Run integration tests (requires envtest)
make test-integration

# Run end-to-end tests against a local envtest cluster
make e2e-run-fake

# Lint
make lint

# Install dev dependencies (golangci-lint, setup-envtest)
make dev-deps

[!NOTE] Integration and e2e tests use controller-runtime envtest — no real cluster needed. Run make dev-deps first to download the required binaries.

Regenerating the embedded Kubernetes schema

kubectl-sql ships with an embedded snapshot of the Kubernetes OpenAPI v2 spec (internal/adapter/datasources/k8s/schema_swagger_k8s_standard_resources.go + .bin.gz), used as a schema source for DESCRIBE TABLE and SELECT * column inference on built-in resources.

To regenerate it:

make generate

This runs tools/genk8sschema, which reads internal/adapter/datasources/k8s/testdata/swagger.json and writes the embedded Go snapshot. That fixture is gitignored; if it's missing, make generate downloads the latest swagger.json from kubernetes/kubernetes master automatically.

To refresh the snapshot to a newer Kubernetes version, delete the fixture and re-run make generate:

rm internal/adapter/datasources/k8s/testdata/swagger.json
make generate

Releasing

Releases are fully automated with GoReleaser via the Release workflow. Pushing a tag matching v* triggers it:

git tag v0.1.0
git push origin v0.1.0

The workflow then:

1. Builds static binaries (CGO_ENABLED=0) for linux/amd64, darwin/amd64, and darwin/arm64
2. Packages each as a tar.gz archive with the LICENSE and README.md
3. Generates a checksums.txt (SHA-256) for all archives
4. Creates a GitHub release with a changelog from commit messages (docs:, test:, and chore: commits excluded)

Tags with a prerelease suffix (e.g. v0.2.0-rc1) are automatically marked as prereleases. Build targets and packaging are configured in .goreleaser.yaml; validate changes locally with goreleaser check or do a full dry run with goreleaser release --snapshot --clean.

Documentation

Document	Description
ADR-001 — Schema inference strategy	Why OpenAPI is the primary schema source with sample-object fallback
ADR-002 — SQL engine choice	Why octosql, and why DuckDB was considered but ruled out
ADR-003 — Go over Rust	Language choice rationale: velocity, Kubernetes ecosystem, static binary
ADR-004 — AI-assisted development	How Claude Code + OpenSpec were used to build this project
SQL grammar (EBNF)	Formal grammar reference
OpenSpec behavioral specs	Long-lived specs for all features

---

⚡ Made blazing fast with love at Sanary-sur-Mer 🌊

Documentation

Overview

Package main is the entrypoint for the kubectl-sql binary.