Python-Flask Web App lost changes to sqlite3 database

Question

Hi We have just deployed a python-flask-sqlite3 web app to azure and have this issue. We can successfully add data to sqlite3 database through the web app, we log-out the app and enter again an we can get the added data and display it through the app everything looks fine. We can login logout many times during 3 or 4 hours and the info is there, however after five or six hours suddenly the added data disappears from de database and we can only see the initially deployed data. It seems like if a commit is not been done but is not about a commit in our app but a commit at azure web app services level that may we have not configured yet. Any advice is welcome.. Regards

Answer 1

Azure Web Apps run on a shared infrastructure, and the local filesystem is not guaranteed to be persistent. Your SQLite3 database file is likely being stored on the local filesystem, which might cause data loss as you have experienced. Using SQLite3 in this manner is not recommended for Azure Web Apps. Here are a few recommendations to resolve this issue:

1. Use Azure SQL Database: Instead of using SQLite3, consider using an Azure SQL Database, which is a fully managed relational database service provided by Azure. It's designed for scalable and reliable applications and offers data persistence, backups, and monitoring. You can follow the official documentation on how to create an Azure SQL Database and the official Flask-SQLAlchemy documentation to update your application to use Azure SQL Database.
2. Use Azure Blob Storage: If you still want to use SQLite3, you can store the SQLite3 database file in Azure Blob Storage and use Azure Blob Storage Fuse to mount it as a virtual directory in your Web App. However, note that SQLite is not designed for high-concurrency access, and you might experience performance issues or database locking with this approach.
3. Use Azure Files: Another option is to use Azure Files, a managed file share service that can be accessed via SMB or REST. You can mount an Azure File share as a local directory in your Web App and store your SQLite3 database file there. This approach is similar to using Azure Blob Storage Fuse, but it uses Azure Files instead. However, similar to using Azure Blob Storage, SQLite might experience performance issues or database locking when used in this manner. The best practice for a production environment is to use a managed relational database service like Azure SQL Database or another supported database like MySQL or PostgreSQL. These databases are designed for high-concurrency access and offer better performance, reliability, and scalability compared to SQLite3.