In this tutorial I will build a Web API application using Azure Functions & SQLite. Although it is not typical to use SQLite with Azure Functions, this is a decent option if you want to have a cheap storage solution. I will later deploy the SQLite enabled Azure Function. This tutorial was done on a Windows 11 computer with VS Code.
Source code: https://github.com/medhatelmasry/SQLiteFunction.git
Prerequisites
- You need an Azure account
- VS Code
- .NET Core 6.0
- Install dotnet-ef tool
- Install Azure Functions Core Tools
- Azure Functions Extension for VS Code
Create a folder named SQLiteFunction on your hard drive at the location where your project will reside.
Under the Functions tab in VS Code, create a new Azure Functions project.
Navigate to the location on your hard drive that you have designated as your workspace folder for this project. You will next be asked to select a programming language. Choose C#.
You will be asked to choose a template for your project's first function. Note that you can have more than one function in your project. Choose HttpTrigger.
Give your function a name. I named my function HttpApi.
Hit Enter after you give your function a name. Give your class a namespace. The namespace I used is SQLiteFunction. I then hit Enter.
Choose Anonymous for AccessRights.
When asked about how you would like to open your project, choose "Open in current window".
If a popup window appears asking if you wish to restore unresolved dependencies, click the Restore button.
Let us see what the app does. Hit CTRL F5 on the keyboard. The built-in VS Code terminal window will eventually display a URL that uses port number 7071:
NOTE: You can start your function app with the terminal command: func start
Copy and paste the URL into a browser or hit CTRL Click on the link. You will see the following output in your browser:
The message in your browser suggests that you should pass a name query string. I appended the following to the URL: ?name=Superman and got the following result:
We will need to add some NuGet packages. Execute the following dotnet commands from a terminal window in the root directory of your project:
dotnet add package Microsoft.Azure.Functions.Extensionsdotnet add package Microsoft.EntityFrameworkCore.SQLitedotnet add package Microsoft.EntityFrameworkCore.SQLite.Designdotnet add package Microsoft.EntityFrameworkCore.Tools
Let us make a few minor enhancements to our application.
1) Our SQLite database will be named school.db. Add the following to the project's .csproj file so that the SQLite database is copied to the output directory when the app gets built:
<ItemGroup><None Update="school.db"><CopyToOutputDirectory>PreserveNewest</CopyToOutputDirectory></None></ItemGroup>
2) Change the signature of the HttpAPI class so that it does not have the static keyword. Therefore, the signature of the class will look like this:
public class HttpApi
3) Create a Models folder and add to it a simple Student.cs class file with the following content:
using System.ComponentModel.DataAnnotations;
namespace SQLiteFunction.Models;public class Student {public int StudentId { get; set; }[Required]public string FirstName { get; set; }[Required]public string LastName { get; set; }[Required]public string School { get; set; }}
4) We will deploy our Azure Functions app to a Windows server on Azure. When the school.db SQLite database file is published to Azure, it will reside in directory d:\home\site\wwwroot. Therefore, we shall create a helper class that will locate for us the SQLite database file in both development and deployment environments. Create a file named Utils.cs in the Models folder and add to it the following code:
using System;namespace SQLiteFunction.Models;public class Utils{public static string GetSQLiteConnectionString(){var home = Environment.GetEnvironmentVariable("HOME") ?? "";Console.WriteLine($"home: {home}");if (!string.IsNullOrEmpty(home)){home = System.IO.Path.Combine(home, "site", "wwwroot");}var databasePath = System.IO.Path.Combine(home, "school.db");var connStr = $"Data Source={databasePath}";return connStr;}}
The above helper class provides us with a static method Utils.GetSQLiteConnectionString() that returns the fully qualified location of the SQLite database file named school.db.
5) Add an Entity Framework DbContext class to the Models folder. In our case, we will add a class file named ApplicationDbContext.cs with the following content:
using Microsoft.EntityFrameworkCore;using System;namespace SQLiteFunction.Models;public class ApplicationDbContext : DbContext{public ApplicationDbContext() { }public ApplicationDbContext(DbContextOptions<ApplicationDbContext> options) : base(options) { }public DbSet<Student> Students { get; set; }protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder){if (!optionsBuilder.IsConfigured){optionsBuilder.UseSqlite(Utils.GetSQLiteConnectionString());}}protected override void OnModelCreating(ModelBuilder builder){base.OnModelCreating(builder);builder.Entity<Student>().HasData(new{StudentId = 1,FirstName = "Jane",LastName = "Smith",School = "Medicine"}, new{StudentId = 2,FirstName = "John",LastName = "Fisher",School = "Engineering"}, new{StudentId = 3,FirstName = "Pamela",LastName = "Baker",School = "Food Science"}, new{StudentId = 4,FirstName = "Peter",LastName = "Taylor",School = "Mining"});}}
The above context class seeds some sample data pertaining to four students.
6) To register a service like ApplicationDbContext, create a class file named Startup.cs in the root of your application. The Startup class implements FunctionStartup. This class will look like this:
using Microsoft.EntityFrameworkCore;using Microsoft.Extensions.DependencyInjection;using Microsoft.Azure.Functions.Extensions.DependencyInjection;using SQLiteFunction.Models;[assembly: FunctionsStartup(typeof(SQLiteFunction.StartUp))]namespace SQLiteFunction{public class StartUp : FunctionsStartup{public override void Configure(IFunctionsHostBuilder builder){builder.Services.AddDbContext<ApplicationDbContext>(options =>{options.UseSqlite(Utils.GetSQLiteConnectionString());});}public override void ConfigureAppConfiguration(IFunctionsConfigurationBuilder builder){base.ConfigureAppConfiguration(builder);}}}
7) Inject the ApplicationDbContext class that is needed by your function class. Open HttpApi.cs in the editor and add the following instance variables and constructor at the top of the class:
private readonly ApplicationDbContext _context;public HttpApi(ApplicationDbContext context) {_context = context;}
8) The next step is to apply Entity Framework migrations. Open a terminal window in the root of your application and execute the following EF migration command inside the same terminal window:
dotnet-ef migrations add m1 -o Data/Migrations
This produces a Data/Migrations folder in your project.
9) The next step is to create the database and tables. Execute the following command in the same terminal window as above:
dotnet-ef database update
If all goes well, you will receive a message that looks like this:
Applying migration '20220314204919_m1'.Done.
10) Let us now create an endpoint in our Azure function that returns all the students as an API. Add the following method to the Azure functions file named HttpApi.cs:
[FunctionName("students")]public IActionResult GetStudents([HttpTrigger(AuthorizationLevel.Anonymous, "get", Route = "students")] HttpRequest req,ILogger log) {log.LogInformation("C# HTTP GET trigger function processed api/students request.");var studentsArray = _context.Students.ToArray();return new OkObjectResult(studentsArray);}
All that is left for us to do is test out our application and make sure it returns our students data. Run the application by hitting CTRL F5 on the keyboard. You will see the following output in a VS Code terminal window:
Copy and paste the /api/students endpoint into a browser. Alternatively, you can simply hit CTRL Click on the link. The result will look like this:
Deployment to Azure
Click on the "Deploy to Function App ..." icon.
Select your subscription.
Choose "+ Create new Function App in Azure ...".
Enter a globally unique name for your function app then hit Enter.
Select .NET 6 for runtime stack:
Choose a preferred data center.
The deployment process starts. Be patient as it could take 3-4 minutes. When deployment is complete you will see the following message:
If you click on the "View output" you will see the two endpoints.
Although the first endpoint works, the second does not. To fix this problem, login into the azure portal https://portal.azure.com. In the filter field, enter func then choose "Function App".
Click on the function that was created.
Select Configuration on the left navigation.
Click on WEBSITE_RUN_FROM_PACKAGE.
Change the value from 1 to 0 then click on OK.
Remember to Save at the top.
Back in VS Code, publish your functions app again.
Click on Deploy to confirm.
This time, deployment will not take as long as the last time. Once deployment is completed, try the /api/students on the deployed endpoint and you should find it working to your satisfaction.
Conclusion
It is easy to create Azure Functions with the SQLite. Also, creating an API with Azure Functions is much more cheaper than doing it with an ASP.NET Core Web API application because you pay a fraction of a cent for every request and the app does not need to be constantly running. It is also worth mentioning that using SQLite makes it even cheaper as you do not need to pay extra for hosting your relational database.
