How to store vector embeddings in Postgres with Drizzle ORM

EDIT: Drizzle ORM now supports vectors out of the box, making implementation much easier.

Import vector from drizzle-orm/pg-core and declare the column:

import { pgTable, vector } from "drizzle-orm/pg-core";
 
const Products = pgTable("products", {
	embedding: vector("embedding", { dimensions: 1024 }),
});

Adding an index to your vector column is just as straightforward:

export const Products = pgTable(
	"products",
	{
		embedding: vector("embedding", { dimensions: 1024 }),
	},
	(t) => [
		index("products_embedding_index").using(
			"hnsw",
			t.embedding.op("vector_ip_ops"),
		),
	],
);

In this example, I’m using inner product as the distance function and HNSW as the index type. I’ve covered pgvector indexing options (HNSW vs IVFFlat) and what each distance operator actually means in separate posts if you want to dig deeper.

Drizzle ORM also ships distance helpers, so a similarity query stays in TypeScript:

import { innerProduct } from "drizzle-orm";
 
const products = await this.db
	.select({
		similarity: innerProduct(Products.embedding, embedding),
	})
	.from(Products)
	.orderBy((t) => asc(t.similarity))
	.limit(5);

One caveat: drizzle-kit does not enable the pgvector extension for you. Run CREATE EXTENSION vector; yourself before the first migration that adds a vector column. The same applies in Payload CMS, whose Postgres adapter is built on Drizzle: enable the extension by hand, then add the vector column through the adapter's beforeSchemaInit hook or a raw migration. If you’re building out your schema, you’ll probably also want auto-updating timestamps for created_at and updated_at, since PostgreSQL doesn’t handle that automatically the way MySQL does.

The rest of this post is the original write-up, from before Drizzle had a built-in type. It still covers what pgvector does and how the pieces fit together.

A vector database indexes arrays of numbers, not rows

Vectors are arrays of numbers that place a data point in multi-dimensional space. They are mathematical representations of complex data such as text, images, or audio.

Vector databases, therefore, specialize in storing, indexing, and querying vector data. They use distance measures (like Euclidean distance or cosine similarity) to find similarities between vectors, enabling fast and efficient retrieval of similar items from a large dataset. This capability is crucial for implementing features like search-by-image, recommendations, or any application requiring similarity searches at scale.

Common use cases for vector databases include:

• Recommendation Systems: Finding similar products, movies, or articles based on a user's past behavior.
• Image and Text Search: Retrieving visually or semantically similar images or text snippets.
• Fraud Detection: Identifying anomalous patterns in financial transactions.

pgvector adds the vector data type, Drizzle ORM types the queries

Postgres has no vector data type of its own. The pgvector extension adds one, so you can store multidimensional data points directly in your Postgres tables. That is the whole reason a plain Postgres database can serve as a vector store.

Drizzle ORM is a tool for TypeScript, designed to make it easier for developers to interact with databases. It provides a type-safe way to query and manipulate data in SQL databases, leveraging TypeScript's advanced type system for more reliable and maintainable code. The API surface is small and the SQL it generates stays readable.

Install pgvector, then enable the extension

The pgvector repo documents several install paths. The quickest one for local work is the official pgvector Docker image. Once the container is up, enable the extension:

CREATE EXTENSION vector;

With the extension in place, declare a schema and connect Drizzle to the database:

import { pgTable, serial, text } from "drizzle-orm/pg-core";
import { drizzle } from "drizzle-orm/node-postgres";
import { Client } from "pg";
 
const client = new Client({
	connectionString: "postgres://user:password@host:port/db",
});
 
export const users = pgTable("users", {
	id: serial("id").primaryKey(),
	fullName: text("full_name"),
});
 
await client.connect();
const db = drizzle(client, { users });

Two ways to add a pgvector column before native support

Drizzle had no vector type of its own back then, so the two did not connect on their own. There were two ways to get a vector column into a drizzle-orm/pg-core schema.

pgvector-node

The pgvector team maintains adapters for several Node.js ORMs, Drizzle ORM among them. Here is the vector column and a nearest-neighbor query using that package:

// Adding vector field
import { vector } from 'pgvector/drizzle-orm';
 
export const users = pgTable('users', {
  id: serial('id').primaryKey(),
  fullName: text('full_name'),
  embedding: vector('embedding', { dimensions: 256 })
});
 
// Finding nearest neighbors to a vector
import { l2Distance } from 'pgvector/drizzle-orm';
 
const nearest = await db.select()
  .from(users)
  .orderBy(l2Distance(users.embedding, [1, 2, 3, ..., 256]))
  .limit(5);

They also provide maxInnerProduct and cosineDistance functions for the other distance measures.

Define your own vector type with customType

Drizzle ORM lets you define custom column types. The second option is to declare the vector type yourself and use it like any other drizzle-orm/pg-core column:

import { customType } from "drizzle-orm/pg-core";
 
export const vector = customType<{
	data: number[];
	driverData: string;
	config: { size: number };
}>({
	dataType(config) {
		const dt =
			!!config && typeof config.size === "number"
				? `vector(${config.size})`
				: "vector";
		return dt;
	},
	fromDriver(value) {
		return JSON.parse(value);
	},
	toDriver(value) {
		return JSON.stringify(value);
	},
});
 
// Adding it to schema
export const users = pgTable("users", {
	id: serial("id").primaryKey(),
	fullName: text("full_name"),
	embedding: vector("embedding", { size: 256 }),
});
 
// Querying nearest users (cosine similarity)
const nearest = await db
	.select({
		id: users.id,
		distance: sql
			.raw(`1 - (${users.embedding.name} <=> '[${vector}]')`)
			.as<number>("distance"),
	})
	.from(user)
	.orderBy(sql`distance DESC`)
	.limit(5);