In this section, we will cover the fundamental CRUD (Create, Read, Update, Delete) operations in Go, focusing on how to interact with a database to perform these operations. CRUD operations are essential for any application that deals with persistent data storage.
Objectives
- Understand the basic CRUD operations.
- Learn how to implement CRUD operations in Go.
- Practice with practical examples and exercises.
Prerequisites
- Basic understanding of Go programming.
- Familiarity with SQL and relational databases.
- Completion of the previous module on connecting to a database.
Table of Contents
Introduction to CRUD Operations
CRUD operations are the four basic functions of persistent storage. They are:
- Create: Insert new data into the database.
- Read: Retrieve data from the database.
- Update: Modify existing data in the database.
- Delete: Remove data from the database.
Setting Up the Database
Before we dive into CRUD operations, let's set up a simple database. We'll use SQLite for simplicity, but the concepts apply to other relational databases like MySQL or PostgreSQL.
Example Database Schema
We'll create a simple users table with the following schema:
CREATE TABLE users (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL,
email TEXT NOT NULL UNIQUE,
age INTEGER
);Create Operation
The Create operation involves inserting new records into the database.
Example: Inserting a New User
package main
import (
"database/sql"
"fmt"
_ "github.com/mattn/go-sqlite3"
)
func main() {
db, err := sql.Open("sqlite3", "./example.db")
if err != nil {
panic(err)
}
defer db.Close()
// Insert a new user
stmt, err := db.Prepare("INSERT INTO users(name, email, age) VALUES(?, ?, ?)")
if err != nil {
panic(err)
}
defer stmt.Close()
res, err := stmt.Exec("John Doe", "[email protected]", 30)
if err != nil {
panic(err)
}
id, err := res.LastInsertId()
if err != nil {
panic(err)
}
fmt.Printf("Inserted user with ID: %d\n", id)
}Explanation
- We open a connection to the SQLite database.
- We prepare an SQL statement for inserting a new user.
- We execute the statement with the user's details.
- We retrieve and print the ID of the newly inserted user.
Read Operation
The Read operation involves retrieving data from the database.
Example: Retrieving All Users
package main
import (
"database/sql"
"fmt"
_ "github.com/mattn/go-sqlite3"
)
func main() {
db, err := sql.Open("sqlite3", "./example.db")
if err != nil {
panic(err)
}
defer db.Close()
rows, err := db.Query("SELECT id, name, email, age FROM users")
if err != nil {
panic(err)
}
defer rows.Close()
for rows.Next() {
var id int
var name, email string
var age int
err = rows.Scan(&id, &name, &email, &age)
if err != nil {
panic(err)
}
fmt.Printf("ID: %d, Name: %s, Email: %s, Age: %d\n", id, name, email, age)
}
err = rows.Err()
if err != nil {
panic(err)
}
}Explanation
- We open a connection to the SQLite database.
- We execute a query to retrieve all users.
- We iterate over the result set and print each user's details.
Update Operation
The Update operation involves modifying existing records in the database.
Example: Updating a User's Age
package main
import (
"database/sql"
"fmt"
_ "github.com/mattn/go-sqlite3"
)
func main() {
db, err := sql.Open("sqlite3", "./example.db")
if err != nil {
panic(err)
}
defer db.Close()
// Update a user's age
stmt, err := db.Prepare("UPDATE users SET age = ? WHERE email = ?")
if err != nil {
panic(err)
}
defer stmt.Close()
res, err := stmt.Exec(35, "[email protected]")
if err != nil {
panic(err)
}
rowsAffected, err := res.RowsAffected()
if err != nil {
panic(err)
}
fmt.Printf("Updated %d rows\n", rowsAffected)
}Explanation
- We open a connection to the SQLite database.
- We prepare an SQL statement for updating a user's age.
- We execute the statement with the new age and the user's email.
- We retrieve and print the number of rows affected by the update.
Delete Operation
The Delete operation involves removing records from the database.
Example: Deleting a User
package main
import (
"database/sql"
"fmt"
_ "github.com/mattn/go-sqlite3"
)
func main() {
db, err := sql.Open("sqlite3", "./example.db")
if err != nil {
panic(err)
}
defer db.Close()
// Delete a user
stmt, err := db.Prepare("DELETE FROM users WHERE email = ?")
if err != nil {
panic(err)
}
defer stmt.Close()
res, err := stmt.Exec("[email protected]")
if err != nil {
panic(err)
}
rowsAffected, err := res.RowsAffected()
if err != nil {
panic(err)
}
fmt.Printf("Deleted %d rows\n", rowsAffected)
}Explanation
- We open a connection to the SQLite database.
- We prepare an SQL statement for deleting a user.
- We execute the statement with the user's email.
- We retrieve and print the number of rows affected by the deletion.
Practical Exercises
Exercise 1: Insert Multiple Users
Write a program to insert multiple users into the users table. Use a loop to insert at least three users.
Exercise 2: Retrieve Users by Age
Write a program to retrieve and print all users who are older than a specified age.
Exercise 3: Update User's Email
Write a program to update a user's email based on their name.
Exercise 4: Delete Users by Age
Write a program to delete all users who are younger than a specified age.
Summary
In this section, we covered the basic CRUD operations in Go:
- Create: Inserting new records into the database.
- Read: Retrieving data from the database.
- Update: Modifying existing records in the database.
- Delete: Removing records from the database.
These operations are fundamental for any application that interacts with a database. By practicing these operations, you will gain a solid understanding of how to manage data in a Go application.
Go Programming Course
Module 1: Introduction to Go
Module 2: Basic Concepts
Module 3: Advanced Data Structures
Module 4: Error Handling
Module 5: Concurrency
Module 6: Advanced Topics
Module 7: Web Development with Go
Module 8: Working with Databases
Module 9: Deployment and Maintenance
- Building and Deploying Go Applications
- Logging
- Monitoring and Performance Tuning
- Security Best Practices