Database Fundamentals with Go

Introduction

Go’s database/sql package provides a standard interface for working with SQL databases. This guide covers connections, queries, transactions, and best practices for database operations.

Core Concepts

Database/SQL Package

The database/sql package provides:

Connection pooling
Prepared statements
Query execution
Transaction support
Error handling

Key Components

sql.DB: Connection pool
sql.Rows: Query results
sql.Row: Single row result
sql.Tx: Transaction

Good: Database Connections

Opening Connections

package main

import (
	"database/sql"
	_ "github.com/lib/pq"
	"fmt"
)

// ✅ GOOD: Open database connection
func openDatabase() (*sql.DB, error) {
	dsn := "user=postgres password=password dbname=mydb sslmode=disable"
	db, err := sql.Open("postgres", dsn)
	if err != nil {
		return nil, err
	}

	// Test connection
	if err := db.Ping(); err != nil {
		return nil, err
	}

	// Configure connection pool
	db.SetMaxOpenConns(25)
	db.SetMaxIdleConns(5)
	db.SetConnMaxLifetime(5 * time.Minute)

	return db, nil
}

// ✅ GOOD: Close connection
func closeDatabase(db *sql.DB) error {
	return db.Close()
}

// ✅ GOOD: Connection pooling
func main() {
	db, _ := openDatabase()
	defer db.Close()

	// Connections are automatically pooled
	// Multiple goroutines can use the same db
}

Good: Query Operations

Executing Queries

package main

import (
	"database/sql"
	"fmt"
)

// ✅ GOOD: Query single row
func getUser(db *sql.DB, id int) (string, error) {
	var name string
	err := db.QueryRow("SELECT name FROM users WHERE id = ?", id).Scan(&name)
	if err != nil {
		return "", err
	}
	return name, nil
}

// ✅ GOOD: Query multiple rows
func getAllUsers(db *sql.DB) ([]string, error) {
	rows, err := db.Query("SELECT name FROM users")
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var names []string
	for rows.Next() {
		var name string
		if err := rows.Scan(&name); err != nil {
			return nil, err
		}
		names = append(names, name)
	}

	return names, rows.Err()
}

// ✅ GOOD: Query with conditions
func findUsersByAge(db *sql.DB, minAge int) ([]string, error) {
	rows, err := db.Query("SELECT name FROM users WHERE age > ?", minAge)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var names []string
	for rows.Next() {
		var name string
		rows.Scan(&name)
		names = append(names, name)
	}

	return names, nil
}

// ✅ GOOD: Scan into struct
type User struct {
	ID    int
	Name  string
	Email string
	Age   int
}

func getUserStruct(db *sql.DB, id int) (*User, error) {
	user := &User{}
	err := db.QueryRow(
		"SELECT id, name, email, age FROM users WHERE id = ?",
		id,
	).Scan(&user.ID, &user.Name, &user.Email, &user.Age)

	if err != nil {
		return nil, err
	}
	return user, nil
}

Good: Prepared Statements

Using Prepared Statements

package main

import (
	"database/sql"
)

// ✅ GOOD: Prepared statement
func createUserWithPrepared(db *sql.DB, name, email string) error {
	stmt, err := db.Prepare("INSERT INTO users (name, email) VALUES (?, ?)")
	if err != nil {
		return err
	}
	defer stmt.Close()

	_, err = stmt.Exec(name, email)
	return err
}

// ✅ GOOD: Reuse prepared statement
func insertMultipleUsers(db *sql.DB, users []User) error {
	stmt, err := db.Prepare("INSERT INTO users (name, email) VALUES (?, ?)")
	if err != nil {
		return err
	}
	defer stmt.Close()

	for _, user := range users {
		if _, err := stmt.Exec(user.Name, user.Email); err != nil {
			return err
		}
	}

	return nil
}

// ✅ GOOD: Query with prepared statement
func getUserWithPrepared(db *sql.DB, id int) (*User, error) {
	stmt, err := db.Prepare("SELECT id, name, email, age FROM users WHERE id = ?")
	if err != nil {
		return nil, err
	}
	defer stmt.Close()

	user := &User{}
	err = stmt.QueryRow(id).Scan(&user.ID, &user.Name, &user.Email, &user.Age)
	if err != nil {
		return nil, err
	}

	return user, nil
}

Good: Transactions

Transaction Handling

package main

import (
	"database/sql"
)

// ✅ GOOD: Basic transaction
func transferFunds(db *sql.DB, fromID, toID int, amount float64) error {
	tx, err := db.Begin()
	if err != nil {
		return err
	}

	// Deduct from source
	_, err = tx.Exec("UPDATE accounts SET balance = balance - ? WHERE id = ?", amount, fromID)
	if err != nil {
		tx.Rollback()
		return err
	}

	// Add to destination
	_, err = tx.Exec("UPDATE accounts SET balance = balance + ? WHERE id = ?", amount, toID)
	if err != nil {
		tx.Rollback()
		return err
	}

	return tx.Commit().Error
}

// ✅ GOOD: Transaction with defer
func createUserWithTransaction(db *sql.DB, user *User) error {
	tx, err := db.Begin()
	if err != nil {
		return err
	}
	defer func() {
		if err != nil {
			tx.Rollback()
		}
	}()

	// Insert user
	result, err := tx.Exec(
		"INSERT INTO users (name, email, age) VALUES (?, ?, ?)",
		user.Name, user.Email, user.Age,
	)
	if err != nil {
		return err
	}

	// Get inserted ID
	id, err := result.LastInsertId()
	if err != nil {
		return err
	}

	user.ID = int(id)
	return tx.Commit().Error
}

Advanced Patterns

Connection Pooling

package main

import (
	"database/sql"
	"time"
)

// ✅ GOOD: Configure connection pool
func configureConnectionPool(db *sql.DB) {
	// Maximum number of open connections
	db.SetMaxOpenConns(25)

	// Maximum number of idle connections
	db.SetMaxIdleConns(5)

	// Maximum lifetime of a connection
	db.SetConnMaxLifetime(5 * time.Minute)

	// Maximum idle time before closing
	db.SetConnMaxIdleTime(10 * time.Minute)
}

// ✅ GOOD: Monitor connection pool
func monitorConnectionPool(db *sql.DB) {
	stats := db.Stats()
	fmt.Printf("Open connections: %d\n", stats.OpenConnections)
	fmt.Printf("In use: %d\n", stats.InUse)
	fmt.Printf("Idle: %d\n", stats.Idle)
}

Error Handling

package main

import (
	"database/sql"
	"errors"
)

// ✅ GOOD: Handle specific errors
func getUserSafe(db *sql.DB, id int) (*User, error) {
	user := &User{}
	err := db.QueryRow("SELECT id, name, email, age FROM users WHERE id = ?", id).
		Scan(&user.ID, &user.Name, &user.Email, &user.Age)

	if err == sql.ErrNoRows {
		return nil, errors.New("user not found")
	}
	if err != nil {
		return nil, err
	}

	return user, nil
}

Best Practices

1. Always Use Prepared Statements

// ✅ GOOD: Parameterized query
db.QueryRow("SELECT * FROM users WHERE id = ?", id)

// ❌ BAD: String concatenation (SQL injection!)
db.QueryRow("SELECT * FROM users WHERE id = " + strconv.Itoa(id))

2. Close Resources

// ✅ GOOD: Defer close
rows, _ := db.Query("SELECT * FROM users")
defer rows.Close()

// ❌ BAD: Forget to close
rows, _ := db.Query("SELECT * FROM users")

// ✅ GOOD: Transaction
tx, _ := db.Begin()
defer tx.Rollback()
// Multiple operations
tx.Commit()

// ❌ BAD: No transaction
db.Exec("INSERT ...")
db.Exec("UPDATE ...")

Resources

database/sql Package: https://pkg.go.dev/database/sql
SQL Drivers: https://github.com/golang/go/wiki/SQLDrivers
Best Practices: https://golang.org/doc/database/sql-injection

Summary

Go’s database/sql package provides a robust interface for database operations. Always use prepared statements to prevent SQL injection, close resources properly, and use transactions for related operations. Proper connection pooling and error handling are essential for production applications.