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-d1npm install -D prisma wrangler2. Create D1 Database
# Login to Cloudflarewrangler login
# Create a new D1 databasewrangler d1 create adblock-storage
# Note the database_id from the output3. 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 schemanpx prisma generate --schema=prisma/schema.d1.prisma6. Create Database Migrations
# Generate SQL migrationnpx prisma migrate diff \ --from-empty \ --to-schema-datamodel prisma/schema.d1.prisma \ --script > migrations/0001_init.sql
# Apply to local D1wrangler d1 execute adblock-storage --local --file=migrations/0001_init.sql
# Apply to remote D1wrangler d1 execute adblock-storage --file=migrations/0001_init.sql7. Create D1 Storage Adapter
See src/storage/D1StorageAdapter.ts for the complete implementation.
Usage in Cloudflare Workers
Worker Entry Point
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
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 serverwrangler dev
# With local D1 databasewrangler dev --local --persistLocal D1 Testing
# Execute SQL on local D1wrangler d1 execute adblock-storage --local --command="SELECT * FROM storage_entries"
# Export local databasewrangler d1 export adblock-storage --local --output=backup.sqlMigration from Prisma/SQLite
Export Data from SQLite
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
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:
- Create a new table with the desired schema.
- Copy all existing rows.
- Drop the original table.
- 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.sqlMigration: 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")inprisma/schema.d1.prisma. Earlier references to a table namedUserin 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 lookupsource- Filter cache queriesconfigName- Compilation historyexpiresAt- TTL cleanup queriestimestamp- Time-series queries
Query Optimization
// Use batch operations when possibleconst 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 setsconst 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 firstlet 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 environmentwrangler deploy --env developmentEnvironment Variables
Set via wrangler or Cloudflare Dashboard:
wrangler secret put CACHE_TTLwrangler secret put DEBUGCI/CD Integration
name: Deploy to Cloudflareon: 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 chunksconst CHUNK_SIZE = 500000; // 500KB chunksconst 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.tomlhas correct[[d1_databases]]configuration
“Query timeout”
- Optimize query or add pagination
- Check for missing indexes
Local vs Remote mismatch
- Ensure migrations applied to both:
--localand remote
Debug Commands
# List all tableswrangler d1 execute adblock-storage --command="SELECT name FROM sqlite_master WHERE type='table'"
# Check table schemawrangler d1 execute adblock-storage --command=".schema storage_entries"
# Count entrieswrangler d1 execute adblock-storage --command="SELECT COUNT(*) FROM storage_entries"