Kysely•9mo ago

using pgvector with Kysely

Following the example here: https://github.com/pgvector/pgvector-node?darkschemeovr=1#kysely But I get the error ERROR: column "embedding" is of type vector but expression is of type character varying Happy to provide code if necessary but there aren’t any differences from the tutorial Using AWS RDS 13.12 (which has pgvector support)

GitHub

GitHub - pgvector/pgvector-node: pgvector support for Node.js and B...

pgvector support for Node.js and Bun (and TypeScript) - pgvector/pgvector-node

Solution:

// Replace `string` with the type you have in the table interface.
sql<string>`${sql.ref(column)} <=> ${toSql(value)}::vector(3)`

// Replace `string` with the type you have in the table interface.
sql<string>`${sql.ref(column)} <=> ${toSql(value)}::vector(3)`

...

Jump to solution

9 Replies

koskimas•9mo ago

I think there are some errors in the tutorial. Didn't read the whole thing, but at least this

.addColumn('embedding', 'vector(3)')

.addColumn('embedding', 'vector(3)')

should be

.addColumn('embedding', sql`vector(3)`)

.addColumn('embedding', sql`vector(3)`)

since vector is not in the list of supported data types. Oh yeah, and the functions under 'pgvector/kysely' return untyped sql instances, which is not supported.

JakeOP•9mo ago

I was able to keep the column as ‘vector’ type actually and use raw sql to do the operations. Only worry is surrounding injections

koskimas•9mo ago

I don't know what that package generates and if it's safe, but kysely always creates safe SQL. No chance of injections unless you call the unsafe functions like sql.raw

JakeOP•9mo ago

When you say vector isn’t a supported datatype, do you mean at the Postgres level or the kysely level? Will share code when I get home

koskimas•9mo ago

The only unsafe functions are under the sql module and you can see if it's unsafe by hovering over the function in your IDE. It's clearly documented. I meant kysely typings don't support vector(3) You need to use a raw SQL snippet

sql`vector(3)`

sql`vector(3)`

This error:

ERROR: column "embedding" is of type vector but expression is of type character varying

ERROR: column "embedding" is of type vector but expression is of type character varying

is due to the SQL the package generates. I have no idea what that is, but there's nothing we can do here. The issue is with the package you use. You probably need to cast the expression to vector in SQL

JakeOP•9mo ago

Ahh okay that makes sense Will try that using

 sql`vector(3)`

 sql`vector(3)`

is throwing the same error sadly This is what I ended up with earlier

  const insertValue = sql`(
    ${sql.raw(`'${document.embedding}'`)}, 
    ${document.text}
  )`;

  const result = await sql<Document>`
  INSERT INTO embedding (embedding, text)
  VALUES ${insertValue}
  RETURNING *
`.execute(db);

  const row = result.rows[0];

  const insertValue = sql`(
    ${sql.raw(`'${document.embedding}'`)}, 
    ${document.text}
  )`;

  const result = await sql<Document>`
  INSERT INTO embedding (embedding, text)
  VALUES ${insertValue}
  RETURNING *
`.execute(db);

  const row = result.rows[0];

I also ended up with

const value = sql`( ${sql.raw(`'[${vector}]'`)})`;

sql`${sql.ref("embedding")} <=> ${value}`;

const value = sql`( ${sql.raw(`'[${vector}]'`)})`;

sql`${sql.ref("embedding")} <=> ${value}`;

over

cosineDistance("embedding", vector);

cosineDistance("embedding", vector);

again, because the kysely package from pgvector-node uses the toSql function:

function cosineDistance(column, value) {
  return sql`${sql.ref(column)} <=> ${toSql(value)}`;
}

function cosineDistance(column, value) {
  return sql`${sql.ref(column)} <=> ${toSql(value)}`;
}

I mean the toSql function just called JSON.stringify on the vector, so it seems to be treating it as varchar? The message indicates that the problem is with the expression not with the column type

function fromSql(value) {
  return value.substring(1, value.length - 1).split(',').map((v) => parseFloat(v));
}

function toSql(value) {
  return JSON.stringify(value);
}

function fromSql(value) {
  return value.substring(1, value.length - 1).split(',').map((v) => parseFloat(v));
}

function toSql(value) {
  return JSON.stringify(value);
}

koskimas•9mo ago

The error comes from Postgres It has nothing to do with kysely You need a cast. As simple as that. An SQL cast. A Postgres cast. Not typescript cast

Solution

koskimas•9mo ago

// Replace `string` with the type you have in the table interface.
sql<string>`${sql.ref(column)} <=> ${toSql(value)}::vector(3)`

// Replace `string` with the type you have in the table interface.
sql<string>`${sql.ref(column)} <=> ${toSql(value)}::vector(3)`

JakeOP•9mo ago

Oh okay I’ll try that too when I sit back down Assuming I can do the same cast for the insert as well Legends, that worked for insert 👌🏼 Worked for cosine search as well

Gaming

Programming

using pgvector with Kysely