Updating and deleting PostgreSQL records using Go's sql package

Thus far in the Using PostgreSQL with Go series we have covered how to work with Postgres using raw SQL, and in the previous two articles we have discussed connecting to a Postgres database with Go and inserting records into a Postgres database with Go. In the latter of those two we also covered what SQL injection is, how the database/sql package helps us avoid it, and how to retrieve the ID of newly created records (it isn’t straightforward with Postgres & Go unfortunately).

In this post we are going to discuss how to update and delete records so that we can wrap up editing data with Postgres and Go, and then in the next post we will discuss querying for records using the database/sql package. After that we will be done with the introductory topics, but that doesn’t mean we are done with the series. There are still plenty of things to cover such as third party packages (some are ORMs, others are not) and migration techniques/packages.

Once again, you need to make sure you understand how to connect to your database using database/sql. You can find instructions for this in the previous post - Connecting to a PostgreSQL DB with Go - and the code in this post will build off of this, but you can easily adapt the code here for whatever connection code you currently have. The important thing is that we have a valid sql.DB object.

On top of having a connection, this tutorial assumes that you have a table named users with the following attributes, along with a few records in the database that we can update and delete. You can create them with plain old SQL or Go - that choice is all yours.

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  age INT,
  first_name TEXT,
  last_name TEXT,
  email TEXT UNIQUE NOT NULL
);

Updating records with database/sql

The first thing we want to do is look at how to take an existing record and update some data inside of it. The raw SQL query is shown below.

UPDATE users
SET first_name = 'Johnny', last_name = 'Appleseed'
WHERE id = 1;

Just like when we created records using Go, updating them is pretty similar to writing raw SQL, but we replace values that we want to set dynamically with a dollar sign ($) follow by a number representing which argument this value should use with a 1-based index. An example is shown below.

sqlStatement := `
UPDATE users
SET first_name = $2, last_name = $3
WHERE id = $1;`
_, err = db.Exec(sqlStatement, 1, "NewFirst", "NewLast")
if err != nil {
  panic(err)
}

Notice that the $1, $2, etc don’t have to be in sequential order in the SQL statement, and can be arranged in whatever order seems best to you.

Deleting records with database/sql

Deleting records is virtually identical to updating records. You simply need to update your SQL statement that is passed into the Exec() method.

sqlStatement := `
DELETE FROM users
WHERE id = $1;`
_, err = db.Exec(sqlStatement, 1)
if err != nil {
  panic(err)
}

How do I verify if a record has been deleted or updated?

The code for updating and deleting records above is technically correct, but in most cases you are going to want to verify if a record was actually updated or deleted. You can achieve this by using the sql.Result object returned by the Exec() method. It should have a RowsAffected() method that will tell us how many rows were updated or deleted by our SQL statements.

sqlStatement := `
UPDATE users
SET first_name = $2, last_name = $3
WHERE id = $1;`
res, err := db.Exec(sqlStatement, 5, "NewFirst", "NewLast")
if err != nil {
  panic(err)
}
count, err := res.RowsAffected()
if err != nil {
  panic(err)
}
fmt.Println(count)

While we haven’t discussed querying records, you could also return the entire updated row by adding a RETURNING * clause to your update or delete SQL statements and then executing them with the QueryRow() method. Below is an example of this where we return two specific fields, but RETURNING * works much the same, but it returns every field. Just be careful with null values! We won’t cover how to handle null integer or string columns until the next post in this series.

sqlStatement := `
UPDATE users
SET first_name = $2, last_name = $3
WHERE id = $1
RETURNING id, email;`
var email string
var id int
err = db.QueryRow(sqlStatement, 6, "NewFirst", "NewLast").Scan(&id, &email)
if err != nil {
  panic(err)
}
fmt.Println(id, email)

Up Next…

You should now be able to create, update, and delete records in a Postgres database using Go. Next we are going to focus on querying records inside of our database, but unfortunately this article isn’t published yet. You can subscribe to my mailing list to get notifications when I publish new articles.

This post is part of a series called Using PostgreSQL with Go, and it covers everything from installing Postgres, using raw SQL, and finally using PostgreSQL with Go. Not familiar with something discussed in this article or just want to refresh your memory? Check out the rest of the series!

Learn Web Development with Go!

Sign up for my mailing list and I'll send you a FREE sample from my course - Web Development with Go. The sample includes three chapters from the book, and over 2.5 hours of screencasts.

You will also receive notifications when I release new articles, along with other freebies that I only share with my mailing list.

Avatar of Jon Calhoun
Written by
Jon Calhoun

Jon Calhoun is a full stack web developer who also teaches about Go, web development, algorithms, and anything programming related. He also consults for other companies who have development needs. (If you need some development work done, get in touch!)

Jon is a co-founder of EasyPost, a shipping API that many fortune 500 companies use to power their shipping infrastructure, and prior to founding EasyPost he worked at google as a software engineer.

More in this series

This post is part of the series, Using PostgreSQL with Go.

Spread the word

Did you find this page helpful? Let others know about it!

Vote on Hacker News

Sharing helps me continue to create both free and premium Go resources.

Want to discuss the article?

See something that is wrong, think this article could be improved, or just want to say thanks? I'd love to hear what you have to say!

You can reach me via email or via twitter.

Recent Articles All Articles Mini-Series Tags About Me Go Courses

©2018 Jonathan Calhoun. All rights reserved.