Tutorials
Golang
Go Database Integration

Go Database Integration

Working with databases is essential for most applications. In Go, you have several excellent options for database integration. This guide will show you how to work with both SQL and NoSQL databases, using Go’s standard library and popular third-party packages.

If you’re new to Go, you might want to review Go data types and functions first.

SQL Databases with database/sql

Go’s standard library provides the database/sql package, which offers a generic interface for working with SQL databases. This package works with various SQL databases including PostgreSQL, MySQL, SQLite, and more.

Setting Up Your Project

First, create a new Go project and install the necessary database driver:

mkdir go-database-example
cd go-database-example
go mod init example.com/db-example

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

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

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

Basic Database Operations

Let’s create a simple example using SQLite (it’s easy to set up and doesn’t require a separate server):

package main

import (
	"database/sql"
	"fmt"
	"log"
	"time"

	_ "github.com/mattn/go-sqlite3"
)

type User struct {
	ID        int       `json:"id"`
	Name      string    `json:"name"`
	Email     string    `json:"email"`
	CreatedAt time.Time `json:"created_at"`
}

func main() {
	// Open database connection
	db, err := sql.Open("sqlite3", "./users.db")
	if err != nil {
		log.Fatal(err)
	}
	defer db.Close()

	// Test the connection
	err = db.Ping()
	if err != nil {
		log.Fatal(err)
	}

	fmt.Println("Connected to database successfully!")

	// Create table
	err = createUserTable(db)
	if err != nil {
		log.Fatal(err)
	}

	// Perform CRUD operations
	userID, err := insertUser(db, "John Doe", "[email protected]")
	if err != nil {
		log.Fatal(err)
	}
	fmt.Printf("Inserted user with ID: %d\n", userID)

	// Read user
	user, err := getUser(db, userID)
	if err != nil {
		log.Fatal(err)
	}
	fmt.Printf("Retrieved user: %+v\n", user)

	// Update user
	err = updateUser(db, userID, "John Smith", "[email protected]")
	if err != nil {
		log.Fatal(err)
	}
	fmt.Println("User updated successfully")

	// List all users
	users, err := getAllUsers(db)
	if err != nil {
		log.Fatal(err)
	}
	fmt.Printf("All users: %+v\n", users)

	// Delete user
	err = deleteUser(db, userID)
	if err != nil {
		log.Fatal(err)
	}
	fmt.Println("User deleted successfully")
}

func createUserTable(db *sql.DB) error {
	query := `
	CREATE TABLE IF NOT EXISTS users (
		id INTEGER PRIMARY KEY AUTOINCREMENT,
		name TEXT NOT NULL,
		email TEXT NOT NULL UNIQUE,
		created_at DATETIME DEFAULT CURRENT_TIMESTAMP
	)`

	_, err := db.Exec(query)
	return err
}

func insertUser(db *sql.DB, name, email string) (int64, error) {
	query := `INSERT INTO users (name, email) VALUES (?, ?)`
	result, err := db.Exec(query, name, email)
	if err != nil {
		return 0, err
	}
	return result.LastInsertId()
}

func getUser(db *sql.DB, id int) (*User, error) {
	query := `SELECT id, name, email, created_at FROM users WHERE id = ?`
	row := db.QueryRow(query, id)

	var user User
	err := row.Scan(&user.ID, &user.Name, &user.Email, &user.CreatedAt)
	if err != nil {
		return nil, err
	}
	return &user, nil
}

func getAllUsers(db *sql.DB) ([]User, error) {
	query := `SELECT id, name, email, created_at FROM users ORDER BY created_at DESC`
	rows, err := db.Query(query)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var users []User
	for rows.Next() {
		var user User
		err := rows.Scan(&user.ID, &user.Name, &user.Email, &user.CreatedAt)
		if err != nil {
			return nil, err
		}
		users = append(users, user)
	}
	return users, nil
}

func updateUser(db *sql.DB, id int, name, email string) error {
	query := `UPDATE users SET name = ?, email = ? WHERE id = ?`
	_, err := db.Exec(query, name, email, id)
	return err
}

func deleteUser(db *sql.DB, id int) error {
	query := `DELETE FROM users WHERE id = ?`
	_, err := db.Exec(query, id)
	return err
}

Using GORM for Object-Relational Mapping

GORM is a popular ORM library for Go that makes database operations more convenient and provides additional features like associations, hooks, and automatic migrations.

Installing GORM 

go get gorm.io/gorm
go get gorm.io/driver/sqlite  # or gorm.io/driver/postgres, gorm.io/driver/mysql

GORM Example 

package main

import (
	"fmt"
	"log"
	"time"

	"gorm.io/driver/sqlite"
	"gorm.io/gorm"
)

type User struct {
	ID        uint           `gorm:"primaryKey" json:"id"`
	Name      string         `gorm:"not null" json:"name"`
	Email     string         `gorm:"uniqueIndex;not null" json:"email"`
	Posts     []Post         `gorm:"foreignKey:UserID" json:"posts,omitempty"`
	CreatedAt time.Time      `json:"created_at"`
	UpdatedAt time.Time      `json:"updated_at"`
	DeletedAt gorm.DeletedAt `gorm:"index" json:"-"`
}

type Post struct {
	ID        uint      `gorm:"primaryKey" json:"id"`
	Title     string    `gorm:"not null" json:"title"`
	Content   string    `json:"content"`
	UserID    uint      `json:"user_id"`
	User      User      `gorm:"foreignKey:UserID" json:"user,omitempty"`
	CreatedAt time.Time `json:"created_at"`
	UpdatedAt time.Time `json:"updated_at"`
}

func main() {
	// Connect to database
	db, err := gorm.Open(sqlite.Open("gorm.db"), &gorm.Config{})
	if err != nil {
		log.Fatal("Failed to connect to database:", err)
	}

	// Auto migrate schema
	err = db.AutoMigrate(&User{}, &Post{})
	if err != nil {
		log.Fatal("Failed to migrate:", err)
	}

	fmt.Println("Database connected and migrated successfully!")

	// Create operations
	user := User{
		Name:  "Alice Johnson",
		Email: "[email protected]",
		Posts: []Post{
			{Title: "First Post", Content: "This is my first post"},
			{Title: "Second Post", Content: "Learning GORM is fun"},
		},
	}

	result := db.Create(&user)
	if result.Error != nil {
		log.Fatal("Failed to create user:", result.Error)
	}
	fmt.Printf("Created user with ID: %d\n", user.ID)

	// Read operations
	var retrievedUser User
	db.Preload("Posts").First(&retrievedUser, user.ID)
	fmt.Printf("Retrieved user: %+v\n", retrievedUser)
	fmt.Printf("User posts: %+v\n", retrievedUser.Posts)

	// Query with conditions
	var users []User
	db.Where("name LIKE ?", "%Alice%").Or("email LIKE ?", "%example%").Find(&users)
	fmt.Printf("Found %d users matching criteria\n", len(users))

	// Pagination
	var paginatedUsers []User
	db.Offset(0).Limit(10).Find(&paginatedUsers)
	fmt.Printf("First 10 users: %+v\n", paginatedUsers)

	// Update operations
	db.Model(&user).Update("name", "Alice Smith")
	fmt.Println("Updated user name")

	// Delete operations (soft delete)
	db.Delete(&user)
	fmt.Println("Soft deleted user")

	// Find soft deleted records
	var deletedUsers []User
	db.Unscoped().Where("deleted_at IS NOT NULL").Find(&deletedUsers)
	fmt.Printf("Found %d soft deleted users\n", len(deletedUsers))
}

Working with MongoDB

For NoSQL databases like MongoDB, you can use the official MongoDB Go driver.

Installing MongoDB Driver 

go get go.mongodb.org/mongo-driver/mongo
go get go.mongodb.org/mongo-driver/mongo/options
go get go.mongodb.org/mongo-driver/bson

MongoDB Example 

package main

import (
	"context"
	"fmt"
	"log"
	"time"

	"go.mongodb.org/mongo-driver/bson"
	"go.mongodb.org/mongo-driver/bson/primitive"
	"go.mongodb.org/mongo-driver/mongo"
	"go.mongodb.org/mongo-driver/mongo/options"
)

type User struct {
	ID        primitive.ObjectID `bson:"_id,omitempty" json:"id"`
	Name      string             `bson:"name" json:"name"`
	Email     string             `bson:"email" json:"email"`
	Age       int                `bson:"age,omitempty" json:"age,omitempty"`
	CreatedAt time.Time          `bson:"created_at" json:"created_at"`
	UpdatedAt time.Time          `bson:"updated_at" json:"updated_at"`
}

func main() {
	// Connect to MongoDB
	client, err := mongo.Connect(context.TODO(), options.Client().ApplyURI("mongodb://localhost:27017"))
	if err != nil {
		log.Fatal(err)
	}
	defer client.Disconnect(context.TODO())

	// Check the connection
	err = client.Ping(context.TODO(), nil)
	if err != nil {
		log.Fatal(err)
	}

	fmt.Println("Connected to MongoDB!")

	// Get collection
	collection := client.Database("testdb").Collection("users")

	// Create user
	user := User{
		Name:      "Bob Wilson",
		Email:     "[email protected]",
		Age:       30,
		CreatedAt: time.Now(),
		UpdatedAt: time.Now(),
	}

	result, err := collection.InsertOne(context.TODO(), user)
	if err != nil {
		log.Fatal(err)
	}
	fmt.Printf("Inserted user with ID: %s\n", result.InsertedID)

	// Read user
	var retrievedUser User
	err = collection.FindOne(context.TODO(), bson.M{"_id": result.InsertedID}).Decode(&retrievedUser)
	if err != nil {
		log.Fatal(err)
	}
	fmt.Printf("Retrieved user: %+v\n", retrievedUser)

	// Update user
	update := bson.M{
		"$set": bson.M{
			"name":       "Bob Smith",
			"age":        31,
			"updated_at": time.Now(),
		},
	}

	_, err = collection.UpdateOne(context.TODO(), bson.M{"_id": result.InsertedID}, update)
	if err != nil {
		log.Fatal(err)
	}
	fmt.Println("Updated user successfully")

	// Query multiple users
	cursor, err := collection.Find(context.TODO(), bson.M{"age": bson.M{"$gte": 25}})
	if err != nil {
		log.Fatal(err)
	}
	defer cursor.Close(context.TODO())

	var users []User
	for cursor.Next(context.TODO()) {
		var user User
		err := cursor.Decode(&user)
		if err != nil {
			log.Fatal(err)
		}
		users = append(users, user)
	}
	fmt.Printf("Found %d users age 25 or older\n", len(users))

	// Delete user
	_, err = collection.DeleteOne(context.TODO(), bson.M{"_id": result.InsertedID})
	if err != nil {
		log.Fatal(err)
	}
	fmt.Println("Deleted user successfully")
}

Database Connection Pooling

Go’s database/sql package automatically manages connection pooling. Here’s how to configure it properly:

package main

import (
	"database/sql"
	"fmt"
	"log"
	"time"

	_ "github.com/lib/pq" // PostgreSQL driver
)

func setupDatabase() (*sql.DB, error) {
	// Connection string
	connStr := "postgres://user:password@localhost/dbname?sslmode=disable"
	
	// Open database
	db, err := sql.Open("postgres", connStr)
	if err != nil {
		return nil, err
	}

	// Configure connection pool
	db.SetMaxOpenConns(25)                 // Maximum number of open connections
	db.SetMaxIdleConns(25)                 // Maximum number of idle connections
	db.SetConnMaxLifetime(5 * time.Minute) // Maximum time a connection can be reused
	db.SetConnMaxIdleTime(5 * time.Minute) // Maximum time a connection can be idle

	// Test connection
	ctx, cancel := context.WithTimeout(context.Background(), 5*time.Second)
	defer cancel()
	
	err = db.PingContext(ctx)
	if err != nil {
		return nil, err
	}

	return db, nil
}

Error Handling Best Practices

Proper error handling is crucial for database operations:

func safeUserOperations(db *sql.DB) {
	// Handle specific error types
	userID := 1
	user, err := getUser(db, userID)
	if err != nil {
		if err == sql.ErrNoRows {
			fmt.Printf("User with ID %d not found\n", userID)
		} else {
			log.Printf("Error retrieving user: %v\n", err)
		}
		return
	}

	fmt.Printf("Found user: %v\n", user)

	// Use transactions for related operations
	err = transferFunds(db, 1, 2, 100.00)
	if err != nil {
		log.Printf("Transfer failed: %v\n", err)
		// Consider retrying or notifying user
	}
}

func transferFunds(db *sql.DB, fromID, toID int, amount float64) error {
	tx, err := db.Begin()
	if err != nil {
		return fmt.Errorf("failed to begin transaction: %w", err)
	}
	defer tx.Rollback()

	// Debit from account
	_, err = tx.Exec("UPDATE accounts SET balance = balance - ? WHERE id = ?", amount, fromID)
	if err != nil {
		return fmt.Errorf("failed to debit account: %w", err)
	}

	// Credit to account
	_, err = tx.Exec("UPDATE accounts SET balance = balance + ? WHERE id = ?", amount, toID)
	if err != nil {
		return fmt.Errorf("failed to credit account: %w", err)
	}

	// Commit transaction
	if err = tx.Commit(); err != nil {
		return fmt.Errorf("failed to commit transaction: %w", err)
	}

	return nil
}

Performance Tips

  1. Use prepared statements: They’re faster and more secure
  2. Select only needed columns: Avoid SELECT *
  3. Use connection pooling: Configure appropriate pool sizes
  4. Batch operations: Use bulk inserts/updates when possible
  5. Index properly: Add database indexes for frequently queried columns
  6. Avoid N+1 queries: Use joins or proper queries with relationships

Choosing the Right Approach

  • Use database/sql: For simple applications, full control over SQL, or when you prefer writing raw SQL
  • Use GORM: For rapid development, complex applications with many models, or when you want ORM features
  • Use NoSQL: For unstructured data, high write throughput, or when you need flexible schemas

For more Go development topics, check out our guide on Go web servers or learn about Go environment variables.

The official Go database documentation is comprehensive, and the GORM documentation provides excellent examples for working with the ORM. For MongoDB users, the MongoDB Go driver documentation is the best resource.

Last updated on
Environment Variables in Go