How OpenSEO Selects the Database Provider at Runtime: Environment-Based Driver Detection

OpenSEO uses a dynamic provider module in src/db/provider.ts that inspects the DATABASE_URL environment variable at startup, choosing between PostgreSQL and SQLite drivers based on the URL protocol prefix.

This approach allows the same codebase to run seamlessly across development, CI, and production environments without configuration changes. The OpenSEO project (every-app/open-seo) implements a lightweight abstraction over Drizzle ORM that makes database driver selection transparent to application code.

The Provider Pattern: Centralized Runtime Selection

OpenSEO's database access layer centers on a single file: src/db/provider.ts. This module evaluates environment conditions once during application startup, then exports a configured Drizzle instance that the rest of the codebase consumes.

The selection logic follows a simple priority:

1. Inspect DATABASE_URL — Check for PostgreSQL protocol indicators
2. Select PostgreSQL driver — If URL starts with postgres:// or postgresql://
3. Fall back to SQLite — Default for local development and CI pipelines

This pattern ensures that server-side modules import db without concern for which driver powers it.

Environment Variable Inspection

The provider begins by reading connection details from process.env. OpenSEO checks DATABASE_URL as the primary source, with POSTGRES_URL as an alternative in some configurations.

// src/db/provider.ts (conceptual structure)
const url = process.env.DATABASE_URL ?? "";

The URL string undergoes protocol detection to determine which driver to instantiate. This happens synchronously at module load time, making the database available immediately for the first request.

PostgreSQL Detection and Driver Initialization

When the environment signals a production PostgreSQL deployment, OpenSEO imports and configures the Postgres-specific Drizzle driver.

Detection criteria:

• URL prefix: postgres:// or postgresql://

Driver: drizzle-orm/postgres-js (or equivalent drizzle-pg bindings)

// src/db/provider.ts (Postgres path)
import { drizzle } from "drizzle-orm/postgres-js";
import postgres from "postgres";

const url = process.env.DATABASE_URL ?? "";

export const db = url.startsWith("postgres")
  ? drizzle(postgres(url), { schema })
  : /* SQLite fallback */;

The Postgres connection is established via the standard postgres driver, wrapped by Drizzle's ORM layer with the shared schema definitions.

SQLite Fallback for Development

When no PostgreSQL URL is present — the common case for local development and automated testing — OpenSEO automatically selects SQLite.

Default configuration:

• Driver: drizzle-orm/better-sqlite3
• Connection: Local file (file:./dev.db or similar)

// src/db/provider.ts (SQLite path)
import { drizzle } from "drizzle-orm/better-sqlite3";
import Database from "better-sqlite3";

const url = process.env.DATABASE_URL ?? "";

export const db = url.startsWith("postgres")
  ? /* Postgres path */
  : drizzle(new Database("dev.db"), { schema });

SQLite requires zero external dependencies, making it ideal for contributor onboarding and CI pipelines where running a full PostgreSQL instance adds friction.

Shared Schema Abstraction

A critical design element enabling this dual-database support is the schema definition in src/db/schema.ts. OpenSEO defines tables, relations, and types using Drizzle's dialect-agnostic API, which compiles to the appropriate SQL for each engine.

Application code imports schemas alongside the db instance:

// src/serverFunctions/projects.ts
import { db } from "@/db/provider";
import { projects, userProjects } from "@/db/schema";

export async function getProjects() {
  return await db.select().from(projects);
}

The projects table definition uses standard Drizzle column types that map to INTEGER/TEXT in SQLite and SERIAL/VARCHAR in PostgreSQL automatically.

Build-Time Configuration Parity

Runtime selection alone isn't sufficient. OpenSEO mirrors this logic in build-time configuration files to ensure database migrations execute against the correct engine.

Configuration files:

File	Purpose
drizzle.config.ts	Base configuration, typically SQLite defaults
drizzle-pg.config.ts	PostgreSQL-specific overrides

These files import the same environment detection logic, ensuring npm run drizzle:migrate targets the appropriate database regardless of context.

# Local development (SQLite)

npm run drizzle:migrate

# Production deployment (PostgreSQL)

DATABASE_URL=postgres://user:pass@host/db npm run drizzle:migrate

Complete Provider Implementation

The full src/db/provider.ts implementation ties these elements together:

// src/db/provider.ts
import { drizzle } from "drizzle-orm/postgres-js";
import { drizzle as drizzleSqlite } from "drizzle-orm/better-sqlite3";
import postgres from "postgres";
import Database from "better-sqlite3";
import * as schema from "./schema";

const url = process.env.DATABASE_URL ?? "";

const isPostgres = url.startsWith("postgres") || url.startsWith("postgresql");

export const db = isPostgres
  ? drizzle(postgres(url), { schema })
  : drizzleSqlite(new Database(process.env.SQLITE_PATH ?? "dev.db"), { schema });

This module executes exactly once per process. Subsequent imports receive the same db instance via ES module caching.

Why This Pattern Works for OpenSEO

Development velocity — Contributors clone, install, and run without configuring PostgreSQL.

Production reliability — Deployments use managed PostgreSQL services with connection pooling.

Testing simplicity — CI pipelines use in-memory or file-based SQLite for speed and isolation.

Operational transparency — The same application code runs everywhere; only the provider module varies.

Summary

• src/db/provider.ts implements runtime database selection based on DATABASE_URL inspection
• PostgreSQL activated by postgres:// or postgresql:// URL prefixes
• SQLite serves as the zero-configuration default for development and CI
• Drizzle ORM provides dialect-agnostic schema definitions that work with both engines
• Build-time configurations (drizzle.config.ts, drizzle-pg.config.ts) maintain parity with runtime logic

Frequently Asked Questions

What environment variables control database selection in OpenSEO?

OpenSEO primarily checks DATABASE_URL. When this variable contains a PostgreSQL protocol prefix, the Postgres driver loads. If undefined or using another protocol, SQLite becomes the default. Some deployments may also reference POSTGRES_URL for specific provider configurations.

Can I force PostgreSQL even without a DATABASE_URL?

The provider logic requires a valid PostgreSQL URL to select that driver. To force PostgreSQL locally, export any valid-formatted URL: DATABASE_URL=postgresql://localhost:5432/openseo. Without this, the SQLite fallback activates automatically.

Does OpenSEO support other databases like MySQL or MongoDB?

According to the source code analysis, OpenSEO implements only PostgreSQL and SQLite drivers through Drizzle ORM. Drizzle itself supports MySQL, but OpenSEO's src/db/provider.ts does not include detection logic or driver imports for additional engines.

How do migrations work with two different database engines?

OpenSEO maintains parallel configuration files. The npm scripts detect the active database via the same DATABASE_URL inspection, then invoke drizzle-kit with the appropriate config (drizzle.config.ts for SQLite, drizzle-pg.config.ts for PostgreSQL). Schema definitions in src/db/schema.ts remain identical across both engines.

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