Database with Go

Most real-world applications need to store and retrieve data reliably. Go provides powerful, efficient tools for working with databases while keeping performance and safety in mind.

In this lesson, you will learn how Go connects to databases, executes queries, handles real data, and follows production best practices.

How Go Works with Databases

Go interacts with databases using the built-in database/sql package. This package provides a generic interface that works with many databases (MySQL, PostgreSQL, SQLite, SQL Server).

Database-specific behavior is handled by drivers.

Installing a Database Driver

Let’s use MySQL as an example. First, install the driver.

go get -u github.com/go-sql-driver/mysql

The dependency is automatically added to go.mod.

Importing Required Packages

A typical database-enabled Go file imports:

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

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

The underscore import ensures the driver registers itself.

Connecting to the Database

Below is a real connection example.

dsn := "user:password@tcp(127.0.0.1:3306)/company_db"
db, err := sql.Open("mysql", dsn)
if err != nil {
    log.Fatal(err)
}

defer db.Close()

Always close database connections to prevent leaks.

Checking the Connection

Verify that the database is reachable.

err = db.Ping()
if err != nil {
    log.Fatal("Database not reachable:", err)
}

Creating a Table

Let’s create a real table for storing users.

query := `
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(100),
    age INT
)`

_, err = db.Exec(query)
if err != nil {
    log.Fatal(err)
}

Inserting Real Data

Insert a user record into the database.

result, err := db.Exec(
    "INSERT INTO users (name, email, age) VALUES (?, ?, ?)",
    "Alice",
    "alice@example.com",
    29,
)
if err != nil {
    log.Fatal(err)
}

id, _ := result.LastInsertId()
fmt.Println("Inserted user ID:", id)

Querying Multiple Rows

Retrieve all users from the table.

rows, err := db.Query("SELECT id, name, email, age FROM users")
if err != nil {
    log.Fatal(err)
}
defer rows.Close()

for rows.Next() {
    var id, age int
    var name, email string

    rows.Scan(&id, &name, &email, &age)
    fmt.Println(id, name, email, age)
}

Querying a Single Record

Fetch a single user by ID.

var name string
err = db.QueryRow(
    "SELECT name FROM users WHERE id = ?",
    1,
).Scan(&name)

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

fmt.Println("User name:", name)

Updating Data

Update a user’s age.

_, err = db.Exec(
    "UPDATE users SET age = ? WHERE id = ?",
    30,
    1,
)
if err != nil {
    log.Fatal(err)
}

Deleting Records

Remove a user from the database.

_, err = db.Exec("DELETE FROM users WHERE id = ?", 1)
if err != nil {
    log.Fatal(err)
}

Prepared Statements

Prepared statements improve performance and security.

stmt, _ := db.Prepare(
    "INSERT INTO users (name, email, age) VALUES (?, ?, ?)",
)
defer stmt.Close()

stmt.Exec("Bob", "bob@example.com", 35)

Handling Errors Properly

Always check errors returned by database operations. Ignoring errors can lead to corrupted data or crashes.

Best Practices for Production

• Use connection pooling (default in database/sql)
• Never hardcode credentials
• Use environment variables
• Log database errors clearly
• Use prepared statements for user input

Practice Exercises

Exercise 1

Create a table called products with name, price, and stock.

Exercise 2

Write a query that returns only users older than 25.

Key Takeaways

• Go uses database/sql for database access
• Drivers handle database-specific logic
• Prepared statements improve safety
• Proper error handling is critical

What’s Next?

In the next lesson, you will learn how to build command-line applications (CLI) using Go for real-world tooling.