Let's say I have a web app that shows a list of posts. The post struct is:

type Post struct {
    Id         int64 `sql:",primary"`
    Title      string
    Body       string
}

It retrieves the posts with:

    var posts []*Post
    rows, err := db.QueryContext(ctx, "select * from posts;")
    if err != nil {
        return nil, oops.Wrapf(err, "could not get posts")
    }

    defer rows.Close()

    for rows.Next() {
        p := &Post{}
        err := rows.Scan(
            &p.Id,
            &p.Title,
            &p.Body,
        )
        if err != nil {
            return nil, oops.Wrapf(err, "could not scan row")
        }
        posts = append(posts, p)
    }

    return posts, nil

All works fine. Now, I want to alter the table schema by adding a column:

ALTER TABLE posts ADD author varchar(62);

Suddenly, the requests to get posts result in:

sql: expected 4 destination arguments in Scan, not 3

which makes sense since the table now has 4 columns instead of the 3 stipulated by the retrieval logic.

I can then update the struct to be:

type Post struct {
    Id         int64 `sql:",primary"`
    Title      string
    Body       string
    Author     string
}

and the retrival logic to be:

    for rows.Next() {
        p := &Post{}
        err := rows.Scan(
            &p.Id,
            &p.Title,
            &p.Body,
            &p.Author
        )
        if err != nil {
            return nil, oops.Wrapf(err, "could not scan row")
        }
        posts = append(posts, p)
    }

which solves this. However, this implies there is always a period of downtime between migration and logic update deploy. How to avoid that downtime?

I have tried swapping the order of the above changes but this does not work, with that same request resulting in:

sql: expected 3 destination arguments in Scan, not 4

(which makes sense, since the table only has 3 columns at that point as opposed to 4);

and other requests resulting in:

Error 1054: Unknown column 'author' in 'field list'

(which makes sense, because at that point the posts table does not have an author column just yet)

CodePudding user response：

You should be able to achieve your desired behavior by adapting the SQL Query to return the exact fields you want to populate.

SELECT Id , Title , Body FROM posts;

This way even if you add another column Author the query results only contain 3 values.