database issue

Question

I have my project set up on GitHub, which includes the frontend and backend code, along with the GitHub Actions workflow for deploying to Azure Container Apps. I'm using GitHub Actions to build and deploy, and it's working correctly.

I've successfully deployed both the frontend and backend as separate Azure Container Apps. The frontend is running fine, and the website is accessible. However, the backend uses SQLite as its database, and it's not responding properly in Azure. I can open the site, but I’m unable to log in or register a new user.

How can I resolve this issue with the SQLite database on Azure?

Answer 1

Hello @satish muthum,

In addition to @Bruce (SqlWork.com),and best practices consideration:

The issue with using SQLite on Azure likely stems from its limitations in cloud environments, as it's better suited for local development. To improve performance and scalability, it's recommended to migrate to Azure Database for PostgreSQL, a fully managed and secure service.

Start by exporting your SQLite data, create a PostgreSQL instance in Azure, and import the data using tools like pg_dump and pg_restore. Then, update your backend's connection settings and test the application before redeploying. This migration will ensure better reliability for your app.
Hope this helps. Do let us know if you any further queries.

Answer 2

SQLIte does not make sense for an Azure container app, unless it's just a read cache. to write to SQLite and have it persist, you need to use a mounted storage volume because container storage is ephemeral and lost when the container shuts down or is restarted.

https://learn.microsoft.com/en-us/azure/container-apps/storage-mounts?tabs=smb&pivots=azure-cli

Container Apps are also replicated for load balancing. while you can have the storage replicated, then each instance will have its own copy of the database file.

using a mounted volume will persist the database between instances and allow instances to use the database file. But SQLite does not support two concurrent instances accessing the same file unless the database is read only.

while you probably should pick a different database, you could start by using a container instance instead of container app, and only configure one instance. You should still use a mounted storage volume to host the database file, to not lose data.

https://learn.microsoft.com/en-us/azure/container-instances/