How OpenSEO Manages Database Operations with Drizzle ORM: A Multi-Provider Architecture

OpenSEO abstracts all data access behind a provider-aware utility layer that enables the same TypeScript codebase to operate on either Cloudflare D1 (SQLite) or PostgreSQL via Hyperdrive, using Drizzle ORM for type-safe query building and custom batch utilities for atomic transactions.

OpenSEO is an open-source SEO management platform designed for edge deployment on Cloudflare Workers. The application leverages Drizzle ORM to handle database operations through a unified abstraction that supports both SQLite (via D1) and PostgreSQL backends without code duplication. This architecture allows repository functions to use the same type-safe query builder regardless of the underlying database provider.

Provider-Aware Schema Architecture

The foundation of OpenSEO's database layer is a runtime schema selection mechanism that swaps table definitions based on the active provider.

Runtime Schema Selection

In src/db/schema.ts, the application re-exports table definitions from both SQLite (*.sqlite) and PostgreSQL (*.pg) schema files. The system selects the appropriate schema at runtime based on the DATABASE_PROVIDER environment variable, ensuring the rest of the codebase imports a single canonical schema.

This approach guarantees that when developers import tables using import { projects } from "@/db/schema", they receive the correct table definition for the active provider without managing provider-specific imports throughout the application.

Database Provider Detection

The provider detection logic resides in src/db/provider.ts. The getDatabaseProvider() function inspects environment variables to determine whether the application should connect to D1 or PostgreSQL. This utility drives the conditional initialization of database clients throughout the request lifecycle.

Request-Scoped Database Client

OpenSEO manages database connections through a unified handle that abstracts provider-specific connection semantics.

Unified Database Handle

The src/db/index.ts file exports a single db object that represents either the D1 client (d1Db) or the PostgreSQL client (pgDb). The selection occurs once per request via getDatabaseProvider(), ensuring that all subsequent database operations in the request use the correct driver.

import { db } from "@/db";

// This query works identically for both D1 and PostgreSQL
const userProjects = await db
  .select()
  .from(projects)
  .where(eq(projects.organizationId, orgId));

Per-Request PostgreSQL Connections

Because Cloudflare Workers cannot reuse sockets across requests, src/db/pg/client.ts implements request-scoped PostgreSQL connections using AsyncLocalStorage. The withPgClient() helper wraps entry points that touch the database, guaranteeing that pgDb resolves to the current request's isolated client instance.

This pattern prevents socket reuse violations while maintaining the illusion of a persistent connection within each individual request context.

Atomic Batch Operations Across Providers

Write-heavy operations require special handling to maintain atomicity across the two different database providers.

The runBatch Utility

The src/db/runBatch.ts file exports a runBatch() function that provides atomic batch writes regardless of the underlying driver. When running against Cloudflare D1, the utility collects statements and executes them using db.batch([...]). For PostgreSQL, it wraps the same statements inside a db.transaction block to preserve atomicity and ordering.

import { runBatch } from "@/db/runBatch";

// Atomic bulk insert of rank snapshots
await runBatch((tx) =>
  rankData.map((snapshot) =>
    tx.insert(rankSnapshots).values({
      projectId: snapshot.projectId,
      keywordId: snapshot.keywordId,
      position: snapshot.position,
      timestamp: new Date()
    })
  )
);

Chunking Large Datasets

For operations that exceed D1's parameter limits, runBatch.ts also provides executeInBatches(). This utility splits large arrays into provider-appropriate chunks before calling runBatch() for each segment. Several repositories, including those handling audit pages and rank snapshots, use this pattern to efficiently insert or update thousands of rows without hitting platform limits.

Repository Pattern Implementation

All feature modules interact with the database through repository layers that remain provider-agnostic.

Example: ProjectRepository

The ProjectRepository in src/server/features/projects/repositories/ProjectRepository.ts demonstrates this abstraction in practice. Repository functions import tables from the canonical schema and use Drizzle's fluent query builder methods (db.select(), db.insert(), db.update()) without conditional logic for the underlying driver.

// Type-safe insert with returning clause
const [newProject] = await db
  .insert(projects)
  .values({
    id: crypto.randomUUID(),
    organizationId: orgId,
    name: projectName,
    createdAt: new Date()
  })
  .returning();

This implementation ensures that business logic remains decoupled from infrastructure concerns, allowing the same repository code to function correctly whether the application is configured for D1 or PostgreSQL.

Summary

• Provider abstraction: OpenSEO uses src/db/schema.ts and src/db/index.ts to present a unified interface that automatically selects SQLite or PostgreSQL table definitions at runtime based on the DATABASE_PROVIDER environment variable.
• Request-scoped connections: PostgreSQL connections in src/db/pg/client.ts use AsyncLocalStorage via withPgClient() to isolate sockets per request in Cloudflare Workers environments.
• Atomic batch operations: The runBatch() utility in src/db/runBatch.ts normalizes atomic writes across providers, using db.batch() for D1 and db.transaction() for PostgreSQL.
• Provider-agnostic repositories: Repository layers like ProjectRepository import the canonical schema and db handle, writing type-safe Drizzle queries without provider-specific branching logic.

Frequently Asked Questions

How does OpenSEO handle the difference between SQLite and PostgreSQL syntax?

OpenSEO relies on Drizzle ORM's dialect abstraction to handle syntax differences automatically. The schema files in src/db/schema.ts export provider-specific table definitions that account for type mappings (such as integer vs. serial primary keys), while the query builder API remains identical across both databases. Repository code uses the canonical schema imports, ensuring that Drizzle generates the correct SQL for the active provider at runtime.

Why does OpenSEO use AsyncLocalStorage for PostgreSQL connections?

AsyncLocalStorage in src/db/pg/client.ts solves Cloudflare Workers' constraint that prohibits socket reuse across requests. Because Workers are serverless and each request may execute on a different isolate, withPgClient() creates a new PostgreSQL connection for each request and stores it in AsyncLocalStorage. This guarantees that all database operations within a single request share the same connection while preventing cross-request contamination or socket errors.

What are the performance implications of using runBatch for bulk inserts?

The runBatch() utility optimizes bulk operations by respecting each provider's atomicity guarantees and parameter limits. For D1, it leverages the native batch() API to execute multiple statements in a single round-trip. For PostgreSQL, it uses transactions to ensure atomicity. The companion executeInBatches() function further optimizes large datasets by chunking operations to stay within D1's parameter limits, preventing request timeouts and database errors when inserting thousands of rows.

Can I use OpenSEO's database layer with other ORMs or raw SQL?

While the repository pattern in OpenSEO is built specifically for Drizzle ORM, the provider abstraction in src/db/index.ts and src/db/provider.ts could theoretically support other database clients. However, the type safety and query building features depend on Drizzle's API. Raw SQL execution would require bypassing the db abstraction and implementing separate connection management for each provider, which would sacrifice the "code once, run anywhere" benefits of the current architecture.

{
  "mcpServers": {
    "instagit": {
      "command": "npx",
      "args": ["-y", "instagit@latest"]
    }
  }
}