Echo SQL Connection

Introduction

Database connectivity is a crucial component of most web applications. In an Echo framework application, connecting to SQL databases requires understanding several concepts including connection pooling, driver selection, and secure configuration management. This guide will help beginners establish database connections in their Echo applications and implement best practices for managing these connections.

Echo doesn't include built-in database-specific functionality; instead, it works smoothly with Go's standard database/sql package and various database drivers. This flexibility allows you to connect to almost any SQL database while maintaining consistent connection patterns.

Prerequisites

Before establishing a database connection in your Echo application, ensure you have:

1. Go installed on your development machine
2. Echo framework installed (go get github.com/labstack/echo/v4)
3. Access to a SQL database (MySQL, PostgreSQL, SQLite, etc.)
4. The appropriate database driver installed

Basic Database Connection

Installing Database Drivers

First, you need to install the appropriate driver for your database:

bash

# For MySQL
go get github.com/go-sql-driver/mysql

# For PostgreSQL
go get github.com/lib/pq

# For SQLite
go get github.com/mattn/go-sqlite3

Establishing a Connection

The following example demonstrates how to connect to a MySQL database in an Echo application:

go

package main

import (
	"database/sql"
	"fmt"
	"log"
	"net/http"

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

// Global DB variable
var db *sql.DB

func main() {
	// Initialize database connection
	initDB()
	defer db.Close()

	// Create a new Echo instance
	e := echo.New()

	// Routes
	e.GET("/users", getUsers)

	// Start server
	e.Start(":8080")
}

// Initialize database connection
func initDB() {
	var err error
	
	// [username]:[password]@tcp([host]:[port])/[database_name]
	dataSourceName := "user:password@tcp(127.0.0.1:3306)/mydatabase"
	
	db, err = sql.Open("mysql", dataSourceName)
	if err != nil {
		log.Fatal(err)
	}

	// Verify connection
	if err = db.Ping(); err != nil {
		log.Fatal(err)
	}
	
	fmt.Println("Database connection established")
}

// Handler to get users
func getUsers(c echo.Context) error {
	rows, err := db.Query("SELECT id, name, email FROM users")
	if err != nil {
		return c.String(http.StatusInternalServerError, "Database error")
	}
	defer rows.Close()
	
	var users []map[string]interface{}
	
	for rows.Next() {
		var id int
		var name, email string
		
		err = rows.Scan(&id, &name, &email)
		if err != nil {
			return c.String(http.StatusInternalServerError, "Scan error")
		}
		
		user := map[string]interface{}{
			"id": id,
			"name": name,
			"email": email,
		}
		
		users = append(users, user)
	}
	
	return c.JSON(http.StatusOK, users)
}

Connection Pooling and Configuration

The standard database/sql package in Go automatically implements connection pooling. However, you can configure various parameters to optimize your database connections:

go

func initDB() {
	var err error
	
	dataSourceName := "user:password@tcp(127.0.0.1:3306)/mydatabase"
	
	db, err = sql.Open("mysql", dataSourceName)
	if err != nil {
		log.Fatal(err)
	}
	
	// Set maximum number of connections
	db.SetMaxOpenConns(25)
	
	// Set maximum number of idle connections
	db.SetMaxIdleConns(5)
	
	// Set maximum lifetime of connections
	db.SetConnMaxLifetime(5 * time.Minute)
	
	// Verify connection
	if err = db.Ping(); err != nil {
		log.Fatal(err)
	}
	
	fmt.Println("Database connection established with pool configuration")
}

PostgreSQL Connection Example

Here's how to connect to a PostgreSQL database:

go

package main

import (
	"database/sql"
	"fmt"
	"log"
	"net/http"

	_ "github.com/lib/pq"
	"github.com/labstack/echo/v4"
)

var db *sql.DB

func main() {
	// Initialize database connection
	initDB()
	defer db.Close()

	// Create a new Echo instance
	e := echo.New()

	// Routes
	e.GET("/products", getProducts)

	// Start server
	e.Start(":8080")
}

func initDB() {
	var err error
	
	// Connection string for PostgreSQL
	connStr := "host=localhost port=5432 user=postgres password=password dbname=store sslmode=disable"
	
	db, err = sql.Open("postgres", connStr)
	if err != nil {
		log.Fatal(err)
	}

	if err = db.Ping(); err != nil {
		log.Fatal(err)
	}
	
	fmt.Println("PostgreSQL connection established")
}

func getProducts(c echo.Context) error {
	rows, err := db.Query("SELECT id, name, price FROM products")
	if err != nil {
		return c.String(http.StatusInternalServerError, "Database error")
	}
	defer rows.Close()
	
	var products []map[string]interface{}
	
	for rows.Next() {
		var id int
		var name string
		var price float64
		
		err = rows.Scan(&id, &name, &price)
		if err != nil {
			return c.String(http.StatusInternalServerError, "Scan error")
		}
		
		product := map[string]interface{}{
			"id": id,
			"name": name,
			"price": price,
		}
		
		products = append(products, product)
	}
	
	return c.JSON(http.StatusOK, products)
}

Environment Variables for Connection Security

Hardcoding database credentials in your application is not secure. Instead, use environment variables:

go

package main

import (
	"database/sql"
	"fmt"
	"log"
	"os"
	
	_ "github.com/go-sql-driver/mysql"
	"github.com/labstack/echo/v4"
)

var db *sql.DB

func initDB() {
	var err error
	
	// Get database configuration from environment variables
	dbUser := os.Getenv("DB_USER")
	dbPass := os.Getenv("DB_PASS")
	dbHost := os.Getenv("DB_HOST")
	dbName := os.Getenv("DB_NAME")
	
	if dbUser == "" || dbPass == "" || dbHost == "" || dbName == "" {
		log.Fatal("Database environment variables are not set")
	}
	
	// Create connection string
	dataSourceName := fmt.Sprintf("%s:%s@tcp(%s)/%s", dbUser, dbPass, dbHost, dbName)
	
	db, err = sql.Open("mysql", dataSourceName)
	if err != nil {
		log.Fatal(err)
	}
	
	if err = db.Ping(); err != nil {
		log.Fatal(err)
	}
	
	fmt.Println("Database connection established using environment variables")
}

To use this approach, set your environment variables before running your application:

bash

export DB_USER=myuser
export DB_PASS=mypassword
export DB_HOST=localhost:3306
export DB_NAME=mydatabase

# Then run your application
go run main.go

Creating a Database Middleware

You can create middleware to make the database instance available to all your handlers:

go

package main

import (
	"database/sql"
	"log"
	"net/http"

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

type DBContext struct {
	echo.Context
	DB *sql.DB
}

func main() {
	// Initialize database
	db, err := sql.Open("mysql", "user:password@tcp(127.0.0.1:3306)/mydatabase")
	if err != nil {
		log.Fatal(err)
	}
	defer db.Close()

	if err = db.Ping(); err != nil {
		log.Fatal(err)
	}

	e := echo.New()

	// Database middleware
	e.Use(func(next echo.HandlerFunc) echo.HandlerFunc {
		return func(c echo.Context) error {
			dbContext := &DBContext{
				Context: c,
				DB:      db,
			}
			return next(dbContext)
		}
	})

	// Routes
	e.GET("/users", getUsersHandler)

	e.Start(":8080")
}

func getUsersHandler(c echo.Context) error {
	// Get database from context
	db := c.(*DBContext).DB
	
	// Use the db connection
	rows, err := db.Query("SELECT id, name FROM users")
	if err != nil {
		return c.String(http.StatusInternalServerError, "Database error")
	}
	defer rows.Close()
	
	// Process rows...
	
	return c.String(http.StatusOK, "Users retrieved successfully")
}

Common Database Operations

Insert Operation

go

func createUser(c echo.Context) error {
	// Parse request
	u := new(User)
	if err := c.Bind(u); err != nil {
		return c.String(http.StatusBadRequest, "Invalid request")
	}
	
	// Insert user
	query := "INSERT INTO users (name, email) VALUES (?, ?)"
	result, err := db.Exec(query, u.Name, u.Email)
	if err != nil {
		return c.String(http.StatusInternalServerError, "Failed to create user")
	}
	
	// Get the inserted ID
	id, err := result.LastInsertId()
	if err != nil {
		return c.String(http.StatusInternalServerError, "Failed to get ID")
	}
	
	// Return the created user with ID
	u.ID = id
	return c.JSON(http.StatusCreated, u)
}

Update Operation

go

func updateUser(c echo.Context) error {
	id, _ := strconv.ParseInt(c.Param("id"), 10, 64)
	
	// Parse request
	u := new(User)
	if err := c.Bind(u); err != nil {
		return c.String(http.StatusBadRequest, "Invalid request")
	}
	
	// Update user
	query := "UPDATE users SET name = ?, email = ? WHERE id = ?"
	_, err := db.Exec(query, u.Name, u.Email, id)
	if err != nil {
		return c.String(http.StatusInternalServerError, "Failed to update user")
	}
	
	return c.JSON(http.StatusOK, u)
}

Delete Operation

go

func deleteUser(c echo.Context) error {
	id, _ := strconv.ParseInt(c.Param("id"), 10, 64)
	
	// Delete user
	query := "DELETE FROM users WHERE id = ?"
	result, err := db.Exec(query, id)
	if err != nil {
		return c.String(http.StatusInternalServerError, "Failed to delete user")
	}
	
	// Check if any row was affected
	rows, err := result.RowsAffected()
	if err != nil {
		return c.String(http.StatusInternalServerError, "Failed to get affected rows")
	}
	
	if rows == 0 {
		return c.String(http.StatusNotFound, "User not found")
	}
	
	return c.NoContent(http.StatusNoContent)
}

Error Handling Best Practices

When working with database connections, proper error handling is crucial:

go

func getUser(c echo.Context) error {
	id, _ := strconv.ParseInt(c.Param("id"), 10, 64)
	
	var user User
	err := db.QueryRow("SELECT id, name, email FROM users WHERE id = ?", id).
		Scan(&user.ID, &user.Name, &user.Email)
	
	switch {
	case err == sql.ErrNoRows:
		return c.JSON(http.StatusNotFound, map[string]string{
			"message": "User not found",
		})
	case err != nil:
		log.Printf("Database error: %v", err)
		return c.JSON(http.StatusInternalServerError, map[string]string{
			"message": "Database error occurred",
		})
	default:
		return c.JSON(http.StatusOK, user)
	}
}

Using ORM with Echo

While the standard database/sql package works well, many developers prefer using ORMs for more complex applications. GORM is a popular choice:

go

package main

import (
	"log"
	"net/http"

	"github.com/labstack/echo/v4"
	"gorm.io/driver/mysql"
	"gorm.io/gorm"
)

// User model
type User struct {
	gorm.Model
	Name  string `json:"name"`
	Email string `json:"email" gorm:"unique"`
}

var db *gorm.DB

func main() {
	// Initialize database
	initDB()

	// Initialize Echo
	e := echo.New()
	
	// Routes
	e.GET("/users", getUsers)
	e.POST("/users", createUser)
	
	e.Start(":8080")
}

func initDB() {
	var err error
	dsn := "user:password@tcp(localhost:3306)/mydatabase?charset=utf8mb4&parseTime=True&loc=Local"
	db, err = gorm.Open(mysql.Open(dsn), &gorm.Config{})
	if err != nil {
		log.Fatal("Failed to connect to database:", err)
	}
	
	// Auto migrate schema
	db.AutoMigrate(&User{})
}

func getUsers(c echo.Context) error {
	var users []User
	if err := db.Find(&users).Error; err != nil {
		return c.JSON(http.StatusInternalServerError, map[string]string{
			"error": err.Error(),
		})
	}
	return c.JSON(http.StatusOK, users)
}

func createUser(c echo.Context) error {
	user := new(User)
	if err := c.Bind(user); err != nil {
		return c.JSON(http.StatusBadRequest, map[string]string{
			"error": "Invalid request",
		})
	}
	
	if err := db.Create(&user).Error; err != nil {
		return c.JSON(http.StatusInternalServerError, map[string]string{
			"error": err.Error(),
		})
	}
	
	return c.JSON(http.StatusCreated, user)
}

Advanced: Using a Database Transaction

For operations that require multiple database changes to succeed or fail together, use transactions:

go

func transferFunds(c echo.Context) error {
	// Parse request
	var req struct {
		FromAccountID int64   `json:"from_account_id"`
		ToAccountID   int64   `json:"to_account_id"`
		Amount        float64 `json:"amount"`
	}
	
	if err := c.Bind(&req); err != nil {
		return c.JSON(http.StatusBadRequest, map[string]string{"error": "Invalid request"})
	}
	
	// Begin transaction
	tx, err := db.Begin()
	if err != nil {
		return c.JSON(http.StatusInternalServerError, map[string]string{"error": "Failed to begin transaction"})
	}
	
	// Deduct from source account
	_, err = tx.Exec("UPDATE accounts SET balance = balance - ? WHERE id = ?", req.Amount, req.FromAccountID)
	if err != nil {
		tx.Rollback()
		return c.JSON(http.StatusInternalServerError, map[string]string{"error": "Failed to deduct from source account"})
	}
	
	// Add to destination account
	_, err = tx.Exec("UPDATE accounts SET balance = balance + ? WHERE id = ?", req.Amount, req.ToAccountID)
	if err != nil {
		tx.Rollback()
		return c.JSON(http.StatusInternalServerError, map[string]string{"error": "Failed to add to 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"})
}

Summary

In this guide, you've learned how to establish and manage SQL database connections in Echo applications. We covered:

• Basic database connection setup with the standard database/sql package
• Connection pooling and configuration
• Examples for MySQL and PostgreSQL connections
• Using environment variables for secure configuration
• Creating database middleware for Echo
• Common CRUD operations
• Error handling best practices
• Using the GORM ORM with Echo
• Working with database transactions

Database connectivity is fundamental to most web applications, and Echo's flexibility allows you to implement connections using standard Go patterns regardless of your database choice.

Exercises

1. Create an Echo application that connects to SQLite and implements a simple REST API for managing a to-do list.
2. Implement proper connection pooling and error handling in an Echo application connecting to MySQL.
3. Create a middleware that logs all SQL queries executed in your application.
4. Implement a REST API using Echo and GORM with relationships between multiple models.
5. Create a function that handles database migrations when your Echo application starts.

Additional Resources

• Go database/sql package documentation
• GORM Documentation
• MySQL Driver Documentation
• PostgreSQL Driver Documentation
• SQLite Driver Documentation