D1 SQL Studio

A management UI and REST API gateway for Cloudflare D1 — bringing your edge database to the outside world, built with Vue 3 + Naive UI + TypeScript + Vite.

💡 Motivation

Cloudflare D1 is built to live inside the Cloudflare ecosystem. Data flows in and out through Worker bindings and platform-native APIs — which works perfectly for edge applications, but leaves a gap: accessing D1 from the outside world requires writing custom Worker code for every use case.

D1 SQL Studio bridges that gap with a single, self-hosted Worker that exposes your database as a standardized REST API — authenticated, paginated, and fully queryable — so any external system (scripts, dashboards, data pipelines, BI tools) can interact with D1 in a consistent, predictable way. The management UI is the human-friendly layer on top of that same API.

✨ Features

🎨 Professional UI - Built with Vue 3 + Naive UI for a tooling-grade experience
📊 Table Management - Create, view, and delete tables with a visual builder and SQL editor
📝 Data Browser - Browse and manage table data with pagination, sorting, and search
⚡ SQL Query Editor - Execute custom SQL with CodeMirror 6, syntax highlighting, and autocompletion
🗂️ Query History - Persistent query history with search and filter
🔐 Secure Authentication - Multi-user API key management with KV storage
🎯 Visual Table Builder - Build table schemas with a GUI — real-time SQL preview
🔑 API Key Management - Generate, manage, and revoke API keys through the UI
🛡️ Security Hardened - SQL injection protection and identifier validation
🌐 Edge Computing - Runs on Cloudflare Workers for global performance
🔌 REST API - Complete REST API for programmatic access
🔀 Multi-Field Sorting - Sort data by multiple columns simultaneously
🔗 Multi-Table Joins - Structured JOIN queries with RESTful API

🏗️ Architecture

Backend (Cloudflare Worker)

Runtime: Cloudflare Workers
Database: Cloudflare D1 (SQLite-based)
API Keys Storage: Cloudflare KV
Static Assets: Workers Assets
Location: src/worker/

Frontend (Vue 3 SPA)

Framework: Vue 3 + Composition API
UI Library: Naive UI
State Management: Pinia
Routing: Vue Router
Editor: CodeMirror 6
Language: TypeScript
Bundler: Vite
Location: src/ui/

┌─────────────────┐
│   Cloudflare    │
│   Workers Edge  │
├─────────────────┤
│  Static Assets  │ ← Vite build output
│  (index.html)   │
├─────────────────┤
│   API Routes    │ ← REST API (/api/*)
│   (/api/*)      │
├─────────────────┤
│   D1 Database   │ ← SQLite (Data)
├─────────────────┤
│   KV Storage    │ ← API Keys
└─────────────────┘

📦 Installation

npm install

🛠️ Development

Local Development

Start the frontend development server (with HMR):

npm run dev

Access at http://localhost:5173

Start the Worker development server (in a separate terminal):

npm run dev:worker

Worker runs at http://localhost:8787

The frontend proxies /api/* requests to the Worker automatically.

Database Setup

Option 1: Configure in wrangler.toml (Local Development)

Create D1 database:

npx wrangler d1 create d1-sql-studio-db

Copy the database_id from the output and update wrangler.toml:

[[d1_databases]]
binding = "DB"
database_name = "d1-sql-studio-db"
database_id = "your-database-id-here"

Restart the dev server

Option 2: Cloudflare Dashboard (Production, Recommended)

Deploy your Worker
Go to Cloudflare Dashboard → Workers & Pages → Your Worker → Settings → Bindings
Add D1 binding: Variable name = DB, select/create your database
Save and wait a few seconds

API Keys Setup

API keys are managed through the UI and stored in Cloudflare KV.

First-time setup:

Deploy the app (see below)
Visit your app URL — you'll see the first-time setup screen
Create your first API key
Save the generated key — it's only shown once
Log in with your new key

🏗️ Build

# Build frontend only
npm run build:ui

# Type-check worker
npm run build:worker

# Full build (frontend + type check)
npm run build

🚀 Deployment

Prerequisites

1. Create D1 Database

npx wrangler d1 create d1-sql-studio

Copy the database_id and update wrangler.toml:

[[d1_databases]]
binding = "DB"
database_name = "d1-sql-studio"
database_id = "your-database-id-here"

2. Create KV Namespace for API Keys

npx wrangler kv:namespace create "API_KEYS"

Copy the id and update wrangler.toml:

[[kv_namespaces]]
binding = "API_KEYS"
id = "your-kv-id-here"

Deploy

npm run deploy

This builds the frontend and deploys to Cloudflare Workers.

First-Time Setup

Visit your Worker URL
See the First-Time Setup screen
Enter a name for your first API key and click Generate API Key
Copy and save the key securely — it won't be shown again
Click Continue to Dashboard

📁 Project Structure

d1-sql-studio/
├── src/
│   ├── worker/                  # Backend (Cloudflare Worker)
│   │   ├── index.ts            # Worker entry point
│   │   ├── router.ts           # API routing
│   │   ├── db.ts               # D1 database operations
│   │   ├── auth.ts             # Authentication
│   │   ├── apikeys.ts          # API key management (KV)
│   │   ├── security.ts         # Security validation
│   │   └── types.ts            # TypeScript types
│   └── ui/                      # Frontend (Vue 3 SPA)
│       ├── main.ts             # App entry point
│       ├── App.vue             # Root component (Naive UI providers)
│       ├── env.d.ts            # Vue module declarations
│       ├── router/
│       │   └── index.ts        # Vue Router + navigation guards
│       ├── stores/
│       │   ├── auth.ts         # Auth state (apiKey, login/logout)
│       │   ├── tables.ts       # Table list + selected table
│       │   └── notification.ts # showToast / showConfirm
│       ├── composables/
│       │   ├── useTableSchema.ts  # Schema loading with 5-min cache
│       │   └── useExport.ts       # CSV / JSON / SQL export
│       ├── layouts/
│       │   └── DashboardLayout.vue  # Sidebar + main content layout
│       ├── views/
│       │   ├── auth/
│       │   │   ├── LoginView.vue
│       │   │   └── FirstTimeSetupView.vue
│       │   ├── tables/TablesView.vue
│       │   ├── data/DataBrowserView.vue
│       │   ├── query/QueryEditorView.vue
│       │   └── keys/ApiKeysView.vue
│       ├── components/
│       │   ├── tables/
│       │   │   ├── CreateTableModal.vue
│       │   │   ├── VisualTableBuilder.vue  # GUI builder + SQL preview
│       │   │   └── EditTableModal.vue       # Columns / Rename / Indexes tabs
│       │   ├── data-browser/
│       │   │   ├── AddRowModal.vue
│       │   │   ├── EditRowModal.vue
│       │   │   └── ApiDocumentation.vue     # Collapsible REST API reference
│       │   ├── query-editor/
│       │   │   ├── SqlEditor.vue            # CodeMirror 6 wrapper
│       │   │   ├── ResultsTable.vue
│       │   │   ├── QueryHistory.vue
│       │   │   └── KeyboardShortcuts.vue
│       │   └── shared/
│       │       └── NullValue.vue
│       └── lib/                 # Pure TypeScript utilities (no framework deps)
│           ├── api.ts          # ApiClient class
│           ├── exportUtils.ts  # CSV / JSON / SQL export
│           ├── queryHistory.ts # localStorage query history
│           └── utils.ts
├── public/                      # Static assets
├── dist/                        # Build output
├── index.html                  # HTML template
├── vite.config.ts              # Vite configuration
├── tsconfig.json               # TypeScript config (UI)
├── tsconfig.worker.json        # TypeScript config (Worker)
├── wrangler.toml               # Cloudflare Workers config
└── package.json

🔧 API Usage

All API endpoints require the Authorization header:

Authorization: Bearer your-api-key

List Tables

GET /api/tables

Get Table Schema

GET /api/tables/:tableName/schema

Get Table Data

GET /api/tables/:tableName/rows?page=1&limit=50&sortBy=id&sortOrder=asc&search=keyword

Query Parameters:

page — Page number (default: 1)
limit — Rows per page (default: 50, max: 1000)
sortBy — Column to sort by (single field)
sortOrder — asc or desc (default: asc)
sort — Multi-field sort, format: field1:order1,field2:order2
search — Keyword search across all TEXT columns

Multi-field sort examples:

# Sort by name ascending, then created_at descending
GET /api/tables/users/rows?sort=name:asc,created_at:desc

Execute SQL Query

POST /api/query
Content-Type: application/json

{
  "sql": "SELECT * FROM users WHERE created_at > ? LIMIT ?",
  "params": ["2024-01-01", 10]
}

Allowed statements: SELECT, INSERT, UPDATE, DELETE, PRAGMA

Security: parameterized queries, multiple statements blocked, DDL not allowed via this endpoint.

Multi-Table Join Query

POST /api/join
Content-Type: application/json

{
  "baseTable": "users",
  "joins": [
    { "table": "orders", "type": "LEFT", "on": "users.id = orders.user_id" }
  ],
  "select": ["users.*", "COUNT(orders.id) as order_count"],
  "where": "users.created_at > ?",
  "groupBy": ["users.id"],
  "orderBy": "order_count DESC",
  "limit": 20,
  "params": ["2024-01-01"]
}

Parameter	Type	Required	Description
`baseTable`	string	✅	Base table name
`joins`	JoinConfig[]	✅	JOIN configurations (1–10 joins)
`select`	string[]	❌	Columns to select (default: `["*"]`)
`where`	string	❌	WHERE clause with `?` placeholders
`groupBy`	string[]	❌	GROUP BY columns
`having`	string	❌	HAVING clause
`orderBy`	string	❌	ORDER BY clause
`limit`	number	❌	Max records (max: 1000)
`offset`	number	❌	Pagination offset
`params`	any[]	❌	Values for WHERE/HAVING

Table CRUD

POST   /api/tables                      # Create table (body: { sql })
DELETE /api/tables/:tableName           # Drop table
POST   /api/tables/:tableName/rows      # Insert row
PUT    /api/tables/:tableName/rows/:id  # Update row
DELETE /api/tables/:tableName/rows/:id  # Delete row

Column Management

POST   /api/tables/:tableName/columns/:columnName   # Add column
PUT    /api/tables/:tableName/columns/:columnName   # Rename column
DELETE /api/tables/:tableName/columns/:columnName   # Drop column
PUT    /api/tables/:tableName/rename                # Rename table

Index Management

GET    /api/tables/:tableName/indexes                     # List indexes
GET    /api/tables/:tableName/indexes/:indexName/columns  # Index columns
POST   /api/tables/:tableName/indexes                     # Create index
DELETE /api/tables/:tableName/indexes/:indexName          # Drop index

API Key Management

GET    /api/keys          # List keys
POST   /api/keys          # Create key (body: { name, description? })
DELETE /api/keys/:id      # Delete key
GET    /api/keys/status   # Check if any keys exist (unauthenticated)

📝 Scripts Reference

Command	Description
`npm run dev`	Start frontend dev server (port 5173)
`npm run dev:worker`	Start Worker dev server (port 8787)
`npm run build`	Full build (UI + type check)
`npm run build:ui`	Build frontend only
`npm run build:worker`	Type-check Worker
`npm run preview`	Preview production build
`npm run deploy`	Build UI + deploy to Cloudflare

🎯 Tech Stack

Frontend:

🟢 Vue 3 — Composition API
🎨 Naive UI — Professional component library
🍍 Pinia — State management
🔀 Vue Router — Client-side routing
📝 CodeMirror 6 — SQL editor with syntax highlighting
📘 TypeScript 5
⚡ Vite 5

Backend:

☁️ Cloudflare Workers — Edge computing
🗄️ Cloudflare D1 — SQLite database
📦 Workers Assets — Static asset serving

Tools:

🔨 Wrangler — Cloudflare CLI
📦 npm

🔒 Security Features

API Key Management — Multi-user support, bcrypt-hashed storage in KV
SQL Injection Protection — Identifier validation with strict regex
Query Whitelisting — Only SELECT/INSERT/UPDATE/DELETE/PRAGMA via /api/query
Identifier Quoting — All SQL identifiers automatically quoted
Same-Origin CORS — API restricted to same-origin requests by default
Session Persistence — Auto-login from localStorage, cleared on logout

🐛 Troubleshooting

"DATABASE_NOT_BOUND" Error

Create D1 database: npx wrangler d1 create d1-sql-studio
Update database_id in wrangler.toml
Redeploy: npm run deploy

First-Time Setup Screen Not Appearing

Create KV namespace: npx wrangler kv:namespace create "API_KEYS"
Update wrangler.toml with namespace ID
Redeploy and clear browser cache

Build Errors

rm -rf node_modules package-lock.json
npm install
npm run build

TypeScript Errors

tsc --noEmit                           # Frontend
tsc --project tsconfig.worker.json    # Worker

🤝 Contributing

Contributions are welcome! Please feel free to submit a Pull Request.

📄 License

MIT License — see LICENSE for details.

🙏 Acknowledgments

Built for Cloudflare Workers
Powered by Cloudflare D1
UI with Vue 3 + Naive UI
Editor by CodeMirror
Bundled by Vite

Star ⭐ this repo if you find it useful!

Name		Name	Last commit message	Last commit date
Latest commit History 96 Commits
examples		examples
public		public
src		src
.dev.vars.example		.dev.vars.example
.gitignore		.gitignore
CHANGELOG.md		CHANGELOG.md
LICENSE		LICENSE
README.md		README.md
index.html		index.html
package-lock.json		package-lock.json
package.json		package.json
tsconfig.json		tsconfig.json
tsconfig.worker.json		tsconfig.worker.json
vite.config.ts		vite.config.ts
wrangler.toml		wrangler.toml

Folders and files

Latest commit

History

Repository files navigation

D1 SQL Studio

💡 Motivation

✨ Features

🏗️ Architecture

Backend (Cloudflare Worker)

Frontend (Vue 3 SPA)

📦 Installation

🛠️ Development

Local Development

Database Setup

API Keys Setup

🏗️ Build

🚀 Deployment

Prerequisites

1. Create D1 Database

2. Create KV Namespace for API Keys

Deploy

First-Time Setup

📁 Project Structure

🔧 API Usage

List Tables

Get Table Schema

Get Table Data

Execute SQL Query

Multi-Table Join Query

Table CRUD

Column Management

Index Management

API Key Management

📝 Scripts Reference

🎯 Tech Stack

🔒 Security Features

🐛 Troubleshooting

"DATABASE_NOT_BOUND" Error

First-Time Setup Screen Not Appearing

Build Errors

TypeScript Errors

🤝 Contributing

📄 License

🙏 Acknowledgments

About

Resources

License

Uh oh!

Stars

Watchers

Forks

Releases

Packages 0

Uh oh!

Contributors

Uh oh!

Languages

Packages