Web App with Go and PostgreSQL - Update Record in PostgreSQL using GoLang

Written by Data Pilot
October 05, 2019
  • PostgreSQL

Introduction

GoLang or Go, as it is frequently referred to, is a popular programming application favorite of developers. That’s because it’s easy to learn and use. Plus it’s open source which has the community collaboration benefit of obtaining the input of developers. When it comes to any database management program however, revising records is a daily task that takes time to complete. It can be streamlined though. Take a few moments to learn an easy way to update a record using web app with Go and PostgreSQL. Smooth out your workflow today.

If you already know how to update a record and want to skip the specifics, go to Just the Code.

Prerequisites

  • PostgreSQL – Download and install PostgreSQL. Start it up.

  • Go language – Select the binary version made for your OS.

  • After that, check the version of GoLang by entering this command:

1
go version
  • You should see a response close to this one:
1
go version go1.13 darwin/amd64

Construct a database PostgreSQL sample

You’ll need a test database to try out the examples that will be shown to use the web app with Go and PostgreSQL.

  • Make a PostgreSQL test database with this command:
1
CREATE DATABASE updatetest;
  • Use \c to connect PostgreSQL and database, then add a table and name it tblinsertTest:
1
\c updatetest;
  • Put in the details of the structure of the table with this command:
1
2
3
4
5
CREATE TABLE tblupdateTest (
id SERIAL PRIMARY KEY,
Firstname TEXT,
Lastname TEXT
);
  • With the command here, make a record insertion:
1
2
INSERT INTO tblupdateTest (Firstname, Lastname) VALUES
('abishai','galisanao');
  • You should see a response like this one below:
1
2
3
4
id | firstname | lastname
----+-----------+-----------
1 | abishai | galisanao
(1 ROW)

Database PostgreSQL connection script of GoLang

  • Create a file in your GoLang project directory in GoLang. Call it main.go.

  • Next, connect your database like this:

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
package main

import (
"fmt"
"database/sql"
"net/http"
"log"
_ "github.com/lib/pq"
)

var db *sql.DB


// This will load the connection only once
func init() {
var err error

connStr := "postgres://postgres:password@localhost/inserttest?sslmode=disable"
db, err = sql.Open("postgres", connStr)

if err != nil {
panic(err)
}

if err = db.Ping(); err != nil {
panic(err)
}
// this will be printed in the terminal, notifying that we successfully connected to our database
fmt.Println("Connected to database")
}

Read Web App with Go and PostgreSQL: Connecting GoLang to PostgreSQL for a detailed explanation about the statement above.

Make a Golang script that instructs the program to be executable

  • Get the package main which makes the program executable.
1
package main

Perform a GoLang dependency importation

  • Import all the dependencies of GoLang that are needed to create the application to utilize the web app with Go and PostgreSQL:
1
2
3
4
5
6
7
8
import (

"database/sql"
_ "github.com/lib/pq"
"fmt"
"net/http"

)

Here are the details of the code written above:

  • The package database/sql is imported so that you can communicate with the database.

  • After that, you add the driver Go PostgreSQL by using github.com/lib/pq. The pq abbreviation at the end stands for “package.” Notice that there must be a _ symbol (underscore) before the code because it’s referenced indirectly.

  • Next, to obtain formatting with I/O, import the fmt package.

  • Lastly, get the package net/http. It’s the requirement for you to make the client and HTTP server implementations.

>NOTE: Avoid putting inside the code the package driver because that will make the code rely totally on the driver. Rather, the specified types in database/sql should be used.

Input the data type struct{} in GoLang

  • Use the struct data type to create matching fields in your table:
1
2
3
4
5
type Information struct {
Id int
Firstname string
Lastname string
}

Perform a PostgreSQL record insertion

  • Here is how you input a PostgreSQL record using a GoLang script:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
func updateTest(w http.ResponseWriter, r *http.Request) {


info := Information{}
info.Id = 1
info.Firstname = "Yeshua David"


// update values
_, err = db.Exec("UPDATE tblupdatetest SET name=$2 WHERE id=$1;", info.Id, info.Firstname)
if err != nil {
http.Error(w, http.StatusText(500), http.StatusInternalServerError)
return
}
fmt.Fprintf(w, "Record Updated!")

}

An explanation of the above:

  • Values are given to Information{} for the data type struct{}.

  • The db.Exec() executes the record updating.

  • After that, it finds out if there are any raised exceptions.

  • The final step is to go to your browser and create a notification message.

Where to put the main() function

  • The beginning of the script GoLang is where you’ll put the func main():
1
2
3
4
func main() {
http.HandleFunc("/updatetest", updateTestInformation) // (1)
http.ListenAndServe(":8080", nil) // (2)
}

  • The http.HandleFunc() handles the parameters in a particular order. The sequence is the first parameter, which is the string and the second parameter, which is the function.

  • Next, the http.handleFunc() handler points to the updateTrestInformation function from the browser.

>NOTE: You skip the parenthesis in the function if an argument is what you want to pass it as. That’s what you’re doing in this case. You’re passing updateTrestInformation. This type of function is called a callback.

  • Adjust your server’s settings to the :8080 port.

  • Time to run a code test for web app with Go and PostgreSQL. In a terminal window, go to the GoLang project directory and enter this command:

1
go run main.go
  • You should see a response that looks like this:
1
You are now connected to the database.

It shows that a successful database connection was made. The server is ready to hear requests on the :8080 port.

  • Check it out by performing a localhost navigation in your browser with this: http://localhost:8080/updatetest

  • The result should show a similarity to this:

1
Record Updated!

The image shows a string "Record Updated!"

  • In the shell script, confirm the update:
1
SELECT * FROM tblupdatetest;
  • You should get a similar result:
1
2
3
4
id | firstname | lastname
----+--------------+-----------
1 | Yeshua David | galisanao
(1 row)

The result is correct. Earlier, you updated the Information{} value for the struct{} data type. In particular, you changed one record for the Firstname field in the tblupdatetest table.

Conclusion

This tutorial explained how to use the web app with Go and PostgreSQL to update a record. You learned how to create a database and table sample to test the examples shown. What’s more, you made a successful PostgreSQL database connection. You then successfully updated a record. Keep these steps handy, and use them often as a reference in your daily database records management tasks.

Just the Code

Here’s the entire sample code for updating a record using the web app with Go and PostgreSQL:

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
package main

import (
"database/sql"
"fmt"
"log"
"net/http"

_ "github.com/lib/pq"
)

var db *sql.DB
var err error

func init() {

connStr := "postgres://postgres:password@localhost/updatetest?sslmode=disable"
db, err = sql.Open("postgres", connStr)

if err != nil {
panic(err)
}

if err = db.Ping(); err != nil {
panic(err)
}
// this will be printed in the terminal, notifying that we successfully connected to our database
fmt.Println("You are now connected to the database.")
}

// the fields will be exported to templates thus, fields need to be capitalized.
// this makes the fields visible our of this package
type Information struct {
Id int
Firstname string
Lastname string
}

func main() {
http.HandleFunc("/updatetest", updateTestInformation)
http.ListenAndServe(":8080", nil)
}

func updateTestInformation(w http.ResponseWriter, r *http.Request) {

info := Information{}
info.Id = 1
info.Firstname = "Yeshua David"

// update values
_, err = db.Exec("UPDATE tblupdatetest SET Firstname=$2 WHERE id=$1;", info.Id, info.Firstname)
if err != nil {
http.Error(w, http.StatusText(500), http.StatusInternalServerError)
return
}
fmt.Fprintf(w, "Record Updated!")

}
Rate 1 Star2 Stars3 Stars4 Stars5 Stars

Pilot the ObjectRocket Platform Free!

Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.

Get Started
Related Topics:
Additional Information:

Keep in the know!

Subscribe to our emails and we’ll let you know what’s going on at ObjectRocket. We hate spam and make it easy to unsubscribe.