npm package discovery and stats viewer.

Discover Tips

  • General search

    [free text search, go nuts!]

  • Package details

    pkg:[package-name]

  • User packages

    @[username]

Sponsor

Optimize Toolset

I’ve always been into building performant and accessible sites, but lately I’ve been taking it extremely seriously. So much so that I’ve been building a tool to help me optimize and monitor the sites that I build to make sure that I’m making an attempt to offer the best experience to those who visit them. If you’re into performant, accessible and SEO friendly sites, you might like it too! You can check it out at Optimize Toolset.

About

Hi, 👋, I’m Ryan Hefner  and I built this site for me, and you! The goal of this site was to provide an easy way for me to check the stats on my npm packages, both for prioritizing issues and updates, and to give me a little kick in the pants to keep up on stuff.

As I was building it, I realized that I was actually using the tool to build the tool, and figured I might as well put this out there and hopefully others will find it to be a fast and useful way to search and browse npm packages as I have.

If you’re interested in other things I’m working on, follow me on Twitter or check out the open source projects I’ve been publishing on GitHub.

I am also working on a Twitter bot for this site to tweet the most popular, newest, random packages from npm. Please follow that account now and it will start sending out packages soon–ish.

Open Software & Tools

This site wouldn’t be possible without the immense generosity and tireless efforts from the people who make contributions to the world and share their work via open source initiatives. Thank you 🙏

© 2024 – Pkg Stats / Ryan Hefner

@fizzyapp/prisma-extension-pg-trgm

v0.0.2

Published

Prisma extension for supporting alphanumeric similarity using PostgreSQL pg_trgm module

Downloads

9

Readme

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

npm install prisma-extension-pg-trgm

Extending Prisma client

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

Basic usage

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:

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

Renamed model and field names

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:
model tags {
  id      Int     @id @default(autoincrement())
  tagName String? @map("tag_name")

  @@map("label")
}
  • Query:
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:

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