sql

When saving data to an SQL database, we often use some kind of autogenerated primary keys for our entities. It's very handy since we don't need to keep track of it and make sure we don't have duplicate identifiers. On the other hand, when inserting data we are not sure how we can retrieve it, as it wasn't chosen by us. Actually, using PostgreSQL it is very easy to achieve, so let's take a look how we can do that in a Golang application.

RETURNING syntax

Before we insert anything, we need to have a table in our SQL database. We'll reuse the structure created for the posts about SQL inheritance (three ways and a bonus one to do that):

CREATE TABLE player (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255),
    club VARCHAR(255),
    country CHAR(2)
);

Now the trick to everything is the RETURNING syntax provided by PostgreSQL, where we define what subset of newly created row should be returned from INSERT command. Think about it as if it was a SELECT clause where you get that row, and you can choose what columns should be displayed:

# psql console
=# INSERT INTO player(name,club,country) VALUES('Francesco Totti', 'AS Roma', 'IT') RETURNING id;
id 
----
7

=# SELECT * FROM player WHERE id = 7;
 id |   name          |  club   | country 
----+-----------------+---------+---------
  7 | Francesco Totti | AS Roma | IT
(1 row)

Golang example

Thanks to that all the heavy work is done by the database, all we need to do in Golang is execute our SQL statement, and scan the results set:

rows, err := db.Query("INSERT INTO player(name,club,country) VALUES('Francesco Totti', 'AS Roma', 'IT') RETURNING id;")
... // err check goes here
rows.Next()
var id int
err := rows.Scan(&id)
... // err check goes here
log.Printf("ID of inserted row: %d", id)

Easy, isn't it? Running the code results in the following output:

$ go run main.go 
2017/07/09 20:19:02 ID of inserted row: 8

Returning everything

As I mentioned before, the RETURNING clause can contain any subset of columns of the table we insert data into. We can also return all of them, which allows us to retrieve a whole row at once. With slight adjustments in our Go code, we can to that as well. Instead of RETURNING id we return * (again, just as in standard SELECT), then we need to alter how we scan the result rows:

rows, err := db.Query("INSERT INTO player(name,club,country) VALUES('Francesco Totti', 'AS Roma', 'IT') RETURNING *;")
if err := rows.Scan(&id, &name, &club, &country); err != nil {
    log.Fatalf("Failed to scan result set: %v", err)
}
log.Printf("[%d, '%s', '%s', '%s']", id, name, club, country)

This time, the output is:

$ go run main.go 
2017/07/09 20:22:14 [9, 'Francesco Totti', 'AS Roma', 'IT']

As you can see, with just a few additional characters we can make our lives much easier, especially if you want to expose an API where the user inserts data expecting that they get the newly created item as a response.

Full source code

func main() {
    db := common.Connect("user=postgres password=mysecretpassword dbname=golang_examples sslmode=disable")

    rows, err := db.Query("INSERT INTO player(name,club,country) VALUES('Francesco Totti', 'AS Roma', 'IT') RETURNING *;")
    if err != nil {
        log.Fatalf("err: %v", err)
    }
    defer rows.Close()

    ok := rows.Next()
    if !ok {
        log.Fatalf("Failed to get id, empty result set.")
    }

    var id int
    var name, club, country string
    if err := rows.Scan(&id, &name, &club, &country); err != nil {
        log.Fatalf("Failed to scan result set: %v", err)
    }

    log.Printf("[%d, '%s', '%s', '%s']", id, name, club, country)
}