Places Diary #2: Removing Prisma

I started building Places using a Remix “stack” that made some decisions about development tools so I could get started iterating on ideas instead of spending time doing a bunch of tedious setup. One of the decisions that came with the stack was the use of Prisma as an ORM.

Prisma makes a lot of things easy:

Connecting to a database
Creating the schema
Handling migrations
Querying the database
CRUD operations

But there are other things you can’t do at all.

Things that I had to work around when using Prisma

Math

Prisma embeds its own binary of SQLite and it does not have SQLite’s math functions enabled. Places is a location-based app and location-based apps rely on the Haversine formula to calculate the geographical distance between points. It was possible to work around this by calculating the straight-line distance between points, but straight-line distance gets less accurate as points get farther apart.

Polymorphic associations

In Places you can add media to a location and you can add media to a check-in. It’s common to use a single table to store these types of associations in a clean and extensible manner. Prisma prefers to create a new table for each type of association. This results in a more complex schema, more complex queries, and more cumbersome maintenance over time. There has been an issue tracking polymorphic associations and unions for four years, but neither are on Prisma’s roadmap as of this writing.

Querying recursive relationships

Places are assigned categories and those categories are organized in a tree structure (e.g. filtering by Asian restaurants will show all Chinese, Thai, and Sushi restaurants). With Prisma, it’s possible to create those relationships, but you have to know the maximum level of nested categories to query them because it doesn’t support recursive queries.

Search

Primsa added support for full-text search for PostgreSQL in 2021. As of February 2024, they have not added full-text search support for SQLite. This means search in Places has been limited to simple pattern matching searches, such as finding saved places with names containing certain letters or words.

Compiling SQLite

Prisma includes its own SQLite binary and I’m not sure if it can be compiled with custom options. I’ve seen mention of compiling your own, but I haven’t seen any examples or docs for doing that. While it’s possible to drop down to raw queries in Prisma, raw queries won’t help if you need functions that aren’t enabled.

Removing Prisma

Because of all the challenges above, I decided to remove Prisma from the project and refactor all the CRUD operations to raw SQL. I’m using better-sqlite3 to interact with database. It was pretty painless to make the switch and now (for better or worse) I am in control of the database schema and queries. better-sqlite3 also bundles its own SQLite binary, but the bundled version is compiled with support for math functions and full-text search. The docs also include information for compiling a custom configuration should I need to in the future.

Prisma was helpful for getting started, but I think replacing its abstraction with direct interaction with SQLite will be more productive and flexible in the long term.

Interested in trying Places?

If you are interested in trying Places, sign up for the waitlist and be among the first to know when it’s ready.