In our post today, we will be adding the sqlite3 database to our application and creating a few more API endpoints for us to add and remove rooms and devices.

SQLite3 should come installed with most Linux distributions but if it is not installed, you can run sudo apt install sqlite3

Below is the updated file structure we are going to modify today:

API endpoints

The first step is to create a room for the home. Rooms can be any part of the room in the house, for example, bedroom, living room or kitchen. Once our rooms are created, we can start associating the rooms with the devices we want to connect.

Our devices can then be tied to the rooms we created and be listed on our front end.

The table below shows the different API routes we will be creating and their description.

Some of our routes have parameters in their paths as denoted by the : in front of the names. The handler will match the routes based on the parameters and map them into values. So a route such as /bedroom/devices will match with the /:roomname/devices and roomname == 'bedroom'.

We can then translate this into code and add them to our router.go file

// router.go

func InitRouter() *gin.Engine {
    r := gin.Default()

    r.GET("/", getServerStatus)


    r.POST("/create-room", createRoom)
    r.GET("/rooms", getRooms)
    r.DELETE("/delete-room/:roomname", deleteRoom)

    r.POST("/:roomname/add-device", addDevice)
    r.GET("/:roomname/devices", showDevices)

    r.GET("/:roomname/:ip", getDeviceInfo)
    r.DELETE("/:roomname/:ip/delete-device", deleteDevice)
    r.POST("/:roomname/:ip/:toggle", toggleDevice)

    return r
}

creating golang struct

To facilitate the loading of data from our routes as well as our database, we will create golang structs that encapsulate our fields of data together. Under the models folder, we can add the following structs for our room and devices.

// models.go

package models

type RoomInfo struct {
    Name  string `json:"name"`
    Count int    `json:"count"`
}

type DeviceType string

const (
    Wled   DeviceType = "wled"
    Switch DeviceType = "switch"
)

type RegisteredDevice struct {
    Hostname string     `json:"hostname"`
    Ipaddr   string     `json:"ipaddr"`
    Name     string     `json:"name"`
    Type     DeviceType `json:"type"`
}

type DeviceStatus struct {
    Connected bool `json:"connected"`
    On_state  bool `json:"on_state"`
}

All the fields here have a corresponding JSON tag, which specifies how the field should be encoded in JSON.

Type names and Fields are capitalized to indicate that the identifier is public

For RoomInfo, we have a Name field that is unique and the Count field, which we will use to keep track of the number of devices in the room.

To keep track of the device, we have the RegisteredDevice type that has fields about the information of the device such as the name, IP address and the device type. DeviceType is defined as a string type that refers to our constant values wled and switch, which represents the different device types we support.

Our DeviceStatus helps to maintain information about the state of the device. Currently, we have Connected field to check if the device is operational and On_state field to check if the switch is on or off.

setting up our database

We will be using the go-sqlite3 package to help us execute SQL queries to our SQLite3 database.

go get github.com/mattn/go-sqlite3

First, let's create our InitDatabase function that runs every time we start our backend server.

// database.go
package database

import (
    "database/sql"
    _ "github.com/mattn/go-sqlite3"
)

const databaseFilePath = "iothome.db"

func InitDatabase() *sql.DB {
    db, err := sql.Open("sqlite3", databaseFilePath)
    if err != nil {
        log.Println("Error opening database")
    }

    if _, err := db.Exec("PRAGMA foreign_keys = 1"); err != nil {
        log.Println("Error setting command")
    }

    return db
}

Using sql.Open, we can try to open our SQL database using the name that we have given. If no such name exists, then our database driver will initialize a new database file for us.

We then execute the SQL command PRAGMA foreign_keys = 1 to enable foreign key constraints in our database. By default, foreign key constraints are disabled. Foreign key constraints are used to enforce referential integrity, which we will be utilizing in our tables later on.

SQL create tables

Next, we create our database schema for containing our data. We have three different tables, rooms, devInfo and devStatus each with its own headers. In the devInfo table, we have our room_id column that references the room_id column in the rooms table. This means that each device info entry has a room_id column which can be referenced to the rooms table. This is similar to the device_id in devInfo and devStatus.

Let's translate this into SQL commands to create our table, as shown below:

const (
    createRooms string = `
        CREATE TABLE IF NOT EXISTS rooms (
            room_id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
            name TEXT NOT NULL UNIQUE
        )
    `

    createDevInfo string = `
        CREATE TABLE IF NOT EXISTS deviceInfo (
            room_id INTEGER NOT NULL,
            device_id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
            name TEXT NOT NULL,
            ipaddr TEXT NOT NULL,
            type TEXT NOT NULL,
            hostname TEXT,
            macaddress TEXT,
            FOREIGN KEY (room_id)
                REFERENCES rooms (room_id)
                    ON DELETE CASCADE
        )
    `

    createDevStatus string = `
        CREATE TABLE IF NOT EXISTS deviceStatus (
            device_status_id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
            device_id INTEGER NOT NULL,
            connected INTEGER NOT NULL,
            on_state INTEGER NOT NULL,
            FOREIGN KEY (device_id)
                REFERENCES deviceInfo (device_id)
                    ON DELETE CASCADE
        )
    `
)

func InitDatabase() *sql.DB {
    //  ... 
    if _, err := db.Exec(createRooms); err != nil {
        log.Println("failed to create new rooms table")
    }

    if _, err := db.Exec(createDevInfo); err != nil {
        log.Println("failed to create new devinfo table")
    }

    if _, err := db.Exec(createDevStatus); err != nil {
        log.Println("failed to create new devstatus table")
    }
    //  ...
}

We use CREATE TABLE IF NOT EXISTS to initialize our database as it will create the table if the table does not currently exist.

NOT NULL is used here to ensure that the important columns we are storing have a value to them and is not empty.

AUTOINCREMENT keyword is used to create a unique identifier for each of our inserted rows in the table. It automatically generates an integer starting from 1.

ON DELETE CASCASE keywords indicate that when a row in deviceInfo is deleted, all the rows in the deviceStatus table that references the deleted row via the device_id foreign key should also be deleted, which makes sense if we want to delete a particular device from the table.

UNIQUE keyword is used here to ensure that our name column will not have any two rows with the same values.

initializing database

let's create a struct DatabaseManager that holds a pointer to the database that we initialized in the previous section. In our database.go file, we will declare Dbman that holds a pointer to the DatabaseManager. This is so that other files can reference Dbman to execute the SQL commands.

// database.go

type DatabaseManager struct {
    Db *sql.DB
}

var Dbman *DatabaseManager

func InitializeGlobals(db *sql.DB) {
    Dbman = &DatabaseManager{
        Db: db,
    }
}

SQL statements

In database.go, we also declare our SQL commands for inserting, selecting, deleting and updating our entries in the database. The ? here denotes the data that will be replaced when we execute these commands below in our server.

// database.go

const (
    insertNewRoom string = `INSERT INTO rooms (name) VALUES (?)`
    getRooms string = `SELECT rooms.name, count(deviceInfo.room_id) FROM rooms LEFT JOIN deviceInfo ON rooms.room_id = deviceInfo.room_id GROUP BY rooms.name`
    getRoomId     string = `SELECT room_id FROM rooms WHERE name=?`

    insertNewDevInfo string = `INSERT INTO deviceInfo (room_id, name, ipaddr, type) VALUES (?, ? ,? ,?)`
    insertNewDevStatus string = `INSERT INTO deviceStatus (device_id, connected, on_state) VALUES (?, ? ,?)`

    getDeviceInfoByRoom string = `SELECT dev.name, dev.ipaddr, dev.type, dev.connected, dev.on_state FROM rooms JOIN (SELECT * FROM deviceInfo JOIN deviceStatus WHERE deviceInfo.device_id = deviceStatus.device_id) as dev WHERE rooms.room_id = dev.room_id and rooms.name=?`

    getDeviceInfo string = `SELECT dev.device_id, dev.name, dev.type, dev.connected, dev.on_state FROM rooms JOIN (SELECT * FROM deviceInfo JOIN deviceStatus WHERE deviceInfo.device_id = deviceStatus.device_id) as dev WHERE rooms.room_id = dev.room_id and rooms.name=? and dev.ipaddr=?`

    updateDeviceStatus string = `UPDATE deviceStatus SET connected=?, on_state=? WHERE device_id=?`

    deleteRoom string = `DELETE FROM rooms WHERE name=?`
    deleteDevice string = `DELETE FROM deviceInfo WHERE ipaddr IN (
        SELECT ipaddr FROM rooms JOIN deviceInfo ON rooms.room_id=deviceInfo.room_id WHERE rooms.name=? and deviceInfo.ipaddr=?
        )
    `
)

After that, we create the corresponding go functions to execute the SQL commands.

For example, create a AddRoom function below, we have a receiver parameter denoted by the (s *DatabaseManager) which allows one to associate a particular function, AddRoom, with a specific type, in this case, the DatabaseManager.

func (s *DatabaseManager) AddRoom(room models.RoomInfo) error {
    _, err := s.Db.Exec(insertNewRoom, room.Name)

    if err != nil {
        log.Println("insertNewRoom query failed")
        return err
    }

    return nil
}

We call s.Db.Exec to execute our insertNewRoom command and also the name of our room. We can then check if the insertion is successful by checking if the err is nil.

To query our database, we can call .Query, which will return to us all the rows that satisfy our conditions. We need to call rows.Close() after reading so that we prevent memory leaks. We can read through all the rows by looping through rows.Next() and then call rows.Scan(<column names queries>). Below is an example for querying all the rooms we have.

func (s *DatabaseManager) GetRooms() ([]models.RoomInfo, error) {
    rows, err := s.Db.Query(getRooms)
    if err != nil {
        log.Println("getRooms query failed")
        return nil, err
    }
    defer rows.Close()

    rooms := []models.RoomInfo{}
    for rows.Next() {
        ri := models.RoomInfo{}
        err = rows.Scan(&ri.Name, &ri.Count)
        if err != nil {
            log.Println("failed to scan db rows")
            return nil, err
        }
        rooms = append(rooms, ri)
    }

    return rooms, nil
}

testing our application

To test the code that we had just written, let's write a function to populate our database:

// database.go

// for testing purposes
func PopulateDatabase() {

    Dbman.AddRoom(models.RoomInfo{Name: "livingroom"})
    Dbman.AddRoom(models.RoomInfo{Name: "bedroom1"})
    Dbman.AddDevice(
        models.RegisteredDevice{Name: "andre", Type: "wled", Ipaddr: "192.168.1.1", Hostname: "123"},
        models.DeviceStatus{Connected: false, On_state: false},
        "bedroom1",
    )
    Dbman.AddDevice(
        models.RegisteredDevice{Name: "betty", Type: "switch", Ipaddr: "192.168.1.2", Hostname: "123"},
        models.DeviceStatus{Connected: false, On_state: false},
        "bedroom1",
    )
    Dbman.AddDevice(
        models.RegisteredDevice{Name: "cathy", Type: "wled", Ipaddr: "192.168.1.3", Hostname: "123"},
        models.DeviceStatus{Connected: false, On_state: false},
        "bedroom1",
    )
    Dbman.AddDevice(
        models.RegisteredDevice{Name: "derick", Type: "switch", Ipaddr: "192.168.1.4", Hostname: "123"},
        models.DeviceStatus{Connected: false, On_state: false},
        "livingroom",
    )
    Dbman.AddDevice(
        models.RegisteredDevice{Name: "eagle", Type: "wled", Ipaddr: "192.168.1.5", Hostname: "123"},
        models.DeviceStatus{Connected: false, On_state: false},
        "livingroom",
    )
}

In our main, we can then initialize our database and call PopulateDatabase.

func main() {
    db := database.InitDatabase()
    database.InitializeGlobals(db)

    database.PopulateDatabase()

    //...
}

After running our main.go once, we can look into our database and check if it is correctly populated.

$ sqlite3 iothome.db
sqlite3> .table
// deviceInfo deviceStatus rooms

sqlite3> select * from rooms;
// 1|livingroom
// 2|bedroom1

sqlite3> select * from deviceInfo;
// room_id|device_id|name|ipaddr|type|hostname|macaddress
// 2|1|andre|192.168.1.1|wled||
// 2|2|betty|192.168.1.2|switch||
// 2|3|cathy|192.168.1.3|wled||
// 1|4|derick|192.168.1.4|switch||
// 1|5|eagle|192.168.1.5|wled||

sqlite3> select * from deviceStatus;
// device_status_id|device_id|connected|on_state
// 1|1|0|0
// 2|2|0|0
// 3|3|0|0
// 4|4|0|0
// 5|5|0|0

sqlite3> .exit

In SQLite3, the boolean is represented by integers. A 0 means false and 1 means true.

In the next part, we will use our DatabaseManager in our Gin routes and merge them.

---

Below is the full implementation of all our database receiver functions for the DatabaseManager type.

// database.go

func (s *DatabaseManager) AddRoom(room models.RoomInfo) error {
    _, err := s.Db.Exec(insertNewRoom, room.Name)

    if err != nil {
        log.Println("insertNewRoom query failed")
        return err
    }

    return nil
}

func (s *DatabaseManager) DelRoom(roomname string) error {
    _, err := s.Db.Exec(deleteRoom, roomname)

    if err != nil {
        log.Println("deleteRoom query failed")
        return err
    }

    return nil
}

func (s *DatabaseManager) DelDevice(roomname string, ipAddr string) error {
    _, err := s.Db.Exec(deleteDevice, roomname, ipAddr)

    if err != nil {
        log.Println("deleteDevice query failed")
        return err
    }

    return nil
}

func (s *DatabaseManager) AddDevice(dev models.RegisteredDevice, devStatus models.DeviceStatus, roomName string) error {
    row := s.Db.QueryRow(getRoomId, roomName)
    var roomId int
    err := row.Scan(&roomId)
    if err == sql.ErrNoRows {
        return err
    } else if err != nil {
        log.Println("getroomId query failed")
        return err
    }

    res, err := s.Db.Exec(insertNewDevInfo, roomId, dev.Name, dev.Ipaddr, dev.Type)

    if err != nil {
        log.Println("inserting entry into deviceInfo table failed")
        return err
    }

    var devId int64
    devId, _ = res.LastInsertId()

    _, err = s.Db.Exec(insertNewDevStatus, devId, devStatus.Connected, devStatus.On_state)

    if err != nil {
        log.Println("inserting entry into deviceStatus table")
        return err
    }

    return nil
}

func (s *DatabaseManager) UpdateDevStatus(roomName string, ipAddr string, devStatus models.DeviceStatus) error {
    device_id, _, _, err := Dbman.GetDevice(roomName, ipAddr)
    if err != nil {
        return err
    }

    _, err = s.Db.Exec(updateDeviceStatus, devStatus.Connected, devStatus.On_state, device_id)
    if err != nil {
        return err
    }

    return nil
}

func (s *DatabaseManager) GetDevice(roomName string, ipAddr string) (int, models.RegisteredDevice, models.DeviceStatus, error) {
    row := s.Db.QueryRow(getDeviceInfo, roomName, ipAddr)

    devInfo := models.RegisteredDevice{}
    devStatus := models.DeviceStatus{}
    var device_id int
    err := row.Scan(&device_id, &devInfo.Name, &devInfo.Type, &devStatus.Connected, &devStatus.On_state)
    if err == sql.ErrNoRows {
        return device_id, devInfo, devStatus, err
    } else if err != nil {
        log.Println("failed to scan db rows")
        return device_id, devInfo, devStatus, err
    }

    devInfo.Ipaddr = ipAddr

    return device_id, devInfo, devStatus, nil
}

func (s *DatabaseManager) GetDevices(roomName string) ([]models.RegisteredDevice, map[string]models.DeviceStatus, error) {
    rows, err := s.Db.Query(getDeviceInfoByRoom, roomName)
    if err != nil {
        log.Println("getDeviceInfoByRoom query failed") 
        return nil, nil, err
    }
    defer rows.Close()

    devList := []models.RegisteredDevice{}
    devStatus := make(map[string]models.DeviceStatus)
    for rows.Next() {
        rd := models.RegisteredDevice{}
        ds := models.DeviceStatus{}
        err = rows.Scan(&rd.Name, &rd.Ipaddr, &rd.Type, &ds.Connected, &ds.On_state)
        if err != nil {
            log.Println("failed to scan db rows")
            return nil, nil, err
        }

        devList = append(devList, rd)
        devStatus[rd.Ipaddr] = ds
    }

    return devList, devStatus, nil
}

func (s *DatabaseManager) GetRooms() ([]models.RoomInfo, error) {
    rows, err := s.Db.Query(getRooms)
    if err != nil {
        log.Println("getRooms query failed")
        return nil, err
    }
    defer rows.Close()

    rooms := []models.RoomInfo{}
    for rows.Next() {

        ri := models.RoomInfo{}
        err = rows.Scan(&ri.Name, &ri.Count)
        if err != nil {
            log.Println("failed to scan db rows")
            return nil, err
        }

        rooms = append(rooms, ri)
    }

    return rooms, nil
}