Integrating SQLite in Azure IoT Edge for local storage

This article is updated on 29-04-2020 with configuration information for Windows containers on Windows 10 1809 LTS (Moby runtime).

Microsoft has opened the Azure Marketplace for IoT Edge modules. This makes it easy to find and install IoT Edge modules into your own IoT Edge devices.

At this moment a dozen or more modules are available:

We see a few well-known modules from Microsoft which support SQL Server, Modbus, OPC-UA or the Temperature sensor simulation.

One of these modules is supporting SQLite.

What is SQLite?

According to the homepage, SQLite is an in-process library that implements a self-contained, serverless, zero-configuration, transactional SQL database engine. The code for SQLite is in the public domain and is thus free for use for any purpose, commercial or private. SQLite is the most widely deployed database in the world with more applications than we can count, including several high-profile projects.

What does the module do?

This IoT Edge module is capable of accessing SQLite databases locally.

Microsoft says:

“Using this module, developers can build Azure IoT Edge solutions with the capability to access SQLite databases. The SQLite module is an Azure IoT Edge module, capable of executing SQL queries sent from other modules, and return the result to the senders or to the Azure IoT Hub via the Edge framework. Developers can modify the module tailoring to any scenario.”

The module is open source and both documentation and source code are put on GitHub.

Why should we use this module?

In the past, I checked out the SQL Server IoT module to persist IoT messages locally.

This SQLite module has a few advantages which you will like:

  • It’s lightweight
  • It’s based on a public domain framework
  • It’s integrated with the routing of IoT Edge

For me, that last item, we can integrate the module directly in the routing is interested.

So let’s check out how we can use it.

Getting the module

You can integrate the module into your IoT Edge pretty easy.

Just select ‘Get it Now’:

Agree with the terms of use and continue:

This will bring you to your Azure subscription in the Azure portal. If needed, you will have to log in to the portal first.

There you will need to select the IoT Hub:

And within the IoT Hub, you will need to select the IoT Edge device of your choice. After you press Find Device, select one:

After selection, press ‘create’. This will bring us to the well-known ‘Set modules’ wizard of your IoT Device.

I just created this IoT device so I just have one module, this new SQLite module:

You can change the name of the module by selecting the row. I just keep it this way.

Til now, the wizard is pretty straight forward. The next step needs some attention.

Select ‘Next’ and check out the route:

As you can see, there are two routes. The Marketplace wizard adds an extra route to the already existing routes. So that’s why we have two routes. Just delete the first, original route.

If you changed the name of the module, you have to alter the module name in the route!

Go to next and finally finish the wizard. The module is added to your device:

What is added?

The module is using Docker image: ‘mcr.microsoft.com/azureiotedge/sqlite:1.0’

Note: (update 2020-04-29) The example in this blog is based on Linux with Linux containers (I tested it on Ubuntu 18.04). See the separate paragraph for the configuration of Windows 10 IoT Enterprise 1809 using Windows containers.

And the module starts with the following desired properties:

{
  "properties.desired": {
    "SQLiteConfigs": {
      "Db01": {
        "DbPath": "/app/db/test.db",
        "Table01": {
          "TableName": "test",
          "Column01": {
            "ColumnName": "Id",
            "Type": "numeric",
            "IsKey": "true",
            "NotNull": "true"
          },
          "Column02": {
            "ColumnName": "Value",
            "Type": "numeric",
            "IsKey": "false",
            "NotNull": "true"
          }
        }
      }
    }
  }
}

As you can see, it refers to a generic database name ‘/app/db/test.db’, a table called ‘test’ and two columns: ‘Id’ and ‘Value’.

Note: ‘Db01’, ‘Table01’, ‘Column01’, and ‘Column02’ are just unique names to keep the JSON format ready.

I will show how to use this the module using this database. Feel free to alter database, table, and columns.

How to combine with routing

If you check out the GitHub documentation, you will see that the module is expecting messages on input ‘input1’. The example on the page shows a select:

{
  "RequestId":"0",
  "RequestModule":"filter",
  "DbName":"/app/db/test.db",
  "Command":"select Id, Value from test;"
}

Note: the message must have property name command-type” with value SQLiteCmd

If a change takes place in the database, a message is sent using output ‘sqliteOutput’.

The output looks like:

[
  {
    "PublishTimestamp":"2018-09-04 04:16:47",
    "RequestId":"0",
    "RequestModule":"filter",
    "Rows":[
      [
        "1",
        "20"
      ],
      [
        "2",
        "100"
      ]
    ]
  }
]

Note: The output message has a property content-type” with value application/edge-sqlite-json“.

Testing the deployment

After deployment, I ran into several errors like:

Attempt to load configuration: {"SQLiteConfigs":{"Db01":{"DbPath":"/app/db/test46.db","Table01":{"TableName":"test","Column01":{"ColumnName":"Id","Type":"numeric","IsKey":"true","NotNull":"true"},"Column02":{"ColumnName":"Value","Type":"numeric","IsKey":"false","NotNull":"true"}}}},"$version":1}
Exception while opening database, err message: SQLite Error 14: 'unable to open database file'.
Check if the database file is created or being mounted into the container correctly

It was time to check out the code:

  • The code was based on an early (probably just GA) template of IoT Edge.
  • The access to the database folder is unclear. Normally we see binds or volumes to make sure the database survives a redeploy of the module.

So I cloned the repository to my repository and upgraded the code to the (current) 1.0.5 template.

Note: (update 2020-04-29) The current module in the iot edge marketplace runs on Windows and Linux (Raspbian).

And I added the Container Create options:

{
  "HostConfig": {
    "Binds": [
      "/app/db:/app/db"
    ]
  }
}

Finally, I created on the host Linux environment a ‘/app/db’ folder and I performed:

sudo chmod 777 db

After that, I was able to see the database file being created:

uno2271gsv@uno2271g2sv-UNO-2271G-E2xAE:/app/db$ ls -l
total 12
-rw-r--r-- 1 uno2271gsv uno2271gsv 12288 jan 13 11:59 test.db
uno2271gsv@uno2271g2sv-UNO-2271G-E2xAE:/app/db$

My code is now working together with IoT Edge runtime 1.0.5. I submitted my code update as a pull request.

How to test the module?

So let’s start querying. How do I push a SQL command to this SQLite module?

For this, I created an extra module which accepted direct methods. These direct methods are turned into messages:

...
// Register direct method in the module
await ioTHubModuleClient.SetMethodHandlerAsync("cmd", CommandMethod, ioTHubModuleClient);
...
 
static async Task<MethodResponse> CommandMethod(MethodRequest methodRequest, object moduleClient)
{
  counter = Interlocked.Increment(ref counter);
 
  var requestModule = System.Environment.GetEnvironmentVariable("IOTEDGE_MODULEID");
 
  try
  {
    ModuleClient ioTHubModuleClient = (ModuleClient) moduleClient;
 
    Console.WriteLine("CommandMethod: Received the request: " + methodRequest.DataAsJson);
 
    dynamic command = JsonConvert.DeserializeObject(methodRequest.DataAsJson);
 
    var messageBody = new SQLiteInMessage
    {
      RequestId = counter,
      RequestModule = requestModule,
      DbName = (string) command.dbName,
      Command = (string) command.command,
    };
 
    var jsonMessage = JsonConvert.SerializeObject(messageBody);
 
    var pipeMessage = new Message(Encoding.UTF8.GetBytes(jsonMessage));
 
    pipeMessage.Properties.Add("command-type", "SQLiteCmd");
 
    await ioTHubModuleClient.SendEventAsync("output1", pipeMessage);
 
    Console.WriteLine($"Command {counter}:{jsonMessage} is sent to output1");
 
    var response =
      new MethodResponse
      (
        result:Encoding.UTF8.GetBytes("{ \"output\": \"Executed " + counter + "\" }"),
        status:(int) 200 /*HttpStatusCode.OK*/
      );
 
    return response;
  }
  catch(Exception ex)
  {
    Console.WriteLine(ex.Message);
    Console.WriteLine(ex.StackTrace);
    return await Task.FromResult(new MethodResponse(500));
  }
}

It wants to know the name of the database and the command to execute.

And I listened for the message coming from SQLite with my Echo module.

These are the routes:

{
  "routes": {
    "cmdToSqlite": "FROM /messages/modules/cmd/outputs/output1 INTO BrokeredEndpoint(\"/modules/SQLite/inputs/input1\")",
    "sqliteToEcho": "FROM /messages/modules/SQLite/outputs/sqliteOutput INTO BrokeredEndpoint(\"/modules/echo/inputs/input1\")"
  }
}

Queries

Here are three separate queries I fired for testing:

{
"dbName":"/app/db/test.db",
"command":"INSERT INTO test (id, value) VALUES (1, 42);"
}
 
{
"dbName":"/app/db/test.db",
"command":"INSERT INTO test (id, value) VALUES (2, 43);"
}
 
{
"dbName":"/app/db/test.db",
"command":"select Id, Value from test;"
}

This looks like:

Note: Insert queries do not return rows. So do not expect to see output coming for these kind of commands.

The result of the SELECT query is:

Here you see the database has persisted the messages.

Windows Containers on Windows 10 1809 LTS

The iot edge module for windows containers on windows (with the moby runtime) needs a slightly other configuration.

The container create options look like this:

{
  "HostConfig": {
    "Binds": [
      "C:\\iotsqlite:c:\\db"
    ]
  }
}

The related desired properties with the database path looks like this:

...
"properties.desired": {
  "SQLiteConfigs": {
    "Db01": {
      "DbPath": "/db/test.db",
        "Table01": {
          "TableName": "test",
          ...

Make sure the c:\iotsqlite folder has sufficient rights.

You will see the the creation of the database on first initialization of the iot edge module.

Conclusion

This module was ‘a tough nut to crack’.

The IoT Edge Marketplace has this module available but it is still written using the GA template and it only seems to work for Linux containers.

On top of that, I had to add create options and change local rights in Ubuntu to get everything working.

But on the bright side, this module brings persisted local storage which is linked to the default routing system of the IoT Edge runtime.