How to Update and Delete Record In PostgreSQL database using Go database/SQL package

Introduction

This tutorial will explain how to update and delete record in PostgreSQL database using Go database/SQL package. To access a database in Go, the SQL database is needed to execute queries, create statements and transactions and obtain results. The package SQL furnishes a generic interface for use with SQL-like databases and the SQL package must be used with a database driver.

Prerequisites to Update and Delete Record in PostgreSQL Database using Go Database/SQL package

  • The PostgreSQL server must be properly installed, configured and running.

  • A basic working SQL Knowledge.

  • Golang must be properly installed. Execute the following command to determine what version of Go is installed:

go version

The results should resemble the following:

go version go1.13 darwin/amd64

How to Create Sample Data Set

A working data set is needed to execute the examples given in this tutorial. First, create a table named applicant_info with a basic schema by executing the following code:

CREATE TABLE applicant_info (
id SERIAL PRIMARY KEY,
app_age INT,
app_firstname TEXT,
app_lastname TEXT,
app_email TEXT UNIQUE NOT NULL
);

Next, insert some records in the table with the following commands: `sql INSERT INTO applicant_info (app_age, app_email, app_firstname, app_lastname) VALUES (20,’arman@startup.io’,’arman’,’doe’), (21,’kevin@startup.io’,’kevin’,’dean’); `

The results should be a table with the following records:

id | app_age | app_firstname | app_lastname | app_email ----+---------+---------------+--------------+------------------
1 | 20 | arman | doe | arman@startup.io
2 | 21 | kevin | dean | kevin@startup.io
(2 ROWS)

How to Update Records in PostgreSQL using Golang Database/SQL

This section will cover how the raw SQL can be used within Golang coding with some minor tweaking. For example, examine the following SQL statement:

UPDATE applicant_info
SET app_firstname = 'sly', app_lastname = 'washington'
WHERE id = 1;

To write code within Golang to execute the task shown in the above code, use the dollar sign “$” combined with a number that represents the position where the value will be used. An example follows:

sqlUpdate := `
UPDATE applicant_info
SET app_firstname = $1, app_lastname = $2
WHERE id = $3;`

_, err = db.Exec(sqlUpdate, "sly", "washington", 1)
if err != nil {
panic(err)
}

Note that the values correspond to the order of the dynamic variable, as “$1,” “$2” and “$3” respectively. Also note that the ordering of the variables are not set in stone and can be arranged as needed.

Executing the above code, and performing a basic SELECT in the PostgreSQL database for verification, should produce results that resemble the following:

id | app_age | app_firstname | app_lastname | app_email
----+---------+---------------+--------------+------------------
2 | 21 | kevin | dean | kevin@startup.io
1 | 20 | sly | washington | arman@startup.io
(2 ROWS)

How to Delete Records in PostgreSQL using Golang Database/SQL

The previous section covered how to update records in PostgreSQL using Golang. This section will explain how to delete a record within the PostgreSQL database. The DELETE process is similar to the UPDATE process explained in the previous section, with only a minor tweaking to the delete SQL statements when used in Golang. Examine the below SQL statement:

DELETE FROM applicant_info WHERE id = 2;`

To use this with Golang code the statement must be changed accordingly, as in the following example:

sqlDelete := `
DELETE FROM applicant_info WHERE id = $1;`

_, err = db.Exec(sqlDelete, 1)
if err != nil {
panic(err)
}

Note that the DELETE codes were almost the same as the UPDATE syntax.

Execute the above SQL statement and verify it via simple SELECT operation against the table applicant_info. The results will resemble the following:

id | app_age | app_firstname | app_lastname | app_email
----+---------+---------------+--------------+------------------
2 | 21 | kevin | dean | kevin@startup.io

Conclusion

This tutorial explained the basics of how to update and delete record in PostgreSQL database using Go database/SQL package. The article covered the prerequisites to update and delete record in PostgreSQL database using Go database/SQL and how to create a sample data set. The tutorial also provided specific examples on how to update and delete records in PostgreSQL using Golang database/SQL. Remember that while the UPDATE and DELETE functions are similar, there is some minor modifications needed to the code.

Just the Code

The overall working code is as follows, just uncomment the block of codes accordingly.

package main

import (
"database/sql"
"fmt"

_ "github.com/lib/pq"
)

const (
host = "localhost"
port = 5432
user = "postgres"
password = ""
dbname = "testdatabase"
)
var db *sql.DB

func main() {
var err error

// ==================== DATABASE CONNECTION ===============
psqlInfo := fmt.Sprintf("host=%s port=%d user=%s "+
"password=%s dbname=%s sslmode=disable",
host, port, user, password, dbname)
db, err := sql.Open("postgres", psqlInfo)

if err != nil {
panic(err)
}
defer db.Close()

err = db.Ping()
if err != nil {
panic(err)
}


// ======= UPDATE code ============

sqlUpdate := `
UPDATE applicant_info
SET app_firstname = $1, app_lastname = $2
WHERE id = $3;`

_, err = db.Exec(sqlUpdate, "sly", "washington", 1)
if err != nil {
panic(err)
}
// ===============================

// ======= DELETE code ============
sqlDelete := `
DELETE FROM applicant_info WHERE id = $1;`

_, err = db.Exec(sqlDelete, 1)
if err != nil {
panic(err)
}

}
// ===============================

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.