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 single records from of our database.

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!