Step 5 — The cart

Per-user rows again, plus one SQL trick that removes a whole branch of Go.

Topic 79 of 98 · 5 min to read · 50 min to build

After this topic you can

Write an upsert with ON CONFLICT to add or increase a cart item.
Read the cart with a join instead of one query per item.
Validate product and quantity before inserting.

What a cart is, in database terms

A cart is not a special thing. It is rows in cart_items belonging to one user: a product, a quantity, and nothing else. There is no cart object, no session, no expiry: the rows are the cart.

So everything from project two applies unchanged. Every query is scoped with WHERE user_id = $1, and adding to somebody else’s cart has to be impossible rather than merely disallowed.

Adding an item — the naive way, and the good one

Adding a product already in the cart should increase the quantity, not create a second row. The obvious approach is: SELECT to see if it is there, then INSERT or UPDATE. That is two round trips, and a race between the check and the write.

SQL has one statement that does the whole thing, and the UNIQUE (user_id, product_id) constraint from step 1 is what makes it work.

internal/adapter/storage/postgres/cart.go — create this file

1func (r *Repository) AddItem(ctx context.Context, userID, productID int64, qty int) error {
2	_, err := r.db.ExecContext(ctx, `
3		INSERT INTO cart_items (user_id, product_id, quantity)
4		VALUES ($1, $2, $3)
5		ON CONFLICT (user_id, product_id)
6		DO UPDATE SET quantity = cart_items.quantity + EXCLUDED.quantity`,
7		userID, productID, qty)
8	return err
9}

ON CONFLICT ... DO UPDATE is called an upsert. One statement, no race, and the branch you would have written in Go disappears.

Reading the cart with a join

The cart rows hold only ids and quantities. To show a cart you also need names, prices and stock, which live on the product. One join brings them together in a single query.

internal/adapter/storage/postgres/cart.go

1func (r *Repository) Items(ctx context.Context, userID int64) ([]Item, error) {
2	rows, err := r.db.QueryContext(ctx, `
3		SELECT ci.id, ci.quantity,
4		       p.id, p.name, p.price_cents, p.stock
5		  FROM cart_items ci
6		  JOIN products p ON p.id = ci.product_id
7		 WHERE ci.user_id = $1
8		 ORDER BY ci.id`,
9		userID)
10	if err != nil {
11		return nil, err
12	}
13	defer rows.Close()
14
15	var out []Item
16	for rows.Next() {
17		var i Item
18		if err := rows.Scan(&i.ID, &i.Quantity,
19			&i.ProductID, &i.Name, &i.PriceCents, &i.Stock); err != nil {
20			return nil, err
21		}
22		out = append(out, i)
23	}
24	return out, rows.Err()
25}

Fetching the cart rows and then querying each product in a loop is the N+1 problem. One join is one round trip.

Totals belong in Go here, not in SQL

This is a judgement call. The monthly report in project two used SQL SUM because it added up thousands of rows. A cart has a handful, and the join has already brought them into Go. Adding them up there is clearer and costs nothing extra.

The rule is *do the work where the data already is*. For the report that was the database; here it is your Go code.

internal/core/service/cart.go

1func (s *Service) View(ctx context.Context, userID int64) (*Cart, error) {
2	items, err := s.repo.Items(ctx, userID)
3	if err != nil {
4		return nil, err
5	}
6
7	var total int64
8	for _, i := range items {
9		total += i.PriceCents * int64(i.Quantity)   // integer cents, never floats
10	}
11
12	return &Cart{Items: items, TotalCents: total}, nil
13}

Validate before you insert

internal/core/service/cart.go

1func (s *Service) Add(ctx context.Context, userID, productID int64, qty int) error {
2	if qty < 1 || qty > 99 {
3		return fmt.Errorf("%w: quantity must be between 1 and 99", ErrValidation)
4	}
5
6	p, err := s.products.GetByID(ctx, productID)
7	if err != nil {
8		return err                     // no such product → 404
9	}
10	if !p.Active {
11		return fmt.Errorf("%w: that product is not for sale", ErrValidation)
12	}
13	if p.Stock < qty {
14		return fmt.Errorf("%w: only %d left", ErrValidation, p.Stock)
15	}
16
17	return s.repo.AddItem(ctx, userID, productID, qty)
18}

This stock check is a courtesy, not a guarantee. Between here and checkout somebody else can buy the last one, and step 6 has to handle that.

Try it

terminal

1curl -s -X POST localhost:8080/v1/cart/items -H "Authorization: Bearer $CUST" \
2  -d '{"product_id":1,"quantity":2}'
3
4# add the same product again — quantity becomes 3, not a second row
5curl -s -X POST localhost:8080/v1/cart/items -H "Authorization: Bearer $CUST" \
6  -d '{"product_id":1,"quantity":1}'
7
8curl -s -H "Authorization: Bearer $CUST" localhost:8080/v1/cart | jq
9
10# another customer sees an empty cart
11curl -s -H "Authorization: Bearer $CUST2" localhost:8080/v1/cart | jq

The short version

A cart is just rows scoped to a user. Nothing about it is special.
ON CONFLICT ... DO UPDATE does insert-or-increment in one statement, with no race.
One join beats fetching the cart then looping for each product. That loop is N+1.
Do the arithmetic where the data is. Small set: Go. Large set: SQL.

Remember: A stock check when adding to a cart is a courtesy. The only check that counts is the one inside the checkout transaction.

Build this before moving on

Build the three cart endpoints. Add the same product twice and confirm you get one row with quantity 3. Then check another user sees an empty cart.

Then try these in your own editor

1Write the add-to-cart the naive way with SELECT then INSERT, and work out where the race is.
2Add a product to your cart, delete the product as admin, then view the cart. Decide what should happen.
3Try adding a quantity of 0 and of 1000.
4Add 20 different products, then check how many queries viewing the cart takes.

Official documentation

Check yourselfTake the Project 3 — Small shop test