Prisma with pgvector, Raw SQL Casting and Safe Migrations

Prisma talks to PostgreSQL well until the column is a vector. Then the schema DSL has no type, migrate gets scared, and TypeScript wants a number[] that the driver will not send as a vector.

AgenticPacks OSS is Postgres plus Prisma without embeddings: Agentic-Packs/agenticpacks-oss. pgvector RAG, chunking, and cosine search are Pro. If you are choosing a vendor instead of a column type, read pgvector vs Pinecone.

Enable the extension in SQL, not in the Prisma schema

CREATE EXTENSION IF NOT EXISTS vector;

Put that in a migration you write by hand. prisma migrate dev will not invent it from a model. On a managed host, confirm you are allowed to CREATE EXTENSION. On the Docker Postgres 16 image Pro uses, you want the pgvector/pgvector:pg16 image, not stock postgres:16.

Unsupported type, then raw SQL

model DocumentChunk {
  id        String                 @id @default(cuid())
  content   String
  embedding Unsupported("vector")?
  createdAt DateTime               @default(now())
}

Unsupported("vector") keeps migrate from pretending this is Bytes. You cannot prisma.documentChunk.create({ data: { embedding: [...] } }). You insert with $executeRaw.

await prisma.$executeRaw`
  INSERT INTO "DocumentChunk" (id, content, embedding)
  VALUES (
    ${id},
    ${content},
    ${JSON.stringify(embedding)}::vector
  )
`;

1536 dimensions for text-embedding-3-small. The cast is required. A JS array is not a vector.

Cosine search is also raw

const rows = await prisma.$queryRaw<
  { id: string; content: string; distance: number }[]
>`
  SELECT id, content, embedding <=> ${JSON.stringify(query)}::vector AS distance
  FROM "DocumentChunk"
  ORDER BY distance ASC
  LIMIT 8
`;

<=> is cosine distance in pgvector. Lower is closer. Create an index when the table is more than a toy:

CREATE INDEX ON "DocumentChunk"
USING ivfflat (embedding vector_cosine_ops)
WITH (lists = 100);

ivfflat needs rows before it is useful. Build the index after the first bulk ingest. HNSW is the alternative if you are on a recent pgvector and can pay the build RAM. That ingest path is in document ingestion for Next.js RAG.

Migrations without wrecking prod

1. Empty migration: npx prisma migrate dev --create-only --name add_pgvector.
2. Write CREATE EXTENSION and ALTER TABLE ... ADD COLUMN embedding vector(1536).
3. Apply. Do not let Prisma guess the column from Unsupported on an existing production database.

Shadow database in migrate dev must also have the extension. Point shadowDatabaseUrl at a second local DB on the same pgvector image.

Never store embeddings as Json or Float[] if you plan to order by distance in SQL. You will table-scan and you will not use the index.

Tired of fighting Unsupported("vector") on a Saturday?

AgenticPacks OSS is the Prisma plus Postgres baseline: Agentic-Packs/agenticpacks-oss.

Pro ships pgvector, the chunker, and cosine search next to the chat UI. See pgvector RAG in the Pro chat UI.

What belongs in the app vs the database

Auth still goes through Prisma models as usual. Do not mix a second ORM just for vectors.

---

Where to Go From Here

You do not have to keep a Pinecone project for a table that already lives next to your users.

Option A: Free open-source baseline (MIT)

• GitHub: github.com/Agentic-Packs/agenticpacks-oss
• What is included: Next.js 15, Compose, PostgreSQL 16, Prisma, Auth.js v5, streaming chat, single-plan Stripe. No pgvector, no Caddy.
• Cost: free, MIT.

---

Option B: Ship with AgenticPacks Pro ($149 one-time)

If RAG should sit in the same Postgres as Auth.js:

• Automated Hetzner provisioning: Terraform, Caddy, Compose, pgvector image.
• Production multi-agent core: LangGraph, chunking, cosine search, Redis.
• Usage & Unit Economics: Stripe subscriptions and a token-to-dollar ledger.