🚀 Mastering Go with PostgreSQL: Building a CLI App with Migrations and Code Generation

Are you ready to dive into the world of Go, PostgreSQL, and command-line wizardry? In this article, we’ll build a PostgreSQL CRUD CLI application that’s not only functional but also fun to work with! Whether you’re a Go newbie or a seasoned gopher, this project will teach you how to interact with a database, manage schema changes, and create a user-friendly CLI—all while having a blast. 🎉

And guess what? You don’t have to start from scratch! The full code is available on GitHub, so you can jump right in and start exploring. Let’s get started!

---

Table of Contents

1. Why This Project?
2. Tools and Libraries Used
3. Step by Step Implementation
   • Setting Environment Variables
   • Setting Up the Database
   • Generating Type Safe SQL Queries with sqlc
   • Building the CLI Application
   • Running the Application
4. Conclusion
5. Explore the Code
6. Resources

---

🌟 Why This Project?

Building a CLI application for database interactions is like assembling a Swiss Army knife for your data. It’s practical, powerful, and a great way to learn key concepts in Go and database management. Here’s what you’ll gain:

1. Database Connectivity: Learn how to connect to and interact with a PostgreSQL database using Go.

2. Type-Safe SQL Queries: Use sqlc to generate Go code from SQL queries, ensuring type safety and reducing runtime errors.

3. Database Migrations: Manage schema changes in a controlled and reproducible way using golang-migrate.

4. CLI Development: Build a user-friendly command-line interface with flags and formatted output.

This tutorial is inspired by the excellent sqlc Getting Started with PostgreSQL Guide. If you’re new to sqlc, I highly recommend checking it out!

---

🛠️ Tools and Libraries Used

Here’s the tech stack we’ll be using to build our PostgreSQL CRUD CLI:

• pgx: A PostgreSQL driver and toolkit for Go. It’s fast, efficient, and packed with features like connection pooling and support for PostgreSQL-specific data types.

• sqlc: A SQL compiler that generates type-safe Go code from SQL queries. Say goodbye to runtime SQL errors and hello to compile-time safety! 🛡️

• golang-migrate: A database migration tool that helps you manage schema changes in a controlled and reproducible way. No more manual ALTER TABLE statements! 🚀

• tablewriter: A library for generating beautifully formatted tables in CLI applications. Perfect for making your output look sleek and professional. 📊

• godotenv: A library for loading environment variables from a .env file. Keep your secrets safe and your configuration clean. 🔒

• flag: Go’s built-in package for parsing command-line flags. It’s simple, lightweight, and perfect for building CLI applications. 🎯

This combination of tools and libraries makes our project not only powerful but also a joy to work with. Ready to dive in? Let’s go! 🚀

---

🚀 Step by Step Implementation

1. Setting Enviroment Variables:

You can choose to use a .env file and set the following variables:

DB_HOST=localhost
DB_PORT=5432
DB_USER=user
DB_PASSWORD=password
DB_NAME=author_db

Or alternatively you can set environment variables.

---

2. Setting Up the Database

First, let’s create a PostgreSQL database:

createdb author_db

Next, we’ll define the initial schema for the authors table using golang-migrate. Create a migrations directory and add the following files:

• 0001_create_authors_table.up.sql:

CREATE TABLE authors (
    id BIGSERIAL PRIMARY KEY,
    name text NOT NULL,
    bio text
);

• 0001_create_authors_table.down.sql:

DROP TABLE authors;

Run the migration to apply the schema:

migrate -path ./migrations -database "postgres://<user>:<pass>@localhost:5432/author_db?sslmode=disable" up

---

3. Generating Type Safe SQL Queries with sqlc

Define your SQL schema in a schema.sql file:

CREATE TABLE authors (
    id BIGSERIAL PRIMARY KEY,
    name text NOT NULL,
    bio text
);

Define your SQL queries in a query.sql file:

-- name: CreateAuthor :one
INSERT INTO authors (name, bio)
VALUES ($1, $2)
RETURNING *;

-- name: GetAuthor :one
SELECT * FROM authors
WHERE id = $1;

-- name: ListAuthors :many
SELECT * FROM authors
ORDER BY id;

-- name: UpdateAuthor :one
UPDATE authors
SET name = $2, bio = $3
WHERE id = $1
RETURNING *;

-- name: DeleteAuthor :exec
DELETE FROM authors
WHERE id = $1;

Define your sqlc.yaml file:

version: "2"
sql:
  - engine: "postgresql"
    queries: "query.sql"
    schema: "schema.sql"
    gen:
      go:
        package: "tutorial"
        out: "tutorial"
        sql_package: "pgx/v5"

Run sqlc to generate Go code:

sqlc generate

This creates a tutorial package with type-safe functions for executing the queries. Magic, right? ✨

---

4. Building the CLI Application

The CLI application is built using Go’s flag package to parse command-line arguments. Here’s a breakdown of the main components:

a. Helper functions
There are several helper functions utilized through out the code base, lets get the run down of what they do.

../helpers/config.go

Full code for ../helpers/config.go:

package helpers

import (
    "fmt"
    "log/slog"
    "os"

    "github.com/joho/godotenv"
)

func LoadEnv() {
    // Load environment variables from .env file
    if err := godotenv.Load(); err != nil {
        slog.Error("Error loading .env file", "error", err)
    }
}

func GetDBConnectionString() string {
    // Load environment variables from .env
    LoadEnv()

    // Read environment variables
    host := os.Getenv("DB_HOST")
    port := os.Getenv("DB_PORT")
    user := os.Getenv("DB_USER")
    password := os.Getenv("DB_PASSWORD")
    dbname := os.Getenv("DB_NAME")

    if host == "" || port == "" || user == "" || dbname == "" {
        slog.Error("Missing required environment variables for database connection")
        os.Exit(1)
    }

    // Construct the connection string
    return fmt.Sprintf("postgres://%s:%s@%s:%s/%s?sslmode=disable", user, password, host, port, dbname)
}

The LoadEnv function utilizes the godotenv package to load environment variables from a .env file. The slog package is used in this function to log errors to stdout.

The GetDBConnectionString function reads environment variables, checks if the environment variables are non empty string values, then constructs the connection string from the environment variables and returns the constructed database connection string. Why not hard code the database connection string, it is good practice, especially when dealing with secrets e.g database password, or in other cases API keys. Reading from environment variables prevents secrets from being exposed in the code base.

../helpers/crud.go

The functions defined in ../helpers/config.goperform CRUD operations on the database.

Full code for ../helpers/crud.go:

package helpers

import (
    "context"

    "github.com/jackc/pgx/v5/pgtype"
    "tutorial.sqlc.dev/app/tutorial"
)

func CreateAuthor(ctx context.Context, query *tutorial.Queries, name string, bio string) (*tutorial.Author, error) {
    newAuthor, err := query.CreateAuthor(ctx, tutorial.CreateAuthorParams{
        Name: name,
        Bio:  pgtype.Text{String: bio},
    })
    if err != nil {
        return nil, err
    }
    return &newAuthor, nil
}

func ReadAuthor(ctx context.Context, query *tutorial.Queries, id int64) (*tutorial.Author, error) {
    author, err := query.GetAuthor(ctx, id)
    if err != nil {
        return nil, err
    }
    return &author, nil
}

func ReadAuthors(ctx context.Context, query *tutorial.Queries) ([]tutorial.Author, error) {
    authors, err := query.ListAuthors(ctx)
    if err != nil {
        return nil, err
    }
    return authors, nil
}

func UpdateAuthorName(ctx context.Context, query *tutorial.Queries, id int64, name string) (*tutorial.Author, error) {
    author, err := query.UpdateAuthor(ctx, tutorial.UpdateAuthorParams{
        ID:   id,
        Name: name,
    })
    if err != nil {
        return nil, err
    }
    return &author, nil
}

func UpdateAuthorBio(ctx context.Context, query *tutorial.Queries, id int64, bio string) (*tutorial.Author, error) {
    author, err := query.UpdateAuthor(ctx, tutorial.UpdateAuthorParams{
        ID:  id,
        Bio: pgtype.Text{String: bio},
    })
    if err != nil {
        return nil, err
    }
    return &author, nil
}

func DeleteAuthor(ctx context.Context, query *tutorial.Queries, id int64) error {
    if err := query.DeleteAuthor(ctx, id); err != nil {
        return err
    }
    return nil
}

../helpers/print.go

The PrintAuthor and PrintAuthors functions use tablewriter to display author details in a table.

Full code for ../helpers/print.go:

package helpers

import (
    "fmt"
    "os"

    "github.com/olekukonko/tablewriter"
    "tutorial.sqlc.dev/app/tutorial"
)

// Helper functions to format the output as a table
func PrintAuthor(author *tutorial.Author) {
    // Create a new table writer
    table := tablewriter.NewWriter(os.Stdout)

    // Set table headers
    table.SetHeader([]string{"Field", "Value"})

    // Add rows to the table
    table.Append([]string{"ID", fmt.Sprintf("%d", author.ID)})
    table.Append([]string{"Name", author.Name})

    // Handle the Bio field (check if it's valid)
    bio := "N/A"
    if author.Bio.Valid {
        bio = author.Bio.String
    }
    table.Append([]string{"Bio", bio})

    // Configure table styling for better readability
    table.SetBorder(true)                            // Add borders around the table
    table.SetCenterSeparator("|")                    // Use a separator for columns
    table.SetColumnSeparator("|")                    // Use a separator for columns
    table.SetRowSeparator("-")                       // Use a separator for rows
    table.SetHeaderAlignment(tablewriter.ALIGN_LEFT) // Align headers to the left
    table.SetAlignment(tablewriter.ALIGN_LEFT)       // Align all columns to the left
    table.SetAutoWrapText(true)                      // Wrap long text in cells

    // Render the table
    table.Render()
}

func PrintAuthors(authors []tutorial.Author) {
    // Create a new table writer
    table := tablewriter.NewWriter(os.Stdout)

    // Set table headers
    table.SetHeader([]string{"ID", "Name", "Bio"})

    // Add rows to the table for each author
    for _, author := range authors {
        // Handle the Bio field (check if it's valid)
        bio := "N/A"
        if author.Bio.Valid {
            bio = author.Bio.String
        }

        // Append the author's details as a row
        table.Append([]string{
            fmt.Sprintf("%d", author.ID), // ID
            author.Name,                  // Name
            bio,                          // Bio
        })
    }

    // Configure table styling for better readability
    table.SetBorder(true)                            // Add borders around the table
    table.SetCenterSeparator("|")                    // Use a separator for columns
    table.SetColumnSeparator("|")                    // Use a separator for columns
    table.SetRowSeparator("-")                       // Use a separator for rows
    table.SetHeaderAlignment(tablewriter.ALIGN_LEFT) // Align headers to the left
    table.SetAlignment(tablewriter.ALIGN_LEFT)       // Align all columns to the left
    table.SetAutoWrapText(true)                      // Wrap long text in cells
    table.SetAutoFormatHeaders(true)                 // Format headers automatically

    // Render the table
    table.Render()
}

b. Main application entry point
The main application entry point is the main.go file, it serves as the orchestrator for the entire application. It sets up the database connection, parses command-line flags, and routes the appropriate CRUD operations based on user input. Let’s break it down step by step.

main.go

package main

import (
    "context"
    "flag"
    "fmt"
    "log/slog"
    "os"
    "os/signal"

    "github.com/jackc/pgx/v5/pgxpool"
    "tutorial.sqlc.dev/app/helpers"
    "tutorial.sqlc.dev/app/tutorial"
)

func main() {
    // Initialize context
    ctx, cancel := context.WithCancel(context.Background())
    defer cancel()

    // Handle SIGINT (Ctrl+C) for graceful shutdown
    go func() {
        sigChan := make(chan os.Signal, 1)
        signal.Notify(sigChan, os.Interrupt)
        <-sigChan
        cancel()
    }()

    // Create a channel to receive the connection instance
    poolChan := make(chan *pgxpool.Pool)

    // Start the run goroutine to initialize the connection instance
    go run(ctx, poolChan)

    // Wait for the connection instance from the run goroutine
    pool := <-poolChan
    defer pool.Close()

    queries := tutorial.New(pool)

    // Define flags
    createFlag := flag.Bool("c", false, "Create a new user")
    readFlag := flag.Bool("r", false, "Read a user")
    updateFlag := flag.Bool("u", false, "Update a user")
    deleteFlag := flag.Bool("d", false, "Delete a user")
    id := flag.Int64("id", 0, "The id of an author")
    name := flag.String("name", "", "The name of an author")
    bio := flag.String("bio", "", "The bio of an author")

    // Parse the flags
    flag.Parse()

    switch {
    case *createFlag:
        if *name == "" {
            slog.Error("Name is required for creating an author")
            os.Exit(1)
        }
        author, err := helpers.CreateAuthor(ctx, queries, *name, *bio)
        if err != nil {
            slog.Error("Failed to create author", "error", err)
            os.Exit(1)
        }
        helpers.PrintAuthor(author)

    case *readFlag:
        if *id == 0 {
            authors, err := helpers.ReadAuthors(ctx, queries)
            if err != nil {
                slog.Error("Failed to read author", "error", err)
                os.Exit(1)
            }
            helpers.PrintAuthors(authors)
        } else {
            author, err := helpers.ReadAuthor(ctx, queries, *id)
            if err != nil {
                slog.Error("Failed to read author", "error", err)
                os.Exit(1)
            }
            helpers.PrintAuthor(author)
        }

    case *updateFlag:
        if *id == 0 {
            slog.Error("Invalid ID", "id", *id)
            os.Exit(1)
        }
        if *name != "" {
            author, err := helpers.UpdateAuthorName(ctx, queries, *id, *name)
            if err != nil {
                slog.Error("Failed to update author name", "error", err)
                os.Exit(1)
            }
            helpers.PrintAuthor(author)
        }
        if *bio != "" {
            author, err := helpers.UpdateAuthorBio(ctx, queries, *id, *bio)
            if err != nil {
                slog.Error("Failed to update author bio", "error", err)
                os.Exit(1)
            }
            helpers.PrintAuthor(author)
        }

    case *deleteFlag:
        if *id == 0 {
            slog.Error("Invalid ID", "id", *id)
            os.Exit(1)
        }
        if err := helpers.DeleteAuthor(ctx, queries, *id); err != nil {
            slog.Error("Failed to delete author", "error", err)
            os.Exit(1)
        }
        fmt.Println("Author deleted successfully")

    default:
        fmt.Println("No valid flag is set. Usage:")
        flag.PrintDefaults()
    }
}

func run(ctx context.Context, c chan<- *pgxpool.Pool) {
    // Build connection string
    dbConnectionString := helpers.GetDBConnectionString()

    // Replace with your actual database connection string
    pgxPool, err := pgxpool.New(ctx, dbConnectionString)
    if err != nil {
        slog.Error("Failed to connect to database", "error", err)
        os.Exit(1)
    }

    // Send the connection instance through the channel
    c <- pgxPool
}

How It Works

Context Initialization:

• The context.Background() function creates a root context, which is used throughout the application for managing deadlines, cancellations, and other request-scoped values.

Environment Variables:

• The helpers.LoadEnv() function loads environment variables from the .env file, ensuring secure access to sensitive data like database credentials.

Database Connection:

• The helpers.GetDBConnectionString() function constructs the connection string, and the run function establishes a connection to a PostgreSQL database using the pgxpool package and then sends the connection pool through a channel. This is a great approach for concurrent applications where you want to manage database connections efficiently.

Flag Parsing:

• The flag package is used to define and parse command-line flags. Flags like -c, -r, -u, and -d determine the operation to perform, while --id, --name, and --bio provide the necessary arguments.

CRUD Operations:

• Based on the flags provided, the application performs the corresponding CRUD operation using the helper functions from helpers/crud.go.

Output Formatting:

• The helpers.PrintAuthor() and helpers.PrintAuthors() functions use tablewriter to display the results in a clean, formatted table.

---

5. Running the Application

Build and run the application:

go build -o author-cli
./author-cli

Use the following commands to interact with the database:

• Create an author:

  ./author-cli -c --name "John Doe" --bio "A passionate writer"

• List all authors:

  ./author-cli -r

• Read an author:

  ./author-cli -r --id 1

• Update an author:

  ./author-cli -u --id 1 --name "Jane Doe"

• Delete an author:

  ./author-cli -d --id 1

---

🎉 Conclusion

And there you have it—a fully functional PostgreSQL CRUD CLI built with Go! This project is a great starting point for building more complex database-driven applications. You can extend it by adding more features, such as pagination, filtering, or support for additional tables. Feel free to drop a comment in the comments section.

---

🔗 Explore the Code

Ready to dive into the code? Check out the full project on GitHub:

👉 PostgreSQL CRUD CLI Repository

Feel free to clone the repository, experiment with the code, and make it your own. Happy coding! 🚀

---

📚 Resources

• Go Documentation
• pgx GitHub Repository
• sqlc Documentation
• golang-migrate GitHub Repository