Prisma•2d ago

creating a BM25 index

I have created a BM25 index by creating a migration file to create it and also added the schema to ignore the index.

model Post {
  id                           Int                                   @id @default(autoincrement())
  name                         String?
  /// @ignored
  /// @schema-ignored-index(post_bm25_idx)
  @@index(fields: [id, name], map: "post_bm25_idx")
}

model Post {
  id                           Int                                   @id @default(autoincrement())
  name                         String?
  /// @ignored
  /// @schema-ignored-index(post_bm25_idx)
  @@index(fields: [id, name], map: "post_bm25_idx")
}

and here is the custom migration file i created to run:

-- CreateIndex
-- prisma-ignore-index
CREATE INDEX post_bm25_idx ON "Post"
USING bm25 (id, name)
WITH (
    key_field='id',
    text_fields='{
        "name": {"fast": true}
    }'
);

-- CreateIndex
-- prisma-ignore-index
CREATE INDEX post_bm25_idx ON "Post"
USING bm25 (id, name)
WITH (
    key_field='id',
    text_fields='{
        "name": {"fast": true}
    }'
);

but the next time i run a migration or try to create a new one, it hangs with a message like:

prisma:schemaEngine:stderr {"timestamp":"2025-02-04T01:31:58.231558Z","level":"WARN","fields":{"message":"Unknown index algorithm on post_bm25_idx: bm25"},"target":"sql_schema_describer::postgres","span":{"namespaces":"None","name":"describe_schema"},"spans":[{"name":"DevDiagnostic"},{"namespaces":"None","name":"describe_schema"}]} +0ms

prisma:schemaEngine:stderr {"timestamp":"2025-02-04T01:31:58.231558Z","level":"WARN","fields":{"message":"Unknown index algorithm on post_bm25_idx: bm25"},"target":"sql_schema_describer::postgres","span":{"namespaces":"None","name":"describe_schema"},"spans":[{"name":"DevDiagnostic"},{"namespaces":"None","name":"describe_schema"}]} +0ms

this is blocking me from running or creating new migration file and the database is in a limbo. please help!

5 Replies

Prisma AI Help•2d ago

You selected the bot's instantaneous insights. Let me compile your answer right away!

RaphaelEtim•2d ago

Hi @ididit @schema-ignored-index isn't a valid statement in Prisma. You may also look at this issue for how to setup custom index with Prisma

Prisma AI Help•this hour

Based on the information provided in the knowledge sources, I don't have enough specific information to fully address your issue with the BM25 index in Prisma. However, I can provide some insights and suggestions based on related issues: 1. Unsupported index types: Prisma currently has limited support for custom index types. The error message you're seeing suggests that Prisma doesn't recognize the "bm25" index algorithm, which is why it's hanging during migration. 2. Ignoring custom indexes: There have been several feature requests and issues raised about ignoring custom indexes in Prisma. For example, Github Issue: A way to ignore indexes on Prisma Schema discusses the need for a directive to ignore existing indexes. 3. Workaround for custom indexes: One possible workaround, as suggested in Github Issue: Prisma drops indexes defined outside of Prisma, is to: a. Use prisma migrate dev --create-only to create a new migration without applying it. b. Manually edit the migration.sql file to add your custom index creation SQL. c. Apply the migration using prisma migrate dev without the --create-only flag. d. When adding or changing indexes, do it one at a time to avoid transaction issues. 4. Separate SQL migrations: As mentioned in a comment on the issue above, you might consider keeping separate SQL migrations for production indexes and other custom database objects that Prisma doesn't fully support. Unfortunately, the @ignored and @schema-ignored-index annotations you've used in your schema are not standard Prisma syntax and are not recognized by Prisma, which is likely why they're not working as intended. To resolve your current situation: 1. Try removing the custom annotations from your Prisma schema. 2. Consider dropping the BM25 index manually from your database. 3. Follow the workaround steps mentioned above to recreate your index in a way that Prisma won't try to drop it in future migrations. Keep in mind that this is a limitation of Prisma's current functionality, and there are ongoing discussions and feature requests to improve support for custom indexes and multi-schema setups. You may want to follow or contribute to these discussions on the Prisma GitHub repository for updates on this functionality.

ididitOP•10h ago

@RaphaelEtim hi raphael, can you show me which issue you were referring to look at?

RaphaelEtim•10h ago

Oh! I thought i linked it earlier. https://github.com/prisma/prisma/issues/12914#issuecomment-1713041472

GitHub

Prisma drops indexes defined outside of Prisma · Issue #12914 · pri...

Problem I don't know if this is feature request or a bug report. When possible, developers should define indexes using Prisma schema language. However there are many scenarios where a developer...

Gaming

Programming

creating a BM25 index

Did you find this page helpful?