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


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?
We need to establish connection with the Database.
Define the schema (i.e., table structure) to store our ToDo items
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:
We’re using MySQL as our database of choice
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"
)
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 recordCreatedAt
– timestamp of when the record was createdUpdatedAt
– timestamp of the last updateDeletedAt
– 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 keytitle
, it will map toTaskName
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 APIvalidate:"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"
)
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"
)
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! 🔗✨
Subscribe to my newsletter
Read articles from NaveenKumar VR directly inside your inbox. Subscribe to the newsletter, and don't miss out.
Written by
