# EctoCockroachExtras

Elixir library exposing CockroachDB cluster and database statistics for use from IEx,
scripts, or [`Phoenix.LiveDashboard`](https://hexdocs.pm/phoenix_live_dashboard).

It mimics the API of [`ecto_psql_extras`](https://github.com/pawurb/ecto_psql_extras), but
`ecto_psql_extras` doesn't work against CockroachDB: it depends on PostgreSQL internals
CockroachDB doesn't have (`pg_stat_activity`, `pg_locks`, `pg_stat_statements`, autovacuum),
and CockroachDB's `pg_catalog` compatibility shims for those are unpopulated stubs. This
library instead queries CockroachDB's own introspection surface: `SHOW JOBS`,
`SHOW CHANGEFEED JOBS`, `SHOW STATISTICS`, `SHOW CLUSTER SETTINGS`, and the
`crdb_internal.*` virtual tables.

## Installation

```elixir
def deps do
  [
    {:ecto_cockroach_extras, "~> 0.1.0"}
  ]
end
```

## Usage

```elixir
EctoCockroachExtras.jobs(MyApp.Repo)
EctoCockroachExtras.changefeed_jobs(MyApp.Repo)
EctoCockroachExtras.table_statistics(MyApp.Repo, "users")
EctoCockroachExtras.sessions(MyApp.Repo)
EctoCockroachExtras.running_queries(MyApp.Repo, args: [threshold: 5])
EctoCockroachExtras.locks(MyApp.Repo)
EctoCockroachExtras.contention(MyApp.Repo)
EctoCockroachExtras.cluster_settings(MyApp.Repo)
EctoCockroachExtras.cluster_settings(MyApp.Repo, args: [all: true, pattern: "sql.defaults%"])
EctoCockroachExtras.index_usage(MyApp.Repo)
EctoCockroachExtras.unused_indexes(MyApp.Repo)
EctoCockroachExtras.table_sizes(MyApp.Repo)
EctoCockroachExtras.index_sizes(MyApp.Repo)
EctoCockroachExtras.top_queries(MyApp.Repo)
EctoCockroachExtras.diagnose(MyApp.Repo, tables: ["users", "orders"])
```

By default an ASCII table is printed. Pass `format: :raw` to get the underlying
`%Postgrex.Result{}` instead:

```elixir
EctoCockroachExtras.jobs(MyApp.Repo, format: :raw)
```

You can also run a query by name:

```elixir
EctoCockroachExtras.query(:cluster_settings, MyApp.Repo, format: :raw)
```

## `SHOW CLUSTER SETTINGS`

`cluster_settings/2` wraps `SHOW CLUSTER SETTINGS` (public settings only) by default. Pass
`args: [all: true]` to switch to `SHOW ALL CLUSTER SETTINGS`, which also includes
non-public/internal settings and one extra `public` column. Pass `args: [pattern: "..."]`
to filter `variable` with a SQL `LIKE` pattern.

## `allow_unsafe_internals`

A few `crdb_internal.*` virtual tables (`locks`, `index_usage`, `unused_indexes`) are gated
behind CockroachDB's `allow_unsafe_internals` session variable in production/CCL builds.
This library handles that automatically by running the `SET` and the query in the same
transaction - no setup needed on your end, just be aware these particular queries incur an
extra round trip.

## Phoenix.LiveDashboard

Phoenix.LiveDashboard's built-in Ecto Stats page dispatches by `repo.__adapter__()`, and a
CockroachDB repo reports `Ecto.Adapters.Postgres` just like a real Postgres repo - so it
gets wrongly routed to `EctoPSQLExtras`, not this library. Use the page shipped with this
library instead:

```elixir
live_dashboard "/dashboard",
  additional_pages: [
    cockroach_stats: {EctoCockroachExtras.LiveDashboardPage, repos: [MyApp.Repo]}
  ]
```

This requires adding `:phoenix_live_dashboard` as a dependency of your application (it's an
optional dependency of this library).

## What's not included (v1)

No CockroachDB equivalent, or lower priority for now: `duplicate_indexes`, `records_rank`,
`table_foreign_keys`, `missing_fk_indexes`, `missing_fk_constraints`, `table_schema`,
`bloat`, `vacuum_stats`, `ssl_used`, `extensions`, `kill_all`/cancel-session support, and
multi-node `{repo, node}` dispatch (all repo access is local-node only).

## Testing

The test suite runs real queries against a live CockroachDB instance - nothing is mocked.
It defaults to `localhost:26257` insecure; override with `COCKROACH_HOST`, `COCKROACH_PORT`,
`COCKROACH_DATABASE`, `COCKROACH_USERNAME`, `COCKROACH_SSL` env vars.

Start a local instance if you don't already have one running:

```sh
docker run -d --name crdb -p 26257:26257 cockroachdb/cockroach:v26.2.0 start-single-node --insecure
```

Then:

```sh
mix deps.get
mix test
```

## License

MIT