prisma-extension-pg-trgm

Extending Prisma Client to support pg_trgm functions, exclusively for PostgreSQL Databases. pg_trm is used for determining similarity between texts based on trigram matching. For extensive documentation on pr_trgm, refer here

Features

- Queries similar to native Prisma's sysntax
- Fully Typed
- Support for filtering and sorting based on similarity scores
- List of functions implemented:
- similarity (text, text)
- word_similarity (text, text)
- strict_word_similarity (text, text)
- JSDoc

Installation

``sh
npm install prisma-extension-pg-trgm
`

Extending Prisma client

`ts
import { PrismaClient } from "@prisma/client";
import { withPgTrgm } from "prisma-extension-pg-trgm";

const prisma = new PrismaClient().$extends(withPgTrgm({ logQueries: true }));
`

> [!NOTE]
>
> logQueries can be set to true to log the queries on the console. It's useful for debugging, however should be turned off while deploying to production environments

Usage

$3

`ts
async function main() {
const result = await prisma.post.similarity({
query: {
title: {
similarity: { text: "interpreter", order: "desc" },
word_similarity: { text: "interpreter", threshold: { gt: 0.01 } },
strict_word_similarity: { text: "interpreter", threshold: { gt: 0.002, lte: 0.3 } },
},
},
});

console.log(result);
}

main();
`

> The query in the above example is converted to the following SQL query:
>
> `sql
> SELECT *,
> similarity(title, 'interpreter') AS title_similarity_score,
> word_similarity(title, 'interpreter') AS title_word_similarity_score,
> strict_word_similarity(title, 'interpreter') AS title_strict_word_similarity_score
> FROM "Post"
> WHERE
> word_similarity(title, 'interpreter') > 0.01 AND
> strict_word_similarity(title, 'interpreter') > 0.002 AND
> strict_word_similarity(title, 'interpreter') <= 0.3
> ORDER BY
> similarity(title, 'interpreter') desc
> `

$3

Prisma allows you to rename the model and field names using @@map and @map through the Prisma Schema. This has been explained in their official documentation here

However, the extension has no way to get those modified names. To counter that, look into the following example

Here the tagName field has been renamed to tag_name and the tags model has been renamed to label. So, in the database level, you'll find a table name as label with a column name as tag_name. As this extension relies on Raw Prisma queries, the actual table and column names are essential

- Prisma Schema:

`prisma
model tags {
id Int @id @default(autoincrement())
tagName String? @map("tag_name")

@@map("label")
}
`

- Query:

`ts
async function main() {
const result = await prisma.tags.similarity({
query: {
// the field in Prisma model is tagName, still in database it's tag_name
tag_name: {
similarity: { text: "or", threshold: { gte: 0.01 }, order: "desc" },
},
},
__meta: {
tableName: "label", // here the actual table name is passed
},
});

console.log(result);
}

main();
`

> The query in the above example is converted to the following SQL query:
>
> `sql
> SELECT *,
> similarity(tag_name, 'or') AS tag_name_similarity_score
> FROM "label"
> WHERE similarity(tag_name, 'or') >= 0.01
> ORDER BY similarity(tag_name, 'or') desc
> ``

Known Issues

- This extension relies on Raw Prisma queries. So, running un-safe queries might come into play. This extension doesn't handle any sanitization of the inputs internally. So, developers implementing this extenstions should put in the right checks before using this in a production system
- There's currently a quirky way to handle renamed model and field values described above. If there's a better way to handle this, please consider opening a Issue or a Pull Request detailing the approach
- Selecting specified fields is currently not supported. Currently all the fields in the model as well as the similarity scores are outputted.
- Joining tables are not supported. I'm not a fan of Prisma's joining techniques (https://github.com/prisma/prisma/discussions/12715) and supporting native join might be shelved for a future release