P
Prisma•7mo ago
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 _ 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 '\'). 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!
1 Reply
Prisma AI Help
Prisma AI Help•7mo ago
Skipping coffee for instant knowledge! Let's brew that AI answer now. You're correct: in SQLite (and other SQL databases), the underscore _ in a LIKE pattern is treated as a single-character wildcard. Escaping it typically requires an ESCAPE clause, e.g., LIKE '%test\_%' ESCAPE '\'. However, Prisma's ORM query API for SQLite does not currently provide a built-in way to specify a custom escape character or to escape wildcards in LIKE patterns directly within the ORM syntax. The official Prisma documentation and discussions confirm that, for MySQL and PostgreSQL, you can escape wildcards by prefixing them with a backslash in your query string (e.g., \\_). But for SQLite, this approach does not work, and the recommended solution is to use a raw SQL query when you need precise control over escaping in LIKE patterns. This is also reflected in official answers to similar questions: "You may need to write raw queries for SQLite" when you want to match literal wildcard characters like % or _ in your search string How does filtering work at the database level?, How to query '%' string?. So, if you need to escape underscores in a LIKE pattern with SQLite using Prisma, you will need to use a raw query (e.g., with $queryRaw or TypedSQL). There is currently no way to do this using only the Prisma ORM query syntax for SQLite. Summary:
- Escaping _ in LIKE patterns is not supported in Prisma's ORM API for SQLite. - Use a raw SQL query with an ESCAPE clause for this scenario. If you want to keep track of this limitation or request improvements, you can follow related issues and discussions in the Prisma GitHub repository. ✅ Great, thanks for letting us know that this solved your question!

Did you find this page helpful?