Recently, I read about the utility of SQLite for web servers and was intrigued by the possibility of using SQLite and an Azure Function to host an effectively free web application. Since an Azure Functions app comes along with an Azure Files storage account by default, the Azure Function should be able to store and read an SQLite file.
Researching into this concept, I found that it had been tried in the past but I was still curious about the scalability and performance of such a solution. I also wanted to validate the possibility of hosting an entire application on an Azure Function rather than just an API, with the goal of offloading the frontend SPA-like processing from my old device. This is the project I built to achieve this objective.
Personal Finance Tracker is a proof-of-concept application demonstrating the possibility of hosting an entire application within an Azure Functions application. The NodeJS Azure Function dynamically generates HTML for the interface, processes API requests for any CRUD actions, and stores data in an SQLite database file.
The application allows the manual entry of statements of accounts and shows a graph of the history of the total of the amounts in every account.
The Azure Function app was scaffolded using Visual Studio Code's Azure extension. 5 HTTP Trigger functions with anonymous authorization were created.
GET /api/Home generates and returns the HTML to be rendered by the browser.
POST /api/CreateAccount and
DELETE /api/DeleteAccount are REST API endpoints that allow the creation and deletion of an account (column).
POST /api/CreateStatements and
DELETE /api/DeleteStatements are also REST API endpoints that allow the creation and deletion of statements (row). A shared database access helper file is also created in the
Note that Azure Functions are typically used as APIs, and have their routes prepended with
/api/. However, since we are using Azure Functions in a manner counter to intended, the
/api/Home route will return an HTML page.
The helper file
/Home/renderHTML.js is responsible for generating the HTML file. Styles and scripts are included within the HTML since they cannot be referred to as files as this is not a typical web server. Additional configurations would have had to be done to serve these static files. This makes the
renderHTML.js file somewhat busy, and an area for potential improvements.
The database contains 2 tables:
accounts represent the accounts identified by their title, and
statements represent the amount in an account on a specific date.
If you want to deploy the application as a Windows Function App, keep reading. If you prefer to deploy as a Linux Function App, jump to the note in the Additional Notes section below.
- Clone the source code of the application locally. Run
func startand ensure the application is working locally.
- Create an Azure Function resource in the Azure Portal, configured as a Consumption app with Windows as the OS. Take note of the name of the Function App you just created. This step will create the accompanying storage account which will store the SQLite database.
- Deploy the application using the
func azure functionapp publish <FUNCTION_APP_NAME> --nozip. This deploys the local source code to the Azure Function resource, and the
--nozipoptions turns off Run-From-Package mode. This will allow us to manually install npm packages in the Azure portal.
- From the Azure Portal, navigate to the Azure Function. In the left menu pane of the Azure Function, navigate to Console.
npm install sqlite sqlite3in the console to install
sqlite3npm packages for the Azure Function. This may take a few minutes.
- Try out the application! Navigate to
https://<FUNCTION_APP_NAME>.azurewebsites.net/api/Homeand test it out!
sqlite3 must be installed within the Azure portal to ensure that the native bindings used by
sqlite3 correspond to the OS version of the host machine running the Azure Functions. Those packages only need to be installed once. Future deployments will only update the source code being used.
Performance & benchmarking
My main concern with such a usage of SQLite on Azure Functions would be the performance. Since this is not an intended usage of Azure Functions, the file locking and concurrency of the SQLite file could slow down the response times of the Azure Function. Therefore, I benchmarked the response times under varying loads using Apache Benchmark. Note that this benchmarking was done on functions that were 'warmed-up' to avoid the response delay caused by a function cold-start.
ab -n 100 -c 10 "https://FUNCTION_APP_NAME.azurewebsites.net/api/home"
Connection Times (ms) min mean[+/-sd] median max Connect: 75 96 11.2 94 152 Processing: 266 613 209.6 570 1435 Waiting: 242 582 209.2 541 1409 Total: 356 708 209.0 664 1524 Percentage of the requests served within a certain time (ms) 50% 664 66% 781 75% 849 80% 887 90% 974 95% 1068 98% 1327 99% 1524 100% 1524 (longest request)
These results demonstrate the response times if 10 users concurrently made 10 requests each. With a mean of 0.7s and a longest time of 1.5s, we can see that these response times are decent.
As expected, increasing the concurrency count increases the response times. With a concurrency of 100 users, the application response times make for a poor user experience.
ab -n 100 -c 100 "https://FUNCTION_APP_NAME.azurewebsites.net/api/home"
Connection Times (ms) min mean[+/-sd] median max Connect: 89 233 166.7 208 1174 Processing: 310 5538 1167.8 5648 7071 Waiting: 250 5509 1164.6 5623 7037 Total: 399 5771 1184.4 5991 7350 Percentage of the requests served within a certain time (ms) 50% 5991 66% 6563 75% 6657 80% 6754 90% 6964 95% 7123 98% 7280 99% 7350 100% 7350 (longest request)
These results show that the SQLite-backed Azure Function has mean response times of 5.8s under a load of 100 concurrent users, which would result in a poor user experience.
For comparison's sake, I decided to benchmark an Azure Function API that uses CosmosDB as storage. Here are the results for 100 concurrent users:
ab -n 100 -c 100 "https://<FUNCTION_APP_NAME>.com/api/getMeetingInfo?meetingId=meetingID"
Connection Times (ms) min mean[+/-sd] median max Connect: 140 187 15.5 188 227 Processing: 116 851 303.1 934 2657 Waiting: 114 850 303.9 933 2657 Total: 271 1038 304.4 1115 2812 Percentage of the requests served within a certain time (ms) 50% 1115 66% 1184 75% 1246 80% 1263 90% 1312 95% 1342 98% 1357 99% 2812 100% 2812 (longest request)
As we can see, CosmosDB performs much better. CosmosDB-backed Azure Functions managed 1.0s mean response times with 100 concurrent users, compared to the 5.8s of the SQLite-backed Azure Functions used in this project. Response times of an SQLite-backed Azure Function are about 5-6 times those of a CosmosDB-backed Azure Function.
Cold starts are also much worse with an SQLite-backed Azure Function than a CosmosDB one. My experience is that CosmosDB-backed functions have 4-6s response times for cold functions, compared to 10-15s response times for cold SQLite-backed functions. I would hypothesize that additional time is required to mount the storage to the Azure Function compute which isn't required when using an external database such as CosmosDB. Clearly, Azure does not intend for the file system to be used in this way.
SQLite with Azure Functions is possible, but it is slow for 100 concurrent users (~6s response times). It also aggravates the cold-start delay by a factor of 2-3x. Building an application with Azure Functions and SQLite would only make sense for a personal project with a handful of users, and even then, other options such as CosmosDB/Azure Table/Azure SQL should be considered.
The deployment of SQLite was complicated because of the need to get correct native bindings for SQLite file access.
If I were to rebuild this application, it would be as a static web application (React or HTML/JS), with Azure Functions providing REST API endpoints and CosmosDB providing storage. The development process would be simplified, more reliable, and officially supported by Azure Static Web Apps.
This project was a proof-of-concept of SQLite on Azure Functions. While the concept is proven, the poor performance of the solution is a limiting factor for any serious use cases. This approach could only be considered for personal projects, but even then, Azure Static Web Apps and Cosmos DB should be strongly considered for their superior performance and better developer experience.
Linux Function Apps do not provide read access to files after deployment, unlike Windows Function Apps. This means that Windows Function Apps would be a better candidate for an Azure Function/SQLite deployment since the db.sqlite3 could be downloaded and viewed at any time. However, Linux Function Apps allow remote build (with the
func azure functionapp publish <FUNCTION_APP_NAME> -b remote command), which facilitates the deployment as the build process downloads the correct SQLite3 native bindings.