How to Mix Magic: A Fun Dive into Go, Gin-Gonic, Gorm, and HTMX, Part 2: The database

Introduction

In my previous post, we set up the packages for our website. Now it is time to start building. We will start by building an important part of the backend, namely the database functions.

If you haven’t done already, go into the empty directory you created and open your IDE.

Creating the model

Since we are creating a very simple todolist we use only one model. To create that, in your empty directory create a directory called ‘models’. In the newly created directory create a file called ‘item.go’. This file will hold the model for our todolist. It looks like this:

package models

type Item struct {
	Id          uint   `gorm:primary_key,autoIncrement`
	Title       string `gorm:"type:varchar(100)"`
	Description string `gorm:"type:varchar(255)"`
	Finished    bool   `gorm:"type:bool"`
}

From this we can see that a todo item has four fields in our system:

1. The Id field, used as the primary key
2. The Title field
3. A Description field
4. And a field to note the fields status.

Each field is tagged with information on how to map the field on the database by providing some metadata about the field. For example the Id field is a primary_key so it has to be unique, and it is auto-incrementing.

The metadata, as you can see in the rest of the fields, also provides information on which database type is used for mapping the field.

Setting up the database and its functions

In your directory, make a directory called db. In that directory create a file called ‘db.go’. We will start this file by setting the package name and importing some packages:

package db

import (
	"fmt"
	"gorm.io/driver/postgres"
	"gorm.io/gorm"
	"<your github username>/ginproject/models"
	"os"
	"sync"
)

Make sure that <your github username> is the same name you chose when setting up this project in our previous chapter. We need the gorm package for the ORM functionality we will need, and the from the sync package we need the Once to make sure some code runs exactly one time.

The os is used because it has some methods for reading environment variables. Finally we use the fmt package for formatting strings.

The DatabaseConnection struct

It is sometimes handy to have all the information for a connection in one struct. In our case that is just one field: the connection of type gorm.DB:

type DatabaseConnection struct {
	Connection *gorm.DB
}

Since we do not want to create new database connections all the time, we build a sort of singleton. Since Go has no concept of static variables, we use some package-wide variables to try and emulate such behaviour:

var (
	connectionInstance *DatabaseConnection
	connectionOnce     sync.Once
)

Some utility functions

In order for us to construct the right connectionstring for our database, we need two utility functions. The first one tries to get the value of an environment variable, and if that variable does not exist, it returns the supplied default value:

func getEnvironmentVariableWithDefault(key string, defaultValue string) string {
	value := os.Getenv(key)
	if value == "" {
		return defaultValue
	}
	return value
}

Now we are ready to construct the Datasource Name (DSN) or connection string:

func constructDsn() string {
	host := getEnvironmentVariableWithDefault("host", "localhost")
	user := getEnvironmentVariableWithDefault("user", "postgres")
	password := getEnvironmentVariableWithDefault("password", "<your password>")
	dbname := getEnvironmentVariableWithDefault("dbname", "htmx_todolist")
	port := getEnvironmentVariableWithDefault("port", "5432")

	dsn := fmt.Sprintf("host=%s user=%s password=%s dbname=%s port=%s", host, user, password, dbname, port)

	return dsn
}

What you can also do now is install your local Postgresql instance, or make sure you have access to one, get your username and password there and create a database called htmx_todolist.

Creating connections

Now that we have all this, let’s start by actually constructing a database connection with our DSN:

func initializeDatabaseConnection() (*gorm.DB, error) {
	dsn := constructDsn()
	db, err := gorm.Open(postgres.Open(dsn), &gorm.Config{})
	return db, err
}

All this does is use the gorm.Open() method, to try and open a database connection, and return the connection, and error object to the caller.

Next we come to our public method which uses a singleton-like approach to create a connection and also perform some migrations when needed. Because both of these actions are done within a Once block, it only happens at the start-up of our web-app. It looks like this:

func GetConnection() (*DatabaseConnection, error) {
	var connectionError error = nil
	connectionOnce.Do(func() {
		dbConnection, dbError := initializeDatabaseConnection()
		if dbError != nil {
			connectionError = dbError
			connectionInstance = nil
			return
		}
		migrateError := dbConnection.AutoMigrate(&models.Item{})
		if migrateError != nil {
			connectionError = migrateError
			connectionInstance = nil
			return
		}
		connectionInstance = &DatabaseConnection{Connection: dbConnection}
	})
	return connectionInstance, connectionError
}

Some explanation:

1. We start by initializing an error variable connectionError to nil. We return this variable at the end of the function
2. Next we start our Once block by trying to initialize the connection. If that fails, the database connection is set to til, and the connectionError is assigned the error.
3. We follow the same procedure when we try to perform a migration.
4. If everything goes well, and no errors occur we initialize our connectionInstance variable.
5. Finally we return the databaseconnection, and the error.

The CRUD methods

Now that we have all of our connection setup done, we can concentrate on getting and retrieving data from the database. We di this by using CRUD method. CRUD stands for Create, Retrieve, Update and Delete. In our example we will leave out the Update. All of these methods are methods of the DatabaseConnection struct we defined earlier.

Get all items: the Fetch() method

The first method we implement simply finds and returns all items. It looks like this:

func (db *DatabaseConnection) Fetch() []models.Item {
	var items []models.Item
	db.Connection.Order("Id DESC").Find(&items)

	return items
}

Some explanation:

1. We define a slice of models.Item which will be filled with the result of our query
2. The Find() method will retrieve all items in the database. Also note that we use order to make sure that our method returns items in the same order, which is very important since we are handing them to an API and ultimately to browser interface. Also note that the Order() method is called before the Find() method. I found that quite counterintuitive

Get a particular item: the Find() method

Sometimes you need to find a particular item in the database. In our simplified example we will just look for an item with a specific primary key, the id. The method looks like this:

func (db *DatabaseConnection) Find(id string) (models.Item, error) {
	var item models.Item
	result := db.Connection.First(&item, id)
	if result.Error != nil {
		return item, result.Error
	}
	return item, nil
}

Line by line:

1. We define an item of type models.Item . This will be our result item
2. Then we try and find it in the database.
3. If we do not find it or another error occurs we handle that
4. If no error occurs, we return the found item

Making a new item: the Create() method

Because GORM provides a lot of functionality, the Create() method is very simple:

func (db *DatabaseConnection) Create(item *models.Item) (*models.Item, error) {
	result := db.Connection.Create(item)
	return item, result.Error
}

This code is self explanatory. If an error occurs during the creation of the object in the database, we return that error to the caller.

Deleting an item: the Delete() method

Also, because of GORM’s built-in functionality, the Delete() method has become very simple:

func (db *DatabaseConnection) Delete(id string) error {
	result := db.Connection.Delete(&models.Item{}, id)
	return result.Error
}

Like in the Create() method, if an error occurs it is returned to the caller of the method.

And finishing off: the ToggleFinished() method

This is a utility method. It toggles whether a task has status finished or not. It looks like this:

func (db *DatabaseConnection) ToggleFinished(id string) error {
	var item models.Item
	result := db.Connection.First(&item, id)
	if result.Error != nil {
		return result.Error
	}
	item.Finished = !item.Finished
	db.Connection.Save(&item)
	return nil
}

The method basically consists of three parts:

1. Finding the item with the specified Id and returning an error we do noty find it or another error has occurred
2. Flipping the Finished flag, from true to false or vice versa.
3. Saving the modified item.

Conclusion

That was quite a lot of code but we’re not there yet. Next we need to define the web api, and after that the front end. The one thing that is clear, is that using GORM, or ORM’s in general, makes using databases much easier.