P
Prisma•2mo ago
Arturs

Prisma and Pothos integration using Raw SQL

I have a use case where a “prismaField” implements resolve which is based on Raw SQL because of full-text search usage. Let's say we are searching for users by name through full-text search. The key problem starts when I use Prisma Pothos.js integration to solve N+1 through relations. Code example from pothos.js prisma docs 
builder.queryType({
  fields: (t) => ({
    me: t.prismaField({
      type: 'User',
      resolve: async (query, root, args, ctx, info) =>
        prisma.user.findUniqueOrThrow({
          ...query,
          where: { id: ctx.userId },
        }),
    }),
  }),
});
 
builder.prismaObject('User', {
  fields: (t) => ({
    id: t.exposeID('id'),
    email: t.exposeString('email'),
    posts: t.relation('posts'),
  }),
});
 
builder.prismaObject('Post', {
  fields: (t) => ({
    id: t.exposeID('id'),
    title: t.exposeString('title'),
    author: t.relation('author'),
  }),
});
builder.queryType({
  fields: (t) => ({
    me: t.prismaField({
      type: 'User',
      resolve: async (query, root, args, ctx, info) =>
        prisma.user.findUniqueOrThrow({
          ...query,
          where: { id: ctx.userId },
        }),
    }),
  }),
});
 
builder.prismaObject('User', {
  fields: (t) => ({
    id: t.exposeID('id'),
    email: t.exposeString('email'),
    posts: t.relation('posts'),
  }),
});
 
builder.prismaObject('Post', {
  fields: (t) => ({
    id: t.exposeID('id'),
    title: t.exposeString('title'),
    author: t.relation('author'),
  }),
});
Because based on their example, I need to pass down the query object to prisma, so the whole preloading would work out of the box. But how can I do it together with using raw SQL? Is there a util that converts prisma POJO to raw SQL that I could add as a fragment?
1 Reply
Nurul
Nurul•2mo ago
Hello 👋 There isn't an inbuilt util to convert prisma queries to raw SQL. We have a feature request for it here. The query logging parameter allows you to log the underlying SQL query. On a sidenote, we recently introduced TypedSQL, which provides type safety for raw SQL queries. While it doesn't directly solve your N+1 issue, it might help in creating more maintainable raw SQL queries.
GitHub
Using Prisma Client as query builder only and get the SQL queries l...
Problem Adding this feature opens the possibility to use Prisma Client as a query builder only. Meaning getting the SQL queries without actually hitting the database. What Lucas wanted is to be abl...
Writing Type-safe SQL with TypedSQL and Prisma Client | Prisma Docu...
Learn how to use TypedSQL to write fully type-safe SQL queries that are compatible with any SQL console and Prisma Client.
Logging | Prisma Documentation
Learn how to configure Prisma Client to log the raw SQL queries it sends to the database and other information.

Did you find this page helpful?