Performing CRUD Operations in PostgreSQL with Go

In this code example, we demonstrate how to perform basic CRUD (Create, Read, Update, Delete) operations in PostgreSQL using the Go programming language. We establish a connection to a PostgreSQL database and showcase the implementation of each operation. Let’s dive into the code and explore each section.

Section 1: Connecting to the PostgreSQL Database In the connectPostgresDB function, we establish a connection to the PostgreSQL database using the database/sql package and the PostgreSQL driver (github.com/lib/pq). The function takes care of creating the database connection and returns the *sql.DB object for further use.

func connectPostgresDB() *sql.DB {
 connstring := "user=postgres dbname=postgres password='*****' host=localhost port=5432 sslmode=disable"
 db, err := sql.Open("postgres", connstring)
 if err != nil {
  fmt.Println(err)
 }
 return db
}

Section 2: Inserting Data The Insert function demonstrates how to insert data into the PostgreSQL database. In this example, we have predefined values for id, name, and domain. The insertIntoPostgres function is responsible for executing the SQL query to insert the data into the students table. If the insertion is successful, a success message is printed.

func Insert(db *sql.DB) {
 id = 001
 name = "Edwin"
 domain = "golang"
 insertIntoPostgres(db, id, name, domain)

}

func insertIntoPostgres(db *sql.DB, id int, name, domain string) {
 _, err := db.Exec("INSERT INTO  students(id,name,domain) VALUES($1,$2,$3)", id, name, domain)
 if err != nil {
  fmt.Println(err)
 } else {
  fmt.Println("value inserted")
 }
}

Section 3: Reading Data The Read function shows how to fetch data from the PostgreSQL database. We execute a SELECT query to retrieve all rows from the students table. The fetched data is then printed in a formatted manner.

func Read(db *sql.DB) {
 rows, err := db.Query("SELECT * FROM students")
 if err != nil {
  fmt.Println(err)
 } else {
  fmt.Println("id  name    domain")
  for rows.Next() {
   rows.Scan(&id, &name, &domain)
   fmt.Printf("%d - %s - %s \n", id, name, domain)
  }

 }
}

Section 4: Updating Data The Update function demonstrates how to update data in the PostgreSQL database. In this case, we update the name field of a student with a specific id value. The SQL query is executed using the db.Exec function, and a success message is displayed upon successful update.

func Update(db *sql.DB) {
 id = 1
 name = "Eddie"
 _, err := db.Exec("UPDATE students SET name=$1 WHERE id=$2", name, id)
 if err != nil {
  fmt.Println(err)
 } else {
  fmt.Println("Data updated")
 }
}

Section 5: Deleting Data The Delete function showcases how to delete data from the PostgreSQL database. Here, we remove a student record based on the id value provided. The db.Exec function executes the DELETE query, and a success message is printed if the deletion is successful.

func Delete(db *sql.DB) {
 id = 1
 _, err := db.Exec("DELETE FROM students WHERE id=$1", id)
 if err != nil {
  fmt.Println(err)
 } else {
  fmt.Println("Data deleted")
 }
}

package main

import (
 "database/sql"
 "fmt"
 "os"

 _ "github.com/lib/pq"
)

var id int
var name string
var domain string

func main() {
 var choice int
 db := connectPostgresDB()
 for {
  fmt.Println("Choose\n1.Insert data\n2.Read data\n3.Update data\n4.Delete data\n5.Exit")
  fmt.Scan(&choice)
  switch choice {
  case 1:
   Insert(db)
  case 2:
   Read(db)
  case 3:
   Update(db)
  case 4:
   Delete(db)
  case 5:
   os.Exit(0)
  }
 }
}

// CONNECT DB

//before connecting you have to create a database and a table in psql shell (just a base code improve these code as well as you need)

func connectPostgresDB() *sql.DB {
 connstring := "user=postgres dbname=postgres password='*****' host=localhost port=5432 sslmode=disable"
 db, err := sql.Open("postgres", connstring)
 if err != nil {
  fmt.Println(err)
 }
 return db
}

// INSERT

func Insert(db *sql.DB) {
 id = 001
 name = "Edwin"
 domain = "golang"
 insertIntoPostgres(db, id, name, domain)

}

func insertIntoPostgres(db *sql.DB, id int, name, domain string) {
 _, err := db.Exec("INSERT INTO  students(id,name,domain) VALUES($1,$2,$3)", id, name, domain)
 if err != nil {
  fmt.Println(err)
 } else {
  fmt.Println("value inserted")
 }
}

// READ

func Read(db *sql.DB) {
 rows, err := db.Query("SELECT * FROM students")
 if err != nil {
  fmt.Println(err)
 } else {
  fmt.Println("id  name    domain")
  for rows.Next() {
   rows.Scan(&id, &name, &domain)
   fmt.Printf("%d - %s - %s \n", id, name, domain)
  }

 }
}

// UPDATE

func Update(db *sql.DB) {
 id = 1
 name = "Eddie"
 _, err := db.Exec("UPDATE students SET name=$1 WHERE id=$2", name, id)
 if err != nil {
  fmt.Println(err)
 } else {
  fmt.Println("Data updated")
 }
}

// DELETE

func Delete(db *sql.DB) {
 id = 1
 _, err := db.Exec("DELETE FROM students WHERE id=$1", id)
 if err != nil {
  fmt.Println(err)
 } else {
  fmt.Println("Data deleted")
 }
}

// completed ?? now do it in GROM 

This code example provides a basic understanding of performing CRUD operations in PostgreSQL using Go. By following this guide, you can connect to a PostgreSQL database, insert, read, update, and delete data. Feel free to expand upon this foundation and explore more advanced features of PostgreSQL and Go.

Next Steps: Implementing CRUD Operations with GORM To enhance the code further, you can consider using the GORM library for Go, which provides an ORM (Object-Relational Mapping) interface. GORM simplifies database interactions and offers additional features such as query building, associations, and migrations. Implementing the same CRUD operations with GORM can provide a more structured and intuitive approach to working with the PostgreSQL database in Go.

Have a nice day GOPHERS