Database

Paperclip uses PostgreSQL via Drizzle ORM. There are three ways to run the database.

1. Embedded PostgreSQL (Default)

Zero config. If you don’t set DATABASE_URL, the server starts an embedded PostgreSQL instance automatically.

pnpm dev

On first start, the server:

1. Creates ~/.paperclip/instances/default/db/ for storage
2. Ensures the paperclip database exists
3. Runs migrations automatically
4. Starts serving requests

Data persists across restarts. To reset: rm -rf ~/.paperclip/instances/default/db.

The Docker quickstart also uses embedded PostgreSQL by default.

2. Local PostgreSQL (Docker)

For a full PostgreSQL server locally:

docker compose up -d

This starts PostgreSQL 17 on localhost:5432. Set the connection string:

cp .env.example .env
# DATABASE_URL=postgres://paperclip:paperclip@localhost:5432/paperclip

Push the schema:

DATABASE_URL=postgres://paperclip:paperclip@localhost:5432/paperclip \
  npx drizzle-kit push

3. Hosted PostgreSQL (Supabase)

For production, use a hosted provider like Supabase.

1. Create a project at database.new
2. Copy the connection string from Project Settings > Database
3. Set DATABASE_URL in your .env

Use the direct connection (port 5432) for migrations and the pooled connection (port 6543) for the application.

If using connection pooling, disable prepared statements:

// packages/db/src/client.ts
export function createDb(url: string) {
  const sql = postgres(url, { prepare: false });
  return drizzlePg(sql, { schema });
}

Switching Between Modes

DATABASE_URL	Mode
Not set	Embedded PostgreSQL
postgres://...localhost...	Local Docker PostgreSQL
postgres://...supabase.com...	Hosted Supabase

The Drizzle schema (packages/db/src/schema/) is the same regardless of mode.