Share via

How to update or insert sqlite database in Web Apps?

Dieu Minh Ly 11 Reputation points
2021-04-06T22:30:00.933+00:00

Hi there,
I currently have an Azure Web App using PHP and SQLite.
In my localhost, I am able to update and insert into the SQLite database.
However, when I deploy the Web App to the Azure App Service, I can only select data from the SQLite .db file.
I cannot do update or insert because the database is locked.
Please let me know how to resolve this issue.
My Azure Web App is on Linux.
Thank you.

Azure App Service
Azure App Service
Azure App Service is a service used to create and deploy scalable, mission-critical web apps.
6,973 questions
0 comments

3 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Gordon Chen 6 Reputation points
    2021-12-08T04:47:04.983+00:00

    I think it is a bug. The web hosting space should be read and written. The purpose which user used Sqlite database is to simplify deployment. Because Sqlite don't need a server to run, it is a embed database, don't need to setup. Most of web hosting companies provide the user the rights for both reading and writing in web hosting space. Why you can't give the user the rights? Your solution trouble users, users have to use other database or looking for other storage. I found this bug only happen in "Linux" and "Code" mode, we can use Sqlite Database in "Windows" and "Code" mode in Your App Service(But it needs VSCode to deploy to Azure App Service). Hope you can improve App service, fix the bug in "Linux" and "Code" mode for the App Service. Thanks.

    1 person found this answer helpful.
  2. Ryan Hill 26,231 Reputation points Microsoft Employee
    2021-04-07T15:40:42.783+00:00

    Hi @Dieu Minh Ly ,

    This isn't a viable option. The underlying storage used by the app service is CIFS (Linux) and SMB (Windows) based. SQLite as a provider doesn't play well with these protocol types and you don't have sufficient access to change underlying properties of the storage. So, there are two options.

    1. The easiest, switch to a cloud offering like MySQL or Postgres. Tutorial: Build a PHP and MySQL app in Azure App Service walks you through how to setup a MySQL instance on Azure and connect your PHP application to it.
    2. You can create an Azure File Share and create a storage mount from your app to the share. See https://learn.microsoft.com/en-us/azure/app-service/configure-connect-to-azure-storage?pivots=container-linux.

    Regards,
    Ryan

  3. Sebastian Ma Lik Keung 1 Reputation point
    2021-07-01T03:52:35.337+00:00

    Hi all,
    When db file is placed back in application directory, even when codes write directly to the db, data is not persisted albeit no error thrown.
    Installed sqlite3 in the same docker image, inserted data using sqlite3. Stop and start Azure App Service. The data is not persisted, not durable.

    Regards
    Seb