🚀 Feature: client-server relational DBMS

[drustan]

🔖 Feature description

Implement a connector so we can use a client-server relational DBMS like MySQL or PostgreSQL instead of SQLite.

🎤 Pitch

I started a HELM chart to deploy Pingvin Share on Kubernetes. This is for now just a proof of concept because the application currently lacks support for concurrent access from multiple clients to the database, so only one replica cans run at a given time.

In my opinion, it would be great to implement a client-server relational DBMS like PostgreSQL or MySQL. These databases would add scalability and permit multiple instances to run in parallel behind a load balancer.

That said, I must admit that I'm not familiar enough with the application to ascertain whether implementing this would be the only required feature to ensure the application to run smoothly with round-robin redirections of a client between Pingvin Share instances.

[drustan]

Thanks for the awesome app by the way !

[stonith404]

Hey, yeah this is currently an issue of Pingvin Share. We are using the Prisma ORM and they don't allow to change the database provider because the migrations are written in the database specific syntax (see the docs).

But it's definitely important to be able to switch database providers. I have to find a solution. One solution would be to write an own migration engine but I'm not sure how difficult this is.

I don't know much about Kubernetes but it interests me how you would manage the stored files with multiple replicas. Can you mount a folder to all replicas or how would you manage this?

[drustan]

You can actually use what is called a Kubernetes Persistent Volumes (PVs) to create a shared storage resource that can be accessed by multiple replicas of an application.

The PVs represent the actual storage, which can be hosted on cloud storage, network-attached storage (NFS), or other storage solutions. Kubernetes ensures that each replica gets access to the same shared storage. This way, all replicas can read and write data to a centralized storage location, enabling data sharing among them.

In my setup, I use a Persistent Volume to mount the directories /opt/app/frontend/public/img and /opt/app/backend/data and preserve data over redeployment. Additionally, I've configured the directories /home/node and /opt/app/frontend/.next/cache as ephemeral volumes to enable running the container's file system in read-only mode.

volumeMounts:
            - name: node-home
              mountPath: /home/node
            - name: frontend-cache
              mountPath: /opt/app/frontend/.next/cache
            - name: data
              mountPath: /opt/app/backend/data
              subPathExpr: data
            - name: data
              mountPath: /opt/app/frontend/public/img
              subPathExpr: img

[stonith404]

Oh that's awesome, thanks for sharing!

Wouldn't mounting the sqlite database as a PV work as a temporary solution?

Nevertheless, I hope that I find a solution for the database.

[drustan]

Sorry for the late reply.

I think it would sort of work. The only potential issue is if two instances are trying to write to the database at the same time. I'm not an expert with SQLite, but I believe there is a lock mechanism in place.

[stonith404]

Yeah you're right