Learning By Doing Golang - ToDo App Part 2.1 - Working with Databases

NaveenKumar VRNaveenKumar VR
9 min read

Table of contents

In this article, we’ll explore the various options available in Golang to interact with a database. We’ll evaluate each approach and identify the most suitable solution for our ToDo app. Once we select the best option, we’ll dive into how it works and implement it as the first step in building our application.

We’ve already discussed why a database is essential for our app and why we chose a SQL database for this project. If you’re just joining us, you can catch up on that here.

⚙️ Prerequisites

Before we start building, let’s set up the basic infrastructure for this topic.

✅ What You’ll Need:

  • Docker installed on your machine:

    • Use regular Docker on Linux

    • Use Docker Desktop on Windows or macOS

    • Make sure Docker is installed and running on your system.

  • Once it’s up, run the following command to spin up a MySQL container that we’ll use in our project.

  •   docker run --name mysql -d \
    -e MYSQL_ROOT_PASSWORD=password \
    -e MYSQL_DATABASE=todo \
    -p 3306:3306 \
    mysql:latest

  • This will start a MySQL container with:

    • Database Name: todo

    • Root Password: password

    • Port: 3306 (exposed to your local machine)

      Make sure port 3306 is not already in use. If it is, update the port accordingly.

🧭 Let’s Start Exploring the Database

Before we dive into code, let’s clarify what exactly we’ll be doing when working with the database from our Golang application.

Here are the key operations we’ll perform:

  1. Establish a connection between our Go app and the MySQL database

  2. Create the database schema — defining the table structure (rows and columns) for our ToDo items

  3. Perform CRUD operations using Go:

    • Create new ToDos

    • Read existing ToDos

    • Update ToDos

    • Delete ToDos

🔗 Establishing Connection to the Database

To connect our Go application to a database, we need to use a library that enables communication between the two. Go offers multiple options for this—both native libraries and third-party libraries built by the developer community.

we’ll start by exploring how to connect to a database using Go’s native database/sql package. This standard library provides a generic interface for interacting with SQL databases. We will look at its advantages and limitations and then we will move towards more suitable options.

🧩 Database Connection with Go Native Library

To establish a connection between our Go application and a MySQL database, we need three essential components. Let’s walk through each one:

  • Importing required library. We start by importing Go’s standard SQL library: database/sql. This package provides a generic interface for working with SQL databases. However, since it's database-agnostic, we need to tell Go which specific database we're using.

  • Add the MySQL Driver. To work with MySQL specifically, we import the driver: _ "github.com/go-sql-driver/mysql"

    💡 How do you know to use _?
    Simple—just Google! Look up golang mysql database/sql and read the official docs or examples. This approach works not just here, but for any library or tool you use. You can try it for this as well. Give a try!!!

  • Third things is DSN(DataSourceName). The DSN tells Go about connection details of database. It includes:

    • Database username

    • Database Password

    • What protocol it is using to connect

    • What is the database server url and Port

    • Database Name

    • Example :- dsn := "root:password@tcp(127.0.0.1:3306)/mysql?parseTime=true"

      • ?parseTime=true Defines Go to parse the date time in time.Time format rather byte or string format.
package main
import (
    "database/sql"
    "fmt"
    "time"
    _ "github.com/go-sql-driver/mysql"
)

func main() {
    // Setting DataSourceName(DSN) Details. "?parseTime=true" Defines Go to parse the date time in time.Time format rather byte or string format.
    // By default it parse in string, by using the "go-sql-driver/mysql" in build capability we are telling go to format the time in time.Time format so that we can use it anywhere in the program
    dsn := "root:password@tcp(127.0.0.1:3306)/mysql?parseTime=true"
    db, err := sql.Open("mysql", dsn)
    // Error Handling
    if err != nil {
        panic(err) // Stop the program immediately with give the error message
    }
    defer db.Close() // This make sure that all the db connection is closed once main exit no matter how it exit
    err = db.Ping() // We are trying to ping the DB to check DB is reachable. Not required in real world, use it for our demo
    // Error Handling    
    if err != nil {
        panic(err)
    }
    fmt.Println("Connected Successfully")

    // Declaring the variable to store the result
    var Id int
    var Zone string
    // Here QueryRow is part of our "go-sql-driver/mysql" library.
    // what we are saying here is Select 1 row(which is SQL Command) and Scan will capture the output then we ask go to store result in Memory Location(&) of testValue variable. So that the actual variable get updated instead of a copy of variable
    err = db.QueryRow("SELECT * FROM time_zone_name LIMIT 1").Scan(&Zone, &Id)
    if err != nil {
        panic(err)
    }
    fmt.Println("Test Query Result:", Id, Zone)
}

You can read through the code above, where I’ve added detailed comments explaining what each line does and why it’s used. This will help you understand the flow and purpose of each part of the connection process.

Once you're comfortable with the code, you can run it to test the database connection.

  • Create a file called main.go

  • Type the complete code into main.go. Make sure all imports and logic are included as discussed earlier.

  • Open your terminal in the project directory and run the following commands:

    • go mod init example.com/test-db - This initializes your Go module.

    • go mod tidy - This downloads and adds all the required libraries and dependencies (like the MySQL driver).

    • go run main.go - This compiles and runs your code.

Limitations of go native library

  • Manual SQL Queries: You must write full SQL queries yourself, even for simple operations.

  • Error Handling Overhead: Requires explicit error checking after every DB operation.

  • Manual Resource Management: You have to close rows and connections manually to avoid leaks.

  • Limited Productivity Features: Offers no tools for validation, relationships, or query building.

  • Repetitive boilerplate : We are forced to repeat the query multiple times at multiple location.

  • And More

To overcome these issues, developers often use third-party libraries that provide a more developer-friendly experience—like GORM, sqlx, or ent. Lets explore what it is

🚀 Introduction to ORM (Object Relational Mapping):

ORM stands for Object Relational Mapping—a technique that allows developers to interact with the database using regular Go code instead of raw SQL. The ORM internally converts that code into SQL queries and executes them on your behalf.

One of the most popular and widely used ORMs in the Go ecosystem is GORM. It simplifies database operations, reduces boilerplate code, and makes your codebase cleaner and easier to maintain—especially as your project grows.

🧩 Database Connection using GORM:

Establishing connection to DB with GORM also follow the same three steps which we discussed in previous section. Only thing which changes is we import GORM library here

  • Importing gorm library gorm.io/gorm

  • Importing gorm sql diriver as we are using sql database gorm.io/driver/mysql

  • DSN Connection details same as native database/sql connection. dsn := "root:password@tcp(127.0.0.1:3306)/mysql?charset=utf8&parseTime=True&loc=Local"

Let see how the code transforms here using GORM

package main

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

// Step 1: Define a struct to map to the table 'time_zone_name'
type TimeZoneName struct {
    ID   int    `gorm:"column:ID"`   // Map to column "ID"
    Name string `gorm:"column:Name"` // Map to column "Name"
}

func main() {
    // Step 2: Define the DSN (Data Source Name) for MySQL with parseTime=true to parse datetime properly
    dsn := "root:password@tcp(127.0.0.1:3306)/mysql?parseTime=true"

    // Step 3: Use GORM to open a connection with MySQL using the DSN
    db, err := gorm.Open(mysql.Open(dsn), &gorm.Config{})
    if err != nil {
        // If connection fails, print the error and stop execution
        panic("failed to connect database: " + err.Error())
    }

    fmt.Println("Connected successfully using GORM")

    // Step 4: Declare a variable to hold the query result
    var tz TimeZoneName

    // Step 5: Use GORM's `First` function to retrieve the first record from the 'time_zone_name' table
    // This performs: SELECT * FROM time_zone_name LIMIT 1
    result := db.Table("time_zone_name").First(&tz)

    // Step 6: Handle query error (if no rows found or any other issue)
    if result.Error != nil {
        panic("Query failed: " + result.Error.Error())
    }

    // Step 7: Print the result
    fmt.Println("Test Query Result:", tz.ID, tz.Name)
}

From the above code, we can see how fetching values from the timezone field transforms into clean, user-friendly Go code. GORM handles all the behind-the-scenes conversions automatically. While this might seem simple for a basic query, GORM’s benefits become even more evident as query complexity grows, making database interactions much easier and more maintainable.

Once you're comfortable with the code, you can run it to test the database connection.

  • Create a file called main.go

  • Type the complete code into main.go. Make sure all imports and logic are included as discussed earlier.

  • Open your terminal in the project directory and run the following commands:

    • Let’s download the Required package for our app using below command

    • go mod init example.com/test-db - This initializes your Go module.

    • go mod tidy - This downloads and adds all the required libraries and dependencies (like the MySQL driver).

    • go run main.go - This compiles and runs your code.

Wrapping Up

This concludes our article on working with databases in Go. While there is much more to explore, I hope this overview helps you get started with building your ToDo app.

Quick Summary of What We Covered So Far:

  • Why a database is essential for our ToDo app project — to store, retrieve, and manage persistent data efficiently.

  • The various options available in Go to connect with databases, including the native database/sql package and third-party ORMs.

  • How to use Go’s native database/sql package to establish connections and run queries against the database.

  • The limitations of the native package, such as verbose boilerplate code and manual data scanning.

  • How Object-Relational Mapping (ORM) tools like GORM solve these limitations by automating SQL generation and data mapping.

  • A hands-on example showing how to use GORM to connect to a database, define models, and perform queries more conveniently.

With these foundations in place, you are now equipped to build the backend for your ToDo app with confidence, leveraging Go’s ecosystem for effective database interaction.

Next Step

With the foundational knowledge we've built so far, it's time to put it into practice!

In the upcoming sessions, we will start working on our ToDo project by:

  • Establishing a robust connection to the database using GORM, ensuring efficient and safe access.

  • Configuring Go to perform CRUD operations — Create, Read, Update, and Delete — to manage our ToDo tasks seamlessly.

  • Implementing proper error handling, validations, and data modeling tailored to our project’s needs.

This practical approach will bring our ToDo app to life, bridging the gap between theory and real-world application.

0
Subscribe to my newsletter

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

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

Written by

NaveenKumar VR
NaveenKumar VR