Learning By Doing Golang - ToDo App Part 2.2 - Creating Database Layer

NaveenKumar VRNaveenKumar VR
11 min read

Table of contents

So far, we’ve discussed why our app needs a database, the various ways to connect a database from Golang, and how to choose the most suitable method for our project.
If you’re just joining the series or missed the earlier discussion on why a database connection is necessary and why we chose a specific approach, please check out the previous articles [link].

🧱 Building the Database Layer for Our ToDo App

With everything we’ve learned so far, let’s go ahead and build the database layer for our ToDo app.

Before we dive into the implementation, let’s outline the operations we’ll be handling through our database layer. This will help us clearly identify what we need to build:

🧩 What Will the Database Layer Do?

  1. We need to establish connection with the Database.

  2. Define the schema (i.e., table structure) to store our ToDo items

  3. Implement CRUD operations (Create, Read, Update, Delete) such as:

    • Create a new ToDo

    • Update an existing ToDo

    • Get all ToDos

    • Get a specific ToDo by ID

    • Delete a ToDo

🔁 Recap of Key Decisions

Before we begin, here’s a quick recap of what we’ve decided so far. If you missed any part, feel free to check the previous articles:

  1. We’re using MySQL as our database of choice

  2. We’ll use Golang with the GORM library, along with the MySQL driver, to interact with the database

✅ With all that clear, let’s start putting things together and build the database layer!

Establishing Connection with our DB:

Let’s start with establishing connection with our DB

package main

import (
    "fmt"
    "gorm.io/driver/mysql"
    "gorm.io/gorm"
)

var (
    // Declare a global variable to hold the DB connection
    db *gorm.DB // Type: pointer to gorm.DB
)

// ConnectDB establishes the connection to the MySQL database
func ConnectDB() {
    // Data Source Name (DSN): contains database connection info
    dsn := "root:password@tcp(127.0.0.1:3306)/todo?charset=utf8&parseTime=True&loc=Local"

    // Connect to the DB using GORM and MySQL driver
    db_conn, err := gorm.Open(mysql.Open(dsn), &gorm.Config{})
    if err != nil {
        // If connection fails, panic and log the error
        panic("Failed to connect to the database: " + err.Error())
    }

    /*Why I'm passing value here why can't I pass it like db,err. 
    If I do that I have to either add err variable globally or remove db global var as I'm using := so I used a var to exchnage value
    */
    db = db_conn 
}

Lets Define Schema:

package main

import (
    "fmt"
    "gorm.io/driver/mysql"
    "gorm.io/gorm"
    "time"
)

var (
    // Declare a global variable to hold the DB connection
    db *gorm.DB // Type: pointer to gorm.DB
)

type ToDo struct{
    gorm.Model
    TaskName    string     `gorm:"not null" json:"title"`
    Description string     `gorm:"type text" json:"description"`
    Status      string     `gorm:"default:NotStarted" json:"status" validate:"required,oneof=NotStarted InProgress Pending Completed"`
    EndDate     *time.Time `json:"end_date"`
}

// ConnectDB establishes the connection to the MySQL database
func ConnectDB() {
    ...
}

// This will initialize DB and create Schema (Our Table structure)
func InitDB(){
    ConnectDB() // This will create new connection and the value is stored in DB variable

    // Here we are saying that Use the Schema &ToDo and create table using ou DB connection 'db'
    //'{}' defines that create empty table with that ToDo Struct Schema
    db.AutoMigrate(&ToDo{})
}

We are defining schema i.e (Table structure) for our database in the form of Struct. Let deep dive what we have entered.

  • gorm.Model

    This is a built-in struct in GORM that automatically includes four common fields:

      • ID – a unique identifier for each record

        * CreatedAt – timestamp of when the record was created

        * UpdatedAt – timestamp of the last update

        * DeletedAt – soft delete timestamp

By embedding gorm.Model at the start of your struct, GORM auto-generates and manages these fields for each record in the table.

  • TaskName string `gorm:"not null" json:"title"`

    • TaskName: This is a field in your struct that will map to a column in your database table. It stores the task name for each ToDo item.

    • string: The data type of the field.

    • gorm:"not null": A GORM tag that enforces the column to never be null in the database.

    • json:"title": This tag tells Go how to map this field when converting JSON data, such as when handling API requests and responses. So, if you send or receive JSON with the key title, it will map to TaskName in your Go struct. We will see how this is used when we are testing the app.

  • Status string gorm:"default:NotStarted" json:"status" validate:"required,oneof=NotStarted InProgress Pending Completed"

    • Status: is a column in your database table used to track the current state of a ToDo item.

    • It’s of type string.

    • json:"status" : This field is same as json:title used when passing JSON using API

    • validate:"required,oneof=NotStarted InProgress Pending Completed" : This field defines that this variable is required and its value should be one of “NotStarted InProgress Pending Completed"

    • default:NotStarted: If no value is defined we are setting default value as NotStarted

  • Since EndDate variable type should be handled in time we mentioned it as *time.Time type

Implement CURD Operation

As we defined the Schema in the above step lets complete all the operation required for our ToDo App

Operation Required

  • Create a new ToDo

  • Update an existing ToDo

  • Get all ToDos

  • Get a specific ToDo by ID

  • Delete a ToDo

package main

import (
    "fmt"
    "gorm.io/driver/mysql"
    "gorm.io/gorm"
    "time"
)

var (
    // Declare a global variable to hold the DB connection
    db *gorm.DB // Type: pointer to gorm.DB
)

type ToDo struct{
    gorm.Model
    TaskName    string     `gorm:"not null" json:"title"`
    Description string     `gorm:"type text" json:"description"`
    Status      string     `gorm:"default:NotStarted" json:"status" validate:"required,oneof=NotStarted InProgress Pending Completed"`
    EndDate     *time.Time `json:"end_date"`
}

// ConnectDB establishes the connection to the MySQL database
func ConnectDB() {
    ...
}

// This will initialize DB and create Schema (Our Table structure)
func InitDB(){
  ...
}

// CreateToDo inserts a new ToDo item into the database.
// It takes a pointer to a ToDo struct as input and returns the same pointer after insertion.
func CreateToDo(todo *ToDo) *ToDo {
    // Use GORM's Create method to insert the new record into the database.
    // The &todo tells GORM to insert the data from the memory location of the passed ToDo.
    db.Create(&todo)
    // Return the same pointer, now updated with DB-generated fields (like ID, CreatedAt, etc.)
    return todo
}

// GetToDo returns a list of all ToDo items from the database.
// This function does not take any input and returns a slice of ToDo structs ([]ToDo).
func GetToDo() []ToDo {
    var todos []ToDo // Declare a variable to hold the fetched ToDo items (slice of ToDo structs)
    // Use GORM's Find method to retrieve all records from the ToDo table and store them in variable 'todos'
    db.Find(&todos)
    // Return the fetched ToDo items
    return todos
}

// GetToDoByID fetches a specific ToDo item from the database based on the provided ID.
// It takes an integer ID as input and returns a pointer to a ToDo struct.
func GetToDoByID(ID int64) *ToDo {
    // We declare a single instance of the ToDo struct (not a slice),
    // because we're expecting only one record, not a list of ToDos.
    var todo ToDo
    // Use GORM's First method to retrieve the first matching record by ID.
    // "id = ?" is a query condition where the placeholder (?) gets replaced by the ID value.
    db.First(&todo, "id = ?", ID)
    // Return a pointer to the fetched ToDo item
    return &todo
}

// DeleteToDo removes a ToDo item from the database based on the provided ID.
// It takes an integer ID as input and returns the deleted ToDo struct.
func DeleteToDo(ID int64) ToDo {
    // We declare a single instance of the ToDo struct (not a slice),
    // because we're expecting only one record, not a list of ToDos.
    var todo ToDo // Declare a variable to hold the ToDo item we want to delete

    // Use GORM's Delete method to remove the record from the database using the given ID.
    // The first argument is the model type, the second is the ID used for deletion.
    db.Delete(&todo, ID)

    // Return the todo struct (note: this will only contain ID as GORM does not fetch the record before deletion)
    return todo
}

// UpdateToDo updates an existing ToDo item in the database.
// It takes a pointer to a ToDo struct (with updated fields) as input and returns the updated ToDo struct.
func UpdateToDo(todo *ToDo) *ToDo {

    // Use GORM's Save method to update the existing record.
    // Save will update the record if it exists (based on the primary key in the struct).
    // When using db.Save(todo), you must pass all the field values — both updated and unchanged.
    // This is because Save performs a full update, replacing the entire row in the database.
    db.Save(todo)

    // Return the updated ToDo item
    return todo
}

✅ Database Layer Completed

We’ve now completed all the essential database operations (CRUD) for our ToDo app. This wraps up our Database Layer Setup! 🎉

Your code so far should include:

  • Database connection setup using GORM

  • ToDo model definition

  • Functions for Create, Read (all & by ID), Update, and Delete operations

package main

import (
    "fmt"
    "gorm.io/driver/mysql"
    "gorm.io/gorm"
    "time"
)

var (
    // Declare a global variable to hold the DB connection
    db *gorm.DB // Type: pointer to gorm.DB
)

type ToDo struct{
    gorm.Model
    TaskName    string     `gorm:"not null" json:"title"`
    Description string     `gorm:"type text" json:"description"`
    Status      string     `gorm:"default:NotStarted" json:"status" validate:"required,oneof=NotStarted InProgress Pending Completed"`
    EndDate     *time.Time `json:"end_date"`
}

// ConnectDB establishes the connection to the MySQL database
func ConnectDB() {
    // Data Source Name (DSN): contains database connection info
    dsn := "root:password@tcp(127.0.0.1:3306)/todo?charset=utf8&parseTime=True&loc=Local"

    // Connect to the DB using GORM and MySQL driver
    db_conn, err := gorm.Open(mysql.Open(dsn), &gorm.Config{})
    if err != nil {
        // If connection fails, panic and log the error
        panic("Failed to connect to the database: " + err.Error())
    }

    /*Why I'm passing value here why can't I pass it like db,err. 
    If I do that I have to either add err variable globally or remove db global var as I'm using := so I used a var to exchnage value
    */
    db = db_conn 
}

func InitDB(){
    ConnectDB() // This will create new connection and the value is stored in DB variable

    // Here we are saying that Use the Schema &ToDo and create table using ou DB connection 'db'
    //'{}' defines that create empty table with that ToDo Struct Schema
    db.AutoMigrate(&ToDo{})
}

// CreateToDo inserts a new ToDo item into the database.
// It takes a pointer to a ToDo struct as input and returns the same pointer after insertion.
func CreateToDo(todo *ToDo) *ToDo {
    // Use GORM's Create method to insert the new record into the database.
    // The &todo tells GORM to insert the data from the memory location of the passed ToDo.
    db.Create(&todo)
    // Return the same pointer, now updated with DB-generated fields (like ID, CreatedAt, etc.)
    return todo
}

// GetToDo returns a list of all ToDo items from the database.
// This function does not take any input and returns a slice of ToDo structs ([]ToDo).
func GetToDo() []ToDo {
    var todos []ToDo // Declare a variable to hold the fetched ToDo items (slice of ToDo structs)
    // Use GORM's Find method to retrieve all records from the ToDo table and store them in variable 'todos'
    db.Find(&todos)
    // Return the fetched ToDo items
    return todos
}

// GetToDoByID fetches a specific ToDo item from the database based on the provided ID.
// It takes an integer ID as input and returns a pointer to a ToDo struct.
func GetToDoByID(ID int64) *ToDo {
    // We declare a single instance of the ToDo struct (not a slice),
    // because we're expecting only one record, not a list of ToDos.
    var todo ToDo
    // Use GORM's First method to retrieve the first matching record by ID.
    // "id = ?" is a query condition where the placeholder (?) gets replaced by the ID value.
    db.First(&todo, "id = ?", ID)
    // Return a pointer to the fetched ToDo item
    return &todo
}

// DeleteToDo removes a ToDo item from the database based on the provided ID.
// It takes an integer ID as input and returns the deleted ToDo struct.
func DeleteToDo(ID int64) ToDo {
    // We declare a single instance of the ToDo struct (not a slice),
    // because we're expecting only one record, not a list of ToDos.
    var todo ToDo // Declare a variable to hold the ToDo item we want to delete

    // Use GORM's Delete method to remove the record from the database using the given ID.
    // The first argument is the model type, the second is the ID used for deletion.
    db.Delete(&todo, ID)

    // Return the todo struct (note: this will only contain ID as GORM does not fetch the record before deletion)
    return todo
}

// UpdateToDo updates an existing ToDo item in the database.
// It takes a pointer to a ToDo struct (with updated fields) as input and returns the updated ToDo struct.
func UpdateToDo(todo *ToDo) *ToDo {

    // Use GORM's Save method to update the existing record.
    // Save will update the record if it exists (based on the primary key in the struct).
    // When using db.Save(todo), you must pass all the field values — both updated and unchanged.
    // This is because Save performs a full update, replacing the entire row in the database.
    db.Save(todo)

    // Return the updated ToDo item
    return todo
}

🚀 What’s Next?

In the next section, we’ll focus on building the Application Layer (Middle Layer) — the core part of our app logic. We’ll learn how to:

  • Connect the DB layer with our application logic

  • Handle HTTP requests

  • Build out our API endpoints

Stay tuned as we start connecting all the dots and bring our ToDo app to life! 🔗✨

1
Subscribe to my newsletter

Read articles from NaveenKumar VR directly inside your inbox. Subscribe to the newsletter, and don't miss out.

golangtodoapplearningbydoinggolang-databaselearning-golanggolang-todogolang-gormgolang-ormlearning-by-doing-golang

Written by

NaveenKumar VR
NaveenKumar VR