Return affectedRows after executing DELETE statement

[carlosmaranje]

Hello,

I am wondering if there is an option we can enable to return the number of affected rows after a DELETE statement is executed. I am working on an API and I would like instead of verifying the ID to delete, before deleting it, simply delete it. The problem is if there is no such ID the DB will not return an error, neither will sqlc which is the expected behaviour. I know you can't delete something that doesn't exist, so in principle I should not expect these requests. I am using gin built-in methods to validate the data, but I want to validate the case where someone sends e.g. an out of range value to delete and return a message saying the record does not exist.

A real-use case is testing with Postman. I am testing my requests for different scenarios and receiving always null makes it difficult to know if I actually deleted an existing row or not.

This is the current function generated by sqlc

func (q *Queries) DeleteAccount(ctx context.Context, id int64) error {
	_, err := q.exec(ctx, q.deleteAccountStmt, deleteAccount, id)
	return err
}

I am wondering if there is a way to generate something like this:

func (q *Queries) DeleteAccount(ctx context.Context, id int64) error {
	result, err := q.exec(ctx, q.deleteAccountStmt, deleteAccount, id)
	if err != nil {
		return err
	}

	affRows, _ := result.RowsAffected()

	if affRows == 0 {
		return sql.ErrNoRows
	}
	return err
}

Thanks in advance.

[kyleconroy]

You're in luck, as we already have this option. Instead of using :exec, use :execrows.

CREATE TABLE authors (
  id   BIGSERIAL PRIMARY KEY,
  name text      NOT NULL,
  bio  text
);

-- name: DeleteAuthor :execrows
DELETE FROM authors
WHERE id = $1;

which generates the code you're looking for

func (q *Queries) DeleteAuthor(ctx context.Context, id int64) (int64, error) {
	result, err := q.db.ExecContext(ctx, deleteAuthor, id)
	if err != nil {
		return 0, err
	}
	return result.RowsAffected()
}

[carlosmaranje]

That's great !! Thanks!