D1 SQL Studio

A modern, lightweight database management tool for Cloudflare D1, built with Preact + TypeScript + Vite.

✨ Features

🎨 Modern Web Interface - Built with Preact for optimal performance
📊 Table Management - Create, view, and delete database tables with visual builder
📝 Data Browser - Browse and manage table data with pagination
⚡ SQL Query Editor - Execute custom SQL queries with Ctrl+Enter shortcut
🔐 Secure Authentication - Multi-user API key management with KV storage
🎯 Visual Table Builder - Create tables with an intuitive drag-and-drop interface
🔑 API Key Management - Generate, manage, and revoke API keys through the UI
✅ Form Validation - Real-time validation with helpful error messages
🛡️ Security Hardened - SQL injection protection and input validation
🌐 Edge Computing - Runs on Cloudflare Workers for global performance
📦 Lightweight - Only ~10KB gzipped bundle size
🔌 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

This project uses a modern, component-based architecture:

Backend (Cloudflare Worker)

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

Frontend (Preact SPA)

Framework: Preact (~3KB React alternative)
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

# Install dependencies
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.

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
Edit wrangler.toml and uncomment the [[d1_databases]] section:

[[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
Navigate to Workers & Pages > Your Worker
Go to Settings > Bindings > D1 Database Bindings
Add binding: Variable name = DB, select/create your database
Save and wait a few seconds for the configuration to take effect

API Keys Setup

Important: API keys are now managed through the UI, not environment variables.

The app uses Cloudflare KV to store API keys, allowing you to:

Create multiple API keys with descriptions
Manage and revoke keys through the dashboard
Track when keys were last used
No need to redeploy when rotating keys

First-time setup:

Deploy the app (see deployment section below)
Visit your app URL - you'll see a first-time setup screen
Create your first API key through the UI
Save the generated key securely
Login with your new API key

Note: For local development, a fallback default key is used if no KV is configured.

🏗️ Build

# Build frontend only
npm run build:ui

# Type-check worker only
npm run build:worker

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

Build output:

Frontend: dist/ directory
Bundle size: ~28KB (~10KB gzipped)

🚀 Deployment

Prerequisites

Before deploying, you need to create two Cloudflare resources:

1. Create D1 Database

npx wrangler d1 create d1-sql-studio

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

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

2. Create KV Namespace for API Keys

npx wrangler kv:namespace create "API_KEYS"

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

[[kv_namespaces]]
binding = "API_KEYS"
id = "your-kv-id-here"  # Replace with your KV namespace ID

For preview environment (optional):

npx wrangler kv:namespace create "API_KEYS" --preview

Deploy to Cloudflare Workers

npm run deploy

This will:

Build the frontend (Vite)
Type-check the worker (TypeScript)
Deploy to Cloudflare Workers

First-Time Setup

After deployment:

Visit your Worker URL (e.g., https://d1-sql-studio.your-subdomain.workers.dev)
You'll see a First-Time Setup screen
Enter a name for your first API key (e.g., "Production Key")
Click Generate API Key
Important: Copy and save the generated key securely - you won't see it again!
Click Continue to Dashboard
You're now logged in and can start managing your database

Managing API Keys

After initial setup, you can manage API keys from the Dashboard:

Settings > API Keys - View all your API keys
Create New Key - Generate additional API keys for different users/apps
Delete - Revoke API keys instantly (no redeployment needed)
Track Usage - See when each key was last used

Alternative: Cloudflare Dashboard Setup

If you prefer using the Cloudflare Dashboard:

Go to Workers & Pages > Select your worker
Settings > Bindings
Add D1 Database Binding:
- Variable name: DB
- Select your D1 database
Add KV Namespace Binding:
- Variable name: API_KEYS
- Select your KV namespace
Save and redeploy

📁 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 (Preact SPA)
│       ├── components/     # React components
│       │   ├── shared/     # Shared components
│       │   │   ├── Modal.tsx
│       │   │   ├── Button.tsx
│       │   │   ├── Alert.tsx
│       │   │   ├── FormField.tsx
│       │   │   ├── Toast.tsx
│       │   │   └── ConfirmDialog.tsx
│       │   ├── Tables/     # Tables view
│       │   │   ├── TableList.tsx
│       │   │   ├── CreateTableModal.tsx
│       │   │   └── VisualTableBuilder.tsx
│       │   ├── DataBrowser/ # Data browser
│       │   │   ├── DataBrowser.tsx
│       │   │   ├── AddRowModal.tsx
│       │   │   └── EditRowModal.tsx
│       │   ├── QueryEditor/ # SQL query editor
│       │   ├── Login.tsx   # Login component
│       │   ├── FirstTimeSetup.tsx # Initial setup
│       │   ├── ApiKeyManagement.tsx # API key management
│       │   └── Dashboard.tsx # Main dashboard
│       ├── contexts/        # React contexts
│       │   └── NotificationContext.tsx
│       ├── hooks/           # Custom hooks
│       │   └── useFormValidation.ts
│       ├── lib/            # Utilities
│       │   ├── api.ts      # API client
│       │   └── utils.ts    # Helper functions
│       ├── App.tsx         # Root component
│       ├── main.tsx        # Entry point
│       └── styles.css      # Global styles
├── 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            # Dependencies & scripts

🔧 API Usage

All API endpoints require authentication via 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 (optional): Page number (default: 1)
limit (optional): Rows per page (default: 50, max: 1000)
sortBy (optional): Column name to sort by (single field)
sortOrder (optional): Sort direction - asc or desc (default: asc)
sort (optional): Multi-field sort - Format: field1:order1,field2:order2
search (optional): Search keyword (searches in all TEXT columns)

Examples:

# Basic pagination
GET /api/tables/users/rows?page=1&limit=50

# Single-field sort: Sort by ID descending
GET /api/tables/users/rows?sortBy=id&sortOrder=desc

# Single-field sort: Sort by name ascending
GET /api/tables/users/rows?sortBy=name&sortOrder=asc

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

# Multi-field sort: Sort by status, priority, and date
GET /api/tables/tasks/rows?sort=status:asc,priority:desc,due_date:asc

# Search + multi-field sort
GET /api/tables/users/rows?search=john&sort=last_name:asc,first_name:asc

Sort Parameter Priority:

If sort is provided, it takes priority (multi-field sorting)
Otherwise, sortBy + sortOrder are used (single-field sorting, backward compatible)

Execute SQL Query

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

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

Supported SQL Statements:

SELECT - Query data (supports JOINs, subqueries, etc.)
INSERT - Insert new records
UPDATE - Update existing records
DELETE - Delete records
PRAGMA - Database metadata queries

Security:

✅ Parameterized queries with ? placeholders
✅ Multiple statements blocked
✅ SQL comments blocked
❌ DDL operations (DROP, CREATE, ALTER) not allowed via this endpoint

Examples:

# Simple SELECT
POST /api/query
{"sql": "SELECT * FROM users LIMIT 10"}

# JOIN query
POST /api/query
{
  "sql": "SELECT u.name, o.total FROM users u INNER JOIN orders o ON u.id = o.user_id WHERE o.total > ?",
  "params": [100]
}

# Aggregate with GROUP BY
POST /api/query
{
  "sql": "SELECT category, COUNT(*) as count, AVG(price) as avg_price FROM products GROUP BY category"
}

# Subquery
POST /api/query
{
  "sql": "SELECT * FROM users WHERE id IN (SELECT user_id FROM orders WHERE total > ?)",
  "params": [500]
}

Multi-Table Join Query (NEW)

Structured, RESTful approach for multi-table JOINs:

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"]
}

Request Parameters:

Parameter	Type	Required	Description
`baseTable`	string	✅	Base table name
`joins`	JoinConfig[]	✅	Array of JOIN configurations (1-10 joins)
`select`	string[]	❌	Columns to select (default: `["*"]`, max: 50)
`where`	string	❌	WHERE clause with `?` placeholders
`groupBy`	string[]	❌	GROUP BY columns (max: 20)
`having`	string	❌	HAVING clause
`orderBy`	string	❌	ORDER BY clause (e.g., `"created_at DESC"`)
`limit`	number	❌	Max records to return (max: 1000)
`offset`	number	❌	Offset for pagination
`params`	any[]	❌	Parameter values for WHERE/HAVING

JoinConfig Object:

Field	Type	Required	Description
`table`	string	✅	Table name to join
`type`	'INNER' \| 'LEFT' \| 'RIGHT' \| 'CROSS'	✅	Join type
`on`	string	❌	Join condition (required except for CROSS JOIN)

Examples:

1. Basic INNER JOIN:

{
  "baseTable": "users",
  "joins": [
    {"table": "orders", "type": "INNER", "on": "users.id = orders.user_id"}
  ],
  "select": ["users.name", "orders.total", "orders.order_date"],
  "where": "orders.total > ?",
  "orderBy": "orders.order_date DESC",
  "limit": 50,
  "params": [100]
}

2. Multiple LEFT JOINs with Aggregation:

{
  "baseTable": "users",
  "joins": [
    {"table": "orders", "type": "LEFT", "on": "users.id = orders.user_id"},
    {"table": "addresses", "type": "LEFT", "on": "users.id = addresses.user_id"}
  ],
  "select": [
    "users.id",
    "users.name",
    "COUNT(orders.id) as order_count",
    "SUM(orders.total) as total_spent",
    "addresses.city"
  ],
  "groupBy": ["users.id", "users.name", "addresses.city"],
  "having": "COUNT(orders.id) > ?",
  "orderBy": "total_spent DESC",
  "limit": 20,
  "params": [5]
}

3. Product Catalog with Categories:

{
  "baseTable": "products",
  "joins": [
    {"table": "categories", "type": "INNER", "on": "products.category_id = categories.id"}
  ],
  "select": [
    "products.*",
    "categories.name as category_name"
  ],
  "where": "products.stock > ? AND products.active = ?",
  "orderBy": "products.created_at DESC",
  "params": [0, true]
}

Security Features:

✅ All table and column names validated
✅ Automatic SQL identifier quoting
✅ Dangerous characters blocked (;, --, /*, */)
✅ Parameterized WHERE/HAVING clauses
✅ Limits on joins (max 10), columns (max 50), and results (max 1000)

When to Use:

Use /api/join for structured, common JOIN patterns
Use /api/query for complex queries (subqueries, CTEs, window functions, etc.)

Create Table

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

{
  "sql": "CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT)"
}

Delete Table

DELETE /api/tables/:tableName

Insert Row

POST /api/tables/:tableName/rows
Content-Type: application/json

{
  "name": "John Doe",
  "email": "john@example.com"
}

Update Row

PUT /api/tables/:tableName/rows/:id
Content-Type: application/json

{
  "name": "Jane Doe"
}

Delete Row

DELETE /api/tables/:tableName/rows/:id

API Key Management

List API Keys

GET /api/keys

Response:

{
  "success": true,
  "data": [
    {
      "name": "Production Key",
      "description": "Main production API key",
      "createdAt": "2024-01-15T10:30:00Z",
      "lastUsedAt": "2024-01-20T15:45:00Z"
    }
  ]
}

Create API Key

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

{
  "name": "New Key",
  "description": "Optional description"
}

Response:

{
  "success": true,
  "data": {
    "name": "New Key",
    "key": "generated-api-key-here",
    "createdAt": "2024-01-20T16:00:00Z"
  }
}

Delete API Key

DELETE /api/keys/:keyName

📝 Scripts Reference

Command	Description
`npm run dev`	Start frontend dev server
`npm run dev:worker`	Start Worker dev server
`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`	Deploy to Cloudflare

🎯 Tech Stack

Frontend:

⚛️ Preact 10 - Lightweight React alternative (~3KB)
📘 TypeScript 5 - Type safety
⚡ Vite 5 - Fast bundler with HMR
🎨 CSS Variables - Themeable design system

Backend:

☁️ Cloudflare Workers - Edge computing
🗄️ Cloudflare D1 - SQLite database
📦 Workers Sites - Static asset serving

Tools:

🔨 Wrangler - Cloudflare CLI
📦 npm - Package manager

🔒 Security Features

Authentication & Authorization

API Key Management: Multi-user support with individual API keys
Secure Storage: API keys stored in Cloudflare KV with bcrypt hashing
Session Persistence: Auto-login with locally stored keys
Key Rotation: Revoke and regenerate keys without redeployment

Input Validation & Security

SQL Injection Protection: All table/column names validated with strict regex patterns
Query Whitelisting: Only SELECT, INSERT, UPDATE, DELETE, and PRAGMA statements allowed
Identifier Quoting: All SQL identifiers properly quoted to prevent injection
Input Sanitization: Comprehensive validation on all user inputs
Type Checking: Strong type validation for all data operations

CORS Policy

Same-Origin Only: CORS restricted to same-origin requests by default
The frontend and API must be served from the same domain
Modify src/worker/auth.ts if you need custom CORS rules

Best Practices

API Keys:
- Generate strong, unique keys for each user/application
- Add descriptions to track key usage
- Regularly review and revoke unused keys
- Never share API keys publicly or commit them to version control
Database Security:
- Query editor blocks dangerous operations (DROP, CREATE, ALTER, TRUNCATE)
- Use the Visual Table Builder for schema changes
- All identifiers are validated against SQL keywords
KV Namespace:
- Keep your KV namespace ID private
- Use separate namespaces for development and production

🐛 Troubleshooting

"DATABASE_NOT_BOUND" Error

The app will show a setup guide if no database is bound. Follow these steps:

Create a D1 database: npx wrangler d1 create d1-sql-studio
Copy the database ID and update wrangler.toml
Redeploy: npm run deploy
Or bind via Cloudflare Dashboard: Settings > Bindings > D1 Database Bindings
Refresh the page

"First-Time Setup" Screen Not Appearing

If you deployed without a KV namespace, the app will use a fallback authentication mode:

Create KV namespace: npx wrangler kv:namespace create "API_KEYS"
Update wrangler.toml with the namespace ID
Redeploy: npm run deploy
Clear your browser cache and revisit the app

Can't Login After Creating API Key

If you created an API key but can't login:

Make sure you copied the full API key (it's only shown once)
Check browser console for errors
Verify KV namespace is properly bound in Cloudflare Dashboard
Try creating a new API key from the First-Time Setup screen

API Keys Not Persisting

If your API keys disappear after deployment:

Verify KV namespace binding in wrangler.toml
Check that the binding name is exactly API_KEYS
Ensure you're not using --preview mode in production
Check Cloudflare Dashboard > KV to verify data is stored

Build Errors

If you encounter build errors:

# Clean install
rm -rf node_modules package-lock.json
npm install

# Rebuild
npm run build

TypeScript Errors

Make sure you're using the correct TypeScript configuration:

Frontend: tsconfig.json
Worker: tsconfig.worker.json

Run type checking separately:

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 the LICENSE file for details.

🙏 Acknowledgments

Built for Cloudflare Workers
Powered by Cloudflare D1
UI with Preact
Bundled by Vite

Star ⭐ this repo if you find it useful!

Name		Name	Last commit message	Last commit date
Latest commit History 71 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

✨ Features

🏗️ Architecture

Backend (Cloudflare Worker)

Frontend (Preact 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 to Cloudflare Workers

First-Time Setup

Managing API Keys

Alternative: Cloudflare Dashboard Setup

📁 Project Structure

🔧 API Usage

List Tables

Get Table Schema

Get Table Data

Execute SQL Query

Multi-Table Join Query (NEW)

Create Table

Delete Table

Insert Row

Update Row

Delete Row

API Key Management

List API Keys

Create API Key

Delete API Key

📝 Scripts Reference

🎯 Tech Stack

🔒 Security Features

Authentication & Authorization

Input Validation & Security

CORS Policy

Best Practices

🐛 Troubleshooting

"DATABASE_NOT_BOUND" Error

"First-Time Setup" Screen Not Appearing

Can't Login After Creating API Key

API Keys Not Persisting

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