**Course Title:** Mastering Go: From Basics to Advanced Development **Section Title:** Data Persistence: Working with Databases **Topic:** CRUD operations in Go with a database **Introduction:** In the previous topic, we covered the basics of working with databases in Go using the `database/sql` package. In this topic, we'll take it a step further and explore how to perform CRUD (Create, Read, Update, Delete) operations in Go with a database. **CRUD Operations Overview:** CRUD operations are the building blocks of any data-driven application. They enable you to manipulate data in your database, allowing you to create new records, read existing ones, update existing records, and delete unwanted data. In this section, we'll delve into each of these operations and provide examples of how to implement them in Go. **Create Operation:** The create operation allows you to insert new records into your database. To perform a create operation in Go, you'll use the `Exec()` function provided by the `database/sql` package. Here's an example of how to create a new record in a PostgreSQL database: ```go package main import ( "database/sql" "fmt" _ "github.com/lib/pq" ) func main() { connStr := "user=myuser dbname=mydb password=mypass host=localhost sslmode=disable" db, err := sql.Open("postgres", connStr) if err != nil { fmt.Println(err) return } defer db.Close() _, err = db.Exec(`INSERT INTO users (name, email) VALUES ('John Doe', 'john@example.com')`) if err != nil { fmt.Println(err) return } } ``` In this example, we're connecting to a PostgreSQL database using the `pq` driver (you can download it using `go get -u github.com/lib/pq`). We then use the `Exec()` function to execute an INSERT query, which inserts a new record into the `users` table. **Read Operation:** The read operation allows you to retrieve existing records from your database. To perform a read operation in Go, you'll use the `Query()` or `QueryRow()` function provided by the `database/sql` package. Here's an example of how to retrieve a record from a PostgreSQL database: ```go package main import ( "database/sql" "fmt" _ "github.com/lib/pq" ) func main() { connStr := "user=myuser dbname=mydb password=mypass host=localhost sslmode=disable" db, err := sql.Open("postgres", connStr) if err != nil { fmt.Println(err) return } defer db.Close() var ( id int name string email string ) err = db.QueryRow(`SELECT id, name, email FROM users WHERE name = 'John Doe'`).Scan(&id, &name, &email) if err != nil { fmt.Println(err) return } fmt.Println(id, name, email) } ``` In this example, we're using the `QueryRow()` function to execute a SELECT query, which retrieves a single record from the `users` table. We then use the `Scan()` function to unmarshal the result into variables. **Update Operation:** The update operation allows you to modify existing records in your database. To perform an update operation in Go, you'll use the `Exec()` function provided by the `database/sql` package. Here's an example of how to update a record in a PostgreSQL database: ```go package main import ( "database/sql" "fmt" _ "github.com/lib/pq" ) func main() { connStr := "user=myuser dbname=mydb password=mypass host=localhost sslmode=disable" db, err := sql.Open("postgres", connStr) if err != nil { fmt.Println(err) return } defer db.Close() _, err = db.Exec(`UPDATE users SET email = 'johndoe@example.com' WHERE name = 'John Doe'`) if err != nil { fmt.Println(err) return } } ``` In this example, we're using the `Exec()` function to execute an UPDATE query, which modifies a record in the `users` table. **Delete Operation:** The delete operation allows you to remove existing records from your database. To perform a delete operation in Go, you'll use the `Exec()` function provided by the `database/sql` package. Here's an example of how to delete a record from a PostgreSQL database: ```go package main import ( "database/sql" "fmt" _ "github.com/lib/pq" ) func main() { connStr := "user=myuser dbname=mydb password=mypass host=localhost sslmode=disable" db, err := sql.Open("postgres", connStr) if err != nil { fmt.Println(err) return } defer db.Close() _, err = db.Exec(`DELETE FROM users WHERE name = 'John Doe'`) if err != nil { fmt.Println(err) return } } ``` In this example, we're using the `Exec()` function to execute a DELETE query, which removes a record from the `users` table. **Conclusion:** In this topic, we covered the basics of CRUD operations in Go using a database. We explored how to create, read, update, and delete records in a PostgreSQL database using the `database/sql` package. With this knowledge, you can now perform common database operations in your Go applications. **What's Next:** In the next topic, we'll cover best practices for managing database connections in Go. **Additional Resources:** * [Go documentation for database/sql package](https://golang.org/pkg/database/sql/) * [PostgreSQL documentation for CREATE, READ, UPDATE, and DELETE operations](https://www.postgresql.org/docs/13/sql-commands.html) * [lib/pq GitHub repository](https://github.com/lib/pq) **Leave a comment below if you have any questions or need further clarification on this topic.**