I have a Python flask app that I want to see if I can turn it into a product, and it just needs a simple storage like SQLite, but it needs to have read/write capabilities. I want to do this as cheap as possible for now because there's a lot of unknowns on if it would even be successful.
Currently, I'm able to push it out to an Azure App Service via VSCode, and it deploys successfully. I have the existing SQLite database included in the Deployment, and it seems to work OK, however, when I restart the App Service, all the data gets reset, which I don't understand? I thought the data would persist since it's part of the app? I do have issues with the app freezing and I haven't figured out why. It all works perfectly when ran locally.
Could I attach an Azure File storage to the App Service and store the SQlite database on there? Is that possible? I'd be using flask_sqlalchemy.
EDIT
I accepted the answer below because it did create the db, but it still doesn't work. When using the app, at some point I'll get the error:
sqlite3.OperationalError: unable to open database file
Even though the db still shows in the directory, and it worked fine before. I don't know what goes wrong.
It's so unfortunate that SQLite seems to just not work with Azure App Services because it's necessary for young apps that are just trying to test the market.
I just hope that Microsoft isn't doing this on purpose because I trust them and that would really burn me.
Regarding the issue of data reset when restarting the App Service, it is because the SQLite database is stored in the temporary storage of the App Service. When you restart the App Service, all data in the temporary storage is deleted.
If you go to the section Development Tools of your Azure App Service, you can SSH and inspect the filesystem of your Web App. You will be warned about any data outside of '/home' is not persisted. Moreover, if you list the files of the current directory, you will find your app files, including your SQLite database inside a /tmp folder.
Therefore, given your scenario, to keep it simple and cheap, my suggestion to avoid complexity by using other Azure Services to persist your database is to save your SQLite file inside /home/site/wwwroot folder as mentioned here.
I built an basic flask example app to test this and it worked.
Project structure
│ app.py
│ database.db
│ requirements.txt
├───templates
├──────index.html
├───venv
index.html
<!DOCTYPE html>
<html>
<head>
<title>Message Board</title>
</head>
<body>
<h1>Message Board</h1>
<form action="/add_message" method="post">
<input type="text" name="message">
<button type="submit">Submit</button>
</form>
<hr>
<ul>
{% for message in messages %}
<li>{{ message['message'] }}</li>
{% endfor %}
</ul>
</body>
</html>
app.py
import os
from flask import Flask, render_template, request, redirect, url_for
import sqlite3
app = Flask(__name__)
# create the database connection
def get_db_connection():
if "AZURE" in os.environ:
# running on Azure
db_path = "/home/site/wwwroot/database.db"
else:
# running locally
db_path = "database.db"
conn = sqlite3.connect(db_path)
conn.row_factory = sqlite3.Row
return conn
# create the table if it doesn't exist
def create_table():
conn = get_db_connection()
conn.execute(
"CREATE TABLE IF NOT EXISTS messages (id INTEGER PRIMARY KEY AUTOINCREMENT, message TEXT)"
)
conn.commit()
conn.close()
create_table()
@app.route("/")
def index():
conn = get_db_connection()
messages = conn.execute("SELECT * FROM messages").fetchall()
conn.close()
return render_template("index.html", messages=messages)
@app.route("/add_message", methods=["POST"])
def add_message():
message = request.form["message"]
conn = get_db_connection()
conn.execute("INSERT INTO messages (message) VALUES (?)", (message,))
conn.commit()
conn.close()
return redirect(url_for("index"))
if __name__ == "__main__":
app.run(debug=True)
This code uses the current working directory on local development while it uses /home/site/wwwroot on Azure. This is possible since I added an App Setting called AZURE with any value at the Configuration section from Azure App Service.
Later, you can safely stop or restart your Web App. The new database.db will be saved at a persistent location.
And again, you can verify that your database.db was not deleted if you SSH into your machine following the steps I mentioned earlier.
Regarding the app freezing issue, it's hard to say without more information about the app and the Azure App Service configuration. You might want to enable application logging and detailed error messages in the Azure App Service to get more information about what's causing the app to freeze.
Finally, regarding PaaS database options in Azure, I'd suggest to go for the serverless compute tier of Azure SQL Database if you do not have too many requests and you don't mind about cold starts. Here is a useful YouTube video that talks about pricing.