Learn Golang by building a fintech banking app - Lesson6: DB connection Pool and Transaction History
Tags: go, tutorial, beginners, webdev
Published: 2020-07-02 11:35:24

This article was originally published at: 
https://www.blog.duomly.com/golang-course-with-building-fintech-banking-app-lesson-6-db-connection-pool-and-transactions-history/

---

### Intro

In the 6th lesson of the golang course, we will talk about the DB connection pool and transaction history.

In the previous lessons, we built some features that made our project bigger.

Here you can find the five previous lessons:

In the previous episodes of the Course, we learned how to do migrations:

[Golang course with building a fintech banking app – Lesson 1: Start the project](https://www.blog.duomly.com/golang-course-with-building-a-fintech-banking-app-lesson-1-start-the-project/)

We learned how to do user login:

[Golang course with building a fintech banking app – Lesson 2: Login and REST API](https://www.blog.duomly.com/golang-course-with-building-a-fintech-banking-app-lesson-2-login-and-rest-api/)

We learned how to do user registration:

[Golang course with building a fintech banking app – Lesson 3: User registration](https://www.blog.duomly.com/golang-course-with-building-a-fintech-banking-app-lesson-3-user-registration/)

And we built user authentication, and started with transactions:

[Golang Course With Building a Fintech Banking App – Lesson 4: User Authentication and Bank Transactions PART 1](https://www.blog.duomly.com/golang-course-with-building-a-fintech-banking-app-lesson-4-user-authentication-and-bank-transactions-part-1/)

And we finished the possibility of doing bank transfers:

[Golang course with building a fintech banking app - Lesson 5: Bank transactions PART 2](https://www.blog.duomly.com/golang-course-with-building-a-fintech-banking-app-lesson-5-bank-transactions-part-2/)

As well you need to remember about Angular 9 course created by my friend Anna:

[Angular Course with building a banking application with Tailwind CSS – Lesson 1: Start the project](https://www.blog.duomly.com/angular-course-building-a-banking-application-with-tailwind-css-lesson-1-start-the-project/)

Today, we can focus on the transaction history and DB connection pooling.

That will help us to avoid DB-kills when too many requests come into the game.

Let's start!

And if you prefer video, here is the youtube version:

VIDEO: <—— Youtube

### Create a database package

As the first step, we need to create the new package that we will use to handle all the DB logic.

To do that, we need to create a directory named "database", into the directory, we need to create a file called "database.go", and go into the file.

Inside the file, we need to declare a package named "database".

```go
package database
```


### Create global variable DB

Next, we need to declare a variable named "DB", that we will use to access DB in other files.

```go
var DB *gorm.DB
```

### Create function InitDatabase

In the third step, we need to create a function named "InitDatabase", that will let us connect with DB, and return the DB connection object.

```go
func InitDatabase() {
	database, err := gorm.Open("postgres", "host=127.0.0.1 port=5432 user=postgres dbname=bankapp password=postgres sslmode=disable")
	helpers.HandleErr(err)
	DB = database
}
```

### Setup connection pool

One of the main elements of today's lesson is to set up the proper connection pool.

We'll do it in this step.

Add connection pool in the "InitDatabase" function, next, setup idle connections as 20, and max connections as 200.

```go
func InitDatabase() {
	database, err := gorm.Open("postgres", "host=127.0.0.1 port=5432 user=postgres dbname=bankapp password=postgres sslmode=disable")
	helpers.HandleErr(err)
	database.DB().SetMaxIdleConns(20)
	database.DB().SetMaxOpenConns(200)
	DB = database
}
```

### Init database in main.go

We have created a database package and database connection pool.

Next, we need to start using it by calling InitDatabase in the file "main.go", and inside the function "main".

```go
func main() {
	database.InitDatabase()
	api.StartApi()
}
```

### Delete ConnectDB from helpers package

Okay, when we're done with the new DB setting, we can go into the helpers.go and delete the function "ConnectDB".

### Refactor createAccounts in migrations.go and remember to always delete close DB

Now, we can go into the refactoring of the logic that uses the DB connection.


As the first place where we need to do that is the function "createAccounts" in migrations.go.
In every place that you refactor DB connection, you need to remember about deleting db.Close(), otherwise you'll close DB after every call, and the app will stop working.

```go
func createAccounts() {
	users := &[2]interfaces.User{
		{Username: "Martin", Email: "martin@martin.com"},
		{Username: "Michael", Email: "michael@michael.com"},
	}
	for i := 0; i < len(users); i++ {
		generatedPassword := helpers.HashAndSalt([]byte(users[i].Username))
		user := &interfaces.User{Username: users[i].Username, Email: users[i].Email, Password: generatedPassword}
		database.DB.Create(&user)

		account := &interfaces.Account{Type: "Daily Account", Name: string(users[i].Username + "'s" + " account"), Balance: uint(10000 * int(i+1)), UserID: user.ID}
		database.DB.Create(&account)
	}
}
```


### Refactor Migrate

As the next step, we need to refactor function "Migrate".

After this one move, we'll have all the migration-related logic in the one function.

```go
func Migrate() {
	User := &interfaces.User{}
	Account := &interfaces.Account{}
	Transactions := &interfaces.Transaction{}
	database.DB.AutoMigrate(&User, &Account, &Transactions)
	
	createAccounts()
}
```

### Delete function Migrate transactions

Now, we won't need the "MigrateTransactions" function anymore.

You can delete it.

### Refactor function CreateTransaction in transactions.go

In the fourth step, we need to refactor the "CreateTransaction" function in the "transactions.go" file, to start using a new database package.

```go
func CreateTransaction(From uint, To uint, Amount int) {
	transaction := &interfaces.Transaction{From: From, To: To}
	database.DB.Create(&transaction)
}
```

### Refactor function updateAccount to use database package

Next, go into the "updateAccount" function and refactor the same as we did before.

```go
func updateAccount(id uint, amount int) interfaces.ResponseAccount {
	account := interfaces.Account{}
	responseAcc := interfaces.ResponseAccount{}

	database.DB.Where("id = ? ", id).First(&account)
	account.Balance = uint(amount)
	database.DB.Save(&account)
	
	responseAcc.ID = account.ID
	responseAcc.Name = account.Name
	responseAcc.Balance = int(account.Balance)
	return responseAcc
}
```

### Refactor function getAccount to use database package

We have a few more places to refactor for the new database package.

In this step, we'll update "GetAccount" in the "useraccounts.go" file.

```go
func getAccount(id uint) *interfaces.Account{
	account := &interfaces.Account{}
	if database.DB.Where("id = ? ", id).First(&account).RecordNotFound() {
		return nil
	}
	return account
}
```

### Refactor function Login to use database package

Next, we can go into the file "users.go", and refactor function "Login".

```go
func Login(username string, pass string) map[string]interface{} {
	// Add validation to login
	valid := helpers.Validation(
		[]interfaces.Validation{
			{Value: username, Valid: "username"},
			{Value: pass, Valid: "password"},
		})
	if valid {
		user := &interfaces.User{}
		if database.DB.Where("username = ? ", username).First(&user).RecordNotFound() {
			return map[string]interface{}{"message": "User not found"}
		}
		// Verify password
		passErr := bcrypt.CompareHashAndPassword([]byte(user.Password), []byte(pass))

		if passErr == bcrypt.ErrMismatchedHashAndPassword && passErr != nil {
			return map[string]interface{}{"message": "Wrong password"}
		}
		// Find accounts for the user
		accounts := []interfaces.ResponseAccount{}
		database.DB.Table("accounts").Select("id, name, balance").Where("user_id = ? ", user.ID).Scan(&accounts)


		var response = prepareResponse(user, accounts, true);

		return response
	} else {
		return map[string]interface{}{"message": "not valid values"}
	}
}
```

### Refactor function Register to use database package

It's almost the last place that we need to refactor function to use a new database package.

In the same file, "users.go", we need to refactor function named "Register".

```go
func Register(username string, email string, pass string) map[string]interface{} {
	// Add validation to registration
	valid := helpers.Validation(
		[]interfaces.Validation{
			{Value: username, Valid: "username"},
			{Value: email, Valid: "email"},
			{Value: pass, Valid: "password"},
		})
	if valid {
		generatedPassword := helpers.HashAndSalt([]byte(pass))
		user := &interfaces.User{Username: username, Email: email, Password: generatedPassword}
		database.DB.Create(&user)

		account := &interfaces.Account{Type: "Daily Account", Name: string(username + "'s" + " account"), Balance: 0, UserID: user.ID}
		database.DB.Create(&account)

		accounts := []interfaces.ResponseAccount{}
		respAccount := interfaces.ResponseAccount{ID: account.ID, Name: account.Name, Balance: int(account.Balance)}
		accounts = append(accounts, respAccount)
		var response = prepareResponse(user, accounts, true)

		return response
	} else {
		return map[string]interface{}{"message": "not valid values"}
	}
	
}
```

### Refactor function GetUser

Woohoo! It's the last place where we need to replace the db connection for the database package.

Refactor function named "GetUser" in a similar way, as each other.

```go
func GetUser(id string, jwt string) map[string]interface{} {
	isValid := helpers.ValidateToken(id, jwt)
	// Find and return user
	if isValid {
		user := &interfaces.User{}
		if database.DB.Where("id = ? ", id).First(&user).RecordNotFound() {
			return map[string]interface{}{"message": "User not found"}
		}
		accounts := []interfaces.ResponseAccount{}
		database.DB.Table("accounts").Select("id, name, balance").Where("user_id = ? ", user.ID).Scan(&accounts)

		var response = prepareResponse(user, accounts, false);
		return response
	} else {
		return map[string]interface{}{"message": "Not valid token"}
	 }
}
```

### Create interface ResponseTransaction

Congratulations, all of the database refactoring is done!

Now, we can go into the transaction history.

To do that, as the first step, you need to go into the interfaces.go and create struct "ResponseTransaction".

It should have four props, ID as uint, From as uint, To as uint, and Amount as int.

```go
type ResponseTransaction struct {
	ID uint
	From uint
	To uint
	Amount int
}
```

### Create function GetTransationsByAccount

In the next step, we can go into file "transactions.go", and start creating logic related to getting transactions.

Like the first one, we need to create a function "GetTransactionsByAccount".

That function will take id as uint, and return the array of "ResponseTransaction".

The logic will look for the transactions where prop "from" or "to" are equal to the id of the account that we passed as a param.

```go
func GetTransactionsByAccount(id uint) []interfaces.ResponseTransaction{
	transactions := []interfaces.ResponseTransaction{}
	database.DB.Table("transactions").Select("id, transactions.from, transactions.to, amount").Where(interfaces.Transaction{From: id}).Or(interfaces.Transaction{To: id}).Scan(&transactions)
	return transactions
}
```

### Create function GetMyTransactions

The next function that we need to create should be named "GetMyTransactions", and take id and jwt as params, both as strings.

Function should return „map[string]interface{}”.

```go
func GetMyTransactions(id string, jwt string) map[string]interface{} {

}
```

### Validate JWT

Inside the function "GetMyTransactions", we should validate JWT token.

If the token is fine, we can go into the logic. If it's not, we should return the response with the message "Not valid token".

```go
func GetMyTransactions(id string, jwt string) map[string]interface{} {
  isValid := helpers.ValidateToken(id, jwt)
	if isValid {
  
  } else {
		return map[string]interface{}{"message": "Not valid token"}
	}
}
```

### Find and return transactions

Fine, now we can go into the logic of the "GetMyTransactions".

As the first step, we should look for all the accounts related to our user.

Next, we should iterate through all of the accounts, and start looking for the transactions related to the account, that we will append into the "transactions" array.

As the last step, we should return them to the user.

```go
func GetMyTransactions(id string, jwt string) map[string]interface{} {
  isValid := helpers.ValidateToken(id, jwt)
	if isValid {
		accounts := []interfaces.ResponseAccount{}
		database.DB.Table("accounts").Select("id, name, balance").Where("user_id = ? ", id).Scan(&accounts)
		
		transactions := []interfaces.ResponseTransaction{}
		for i := 0; i < len(accounts); i++ {
			accTransactions := GetTransactionsByAccount(accounts[i].ID)
			transactions = append(transactions, accTransactions...)
		}

		var response = map[string]interface{}{"message": "all is fine"}
		response["data"] = transactions
		return response
  } else {
		return map[string]interface{}{"message": "Not valid token"}
	}
}
```

### Create function getMyTransactions in api.go

We are almost ready!

Now, we can go into the file "api.go", and create the function "getMyTransactions".

That function similar to the "getUser" should handle the "userID", and authorization.

Next, we should pass that into the "GetMyTransactions" function, and return all of that to the user.

```go
func getMyTransactions(w http.ResponseWriter, r *http.Request) {
	vars := mux.Vars(r)
	userId := vars["userID"]
	auth := r.Header.Get("Authorization")

	transactions := transactions.GetMyTransactions(userId, auth)
	apiResponse(transactions, w)
}
```

### Handle API endpoint

As the last step, we just need to handle the API endpoint in the routing.

```go
func StartApi() {
	router := mux.NewRouter()
	// Add panic handler middleware
	router.Use(helpers.PanicHandler)
	router.HandleFunc("/login", login).Methods("POST")
	router.HandleFunc("/register", register).Methods("POST")
	router.HandleFunc("/transaction", transaction).Methods("POST")
	router.HandleFunc("/transactions/{userID}", getMyTransactions).Methods("GET")
	router.HandleFunc("/user/{id}", getUser).Methods("GET")
	fmt.Println("App is working on port :8888")
	log.Fatal(http.ListenAndServe(":8888", router))
}
```

### Conclusion

Congratulations, your code looks much better now!

I hope today's refactoring taught you the beginning of the project, and the development of new features is easy, but if the project grows, maintenance can be problematic.

We built some new features like transaction history and fixed some big-code issues, but it's not all that should be fixed.

In the next episodes, we will talk about usage SQL Relations, that will help us avoid many of the calls that we needed to do, just by using Golang Associations.

That will teach you how implementing a few good practices at the beginning of the project could save us a lot of time and work.

Code for today's lesson you can find here:
https://github.com/Duomly/go-bank-backend/tree/Golang-course-Lesson-6

<a href="https://www.duomly.com?code=lifetime-80">![Duomly promo code](https://dev-to-uploads.s3.amazonaws.com/i/vnr9vc68e3pzl9zfrgyt.png)</a>

Thanks for reading,
Radek from Duomly