---
title: "How to store vector embeddings in Postgres with Drizzle ORM"
description: "Import vector from drizzle-orm/pg-core, enable the pgvector extension, define the embedding column in Drizzle, and add an index. Includes Payload CMS."
canonical: https://omiid.me/notebook/15/how-to-store-vector-embeddings-in-postgres-with-drizzle-orm
author: Omid Sayfun
published: 2024-03-21
updated: 2026-08-09
---

# 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:

```typescript
import { pgTable, vector } from "drizzle-orm/pg-core";

const Products = pgTable("products", {
	embedding: vector("embedding", { dimensions: 1024 }),
});
```

Adding an index to your vector column works the same way:

```typescript
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)](https://omiid.me/notebook/31/pgvector-indexing-options-for-vector-similarity-search) and [what each distance operator actually means](https://omiid.me/notebook/32/what-the-pgvector-operators-and-actually-mean) in separate posts if you want to dig deeper.

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

```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](https://omiid.me/notebook/6/postgresql-doesn-t-have-on-update-current-timestamp-here-s-the-fix), 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](https://en.wikipedia.org/wiki/Euclidean_distance) or [cosine similarity](https://en.wikipedia.org/wiki/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](https://github.com/pgvector/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](https://orm.drizzle.team/) 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](https://hub.docker.com/r/pgvector/pgvector). Once the container is up, enable the extension:

```sql
CREATE EXTENSION vector;
```

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

```typescript
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](https://github.com/pgvector/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:

```typescript
// 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:

```typescript
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);
```
