CRUD Operations

Introduction

CRUD (Create, Read, Update, Delete) is the backbone of every backend API. In Go, implement CRUD in a repository layer with database/sql, wrap operations in transactions where needed, and expose through HTTP handlers returning appropriate status codes.

This lesson builds a complete CRUD flow for a Task resource — insert with returning ID, list with pagination, update with optimistic locking, and soft delete. These patterns appear in take-home assignments and onsite coding rounds.

Understanding RETURNING clauses, rows affected checks, and transactional create-with-relations separates production code from tutorial snippets.

The story

A Cloudflare dashboard backend implements full CRUD for firewall rules stored in PostgreSQL: create inserts a row and returns 201 with the new ID, read fetches by UUID, update applies a partial PATCH, delete soft-deletes with a deleted_at timestamp. Transactions wrap multi-table writes so a rule and its audit log entry commit atomically.

Repository layers isolate SQL from HTTP handlers — the pattern that keeps Uber's service codebase testable with an in-memory fake store.

Understanding the topic

Key concepts

• Create: INSERT ... RETURNING id for Postgres.
• Read: SELECT with WHERE and LIMIT/OFFSET pagination.
• Update: UPDATE ... WHERE id=$1 checking RowsAffected.
• Delete: hard DELETE or soft delete with deleted_at column.
• Transactions: tx.Exec multiple statements atomically.
• Repository interface enables mock swap in handler tests.

Step-by-step explanation

1. Handler validates DTO, calls service method.
2. Service applies business rules, calls repository.
3. Repository executes SQL within context timeout.
4. Create returns generated ID to handler → 201 response.
5. Update with 0 rows affected → 404 Not Found.
6. List returns slice + total count for pagination metadata.

Practical code example

Full CRUD repository for tasks with PostgreSQL and transaction:

package main

import (
	"context"
	"database/sql"
	"fmt"
	"time"
)

type Task struct {
	ID        int64
	Title     string
	Done      bool
	CreatedAt time.Time
}

type TaskRepo struct {
	db *sql.DB
}

func (r *TaskRepo) Create(ctx context.Context, title string) (Task, error) {
	const q = `INSERT INTO tasks (title, done) VALUES ($1, false) RETURNING id, title, done, created_at`
	var t Task
	err := r.db.QueryRowContext(ctx, q, title).Scan(&t.ID, &t.Title, &t.Done, &t.CreatedAt)
	return t, err
}

func (r *TaskRepo) List(ctx context.Context, limit, offset int) ([]Task, error) {
	const q = `SELECT id, title, done, created_at FROM tasks ORDER BY id LIMIT $1 OFFSET $2`
	rows, err := r.db.QueryContext(ctx, q, limit, offset)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var tasks []Task
	for rows.Next() {
		var t Task
		if err := rows.Scan(&t.ID, &t.Title, &t.Done, &t.CreatedAt); err != nil {
			return nil, err
		}
		tasks = append(tasks, t)
	}
	return tasks, rows.Err()
}

func (r *TaskRepo) MarkDone(ctx context.Context, id int64) error {
	res, err := r.db.ExecContext(ctx, `UPDATE tasks SET done=true WHERE id=$1`, id)
	if err != nil {
		return err
	}
	n, _ := res.RowsAffected()
	if n == 0 {
		return fmt.Errorf("task %d not found", id)
	}
	return nil
}

func (r *TaskRepo) Delete(ctx context.Context, id int64) error {
	res, err := r.db.ExecContext(ctx, `DELETE FROM tasks WHERE id=$1`, id)
	if err != nil {
		return err
	}
	n, _ := res.RowsAffected()
	if n == 0 {
		return fmt.Errorf("task %d not found", id)
	}
	return nil
}

Line-by-line code explanation

• tx, err := db.BeginTx(ctx, nil) starts a transaction for atomic multi-statement operations.
• defer tx.Rollback() rolls back unless Commit succeeds — safe cleanup pattern.
• result, err := tx.ExecContext(ctx, "INSERT ...", args) runs write queries.
• id, err := result.LastInsertId() retrieves auto-generated IDs (database-dependent).
• rows, err := db.QueryContext(ctx, "SELECT ...") returns multiple rows for iteration.
• defer rows.Close() releases the row cursor — check rows.Err() after the loop.
• tx.Commit() persists all changes — Rollback becomes a no-op after successful Commit.
• UPDATE ... SET col=$1 WHERE id=$2 with parameterized queries prevents SQL injection.

Key takeaway: Check RowsAffected for update/delete not-found. RETURNING avoids second query on create. Always rows.Err() after loop.

Real-world use

Where you'll use this in production

• Admin panels managing users, products, and orders.
• Todo and project management SaaS backends.
• CMS content CRUD with versioning.
• Internal tooling APIs for operations teams.

Best practices

• Repository interface per aggregate root.
• Use transactions for multi-table creates.
• Paginate list endpoints — never return unbounded rows.
• Soft delete for audit trail when regulations require.
• Return domain errors; handler maps to HTTP status.
• Integration test CRUD against real Postgres in CI.

Common mistakes

• Not checking RowsAffected — silent no-op updates.
• N+1 queries loading related entities in loop.
• Missing pagination on list — OOM on large tables.
• Expose database errors directly to API clients.
• Forget rows.Err() after rows.Next loop.

Advanced interview questions

Summary

CRUD lives in repository layer with parameterized SQL. Check RowsAffected for update/delete success. Use transactions for multi-statement atomicity. Paginate list endpoints; map domain errors to HTTP status. Next lesson: unit testing with testing package.