This post will give a basic Go main.go file that can connect to a database and perform the basic CRUD operations.
I have created a test PostgreSQL database to demonstrate this.
I have created a new main.go file and will initiate the go mod file with the below
go mod init github.com/ntwklab/database_connection_testBasic Steps
The basic skeleton of the programme will be as follows
package main
func main () {
// connection to a database
// test connection
// get rows from table
// insert a row
// get rows from table again
// update a row
// get rows from table again
// get one row by id
// delete a row
// get rows again
}Install PGX
go get github.com/jackc/pgx/v5Connect and Ping Database
package main
import (
"database/sql"
"fmt"
"log"
_ "github.com/jackc/pgx/v5/stdlib"
)
func main() {
// connection to a database
conn, err := sql.Open("pgx", "host=localhost port=5432 dbname=go_connection_test user=stefankelly password=")
if err != nil {
log.Fatal(fmt.Sprintf("Unable to connec to: %v\n", err))
}
defer conn.Close()
log.Println("Connected to database")
// test connection
err = conn.Ping()
if err != nil {
log.Fatal(err)
}
log.Println("Pinged database!")
// get rows from table
// insert a row
// get rows from table again
// update a row
// get rows from table again
// get one row by id
// delete a row
// get rows again
}stefankelly@Stefans-MacBook-Pro Database_Connection_Test % go run main.go
2023/11/12 23:40:15 Connected to database
2023/11/12 23:40:15 Pinged database!Full Programme to; Create, READ, Update and Delete
package main
import (
"database/sql"
"fmt"
"log"
_ "github.com/jackc/pgx/v5/stdlib"
)
func main() {
// connection to a database
conn, err := sql.Open("pgx", "host=localhost port=5432 dbname=go_connection_test user=stefankelly password=")
if err != nil {
log.Fatal(fmt.Sprintf("Unable to connec to: %v\n", err))
}
defer conn.Close()
log.Println("Connected to database")
// test connection
err = conn.Ping()
if err != nil {
log.Fatal(err)
}
log.Println("Pinged database!")
// get rows from table
err = getAllRows(conn)
if err != nil {
log.Fatal(err)
}
// insert a row
query := `insert into users (first_name, last_name) values ($1, $2)`
_, err = conn.Exec(query, "David", "Koresh")
if err != nil {
log.Fatal(err)
}
log.Println("Inseted a row")
// get rows from table again
err = getAllRows(conn)
if err != nil {
log.Fatal(err)
}
// update a row
stmt := `update users set first_name = $1 where id = $2`
_, err = conn.Exec(stmt, "David", 5)
if err != nil {
log.Fatal(err)
}
log.Println("Updated one or more rows")
// get rows from table again
err = getAllRows(conn)
if err != nil {
log.Fatal(err)
}
// get one row by id
query = `select id , first_name, last_name from users where id = $1`
var first_name, last_name string
var id int
row := conn.QueryRow(query, 1)
err = row.Scan(&id, &first_name, &last_name)
if err != nil {
log.Fatal(err)
}
log.Println("QueryRow returns", id, first_name, last_name)
// delete a row
query = `delete from users where id = $1`
_, err = conn.Exec(query, 9)
if err != nil {
log.Fatal(err)
}
log.Println("Deleted a row!")
// get rows again
err = getAllRows(conn)
if err != nil {
log.Fatal(err)
}
}
func getAllRows(conn *sql.DB) error {
rows, err := conn.Query("select id, first_name, last_name from users")
if err != nil {
log.Println(err)
return err
}
defer rows.Close()
var first_name, last_name string
var id int
for rows.Next() {
err := rows.Scan(&id, &first_name, &last_name)
if err != nil {
log.Println(err)
return err
}
fmt.Println("Record is", id, first_name, last_name)
}
if err = rows.Err(); err != nil {
log.Fatal("Error scaning rows", err)
}
fmt.Println("====================================")
return nil
}
stefankelly@Stefans-MacBook-Pro Database_Connection_Test % go run main.go
2023/11/13 00:09:11 Connected to database
2023/11/13 00:09:11 Pinged database!
Record is 1 James Jamerson
Record is 2 John Johnson
Record is 3 Dave Koresh
Record is 4 Dave Koresh
Record is 6 David Koresh
Record is 7 David Koresh
Record is 8 David Koresh
Record is 9 David Koresh
Record is 10 David Koresh
Record is 5 David Koresh
====================================
2023/11/13 00:09:11 Inseted a row
Record is 1 James Jamerson
Record is 2 John Johnson
Record is 3 Dave Koresh
Record is 4 Dave Koresh
Record is 6 David Koresh
Record is 7 David Koresh
Record is 8 David Koresh
Record is 9 David Koresh
Record is 10 David Koresh
Record is 5 David Koresh
Record is 11 David Koresh
====================================
2023/11/13 00:09:11 Updated one or more rows
Record is 1 James Jamerson
Record is 2 John Johnson
Record is 3 Dave Koresh
Record is 4 Dave Koresh
Record is 6 David Koresh
Record is 7 David Koresh
Record is 8 David Koresh
Record is 9 David Koresh
Record is 10 David Koresh
Record is 11 David Koresh
Record is 5 David Koresh
====================================
2023/11/13 00:09:11 QueryRow returns 1 James Jamerson
2023/11/13 00:09:11 Deleted a row!
Record is 1 James Jamerson
Record is 2 John Johnson
Record is 3 Dave Koresh
Record is 4 Dave Koresh
Record is 6 David Koresh
Record is 7 David Koresh
Record is 8 David Koresh
Record is 10 David Koresh
Record is 11 David Koresh
Record is 5 David Koresh
====================================