Prisma ORM with SQLite - how to escape underscores in a query

Hello! I have a small web app set up and using Prisma to query a SQLite database and am running into some confusion. I'm doing a search based on a query string thus:

const results = await prisma.plotFlag.findMany({
  where: {
    OR: [
      {
        name: {
          contains: query,
        },
      },
      {
        description: {
          contains: query,
        },
      },
    ],
  },
});

const results = await prisma.plotFlag.findMany({
  where: {
    OR: [
      {
        name: {
          contains: query,
        },
      },
      {
        description: {
          contains: query,
        },
      },
    ],
  },
});

Which seems to work fine except that queries that include

have that underscore treated as a wildcard character (as is I believe normal in SQLite). Backslashes don't work to escape the underscore, and it looks like the standard way to do this in SQLite is to add an escape clause at the end of the query to declare a particular character as an escape character (i.e.

where name LIKE '%test\_%' ESCAPE '\'

where name LIKE '%test\_%' ESCAPE '\'

).

I am guessing that I could do this with a raw query/TypedSQL if necessary, but I'd rather take advantage of the ORM syntax if I can; is there any way to handle this scenario without resorting to raw execution?

Thanks much!

Prisma•10mo ago•

3 replies

blackjackkent

Prisma ORM with SQLite - how to escape underscores in a query

Hello! I have a small web app set up and using Prisma to query a SQLite database and am running into some confusion. I'm doing a search based on a query string thus:

const results = await prisma.plotFlag.findMany({
  where: {
    OR: [
      {
        name: {
          contains: query,
        },
      },
      {
        description: {
          contains: query,
        },
      },
    ],
  },
});

const results = await prisma.plotFlag.findMany({
  where: {
    OR: [
      {
        name: {
          contains: query,
        },
      },
      {
        description: {
          contains: query,
        },
      },
    ],
  },
});

Which seems to work fine except that queries that include

where name LIKE '%test\_%' ESCAPE '\'

where name LIKE '%test\_%' ESCAPE '\'

Prisma ORM with SQLite - how to escape underscores in a query

Similar Threads

Prisma ORM with SQLite - how to escape underscores in a query

Similar Threads

Similar Threads

Similar Threads