DBPilot
Self-hosted database performance diagnostics for SQL Server, MySQL & PostgreSQL: trends, AAS insight, Top SQL, plan-change tracking, blocking & deadlock analysis. Single-process deploy, built-in MCP server for AI agents.
Safety scan
A static text scan, not a guarantee. Select the badge to see what was scanned and every finding.
Badge for your README
Markdown
[](/listings/skychensky--dbpilot)HTML
<a href="/listings/skychensky--dbpilot"><img src="/api/listings/skychensky--dbpilot/badge.svg" alt="findsafeskills safety rating" /></a>Install
Install instructions from the README
As written by the repo's author. findsafeskills has not verified these steps.
Fastest path: NuGet packages (frontend embedded — no Node.js). Requires .NET SDK 8.0+ — packages run on .NET 8 or later.
1. Create a host project and add the metapackage:
mkdir dbpilot-demo && cd dbpilot-demo dotnet new web dotnet add package DBPilot
> Don't name the host project `dbpilot` or `DBPilot.*` — it collides with the NuGet package and restore fails (NU1108).
2. Replace `Program.cs` with:
using DBPilot.AspNetCore.Extension; using DBPilot.Core.Providers; var builder = WebApplication.CreateBuilder(args); builder.AddDBPilot(o => o.PlatformEngine = DbpilotEngine.Sqlite); // metadata store: SQLite, zero dependencies var app = builder.Build(); app.UseDBPilot(); app.Run();
3. Replace `appsettings.json` with (SQLite flavor):
{
"Serilog": {
"MinimumLevel": {
"Default": "Information",
"Override": { "Microsoft": "Warning", "System": "Warning" }
}
},
"DBPilot": {
"ConnectionString": "Data Source=dbpilot_platform.db",
"Mcp": { "ApiKey": "<random key; non-empty enables MCP, empty = off>" },
"Auth": {
"Username": "admin",
"PasswordHash": "pbkdf2$100000$IOaBWezPYRnzEbBWYwLxZA==$xBWm8jjDfanDmyRttfnZoG4MIk8lcF0UnDbpjJuWaEU=",
"Secret": "<your master key (>= 32-char random string)>"
}
},
"AllowedHosts": "*"
}- `ConnectionString` and `Auth:Secret` are required — the SQLite file and schema are created on startup; the master key is generated as shown [below](#password-and-master-key). - `PasswordHash` is the hash of the default password `dbpilot@2026`; keep it to log in with the default.
4. Run:
dotnet run # → http://localhost:5000
5. Log in with `admin` / `dbpilot@2026` (**change it after deployment**), then register your first instance: **Instances → Add** → Test connection → Enable. Real-time pages work immediately; history accumulates.
**Metadata store**: for SQL Server / MySQL / PostgreSQL, switch `PlatformEngine` and the connection string — that's all; registered instances are unaffected. SQLite suits evaluation and single-machine use; prefer a server engine for production (and multi-process roles). Ready-made hosts: [`samples/`](samples/) (SqlServer :5200 / MySql :5201 / Sqlite :5203 / PostgreSql :5204).
What was scanned
The files the safety scan read, as written by the repository's author and shown as plain text. Lines with a finding are marked.
README.md · README · 365 lines · 1 finding marked
View this file on GitHub (read at the repository's current version: the scan did not record which commit it read)
- # DBPilot
- **Self-hosted database autonomous diagnostics platform**.
- [](https://www.nuget.org/packages/DBPilot)
- [](https://www.nuget.org/packages/DBPilot)
- [](LICENSE.txt)
- [](https://dotnet.microsoft.com/download/dotnet/8.0)
- [](#engine-support-matrix)
- DBPilot continuously samples your instances and serves the day-to-day DBA workflow in one web console: performance trends and insight (AAS load decomposition), Top SQL, query plan change tracking, missing-index advice, index usage & fragmentation, blocking analysis, deadlock analysis, and slow query logs.
- [简体中文](README.zh-CN.md)
- 
- ## Contents
- - [Highlights](#highlights)
- - [Screenshots](#screenshots)
- - [Quick start](#quick-start)
- - [Password and master key](#password-and-master-key)
- - [Configuration](#configuration-appsettingsjson)
- - [Building from source](#building-from-source)
- - [Architecture](#architecture)
- - [Features](#features)
- - [Engine support matrix](#engine-support-matrix)
- - [AI diagnostics (MCP Server)](#ai-diagnostics-mcp-server)
- - [Overhead on monitored instances](#overhead-on-monitored-instances)
- - [Embedding via NuGet](#embedding-via-nuget)
- - [FAQ](#faq)
- - [License](#license)
- ## Highlights
- - **Single-process deployment** — one .NET process (8.0+) + one metadata DB, schema auto-created on startup (SQL Server / MySQL / PostgreSQL / SQLite).
- - **Monitors three engines** — SQL Server 2008–2022, MySQL 8.0+, PostgreSQL 13+. Monitoring and metadata-store engines are independent axes: any combination works.
- - **<1% CPU overhead** — collectors read only in-memory metadata views (DMVs / `pg_stat_*` / `performance_schema`); business tables are never scanned.
- - **AI-ready** — built-in read-only MCP Server exposes 11 diagnostic tools to AI agents (Claude Code, Codex CLI). Just ask: *"did any SQL slow down in the last hour?"*
- - **Capability-driven UI** — features without an equivalent data source hide or degrade per engine (PostgreSQL gets deadlock *trends* instead of event details); APIs answer with actionable messages.
- ## Screenshots
- | Performance insight (AAS) | Performance trends |
- |---|---|
- |  |  |
- | Deadlock analysis | Deadlock event detail |
- |---|---|
- |  |  |
- | Blocking analysis | Blocking chain detail |
- |---|---|
- |  |  |
- | Index usage | Slow query log |
- |---|---|
- |  |  |
- ## Quick start
- Fastest path: NuGet packages (frontend embedded — no Node.js). Requires .NET SDK 8.0+ — packages run on .NET 8 or later.
- 1. Create a host project and add the metapackage:
- ```bash
- mkdir dbpilot-demo && cd dbpilot-demo
- dotnet new web
- dotnet add package DBPilot
- ```
- > Don't name the host project `dbpilot` or `DBPilot.*` — it collides with the NuGet package and restore fails (NU1108).
- 2. Replace `Program.cs` with:
- ```csharp
- using DBPilot.AspNetCore.Extension;
- using DBPilot.Core.Providers;
- var builder = WebApplication.CreateBuilder(args);
- builder.AddDBPilot(o => o.PlatformEngine = DbpilotEngine.Sqlite); // metadata store: SQLite, zero dependencies
- var app = builder.Build();
- app.UseDBPilot();
- app.Run();
- ```
- 3. Replace `appsettings.json` with (SQLite flavor):
- ```json
- {
- "Serilog": {
- "MinimumLevel": {
- "Default": "Information",
- "Override": { "Microsoft": "Warning", "System": "Warning" }
- }
- },
- "DBPilot": {
- "ConnectionString": "Data Source=dbpilot_platform.db",
- "Mcp": { "ApiKey": "<random key; non-empty enables MCP, empty = off>" },
- "Auth": {
- "Username": "admin",
- "PasswordHash": "pbkdf2$100000$IOaBWezPYRnzEbBWYwLxZA==$xBWm8jjDfanDmyRttfnZoG4MIk8lcF0UnDbpjJuWaEU=",
- Note: Contains a long base64-looking blob — not necessarily malicious, but worth inspecting what it decodes to
- Line 103 on GitHub
- "Secret": "<your master key (>= 32-char random string)>"
- }
- },
- "AllowedHosts": "*"
- }
- ```
- - `ConnectionString` and `Auth:Secret` are required — the SQLite file and schema are created on startup; the master key is generated as shown [below](#password-and-master-key).
- - `PasswordHash` is the hash of the default password `dbpilot@2026`; keep it to log in with the default.
- 4. Run:
- ```bash
- dotnet run # → http://localhost:5000
- ```
- 5. Log in with `admin` / `dbpilot@2026` (**change it after deployment**), then register your first instance: **Instances → Add** → Test connection → Enable. Real-time pages work immediately; history accumulates.
- **Metadata store**: for SQL Server / MySQL / PostgreSQL, switch `PlatformEngine` and the connection string — that's all; registered instances are unaffected. SQLite suits evaluation and single-machine use; prefer a server engine for production (and multi-process roles). Ready-made hosts: [`samples/`](samples/) (SqlServer :5200 / MySql :5201 / Sqlite :5203 / PostgreSql :5204).
- ## Password and master key
- **Change the login password**: passwords are PBKDF2 hashes in `DBPilot:Auth:PasswordHash` — replace the hash to change the password.
- 1. Create a two-line `hash.cs` **outside any project directory** (your home dir works). Inside a project, `dotnet run` would treat it as an argument. File-based apps with `#:package` need .NET SDK 10; on SDK 8 use the repo-clone alternative below:
- ```csharp
- #:package DBPilot.Core@0.5.5
- Console.WriteLine(DBPilot.Core.Auth.PasswordHasher.Hash(args[0]));
- ```
- 2. Run it — the whole output line is the new hash:
- ```bash
- dotnet run hash.cs <new-password>
- ```
- 3. Paste it into `DBPilot:Auth:PasswordHash`, restart.
- Repo clones can skip the steps: `dotnet run --project samples/DBPilot.Sample.SqlServer -- --hash <new-password>`.
- **Master key** (`Auth:Secret`, or env `DBPILOT_MASTER_KEY`) — required, exactly two uses:
- | Purpose | What it does | If you change it |
- |---|---|---|
- | Login-cookie signing | issues/validates login tickets (HMAC-SHA256) | all sessions logged out; log in again |
- | Instance-credential encryption | instance passwords stored AES-256-GCM-encrypted in the metadata DB | **old ciphertext is undecryptable, no recovery** — re-enter each instance's password in the UI |
- Hence startup fails without it, and you should **not change it once in use**. Generate one:
- ```bash
- openssl rand -base64 32 # Linux / macOS / Git Bash
- # PowerShell: [guid]::NewGuid().ToString("N") + [guid]::NewGuid().ToString("N")
- ```
- ## Configuration (appsettings.json)
- Two required keys + one required value — `DBPilot:ConnectionString`, `DBPilot:PlatformEngine`, and a master key (`Auth:Secret` or env `DBPILOT_MASTER_KEY`). Missing → startup error; everything else is optional with defaults.
- | Key | Required | Notes |
- |---|---|---|
- | `DBPilot:ConnectionString` | **yes** | metadata DB connection string; schema auto-created on startup |
- | `DBPilot:PlatformEngine` | **yes** | `sqlserver` / `mysql` / `postgresql` / `sqlite`; in code: `o.PlatformEngine = DbpilotEngine.SqlServer`. Independent of which engines you monitor |
- | `DBPilot:Auth:Secret` | **yes** (or env) | master key — signs login cookies, encrypts instance credentials; ≥32-char random string |
- | `DBPILOT_MASTER_KEY` (env) | **yes** (or Secret) | env form of the master key, container-friendly; config value takes precedence |
- | `DBPilot:Auth:Username` / `PasswordHash` | no | login account (default `admin`) / password hash (default `dbpilot@2026`) |
- | `DBPilot:Mcp:ApiKey` | no | MCP Server switch (non-empty = on) |
- | `DBPilot:Roles` | no | process roles (Web / Collector, both by default); multi-process = 1 collector + N web fronts — two collectors on one metadata DB double-collect |
- | `DBPilot:Jobs` | no | per-collector switch & cron; empty value = disable that collector |
- | `DBPilot:Retention` / `DBPilot:Collect` | no | retention days (auto-clean) / parallelism & backoff |
- | `DBPilot:AutoInitSchema` | no | auto-create schema on startup (default true; set false when DBAs own the schema); versioned incremental migrations (`dbpilot_schema_version`) run alongside on upgrade |
- | `DBPilot:TopSqlExcludePatterns` | no | Top SQL noise filter (LIKE patterns); default set built in, explicit empty array clears it |
- Monitored instances are never configured here — they live in the UI, credentials encrypted in the metadata DB.
- ## Building from source
- Additionally requires **Node.js 20+** (the embedded web UI comes from the frontend build; NuGet packages ship it prebuilt).
- ```bash
- git clone https://github.com/SkyChenSky/DBPilot.git
- cd DBPilot
- scripts\run\setup.bat # frontend build + compile + sample appsettings.json (default SqlServer host; pass MySql / Sqlite / PostgreSql to switch)
- # edit samples/DBPilot.Sample.SqlServer/appsettings.json — fill in the connection string and Auth:Secret
- dotnet run --project samples/DBPilot.Sample.SqlServer # → http://localhost:5200
- ```
- Non-Windows / manual equivalent:
- ```bash
- cd web && npm install && npm run build
- cd .. && dotnet build
- cp samples/DBPilot.Sample.SqlServer/appsettings.template.json samples/DBPilot.Sample.SqlServer/appsettings.json
- ```
- Day-to-day dev: `scripts\run\dev.bat`, or two terminals — `dotnet watch --project samples/DBPilot.Sample.SqlServer` (backend, Swagger at `/swagger`) + `cd web && npm run dev` (frontend → http://localhost:5173). Tests: `scripts\run\test.bat` (compile + unit tests + frontend build). Self-test load scripts in `scripts/test/` — never run them against production databases.
- ## Architecture
- ```mermaid
- flowchart LR
- subgraph browser["Browser"]
- ui["Web console"]
- end
- subgraph host["DBPilot service (single process)"]
- api["Web API + MCP Server"]
- sched["Background collectors (Quartz)"]
- webhost["Static UI hosting"]
- end
- subgraph platform["Metadata DB (schema auto-created)"]
- tables["Historical data"]
- end
- subgraph monitored["Monitored instances (any number)"]
- dmv["DMV / XE / pg_stat_* / performance_schema"]
- end
- ui --> webhost
- ui -->|API| api
- sched -->|scheduled sampling| dmv
- sched --> tables
- api --> tables
- api -->|real-time queries| dmv
- ```
- The browser talks only to the DBPilot service: real-time pages query instances directly, history pages read the metadata DB — both share the same data conventions (noise exclusion, fingerprinting, time windows).
- ## Features
- | Page | What it answers |
- |---|---|
- | **Overview** | instance health at a glance — metric cards with sparklines, recent events, Top SQL digest |
- | **Performance trends** | CPU / memory / PLE / QPS·TPS / IO / disk, 10s granularity (30-day retention, auto down-sampling); event overlay for deadlocks / slow SQL / plan changes |
- | **Performance insight** | Average Active Sessions decomposed into CPU / lock / IO / waits — which resource is saturated, and the SQL statements contributing the load |
- | **Top SQL** | real-time leaderboard + history trends, merged by fingerprint; one-click noise exclusion |
- | **Query plans** | plan versions snapshotted automatically; changes raise events with before/after comparison, plan tree and XML |
- | **Missing indexes** | optimizer recommendations ranked by impact, with CREATE scripts and overlap hints |
- | **Index usage / fragmentation** | unused-index detection with drop scripts; fragmentation scan with REBUILD / REORGANIZE scripts |
- | **Blocking analysis** | real-time blocking tree (head blocker, chain, wait times) + historical statistics |
- | **Deadlock analysis** | captured automatically; graph view of the cycle, statements and lock relationships |
- | **Slow query log** | above-threshold statements archived automatically — full text, duration, IO, fingerprint |
- ## Engine support matrix
- | Capability | SQL Server | MySQL 8.0+ | PostgreSQL 13+ |
- |---|---|---|---|
- | Sessions / blocking (real-time tree + history) | ✅ | ✅ | ✅ (`pg_stat_activity` + `pg_blocking_pids`) |
- | Top SQL (fingerprinted) | ✅ | ✅ (`performance_schema` digest) | ✅ (`pg_stat_statements`) |
- | Slow query log | ✅ (XE events) | ✅ (`mysql.slow_log` table) | ◐ template leaderboard |
- | Performance trends | ✅ | ◐ (no OS CPU/memory, PLE, compile counters) | ◐ (QPS is transaction-scope) |
- | Deadlock analysis | ✅ event details + graph | ❌ | ◐ trend only |
- | Query plan snapshots / change tracking | ✅ | ❌ | ❌ |
- | Missing index advice | ✅ | ❌ | ❌ |
- | Index usage | ✅ | ◐ (unused-index detection conservative) | ◐ (unused indexes reliably detectable) |
- | Fragmentation scan | ✅ | ❌ | ❌ |
- | Index disable script | ✅ | ✅ (`INVISIBLE`) | ❌ |
- | Disk usage | ✅ volume-level | ◐ database-level | ◐ database-level |
- **Metadata store**: SQL Server / MySQL / PostgreSQL / SQLite — independent from the monitored engines, any combination (SQLite = single-executable + single-file embedded deployment).
- Prerequisites are minimal and self-checked by the connection-test wizard: MySQL needs `performance_schema` + slow-log settings; PostgreSQL needs the `pg_stat_statements` extension; SQL Server works from 2008 up (deadlock capture rides the built-in `system_health` session). The wizard tells you exactly what to change when something is missing.
- ## AI diagnostics (MCP Server)
- A read-only MCP Server (`/mcp`, Streamable HTTP + API key) hands the platform's evidence — metrics, slow SQL, deadlocks, blocking, indexes — to AI agents as 11 read-only tools. Agents never connect to your databases directly; every call is audit-logged.
- Enable (off by default; non-empty `ApiKey` = on):
- ```json
- "DBPilot": { "Mcp": { "ApiKey": "a sufficiently random key" } }
- ```
- ```bash
- claude mcp add --transport http dbpilot http://localhost:5200/mcp --header "X-Api-Key: <your-key>"
- ```
- Then just ask: *"use dbpilot to check the instance load over the last hour — any SQL getting slower?"* Add `--scope user` to make it global.
- **Codex CLI** (`~/.codex/config.toml`):
- ```toml
- [mcp_servers.dbpilot]
- url = "http://localhost:5200/mcp"
- [mcp_servers.dbpilot.http_headers]
- X-Api-Key = "<your-key>"
- ```
- Example projects live in [`examples/`](examples/): `DBPilot.McpConsole` (command-line diagnostic console) and `DBPilot.Scenarios` (fault-drill project), both referencing the official NuGet packages.
- ## Overhead on monitored instances
- **<1% CPU, zero disk pressure.** Collectors read in-memory metadata views only — no business-table scans, no physical IO, no locks on user objects.
- | Collector | Frequency | Cost |
- |---|---|---|
- | Sessions / metrics / Top SQL delta / deadlocks / slow SQL | 10–60s | millisecond-level metadata queries; XE cursors near zero when idle |
- | Query plan snapshots | 5 min | plan XML fetched once per fingerprint (XML generation is the expensive part) |
- | Index snapshots (incl. fragmentation) | daily 03:10 | heaviest tick, scheduled at night |
- | History writes | — | always to the metadata DB, never to monitored instances |
- Built-in mitigations: XE predicates exclude the platform's own traffic, every cron is configurable, instances disable individually, failed connections back off. Same DMV-polling path as SQL Server's own `system_health` and AWS Performance Insights.
- Verify it yourself — after a day of running, the monitoring account's accumulated CPU seconds is the true cost:
- ```sql
- SELECT login_name, SUM(cpu_time)/1000 AS cpu_seconds_total
- FROM sys.dm_exec_sessions
- WHERE host_process_id IS NOT NULL
- GROUP BY login_name;
- ```
- ## Embedding via NuGet
- Published on [nuget.org](https://www.nuget.org/packages?q=DBPilot):
- ```bash
- dotnet add package DBPilot # metapackage: AspNetCore + all engines, one line
- # or pick what you need:
- dotnet add package DBPilot.AspNetCore # API / MCP / auth / scheduling / embedded frontend
- dotnet add package DBPilot.SqlServer # engines — SqlServer / MySql / PostgreSql (monitor + storage)
- dotnet add package DBPilot.MySql # and Sqlite (storage only, embedded deployments)
- dotnet add package DBPilot.Sqlite
- ```
- ```csharp
- using DBPilot.AspNetCore.Extension;
- using DBPilot.Core.Providers;
- var builder = WebApplication.CreateBuilder(args);
- builder.AddDBPilot(o =>
- {
- o.PlatformEngine = DbpilotEngine.SqlServer; // no default — set explicitly (or via DBPilot:PlatformEngine)
- // o.WebOnly(); // delegate sets only what you want to change
- });
- var app = builder.Build();
- app.UseDBPilot();
- app.Run();
- ```
- - **Zero wiring** — referenced engine packages register themselves (output-directory scan); instances route by their `engine` column. Explicit registration (`AddDbpilotSqlServer()`) mixes in and is required for single-file publishes.
- - Package graph: `DBPilot.AspNetCore → Core → Storage → Common`; engine packages depend on Core + Storage.
- - Frontend assets ship twice — `buildTransitive` targets copy into `wwwroot`, plus an embedded-manifest fallback so the UI works even with an empty wwwroot.
- - Fine-grained methods (`AddDbpilotWeb` / `AddDbpilotMcp` / `AddDbpilotQuartz` / ...) remain available.
- ## FAQ
- | Symptom | Fix |
- |---|---|
- | Startup warning `平台库结构初始化失败` (schema init failed) | check `DBPilot:ConnectionString` and DB reachability; ignorable without persistence |
- | Home page 404 / stale UI | frontend not built — `cd web && npm install && npm run build`, rebuild, restart |
- | Startup error "DBPilot 主密钥未配置" (master key missing) | set `DBPilot:Auth:Secret` or env `DBPILOT_MASTER_KEY` |
- | Collector error `The computed authentication tag did not match...` | master key doesn't match the stored credential ciphertext (changed after instances were registered) — re-enter each instance's password in the UI |
- | Performance insight empty | instance enabled and collecting? Insights need ~1 minute of samples |
- | Deadlock / slow SQL events not showing yet | event files buffer ~1 minute — wait and refresh |
- ## License
- [Apache-2.0](LICENSE.txt)
Ratings and comments
Sign in to rate this skill.
Sign inFree to use. If it’s helped, pass it on.