This boilerplate uses SQLite for production. SQLite is a self-contained, serverless, zero-configuration, transactional SQL database engine. It is the most widely deployed database in the world with millions of users. We are accessing the database by using the Prisma ORM. This ORM is used to interact with the database and perform CRUD operations, and generally to ease the process of working with databases.
From my professional experience, we had to implement this stack to create a standalone application that could be used by multiple users. Prisma and SQLite were are go-to choices because of their ease of use and the fact that they are both open-source. Up until we encountered some issues with this stack... but that's a story for another time.
The implementation of the SQLite database is done in the prisma/schema.prisma file. This file is used to define the database schema and the tables that will be used in the database. The schema is then used to generate the Prisma client, which is used to interact with the database. The Prisma client is generated by running the prisma generate command, and applying new migrations is done by running the prisma migrate dev command. Keep in mind this command should be run only in development mode as it erases the data in the database. And here we encounter the first issue with this stack, the fact that we can't run migrations in production without erasing the data in the database.
As we don't want any interuptions or data loss, but are unable to run npm scripts on production(user's) machine, we need to run migrations on startup. Essentially, we are copying empty database file found in root of the boilerplate(database.db) and copying it to the user's home directory. This way, the user is responsible for maintaning the database and keeping it safe. Inside it, we create a _prisma_migrations table which keeps track of applied migrations. This way, we can run migrations on startup and apply only the new ones.
As the migration folder is bundled into our production build, we keep track of the newly added/removed migrations. If there was a new migration, we would simply apply it to the user's database. If there was a removed migration, we would remove it from the _prisma_migrations column. This way, we can keep the database up to date without any data loss.
This is a simple and effective way to implement a SQLite database in a production environment. It is easy to use and maintain, and it is a good choice for standalone applications that need a simple and lightweight database engine. However, it has some limitations, such as the inability to run migrations in production without erasing the data in the database. But with the implementation described above, we can overcome this limitation and have a reliable and efficient database implementation for our application. For now, this is how we are going to implement SQLite in production for this boilerplate. Any changes or improvements are greatly appreciated.