DevOping - 1

DevOping - 1: Introduction & Database

This is not intended to be a guide. I’m sure that there are simpler, and more efficient approaches to everything detailed here. HThe intention is to explain my entire learning process and the solutions that could solve the problems I faced.

Project Introduction

Two years after starting my first job, I realized I needed to keep practicing my coding skills, otherwise I would lose them. Moreover, I wanted to learn new things (DevOps), and that’s the reason I started this side project.

The main idea was to develop everything from scratch: the full stack of the application, then add automation to deliver the application to the AWS infrastructure I built.

I didn’t invest a lot of time in the application and its functionalities since I wanted to focus on the DevOps part. The app is just a simple Restaurant List app, where you can create your own restaurants and associate them to lists.

The way I did this was:

• First, develop and have a functional app working in a local environment, with some testing.
• Secondly, build AWS infrastructure and deploy the app there (and solve all the problems until the app was working again; I spent a lot of time here 😖).
• Automate the deployment every time a push was done to the master branch of the code repository.

The purpose of all this was to learn, so I have to admit that I didn’t spend a lot of time doing everything perfectly. For example, for the unit testing, I created some unit tests related to the CRUD (Create, Read, Update, and Delete) operations, but when I further developed more functionalities, I didn’t spend the time to develop more tests.

My intention is to explain everything; however, I’m not going to analyze my code line by line. I want to do several posts, focusing on different problems/functionalities I had to overcome/build.

I’m not a software developer, nor an architect, nor a DevOps engineer, so any feedback will be appreciated 😊.

Part 1: Database

Design

Before starting to code, I had to decide what type of data I wanted to store and the type of database I was going to use. I’ve always used none relational databases in my last projects, so I wanted to try something new and I went for a MySQL Database.

What I first did was install MySQL in my computer and create a database (restaurant_app) and a user (restaurantapp (Yes I’m super original)) to interact with this database:

After this, using the MySQL Workbench GUI I created the tables. I already knew what I wanted to do: ”An app where I can create restaurant lists, I can create restaurant instances or use already existent restaurants”

Hence, I knew that I was going to need these tables:

• users
• restaurants
• restaurant Lists

A User can have from 0 to many lists associated, but a list is only tied to one user (creator). A list will have 0 to many restaurants and at the same time, a restaurant can be present at 0 to many lists. I remember from my classes that when a relation between to tables was many to many (a list can have many restaurants and a restaurant can be at many lists), a new table was needed to define these relationships (otherwise we can’t know what restaurants a list has or the other way around). That’s why I also had to create this table:

• restaurant_list_associations

After deciding the fields that each table was going to have (I regret adding that amount of fields to the restaurant table), the primary keys, foreign keys, data types, this was the resulting schema:

I used auto-incremental ids as primary keys. The restaurant_list_associations has as foreign keys the ids of the restaurant and the list.

Interacting with the DB

Once I had the database created and designed, it was time to start coding a bit. I decided to do the Backend with Golang, so after configuring the environment for a long time, I created my Go project and a /database folder.

Imports and init()

Inside the /database folder, I created the database.go file and started coding. This will not be the main module of the app, but a package used to interact with the DB (that is why we will start by defining the package where this code will be part of).

This is the beginning of the database.go file:

package database

import (
	"database/sql"
	"fmt"
	"log"
	"os"
	"time"

	"github.com/Adriapt/foodlist/backend/models"
	_ "github.com/go-sql-driver/mysql" 	
	"github.com/joho/godotenv"
)

var db *sql.DB // Global variable to store the database connection

func init() {
	// Initialize the database connection during application startup
	var err error
	db, err = ConnectDB()
	if err != nil {
		log.Fatal("Error connecting to the database:", err)
	}
}

We use the underscore (_) before a package name when we want to import only a package because of its initialization side effects (like loading drivers) but we won’t use any of its functions.

Models

I import a local package that I haven’t mentioned yet, named “models”. This package is another package I created in this Golang project and contains the data structures that will be used to map with the database tables:

package models

var RestaurantType = []string{
	"Mexican",
	"Asian",
	"Indian",
	"Cafe",
	"African",
	"Mediterranean",
	"American",
	"Michelin",
	"Vegan",
}

The restaurant_type.go file just contains an slice of possible values that a restaurant can have in its “Type” parameter.

I don’t need to include here the restaurant_list_associations because this only needs to exist in the DB. Here I can just add an slice of restaurants (a slice is an array with a dynamic size) inside of the restaurant list doing the correct queries to the database, as we will see later.

As you can see, the init function is just creating a connection using the ConnectDB() function and storing a pointer to the object object that will allow us to interact with the database in the db variable.

DB connection

The ConnectDB() function looks like this:

We don’t want to hardcode credentials or variables like hostnames/URLs, so I used a .env file to store this data. Then, using the godotenv library, we retrieve the values from the environmental variables. This also gives us flexibility when using different environmental variables and obtaining different outcomes without having to modify the code at all.

// Create a new MySQL DB Connection and returns a pointer to the DB object
func ConnectDB() (*sql.DB, error) {
	godotenv.Load(".env")
	username := os.Getenv("DB_USERNAME")
	password := os.Getenv("DB_PASSWORD")
	database := os.Getenv("DB_DATABASE")
	db_hostname := os.Getenv("DB_HOSTNAME")
	db_port := os.Getenv("DB_PORT")

	//creates the string "username:password@tcp(hostname:port)/database"
	dataSourceName := fmt.Sprintf("%s:%s@tcp(%s:%s)/%s", username, password, db_hostname, db_port, database)
	fmt.Println(dataSourceName)
	db, err := sql.Open("mysql", dataSourceName)
	if err != nil {
		return nil, err
	}

	//test connection to the DB using a ping
	if err := db.Ping(); err != nil {
		return nil, err
	}

	//return the db pointer
	return db, nil
}

Then, I created the string using all these environment variables to connect to the local database. The string has this structure: username:password@tcp(hostname:port)/database.

Since I was running the database on localhost, the hostname and port were localhost:3306 and the database name is restaurant_app. I also used the username restaurantapp and its password.

With this code, I was able to create a connection to my local mySQL database.

CRUD Functions

The next step was to create some CRUD (Create, Read, Update, Delete) functions to have basic functionality and be able to interact with the database.

I won’t put them all here, but I will show one of them so I can explain how it works.

Create Restaurant

// Creates a new Restaurant in the DB
func CreateRestaurant(restaurant models.Restaurant) (models.Restaurant, error) {
	//SQL query
	query := `
		INSERT INTO restaurants (
			name, 
			type,
			street_no,
			street_name,
			city,
			postal_code,
			country,
			punctuation,
			num_reviews
		) VALUES (
			?, ?, ?, ?, ?, ?, ?, ?, ?
		)
	`

	// Executing the SQL query with the restaurant details.
	result, err := db.Exec(
		query,
		restaurant.Name,
		restaurant.Type,
		restaurant.Street_no,
		restaurant.Street_name,
		restaurant.City,
		restaurant.Postal_code,
		restaurant.Country,
		restaurant.Punctuation,
		restaurant.NumReviews,
	)
	if err != nil {
		log.Printf("Error creating restaurant: %v", err)
		return models.Restaurant{}, err
	}
	// Retrieve the auto-generated ID
	restaurantID, err := result.LastInsertId()
	if err != nil {
		log.Printf("Error getting last insert ID: %v", err)
		return models.Restaurant{}, err
	}
	restaurant.ID = int(restaurantID)
	fmt.Printf("Restaurant created: %s, Address: %s\n", restaurant.Name, restaurant.Street_name)
	return restaurant, nil
}

This function receives a restaurant object as a parameter.

As you can see, we first define the SQL query in a string. The VALUES clause only contains ?. This is because the values will be sent when executing the query using the db.Exec() function. This helps us to avoid SQL injections, since directly adding variables inside the query without validation could allow an attacker to inject malicious code. For more information about SQL injections, you can reference this post: SQL Injection

Then, we use the db.Exec() function, sending the SQL query as the first parameter and then all the parameters that will substitute each ? in order. This function doesn’t return the created object, but since in our database this object has an auto-increment ID, we can obtain it using the LastInsertId() function and then add this value to the initial restaurant object that was sent as a parameter and return it.

All the other CRUD operations follow the same methodology, but changing the SQL query and some quality checks. However, it may be worth to mention this other function.

GetListRestaurants

func GetListRestaurants(listID int) ([]models.Restaurant, error) {
	query := "SELECT restaurants.* FROM restaurants	JOIN restaurant_list_associations ON restaurants.id = restaurant_list_associations.restaurant_id WHERE restaurant_list_associations.restaurant_list_id = ?;"

	var restaurants []models.Restaurant

	rows, err := db.Query(query, listID)
	if err == sql.ErrNoRows {
		log.Printf("Error executing retrieving restaurants from list")
		return nil, err
	}

	defer rows.Close()
	for rows.Next() {
		var restaurant models.Restaurant
		if err := rows.Scan(&restaurant.ID, &restaurant.Name, &restaurant.Type, &restaurant.Street_no, &restaurant.Street_name, &restaurant.City, &restaurant.Postal_code, &restaurant.Country, &restaurant.Punctuation, &restaurant.NumReviews); err != nil {
			log.Printf("Error scanning row: %v", err)
			return nil, err
		}
		restaurants = append(restaurants, restaurant)
	}
	return restaurants, nil
}

This function returns all the restaurants that a list has (providing the list ID). It utilizes the restaurant_list_associations, performing a JOIN operation between the restaurants table and restaurant_list_associations using the restaurant_id field.

In other words, it retrieves the restaurant_list_association entries where the list_id matches the one provided in the query. Then, it uses the restaurant_id of those entries to perform a JOIN with the restaurants table to retrieve all the information of those restaurants. It repeats this process for all restaurant_list_association entries where the list_id matches the one provided, in order to retrieve all restaurants associated with that list.

Since it is a SELECT request, instead of using db.Exec(), we use the db.Query() function. This allows us to iterate over the results returned by the function and assign the values to our object parameters using the Scan() function.

I use this query to fill the RestaurantList struct and add all the restaurants the slice that it contains so the rest of the code can access them easily.

Conclusion

In this first post, aside from introducing the project, I explained how I designed the database and introduced some code I used to interact with this database.

In the next post, I will introduce GitHub Actions and unit testing and how I used these concepts to automate the unit testing of some CRUD operations.