Go Store App

Welcome to my first post about Software. For some months, I have been learning the Go programming language at AltSchools. We were given a previous assignment to build a store using Classes.

This blog post talks about a new and improved version of a store app. Since we had the freedom to improve it as we like, I decided to take a different approach entirely. The initial problem I had was how to implement a feature to show the inventory of the store. I considered using maps. Something like this, [ID : (The item information)]:

var shopitems = map[int]map[string]string{
 1 : {"name":"IPhone14", "qty":"2", "color":"blue", "price":"1500"},
 2 : {"name":"IPhone13 Pro", "qty":"5", "color":"red", "price":"1200"},
 3 : {"name":"IPhone13", "qty":"4", "color":"black", "price":"900"},
 4 : {"name":"Samsung S23", "qty":"1", "color":"green", "price":"1350"},
 5 : {"name":"Samsung S22", "qty":"6", "color":"white", "price":"800"},
}

With an output that resembles this:

map[1:map[color:blue name:IPhone14 price:1500 qty:2] 2:map[color:red name:IPhone13 Pro price:1200 qty:5] 3:map[color:black name:IPhone13 price:900 qty:4] 4:map[color:green name:Samsung S23 price:1350 qty:1] 5:map[color:white name:Samsung S22 price:800 qty:6]]

The problem I encountered was I couldn’t properly reference the inner values of each ID. In other words, I needed some sort of data structure that would be easy to manipulate and use. So I spoke to a friend about this and she said, “How about you use a database instead, they are meant for data storage and processing right?”. So I thought about it and decided to build my store using the MYSQL database. After a couple of nights of fighting bugs, I finally came up with a solution that works.

I am a beginner in software development learning Go. Please feel free to make pull requests to improve this project.

Specifications

This is a CLI store app built with Go. It is called the Spades shop application. It uses MYSQL for data storage and processing. The Spades shop application performs the following actions below:

• Display inventory.
• Buy products.
• Remove products.
• Add products.
• Display total sales.
• Display master inventory.

The Spades Shop application

Requirement

These requirements are tailored to Windows Amd64 operating systems.

1. Go: Install the latest version of Go but version “go1.19.4 windows/amd64” was used in this project.
2. MSQL: mysql-installer-community-8.0.31.0.msi was used. This video guide is useful for the installation. Make sure to install the MYSQL server and workbench components.
3. Go MYSQL driver: This is used to interface with Go’s database/sql package.
4. Go-pretty: This is used to create pretty tables.
5. IDE: Vscode with Go plugins.

Let’s dive in

First, let us address the database. The MYSQL database is used to store product information. This helps for easy referencing and data processing.

Using the command-line interface(CLI), log into the MYSQL instance using your credentials. In this case, the username and password are “root” and “mysqlpassword” respectively:

mysql -u root -p 

Create a new database:

mysql> CREATE DATABASE spades_shop;
mysql> exit;

That will be all on the database configuration.

Create a go.mod file to track dependencies of this application:

go mod init <Your Github repo>

Install packages and dependencies:

$ go get -u github.com/go-sql-driver/mysql
$ go get github.com/jedib0t/go-pretty/v6
$ go get github.com/jedib0t/go-pretty/v6/text@v6.4.4

The code for this project can be found here on GitHub. I will explain the major parts of the project which are the database.go and main.go sections.

database.go

package main

import (
 "database/sql"
 "fmt"
 _ "github.com/go-sql-driver/mysql"
)

var DB *sql.DB

func connectDB() {
 db, error := sql.Open(`mysql`, `root:mysqlpassword@tcp(127.0.0.1:3306)/spades_shop`)
 
 error = db.Ping()
 if error != nil {
  fmt.Println("A connection error occurred!")
  panic(error.Error())
 }

 fmt.Println("\nSuccessfully connected to database")
 // defer db.Close()
 DB = db
}

func createInventoryTable() {
 _,  error := DB.Exec(`CREATE TABLE inventory (
  inventory_id INT NOT NULL AUTO_INCREMENT,
  name VARCHAR(45) NOT NULL,
  price INT NOT NULL,
  qty INT NOT NULL,
  qty_sold INT NULL,
  PRIMARY KEY (inventory_id),
  UNIQUE INDEX name_UNIQUE (name ASC) VISIBLE);
   `)

 if error != nil {
  panic(error)
 } 

 fmt.Println("Inventory table created")
}

func insertDefaultInventory() {
 insert, error := DB.Query(`INSERT INTO inventory (name, price, qty, qty_sold)  
 VALUES
 ('Mercedes-AMG GT 53', '25000', '3', '0'),
    ('Ferrari Roma', '15000', '1', '0'),
    ('BMW X6', '19000', '5', '0'),
    ('Honda Accord', '17000', '5', '0');`)

 if error != nil {
  fmt.Println("Database insert error!")
  panic(error.Error())
 }
 fmt.Println("Inventory created")
    defer insert.Close()
}

func database () {
 connectDB()

 _, table_check := DB.Query(`SELECT * FROM inventory`)
 if table_check != nil {
  createInventoryTable()
  insertDefaultInventory()
 } else {
  return
 }
 
}

• The needed packages are imported and a DB variable is created to hold the MYSQL database connection.
• The connectDB() function opens a connection to the MYSQL database. It uses error = db.Ping() to actually check if the connection is successful or if there is an error.

The credentials for the spades_shop database created are root:mysqlpassword. It is not good practice to hardcode passwords.

• The first time the application is run, the createInventoryTable() function creates a table called inventory with inventory_id, name, price, qty, qty_sold, and removed columns. The removed column is used to mark the product as deleted.
• The insertDefaultInventory() function is used to insert default data into the inventory table. This data will be used by the rest of the application. This function is executed only the first time this application is run.
• The database() function initializes the MYSQL database, and it calls on the other functions listed above. It checks if the inventory table has already been created. If there is no table, it creates it and adds default data. If the table already exists, it simply prepares that database for connection when other functions need it.

main.go

package main

import (
 "fmt"
 "log"
 "os"
 _ "github.com/go-sql-driver/mysql"
 "github.com/jedib0t/go-pretty/v6/table"
)

type Product struct {
 productID int
 productName     string
 productPrice    int
 productQty int
 qtySold int
 isRemoved int
}

var (
 inventory Product
)

The needed packages are imported. A product struct is created to hold the equivalent data created in the database. Basically when data is pulled from the MYSQL database, Go needs to have variables to store them, hence the product struct.

// Add new n line(s)
func newline(numberOfLines int) {
 i := 0
 for i < numberOfLines {
  fmt.Println("\n")
  i++
 }
}

func welcomePage() {
 newline(1)
 fmt.Println("Welcome to ")
 fmt.Println("\n███████╗██████╗  █████╗ ██████╗ ███████╗███████╗    ███████╗██╗  ██╗ ██████╗ ██████╗ ")
 fmt.Println("██╔════╝██╔══██╗██╔══██╗██╔══██╗██╔════╝██╔════╝    ██╔════╝██║  ██║██╔═══██╗██╔══██╗")
 fmt.Println("███████╗██████╔╝███████║██║  ██║█████╗  ███████╗    ███████╗███████║██║   ██║██████╔╝")
 fmt.Println("╚════██║██╔═══╝ ██╔══██║██║  ██║██╔══╝  ╚════██║    ╚════██║██╔══██║██║   ██║██╔═══╝")
 fmt.Println("███████║██║     ██║  ██║██████╔╝███████╗███████║    ███████║██║  ██║╚██████╔╝██║")
 fmt.Println("╚══════╝╚═╝     ╚═╝  ╚═╝╚═════╝ ╚══════╝╚══════╝    ╚══════╝╚═╝  ╚═╝ ╚═════╝ ╚═╝\n")
 fmt.Println("We sell Luxury cars and offer free deliveries worldwide.")
}

• The newline() prints new (n) lines.
• The welcomePage() prints a welcome banner.

// Display different menu options
func displayMenu() {
 newline(1)
 fmt.Println("Select an operation:")
 fmt.Println("1. Show inventory.\t\t2. Buy product.")
 fmt.Println("3. Remove product.\t\t4. Add product.")
 fmt.Println("5. View sales.\t\t\t6. Show master inventory.")
 fmt.Println("7. Exit.")

 var menuNumber int
 _, err := fmt.Scan(&menuNumber)

 if err != nil {
  fmt.Println("Error: Please enter a number between 1 and 6")
 }

 //Switch for menu options
 switch menuNumber {
 case 1:
  showInventory()
 case 2:
  buyProduct()
 case 3:
  removeProduct()
 case 4:
  addProduct()
 case 5:
  showSales()
 case 6:
  showMasterInventory()
 case 7:
  exit()
 default:
  fmt.Println("Error: Please enter a number between 1 and 6")
  displayMenu()
 }
}

The displayMenu() function above describes the CLI menu options of the store. You can:

• Display inventory.
• Buy a product.
• Remove a product.
• Add a product.
• Display total sales.
• Display master inventory.

// Display available products information.
func showInventory(){
 fmt.Println("This is our current inventory")
 showInventoryDatabase, error := DB.Query(`SELECT * FROM inventory;`)
 if error != nil{
  log.Fatal("Error occured when selecting inventory data:", error)
 }

     t := table.NewWriter()
 t.SetOutputMirror(os.Stdout)
 t.AppendHeader(table.Row{"productID", "Product name", "Price (USD)", "Quantity", "Quantity sold"})
 for showInventoryDatabase.Next() {
  error = showInventoryDatabase.Scan(&inventory.productID, &inventory.productName, &inventory.productPrice, &inventory.productQty, &inventory.qtySold, &inventory.isRemoved)
  if error != nil{
   log.Fatal("Error occured!:", error)
  }
        
  if inventory.isRemoved == 0 && inventory.productQty > 0 {
   t.AppendRows([]table.Row{
    {inventory.productID, inventory.productName, inventory.productPrice, inventory.productQty, inventory.qtySold},
   })
   t.AppendSeparator()
 
  }
 }
 // newline(1)
 t.Render()
 displayMenu()
}

// Buy a product from the store.
func buyProduct() {
 var id int
 fmt.Println("Please select the productID of the product you want to purchase:")
 _, error := fmt.Scan(&id)
 if error != nil {
  panic(error)
 }
 
 selector, error := DB.Query(`SELECT * FROM inventory WHERE product_id = ?;`, id)
 if error != nil{
  log.Fatal("Error occured when preparing the inventory data:", error)
 }

 for selector.Next() {
  error = selector.Scan(&inventory.productID, &inventory.productName, &inventory.productPrice, &inventory.productQty, &inventory.qtySold, &inventory.isRemoved)
  if error != nil{
   panic(error.Error())
  }
 }

 if error != nil{
  log.Fatal("Error occured when fetching inventory data:", error)
 }

 if id != inventory.productID {
  fmt.Println("Please enter a valid product ID")
  return
 }

 if inventory.productQty == 0 {
  fmt.Println("This product is unavailable for sale")
  return
 } else {
  // Reduce the quantity of the store by 1.
  inventory.productQty = inventory.productQty - 1
  inventory.qtySold = inventory.qtySold + 1
  _, error := DB.Exec(`UPDATE inventory SET qty = ?, qty_sold = ? WHERE product_id = ?;`, inventory.productQty, inventory.qtySold, id)
  if error != nil {
   panic(error)
  }
 }

 fmt.Printf("You successfuly purchased %s costing $%d from Spades shop.", inventory.productName, inventory.productPrice)
 newline(1)

 displayMenu()
}

• The showInventory() function displays only the products that are available for sale.
• The buyProduct() function allows a user to purchase a product from the store using a productID. It checks that the productID is valid and not zero. It then decreases the product’s quantity, increases the quantity sold, and updates the inventory table.

// Remove a product from the store.
func removeProduct() {
 var id int
 // deleted := 0
 fmt.Println("Please select the productID of the product you want to remove from the store:")
 _, error := fmt.Scan(&id)
 if error != nil {
  panic(error)
 }
 
 toBeDeleted, error := DB.Query(`SELECT removed, name FROM spades_shop.inventory WHERE product_id = ?;`, id)
 if error != nil{
  log.Fatal("Error occured when fetching inventory data:", error)
 }
 
 for toBeDeleted.Next(){
  error = toBeDeleted.Scan(&inventory.isRemoved, &inventory.productName)
  
  if error != nil{
   panic(error.Error())
  }

  fmt.Println("deleted: ", inventory.isRemoved)
  if inventory.isRemoved == 1 {
   fmt.Println("The ID of this product is not in the Inventory! Enter a valid product ID.")
   displayMenu()
  } else {
   remove, error := DB.Query(`UPDATE inventory SET removed = '1' WHERE product_id = ?;`, id)
   if error != nil{
   log.Fatal("Error occured when deleting product from the inventory:", error)
   }

   defer remove.Close()
   fmt.Printf("You have successfuly removed %s from Spades shop.", inventory.productName)
   showInventory()
   displayMenu()
  }
 }
}

// Add a new product to the store.
func addProduct() {
 newProduct := new(Product)
 fmt.Println("Enter the name of the Car: ")
 _, err_name := fmt.Scan(&newProduct.productName)
 if err_name != nil{
  log.Fatal(err_name)
 }

 fmt.Println("Enter the price of the product: ")
 _, err_price := fmt.Scan(&newProduct.productPrice)
 if err_price != nil{
  log.Fatal(err_price)
 } else if newProduct.productPrice * 1 != newProduct.productPrice || newProduct.productPrice * 0 != 0 {
  fmt.Println("Enter a number!")
  return
 }

 fmt.Println("Enter the quantity of the product: ")
 _, err_qty := fmt.Scan(&newProduct.productQty)
 if err_qty != nil{
  log.Fatal(err_qty)
 } else if newProduct.productQty * 1 != newProduct.productQty || newProduct.productQty * 0 != 0 {
  fmt.Println("Enter a number!")
  return
 }

 newProduct.qtySold = 0
 // Insert new product into the database.
 insertQuery := "INSERT INTO inventory (name, price, qty, qty_sold) VALUES (?, ?, ?, ?);"
 stmt, error := DB.Prepare(insertQuery)
 if error != nil {
  log.Fatal("Unable to prepare statement:", error)
 }

 _, err := stmt.Exec( newProduct.productName, newProduct.productPrice, newProduct.productQty, newProduct.qtySold)
 if err != nil {
  log.Fatal("Unable to execute statement:", error)
 }
 fmt.Printf("%s was succesfully added to the store.", newProduct.productName)
    
 // newline(1)
 displayMenu()
}

• The removeProduct() function removes a product from the store. The removed project will no longer be available for purchase.
• The addProduct() function adds a new product to the store.

// Show the current sales of the store.
func showSales () {
 fmt.Println("Below shows the current sales of Spades shop")
 currentSales, error := DB.Query(`SELECT name, price, qty_sold FROM inventory;`)
 if error != nil{
  log.Fatal("Error occured when fetching sales data:", error)
 }

 sales := 0
 // salesPerProduct := 0
 for currentSales.Next() {
  
  error = currentSales.Scan(&inventory.productName, &inventory.productPrice, &inventory.qtySold)
  if error != nil{
   panic(error.Error())
  }
        
  if inventory.qtySold > 0 {
   salesPerProduct := inventory.qtySold * inventory.productPrice
   fmt.Printf("We sold %d %s car(s) worth $%d\n", inventory.qtySold, inventory.productName, inventory.productPrice)
   sales += salesPerProduct
  }
  
 }

 fmt.Printf("The total sales is $%d.", sales)
 displayMenu()
}

// Display all products information. This menu shows available, soldout and deleted products.
func showMasterInventory(){
 fmt.Println("This is our current inventory")
 showInventoryDatabase, error := DB.Query(`SELECT * FROM inventory;`)
 if error != nil{
  log.Fatal("Error occured when fetching inventory data:", error)
 }


 t := table.NewWriter()
 t.SetOutputMirror(os.Stdout)
 t.AppendHeader(table.Row{"productID", "Product name", "Price (USD)", "Quantity", "Quantity sold", "Deleted(yes: 1, no: 0)"})
 for showInventoryDatabase.Next() {
  error = showInventoryDatabase.Scan(&inventory.productID, &inventory.productName, &inventory.productPrice, &inventory.productQty, &inventory.qtySold, &inventory.isRemoved)
  if error != nil{
   panic(error.Error())
  }
        
  t.AppendRows([]table.Row{
   {inventory.productID, inventory.productName, inventory.productPrice, inventory.productQty, inventory.qtySold, inventory.isRemoved},
  })
  t.AppendSeparator()
 }
 // newline(1)
 t.Render()
 displayMenu()
}

// Exit the store.
func exit() {
 fmt.Println("Thanks for using our shop.")
 newline(1)
 fmt.Println("\t██████╗  ██████╗  ██████╗ ██████╗     ██████╗ ██╗   ██╗███████╗")
 fmt.Println("\t██╔════╝ ██╔═══██╗██╔═══██╗██╔══██╗    ██╔══██╗╚██╗ ██╔╝██╔════╝")
 fmt.Println("\t██║  ███╗██║   ██║██║   ██║██║  ██║    ██████╔╝ ╚████╔╝ █████╗")
 fmt.Println("\t██║   ██║██║   ██║██║   ██║██║  ██║    ██╔══██╗  ╚██╔╝  ██╔══╝")
 fmt.Println("\t╚██████╔╝╚██████╔╝╚██████╔╝██████╔╝    ██████╔╝   ██║   ███████╗")
 fmt.Println("\t ╚═════╝  ╚═════╝  ╚═════╝ ╚═════╝     ╚═════╝    ╚═╝   ╚══════╝")
 os.Exit(0)
}

• The showSales() function displays the quantity of products purchased and the total sales of the store.
• The showMasterInventory() function solves the initial problem I talked about in the introduction. It selects all products in the database and presents them using the product struct defined earlier. It uses the infamous “SELECT * FROM <TABLE>” SQL to query the inventory table. Using this function we can see all products that have been added to the store. These are the available, sold out, and removed products.
• The exit() function displays a banner and exits the program.

Conclusion

In this project, we built a command line Store application using Go programming language and a MYSQL database. A user can perform the following actions in the store:

• Display inventory.
• Buy products.
• Remove products.
• Add products.
• Display total sales.
• Display master inventory.

The code for this project can be found here on GitHub.