Database-Backed Task Management API

Learn database-backed task management api through an executable example, then connect the small demonstration to production code.

A database-backed API stores tasks beyond a process restart. In Go, database/sql uses a configured driver and connection pool. Handlers should depend on a repository interface so their behavior can be tested without a live database.

The primary example uses an in-memory repository to exercise the HTTP boundary; the SQL schema and real repository method are shown afterward and require driver configuration.

Follow this diagram before executing the example. Check where a value or decision changes what runs next.

SQL table and repository pattern

CREATE TABLE tasks (
    id BIGINT PRIMARY KEY,
    title TEXT NOT NULL
);

type SQLRepo struct { DB *sql.DB }

func (s SQLRepo) Find(ctx context.Context, id int64) (Task, error) {
    var task Task
    err := s.DB.QueryRowContext(ctx,
        "SELECT id, title FROM tasks WHERE id = ?", id,
    ).Scan(&task.ID, &task.Title)
    return task, err
}

This repository fragment is not a standalone program. Import context and database/sql, use a suitable int64 ID type, add a real SQL driver, adapt placeholders to your database (for example, PostgreSQL commonly uses $1), pass r.Context(), and translate sql.ErrNoRows into HTTP 404. See Go database documentation.

Full project: implement migrations, create/list/update/delete endpoints, transactions where needed, validation, authorization, integration tests and backup/restore procedures.

This example is a complete program. Save it as main.go or paste it into the Go Playground.

Go code

package main

import (
	"encoding/json"
	"fmt"
	"net/http"
	"net/http/httptest"
)

type Task struct {
	ID    int    `json:"id"`
	Title string `json:"title"`
}
type Repository interface{ Find(int) (Task, bool) }
type MemoryRepo map[int]Task

func (m MemoryRepo) Find(id int) (Task, bool) { t, ok := m[id]; return t, ok }
func handler(repo Repository) http.HandlerFunc {
	return func(w http.ResponseWriter, r *http.Request) {
		t, ok := repo.Find(1)
		if !ok {
			http.NotFound(w, r)
			return
		}
		w.Header().Set("Content-Type", "application/json")
		json.NewEncoder(w).Encode(t)
	}
}
func main() {
	repo := MemoryRepo{1: {ID: 1, Title: "Persist tasks"}}
	recorder := httptest.NewRecorder()
	handler(repo)(recorder, httptest.NewRequest("GET", "/tasks/1", nil))
	fmt.Println("Status:", recorder.Code)
	fmt.Print(recorder.Body.String())
}

Output

Status: 200
{"id":1,"title":"Persist tasks"}

The handler asks its Repository for task 1; the MemoryRepo returns it and the handler serializes it as JSON. This validates handler wiring but does not claim to persist anything to SQL.

• Do not interpret success with a fake repository as proof that SQL transactions or constraints work.
• Configure a real database driver, migration strategy, connection lifecycle and resource-level authorization before deployment.

Implement SQLRepo.Find using QueryRowContext and write a database integration test with a test database.

Tip: Edit the example above, predict the output, and run the modified code to test your reasoning.

You have practiced Database-Backed Task Management API in a small, self-contained program. The important habit is to predict what runs, inspect the actual output and distinguish the language rule from the particular example.

Before continuing, make sure you can explain the program to someone who has not seen it, including the line that changes its result.