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:
- 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.
- 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.
- 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.