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
|
0 1 2 |
go mod init github.com/ntwklab/database_connection_test |
Basic Steps
The basic skeleton of the programme will be as follows
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
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
|
0 1 2 |
go get github.com/jackc/pgx/v5 |
Connect and Ping Database
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 |
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 } |
|
0 1 2 3 4 |
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
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 |
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 } |
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 |
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 ==================================== |