# IOT Home application Part 3

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:

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1683384262487/48b141f3-fad1-4e11-a22e-209e2ef7c61a.png align="center")

## 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.

| Endpoint | HTTP method | Description |
| --- | --- | --- |
| /create-room | POST | creates a room |
| /rooms | GET | show all available rooms |
| /delete-rooms/:roomname | DELETE | deletes a room named by `:roomname` |
|  |  |  |
| /:roomname/add-device | POST | add a device to the room |
| /:roomname/devices | GET | show all devices in the room |
| /:roomsname/:ip | GET | show device information associated to its `:ip` |
|  |  |  |
| /:roomname/:ip/delete-device | DELETE | delete a device from the room |
| /:roomname/:ip/:toggle | POST | switches the device on and off |

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

```go
// 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.

```go
// 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.

```bash
go get github.com/mattn/go-sqlite3
```

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

```go
// 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`.

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1682782694486/4c7d3c25-27a1-4c86-aad7-ffaf23caa1ce.png align="center")

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

```go
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.

```go
// 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.

```go
// 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.

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

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.

```go
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:

```go
// 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`.

```go
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.

```bash
$ 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.

```go
// 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
}
```