Build & publish Azure Functions app that uses a budget SQLite database &...

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.

Prerequisites

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 then be asked to choose the .NET runtime, choose .NET 6: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".

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:

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; }

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;

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(

              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"

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);

private readonly ApplicationDbContext _context;

public HttpApi(ApplicationDbContext context) {

   _context = context;

Applying migration '20220314204919_m1'.

Done.

[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);