Echo Query Building

Introduction

Query building is a fundamental aspect of database operations in web applications. In Echo, you can construct SQL queries programmatically using the built-in query builder, which offers a clean and intuitive way to interact with your database. This approach helps prevent SQL injection attacks and makes your code more maintainable.

In this guide, we'll explore the Echo query building capabilities, understand how to construct various types of queries, and learn best practices for effective database interactions.

Understanding Echo's Query Builder

Echo's query builder provides a fluent interface to create SQL statements. Instead of writing raw SQL strings, you construct queries using method chaining, which makes your code more readable and less prone to errors.

Basic Query Structure

The query builder typically follows this pattern:

go
db.Table("users").
   Select("name", "email").
   Where("status = ?", "active").
   OrderBy("created_at DESC").
   Get()

This approach is more structured than writing:

sql
SELECT name, email FROM users WHERE status = 'active' ORDER BY created_at DESC

Getting Started with Query Building

Setting Up the Database Connection

Before you can build queries, you need to establish a database connection:

go
package main

import (
  "github.com/labstack/echo/v4"
  "github.com/go-sql-driver/mysql"
)

func main() {
  e := echo.New()
  
  // Configure the database connection
  db, err := sql.Open("mysql", "user:password@tcp(localhost:3306)/mydatabase")
  if err != nil {
    e.Logger.Fatal(err)
  }
  defer db.Close()
  
  // Your routes and handlers follow...
}

Basic Query Operations

SELECT Queries

To retrieve data from a table, use the Select method:

go
// Handler function
func getUsers(c echo.Context) error {
  // Create a query to get all active users
  rows, err := db.Query("SELECT id, name, email FROM users WHERE status = ?", "active")
  if err != nil {
    return c.JSON(http.StatusInternalServerError, map[string]string{
      "error": "Failed to query users",
    })
  }
  defer rows.Close()
  
  // Process the results
  var users []User
  for rows.Next() {
    var user User
    if err := rows.Scan(&user.ID, &user.Name, &user.Email); err != nil {
      return c.JSON(http.StatusInternalServerError, map[string]string{
        "error": "Failed to scan user",
      })
    }
    users = append(users, user)
  }
  
  return c.JSON(http.StatusOK, users)
}

INSERT Queries

To add new records to a table:

go
func createUser(c echo.Context) error {
  // Bind the request body to a User struct
  u := new(User)
  if err := c.Bind(u); err != nil {
    return c.JSON(http.StatusBadRequest, map[string]string{
      "error": "Invalid request payload",
    })
  }
  
  // Execute the insert query
  result, err := db.Exec(
    "INSERT INTO users (name, email, status) VALUES (?, ?, ?)",
    u.Name, u.Email, "active",
  )
  if err != nil {
    return c.JSON(http.StatusInternalServerError, map[string]string{
      "error": "Failed to create user",
    })
  }
  
  // Get the ID of the newly inserted record
  id, _ := result.LastInsertId()
  
  u.ID = int(id)
  return c.JSON(http.StatusCreated, u)
}

UPDATE Queries

To modify existing records:

go
func updateUser(c echo.Context) error {
  // Get the user ID from the URL parameter
  id := c.Param("id")
  
  // Bind the request body to a User struct
  u := new(User)
  if err := c.Bind(u); err != nil {
    return c.JSON(http.StatusBadRequest, map[string]string{
      "error": "Invalid request payload",
    })
  }
  
  // Execute the update query
  _, err := db.Exec(
    "UPDATE users SET name = ?, email = ? WHERE id = ?",
    u.Name, u.Email, id,
  )
  if err != nil {
    return c.JSON(http.StatusInternalServerError, map[string]string{
      "error": "Failed to update user",
    })
  }
  
  return c.JSON(http.StatusOK, u)
}

DELETE Queries

To remove records from a table:

go
func deleteUser(c echo.Context) error {
  // Get the user ID from the URL parameter
  id := c.Param("id")
  
  // Execute the delete query
  _, err := db.Exec("DELETE FROM users WHERE id = ?", id)
  if err != nil {
    return c.JSON(http.StatusInternalServerError, map[string]string{
      "error": "Failed to delete user",
    })
  }
  
  return c.NoContent(http.StatusNoContent)
}

Advanced Query Building

Joining Tables

When working with related data across tables, you'll need to use SQL joins:

go
func getUserPosts(c echo.Context) error {
  userId := c.Param("id")
  
  rows, err := db.Query(`
    SELECT p.id, p.title, p.content, u.name as author 
    FROM posts p
    INNER JOIN users u ON p.user_id = u.id
    WHERE u.id = ?
  `, userId)
  if err != nil {
    return c.JSON(http.StatusInternalServerError, map[string]string{
      "error": "Failed to query user posts",
    })
  }
  defer rows.Close()
  
  var posts []Post
  for rows.Next() {
    var post Post
    if err := rows.Scan(&post.ID, &post.Title, &post.Content, &post.Author); err != nil {
      return c.JSON(http.StatusInternalServerError, map[string]string{
        "error": "Failed to scan post",
      })
    }
    posts = append(posts, post)
  }
  
  return c.JSON(http.StatusOK, posts)
}

Transactions

For operations that require multiple related database actions, transactions ensure data integrity:

go
func transferFunds(c echo.Context) error {
  // Parse request
  var transfer struct {
    FromAccountID int     `json:"from_account_id"`
    ToAccountID   int     `json:"to_account_id"`
    Amount        float64 `json:"amount"`
  }
  
  if err := c.Bind(&transfer); err != nil {
    return c.JSON(http.StatusBadRequest, map[string]string{
      "error": "Invalid request payload",
    })
  }
  
  // Begin transaction
  tx, err := db.Begin()
  if err != nil {
    return c.JSON(http.StatusInternalServerError, map[string]string{
      "error": "Failed to start transaction",
    })
  }
  
  // Deduct from source account
  _, err = tx.Exec(
    "UPDATE accounts SET balance = balance - ? WHERE id = ?",
    transfer.Amount, transfer.FromAccountID,
  )
  if err != nil {
    tx.Rollback()
    return c.JSON(http.StatusInternalServerError, map[string]string{
      "error": "Failed to update source account",
    })
  }
  
  // Add to destination account
  _, err = tx.Exec(
    "UPDATE accounts SET balance = balance + ? WHERE id = ?",
    transfer.Amount, transfer.ToAccountID,
  )
  if err != nil {
    tx.Rollback()
    return c.JSON(http.StatusInternalServerError, map[string]string{
      "error": "Failed to update destination account",
    })
  }
  
  // Commit transaction
  if err = tx.Commit(); err != nil {
    return c.JSON(http.StatusInternalServerError, map[string]string{
      "error": "Failed to commit transaction",
    })
  }
  
  return c.JSON(http.StatusOK, map[string]string{
    "message": "Transfer successful",
  })
}

Using Query Parameters

Always use parameterized queries to prevent SQL injection:

go
// UNSAFE - DO NOT DO THIS:
query := "SELECT * FROM users WHERE username = '" + username + "'"

// SAFE - ALWAYS DO THIS:
rows, err := db.Query("SELECT * FROM users WHERE username = ?", username)

Real-World Example: Building a REST API

Let's create a complete REST API for a simple blog application:

go
package main

import (
  "database/sql"
  "net/http"
  
  "github.com/labstack/echo/v4"
  _ "github.com/go-sql-driver/mysql"
)

type Post struct {
  ID      int    `json:"id"`
  Title   string `json:"title"`
  Content string `json:"content"`
  AuthorID int   `json:"author_id"`
}

var db *sql.DB

func main() {
  // Initialize database connection
  var err error
  db, err = sql.Open("mysql", "user:password@tcp(localhost:3306)/blog")
  if err != nil {
    panic(err)
  }
  defer db.Close()
  
  // Initialize Echo
  e := echo.New()
  
  // Routes
  e.GET("/posts", getAllPosts)
  e.GET("/posts/:id", getPost)
  e.POST("/posts", createPost)
  e.PUT("/posts/:id", updatePost)
  e.DELETE("/posts/:id", deletePost)
  
  // Start server
  e.Logger.Fatal(e.Start(":8080"))
}

func getAllPosts(c echo.Context) error {
  rows, err := db.Query("SELECT id, title, content, author_id FROM posts")
  if err != nil {
    return c.JSON(http.StatusInternalServerError, map[string]string{
      "error": "Failed to query posts",
    })
  }
  defer rows.Close()
  
  var posts []Post
  for rows.Next() {
    var post Post
    if err := rows.Scan(&post.ID, &post.Title, &post.Content, &post.AuthorID); err != nil {
      return c.JSON(http.StatusInternalServerError, map[string]string{
        "error": "Failed to scan post",
      })
    }
    posts = append(posts, post)
  }
  
  return c.JSON(http.StatusOK, posts)
}

func getPost(c echo.Context) error {
  id := c.Param("id")
  
  var post Post
  err := db.QueryRow("SELECT id, title, content, author_id FROM posts WHERE id = ?", id).
    Scan(&post.ID, &post.Title, &post.Content, &post.AuthorID)
    
  if err != nil {
    if err == sql.ErrNoRows {
      return c.JSON(http.StatusNotFound, map[string]string{
        "error": "Post not found",
      })
    }
    return c.JSON(http.StatusInternalServerError, map[string]string{
      "error": "Failed to query post",
    })
  }
  
  return c.JSON(http.StatusOK, post)
}

func createPost(c echo.Context) error {
  post := new(Post)
  if err := c.Bind(post); err != nil {
    return c.JSON(http.StatusBadRequest, map[string]string{
      "error": "Invalid request payload",
    })
  }
  
  result, err := db.Exec(
    "INSERT INTO posts (title, content, author_id) VALUES (?, ?, ?)",
    post.Title, post.Content, post.AuthorID,
  )
  if err != nil {
    return c.JSON(http.StatusInternalServerError, map[string]string{
      "error": "Failed to create post",
    })
  }
  
  id, _ := result.LastInsertId()
  post.ID = int(id)
  
  return c.JSON(http.StatusCreated, post)
}

func updatePost(c echo.Context) error {
  id := c.Param("id")
  
  post := new(Post)
  if err := c.Bind(post); err != nil {
    return c.JSON(http.StatusBadRequest, map[string]string{
      "error": "Invalid request payload",
    })
  }
  
  _, err := db.Exec(
    "UPDATE posts SET title = ?, content = ? WHERE id = ?",
    post.Title, post.Content, id,
  )
  if err != nil {
    return c.JSON(http.StatusInternalServerError, map[string]string{
      "error": "Failed to update post",
    })
  }
  
  post.ID = atoi(id)
  return c.JSON(http.StatusOK, post)
}

func deletePost(c echo.Context) error {
  id := c.Param("id")
  
  result, err := db.Exec("DELETE FROM posts WHERE id = ?", id)
  if err != nil {
    return c.JSON(http.StatusInternalServerError, map[string]string{
      "error": "Failed to delete post",
    })
  }
  
  rowsAffected, _ := result.RowsAffected()
  if rowsAffected == 0 {
    return c.JSON(http.StatusNotFound, map[string]string{
      "error": "Post not found",
    })
  }
  
  return c.NoContent(http.StatusNoContent)
}

// Helper function to convert string to int
func atoi(s string) int {
  i, _ := strconv.Atoi(s)
  return i
}

Best Practices for Query Building

Always use parameterized queries to prevent SQL injection attacks
Keep your SQL queries in one place for better maintainability
Handle database errors properly and provide meaningful error messages
Use transactions for operations that modify multiple records
Close resources like result sets and statements to prevent memory leaks
Consider using an ORM for complex applications to simplify database interactions

Summary

Echo's query building capabilities provide a powerful way to interact with databases in your web applications. By using the techniques covered in this guide, you can:

Create, read, update, and delete records in your database
Perform complex operations like joins and transactions
Build secure applications that are protected against SQL injection
Create maintainable code by using structured query building approaches

Remember that effective query building is about finding the right balance between raw SQL expressiveness and code maintainability. Echo gives you the tools to achieve this balance in your applications.

Additional Resources

Exercises

Create a simple REST API for a "products" table with basic CRUD operations
Implement pagination for a list endpoint (e.g., /posts?page=2&limit=10)
Build a search endpoint that uses multiple WHERE conditions
Implement a join query that retrieves data from three related tables
Create a transaction that performs multiple database operations

If you spot any mistakes on this website, please let me know at [email protected]. I’d greatly appreciate your feedback! :)

Introduction​

Understanding Echo's Query Builder​

Basic Query Structure​

Getting Started with Query Building​

Setting Up the Database Connection​

Basic Query Operations​

SELECT Queries​

INSERT Queries​

UPDATE Queries​

DELETE Queries​

Advanced Query Building​

Joining Tables​

Transactions​

Using Query Parameters​

Real-World Example: Building a REST API​

Best Practices for Query Building​

Summary​

Additional Resources​

Exercises​