# How to Configure Database Backends for OpenSEO (SQLite/PostgreSQL)

> Learn to configure database backends for OpenSEO. Set DATABASE_PROVIDER to d1 for SQLite or postgres for PostgreSQL and run migrations.

- Repository: [Every App/open-seo](https://github.com/every-app/open-seo)
- Tags: how-to-guide
- Published: 2026-09-10

---

**To configure database backends for OpenSEO, set the `DATABASE_PROVIDER` environment variable to either `d1` (SQLite) or `postgres`, then run the corresponding Drizzle migration and ensure your Hyperdrive binding is configured for PostgreSQL deployments.**

OpenSEO abstracts its storage layer behind a provider-agnostic architecture that supports both Cloudflare D1 (SQLite) and PostgreSQL backends. This guide explains how to configure database backends for OpenSEO by leveraging the runtime detection system implemented in the `every-app/open-seo` repository.

## Understanding the Database Provider Architecture

OpenSEO uses a single source of truth for database selection located in [`src/db/provider.ts`](https://github.com/every-app/open-seo/blob/main/src/db/provider.ts). The `getDatabaseProvider()` function reads the `DATABASE_PROVIDER` environment variable at runtime and returns either `"d1"` or `"postgres"`.

When `DATABASE_PROVIDER` is undefined, empty, or explicitly set to `"d1"`, the application defaults to the SQLite backend. If set to `"postgres"`, the application initializes the PostgreSQL client using a Cloudflare Hyperdrive connection string.

The TypeScript types for these environment variables are declared in [`src/env.d.ts`](https://github.com/every-app/open-seo/blob/main/src/env.d.ts), ensuring type safety across the codebase.

## Configuring SQLite (D1) Backend

SQLite is the default database backend for OpenSEO when no provider is specified.

Set the environment variable in your `.env` file:

```bash
DATABASE_PROVIDER=d1

```

Or leave it undefined, as the system defaults to D1.

The SQLite schema is defined in [`src/db/d1/schema.ts`](https://github.com/every-app/open-seo/blob/main/src/db/d1/schema.ts) and uses the Drizzle ORM configuration stored in [`drizzle.config.ts`](https://github.com/every-app/open-seo/blob/main/drizzle.config.ts) with `dialect: "sqlite"`.

## Configuring PostgreSQL Backend

To use PostgreSQL, you must configure both the environment variable and a Hyperdrive binding.

First, set the provider in your environment:

```bash
DATABASE_PROVIDER=postgres

```

Then, configure the Hyperdrive binding in your [`wrangler.toml`](https://github.com/every-app/open-seo/blob/main/wrangler.toml):

```toml
[vars]
DATABASE_PROVIDER = "postgres"

[[hyperdrive]]
binding = "HYPERDRIVE"
database = "your-postgres-db"

```

The `getPostgresConnectionString()` function in [`src/db/provider.ts`](https://github.com/every-app/open-seo/blob/main/src/db/provider.ts) retrieves the connection string from `env.HYPERDRIVE.connectionString` at runtime. The PostgreSQL schema is maintained separately in [`src/db/pg/schema.ts`](https://github.com/every-app/open-seo/blob/main/src/db/pg/schema.ts) and uses [`drizzle-pg.config.ts`](https://github.com/every-app/open-seo/blob/main/drizzle-pg.config.ts) with `dialect: "postgresql"`.

## Running Database Migrations

You must run different migration commands depending on your chosen backend.

For SQLite (D1):

```bash
npx drizzle-kit generate --config drizzle.config.ts

```

For PostgreSQL:

```bash
npx drizzle-kit generate --config drizzle-pg.config.ts

```

The [`scripts/migrate-d1-to-postgres.ts`](https://github.com/every-app/open-seo/blob/main/scripts/migrate-d1-to-postgres.ts) utility script can migrate existing D1 data to PostgreSQL if you are switching backends in production.

## Accessing the Database in Application Code

Application code interacts with the database through provider-aware imports that work transparently across both backends.

Import the database client from the barrel file:

```typescript
import { db } from "@/db";

async function listProjects() {
  return await db.select().from(projects).limit(10);
}

```

For provider-specific logic, use the `getDatabaseProvider()` function:

```typescript
import { getDatabaseProvider } from "@/db/provider";

const provider = getDatabaseProvider();
if (provider === "postgres") {
  // PostgreSQL-specific optimizations
}

```

Because the code imports from the provider-aware barrel ([`src/db/schema.ts`](https://github.com/every-app/open-seo/blob/main/src/db/schema.ts)), repositories work against either backend without modification.

## Summary

- **Provider Detection**: The `getDatabaseProvider()` function in [`src/db/provider.ts`](https://github.com/every-app/open-seo/blob/main/src/db/provider.ts) reads `DATABASE_PROVIDER` and defaults to D1 (SQLite) when unspecified.
- **SQLite Configuration**: Set `DATABASE_PROVIDER=d1` or leave undefined; use [`drizzle.config.ts`](https://github.com/every-app/open-seo/blob/main/drizzle.config.ts) for migrations.
- **PostgreSQL Configuration**: Set `DATABASE_PROVIDER=postgres` and configure the `HYPERDRIVE` binding in [`wrangler.toml`](https://github.com/every-app/open-seo/blob/main/wrangler.toml); use [`drizzle-pg.config.ts`](https://github.com/every-app/open-seo/blob/main/drizzle-pg.config.ts) for migrations.
- **Schema Separation**: SQLite schemas live in [`src/db/d1/schema.ts`](https://github.com/every-app/open-seo/blob/main/src/db/d1/schema.ts) while PostgreSQL schemas are in [`src/db/pg/schema.ts`](https://github.com/every-app/open-seo/blob/main/src/db/pg/schema.ts).
- **Runtime Consistency**: Import from `@/db` to write database code that works transparently across both backends.

## Frequently Asked Questions

### What happens if DATABASE_PROVIDER is not set?

If `DATABASE_PROVIDER` is undefined, empty, or contains an unsupported value, `getDatabaseProvider()` defaults to `"d1"` (SQLite) according to the logic in [`src/db/provider.ts`](https://github.com/every-app/open-seo/blob/main/src/db/provider.ts). This ensures backward compatibility and safe local development defaults.

### Can I migrate existing data from D1 to PostgreSQL?

Yes. The [`scripts/migrate-d1-to-postgres.ts`](https://github.com/every-app/open-seo/blob/main/scripts/migrate-d1-to-postgres.ts) script handles provider-aware migrations and can copy existing D1 data into a new PostgreSQL instance. Run this after configuring your PostgreSQL backend and before switching your production deployment.

### Do I need to write different database code for each backend?

No. OpenSEO uses provider-aware barrel exports ([`src/db/schema.ts`](https://github.com/every-app/open-seo/blob/main/src/db/schema.ts)) that abstract the underlying dialect. You write standard Drizzle ORM queries that work against both SQLite and PostgreSQL. Only use `getDatabaseProvider()` when you need dialect-specific optimizations.

### How does the PostgreSQL connection work in production?

In production, the `getPostgresConnectionString()` function extracts the connection string from the `HYPERDRIVE` Cloudflare binding (`env.HYPERDRIVE.connectionString`). This connection pooling service is configured in [`wrangler.toml`](https://github.com/every-app/open-seo/blob/main/wrangler.toml) and injected into the Workers runtime environment automatically.