Fix serialization and parsing of vectors for vector type of pgvector

[uncomplexity]

Fixes the serialization and parsing of vectors when using pgvector https://github.com/pgvector/pgvector.

import assert from "node:assert";
import postgres from "postgres";

const POSTGRESQL_HOSTNAME = process.env["POSTGRESQL_HOSTNAME"] || "localhost";
const POSTGRESQL_PORT = Number(process.env["POSTGRESQL_PORT"]) || 5432;
const POSTGRESQL_DB = process.env["POSTGRESQL_DB"] || "postgres";
const POSTGRESQL_USER = process.env["POSTGRESQL_USER"] || "postgres";
const POSTGRESQL_PASSWORD = process.env["POSTGRESQL_PASSWORD"] || "postgres";

// temporary postgresql client for getting vector oid
const tsql = postgres({
	database: POSTGRESQL_DB,
	host: POSTGRESQL_HOSTNAME,
	port: POSTGRESQL_PORT,
	user: POSTGRESQL_USER,
	password: POSTGRESQL_PASSWORD,
	max: 10,
	connect_timeout: 2500,
	idle_timeout: 2500,
	onnotice: (notice) => {
		const severity = notice["severity"];
		const message = notice["message"];
		console.log(severity, message);
	},
});

// enable the extension
await tsql`CREATE EXTENSION IF NOT EXISTS vector;`;

// get the oid
const [vector] = await tsql`
	SELECT oid, typname FROM pg_type WHERE typname = 'vector';
`;
assert(vector instanceof Object);
assert("oid" in vector);
assert(typeof vector["oid"] === "number");
assert("typname" in vector);
assert(typeof vector["typname"] === "string");

// close the temporary connection
await tsql.end();

// now use the oid here for the vector type
const sql = postgres({
	database: POSTGRESQL_DB,
	host: POSTGRESQL_HOSTNAME,
	port: POSTGRESQL_PORT,
	user: POSTGRESQL_USER,
	password: POSTGRESQL_PASSWORD,
	max: 10,
	types: {
		vector: {
			to: vector["oid"],
			from: [vector["oid"]],
			serialize: (value: number[]) => {
				return JSON.stringify(value);
			},
			parse: (value: string) => {
				return JSON.parse(value);
			},
		},
	},
});

export default sql;

[uncomplexity]

closing this now, just created it to make it visible for others when they search issue here.