Part 2: Persistence with SQLite and database/sql

Make data survive a restart, and remove the global variable while we are at it.

---

Series: Build a production-grade REST API in Go · Part 2 of 17 Where we are: the in-memory API from Part 1.

In Part 1 we found that restarting the server loses everything (problem #2) and that a package-level var store makes the handlers impossible to test or reconfigure (problem #3). This post fixes both. We will:

1. store users in SQLite through Go's database/sql package
2. inject the store into the handlers instead of using a global

Why SQLite first?

SQLite is a complete SQL database in a single file, with no server to install. It is perfect for learning database/sql and for small tools. (In Part 6 we will outgrow it and move to PostgreSQL, and you will see how little changes when the code is layered well.)

We use the modernc.org/sqlite driver because it is pure Go: no C compiler (CGO) needed, so go build works everywhere and cross-compiling stays easy.

go get modernc.org/sqlite

How database/sql works (read this once)

Three ideas explain most confusion:

1. *sql.DB is not a connection. It is a connection pool. It is safe for concurrent use; create it once and share it. Never open one per request.
2. sql.Open does not connect. It only validates arguments. The first query connects, so errors appear late unless you Ping.
3. Drivers register themselves. import _ "modernc.org/sqlite" (note the blank identifier) runs the package's init(), which registers the driver under the name "sqlite". You then call sql.Open("sqlite", ...).

Step 1: the new store

Replace the contents of internal/storage/storage.go with

package storage

import (
	"context"
	"database/sql"
	"errors"
	"log"

	_ "modernc.org/sqlite"
)

type User struct {
	ID   int    `json:"id"`
	Name string `json:"name"`
}

type UserStore struct {
	db *sql.DB
}

func NewUserStore(db *sql.DB) *UserStore {
	return &UserStore{db: db}
}

func InitDB(dsn string) (*sql.DB, error) {
	db, err := sql.Open("sqlite", dsn)
	if err != nil {
		return nil, err
	}

	// SQLite works well with a single connection for this example.
	db.SetMaxOpenConns(1)

	query := `
		CREATE TABLE IF NOT EXISTS users (
			id INTEGER PRIMARY KEY AUTOINCREMENT,
			name TEXT NOT NULL
		);
	`

	if _, err := db.Exec(query); err != nil {
		db.Close()
		return nil, err
	}

	return db, nil
}

func (s *UserStore) GetAll() ([]*User, error) {
	rows, err := s.db.QueryContext(
		context.Background(),
		"SELECT id, name FROM users ORDER BY id",
	)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	users := make([]*User, 0)

	for rows.Next() {
		user := &User{}

		if err := rows.Scan(&user.ID, &user.Name); err != nil {
			return nil, err
		}

		users = append(users, user)
	}

	if err := rows.Err(); err != nil {
		return nil, err
	}

	return users, nil
}

func (s *UserStore) Create(user *User) (*User, error) {
	result, err := s.db.ExecContext(
		context.Background(),
		"INSERT INTO users (name) VALUES (?)",
		user.Name,
	)
	if err != nil {
		return nil, err
	}

	id, err := result.LastInsertId()
	if err != nil {
		return nil, err
	}

	createdUser := &User{
		ID:   int(id),
		Name: user.Name,
	}

	return createdUser, nil
}

func (s *UserStore) Update(
	id int,
	updatedUser *User,
) (*User, error) {
	result, err := s.db.ExecContext(
		context.Background(),
		"UPDATE users SET name = ? WHERE id = ?",
		updatedUser.Name,
		id,
	)
	if err != nil {
		return nil, err
	}

	rowsAffected, err := result.RowsAffected()
	if err != nil {
		return nil, err
	}

	if rowsAffected == 0 {
		return nil, errors.New("user not found")
	}

	return &User{
		ID:   id,
		Name: updatedUser.Name,
	}, nil
}

func (s *UserStore) Delete(id int) (bool, error) {
	result, err := s.db.ExecContext(
		context.Background(),
		"DELETE FROM users WHERE id = ?",
		id,
	)
	if err != nil {
		return false, err
	}

	rowsAffected, err := result.RowsAffected()
	if err != nil {
		return false, err
	}

	if rowsAffected == 0 {
		return false, errors.New("user not found")
	}

	log.Printf("deleted user with userId: %d", id)
	return true, nil
}

Read it top to bottom; there are several patterns worth learning.

InitDB opens the database and creates the table if it does not exist (CREATE TABLE IF NOT EXISTS is idempotent: safe to run on every start). SetMaxOpenConns(1) serializes access, which keeps this first version simple because SQLite allows only one writer anyway.

QueryContext vs ExecContext. Use Query* when you expect rows back (a SELECT), Exec* when you do not (INSERT, UPDATE, DELETE). Exec returns a Result giving you LastInsertId() and RowsAffected().

Placeholders (?). Look at "UPDATE users SET name = ? WHERE id = ?". We never build SQL by concatenating strings. The database receives the query and the values separately, so a name like '; DROP TABLE users; -- is just a funny string, not an attack. This single habit prevents SQL injection, the most famous web vulnerability. Never use fmt.Sprintf to put user input into SQL.

The rows loop. Three rules, all visible in GetAll:

• defer rows.Close() returns the connection to the pool; forgetting it leaks connections until the pool is exhausted.
• rows.Scan(&user.ID, &user.Name) copies columns into variables (pointers, so Scan can write to them).
• Check rows.Err() after the loop. rows.Next() returning false can mean "done" or "failed halfway". Skipping this check can silently return a truncated list.

RowsAffected() == 0 means "not found" for UPDATE and DELETE: the statement was valid but matched nothing.

Step 2: inject the store into the handlers

Instead of a package-level variable, handlers now live on a struct that receives its dependency:

internal/handlers/handlers.go (excerpt)

type Handler struct {
	store *storage.UserStore
}

func NewHandler(store *storage.UserStore) *Handler {
	return &Handler{store: store}
}

Each handler becomes a method (func (h *Handler) HandleGetUsers(...)) that calls h.store; the full file is in the code listing at the end of this post. The router now receives the handler, and main builds everything:

Change internal/routes/routes.go

--- a/internal/routes/routes.go
+++ b/internal/routes/routes.go
@@ -5,9 +5,9 @@ import (
 	"net/http"
 )
 
-func SetupRoutes(mux *http.ServeMux) {
-	mux.HandleFunc("GET /users", handlers.HandleGetUsers)
-	mux.HandleFunc("POST /users", handlers.HandleCreateUsers)
-	mux.HandleFunc("PUT /users/{id}", handlers.HandleUpdateUsers)
-	mux.HandleFunc("DELETE /users/{id}", handlers.HandleDeleteUsers)
+func SetupRoutes(mux *http.ServeMux, h *handlers.Handler) {
+	mux.HandleFunc("GET /users", h.HandleGetUsers)
+	mux.HandleFunc("POST /users", h.HandleCreateUsers)
+	mux.HandleFunc("PUT /users/{id}", h.HandleUpdateUsers)
+	mux.HandleFunc("DELETE /users/{id}", h.HandleDeleteUsers)
 }

Change cmd/rest/main.go

--- a/cmd/rest/main.go
+++ b/cmd/rest/main.go
@@ -1,14 +1,25 @@
 package main
 
 import (
+	"go-rest/internal/handlers"
 	"go-rest/internal/routes"
+	"go-rest/internal/storage"
 	"log"
 	"net/http"
 )
 
 func main() {
+	db, err := storage.InitDB("users.db")
+	if err != nil {
+		log.Fatal(err)
+	}
+	defer db.Close()
+
+	store := storage.NewUserStore(db)
+	h := handlers.NewHandler(store)
+
 	mux := http.NewServeMux()
-	routes.SetupRoutes(mux)
+	routes.SetupRoutes(mux, h)
 
 	log.Println("server running on :7800")
 

This is dependency injection, without any framework: the thing that needs a dependency receives it from whoever creates it (main). The benefits:

• Tests can build a handler with a test database.
• Changing the storage implementation touches main, not every handler.
• Dependencies are visible in constructors rather than hidden in globals.

main is the only place that knows how everything is wired together. That is why we call it the composition root.

Step 3: keep the database out of git

SQLite stores everything in a file (users.db, plus -wal and -shm companions while it runs). Databases do not belong in version control:

Create .gitignore

users.db
users.db-shm
users.db-wal
bin/

Complete code for this part

Everything you need to type, file by file. Paste them in any order, then run go mod tidy and go build ./.... (Files shown earlier in this post are not repeated.)

Already given earlier in this post: internal/routes/routes.go, internal/storage/storage.go, cmd/rest/main.go, .gitignore.

Replace the contents of internal/handlers/handlers.go with

package handlers

import (
	"encoding/json"
	"go-rest/internal/storage"
	"net/http"
	"strconv"
)

type Handler struct {
	store *storage.UserStore
}

func NewHandler(store *storage.UserStore) *Handler {
	return &Handler{store: store}
}

func (h *Handler) HandleGetUsers(w http.ResponseWriter, r *http.Request) {
	users, err := h.store.GetAll()
	if err != nil {
		http.Error(w, "Failed to fetch users", http.StatusInternalServerError)
		return
	}

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

func (h *Handler) HandleCreateUsers(w http.ResponseWriter, r *http.Request) {
	var user storage.User

	if err := json.NewDecoder(r.Body).Decode(&user); err != nil {
		http.Error(w, "Invalid JSON", http.StatusBadRequest)
		return
	}

	created, err := h.store.Create(&user)
	if err != nil {
		http.Error(w, "Failed to create user", http.StatusInternalServerError)
		return
	}

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

func (h *Handler) HandleUpdateUsers(w http.ResponseWriter, r *http.Request) {
	id, err := strconv.Atoi(r.PathValue("id"))
	if err != nil || id <= 0 {
		http.Error(w, "Invalid user ID", http.StatusBadRequest)
		return
	}

	var updates storage.User
	if err := json.NewDecoder(r.Body).Decode(&updates); err != nil {
		http.Error(w, "Invalid JSON", http.StatusBadRequest)
		return
	}

	updated, err := h.store.Update(id, &updates)
	if err != nil {
		if err.Error() == "user not found" {
			http.Error(w, "User not found", http.StatusNotFound)
			return
		}
		http.Error(w, "Failed to update user", http.StatusInternalServerError)
		return
	}

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

func (h *Handler) HandleDeleteUsers(w http.ResponseWriter, r *http.Request) {
	id, err := strconv.Atoi(r.PathValue("id"))
	if err != nil || id <= 0 {
		http.Error(w, "Invalid user ID", http.StatusBadRequest)
		return
	}

	deleted, err := h.store.Delete(id)
	if err != nil {
		if err.Error() == "user not found" {
			http.Error(w, "User not found", http.StatusNotFound)
			return
		}
		http.Error(w, "Failed to delete user", http.StatusInternalServerError)
		return
	}

	w.Header().Set("Content-Type", "application/json")
	json.NewEncoder(w).Encode(struct {
		Deleted bool `json:"deleted"`
	}{
		Deleted: deleted,
	})
}

Replace the contents of makefile with

.PHONY: run build clean db-shell

DB_FILE := users.db
BINARY := bin/server

run:
	go run cmd/rest/main.go

build:
	mkdir -p bin
	go build -o $(BINARY) cmd/rest/main.go

clean:
	rm -f $(BINARY)

db-shell:
	sqlite3 $(DB_FILE)

Try it: data survives a restart

$ make run &
$ curl -XPOST localhost:7800/users -d '{"name":"Ann"}'
{"id":1,"name":"Ann"}

$ # stop the server (Ctrl-C), start it again, then:
$ curl localhost:7800/users
[{"id":1,"name":"Ann"}]

Problem #2 solved. A users.db file now sits next to the code.

What is still wrong

Open HandleUpdateUsers in this version and you will see:

if err.Error() == "user not found" {

That compares error message strings. It works today and it will break silently the day someone rewords the message or wraps the error. Also notice the store uses context.Background(), which can never be cancelled, so a query keeps running even if the client has already disconnected. We will fix both in the next post, along with a long list of other issues.

Recap

• *sql.DB is a pool: create once, share, close at shutdown.
• Always use placeholders; never concatenate user input into SQL.
• defer rows.Close() and check rows.Err().
• Inject dependencies through constructors; main is the composition root.

Exercises

1. Add GET /users/{id} using QueryRowContext. How do you detect "no rows"? (Look up sql.ErrNoRows.)
2. Use the sqlite3 command line (make db-shell) to inspect users.db. Insert a row by hand and see it appear in the API.
3. Try SetMaxOpenConns(10) and hammer the API with concurrent writes. What error do you see, and why?