Cookies management by TermsFeed Cookie Consent

Implement Create method

Please disable your ad blocker šŸ™‚. Our ads are not intrusive

11/21

We have already created the repository structure and the Migrate() method, so it is time to implement the ā€œCā€ from the CRUD abbreviation, which is the Create() function. Copy the code for this method into our classic repository file and make sure you have all the necessary imports.

Create() method

The Create() function takes the website given as an argument and inserts it into the table. Generally, the DB.Query*() methods of the DB struct are used for reading the data from the database, and DB.Exec*() for inserts, updates, and deletes. So you may wonder why we use the DB.QueryRowContext() method for insertion instead of DB.ExecContext(). To explain that, look at the last part of the INSERT query:

RETURNING id

It causes the query to return the id of just inserted record as a result, which needs to be read using the Scan() method of the Row object returned by the DB.QueryRowContext().

err := r.db.QueryRowContext(ctx, "INSERT INTO websites(name, url, rank) values($1, $2, $3) RETURNING id", website.Name, website.URL, website.Rank).Scan(&id)

With the DB.ExecContext(), we would not be able to get the id of the last inserted record. This function returns a Result object (and an error) that has the LastInsertId() method, but due to the specific behavior of the INSERT command in PostgreSQL, this method is not supported by the pq and pgx drivers. More information here.

Note how we pass arguments to the INSERT query. We use the symbols $1, $2, $3, which at the execution stage are replaced with the values in the first, second, and third place after the SQL query in the DB.QueryRowContext() function. This way is supported by PostgreSQL and much safer in terms of defending against SQL injection attacks than using the fmt.Sprintf() function to build the final query.

After executing the INSERT command, we check if an error has occurred. If so, we check if it is an instance of the pgconn.PgError error and if its code indicates the unique constraint violation (code 23505). Such an error means that a row with the same UNIQUE field already exists in the table. For the websites table it means that a website with the same Name already exists. In this case, we can map the internal PostgreSQL error pgconn.PgError to the general Repository error ErrDuplicate, which we defined earlier. As the last thing, we assign the ID of the inserted item to the object that will be returned from the Create() function so that it reflects the current state in the database.

website/repository_postgresql_classic.go

package website

import (
    "context"
    "database/sql"
    "errors"

    "github.com/jackc/pgconn"
)

// ... constructor and Migrate() method

func (r *PostgreSQLClassicRepository) Create(ctx context.Context, website Website) (*Website, error) {
    var id int64
    err := r.db.QueryRowContext(ctx, "INSERT INTO websites(name, url, rank) values($1, $2, $3) RETURNING id", website.Name, website.URL, website.Rank).Scan(&id)
    if err != nil {
        var pgxError *pgconn.PgError
        if errors.As(err, &pgxError) {
            if pgxError.Code == "23505" {
                return nil, ErrDuplicate
            }
        }
        return nil, err
    }
    website.ID = id

    return &website, nil
}
11/21