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
- The
Save()function is used to save the article data into a table in the database - The
Update()function is used to change article data - The
Delete()function is used to delete article data - The
FindById()function is used to search data by Id - 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.
1package repository
2
3import (
4 "context"
5
6 "github.com/santekno/learn-golang-restful/models"
7)
8
9type ArticleRepository interface {
10 GetAll(ctx context.Context) ([]*models.Article, error)
11 GetByID(ctx context.Context, id int64) (*models.Article, error)
12 Update(ctx context.Context, article *models.Article) (*models.Article, error)
13 Store(ctx context.Context, article *models.Article) (int64, error)
14 Delete(ctx context.Context, id int64) (bool, error)
15}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
1type ArticleStore struct {
2 db *sql.DB
3}
4
5// New will create an object that represents the article_repository interface
6func New(conn *sql.DB) *ArticleStore {
7 return &ArticleStore{conn}
8}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.
1func (r *ArticleStore) GetAll(ctx context.Context) ([]*models.Article, error) {
2 var result []*models.Article
3
4 rows, err := r.db.QueryContext(ctx, queryGetAll)
5 if err != nil {
6 return result, err
7 }
8
9 defer rows.Close()
10
11 for rows.Next() {
12 t := models.Article{}
13 err = rows.Scan(&t.ID, &t.Title, &t.Content, &t.CreateAt, &t.UpdateAt)
14
15 if err != nil {
16 return nil, err
17 }
18
19 result = append(result, &t)
20 }
21
22 return result, nil
23}queryGetAll variable which contains a query like this.1const 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.
1func (r *ArticleStore) GetByID(ctx context.Context, id int64) (*models.Article, error) {
2 var result models.Article
3
4 err := r.db.QueryRowContext(ctx, queryGetById, id).Scan(&result.ID, &result.Title, &result.Content, &result.CreateAt, &result.UpdateAt)
5 if err != nil {
6 return &result, err
7 }
8
9 return &result, nil
10}With the query used queryGetById like this.
1const 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.
1func (r *ArticleStore) Update(ctx context.Context, article *models.Article) (*models.Article, error) {
2 res, err := r.db.ExecContext(ctx, queryUpdate, article.Title, article.Content, article.UpdateAt, article.ID)
3 if err != nil {
4 return nil, err
5 }
6
7 count, err := res.RowsAffected()
8 if err != nil {
9 return nil, err
10 }
11
12 fmt.Printf("success update with affected %d", count)
13 return article, nil
14}By using UPDATE sql query we can change some columns in the database with specific Id parameter.
1const queryUpdate = `UPDATE article SET nim=?, name=?, birth_place=? WHERE id=?Create an Article Data Delete Function
The following function deletes article data like this.
1func (r *ArticleStore) Store(ctx context.Context, article *models.Article) (int64, error) {
2 res, err := r.db.ExecContext(ctx, queryInsert, article.Title, article.Content, article.CreateAt, article.UpdateAt)
3 if err != nil {
4 return 0, err
5 }
6
7 lastId, err := res.LastInsertId()
8 if err != nil {
9 return 0, err
10 }
11
12 fmt.Printf("success create with lastId: %d", lastId)
13 return lastId, nil
14}
15
16func (r *ArticleStore) Delete(ctx context.Context, id int64) (bool, error) {
17 _, err := r.db.ExecContext(ctx, queryDelete, id)
18 if err != nil {
19 return false, err
20 }
21
22 return true, nil
23}With the DELETE sql query we can delete data in a table
1const queryDelete = `DELETE FROM article WHERE id=?`Create a function to add article data
We can create a function to add article data like this.
1func (r *ArticleStore) Store(ctx context.Context, article *models.Article) (int64, error) {
2 res, err := r.db.ExecContext(ctx, queryInsert, article.Title, article.Content, article.CreateAt)
3 if err != nil {
4 return 0, err
5 }
6
7 lastId, err := res.LastInsertId()
8 if err != nil {
9 return 0, err
10 }
11
12 fmt.Printf("success create with lastId: %d", lastId)
13 return lastId, nil
14}Using the INSERT query we can add data to the article table.
1const 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.