add PostgreSQL-specific indexes without Prisma drift?
I'm implementing vector similarity search with
pgvector and need to create specialized indexes (HNSW for embeddings, GIN for full-text search) that Prisma doesn't support in schema.prisma.
Problem:
1. Create a manual SQL migration with CREATE INDEX ... USING hnsw/GIN
2. The migration applies successfully ✅
3. But then Prisma detects "drift" because these indexes exist in the DB but aren't defined in schema.prisma
4. It tries to create a new migration to DROP them 😢
What I've tried (none of these works):
- Adding @@index([embedding]) in schema.prisma → Prisma creates B-tree, not HNSW
- Manually editing generated migrations → Checksum mismatch errors
- Using IF NOT EXISTS in migrations → Still detects drift on subsequent runs
Question:
What's the recommended approach in Wasp/Prisma for adding PostgreSQL-specific index types that aren't in the schema file?2 Replies
Hi @NEROX. I'm kapa.ai, your friendly support bot. I will try to answer your question by searching through the knowledge base. :wave:
----------------------
Wasp (and Prisma) currently do not provide a built-in way to define PostgreSQL-specific index types like HNSW or GIN directly in the schema.prisma file. Prisma only supports standard B-tree indexes via the @@index attribute, and does not expose a way to specify index types or advanced options. As a result, when you create such indexes manually via SQL migrations, Prisma's migration engine may detect "drift"—it sees a difference between the schema.prisma and the actual database schema, and may try to drop your custom indexes in subsequent migrations.
This is a known limitation and pain point for users working with advanced PostgreSQL features, including vector search with pgvector and full-text search with GIN indexes. The issue is discussed in the Wasp community, and the recommended approaches are as follows:
Current Workarounds and Recommendations:
1. Manual SQL Migrations (with Drift):
- You can create a manual migration with your custom index (e.g.,
There is currently no way in Wasp/Prisma to define PostgreSQL-specific index types in schema.prisma or to prevent Prisma from detecting drift for such indexes. The best practice is to manage these indexes outside of Prisma and be aware of the drift warnings. Keep an eye on Wasp and Prisma updates, as this is a known area of improvement. If you need to automate this, consider scripting the index creation as a post-migration step, and document this in your deployment process. If you need more details or want to follow progress, see the related discussions: - Allow users to add postgres extensions, e.g. pgvector - Document how to use Wasp with ChatGPT embeddings (pgvector) - Prisma Configuration ---------------------- Note: you can ask me a follow up question by @ mentioning me again :speech_balloon: ---------------------- Sorry @NEROX, I seem to be having a bit of trouble. Don't worry though, I've pinged my support and they will try to fix me up! :technologist: Perhaps try again in 15mins?
CREATE INDEX ... USING hnsw). However, as you've experienced, Prisma will detect drift and may try to remove these indexes in future migrations. There is no official way to tell Prisma to "ignore" these indexes or mark them as managed outside of Prisma.
2. Unsupported Types and Raw Queries:
- For unsupported column types (like vector), you can use Prisma's Unsupported type and interact with those columns using $queryRaw. However, this does not solve the index drift issue, only the column type problem Document how to use Wasp with ChatGPT embeddings (pgvector).
3. Prisma Preview Features and Extensions:
- Wasp now supports enabling Prisma's postgresqlExtensions preview feature and specifying extensions in the app.db.prisma.dbExtensions field. This is useful for enabling extensions like pgvector, but it does not extend to index definitions Prisma Configuration.
4. External Management:
- The most robust approach is to manage these specialized indexes outside of Prisma's migration system. This means:
- Creating and maintaining them manually (e.g., via a separate SQL script or migration tool).
- Accepting that Prisma will not be aware of these indexes, and you may need to re-apply them if the database is reset or re-created.
- Monitoring for drift warnings and ignoring them for these specific indexes.
5. Feature Requests and Limitations:
- The Wasp and Prisma teams are aware of this limitation. There are open issues discussing the need for more flexible schema customization, including the ability to add custom SQL to migrations or to extend the Prisma schema with unsupported features Allow users to add postgres extensions, e.g. pgvector, Allow specifying Postgres extensions in Prisma.
Summary:There is currently no way in Wasp/Prisma to define PostgreSQL-specific index types in schema.prisma or to prevent Prisma from detecting drift for such indexes. The best practice is to manage these indexes outside of Prisma and be aware of the drift warnings. Keep an eye on Wasp and Prisma updates, as this is a known area of improvement. If you need to automate this, consider scripting the index creation as a post-migration step, and document this in your deployment process. If you need more details or want to follow progress, see the related discussions: - Allow users to add postgres extensions, e.g. pgvector - Document how to use Wasp with ChatGPT embeddings (pgvector) - Prisma Configuration ---------------------- Note: you can ask me a follow up question by @ mentioning me again :speech_balloon: ---------------------- Sorry @NEROX, I seem to be having a bit of trouble. Don't worry though, I've pinged my support and they will try to fix me up! :technologist: Perhaps try again in 15mins?
I think this should be the way to go:
Manually editing generated migrationsIIRC The checksum errors might happen when you're modifying an already-applied migration. Can you try to rollback that migration, then modifying it, and then re-applying?