Appendix G. Connecting to a database
This appendix moves the Habits tracker from an in-memory store to a real PostgreSQL database to achieve persistence and reliability. It motivates the change by noting the drawbacks of volatile, in-memory data and introduces a practical, beginner-friendly path to connecting a Go service to a database. The focus is on first steps rather than exhaustive SQL coverage, using the existing project as a concrete example.
The setup starts by running PostgreSQL locally via Docker and connecting with a client using a simple URL. It then seeds a minimal schema composed of users, habits, and ticks, with foreign keys enforcing data consistency, and suggests inserting one initial user manually. On the Go side, the repository is rewritten to accept a shared database handle from database/sql, registering the pgx driver, sourcing the connection string from configuration (e.g., environment variables), opening the connection, and pinging it for liveness before injecting it into the service.
The data access layer implements three core operations: selecting many rows, selecting one row, and inserting. It demonstrates using context-aware methods (QueryContext and ExecContext), scanning result rows into Go structs, and relying on parameterized placeholders (e.g., $1) to prevent SQL injection. With foreign keys handling referential integrity, pre-checks become simpler, and time values may need formatting for PostgreSQL. The appendix closes by noting that while handwritten SQL is fine for small cases, larger or evolving schemas benefit from ORMs and query builders such as gorm, ksql, or squirrel.
G.4 Summary
- Use sql.DB, unless you need specific behaviours found in sql.Conn
- Inject dependencies from the main function
- Replace user input with variables and let the DB struct replace them with your values to prevent SQL injection
- Write your own statement or generate them with clever libraries, but don’t add complexity before you need it.
FAQ
Why shouldn’t I use in-memory storage in a real app?
Because data vanishes on server restarts and you lose durability. Also, mature database systems exist, built and maintained by experts, providing reliability, concurrency, and tooling you don’t want to reimplement.How do I start a local PostgreSQL instance with Docker?
Install Docker, then pull and run Postgres:docker pull postgres and docker run --name postgres-habits -p 5455:5432 -e POSTGRES_USER=postgres -e POSTGRES_PASSWORD=secretpassword -e POSTGRES_DB=habits-tracker -d postgres. The flags set a container name, map port 5455 to 5432, configure user/password/DB, and run detached.How do I connect to the containerized Postgres with a client?
Use a client like pgAdmin, psql, or your IDE’s DB plugin. Example URL:jdbc:postgresql://postgres:secretpassword@localhost:5455/habits-tracker. Adjust format as needed for your tool.How do I seed the database schema for the Habits tracker?
Create three tables:users, habits, and ticks. Use identity columns for IDs, and foreign keys: habits.user_id -> users.id and ticks.habit_id -> habits.id. Then insert at least one user (manually for now) so you can add habits/ticks.Why add a users table if the service doesn’t handle users yet?
Each habit belongs to one person. You generally share a single DB across users and segregate data logically (by foreign keys), not by deploying a DB per user.Which Go packages and driver should I use to connect to Postgres?
Use Go’sdatabase/sql with the pgx driver. Register it via a blank import: import _ "github.com/jackc/pgx/v5/stdlib". It’s well maintained and avoids CGO complexity.How do I open and verify a DB connection in Go?
Build a DSN (ideally from env/config), open and ping:db, err := sql.Open("pgx", dbURL); handle errors; defer db.Close(); then if err := db.Ping(); err != nil { ... } to ensure the DB is reachable. Pass *sql.DB into your repository for reuse.How do I fetch multiple rows (all habits) safely?
Use context-aware queries and scan rows: callrows, err := db.QueryContext(ctx, `SELECT id, title, weekly_frequency FROM habits ORDER BY created_on DESC`); defer rows.Close(); loop with for rows.Next(); rows.Scan(&h.ID, &h.Name, &h.WeeklyFrequency); check rows.Err() and return the slice.How do I prevent SQL injection when selecting a single habit?
Use parameterized queries with placeholders, not string concatenation. Example:row := db.QueryRowContext(ctx, `SELECT title, weekly_frequency FROM habits WHERE id = $1`, id); then row.Scan(&h.Name, &h.WeeklyFrequency). Never inject user input into SQL strings.How do I insert a tick for a habit?
Execute a parameterized INSERT:_, err := db.ExecContext(ctx, `INSERT INTO "ticks"("habit_id","created_on") VALUES($1,$2)`, id, t). Depending on settings, you may need to ensure your time.Time is compatible with PostgreSQL’s timestamp.
Learn Go with Pocket-Sized Projects ebook for free
