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

Written by Data Pilot
October 07, 2019
  • Go
  • PostgreSQL

Introduction

This article will show you how to connect to the database using the package SQL, by opening up a database by getting a pointer to a DB and we will be using within the packages Query, QueryRow() and Exec() which will return a result and an error, where the results is the LastInsertId() and RowsAffected(). All the mentioned packages will be used within this article.

Prerequisites

  • Ensure that PostgreSQL server is properly installed, configured and running on the background.

  • Ensure that Go Lang is properly installed in your system.

  • To know if you have Go Lang installed in your system and what version it is, use the following command.

1
go version

Output:

1
go version go1.13 darwin/amd64
  • Basic SQL Knowledge.
  • Overview understanding of the GoLang’s “nethttp” package

Creating Sample Database

Let us create a sample database that we are going to use in this article, use the following command in your PostgreSQL shell.

1
CREATE DATABASE insertTest;

After creating the database let us create a table named tblinsertTest with the following structure, first, you have connected to the database by using the PostgreSQL meta-command c then specify the database. The code looks something like the this:

1
c inserttest;

then execute the following command:

1
2
3
4
5
CREATE TABLE tblinsertTest (
id SERIAL PRIMARY KEY,
Firstname TEXT,
Lastname TEXT
);

We now insert one record using the following statement.

1
2
INSERT INTO tblinsertTest (Firstname, Lastname) VALUES
('yeshua','galisanao');

Below is the result of the above statement:

1
2
3
4
id | firstname | lastname
----+-----------+-----------
1 | yeshua | galisanao
(1 ROW)

Connecting GoLang to PostgreSQL Database using database/sql Package

In this section, we will be going to connect GoLang to the PostgreSQL database that we created in the previous section.

GoLang Script for Connecting to PostgreSQL Database

Let us create a new file in our Go project directory and save it as main.go. Below is the script that we will be using for connecting GoLang to PostgreSQL database:

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("You are now connected to the inserttest database.")
}

Basically, the above code creates an init() function that will be executed only once. This is an ideal set-up for a database connection.

for a detailed explanation of the above statement, you can visit an article dedicated for this purpose here: Web App with Go and PostgreSQL : Connecting GoLang to PostgreSQL

Insert Record in PostgreSQL using GoLang

Before we proceed with the tutorial, let us first create a struct{} that allows us to make a collection of fields that matches the field of our table tblinsertTest

1
2
3
4
5
type Information struct {
Id int
Firstname string
Lastname string
}

Now that we are connected to the sample database, let’s try to insert a new record using GoLang:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
func createInformation(w http.ResponseWriter, r *http.Request) {

var err error

info := Information{}
info.Firstname := "raizel"
info.Lastname := "galisanao"

_, err = db.Exec("INSERT INTO tblinsertTest (Firstname,Lastname) VALUES ($1,$2)", info.Firstname, info.Lastname)
if err != nil {
http.Error(w, http.StatusText(500), http.StatusInternalServerError)
return
}

fmt.Fprintf(w, "Record Created: ")
fmt.Fprintf(w, "%s %sn", info.Firstname, info.Lastname) // (3)
}

Let’s discuss the above code part by part.

  1. First, hardcoded the values for the fields specified in the Information{} struct.

  2. Then we use the db.Exec to perform the INSERT operation and check for errors afterward.

  3. Lastly, we show the details of the records we inserted in the table in the browser.

To test if the code is working, in the func Main() use the following code:

1
2
http.HandleFunc("/createinfo", createInformation)
http.ListenAndServe(":8081", nil)

Then navigate to the project directory via terminal and run the main.go using the following command:

1
go run main.go

Output:

1
You are now connected to the inserttest database.

Finally, we test it via browser by navigating using the following URL http://localhost:8081/createinfo, this will be handled accordingly by GoLang router and locate the function to process the request.

The output looks something like the following:

The image shows the browser displaying the newly inserted record in PostgreSQL using GoLang

Conclusion

In this article, we learn how to insert a record in PostgreSQL using GoLang.

The Code

Below is the code we reviewed in its entirety:

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

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

_ "github.com/lib/pq"
)

var db *sql.DB


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("You are now connected to the inserttest 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("/createinfo", createInformation)
http.ListenAndServe(":8081", nil)
}


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

var err error

info := Information{}
info.Firstname = "raizel"
info.Lastname = "galisanao"

_, err = db.Exec("INSERT INTO tblinsertTest (Firstname,Lastname) VALUES ($1,$2)", info.Firstname, info.Lastname)
if err != nil {
http.Error(w, http.StatusText(500), http.StatusInternalServerError)
return
}

fmt.Fprintf(w, "Record Created: ")
fmt.Fprintf(w, "%s %sn", info.Firstname, info.Lastname) // (3)
}
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.