05 Creating a Repository as Access Data

This time we will create an Article Repository that needs to communicate to the MySQL database. We have a table in MySQL previously explained, then we will create functions that will be used for API specification needs. Here are the functions that we will like this

1. The Save() function is used to save the article data into a table in the database
2. The Update() function is used to change article data
3. The Delete() function is used to delete article data
4. The FindById() function is used to search data by Id
5. The GetAll() function is used to retrieve all data from the tables in the database 5.

We store these functions in one file article_repository.go using type interface as a contract that we will implement in each function.

First create the article_repository.go file in the repository folder then fill the file with the code below.

package repository

import (
	"context"

	"github.com/santekno/learn-golang-restful/models"
)

type ArticleRepository interface {
	GetAll(ctx context.Context) ([]*models.Article, error)
	GetByID(ctx context.Context, id int64) (*models.Article, error)
	Update(ctx context.Context, article *models.Article) (*models.Article, error)
	Store(ctx context.Context, article *models.Article) (int64, error)
	Delete(ctx context.Context, id int64) (bool, error)
}

This interface will be the main attribute and will be injected in the usecase layer. The usecase will only know the functions in the repository through this interface, so the repository must implement this interface.

Next, we will make each of the functions that we have defined into an implementation function that we will separate the files.

Create a file in the repository folder mysql/article.go then fill in the initialization code like this

type ArticleStore struct {
	db *sql.DB
}

// New will create an object that represents the article_repository interface
func New(conn *sql.DB) *ArticleStore {
	return &ArticleStore{conn}
}

The above Struct we need to send a database connection that we will use to perform data processing such as retrieving, adding, changing and deleting article data in the table in the database. The struct initialization will be called by each function for processing needs.

Create a function to retrieve all article data

Here is the function to retrieve all article data as below.

func (r *ArticleStore) GetAll(ctx context.Context) ([]*models.Article, error) {
	var result []*models.Article

	rows, err := r.db.QueryContext(ctx, queryGetAll)
	if err != nil {
		return result, err
	}

	defer rows.Close()

	for rows.Next() {
		t := models.Article{}
		err = rows.Scan(&t.ID, &t.Title, &t.Content, &t.CreateAt, &t.UpdateAt)

		if err != nil {
			return nil, err
		}

		result = append(result, &t)
	}

	return result, nil
}

This function requires a query to retrieve data into the database, the code above calls the queryGetAll variable which contains a query like this.

const queryGetAll = `SELECT id, title, content, create_at, update_at FROM articles`

In this function we call QueryContext which is a library of database/sql that we can use to retrieve data based on the query that has been defined.

Create a Function to Retrieve Data based on Article Id

The function to retrieve data based on Id can be seen like this.

func (r *ArticleStore) GetByID(ctx context.Context, id int64) (*models.Article, error) {
	var result models.Article

	err := r.db.QueryRowContext(ctx, queryGetById, id).Scan(&result.ID, &result.Title, &result.Content, &result.CreateAt, &result.UpdateAt)
	if err != nil {
		return &result, err
	}

	return &result, nil
}

With the query used queryGetById like this.

const queryGetById = `SELECT id, title, content, create_at, update_at FROM articles WHERE id=?`

In this Id-based query we use the QueryRowContext function in the database/sql libary which is the difference from QueryContext is the result of returning the data, if we use QueryContext then the resulting data will be in the form of rows that are more than one so we need to loop because of the amount of data returned. While for QueryRowContext the data returned is only one data which we can directly input in the article struct.

Create a function to change article data

The function to change the data for this article can be seen like this.

func (r *ArticleStore) Update(ctx context.Context, article *models.Article) (*models.Article, error) {
	res, err := r.db.ExecContext(ctx, queryUpdate, article.Title, article.Content, article.UpdateAt, article.ID)
	if err != nil {
		return nil, err
	}

	count, err := res.RowsAffected()
	if err != nil {
		return nil, err
	}

	fmt.Printf("success update with affected %d", count)
	return article, nil
}

By using UPDATE sql query we can change some columns in the database with specific Id parameter.

const queryUpdate = `UPDATE article SET nim=?, name=?, birth_place=? WHERE id=?

Create an Article Data Delete Function

The following function deletes article data like this.

func (r *ArticleStore) Store(ctx context.Context, article *models.Article) (int64, error) {
	res, err := r.db.ExecContext(ctx, queryInsert, article.Title, article.Content, article.CreateAt, article.UpdateAt)
	if err != nil {
		return 0, err
	}

	lastId, err := res.LastInsertId()
	if err != nil {
		return 0, err
	}

	fmt.Printf("success create with lastId: %d", lastId)
	return lastId, nil
}

func (r *ArticleStore) Delete(ctx context.Context, id int64) (bool, error) {
	_, err := r.db.ExecContext(ctx, queryDelete, id)
	if err != nil {
		return false, err
	}

	return true, nil
}

With the DELETE sql query we can delete data in a table

const queryDelete = `DELETE FROM article WHERE id=?`

Create a function to add article data

We can create a function to add article data like this.

func (r *ArticleStore) Store(ctx context.Context, article *models.Article) (int64, error) {
	res, err := r.db.ExecContext(ctx, queryInsert, article.Title, article.Content, article.CreateAt)
	if err != nil {
		return 0, err
	}

	lastId, err := res.LastInsertId()
	if err != nil {
		return 0, err
	}

	fmt.Printf("success create with lastId: %d", lastId)
	return lastId, nil
}

Using the INSERT query we can add data to the article table.

const queryInsert = `INSERT INTO articles(title, content, create_at, update_at) VALUES(?,?,?,?)`

If we look at adding, changing and deleting data the function in the database/sql library that is used is ExecContext. We use this function when there is a need in the form of DML or when there are data changes.