Cloudflare D1 Integration Guide

Cloudflare D1 Integration Guide

Complete guide for using Prisma with Cloudflare D1 in the bloqr-backend project.

Overview

Cloudflare D1 is a serverless SQLite database that runs at the edge, offering:

• Global distribution - Data replicated across Cloudflare’s edge network
• SQLite compatibility - Familiar SQL syntax and tooling
• Serverless - No infrastructure management
• Low latency - Edge-first architecture
• Cost effective - Pay-per-use pricing model

Prerequisites

• Cloudflare account with Workers enabled
• Wrangler CLI installed (npm install -g wrangler)
• Node.js 18+ or Deno

Quick Start

1. Install Dependencies

npm install @prisma/client @prisma/adapter-d1
npm install -D prisma wrangler

2. Create D1 Database

# Login to Cloudflare
wrangler login

# Create a new D1 database
wrangler d1 create adblock-storage

# Note the database_id from the output

3. Configure wrangler.toml

Create or update wrangler.toml in your project root:

name = "bloqr-backend"
main = "src/worker.ts"
compatibility_date = "2024-01-01"

[[d1_databases]]
binding = "DB"
database_name = "adblock-storage"
database_id = "YOUR_DATABASE_ID_HERE"

4. Create D1 Prisma Schema

Create prisma/schema.d1.prisma:

generator client {
  provider        = "prisma-client-js"
  previewFeatures = ["driverAdapters"]
}

datasource db {
  provider = "sqlite"
  url      = "file:./dev.db"
}

model StorageEntry {
  id        String   @id @default(cuid())
  key       String   @unique
  data      String
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
  expiresAt DateTime?
  tags      String?

  @@index([key])
  @@index([expiresAt])
  @@map("storage_entries")
}

model FilterCache {
  id        String   @id @default(cuid())
  source    String   @unique
  content   String
  hash      String
  etag      String?
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
  expiresAt DateTime?

  @@index([source])
  @@index([expiresAt])
  @@map("filter_cache")
}

model CompilationMetadata {
  id          String   @id @default(cuid())
  configName  String
  timestamp   DateTime @default(now())
  sourceCount Int
  ruleCount   Int
  duration    Int
  outputPath  String?

  @@index([configName])
  @@index([timestamp])
  @@map("compilation_metadata")
}

model SourceSnapshot {
  id          String   @id @default(cuid())
  source      String
  timestamp   DateTime @default(now())
  contentHash String
  ruleCount   Int
  ruleSample  String?
  etag        String?
  isCurrent   Int      @default(1)

  @@unique([source, isCurrent])
  @@index([source])
  @@index([timestamp])
  @@map("source_snapshots")
}

model SourceHealth {
  id                  String   @id @default(cuid())
  source              String   @unique
  status              String
  totalAttempts       Int      @default(0)
  successfulAttempts  Int      @default(0)
  failedAttempts      Int      @default(0)
  consecutiveFailures Int      @default(0)
  averageDuration     Float    @default(0)
  averageRuleCount    Float    @default(0)
  lastAttemptAt       DateTime?
  lastSuccessAt       DateTime?
  lastFailureAt       DateTime?
  recentAttempts      String?
  updatedAt           DateTime @updatedAt

  @@index([source])
  @@index([status])
  @@map("source_health")
}

model SourceAttempt {
  id        String   @id @default(cuid())
  source    String
  timestamp DateTime @default(now())
  success   Int      @default(0)
  duration  Int
  error     String?
  ruleCount Int?
  etag      String?

  @@index([source])
  @@index([timestamp])
  @@map("source_attempts")
}

5. Generate Prisma Client

# Generate with D1 schema
npx prisma generate --schema=prisma/schema.d1.prisma

6. Create Database Migrations

# Generate SQL migration
npx prisma migrate diff \
  --from-empty \
  --to-schema-datamodel prisma/schema.d1.prisma \
  --script > migrations/0001_init.sql

# Apply to local D1
wrangler d1 execute adblock-storage --local --file=migrations/0001_init.sql

# Apply to remote D1
wrangler d1 execute adblock-storage --file=migrations/0001_init.sql

7. Create D1 Storage Adapter

See src/storage/D1StorageAdapter.ts for the complete implementation.

Usage in Cloudflare Workers

Worker Entry Point

src/worker.ts

import { PrismaClient } from '@prisma/client';
import { PrismaD1 } from '@prisma/adapter-d1';
import { D1StorageAdapter } from './storage/D1StorageAdapter';

export interface Env {
    DB: D1Database;
}

export default {
    async fetch(request: Request, env: Env): Promise<Response> {
        // Create Prisma client with D1 adapter
        const adapter = new PrismaD1(env.DB);
        const prisma = new PrismaClient({ adapter });

        // Create storage adapter
        const storage = new D1StorageAdapter(prisma);

        // Example: Cache a filter list
        await storage.cacheFilterList(
            'https://example.com/filters.txt',
            ['||ad.example.com^'],
            'hash123',
        );

        // Example: Get cached filter
        const cached = await storage.getCachedFilterList('https://example.com/filters.txt');

        return new Response(
            JSON.stringify({
                cached: cached !== null,
                ruleCount: cached?.content.length || 0,
            }),
            {
                headers: { 'Content-Type': 'application/json' },
            },
        );
    },
};

Type Definitions

src/types/env.d.ts

interface Env {
    DB: D1Database;
    CACHE_TTL?: string;
    DEBUG?: string;
}

D1 Storage Adapter API

The D1 adapter implements the same IStorageAdapter interface:

interface ID1StorageAdapter {
    // Core operations
    set<T>(key: string[], value: T, ttlMs?: number): Promise<boolean>;
    get<T>(key: string[]): Promise<StorageEntry<T> | null>;
    delete(key: string[]): Promise<boolean>;
    list<T>(options?: QueryOptions): Promise<Array<{ key: string[]; value: StorageEntry<T> }>>;

    // Filter caching
    cacheFilterList(source: string, content: string[], hash: string, etag?: string, ttlMs?: number): Promise<boolean>;
    getCachedFilterList(source: string): Promise<CacheEntry | null>;

    // Metadata
    storeCompilationMetadata(metadata: CompilationMetadata): Promise<boolean>;
    getCompilationHistory(configName: string, limit?: number): Promise<CompilationMetadata[]>;

    // Maintenance
    clearExpired(): Promise<number>;
    clearCache(): Promise<number>;
    getStats(): Promise<StorageStats>;
}

Local Development

Using Wrangler Dev

# Start local development server
wrangler dev

# With local D1 database
wrangler dev --local --persist

Local D1 Testing

# Execute SQL on local D1
wrangler d1 execute adblock-storage --local --command="SELECT * FROM storage_entries"

# Export local database
wrangler d1 export adblock-storage --local --output=backup.sql

Migration from Prisma/SQLite

Export Data from SQLite

scripts/export-from-sqlite.ts

import { PrismaStorageAdapter } from './src/storage/PrismaStorageAdapter.ts';

const storage = new PrismaStorageAdapter(logger, { type: 'prisma' });
await storage.open();

const entries = await storage.list({ prefix: [] });
const exportData = entries.map((e) => ({
    key: e.key.join('/'),
    data: JSON.stringify(e.value.data),
    createdAt: e.value.createdAt,
    expiresAt: e.value.expiresAt,
}));

await Deno.writeTextFile('export.json', JSON.stringify(exportData, null, 2));

Import to D1

scripts/import-to-d1.ts

const data = JSON.parse(await Deno.readTextFile('export.json'));

for (const entry of data) {
    await env.DB.prepare(`
    INSERT INTO storage_entries (id, key, data, createdAt, expiresAt)
    VALUES (?, ?, ?, ?, ?)
  `).bind(
            crypto.randomUUID(),
            entry.key,
            entry.data,
            entry.createdAt,
            entry.expiresAt,
        ).run();
}

Schema Migrations

SQLite (and therefore D1) does not support ALTER COLUMN or DROP NOT NULL directly. The standard approach is to rebuild the table:

1. Create a new table with the desired schema.
2. Copy all existing rows.
3. Drop the original table.
4. Rename the new table.

Migration files live in migrations/ and are numbered sequentially (e.g., 0004_users_email_nullable.sql).

Applying a migration

# Local D1 (development)
wrangler d1 execute bloqr-backend-app-db --local \
  --file=migrations/0004_users_email_nullable.sql

# Remote D1 (production)
wrangler d1 execute bloqr-backend-app-db --remote \
  --file=migrations/0004_users_email_nullable.sql

Migration: Make users.email nullable (0004)

Clerk supports users without email addresses (phone-only, passkey, OAuth without email, and the Clerk dashboard webhook tester). migrations/0004_users_email_nullable.sql removes the NOT NULL constraint from the email column in the users table.

Note: The table name is users (lowercase), as defined by @@map("users") in prisma/schema.d1.prisma. Earlier references to a table named User in commit messages were incorrect.

PRAGMA foreign_keys = OFF;

CREATE TABLE _users_new (
    id TEXT PRIMARY KEY,
    email TEXT UNIQUE,          -- was NOT NULL; now nullable
    display_name TEXT,
    role TEXT NOT NULL DEFAULT 'user',
    created_at TEXT NOT NULL DEFAULT (datetime('now')),
    updated_at TEXT NOT NULL DEFAULT (datetime('now')),
    clerk_user_id TEXT UNIQUE,
    tier TEXT NOT NULL DEFAULT 'free',
    first_name TEXT,
    last_name TEXT,
    image_url TEXT,
    email_verified INTEGER NOT NULL DEFAULT 0,
    last_sign_in_at TEXT
);

INSERT INTO _users_new
    SELECT id, email, display_name, role, created_at, updated_at,
           clerk_user_id, tier, first_name, last_name, image_url,
           email_verified, last_sign_in_at
    FROM users;

DROP TABLE users;

ALTER TABLE _users_new RENAME TO users;

CREATE INDEX IF NOT EXISTS idx_users_email ON users(email);
CREATE INDEX IF NOT EXISTS idx_users_clerk_user_id ON users(clerk_user_id);
CREATE INDEX IF NOT EXISTS idx_users_tier ON users(tier);

PRAGMA foreign_keys = ON;

Existing user data is safe — all current rows have emails and will be preserved by the INSERT INTO … SELECT *.

Performance Optimization

Indexing Strategy

The schema includes indexes on:

• key - Primary lookup
• source - Filter cache queries
• configName - Compilation history
• expiresAt - TTL cleanup queries
• timestamp - Time-series queries

Query Optimization

// Use batch operations when possible
const batch = await env.DB.batch([
  env.DB.prepare('INSERT INTO storage_entries ...').bind(...),
  env.DB.prepare('INSERT INTO storage_entries ...').bind(...),
]);

// Use pagination for large result sets
const entries = await prisma.storageEntry.findMany({
  take: 100,
  skip: page * 100,
  orderBy: { createdAt: 'desc' }
});

Caching Layer

For frequently accessed data, combine D1 with Workers KV:

// Check KV cache first
let data = await env.KV.get(key, 'json');

if (!data) {
    // Fall back to D1
    data = await storage.get(key);

    // Cache in KV for faster access
    await env.KV.put(key, JSON.stringify(data), { expirationTtl: 300 });
}

Monitoring and Debugging

D1 Analytics

Access D1 metrics in Cloudflare Dashboard:

• Query counts
• Read/write operations
• Storage usage
• Query latency

Query Logging

const prisma = new PrismaClient({
    adapter,
    log: ['query', 'info', 'warn', 'error'],
});

Error Handling

try {
    await storage.set(['key'], value);
} catch (error) {
    if (error.message.includes('D1_ERROR')) {
        console.error('D1 database error:', error);
        // Implement retry logic or fallback
    }
    throw error;
}

Deployment

Deploy to Cloudflare Workers

# Deploy worker (production — top-level default, no --env flag needed)
wrangler deploy

# Deploy to development environment
wrangler deploy --env development

Environment Variables

Set via wrangler or Cloudflare Dashboard:

wrangler secret put CACHE_TTL
wrangler secret put DEBUG

CI/CD Integration

.github/workflows/deploy.yml

name: Deploy to Cloudflare
on:
    push:
        branches: [main]

jobs:
    deploy:
        runs-on: ubuntu-latest
        steps:
            - uses: actions/checkout@v4

            - name: Setup Node
              uses: actions/setup-node@v4
              with:
                  node-version: '20'

            - name: Install dependencies
              run: npm ci

            - name: Generate Prisma
              run: npx prisma generate --schema=prisma/schema.d1.prisma

            - name: Run D1 migrations
              run: wrangler d1 migrations apply adblock-storage
              env:
                  CLOUDFLARE_API_TOKEN: ${{ secrets.CF_API_TOKEN }}

            - name: Deploy Worker
              run: wrangler deploy
              env:
                  CLOUDFLARE_API_TOKEN: ${{ secrets.CF_API_TOKEN }}

Limitations

D1 Constraints

• Row size: Maximum 1MB per row
• Database size: 10GB per database (free tier: 5GB)
• Query complexity: Complex JOINs may be slower
• Concurrent writes: Limited compared to distributed databases

Workarounds

For large filter lists:

// Split large content into chunks
const CHUNK_SIZE = 500000; // 500KB chunks
const chunks = splitIntoChunks(content, CHUNK_SIZE);

for (let i = 0; i < chunks.length; i++) {
    await storage.set(['cache', 'filters', source, `chunk-${i}`], chunks[i]);
}

Troubleshooting

Common Issues

“D1_ERROR: no such table”

• Run migrations: wrangler d1 execute adblock-storage --file=migrations/0001_init.sql

“BINDING_NOT_FOUND”

• Verify wrangler.toml has correct [[d1_databases]] configuration

“Query timeout”

• Optimize query or add pagination
• Check for missing indexes

Local vs Remote mismatch

• Ensure migrations applied to both: --local and remote

Debug Commands

# List all tables
wrangler d1 execute adblock-storage --command="SELECT name FROM sqlite_master WHERE type='table'"

# Check table schema
wrangler d1 execute adblock-storage --command=".schema storage_entries"

# Count entries
wrangler d1 execute adblock-storage --command="SELECT COUNT(*) FROM storage_entries"

References

• Cloudflare D1 Documentation
• Prisma D1 Adapter
• Wrangler CLI Reference
• D1 SQL Reference