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

> Discover how OpenSEO uses Drizzle ORM in a multi-provider architecture to manage database operations seamlessly across Cloudflare D1 and PostgreSQL for type-safe queries and atomic transactions.

- Repository: [Every App/open-seo](https://github.com/every-app/open-seo)
- Tags: architecture
- Published: 2026-08-05

---

**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`](https://github.com/every-app/open-seo/blob/main/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`](https://github.com/every-app/open-seo/blob/main/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`](https://github.com/every-app/open-seo/blob/main/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.

```typescript
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`](https://github.com/every-app/open-seo/blob/main/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`](https://github.com/every-app/open-seo/blob/main/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.

```typescript
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`](https://github.com/every-app/open-seo/blob/main/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`](https://github.com/every-app/open-seo/blob/main/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.

```typescript
// 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`](https://github.com/every-app/open-seo/blob/main/src/db/schema.ts) and [`src/db/index.ts`](https://github.com/every-app/open-seo/blob/main/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`](https://github.com/every-app/open-seo/blob/main/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`](https://github.com/every-app/open-seo/blob/main/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`](https://github.com/every-app/open-seo/blob/main/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`](https://github.com/every-app/open-seo/blob/main/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`](https://github.com/every-app/open-seo/blob/main/src/db/index.ts) and [`src/db/provider.ts`](https://github.com/every-app/open-seo/blob/main/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.