Building a Micro Web API with Azure Functions and SQLite

• GitHub Project

Introduction

In this article, we're going to write a basic API with an Azure Function, using SQLite as the backend.

We will create a web API that returns a city for a ZIP Code you send. This will be a simple, small service you can use for forms and other lookups.

We will use a pre-populated SQLite database and build an Azure Function to interact with it and provide a web API interface.

SQLite is a small, self contained SQL database engine that is incredible for working with small data. It's a well designed, performant, and reliable database you probably use every day. It's ubiquitous on mobile phones and other portable devices due to its size and resource usage.

You can learn more about SQLite with the Android: SQLite Fundamentals from Pluralsight. While it is focused on Android development it covers SQLite basics very well.

You can learn more about Azure Functions here.

The Database

The database for this project is available here for download. This will be the database we're working with.

It's a database of ZIP codes with their corresponding cities. Here's what the schema of the database looks like:

This database contains information for most of the cities in the United States, with a ZIP code used as the primary key.

We will connect to this database locally and package and ship it with our Azure Function.

Setting up our Azure Function

We'll want to set up an Azure Function in Azure to deploy our application to. Here's what to do.

Log in to your Azure Portal. Search for "Function App"

Click "Create". You will see a dialog that looks like this:

Select your resource group, and then specify the name of your project. This will be the domain name of your service.

Select "Code" for publishing, and .NET as the runtime stack.

Select version 3.1

And the region you'd like your Azure Function to live in.

Then click Review + create

Creating our Azure Function

Open Visual Studio 2019 (or greater) and create a new project.

Search for the Azure Functions template and click next.

Name your project whatever you'd like. I named mine "ZiptoCity".

Make sure to select:

• .NET Core 3 (LTS)
• Http trigger
• Anonymous authorization level

And create the project.

Creating the model

Create a new folder named Models and create a new class named City.cs. This will be the model we will return as a result.

Add the following properties to your class, so it looks like this:

using Newtonsoft.Json;

namespace ZiptoCity.Models
{
    public class City
    {
        [JsonProperty("zipcode")]
        public int ZipCode { get; set; }

        [JsonProperty("cityname")]
        public string CityName { get; set; }

        [JsonProperty("county")]
        public string County { get; set; }

        [JsonProperty("state")]
        public string State { get; set; }

        [JsonProperty("timezone")]
        public string TimeZone { get; set; }
    }
}

These are the bits of database we want to expose in our API. We are decorating the properties with JsonProperty because it will be displayed as JSON later.

Creating our Main Function

Next we're going to create our function to access this data.

Right click in your project folder, and select add -> New Azure Function:

Select Http trigger with anonymous authentication.

And add the new file.

You are presented with a new function. Let's make some changes.

In the signature of the autogenerated method, you'll see this:

public static async Task<IActionResult> Run(
    [HttpTrigger(AuthorizationLevel.Anonymous, "get", "post", Route = null)] HttpRequest req, ILogger log)

Change it so it looks like this:

public static async Task<IActionResult> Run(
    [HttpTrigger(AuthorizationLevel.Anonymous, "get", Route = "city/{id}")] HttpRequest req, ILogger log, int id)

We will remove POST from the available API methods because we aren't using it.

Then we'll add a new route that takes {id} as a parameter. Here is where we'll add in our zip code.

Finally, we'll capture that id with int id.

Let's change the log message:

log.LogInformation("Requested city for " + id);

And now we'll connect to the database.

Note: With larger applications, it's better to abstract the database connection information. But since we're performing a single action, I decided to put all the connection handling within the method.

Connect to the database

Now we're going to connect to our SQLite database. Make sure and copy this database into a folder named Data, and set the database to Copy always.

Then we need to install the SQLite Nuget package:

Install-Package System.Data.SQLite -Version 1.0.115

In our Run method, add the following:

var sqlite_conn = new SQLiteConnection(@"Data Source=Data\zipcode.db; Version = 3; New = True; Compress = True; ");

try
{
    sqlite_conn.Open();
}
catch (Exception ex)
{
    log.LogInformation("Error: " + ex.Message);
}

This code connects to our SQLite database located in the Data folder. If we have an issue with connecting, it will be logged.

Querying the data

Next we'll add some lines to create a SQLiteDataReader and a SQLiteCommand to populate it with data.

SQLiteDataReader sqlite_datareader;
SQLiteCommand sqlite_cmd;
sqlite_cmd = sqlite_conn.CreateCommand();

Now, we'll create a query to grab the data from the database we want to return. We'll pass in the id as a parameter for the query.

sqlite_cmd.CommandText = "SELECT zip, primaryCity, state, county, timezone FROM zip_code_database WHERE zip = '" + id + "'";

Then we can execute the reader:

sqlite_datareader = sqlite_cmd.ExecuteReader();

Retrieving and assigning the data

Now we want to use the reader to populate the City object we're going to return. We will create an object to populate.

var resultCity = new City { };

We'll also create a bool we can check before sending out our final result:

var goodResult = false;

Then we'll create a loop that will run if we get a successful result from our query:

if (sqlite_datareader.HasRows)
{
    goodResult = true;
    
    while (sqlite_datareader.Read())
    {
        resultCity.ZipCode = sqlite_datareader.GetInt32(0);
        resultCity.CityName = sqlite_datareader.GetString(1);
        resultCity.State = sqlite_datareader.GetString(2);
        resultCity.County = sqlite_datareader.GetString(3);
        resultCity.TimeZone = sqlite_datareader.GetString(4);
    }
}

Within this loop, we set goodResult to true. Then loop through the datareader and assign the values from it to our resultCity object.

Now we can close the connection.

sqlite_conn.Close();

Sending the results

Now we're ready to send the results based on our query. If we don't recieve a city from the database, goodResult will remain false.

If the query was successful, we'll send out a JSON representation of our City object:

if (goodResult)
{                
    return new OkObjectResult(resultCity);
}
else
{
    return new NotFoundResult();
}

And we're done! Now our Azure function is ready to test.

The full function

The full function should look like this:

[FunctionName("ZipCodeLookup")]
public static async Task<IActionResult> Run(
    [HttpTrigger(AuthorizationLevel.Anonymous, "get", Route = "city/{id}")] HttpRequest req,
    ILogger log, int id)
    {
        log.LogInformation("Requested city for " + id);

        var sqlite_conn = new SQLiteConnection(@"Data Source=Data\zipcode.db; Version = 3; New = True; Compress = True; ");

        try
        {
            sqlite_conn.Open();
        }
        catch (Exception ex)
        {
            log.LogInformation("Error: " + ex.Message);
        }

        SQLiteDataReader sqlite_datareader;
        SQLiteCommand sqlite_cmd;
        sqlite_cmd = sqlite_conn.CreateCommand();

        sqlite_cmd.CommandText = "SELECT zip, primaryCity, state, county, timezone FROM zip_code_database WHERE zip = '" + id + "'";

        sqlite_datareader = sqlite_cmd.ExecuteReader();

        var resultCity = new City { };
        var goodResult = false;

        if (sqlite_datareader.HasRows)
        {
            goodResult = true;

            while (sqlite_datareader.Read())
            {
                resultCity.ZipCode = sqlite_datareader.GetInt32(0);
                resultCity.CityName = sqlite_datareader.GetString(1);
                resultCity.State = sqlite_datareader.GetString(2);
                resultCity.County = sqlite_datareader.GetString(3);
                resultCity.TimeZone = sqlite_datareader.GetString(4);
            }
        }

        sqlite_conn.Close();

        if (goodResult)
        {                
            return new OkObjectResult(resultCity);
        }
        else
        {
            return new NotFoundResult();
        }
    }
}

Let's test it out!

Testing the Azure Function

We can now test our Azure function by pressing 5. You'll see a console window that shows you how to connect to it:

You can connect to your function locally, but using the URL + a sample ZIP code:

Now that we know it's good, we can deploy it.

Deploying the Azure Function

Go Build -> Publish and select Azure.

Select Azure Function App (Windows).

Select your subscription, resource group, and select the function app. Make sure Run from package file (recommended) is NOT checked.

Press Finish, then Publish to deploy to Azure.

Running your application in Azure

Now that the application is published to Azure, you'll have to make some quick changes, and it will be ready to go.

In the Azure portal, select your function. Then scroll down to CORS

In the allowed origins, add a wildcard (*) character.

Note: for production uses you will want to enter the domain names allowed to access this application.

Save the configuration and you're ready to go.

You can now use your browser, or Postman to send queries to your new API:

And that's it!

Conclusion

In this project, we built a small web API that takes a ZIP code and returns city information. This is a great example of a "Micro API" you can use in your organization. Small, lean applications are always popular, and by using Azure Functions and SQLite, you can make tiny applications that perform well and scale.

The microservices architecture is getting more popular every day. Building small services and APIs that are loosely coupled can help your organization build performant and easy to maintain applications. Azure functions make that possible by enabling small pay-for-what-you-use spaces to build your applications.

You can learn more about Azure Functions here.