Database Technologies in the OpenShip Repository: PostgreSQL, Drizzle ORM, and PGlite

OpenShip utilizes PostgreSQL for production data persistence, Drizzle ORM for type-safe query building, and PGlite for in-memory database testing.

The oblien/openship project implements a modern, type-safe data layer using specific database technologies designed for reliability and developer experience. Understanding how the repository leverages PostgreSQL, Drizzle ORM, and PGlite is essential for contributors working with data persistence, schema migrations, or test suite optimization.

Core Database Stack: PostgreSQL and Drizzle ORM

Production Database with PostgreSQL

At the foundation of OpenShip's architecture lies PostgreSQL, serving as the primary relational database for all production deployments. The application interacts with PostgreSQL through Drizzle's sql tag helper, enabling direct SQL execution with type safety. For example, in packages/db/src/repos/webhook-delivery.repo.ts, the repository layer filters records using expressions like where(sql${t.source} = 'github') to query specific webhook sources.

Type-Safe Data Access with Drizzle ORM

OpenShip integrates Drizzle ORM (drizzle-orm) as its database toolkit, declared in packages/db/package.json. The ORM client is initialized in packages/db/src/index.ts and exported as a db instance used throughout the codebase:

import { drizzle } from "drizzle-orm/postgres-js";
import postgres from "postgres";

const client = postgres(process.env.DATABASE_URL!, { max: 10 });
export const db = drizzle(client);

Drizzle provides a SQL-like syntax that mirrors raw SQL while enforcing compile-time type checking against the database schema, reducing runtime errors during query construction.

Schema Definitions and Repository Pattern

Database schemas are defined in TypeScript files within packages/db/src/schema/, such as schema/project.ts, which exports table structures and TypeScript types. The repository layer in packages/db/src/repos/ encapsulates all CRUD logic, using the Drizzle client to execute type-safe queries:

import { db } from "./db";
import * as schema from "./schema/project";

export async function getActiveProjects(owner: string, repo: string) {
  return db
    .select()
    .from(schema.project)
    .where(sql`lower(${schema.project.gitOwner}) = ${owner.toLowerCase()}`)
    .where(sql`lower(${schema.project.gitRepo}) = ${repo.toLowerCase()}`)
    .where(sql`${schema.project.activeDeploymentId} IS NOT NULL`);
}

This pattern separates database schema definitions from query logic, maintaining clean architecture principles while leveraging Drizzle's type inference.

Testing Database Technologies with PGlite

For unit and integration testing, OpenShip employs PGlite, an in-memory PostgreSQL implementation from @electric-sql/pglite. This technology eliminates the need for a live PostgreSQL server during test execution, providing fast, deterministic, and isolated test environments. The test file packages/db/src/repos/webhook-delivery.repo.test.ts demonstrates this approach by importing PGlite and initializing an in-memory database instance:

import { PGlite } from "@electric-sql/pglite";
import { drizzle } from "drizzle-orm/pglite";

let db: ReturnType<typeof drizzle>;

beforeAll(async () => {
  const pglite = new PGlite();
  db = drizzle(pglite);
  await db.run(schema.projectDDL);
});

PGlite maintains full SQL dialect compatibility with PostgreSQL, ensuring that tests accurately reflect production query behavior without the overhead of containerized database dependencies.

Summary

• PostgreSQL serves as the primary relational database for production deployments, handling all persistent data storage in OpenShip.
• Drizzle ORM provides type-safe query building and schema management, with table definitions located in packages/db/src/schema/ and the client initialized in packages/db/src/index.ts.
• PGlite enables fast, in-memory database testing via @electric-sql/pglite, as implemented in packages/db/src/repos/webhook-delivery.repo.test.ts.
• The repository pattern encapsulates database interactions using Drizzle's SQL template tags for direct, type-safe PostgreSQL communication.

Frequently Asked Questions

What database technologies does OpenShip use in production?

OpenShip uses PostgreSQL as its sole production database technology. The application connects to PostgreSQL instances using connection strings (typically from the DATABASE_URL environment variable) and executes queries through the Drizzle ORM client configured in packages/db/src/index.ts.

Why does OpenShip choose Drizzle ORM over other database libraries?

OpenShip selects Drizzle ORM for its lightweight footprint and SQL-like syntax that closely mirrors raw PostgreSQL queries. Unlike heavier ORMs, Drizzle provides compile-time type safety through schema files like packages/db/src/schema/project.ts while allowing developers to write explicit SQL logic using the sql template tag helper.

How does OpenShip run database tests without a real PostgreSQL server?

The project utilizes PGlite, an in-memory PostgreSQL implementation imported from @electric-sql/pglite. Test suites initialize PGlite instances to create isolated databases that match PostgreSQL's SQL dialect exactly, eliminating external dependencies and significantly speeding up test execution compared to containerized database approaches.

Where are the database schema definitions located in the OpenShip repository?

Schema definitions reside in the packages/db/src/schema/ directory, with individual tables defined in files such as project.ts and deployment.ts. These files export Drizzle table configurations that define column types, constraints, and relationships, which the repository layer in packages/db/src/repos/ imports to construct type-safe queries.

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