Write Tests for a Go Task API with Database Isolation

Problem/Feature Description

A project management tool has a Go API for managing tasks. Tasks have a title, description, status (todo/in_progress/done), and priority (low/medium/high). The API uses SQLite for storage. The team recently discovered that their tests were passing individually but failing when run together because they shared the same database and depended on execution order.

You have been asked to write a test suite that uses proper database isolation so every test starts with a clean, known state. Each test should create its own in-memory database, run migrations, and optionally seed data. The tests should be independent and runnable in any order.

Output Specification

Produce the following files:

• handlers_test.go -- the main test file with CRUD tests
• testhelpers_test.go -- reusable setup helpers (setupTestDB, setupTestServer, makeRequest, etc.)

Input Files

The following files are provided as inputs. Extract them before beginning.

=============== FILE: go.mod =============== module taskapi

go 1.23

require github.com/mattn/go-sqlite3 v1.14.22

=============== FILE: main.go =============== package main

import ( "database/sql" "encoding/json" "log" "net/http"

_ "github.com/mattn/go-sqlite3"

)

func main() { db, err := sql.Open("sqlite3", "tasks.db") if err != nil { log.Fatal(err) } defer db.Close() runMigrations(db)

mux := setupRoutes(db)
log.Println("Server starting on :8080")
log.Fatal(http.ListenAndServe(":8080", mux))

}

func runMigrations(database *sql.DB) { _, err := database.Exec( CREATE TABLE IF NOT EXISTS tasks ( id INTEGER PRIMARY KEY AUTOINCREMENT, title TEXT NOT NULL, description TEXT DEFAULT '', status TEXT NOT NULL DEFAULT 'todo' CHECK(status IN ('todo', 'in_progress', 'done')), priority TEXT NOT NULL DEFAULT 'medium' CHECK(priority IN ('low', 'medium', 'high')), created_at DATETIME DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ) ) if err != nil { log.Fatal(err) } }

func setupRoutes(database *sql.DB) *http.ServeMux { mux := http.NewServeMux() mux.HandleFunc("GET /api/tasks", handleListTasks(database)) mux.HandleFunc("POST /api/tasks", handleCreateTask(database)) mux.HandleFunc("GET /api/tasks/{id}", handleGetTask(database)) mux.HandleFunc("PUT /api/tasks/{id}", handleUpdateTask(database)) mux.HandleFunc("DELETE /api/tasks/{id}", handleDeleteTask(database)) mux.HandleFunc("PATCH /api/tasks/{id}/status", handleUpdateStatus(database)) return mux }

func handleListTasks(database *sql.DB) http.HandlerFunc { return func(w http.ResponseWriter, r *http.Request) { status := r.URL.Query().Get("status") var rows *sql.Rows var err error if status != "" { rows, err = database.Query("SELECT id, title, description, status, priority, created_at FROM tasks WHERE status = ?", status) } else { rows, err = database.Query("SELECT id, title, description, status, priority, created_at FROM tasks") } if err != nil { writeError(w, http.StatusInternalServerError, "query failed") return } defer rows.Close()

var tasks []map[string]any
	for rows.Next() {
		var id int
		var title, desc, st, prio, createdAt string
		rows.Scan(&id, &title, &desc, &st, &prio, &createdAt)
		tasks = append(tasks, map[string]any{
			"id": id, "title": title, "description": desc,
			"status": st, "priority": prio, "created_at": createdAt,
		})
	}
	if tasks == nil {
		tasks = []map[string]any{}
	}
	writeJSON(w, http.StatusOK, map[string]any{"data": tasks})
}

}

func handleCreateTask(database *sql.DB) http.HandlerFunc { return func(w http.ResponseWriter, r *http.Request) { var body struct { Title string json:"title" Description string json:"description" Priority string json:"priority" } if err := json.NewDecoder(r.Body).Decode(&body); err != nil { writeError(w, http.StatusBadRequest, "invalid JSON") return } if body.Title == "" { writeError(w, http.StatusBadRequest, "title is required") return } if body.Priority == "" { body.Priority = "medium" }

result, err := database.Exec(
		"INSERT INTO tasks (title, description, priority) VALUES (?, ?, ?)",
		body.Title, body.Description, body.Priority,
	)
	if err != nil {
		writeError(w, http.StatusInternalServerError, "insert failed")
		return
	}
	id, _ := result.LastInsertId()
	writeJSON(w, http.StatusCreated, map[string]any{
		"data": map[string]any{
			"id": id, "title": body.Title, "description": body.Description,
			"status": "todo", "priority": body.Priority,
		},
	})
}

}

func handleGetTask(database *sql.DB) http.HandlerFunc { return func(w http.ResponseWriter, r *http.Request) { id := r.PathValue("id") var title, desc, status, priority, createdAt string err := database.QueryRow( "SELECT title, description, status, priority, created_at FROM tasks WHERE id = ?", id, ).Scan(&title, &desc, &status, &priority, &createdAt) if err != nil { writeError(w, http.StatusNotFound, "task not found") return } writeJSON(w, http.StatusOK, map[string]any{ "data": map[string]any{ "id": id, "title": title, "description": desc, "status": status, "priority": priority, "created_at": createdAt, }, }) } }

func handleUpdateTask(database *sql.DB) http.HandlerFunc { return func(w http.ResponseWriter, r *http.Request) { id := r.PathValue("id") var body struct { Title string json:"title" Description string json:"description" Priority string json:"priority" } if err := json.NewDecoder(r.Body).Decode(&body); err != nil { writeError(w, http.StatusBadRequest, "invalid JSON") return } result, err := database.Exec( "UPDATE tasks SET title = COALESCE(NULLIF(?, ''), title), description = COALESCE(NULLIF(?, ''), description), priority = COALESCE(NULLIF(?, ''), priority), updated_at = CURRENT_TIMESTAMP WHERE id = ?", body.Title, body.Description, body.Priority, id, ) if err != nil { writeError(w, http.StatusInternalServerError, "update failed") return } rows, _ := result.RowsAffected() if rows == 0 { writeError(w, http.StatusNotFound, "task not found") return } writeJSON(w, http.StatusOK, map[string]any{"data": map[string]any{"id": id, "updated": true}}) } }

func handleDeleteTask(database *sql.DB) http.HandlerFunc { return func(w http.ResponseWriter, r *http.Request) { id := r.PathValue("id") result, err := database.Exec("DELETE FROM tasks WHERE id = ?", id) if err != nil { writeError(w, http.StatusInternalServerError, "delete failed") return } rows, _ := result.RowsAffected() if rows == 0 { writeError(w, http.StatusNotFound, "task not found") return } w.WriteHeader(http.StatusNoContent) } }

func handleUpdateStatus(database *sql.DB) http.HandlerFunc { return func(w http.ResponseWriter, r *http.Request) { id := r.PathValue("id") var body struct { Status string json:"status" } if err := json.NewDecoder(r.Body).Decode(&body); err != nil { writeError(w, http.StatusBadRequest, "invalid JSON") return } if body.Status != "todo" && body.Status != "in_progress" && body.Status != "done" { writeError(w, http.StatusBadRequest, "status must be todo, in_progress, or done") return } result, err := database.Exec("UPDATE tasks SET status = ?, updated_at = CURRENT_TIMESTAMP WHERE id = ?", body.Status, id) if err != nil { writeError(w, http.StatusInternalServerError, "update failed") return } rows, _ := result.RowsAffected() if rows == 0 { writeError(w, http.StatusNotFound, "task not found") return } writeJSON(w, http.StatusOK, map[string]any{"data": map[string]any{"id": id, "status": body.Status}}) } }

func writeJSON(w http.ResponseWriter, status int, data any) { w.Header().Set("Content-Type", "application/json") w.WriteHeader(status) json.NewEncoder(w).Encode(data) }

func writeError(w http.ResponseWriter, status int, message string) { writeJSON(w, status, map[string]any{"error": map[string]any{"message": message}}) }