Theo's Typesafe Cult•3mo ago

Prisma w/pgvector

Hey, we got this t3 app, but Prisma does not support vectors when trying to query the column that's set to vector type (with pgvector database extension enabled). When trying to raw query instead it say's it's not able to serialize. So therefore I tried to extend the prisma client with the custom query but there is absolutely no intellisense or types following my change, and I believe it must be because I'm not doing it correctly. Below in the comments I've attached the relevant files.. Please tell me if I'm missing a change somewhere.

2 Replies

Mads•3mo ago

prisma.schema

message.txt

Mads•3mo ago

import { PrismaClient, Prisma } from "@prisma/client";
import { env } from "~/env.mjs";

const prismaClientSingleton = () => {
    return new PrismaClient();
};

type Embedding = number[];

declare global {
    var prisma: undefined | ReturnType<typeof prismaClientSingleton>;
}

export const prisma = globalThis.prisma ?? prismaClientSingleton();

const getUserEmbedding = async (userId: number): Promise<Embedding> => {
    const embedding: Embedding = await prisma.$queryRaw`
        SELECT embedding
        FROM "User"
        WHERE id = ${userId}
    `;
    return embedding;
};

prisma.$extends({
    model: {
        user: {
            async getEmbedding(userId: number) {
                return getUserEmbedding(userId);
            },
        },
    },
});
if (process.env.NODE_ENV !== "production") globalThis.prisma = prisma;

export default prisma;

import { PrismaClient, Prisma } from "@prisma/client";
import { env } from "~/env.mjs";

const prismaClientSingleton = () => {
    return new PrismaClient();
};

type Embedding = number[];

declare global {
    var prisma: undefined | ReturnType<typeof prismaClientSingleton>;
}

export const prisma = globalThis.prisma ?? prismaClientSingleton();

const getUserEmbedding = async (userId: number): Promise<Embedding> => {
    const embedding: Embedding = await prisma.$queryRaw`
        SELECT embedding
        FROM "User"
        WHERE id = ${userId}
    `;
    return embedding;
};

prisma.$extends({
    model: {
        user: {
            async getEmbedding(userId: number) {
                return getUserEmbedding(userId);
            },
        },
    },
});
if (process.env.NODE_ENV !== "production") globalThis.prisma = prisma;

export default prisma;

user router:

...other routers
 deleteUser: protectedProcedure.mutation(async ({ ctx }) => {
        await ctx.prisma.image.deleteMany({
            where: { userId: ctx.session.user.id },
        });
        const deletedUser = await ctx.prisma.user.delete({
            where: { id: ctx.session.user.id },
        });
        return deletedUser;
    }),

    getEmbeddings: protectedProcedure.query(async ({ ctx }) => {
        await ctx.prisma.user <-- No getUserEmbedding?
    }),

...other routers
 deleteUser: protectedProcedure.mutation(async ({ ctx }) => {
        await ctx.prisma.image.deleteMany({
            where: { userId: ctx.session.user.id },
        });
        const deletedUser = await ctx.prisma.user.delete({
            where: { id: ctx.session.user.id },
        });
        return deletedUser;
    }),

    getEmbeddings: protectedProcedure.query(async ({ ctx }) => {
        await ctx.prisma.user <-- No getUserEmbedding?
    }),

I ended up using queryRaw and casting the response as text, so sort of a hack so I really hope Prisma starts to support a better way to handle unsupported types. For anyone who is (probably) going to have the same problem as me, this is how I handle it:

 getEmbeddings: protectedProcedure.query(async ({ ctx }) => {
        const embeddings = await ctx.prisma.$queryRawUnsafe(
            `SELECT embedding::text FROM "User" where id = ${ctx.session.user.id}`
        );
        return embeddings;
    }),

 getEmbeddings: protectedProcedure.query(async ({ ctx }) => {
        const embeddings = await ctx.prisma.$queryRawUnsafe(
            `SELECT embedding::text FROM "User" where id = ${ctx.session.user.id}`
        );
        return embeddings;
    }),

Gaming

Programming

Prisma w/pgvector