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:
1 | go version |
The output of this command should look like the following:
1 | 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:
1 | 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:
1 | \c deletetest; |
Then we’ll execute the following command:
1 2 3 4 5 | CREATE TABLE tbldeleteTest ( id SERIAL PRIMARY KEY, Firstname TEXT, Lastname TEXT ); |
We’ll also insert records into our new table using the following statement:
1 2 3 4 5 | INSERT INTO tbldeleteTest (Firstname, Lastname) VALUES ('abishai','galisanao'), ('yeshua', 'galisanao'), ('raizel','galisanao'), ('risa','galisanao'); |
The result of the above statement should look like this:
1 2 3 4 5 6 7 | 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:
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 | 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.
1 | 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:
1 2 3 4 5 6 7 8 | 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:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | 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:
1 2 3 4 | 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:
1 | go run main.go |
You’ll get output that looks like the following:
1 | 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:
1 | Deleted a record! |
We can verify that we successfully deleted a record from the table tbldeletetest
with an id of 1
:
1 | SELECT * FROM tbldeletetest; |
The result should look something like this:
1 2 3 4 5 6 | 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:
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 | 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