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

> Explore OpenShip's database technologies: PostgreSQL for production, Drizzle ORM for type-safe queries, and PGlite for efficient in-memory testing. Learn how these tools power the repository.

- Repository: [oblien/openship](https://github.com/oblien/openship)
- Tags: internals
- Published: 2026-07-29

---

**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`](https://github.com/oblien/openship/blob/main/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`](https://github.com/oblien/openship/blob/main/packages/db/package.json). The ORM client is initialized in [`packages/db/src/index.ts`](https://github.com/oblien/openship/blob/main/packages/db/src/index.ts) and exported as a `db` instance used throughout the codebase:

```typescript
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`](https://github.com/oblien/openship/blob/main/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:

```typescript
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`](https://github.com/oblien/openship/blob/main/packages/db/src/repos/webhook-delivery.repo.test.ts) demonstrates this approach by importing `PGlite` and initializing an in-memory database instance:

```typescript
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`](https://github.com/oblien/openship/blob/main/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`](https://github.com/oblien/openship/blob/main/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`](https://github.com/oblien/openship/blob/main/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`](https://github.com/oblien/openship/blob/main/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`](https://github.com/oblien/openship/blob/main/project.ts) and [`deployment.ts`](https://github.com/oblien/openship/blob/main/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.