Web App with Go and PostgreSQL - How to Delete a Record in PostgreSQL using GoLang

Introduction

If you’re working with data in a PostgreSQL database, you’ll find that you sometimes need to delete records from a table. This task can be accomplished in a GoLang script with the help of the Go database/sql package. In this article, we’ll show you how to build a simple web app that will delete a record in PostgreSQL using GoLang.

Prerequisites

Before we dig into our GoLang script, it’s important to review a few prerequisites that are necessary for this project:

  • You’ll need to ensure that PostgreSQL server is properly installed on your server or machine. The service should be running in the background.

  • You’ll also need to ensure that GoLang is properly installed on your machine. To check if you have GoLang installed and to determine what version it is, use the following command:

go version

The output of this command should look like the following:

go version go1.13 darwin/amd64
  • It’s recommended to have some basic SQL knowledge in order to follow along with the examples in this tutorial. It’s also helpful to have a general understanding of the GoLang nethttp package

Creating a Sample Database

Let’s begin by creating a sample database that we’ll use in this article. To do this, use the following command in the PostgreSQL shell:

CREATE DATABASE deleteTest;

After we create the database, we’ll also create a table named tbldeleteTest. We need to be connected to our newly-created database, so we’ll use the PostgreSQL meta-command \c and specify the database, as shown below:

\c deletetest;

Then we’ll execute the following command:

CREATE TABLE tbldeleteTest (
id SERIAL PRIMARY KEY,
Firstname TEXT,
Lastname TEXT
);

We’ll also insert records into our new table using the following statement:

INSERT INTO tbldeleteTest (Firstname, Lastname) VALUES
('abishai','galisanao'),
('yeshua', 'galisanao'),
('raizel','galisanao'),
('risa','galisanao');

The result of the above statement should look like this:

id | firstname | lastname
----+-----------+-----------
1 | abishai | galisanao
2 | yeshua | galisanao
3 | raizel | galisanao
4 | risa | galisanao
(4 ROWS)

How to Connect GoLang to a PostgreSQL Database

Now that we have a sample database to work with, we can connect GoLang to it.

To do this, we need to create an executable file that we’ll be using to interact with our database. In our project directory, let’s create a GoLang file and name it main.go. Then we’ll add the following code:

package main

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

var db *sql.DB

// This function will make a connection to our database only once.
func init() {
var err error

connStr := "postgres://postgres:password@localhost/deletetest?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")
}

For a detailed explanation of what’s going on in the code shown above, you can consult this link: Web App with Go and PostgreSQL : Connecting GoLang to PostgreSQL

Deleting a Record in PostgreSQL Using a GoLang Script

In this section, we’ll look at the GoLang code that will perform the DELETE operation in our PostgreSQL database.

Package Main

As we mentioned earlier, we’re going to create a GoLang executable file. That’s why we include the line package main in our file– it tells the GoLang compiler that this package will be compiled as an executable file.

package main

Importing the GoLang Dependencies

To access the methods and functions that we’ll need to connect and interact with the database, we import the following dependencies:

import (

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

)

Let’s take a closer look at each of the packages we imported:

  • The package database/sql allows us to perform operations against the database.
  • The package pq is a GoLang PostgreSQL driver. Notice that we use an _ underscore, which tells GoLang to import the package even though it will not be referenced directly within the GoLang scripts.
  • For I/O formatting, we import the fmt package.
  • Finally, we import the net/http package for its HTTP client/server implementations.

NOTE: It’s not advisable to use the package directly in the code. Instead, it’s better to use the types as defined in the package database/sql, to avoid making the code dependent on the drivers.

GoLang Script for Deleting a PostgreSQL Record

In this section, we’ll show you the GoLang script that will be used to delete a record in our PostgreSQL database:

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


testinfo.Id := 1

// delete values
_, err = db.Exec("DELETE tbldeletetest WHERE id=$1;", testinfo.Id)
if err != nil {
http.Error(w, http.StatusText(500), http.StatusInternalServerError)
return
}
fmt.Fprintf(w, " Deleted a record!")

}

The above code simply performs the deletion of a specific record using the db.Exec() method; it then checks for errors. After that, it generates a notification message in the browser.

The func main() – GoLang Entry Point

This section will focus on GoLang’s entry point, the func main(). Shown below is the GoLang script:

func main() {
http.HandleFunc("/delete", deleteTest) // (1)
http.ListenAndServe(":8080", nil) // (2)
}

The http.HandleFunc() acts as the router of our basic application. It decides what function to execute based on the parameters it receives from the browser. In this case, the only router we have is for the /delete parameter.

NOTE: It’s important to point out that we have just performed a callback function in the above code, where we pass a function as an argument.

Next, we tell the server to listen to port :8080, using the http.ListenAndServe() function.

At this point, we can test our GoLang script by navigating to the project directory in our terminal and using the following code:

go run main.go

You’ll get output that looks like the following:

You are now connected to the database.

This tells us that the server is now running and listening on port “:8080”. To execute our web app, we then navigate to it using the following URL: http://localhost:8080/delete.

The output will look like the following HTML:

Deleted a record!

The image shows a string "Deleted a record!"

We can verify that we successfully deleted a record from the table tbldeletetest with an id of 1:

SELECT * FROM tbldeletetest;

The result should look something like this:

id | firstname | lastname
----+-----------+-----------
2 | yeshua | galisanao
3 | raizel | galisanao
4 | risa | galisanao
(3 rows)

Conclusion

If you’re a Go developer and you want to interact with PostgreSQL in your scripts, it’s important to know how to execute basic database operations such as deletions. In this article, we showed you how to construct a web app with Go and PostgreSQL that can delete a specific record from a table. Using the code we provided as an example, you’ll be able to create your own GoLang scripts to delete rows from PostgreSQL when needed.

The Code

Shown below is the GoLang script needed to delete a record in its entirety:

package main

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

var db *sql.DB
var err error

func init() {

connStr := "postgres://postgres:password@localhost/deletetest?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.")
}


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

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

id := 1

// delete values
_, err = db.Exec("DELETE FROM tbldeletetest WHERE id=$1;", id)
if err != nil {
http.Error(w, http.StatusText(500), http.StatusInternalServerError)
return
}
fmt.Fprintf(w, " Deleted a record!")

}

Pilot the ObjectRocket Platform Free!

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

Get Started

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.