Database Drivers and SQL Queries in Go

In this chapter, we'll delve into the world of database drivers and SQL queries in the Go programming language. We'll start from the basics, covering how to connect to databases, perform basic CRUD (Create, Read, Update, Delete) operations, and advance towards more complex queries and optimizations. By the end of this chapter, you'll have a solid understanding of how to work with databases in Go, from setting up connections to executing advanced SQL queries efficiently.

Introduction to Database Drivers in Go

What are Database Drivers?

Database drivers in Go are libraries that provide an interface to interact with various database systems. These drivers enable Go programs to communicate with databases, execute queries, and retrieve data.

Popular Database Drivers in Go

database/sql: Standard database package in Go, providing a generic interface for working with SQL databases.
Specific database drivers like github.com/go-sql-driver/mysql, github.com/lib/pq for PostgreSQL, github.com/mattn/go-sqlite3 for SQLite, etc

Setting Up a Database Connection

				
					import (
    "database/sql"
    _ "github.com/go-sql-driver/mysql"
)

func main() {
    // Open a connection to the database
    db, err := sql.Open("mysql", "user:password@tcp(localhost:3306)/database_name")
    if err != nil {
        panic(err)
    }
    defer db.Close()

    // Ping the database to ensure the connection is alive
    err = db.Ping()
    if err != nil {
        panic(err)
    }

    fmt.Println("Connected to the database!")
}

sql.Open() establishes a connection to the database.
defer db.Close() ensures the database connection is closed when the function exits.
db.Ping() checks if the connection to the database is successful.

Basic SQL Operations in Go

Executing SQL Queries

				
					// Insert Operation
func insertUser(db *sql.DB, name string, age int) error {
    _, err := db.Exec("INSERT INTO users (name, age) VALUES (?, ?)", name, age)
    return err
}

// Select Operation
func getUsers(db *sql.DB) ([]User, error) {
    rows, err := db.Query("SELECT name, age FROM users")
    if err != nil {
        return nil, err
    }
    defer rows.Close()

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

db.Exec() executes an SQL statement without returning any rows.
db.Query() executes an SQL query that returns rows.
rows.Scan() reads the values from the current row into variables.

Update and Delete Operations

				
					// Update Operation
func updateUserAge(db *sql.DB, name string, newAge int) error {
    _, err := db.Exec("UPDATE users SET age = ? WHERE name = ?", newAge, name)
    return err
}

// Delete Operation
func deleteUser(db *sql.DB, name string) error {
    _, err := db.Exec("DELETE FROM users WHERE name = ?", name)
    return err
}

UPDATE statement is used to modify existing records.
DELETE statement is used to remove records from the table.

Advanced SQL Queries and Optimizations

Prepared Statements

Prepared statements help in executing the same SQL repeatedly with high efficiency by precompiling it.

				
					func preparedInsert(db *sql.DB, name string, age int) error {
    stmt, err := db.Prepare("INSERT INTO users (name, age) VALUES (?, ?)")
    if err != nil {
        return err
    }
    defer stmt.Close()

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

db.Prepare() prepares the SQL statement for execution.

Transactions

Transactions ensure that a group of operations are performed atomically.

				
					func transferFunds(db *sql.DB, sender, receiver string, amount int) error {
    tx, err := db.Begin()
    if err != nil {
        return err
    }
    defer func() {
        if err != nil {
            tx.Rollback()
            return
        }
        err = tx.Commit()
    }()

    // Debit from sender
    _, err = tx.Exec("UPDATE accounts SET balance = balance - ? WHERE name = ?", amount, sender)
    if err != nil {
        return err
    }

    // Credit to receiver
    _, err = tx.Exec("UPDATE accounts SET balance = balance + ? WHERE name = ?", amount, receiver)
    if err != nil {
        return err
    }

    return nil
}

db.Begin() starts a transaction.
tx.Commit() commits the transaction, making its changes permanent.

In this chapter, we've covered the fundamentals of working with databases in Go. From setting up database connections to executing basic and advanced SQL queries, you've learned how to interact with databases efficiently. Remember to handle errors appropriately and consider optimizations like prepared statements and transactions for better performance and data integrity in your Go applications. Happy coding !❤️