Building a Todos App in Go: Comparing database/sql and GORM

Introduction

Welcome back! In my previous article, Building APIs in Go with OpenAPI: A Guide for Beginners, we explored the foundational steps to create a simple TODO list API using OpenAPI and Go. We designed an API contract, generated boilerplate code, and implemented the endpoints.

Now, we’ll take the TODO list API further by integrating a database. Specifically, we’ll compare two popular approaches in Go: the lightweight database/sql package and the feature-rich GORM ORM. By the end of this article, you’ll understand the benefits and trade-offs of each approach and how to implement them in your Go applications.

Why Compare database/sql and GORM?

When building APIs, the choice of database management is crucial. Do you need fine-grained control over queries, or do you prefer simplicity and abstraction?

• database/sql: A low-level interface for database interactions, offering maximum control but requiring more boilerplate code.
• GORM: An ORM that simplifies database operations with high-level abstractions but introduces additional overhead.

We’ll demonstrate both approaches by implementing the same functionality in the TODO list API.

Recap: The TODO List API

In the previous article, we implemented a simple in-memory TODO list API. Let’s briefly revisit the core functionality:

• Endpoints: Create, retrieve, and delete TODO items.
• Data Model: The Todo struct defines each task.

In this article, we’ll enhance the API by persisting TODO items in a SQLite database.

Setting Up the Project

Follow these steps to set up the project. If you’ve already completed the steps in the previous article, skip to the database implementation.

1. Generate Server Code:

   oapi-codegen -generate models,gin-server -package openapi -o ./openapi/api.gen.go api.yaml
   

2. Install Dependencies:

   go mod tidy
   

3. Project Structure:

   todos-app/
   ├── db/
   │   ├── sqlhandler.go
   │   ├── gormhandler.go
   ├── model/
   │   └── todo.go
   ├── openapi/
   │   └── api.gen.go
   ├── main.go
   ├── api.yaml
   ├── README.md
   └── go.mod
   

Database Setup

We’ll use a SQLite database hosted in a Docker container. This ensures portability and avoids manual setup complexities.

1. Pull the SQLite Docker image:

   docker pull nouchka/sqlite3
   

2. Create a folder for the database:

   mkdir -p $(pwd)/data
   

3. Run the SQLite container:

   docker run --rm -d \
     -v $(pwd)/data:/data \
     --name sqlite-todos nouchka/sqlite3
   

4. (Optional) Verify the database:

   docker exec -it sqlite-todos sqlite3 /data/todos.db
   .tables
   

Implementing database/sql

The SQLHandler uses raw SQL queries for database interactions. Here’s an example of the handler for retrieving TODOs:

func (s *SQLHandler) GetTodos() ([]model.Todo, error) {
    rows, err := s.db.Query("SELECT id, task FROM todos")
    if err != nil {
        return nil, err
    }
    defer rows.Close()

    var todos []model.Todo
    for rows.Next() {
        var todo model.Todo
        if err := rows.Scan(&todo.ID, &todo.Task); err != nil {
            return nil, err
        }
        todos = append(todos, todo)
    }
    return todos, nil
}

Implementing GORM

The GORMHandler simplifies database interactions with higher-level abstractions. For example, retrieving TODOs looks like this:

func (g *GORMHandler) GetTodos() ([]model.Todo, error) {
    var todos []model.Todo
    if err := g.db.Find(&todos).Error; err != nil {
        return nil, err
    }
    return todos, nil
}

Comparison of database/sql and GORM

Choosing between database/sql and GORM depends on your experience level and the requirements of your project. Both tools have their strengths and trade-offs, making them suitable for different kinds of developers and use cases.

Ease of Use

• GORM: Abstracts away the complexities of SQL, making it ideal for beginners or developers who don’t want to deal with raw SQL queries. However, it can obscure the underlying SQL, leading to inefficiencies in complex scenarios.
• database/sql: Offers full control and transparency over queries, but its verbosity and steep learning curve can be challenging for newcomers.

Performance

• GORM: Introduces some overhead due to its abstraction layer but is sufficient for most applications.
• database/sql: Has minimal overhead and is the go-to choice for performance-critical applications.

Flexibility

• GORM: Simplifies common operations but may require workarounds for complex queries.
• database/sql: Fully customizable, making it suitable for highly specific or advanced database operations.

Learning Curve

• GORM: Easier for beginners to get started, with an intuitive API and built-in features.
• database/sql: Demands a strong understanding of SQL, offering a more hands-on learning experience.

Features

• GORM: Includes built-in migrations, hooks, and relationships, saving development time.
• database/sql: Requires manual implementation, offering greater control but requiring more effort.

Which Approach Should You Choose?

• Choose GORM if:

  • You’re new to SQL or database management.
  • You want to quickly prototype or build an application with common CRUD operations.
  • Your application doesn’t require highly optimized queries or complex database interactions.

• Choose database/sql if:

  • You need precise control over your queries and performance.
  • Your application involves complex queries or specific database optimizations.
  • You’re comfortable working directly with SQL and value the clarity of knowing exactly what your application is executing.

Conclusion

Building on the concepts from Building APIs in Go with OpenAPI, this article demonstrated how to integrate databases into your Go projects using database/sql and GORM.

Both approaches have their place:

• GORM is a great starting point for developers new to SQL or working on straightforward CRUD applications.
• database/sql provides the power and flexibility needed for more complex projects or those requiring fine-tuned performance.

Experiment with both implementations in the TODO list API, and discover what works best for your skills and project needs. You can find the complete source code for this project in the GitHub repository.

Let me know your thoughts or questions in the comments below, and stay tuned for more hands-on guides in Go development!