# How Paperclip Handles Database Interactions: A Deep Dive into Drizzle ORM, Postgres.js, and Embedded Postgres

> Discover how Paperclip manages database interactions using Drizzle ORM and postgres.js. Learn about its support for external Postgres and embedded PGLite, typed clients, and migrations.

- Repository: [Paperclip/paperclip](https://github.com/paperclipai/paperclip)
- Tags: deep-dive
- Published: 2026-08-14

---

**Paperclip uses a three-layer database architecture built on Drizzle ORM with the postgres.js driver, supporting both external PostgreSQL and embedded PGLite instances through runtime configuration resolution, typed client creation, and idempotent migration management.**

The `paperclipai/paperclip` repository implements a robust data layer that prioritizes developer experience, type safety, and deployment flexibility. At its core, Paperclip's database interactions are orchestrated through a handful of well-defined modules in the `packages/db` workspace, with clear separation between runtime configuration, client instantiation, and schema evolution.

## How Paperclip Resolves Database Targets

The entry point for all database interactions begins with **target resolution**. The `resolveDatabaseTarget()` function in [`packages/db/src/runtime-config.ts`](https://github.com/paperclipai/paperclip/blob/main/packages/db/src/runtime-config.ts) determines whether to connect to an external PostgreSQL instance or spin up an embedded PGLite engine.

The resolution follows a strict hierarchy:

1. **`process.env.DATABASE_URL`** – environment variable takes highest precedence
2. **`.paperclip/.env` file** – parsed via `readEnvEntries()` for a `DATABASE_URL` entry
3. **[`config.json`](https://github.com/paperclipai/paperclip/blob/main/config.json)** – the `database.connectionString` field when `mode: "postgres"`
4. **Fallback to embedded** – defaults to `mode: "embedded-postgres"` with configurable `dataDir` and `port`

The resolved target object carries provenance information, enabling consistent logging and debugging across development and production environments:

```typescript
// Example: Inspecting the resolved database target
import { resolveDatabaseTarget } from "@paperclipai/db/runtime-config";

const target = resolveDatabaseTarget();

if (target.mode === "postgres") {
  console.log("External Postgres:", target.connectionString);
} else {
  console.log(`Embedded Postgres on port ${target.port}, data: ${target.dataDir}`);
}

```

## Creating a Typed Database Client with Drizzle ORM

Once the target is resolved, `createDb()` in [`packages/db/src/client.ts`](https://github.com/paperclipai/paperclip/blob/main/packages/db/src/client.ts) constructs a fully typed database client. This function bridges the postgres.js driver with Drizzle's type system.

The implementation applies environment-driven tuning options before wrapping the connection:

```typescript
export function createDb(url: string, options?: DatabaseClientOptions) {
  const resolved = options ?? databaseClientOptionsFromEnv();
  const sql = postgres(url, postgresJsOptions(resolved));
  return drizzlePg(sql, { schema });
}

```

Key components of the client creation process:

- **`schema`** – exported from [`packages/db/src/schema/index.ts`](https://github.com/paperclipai/paperclip/blob/main/packages/db/src/schema/index.ts), contains all table definitions
- **`postgresJsOptions`** – respects `DATABASE_PREPARED_STATEMENTS`, `DATABASE_POOL_MAX`, and other driver settings
- **Return type (`Db`)** – provides query builders like `db.select().from(companies).where(...)`

Server-side services throughout Paperclip import this `db` object and use it for all data operations, ensuring compile-time type safety across the entire stack.

## Paperclip's Idempotent Migration System

The migration workflow in [`packages/db/src/client.ts`](https://github.com/paperclipai/paperclip/blob/main/packages/db/src/client.ts) implements a **journal-first strategy** that safely handles both fresh databases and incremental schema updates. All migrations are stored as raw `.sql` files in `packages/db/src/migrations`.

### Migration Lifecycle

| Phase | Function | Purpose |
|-------|----------|---------|
| Discovery | `listMigrationFiles()`, `listJournalMigrationEntries()` | Reads migration folder and existing journal |
| Schema Detection | `discoverMigrationTableSchema()` | Locates `__drizzle_migrations` table in any schema |
| State Loading | `loadAppliedMigrations()` | Retrieves already-run migrations by name, hash, or timestamp |
| Reconciliation | `reconcilePendingMigrationHistory()` | Repairs manually-applied migrations, updates metadata |
| Execution | `applyPendingMigrationsManually()` → `runInTransaction()` | Applies pending migrations with statement-level rollback |
| Bootstrap | `migratePg()` (Drizzle built-in) | Handles completely empty databases |

### Safe Statement Execution

Each SQL statement is pre-checked for idempotency using helper functions:

- `tableExists()` – for `CREATE TABLE`
- `columnExists()` – for `ALTER TABLE ... ADD COLUMN`
- `indexExists()` – for `CREATE INDEX`
- `constraintExists()` – for `ADD CONSTRAINT`

Statements that cannot be safely inferred are skipped for manual handling. Migrations use `--> statement-breakpoint` markers to split multi-statement files into individually retryable units within a transaction wrapper.

## Managing Embedded PostgreSQL with PGLite

When target resolution yields `mode: "embedded-postgres"`, Paperclip manages a lightweight PostgreSQL instance via PGLite. Three core utilities handle embedded database lifecycle:

- `ensurePostgresDatabase()` – initializes the embedded instance if not running
- `resetPostgresDatabase()` – wipes and recreates for testing scenarios
- `migratePostgresIfEmpty()` – one-time bootstrap on fresh instances

```typescript
const { mode, dataDir, port } = resolveDatabaseTarget();

if (mode === "embedded-postgres") {
  // Fresh test database setup
  await resetPostgresDatabase(`postgres://localhost:${port}`, "paperclip_test");
}

```

The embedded mode eliminates external dependencies for local development and CI pipelines while maintaining full PostgreSQL compatibility.

## Complete Database Initialization Sequence

A typical Paperclip server startup follows this pattern:

```typescript
import { resolveDatabaseTarget } from "@paperclipai/db/runtime-config";
import { createDb, applyPendingMigrations } from "@paperclipai/db";

async function initDatabase() {
  const target = resolveDatabaseTarget();

  // Resolve connection parameters
  const url = target.mode === "postgres"
    ? target.connectionString
    : `postgres://localhost:${target.port}`;

  // Idempotent schema alignment
  await applyPendingMigrations(url);

  // Typed client ready for application use
  const db = createDb(url);
  return db;
}

```

This sequence ensures every server instance—whether in development, testing, or production—operates against a schema-migrated, type-safe database connection.

## Key Files for Understanding Paperclip Database Interactions

| File | Responsibility |
|------|--------------|
| [`packages/db/src/runtime-config.ts`](https://github.com/paperclipai/paperclip/blob/main/packages/db/src/runtime-config.ts) | `resolveDatabaseTarget()` and configuration hierarchy |
| [`packages/db/src/client.ts`](https://github.com/paperclipai/paperclip/blob/main/packages/db/src/client.ts) | `createDb()`, migration orchestration, transaction management |
| [`packages/db/src/schema/index.ts`](https://github.com/paperclipai/paperclip/blob/main/packages/db/src/schema/index.ts) | Central Drizzle table definitions |
| `packages/db/src/migrations/*.sql` | Versioned schema changes |
| [`packages/db/src/migrations/meta/_journal.json`](https://github.com/paperclipai/paperclip/blob/main/packages/db/src/migrations/meta/_journal.json) | Migration provenance and ordering |

## Summary

- **Target resolution** in [`runtime-config.ts`](https://github.com/paperclipai/paperclip/blob/main/runtime-config.ts) determines external vs. embedded PostgreSQL through a prioritized configuration hierarchy
- **`createDb()`** constructs type-safe Drizzle clients with tunable postgres.js driver options
- **Migration management** guarantees idempotent schema evolution through discovery, reconciliation, and safe statement execution
- **Embedded PostgreSQL support** via PGLite enables zero-dependency local development
- All server components consume a single `db` export, ensuring consistent types and schema contracts

## Frequently Asked Questions

### What ORM does Paperclip use for database interactions?

Paperclip uses **Drizzle ORM** with the **postgres.js** driver. The `drizzlePg()` wrapper in [`packages/db/src/client.ts`](https://github.com/paperclipai/paperclip/blob/main/packages/db/src/client.ts) combines postgres.js connections with the schema definitions exported from [`packages/db/src/schema/index.ts`](https://github.com/paperclipai/paperclip/blob/main/packages/db/src/schema/index.ts) to provide fully typed query builders.

### How does Paperclip handle database migrations in production?

Paperclip applies migrations idempotently through `applyPendingMigrations()` in [`packages/db/src/client.ts`](https://github.com/paperclipai/paperclip/blob/main/packages/db/src/client.ts). The system detects existing schema state, reconciles any manually-applied migrations, and executes pending `.sql` files inside transactions with statement-level rollback protection via `--> statement-breakpoint` markers.

### Can Paperclip run without an external PostgreSQL server?

Yes. When no `DATABASE_URL` is configured, Paperclip defaults to **embedded PostgreSQL** using PGLite. The `resolveDatabaseTarget()` function returns `mode: "embedded-postgres"` with configurable `dataDir` and `port`, and utilities like `ensurePostgresDatabase()` manage the embedded instance lifecycle.

### Where are Paperclip's database table schemas defined?

All Drizzle table definitions are centralized in [`packages/db/src/schema/index.ts`](https://github.com/paperclipai/paperclip/blob/main/packages/db/src/schema/index.ts). This file exports the `schema` object passed to `drizzlePg()`, making type-safe query builders available throughout the server codebase for tables like `companies`, `users`, and other domain entities.