Now, we have to implement the functionality of our database layer.

In the previous chapter, we designed a database layer interface that defined all of the database operations we are expected to need. This is what it looked like:

type DBLayer interface {
  GetAllProducts() ([]models.Product, error)
  GetPromos() ([]models.Product, error)
  GetCustomerByName(string, string) (models.Customer, error)
  GetCustomerByID(int) (models.Customer, error)
  GetProduct(int) (models.Product, error)
  AddUser(models.Customer) (models.Customer, error)
  SignInUser(username, password string) (models.Customer, error)
  SignOutUserById(int) error
  GetCustomerOrdersByID(int) ([]models.Order, error)
}

In our project folder, let's create a new file called orm.go in the same folder as dblayer. The file will exist in the {our_project_folder}/backend/src/dblayer folder.

The GORM package relies on plugins to connect to the different databases that GORM supports. Plugins are Go packages that need to be imported silently in the package where GORM is being used.

To import a plugin package silently in Go, along with the GORM package, we can use the following syntax:

import (
    _ "github.com/go-sql-driver/mysql"
    "github.com/jinzhu/gorm"
)

Our plugin is the github.com/go-sql-driver/mysql package. If you don't already have it installed in your machine, you will need to retrieve it using the go get command from your favorite Terminal:

go get github.com/go-sql-driver/mysql

Next, we'll need to create a Go struct type, which will implement our DBLayer interface.

Our Go struct will host a data type called *gorm.DB. The *gorm.DB type is our entry point for using GORM's functionality. Here is what the code will look like:

type DBORM struct {
  *gorm.DB
}

We need to create a constructor for our new type. The constructor will initialize our embedded *gorm.DB type. 

To obtain an initialized *gorm.DB type, we need to use a function called gorm.Open(). This function takes two arguments—our database type name, which in our case is mysql, and our connection string. A connection string basically contains information regarding how to connect to the specific database that we are trying to access. To make our constructor flexible, we won't hardcode the database name or the connection string. Instead, we'll allow this information to be passed to the constructor. Here is the code:

func NewORM(dbname, con string) (*DBORM, error) {
  db, err := gorm.Open(dbname, con)
  return &DBORM{
    DB: db,
  }, err
}

It's finally time to start implementing the methods of the DBLayer interface.

We'll start by making use of GORM helpful methods, which will spare us from having to write explicit queries. The first method to implement is GetAllProducts(). This method simply returns a list of all products, which is the equivalent of a select * SQL statement. This can be achieved using GORM's db.Find() method, which belongs to the *gorm.DB type. Here is the code:

func (db *DBORM) GetAllProducts() (products []models.Product, err error) {
  return products, db.Find(&products).Error
}

You can see how using an ORM like GORM can produce extremely efficient code. The single line of code in the preceding method executed a select * from products query at the products table and then returned all of the results. The Find() method was able to detect that we seek the products table because we supplied it with an argument of type []models.Product. 

Next, we write the GetPromos() method, which returns a list of products where the promotion field is not null.

This is simply a select statement with a where clause. GORM allows you to achieve this by using a method called Where(), combined with the Find() method we covered earlier. Here is the code:

func (db *DBORM) GetPromos() (products []models.Product, err error) {
  return products, db.Where("promotion IS NOT NULL").Find(&products).Error
}

Again, this is simple and efficient. The preceding method simply executed the equivalent of the following query:

select * from products where promotion IS NOT NULL

The Where() method can also take a Go struct value, which represents the condition in our query. We'll see that in our next DBLayer method, which is GetCustomerByName. This method takes the first and last names of a customer and then returns the customer's information. Here is the code:

func (db *DBORM) GetCustomerByName(firstname string, lastname string) (customer models.Customer, err error) {
  return customer, db.Where(&models.Customer{FirstName: firstname, LastName: lastname}).Find(&customer).Error
}

This method is very similar to the GetPromos() method, except that the Where() method is fed a Go struct value with the first and last names instead of a string where clause. The equivalent of the following query was executed:

select * from customers where firstname='..' and lastname='..'

Next, we will implement GetCustomerByID(), which will retrieve a customer by using their ID in the database.

This time, instead of using a combination of Where and Find, we will use a method called First, which can get the first result that corresponds to a certain condition:

func (db *DBORM) GetCustomerByID(id int) (customer models.Customer, err error) {
  return customer, db.First(&customer, id).Error
}

Next, we will implement a method to get a product by ID, which is very similar to GetCustomerByID(), except this time the result is a product and not a customer:

func (db *DBORM) GetProduct(id int) (product models.Product, error error) {
  return product, db.First(&product, id).Error
}

So far, we have been writing methods that execute queries and retrieve results. But now, it's time to start writing methods that add or update rows.

Our next method is AddUser(), which basically adds a new user to the database.

This method will also hash the user's password (which we will cover later in the Security section), and will set the user as logged in. GORM provides a very handy method called Create() so that we can add rows to our database:

func (db *DBORM) AddUser(customer models.Customer) (models.Customer, error) {
  //we will cover the hashpassword function later
  hashPassword(&customer.Pass)
  customer.LoggedIn = true
  return customer, db.Create(&customer).Error
}

Next, we need to implement the SignInUser method, which basically updates the loggedin field in a row representing a specific customer in our customers table.

The SignInUser method will identify the user that just logged in based on their email. We will then verify the user's password. If the password is correct, then we update the database. Here is what the code will look like:

func (db *DBORM) SignInUser(email, pass string) (customer models.Customer, err error) {
  //Verify the password, we'll cover this function later
  if !checkPassword(pass) {
    return customer, errors.New("Invalid password")
  }
  //Obtain a *gorm.DB object representing our customer's row
  result := db.Table("Customers").Where(&models.Customer{Email: email})
  //update the loggedin field
  err = result.Update("loggedin", 1).Error
  if err != nil {
    return customer, err
  }
  //return the new customer row
  return customer, result.Find(&customer).Error
}

The preceding code covers many of the methods we covered before, except in two places:

• result := db.Table("Customers").Where(&models.Customer{Email: email}): This is how we can obtain an object representing the row that we are interested in
• result.Update("loggedin", 1): This is how we update our row

The SignOutUserById() method is used to sign out users using their IDs. This will follow the same techniques that we have covered so far:

func (db *DBORM) SignOutUserById(id int) error {
 //Create a customer Go struct with the provided if
  customer := models.Customer{
    Model: gorm.Model{
      ID: uint(id),
    },
  }
 //Update the customer row to reflect the fact that the customer is not logged in
  return db.Table("Customers").Where(&customer).Update("loggedin", 0).Error
}

Finally, we implement the GetCustomerOrdersByID() method to get customer orders by customer_id:

func (db *DBORM) GetCustomerOrdersByID(id int) (orders []models.Order, err error) {
    return orders, db.Table("orders").Select("*")
                .Joins("join customers on customers.id = customer_id")
                .Joins("join products on products.id = product_id")
                .Where("customer_id=?", id).Scan(&orders).Error 

}

The preceding code is a bit different than the previous methods. This is because we need to execute a couple of joins to produce the results we seek. We need to join three tables: the orders table, the customers table, and the products table. From the customers table, we only want customers with IDs that correspond to the provided customer's ID. For the products table, we only want products where the product ID corresponds to the product ID of the current order. Luckily, the GORM package provides a method called Joins, which can be used to join tables. The preceding code will translate into the following query (assuming that we have a customer_id of value '1'):

SELECT * FROM `orders` join customers on customers.id = customer_id join products on products.id = product_id WHERE (customer_id='1')

And with that, our database layer is almost done. Let's take a look at what is middleware in the next section.