Database Access with database/sql

package main

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

  _ "modernc.org/sqlite" // driver registers itself with database/sql
)

// --- User struct ---
type User struct {
  ID        int       `json:"id"`
  Name      string    `json:"name"`
  Email     string    `json:"email"`
  CreatedAt time.Time `json:"created_at"`
}

// --- Server holds dependencies ---
type Server struct {
  db *sql.DB
}

// --- Handlers (methods on Server) ---

func (s *Server) listUsers(w http.ResponseWriter, r *http.Request) {
  rows, err := s.db.QueryContext(r.Context(),
      "SELECT id, name, email, created_at FROM users ORDER BY id")
  if err != nil {
      http.Error(w, err.Error(), http.StatusInternalServerError)
      return
  }
  defer rows.Close()

  var users []User
  for rows.Next() {
      var u User
      if err := rows.Scan(&u.ID, &u.Name, &u.Email, &u.CreatedAt); err != nil {
          http.Error(w, err.Error(), http.StatusInternalServerError)
          return
      }
      users = append(users, u)
  }
  if err := rows.Err(); err != nil {
      http.Error(w, err.Error(), http.StatusInternalServerError)
      return
  }

  w.Header().Set("Content-Type", "application/json")
  json.NewEncoder(w).Encode(users)
}

func (s *Server) getUser(w http.ResponseWriter, r *http.Request) {
  id := r.PathValue("id")

  var u User
  err := s.db.QueryRowContext(r.Context(),
      "SELECT id, name, email, created_at FROM users WHERE id = ?", id).
      Scan(&u.ID, &u.Name, &u.Email, &u.CreatedAt)

  if err == sql.ErrNoRows {
      http.Error(w, "user not found", http.StatusNotFound)
      return
  }
  if err != nil {
      http.Error(w, err.Error(), http.StatusInternalServerError)
      return
  }

  w.Header().Set("Content-Type", "application/json")
  json.NewEncoder(w).Encode(u)
}

func (s *Server) createUser(w http.ResponseWriter, r *http.Request) {
  var input struct {
      Name  string `json:"name"`
      Email string `json:"email"`
  }
  if err := json.NewDecoder(r.Body).Decode(&input); err != nil {
      http.Error(w, "invalid JSON", http.StatusBadRequest)
      return
  }
  defer r.Body.Close()

  result, err := s.db.ExecContext(r.Context(),
      "INSERT INTO users (name, email, created_at) VALUES (?, ?, ?)",
      input.Name, input.Email, time.Now())
  if err != nil {
      http.Error(w, err.Error(), http.StatusInternalServerError)
      return
  }

  id, _ := result.LastInsertId()

  // Fetch the full row we just created
  var u User
  s.db.QueryRowContext(r.Context(),
      "SELECT id, name, email, created_at FROM users WHERE id = ?", id).
      Scan(&u.ID, &u.Name, &u.Email, &u.CreatedAt)

  w.Header().Set("Content-Type", "application/json")
  w.WriteHeader(http.StatusCreated)
  json.NewEncoder(w).Encode(u)
}

// --- Main ---

func main() {
  // Open the database (creates the file if it doesn't exist)
  db, err := sql.Open("sqlite", "app.db")
  if err != nil {
      log.Fatal(err)
  }
  defer db.Close()

  // Verify we can actually reach the database
  ctx, cancel := context.WithTimeout(context.Background(), 5*time.Second)
  defer cancel()
  if err := db.PingContext(ctx); err != nil {
      log.Fatal("cannot ping database:", err)
  }

  // Create the table if it doesn't exist
  _, err = db.ExecContext(ctx, `CREATE TABLE IF NOT EXISTS users (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      name TEXT NOT NULL,
      email TEXT NOT NULL UNIQUE,
      created_at DATETIME NOT NULL
  )`)
  if err != nil {
      log.Fatal("cannot create table:", err)
  }

  // Seed some data if the table is empty
  var count int
  db.QueryRowContext(ctx, "SELECT COUNT(*) FROM users").Scan(&count)
  if count == 0 {
      db.ExecContext(ctx, "INSERT INTO users (name, email, created_at) VALUES (?, ?, ?)",
          "Alice", "alice@example.com", time.Now())
      db.ExecContext(ctx, "INSERT INTO users (name, email, created_at) VALUES (?, ?, ?)",
          "Bob", "bob@example.com", time.Now())
      log.Println("Seeded 2 users")
  }

  s := &Server{db: db}

  mux := http.NewServeMux()
  mux.HandleFunc("GET /users", s.listUsers)
  mux.HandleFunc("GET /users/{id}", s.getUser)
  mux.HandleFunc("POST /users", s.createUser)

  log.Println("Server starting on http://localhost:8080")
  log.Fatal(http.ListenAndServe(":8080", mux))
}

go run main.go

# List all users (comes back with seeded data)
curl http://localhost:8080/users

# Get a single user by ID
curl http://localhost:8080/users/1

# Create a new user
curl -X POST http://localhost:8080/users \
-H "Content-Type: application/json" \
-d '{"name":"Charlie","email":"charlie@example.com"}'

# Verify it persisted — list users again
curl http://localhost:8080/users

# Try fetching a user that doesn't exist
curl http://localhost:8080/users/999

# macOS comes with sqlite3 preinstalled. Try it:
sqlite3 app.db ".tables"          # list tables
sqlite3 app.db "SELECT * FROM users;"  # query data
sqlite3 app.db ".schema users"    # see the table schema

// Add above main()

type ctxKey string
const ctxKeyAPIKey ctxKey = "api_key"

func apiKeyMiddleware(next http.Handler) http.Handler {
  return http.HandlerFunc(func(w http.ResponseWriter, r *http.Request) {
      key := r.Header.Get("X-API-Key")
      if key != "super-secret" {
          http.Error(w, "unauthorized", http.StatusUnauthorized)
          return
      }
      ctx := context.WithValue(r.Context(), ctxKeyAPIKey, key)
      next.ServeHTTP(w, r.WithContext(ctx))
  })
}

// --- Update your main() to use it: ---

func main() {
  // ... db setup stays the same ...

  mux := http.NewServeMux()
  mux.HandleFunc("GET /users", s.listUsers)
  mux.HandleFunc("GET /users/{id}", s.getUser)

  // Protected routes — only POST needs the API key
  protected := http.NewServeMux()
  protected.HandleFunc("POST /users", s.createUser)
  mux.Handle("/", apiKeyMiddleware(protected))

  log.Fatal(http.ListenAndServe(":8080", mux))
}

# GET still works without auth (public endpoint)
curl http://localhost:8080/users

# POST without API key — blocked with 401
curl -X POST http://localhost:8080/users \
-H "Content-Type: application/json" \
-d '{"name":"Dave","email":"dave@example.com"}'

# POST with valid API key — works
curl -X POST http://localhost:8080/users \
-H "Content-Type: application/json" \
-H "X-API-Key: super-secret" \
-d '{"name":"Dave","email":"dave@example.com"}'