# 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.

```bash
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**

```go
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)`

```go
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`

```diff
--- 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`

```diff
--- 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`

```plaintext
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**

```go
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**

```makefile
.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

```bash
$ 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:

```go
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?