Skip to content

Worker Implementation

Jump to bottom
Temp edited this page Feb 11, 2026 · 3 revisions

Worker Implementation

Cloudflare Worker backend architecture and implementation details.

Overview

The D1 Manager backend is a Cloudflare Worker that handles all API requests, authentication, and D1 database operations. It runs on Cloudflare's edge network for low-latency global access.

Key Responsibilities:

  • API endpoint handling
  • JWT authentication validation
  • D1 database operations via REST API
  • CORS management
  • Error handling
  • Mock data for local development

Worker Architecture

Entry Point

File: worker/index.ts

export default {
  async fetch(request: Request, env: Env): Promise<Response> {
    try {
      return await handleApiRequest(request, env);
    } catch (err) {
      console.error("[Worker] Unhandled error:", err);
      return new Response(
        JSON.stringify({
          error: "Internal Server Error",
        }),
        { status: 500 },
      );
    }
  },
};

Flow:

  1. Request arrives at worker
  2. handleApiRequest() processes it
  3. Response returned to client
  4. Errors caught and handled

Request Handling

Main Handler:

async function handleApiRequest(request: Request, env: Env): Promise<Response> {
  const url = new URL(request.url);
  const corsHeaders = getCorsHeaders(request);

  // Handle CORS preflight
  if (request.method === "OPTIONS") {
    return new Response(null, { status: 204, headers: corsHeaders });
  }

  // Detect local development
  const isLocalDev =
    url.hostname === "localhost" || url.hostname === "127.0.0.1";

  // Validate authentication (skip for localhost)
  if (!isLocalDev) {
    const userEmail = await validateAccessJWT(request, env);
    if (!userEmail) {
      return new Response(JSON.stringify({ error: "Unauthorized" }), {
        status: 401,
        headers: corsHeaders,
      });
    }
  }

  // Route to appropriate handler
  if (url.pathname.startsWith("/api/databases")) {
    return await handleDatabaseRoutes(
      request,
      env,
      url,
      corsHeaders,
      isLocalDev,
    );
  }

  if (url.pathname.startsWith("/api/tables")) {
    return await handleTableRoutes(request, env, url, corsHeaders, isLocalDev);
  }

  // ... more routes
}

Route Handlers

Modular Structure

Routes are organized by functionality:

worker/routes/
├── databases.ts     # Database CRUD operations
├── tables.ts        # Table operations + columns
├── queries.ts       # SQL query execution
├── saved-queries.ts # Saved queries management
├── undo.ts          # Undo/rollback operations
├── jobs.ts          # Job history and events
└── time-travel.ts   # Time Travel bookmarks and checkpoints

databases.ts

Responsibilities:

  • List databases
  • Create database
  • Delete database
  • Rename database (migration-based)
  • Bulk export
  • Bulk import
  • Bulk optimize

Example Handler:

export async function handleDatabaseRoutes(
  request: Request,
  env: Env,
  url: URL,
  corsHeaders: Record<string, string>,
  isLocalDev: boolean,
): Promise<Response> {
  // List databases
  if (request.method === "GET" && url.pathname === "/api/databases") {
    if (isLocalDev) {
      return new Response(
        JSON.stringify({
          result: MOCK_DATABASES,
          success: true,
        }),
        { headers: corsHeaders },
      );
    }

    // Production: Call D1 API
    const response = await fetch(
      `https://api.cloudflare.com/client/v4/accounts/${env.ACCOUNT_ID}/d1/database`,
      {
        headers: {
          Authorization: `Bearer ${env.API_KEY}`,
          "Content-Type": "application/json",
        },
      },
    );

    const data = await response.json();
    return new Response(JSON.stringify(data), { headers: corsHeaders });
  }

  // Create database
  if (request.method === "POST" && url.pathname === "/api/databases") {
    const { name } = await request.json();

    // Production: Call D1 API
    const response = await fetch(
      `https://api.cloudflare.com/client/v4/accounts/${env.ACCOUNT_ID}/d1/database`,
      {
        method: "POST",
        headers: {
          Authorization: `Bearer ${env.API_KEY}`,
          "Content-Type": "application/json",
        },
        body: JSON.stringify({ name }),
      },
    );

    const data = await response.json();
    return new Response(JSON.stringify(data), { headers: corsHeaders });
  }

  // ... more endpoints
}

tables.ts

Responsibilities:

  • List tables
  • Get table schema
  • Get table data (with pagination/filtering)
  • Get indexes
  • Get dependencies
  • Create table
  • Rename table
  • Clone table
  • Export table
  • Delete table
  • Column operations (add, rename, modify, drop)
  • Simulate cascade impact

Key Features:

  • Server-side filtering
  • SQL injection protection
  • Batch operations
  • Progress tracking

Example: Row Filtering

// Build WHERE clause from filters
function buildWhereClause(filters: FilterCondition[]): string {
  const conditions: string[] = [];

  for (const filter of filters) {
    switch (filter.operator) {
      case "contains":
        conditions.push(`${filter.column} LIKE '%${escapeSQL(filter.value)}%'`);
        break;
      case "equals":
        conditions.push(`${filter.column} = '${escapeSQL(filter.value)}'`);
        break;
      case "gt":
        conditions.push(`${filter.column} > ${filter.value}`);
        break;
      // ... more operators
    }
  }

  return conditions.length > 0 ? `WHERE ${conditions.join(" AND ")}` : "";
}

queries.ts

Responsibilities:

  • Execute SQL queries
  • Execute batch queries
  • Get query history
  • Validate SQL (prevent DROP/DELETE without skip)
  • Track execution metrics

Example: Query Execution

export async function executeQuery(
  dbId: string,
  query: string,
  env: Env,
  skipValidation: boolean = false,
): Promise<QueryResult> {
  // Validate query (unless skipped)
  if (!skipValidation) {
    const dangerous = /\b(DROP|DELETE|TRUNCATE)\b/i.test(query);
    if (dangerous) {
      throw new Error(
        "Dangerous query detected. Check skip validation to proceed.",
      );
    }
  }

  // Execute query
  const startTime = Date.now();
  const response = await fetch(
    `https://api.cloudflare.com/client/v4/accounts/${env.ACCOUNT_ID}/d1/database/${dbId}/query`,
    {
      method: "POST",
      headers: {
        Authorization: `Bearer ${env.API_KEY}`,
        "Content-Type": "application/json",
      },
      body: JSON.stringify({ sql: query }),
    },
  );

  const data = await response.json();
  const duration = Date.now() - startTime;

  // Store in query history
  await env.DB.prepare(
    `
    INSERT INTO query_history (database_id, query, duration_ms, executed_at)
    VALUES (?, ?, ?, datetime('now'))
  `,
  )
    .bind(dbId, query, duration)
    .run();

  return {
    results: data.result[0].results,
    meta: {
      duration,
      rows_read: data.result[0].meta.rows_read,
      rows_written: data.result[0].meta.rows_written,
    },
  };
}

saved-queries.ts

Responsibilities:

  • List saved queries
  • Save new query
  • Update saved query
  • Delete saved query
  • Filter by database

Uses metadata database (d1-manager-metadata) via worker binding.

Utilities

auth.ts

JWT validation for Cloudflare Access:

import { jwtVerify, createRemoteJWKSet } from "jose";

export async function validateAccessJWT(
  request: Request,
  env: Env,
): Promise<string | null> {
  // Extract JWT from cookie
  const cookies = request.headers.get("Cookie") || "";
  const match = cookies.match(/CF_Authorization=([^;]+)/);
  if (!match) return null;

  const token = match[1];

  // Fetch public keys from Cloudflare Access
  const JWKS = createRemoteJWKSet(
    new URL(`${env.TEAM_DOMAIN}/cdn-cgi/access/certs`),
  );

  try {
    // Verify JWT
    const { payload } = await jwtVerify(token, JWKS, {
      issuer: env.TEAM_DOMAIN,
      audience: env.POLICY_AUD,
    });

    return payload.email as string;
  } catch (err) {
    console.error("[Auth] JWT verification failed:", err);
    return null;
  }
}

cors.ts

CORS header management:

export function getCorsHeaders(request: Request): Record<string, string> {
  const origin = request.headers.get("Origin") || "";

  const allowedOrigins = [
    "http://localhost:5173",
    "http://localhost:8787",
    // Production origin from request
  ];

  const allowOrigin = allowedOrigins.includes(origin) ? origin : "";

  return {
    "Access-Control-Allow-Origin": allowOrigin,
    "Access-Control-Allow-Methods": "GET, POST, PUT, PATCH, DELETE, OPTIONS",
    "Access-Control-Allow-Headers": "Content-Type, Authorization",
    "Access-Control-Allow-Credentials": "true",
    "Access-Control-Max-Age": "86400",
  };
}

helpers.ts

Common utility functions:

// SQL injection protection
export function escapeSQL(value: string): string {
  return value.replace(/'/g, "''");
}

// Generate unique ID
export function generateId(): string {
  return crypto.randomUUID();
}

// Format timestamp
export function formatTimestamp(date: Date): string {
  return date.toISOString();
}

// Validate database name
export function isValidDatabaseName(name: string): boolean {
  return (
    /^[a-z0-9-]{3,63}$/.test(name) &&
    !name.startsWith("-") &&
    !name.endsWith("-")
  );
}

database-tracking.ts

Track database access:

export async function trackDatabaseAccess(
  dbId: string,
  dbName: string,
  env: Env,
): Promise<void> {
  await env.DB.prepare(
    `
    INSERT INTO databases (database_id, database_name, last_accessed)
    VALUES (?, ?, datetime('now'))
    ON CONFLICT(database_id) 
    DO UPDATE SET last_accessed = datetime('now')
  `,
  )
    .bind(dbId, dbName)
    .run();
}

Environment & Bindings

Environment Variables (Secrets)

Accessed via env object:

interface Env {
  // Secrets (set via wrangler secret put)
  ACCOUNT_ID: string;
  API_KEY: string;
  TEAM_DOMAIN: string;
  POLICY_AUD: string;

  // D1 Binding
  DB: D1Database;
}

Setting Secrets:

npx wrangler secret put ACCOUNT_ID
npx wrangler secret put API_KEY
npx wrangler secret put TEAM_DOMAIN
npx wrangler secret put POLICY_AUD

D1 Binding

Metadata Database:

// Direct access via binding
const result = await env.DB.prepare(
  `
  SELECT * FROM query_history
  WHERE database_id = ?
  ORDER BY executed_at DESC
  LIMIT 100
`,
)
  .bind(dbId)
  .all();

User Databases:

// Access via REST API
const response = await fetch(
  `https://api.cloudflare.com/client/v4/accounts/${env.ACCOUNT_ID}/d1/database/${dbId}/query`,
  {
    method: "POST",
    headers: {
      Authorization: `Bearer ${env.API_KEY}`,
      "Content-Type": "application/json",
    },
    body: JSON.stringify({ sql: query }),
  },
);

Mock Data System

Local Development Detection

const isLocalDev = url.hostname === "localhost" || url.hostname === "127.0.0.1";

Mock Data Sources

Mock Databases:

const MOCK_DATABASES = [
  {
    uuid: "dev-db-uuid",
    name: "dev-database",
    version: "production",
    created_at: "2024-01-01T00:00:00Z",
    num_tables: 3,
  },
  // ... more mock databases
];

Mock Tables:

const MOCK_TABLES = {
  "dev-database": [
    { name: "users", type: "table", ncol: 5 },
    { name: "posts", type: "table", ncol: 7 },
    { name: "comments", type: "table", ncol: 4 },
  ],
};

Mock Data:

const MOCK_DATA = {
  users: [
    { id: 1, name: "Alice", email: "alice@example.com" },
    { id: 2, name: "Bob", email: "bob@example.com" },
    // ... more mock rows
  ],
};

Mock Response Logic

if (isLocalDev) {
  // Return mock data based on endpoint
  if (url.pathname === "/api/databases") {
    return new Response(
      JSON.stringify({
        result: MOCK_DATABASES,
        success: true,
      }),
    );
  }

  if (url.pathname.startsWith("/api/tables")) {
    const dbName = extractDatabaseName(url);
    return new Response(
      JSON.stringify({
        result: MOCK_TABLES[dbName] || [],
        success: true,
      }),
    );
  }
}

Error Handling

Layered Approach

1. Route Level:

try {
  const result = await performOperation();
  return new Response(JSON.stringify({ result }), { headers: corsHeaders });
} catch (error) {
  console.error("[Route] Error:", error);
  return new Response(
    JSON.stringify({
      error: error.message,
      success: false,
    }),
    {
      status: 500,
      headers: corsHeaders,
    },
  );
}

2. Worker Level:

export default {
  async fetch(request, env) {
    try {
      return await handleApiRequest(request, env);
    } catch (err) {
      console.error("[Worker] Unhandled error:", err);
      return new Response(
        JSON.stringify({
          error: "Internal Server Error",
        }),
        { status: 500 },
      );
    }
  },
};

Error Response Format

{
  "error": "Database not found",
  "success": false,
  "code": "DB_NOT_FOUND"
}

Performance Optimization

Edge Deployment

  • Deployed to 300+ Cloudflare locations
  • Sub-10ms latency globally
  • Automatic load balancing

Caching Strategy

Static Assets:

  • Cached aggressively by Cloudflare CDN
  • Cache headers set appropriately

API Responses:

  • Not cached (dynamic data)
  • Fresh data on every request

Query Optimization

// Batch multiple queries
const batch = [
  env.DB.prepare("SELECT * FROM table1"),
  env.DB.prepare("SELECT * FROM table2"),
];

const results = await env.DB.batch(batch);

Security Implementation

Input Validation

// Validate database name
if (!isValidDatabaseName(name)) {
  return new Response(
    JSON.stringify({
      error: "Invalid database name",
    }),
    { status: 400 },
  );
}

// Validate SQL
if (containsDangerousSQL(query) && !skipValidation) {
  return new Response(
    JSON.stringify({
      error: "Dangerous query detected",
    }),
    { status: 400 },
  );
}

SQL Injection Prevention

// Bad: String concatenation
const query = `SELECT * FROM users WHERE email = '${userEmail}'`;

// Good: Parameterized query
const query = "SELECT * FROM users WHERE email = ?";
const result = await db.prepare(query).bind(userEmail).all();

Authentication Enforcement

// Skip auth for localhost
if (!isLocalDev) {
  const userEmail = await validateAccessJWT(request, env);
  if (!userEmail) {
    return new Response(
      JSON.stringify({
        error: "Unauthorized",
      }),
      { status: 401 },
    );
  }
}

Development Configuration

wrangler.dev.toml

name = "d1-manager-dev"
main = "worker/index.ts"
compatibility_date = "2024-11-01"

# D1 Database binding
[[d1_databases]]
binding = "DB"
database_name = "d1-manager-metadata-dev"
database_id = "local-dev-id"

[dev]
port = 8787
local_protocol = "http"

Features:

  • Local development mode
  • Mock D1 database
  • No secrets required
  • Hot reload enabled

Deployment Configuration

wrangler.toml

name = "d1-manager"
main = "worker/index.ts"
compatibility_date = "2024-11-01"

# D1 Database binding
[[d1_databases]]
binding = "DB"
database_name = "d1-manager-metadata"
database_id = "your-actual-database-id"

# Routes (optional, for custom domain)
routes = [
  { pattern = "d1.yourdomain.com/*", zone_name = "yourdomain.com" }
]

# Assets binding (for serving frontend)
[assets]
directory = "dist"

Next Steps

Need Help? See Troubleshooting or open an issue.

D1 Manager Wiki

🚀 Getting Started

📚 Core Features

🔗 Relationships

🔍 Search & Analysis

📊 Observability

🔧 Advanced Features

⚙️ Development

🌐 Deployment

📖 Reference

Clone this wiki locally