Web App with Go and PostgreSQL - Insert Record in PostgreSQL using GoLang
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.
Output:
- 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.
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:
then execute the following command:
id SERIAL PRIMARY KEY,
Firstname TEXT,
Lastname TEXT
);
We now insert one record using the following statement.
('yeshua','galisanao');
Below is the result of the above statement:
----+-----------+-----------
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:
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
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:
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.
First, hardcoded the values for the fields specified in the
Information{}struct.Then we use the
db.Execto perform theINSERToperation and check for errors afterward.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:
http.ListenAndServe(":8081", nil)
Then navigate to the project directory via terminal and run the main.go using the following command:
Output:
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:
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:
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)
}
Pilot the ObjectRocket Platform Free!
Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.
Get Started