Updating and Deleting rows in PostgreSQL from Go lang project

Introduction

In the previous post Inserting records into a database with Go, we learned how to insert rows to PostgreSQL database from Go project using database/sql package that ships with Go, along with github.com/lib/pq Postgres driver. In this post, we’ll learn the remaining two DML (data manipulation language) operations i.e. UPDATE and DELETE.

Prerequisites

To be able to achieve our objective i.e. Updating and Deleting rows to PostgreSQL from Go, there are some prerequisites. Sticking to the core subject of this blog, I won’t cover them, however, I’m providing with some references

For purpose of this tutorial, I’m using Postgres 11 and Go 1.13.xx

Objective – Updating and  Deleting rows in a PostgreSQL table using Go lang database/sql package

In following steps, I’ll demonstrate how to write a simple command-line tool (CLI) using Go lang to update/delete rows in PostgreSQL database table

Step 1. SQL Table definition

We’ll re-use our test table EMP that we created in the last post while learning to insert rows. Below is table definition

CREATE TABLE emp (  
  empno SERIAL PRIMARY KEY,  
  ename TEXT,
  sal INT,
  email TEXT UNIQUE NOT NULL 
);

Step 2. UPDATE and DELETE statements in SQL

Lets first write down our DML statements for update and delete

-- SQL stmt to update a row
UPDATE emp
	SET sal = 1800
	WHERE ename = 'Allen';


-- SQL stmt to delete a row
DELETE FROM emp
	WHERE ename = 'Blake';

Step 3. Connecting to PostgreSQL instance from Go lang

As always, we first need to make a connection to PostgreSQL instance. Since the code to make a connecting to PostgreSQL database is same, I’ll skip repeating it here, however, if you need a refresher please visit the earlier post Inserting rows in PostgreSQL db from Go lang project

Step 4. Preparing Go lang code

In our Go code, we’ll use the same SQL statement and calling db.Exec() method on sql.db object. An imp thing to note is here is db.Exec() method returns two values – a Result and an Error. We’ll use Error for error handling, to generate panic() if anything goes wrong. And we’ll use Result to verify the count of rows that were updated or deleted. Let’s see

// update a row in table
sqlStatementUpdt := `
	UPDATE emp
	SET sal = 3000 WHERE ename = $1;`
	res, err := db.Exec(sqlStatementUpdt, "Smith")
	if err != nil {
		panic(err)
	}
	count, err := res.RowsAffected()
	
	if err != nil {
		panic(err)
	}
	fmt.Printf("rows updated: %v\n", count)
// delete a rows in table
sqlStatementDel := `
	DELETE FROM emp
	WHERE ename = $1;`
	res1, err := db.Exec(sqlStatementDel, "Jones")
	if err != nil {
		panic(err)
	}
	count, err = res1.RowsAffected()
	if err != nil {
		panic(err)
	}
	fmt.Printf("rows deleted: %v\n", count)

Step 5. Putting this all together

package main

	// updating PostgreSQL records using Go's database/sql package

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

const (
	host = "localhost"
	port = 5432
	user = "postgres"
	password = "secure-password"
	dbname = "connect-db"
)

func main() {

	connStr := fmt.Sprintf("host=%s port=%d user=%s "+
	"password=%s dbname=%s sslmode=disable",
	host, port, user, password, dbname)
	
	db, err := sql.Open("postgres", connStr)
	if err != nil {
		panic(err)
	}
	defer db.Close()

	// update row
	sqlStatementUpdt := `
	UPDATE emp
	SET sal = 3000 WHERE ename = $1;`
	res, err := db.Exec(sqlStatementUpdt, "Smith")
	if err != nil {
			panic(err)
	}
	count, err := res.RowsAffected()
	if err != nil {
		panic(err)
	}
	fmt.Printf("rows updated: %v\n", count)

	// delete row
	sqlStatementDel := `
	DELETE FROM emp
	WHERE ename = $1;`
	res1, err := db.Exec(sqlStatementDel, "Jones")
	if err != nil {
		panic(err)
	}
	count, err = res1.RowsAffected()
	
	if err != nil {
		panic(err)
	}
	fmt.Printf("rows deleted: %v\n", count)
}

Conclusion

In this post we learned how to UPDATE and DELETE records in PostgreSQL db table in Go lang project. In the next post, we’re going to learn how to read rows (aka SELECT) rows from the database table.

Author: Varun Dhawan

Hello dear reader, I'm DevOps Engineer based in Minnesota, US (beautiful land of 10,000 lakes). I am perpetually curious and always willing to learn and engineer systems that can help solve complex problems using data. When I am not engineering or blogging, you’ll find me cooking and spending time with my wife and daughter. Varun.Dhawan@gmail.com

2 thoughts on “Updating and Deleting rows in PostgreSQL from Go lang project”

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s