KyselyJake

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)

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

...

GitHub

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

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

koskimas•71d 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.

Jake•71d 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•71d 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

Jake•71d 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•71d 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

Jake•71d 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•71d 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•71d 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)`

Jake•71d 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

Kysely Join

1.4KMembers

View on Discord

Want results from more Discord servers?

Add your server

Static/reusable custom window functionsHello, I'm trying to buld a properly typed `array_agg` window function and would like to reuse the e Is the Kysely main site down?I just want to confirm if Kysely's main site is down On Conflict do update set ALL to be inserted columnsHere I am once again with a question for the on conflict bit in postgres. Would there be a way to au How to use kysely in edge runtime (like NextJS 14 middleware)?Error: The edge runtime does not support Node.js 'crypto' module. Learn More: https://nextjs.org/doc how to write not (array1 && array2)Hi again, how to write following code? I would love to use not(expression) Expected result: ``` no Partial compileHello everyone, is it possible (somehow) to generate only part of the compiled query? I have an edge Querying on jsonArrayFromHi. So i need to fetch posts with comments from a database that are written by a set group of author Why does `.stream()` return a single result when I pass in a `chunkSize` option ?I'm guessing it returns `chunkSize` mount of rows for each iteration, but shouldn't it be an array i Is there a way to keep full date precision?It appears that selecting dates from Postgresql loses some precision, which wreaks havoc with my pag How can I get total row count without blowing up types?I have a simple query function that is in charge of generating a list of ids so that it can be passe Is there a way to handle migrations with .sql files?I enjoy being able to control the specific queries in the migration files. To do this, I use postgra Postgres: Transaction not honouring previous queriesHi, I have a table with unique constraint on some columns. In a transaction, I am deleting all the How do I specify a MySQL index hint?Hi, I am trying to build the following query (as a CTE, as part of a larger query), but I can not wo question about transaction isolation levelI am not sure if the way I do queries with transaction with kysely is the right way. I would be glad case when column = another columnI have a semi-complex query, and I'm trying to use a case() that includes a when() where the two val Webstorm warning with multiple joins```ts const organizationsWithAdminUser = await db .selectFrom("organizations") .How do I use `WHERE NOT EXISTS`?I am trying to write the followng where condition: ```sql INSERT INTO Filing (location_id, filing_da transforming database values to JS values (dates in sqlite)hi. is it possible to transform database values with any driver into any JS value? such as integers How to enable pragmas with SqliteDialectHello, I am using SqliteDialect and want to enable foreign_keys pragma. From [SQLite Docs](https:/Dynamic return type based on provided select arraySo I have this function: ``` async getBillableUsers(select: SelectExpression<DB, 'user_data'>[]) {

Gaming

Programming

using pgvector with Kysely