# Advantages of Using Drizzle ORM with PostgreSQL/D1 in OpenSEO: Type Safety and Edge Performance

> Discover the advantages of Drizzle ORM with PostgreSQL/D1 in OpenSEO. Gain type safety, portability, and edge performance for your serverless applications.

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

---

**OpenSEO leverages Drizzle ORM with PostgreSQL and Cloudflare D1 to deliver compile-time type safety, environment portability, and request-scoped connection management optimized for serverless edge computing.**

OpenSEO is an open-source SEO management platform designed for Cloudflare Workers. By adopting **Drizzle ORM** as the unified data access layer, the project eliminates runtime schema mismatches while seamlessly supporting both local D1 (SQLite) development and PostgreSQL production deployments. This architecture solves critical edge computing constraints through dependency injection and request-scoped client lifecycles.

## Strong Type Safety At Compile Time

Drizzle generates fully-typed query helpers directly from the schema definitions in [`src/db/pg/schema.ts`](https://github.com/every-app/open-seo/blob/main/src/db/pg/schema.ts). Every table declaration includes typed columns, ensuring that queries such as `eq(project.id, …)` are validated during TypeScript compilation rather than at runtime.

This approach catches misspelled columns, incorrect data types, and invalid relationships before deployment, reducing production errors and eliminating the need for separate type definition files.

## Unified Codebase for PostgreSQL and D1 Environments

OpenSEO maintains a single codebase that runs on Cloudflare D1 locally and switches to PostgreSQL in production without modifying business logic. The [`src/lib/auth.ts`](https://github.com/every-app/open-seo/blob/main/src/lib/auth.ts) file dynamically selects the appropriate adapter by checking environment variables:

- **`drizzleAdapter(pgDb, …)`** for PostgreSQL production
- **`drizzleAdapter(d1Db, …)`** for D1 local development

This abstraction allows developers to test locally against SQLite while deploying to managed PostgreSQL services, ensuring consistent behavior across environments.

## Request-Scoped PostgreSQL Client Management

Cloudflare Workers cannot reuse database sockets across requests, creating lifecycle challenges for traditional connection pooling. OpenSEO solves this through a **dependency injection** pattern using `AsyncLocalStorage`.

In [`src/db/pg/client.ts`](https://github.com/every-app/open-seo/blob/main/src/db/pg/client.ts), the `pgDb` object is implemented as a Proxy that retrieves the active client from `pgClientStore`:

```typescript
// ── src/db/pg/client.ts ────────────────────────────────────────────────
import { AsyncLocalStorage } from "node:async_hooks";
import { drizzle } from "drizzle-orm/postgres-js";
import postgres from "postgres";

const pgClientStore = new AsyncLocalStorage<{
  sql: ReturnType<typeof postgres>;
  db: ReturnType<typeof drizzle>;
}>();

export const pgDb = new Proxy(
  {},
  {
    get(_target, prop, receiver) {
      const store = pgClientStore.getStore();
      if (!store) {
        throw new Error(
          "Postgres database accessed outside a request scope. " +
            "Wrap DB usage in withPgClient().",
        );
      }
      return Reflect.get(store.db, prop, receiver);
    },
  },
) as ReturnType<typeof drizzle>;

```

The `withPgClient()` wrapper ensures each request receives a dedicated client instance, preventing "I/O on a different request" errors:

```typescript
export async function withPgClient<T>(fn: () => Promise<T>): Promise<T> {
  if (process.env.DATABASE_PROVIDER !== "postgres") return fn();
  const sql = postgres(process.env.POSTGRES_DATABASE_URL!, {
    max: 1,
    connect_timeout: 10,
  });
  return pgClientStore.run({ sql, db: drizzle(sql, { schema }) }, fn);
}

```

This pattern guarantees that every database interaction uses a fresh connection with `max: 1`, optimized for the short-lived Worker environment.

## Automatic Retries and Connection Resilience

OpenSEO implements query resilience through the `withQueryRetries` decorator in [`src/db/pg/retry.ts`](https://github.com/every-app/open-seo/blob/main/src/db/pg/retry.ts). This wrapper intercepts transient failures and retries operations with exponential backoff, respecting the strict lifetime constraints of Cloudflare Workers while maintaining low latency for successful requests.

## Zero-Configuration Schema Migrations

Database schema evolution is managed entirely through TypeScript source code. The [`drizzle-pg.config.ts`](https://github.com/every-app/open-seo/blob/main/drizzle-pg.config.ts) configuration file points to [`src/db/pg/schema.ts`](https://github.com/every-app/open-seo/blob/main/src/db/pg/schema.ts), allowing Drizzle-Kit to generate migration SQL automatically.

This keeps schema definitions in version control and eliminates manual SQL maintenance, ensuring that TypeScript types remain synchronized with the actual database structure.

## Seamless Better-Auth Integration

The authentication layer in OpenSEO uses Drizzle adapters to provide type-safe access to user data, sessions, and API keys. In [`src/lib/auth.ts`](https://github.com/every-app/open-seo/blob/main/src/lib/auth.ts), the integration is configured as:

```typescript
// ── src/lib/auth.ts ───────────────────────────────────────────────────────
import { drizzleAdapter } from "better-auth/adapters/drizzle";
import { pgDb } from "@/db/pg/client";

export const authAdapter = drizzleAdapter(pgDb, {
  tables: {
    users: "users",
    sessions: "sessions",
    apiKeys: "api_keys",
  },
});

```

This configuration allows Better-Auth to leverage the same request-scoped `pgDb` instance used by the rest of the application, maintaining type consistency across authentication and business logic layers.

## Performance Optimization for Edge Computing

OpenSEO tunes PostgreSQL connections specifically for the Workers environment. The configuration in [`src/db/pg/client.ts`](https://github.com/every-app/open-seo/blob/main/src/db/pg/client.ts) uses the native `postgres` driver with `max: 1` and `connect_timeout: 10`, eliminating connection pool overhead while ensuring rapid fail-fast behavior for unavailable databases.

## Summary

OpenSEO’s implementation of Drizzle ORM with PostgreSQL and D1 provides:

- **Compile-time type safety** through schema-generated TypeScript definitions in [`src/db/pg/schema.ts`](https://github.com/every-app/open-seo/blob/main/src/db/pg/schema.ts)
- **Environment portability** allowing the same code to run on D1 (SQLite) and PostgreSQL via adapter switching
- **Request-scoped lifecycle management** using `AsyncLocalStorage` in [`src/db/pg/client.ts`](https://github.com/every-app/open-seo/blob/main/src/db/pg/client.ts) to prevent cross-request I/O errors
- **Automatic resilience** via `withQueryRetries` for handling transient connection failures
- **Zero-config migrations** through Drizzle-Kit configuration in [`drizzle-pg.config.ts`](https://github.com/every-app/open-seo/blob/main/drizzle-pg.config.ts)
- **Type-safe authentication** integration with Better-Auth using `drizzleAdapter`

## Frequently Asked Questions

### How does OpenSEO handle database connections in Cloudflare Workers?

OpenSEO creates a new PostgreSQL client for each request using `withPgClient()` in [`src/db/pg/client.ts`](https://github.com/every-app/open-seo/blob/main/src/db/pg/client.ts). This function stores the client in `AsyncLocalStorage`, ensuring that database queries always use the correct request-scoped connection and preventing socket reuse across concurrent Worker executions.

### Can I switch between D1 and PostgreSQL without changing application code?

Yes. The codebase detects the `DATABASE_PROVIDER` environment variable and automatically selects the appropriate Drizzle adapter in [`src/lib/auth.ts`](https://github.com/every-app/open-seo/blob/main/src/lib/auth.ts). Your business logic remains identical while the underlying driver switches between SQLite (D1) and PostgreSQL dialects.

### What authentication library works with Drizzle in OpenSEO?

OpenSEO uses **Better-Auth** with the `drizzleAdapter` imported from `better-auth/adapters/drizzle`. This adapter is configured in [`src/lib/auth.ts`](https://github.com/every-app/open-seo/blob/main/src/lib/auth.ts) to use the same `pgDb` proxy that powers the rest of the application, ensuring consistent type safety for users, sessions, and API keys.

### How are database schema changes managed in the project?

Schema changes are handled through Drizzle-Kit, configured in [`drizzle-pg.config.ts`](https://github.com/every-app/open-seo/blob/main/drizzle-pg.config.ts). The tool reads the TypeScript schema definitions from [`src/db/pg/schema.ts`](https://github.com/every-app/open-seo/blob/main/src/db/pg/schema.ts) and generates migration SQL automatically, keeping schema evolution in source control without requiring manual DDL scripts.