DEV Community

sen ki
sen ki

Posted on

One Platform for All Your Database Queries — Introducing dbquery

#database #devops #django #tooling

One Platform for All Your Database Queries — Introducing dbquery

tag ： devops selfhosted database django

The daily reality for ops and dev teams: Navicat open on one screen, DBeaver on another, Redis Desktop Manager somewhere else, MongoDB Compass somewhere else again — constantly switching. You want to let a QA engineer look up a production table, but you can't hand out the database password. New hires spend hours configuring client tools before they can do anything useful. dbquery solves all of this in one shot.

What Is dbquery?

dbquery is a lightweight, self-hosted database query platform built with Django 4.2 + Bootstrap 5. It runs entirely in the browser — no client installation required — and supports MySQL, TiDB, PostgreSQL, Redis, and MongoDB. It comes with a built-in role-based permission system so DBAs, developers, and QA engineers can safely share one tool.

One command to deploy, up and running in 5 minutes:

docker-compose up -d
Enter fullscreen mode Exit fullscreen mode

GitHub: https://github.com/kisen9102-lgtm/dbquery

Core Features

1. Cross-Instance Database Search

Can't remember which server a database lives on? Type the database name into the search box and dbquery scans all registered instances, returning matching database names, their host instances, table counts, sizes, and types — all in one table.

You can also search by IP + port to get an overview of all databases on a specific instance.

2. Online SQL Editor

Select an instance and database, then start querying — right in the browser.

  • Syntax highlighting: SQL mode for MySQL/TiDB/PostgreSQL; plain text mode for Redis/MongoDB — switches automatically
  • Object browser: left panel shows all tables (MySQL/PG) or collections (MongoDB); click to insert the name into the editor
  • Multi-statement execution: submit multiple statements at once; each returns its own result set
  • CSV export: export any result set with one click
  • Row limit: results are automatically truncated with a warning to prevent accidental overload
  • DB type indicator: the current connection type (MySQL / TiDB / PostgreSQL / Redis / MongoDB) is shown in the top-left corner at all times

Redis read-only command whitelist:

GET  MGET  KEYS  SCAN  TYPE  TTL  PTTL  EXISTS  STRLEN  GETRANGE
INFO  DBSIZE  TIME  HGET  HGETALL  HMGET  HKEYS  HVALS  HLEN
LRANGE  LLEN  LINDEX  SCARD  SMEMBERS  SRANDMEMBER  SISMEMBER
ZRANGE  ZRANGEBYSCORE  ZREVRANGE  ZCARD  ZSCORE  ZRANK
Enter fullscreen mode Exit fullscreen mode

MongoDB query syntax:

db.<collection>.find({"field": "value"})
db.<collection>.count_documents({"field": "value"})
db.<collection>.aggregate([{"$match": {...}}, {"$group": {...}}])
Enter fullscreen mode Exit fullscreen mode

3. Instance Management

Register, edit, and delete database instances from the management page:

  • Set instance name, IP, port, environment (dev / staging / production), and database type
  • Redis / MongoDB support per-instance credentials (username, password, authSource); MySQL / TiDB / PostgreSQL use a shared service account
  • Each instance has a Test Connection button so you can verify connectivity before saving
  • Color-coded type badges: MySQL (blue), TiDB (cyan), PostgreSQL (dark blue), Redis (red), MongoDB (green)

4. User Management and Access Control

A three-tier role system for precise access control:

Role Can Do Restrictions
root Everything, no limits —
admin Manage instances, users, and groups Cannot modify the root account
query Read-only SQL queries Can only see instances in their assigned groups; instance IP/port is hidden; system databases are blocked; only SELECTis allowed

When a query user opens the SQL editor, they only see the instances they've been granted access to — and they never see the IP address or port. Even if their account is compromised, an attacker cannot use it to connect directly to any database.

5. Group Management

Create user groups, add instances and query users to them. Groups are the core unit of access control:

  • One instance can belong to multiple groups
  • One user can belong to multiple groups
  • All instances in a group are visible to all query users in that group

This makes it easy to partition access by team or environment: the e-commerce team only sees e-commerce instances; the payments team only sees payments instances.

6. Multilingual UI

The interface supports Chinese / English with one click. The preference is saved in localStorage and restored on next visit.

How It Compares

Feature dbquery Navicat DBeaver Archery Yearning
Deployment Docker, no client Desktop install Desktop install Docker Docker
Multi-user ✅ Web, built-in ❌ Per-person install ❌ Per-person install ✅ ✅
MySQL ✅ ✅ ✅ ✅ ✅
TiDB ✅ Native label ⚠️ MySQL compat ⚠️ MySQL compat ⚠️ MySQL compat ⚠️ MySQL compat
PostgreSQL ✅ Multi-schema ✅ ✅ ✅ ❌
Redis ✅ Read-only whitelist ✅ (paid) ✅ (plugin) ❌ ❌
MongoDB ✅ Read-only queries ✅ (paid) ✅ (plugin) ❌ ❌
Role-based access ✅ 3 roles + groups ❌ Local tool ❌ Local tool ✅ ✅
Hide IP/port from query users ✅ ❌ ❌ ❌ ❌
Enforce read-only SQL ✅ ❌ ❌ ✅ ✅
Redis read-only whitelist ✅ ❌ ❌ ❌ ❌
Object browser ✅ ✅ ✅ ⚠️ Limited ⚠️ Limited
i18n ✅ CN/EN ✅ ✅ CN only CN only
Open source & free ✅ ❌ ($200+/yr) ✅ (community) ✅ ✅
No frontend build step ✅ — — ❌ ❌

Summary:

  • vs. Navicat / DBeaver: dbquery is a web app — naturally multi-user, no per-person setup. Its built-in permission system lets you safely give developers and QA engineers database access without handing out credentials.
  • vs. Archery / Yearning: those are heavy SQL review and approval workflow platforms — powerful but complex to deploy. dbquery is lighter, focused on being a safe online query tool without the workflow overhead. Better fit for small and mid-sized teams.
  • dbquery's unique differentiator: read-only access to Redis and MongoDB — nearly absent from comparable open-source tools.

Deployment

Prerequisites

  • Docker + Docker Compose
  • An external MySQL 8.0 instance as the platform metadata database (stores users, instance info)

Steps

1. Create the metadata database

CREATE DATABASE ops_db CHARACTER SET utf8mb4;
CREATE USER 'ops_user'@'%' IDENTIFIED BY 'your-password';
GRANT ALL PRIVILEGES ON ops_db.* TO 'ops_user'@'%';
FLUSH PRIVILEGES;
Enter fullscreen mode Exit fullscreen mode

2. Configure environment variables

cp .env.docker.example .env
# Edit .env with your metadata DB connection and query account credentials
Enter fullscreen mode Exit fullscreen mode

Key .env settings:

DBS_DB_HOST=host.docker.internal   # metadata DB host
DBS_DB_PORT=3306
DBS_DB_USER=ops_user
DBS_DB_PASSWORD=your-password
DBS_DB_NAME=ops_db

QUERY_DEFAULT_ACCOUNT=dbs_admin    # account used to query MySQL/TiDB/PG instances
QUERY_DEFAULT_PASSWORD=your-dbs-admin-password

SECRET_KEY=your-random-secret-key
Enter fullscreen mode Exit fullscreen mode

3. Start

docker-compose up -d
Enter fullscreen mode Exit fullscreen mode

On first start, the container automatically: waits for MySQL → runs migrations → creates the superuser → starts gunicorn.

4. Open

Visit http://localhost:8000. Default credentials: dbsroot / Dbs@Root2026

Real-World Usage Examples

Scenario 1: Register a new TiDB instance

  1. Log in as admin or root, go to Instance Management
  2. Click Add Instance, fill in: name tidb-prod-primary, IP 10.0.1.100, port 4000, env Production, type TiDB
  3. Click Test Connection to verify, then click Add
  4. The instance appears in the list with a cyan TiDB badge

Scenario 2: Give a QA engineer read-only access

  1. Go to User Management → Add User, set role to query
  2. Go to Group Management → create a group "QA Team"
  3. Add the relevant instances to the group, add the user to the group
  4. The QA engineer logs in and sees only the instances in "QA Team" — no IP addresses, SELECT only

Scenario 3: Find a database across all instances

  1. Go to Database Search
  2. Type order in the search box
  3. dbquery scans all instances and returns every database whose name contains order, with instance, type, table count, and size

Scenario 4: Query Redis

  1. Select a Redis instance in the SQL editor
  2. Top-left shows Redis; the object browser is hidden (Redis has no table structure)
  3. Select db0 from the database selector
  4. Enter a read-only command: 
   HGETALL user:1001
Enter fullscreen mode Exit fullscreen mode

or

   SCAN 0 MATCH user:* COUNT 100
Enter fullscreen mode Exit fullscreen mode
  1. Write commands (SET, DEL, etc.) are rejected immediately

Scenario 5: Query MongoDB

  1. Select a MongoDB instance
  2. Top-left shows MongoDB; the object browser lists collections with row counts
  3. Select a database and enter a query: 
   db.users.find({"age": 28})
Enter fullscreen mode Exit fullscreen mode

or aggregation:

   db.orders.aggregate([{"$group": {"_id": "$status", "count": {"$sum": 1}}}])
Enter fullscreen mode Exit fullscreen mode

Security Design

  • IP/port hidden from query users: even if an account is compromised, the attacker cannot connect directly to any database
  • Read-only enforcement: the backend validates every SQL statement; anything other than SELECT returns 403
  • Redis command whitelist: only pre-defined read-only commands are allowed; SET/DEL/FLUSHDB are rejected
  • MongoDB double validation: query structure is checked with regex first, then json.loads validates JSON — no eval(), no injection
  • CSRF protection: all write operations require a CSRF token
  • Session authentication: no JWT, no token leakage risk

Get It

If you find it useful, a ⭐ on GitHub goes a long way. Issues and PRs welcome.

dbquery — lightweight, secure, ready to deploy

Top comments (0)