I've started refactoring one of my older JavaScript projects into TypeScript, and this time, I'm also swapping out SQLite for PostgreSQL using Prisma.
The migration hasn't been entirely smooth, especially when dealing with advanced database features like full-text search.
For one of my models, I needed to auto-generate a search vector from fields like name, description, and skills. This was crucial for the recommendation system I'm building, where projects are matched based on context and developer metadata. I'm doing this dynamically whenever a record is updated.
In SQLite, I had built a workaround using a raw SQL trigger to keep the search vector updated in the background. But when I moved to Prisma with PostgreSQL, things got tricky. Prisma doesn't officially support PostgreSQL's tsvector type yet, so I had to mark the field as Unsupported("tsvector") in my Prisma schema.
Now, every time I update a model, I run a raw SQL command to handle the tsvector update manually. I created a custom utility function that fires right after each model update and handles this with raw SQL inside the PostgreSQL database.
It's not elegant, but it works — and it's a reminder that migrations often come with some messy but necessary trade-offs.
Still, I'm enjoying the flexibility and type safety TypeScript brings to the table. Prisma's DX is fantastic overall, and this project is helping me push deeper into the real-world constraints of ORM tooling.