pgvector ecosystem

import numpy as np
import psycopg
from pgvector.psycopg import register_vector

conn = psycopg.connect("dbname=app", autocommit=True)
conn.execute("CREATE EXTENSION IF NOT EXISTS vector")
register_vector(conn)   # after CREATE EXTENSION: it looks up the vector type's OID

conn.execute("CREATE TABLE documents (id bigserial PRIMARY KEY, content text, embedding vector(3))")
conn.execute("INSERT INTO documents (content, embedding) VALUES (%s, %s)", ("hello", np.array([1, 2, 3])))

rows = conn.execute(
    "SELECT content, embedding <=> %s AS distance FROM documents ORDER BY distance LIMIT 5",
    (np.array([1, 2, 4]),),
).fetchall()
print(rows)   # [('hello', 0.00853986601633272)]

from pgvector.sqlalchemy import Vector
from sqlalchemy import BigInteger, Text, create_engine, select
from sqlalchemy.orm import DeclarativeBase, Mapped, Session, mapped_column

class Base(DeclarativeBase):
    pass

class Document(Base):
    __tablename__ = "documents"
    id: Mapped[int] = mapped_column(BigInteger, primary_key=True)
    content: Mapped[str] = mapped_column(Text)
    embedding = mapped_column(Vector(3))

engine = create_engine("postgresql+psycopg://localhost/app")
with Session(engine) as session:
    session.add(Document(content="hello", embedding=[1, 2, 3]))
    session.commit()
    query = [1, 2, 4]
    stmt = (select(Document.content, Document.embedding.cosine_distance(query).label("distance"))
            .order_by(Document.embedding.cosine_distance(query)).limit(5))
    print(session.execute(stmt).all())   # [('hello', 0.00853986601633272)]

import pg from 'pg';
import pgvector from 'pgvector/pg';

const client = new pg.Client();
await client.connect();
await client.query('CREATE EXTENSION IF NOT EXISTS vector');
await pgvector.registerTypes(client);

await client.query('INSERT INTO documents (content, embedding) VALUES ($1, $2)',
  ['hello', pgvector.toSql([1, 2, 3])]);

const res = await client.query(
  'SELECT content, embedding, embedding <=> $1 AS distance FROM documents ORDER BY distance LIMIT 5',
  [pgvector.toSql([1, 2, 4])]);
console.log(res.rows);
// [ { content: 'hello', embedding: [ 1, 2, 3 ], distance: 0.00853986601633272 } ]