Introduction

Fly.io is a cloud platform that allows developers to easily deploy scalable applications. In this article, we will introduce how to manage databases effectively in an application using Remix, Prisma, and LiteFS on Fly.io.

What is LiteFS?

LiteFS is a replication tool for SQLite databases, designed for use in distributed environments. It operates in the same environment as the application, ensuring fast data reading and low latency. Additionally, with automatic scaling features, it offers low scaling costs and high availability. Distributed SQLite is one of the technologies gaining attention due to increasing demands for fast response times.

Limitations and Challenges of SQLite

SQLite is a lightweight and easy-to-use database, but because it lacks a daemon process, checking and modifying data on the server requires accessing the application container and operating on the command line. For MySQL or PostgreSQL, you can use port forwarding to the DB server and DB client GUI apps (e.g., Sequel Ace, pgAdmin) to manipulate the data on the server, but this is difficult with SQLite.

Utilizing Prisma Studio

Prisma Studio is a database management tool provided by Prisma that allows you to visually manipulate data in your browser. If your application uses Prisma as an ORM during development, utilizing Prisma Studio for data management is convenient.

Configuration Steps

Connect to the Application Container

First, connect to the Fly.io server using SSH.

fly ssh console -a {your_application_name}

Start Prisma Studio

Next, start Prisma Studio.

npx prisma studio

Set Up Port Forwarding

Set up port forwarding to access Prisma Studio locally.

fly proxy 5555:5555 -a {your_application_name}

Access the Forwarded Local Port

Open your browser and access the following URL.

http://localhost:5555

Security Considerations

Starting and Stopping Studio

In a properly operated service, there are not many cases where database investigation or manipulation is necessary. It is not desirable to always keep Prisma Studio running from both a security and memory efficiency perspective, so it is recommended to start it only when necessary.

Use Non-Public Ports

Make sure to use a non-public port that cannot be accessed externally when starting Prisma Studio. Check your fly.toml configuration carefully.

Conclusion

By leveraging LiteFS, a distributed SQLite, you can build scalable and high-performance applications. This article introduced how to efficiently manage data using Prisma Studio while considering security to address the data management challenges of SQLite.