Go · Chapter 23 of 26
Postgres from Go
By the end of this chapter you can talk to Postgres from Go with pgx: open a pool, read one row with QueryRow and many with Query, change rows with Exec, scan columns into struct fields, pass values with $1 so SQL injection cannot happen, tell "no row" from a real error, put a time limit on a query with a context, and make several writes one transaction that happens whole or not at all. Build it in your browser: a real editor and terminal, AI that explains, and tests that tell you when it works.
- Part: Services
- 4 tasks
- Go
- In the all labs plan
The first 2 chapters of Go are free. This one comes with the all labs plan.
Why you need this
Everything so far has read BijliGo's data from bijligo/bijli, a copy that lives inside your program. The real data lives in Postgres: 240 stations, 1,180 chargers, 2,000 drivers with their wallets, Wednesday's 3,600 sessions. Pooja needs a driver's sessions when someone calls about a bill, and she charges each session to the driver's wallet, which must never be charged twice or go below zero. Tenzin needs the support desk's search to stop failing on names with an apostrophe.
Your lab has Postgres running with all of it in a database called bijligo (lab db opens psql on it; lab db reset puts the data back). What Go needs is a way to send SQL, get rows back as Go values, and keep two writes together when a wallet and a ledger must agree.
What you build: 4 tasks
Each task has tests. You write the code in the lab's editor and press Check.
- Task 1
charger.go: one charger from the database
In charger.go, write ChargerByID(ctx context.Context, pool *pgxpool.Pool, id string) (Charger, error). It reads the row of the chargers table whose id is id into a Charger: ID from id, Station from station_id, Kind from kind, PowerKW from power_kw, PaisePerKWh from paise_per_kwh, Status from status.
ChargerByID(ctx, pool, "C1051") returns {C1051 ST009 AC7 7.4 1400 available} and a nil error. The Check first sets C1019's status to offline in its database, so read the database, not bijli's data.
For an id with no row (C9999, and C1051' OR '1'='1) it returns an error that wraps ErrNotFound (errors.Is finds it) and has the id in its message. pgx says "no row" with its own error, pgx.ErrNoRows; other errors are passed on.
- Task 2
sessions.go: a driver's sessions, oldest first
In sessions.go, write DriverSessions(ctx context.Context, pool *pgxpool.Pool, driverID string) ([]Session, error). It returns every row of the sessions table whose driver_id is driverID, as Sessions ordered by started_at (then by id): ID from id, Charger from charger_id, StartedAt from started_at, EnergyWh from energy_wh, CostPaise from cost_paise, Status from status.
DriverSessions(ctx, pool, "D11142") returns 8 sessions, S901346 first. The Check first sets S901346's energy_wh to 12345 in its database, so read the database. For D10004, who has no sessions, it returns an empty list and a nil error. With a cancelled ctx it returns an error that errors.Is finds as context.Canceled.
- Task 3
charge.go: charge a session to a wallet, both or neither
Pooja charges each session to its driver's wallet. In charge.go, write ChargeSession(ctx context.Context, pool *pgxpool.Pool, sessionID string) (int, error). In one transaction it reads the session's driver_id and cost_paise, adds a row to wallet_charges (session_id, driver_id, amount_paise; its primary key is session_id, and charged_at fills itself in), takes the cost from that driver's wallet_paise in the drivers table, and returns the wallet's new balance. ChargeSession(ctx, pool, "S900001") returns 85815 (D11757 had 87500, the session cost 1685).
Errors, each wrapped so that errors.Is finds it, and each changing nothing at all: no such session (S999999) is ErrNotFound; a session already in wallet_charges is ErrAlreadyCharged (the wallet stays as it was); a wallet that holds less than the cost is ErrLowBalance, with no row left in wallet_charges (S901212 costs 3234 and D10233's wallet holds 0).
The Check makes the table wallet_charges in its fresh database (main.go makes it in yours), and also charges S900002, S900005 and S900004 from ten goroutines at once each: exactly one call charges, nine get ErrAlreadyCharged, and the wallet goes down once.
- Task 4 · interview question
Interview: search.go, the search that broke
Tenzin wrote DriversNamed(ctx context.Context, pool *pgxpool.Pool, name string) ([]string, error) for the support desk: the ids of the drivers whose name is exactly name, in id order. DriversNamed(ctx, pool, "Sai Rao") returns 8 ids. For a driver the Check renames Anil D'Souza (D10013), it fails with a syntax error, and for the "name" Sai Rao' OR '1'='1 it returns all 2000 drivers. Fix it so that every name is only a name: Anil D'Souza returns [D10013], Sai Rao' OR '1'='1 returns no ids, and neither returns an error.
Joseph will ask: why $1 and never fmt.Sprintf in SQL? What does Postgres receive in each case?
What you will learn
- pgxpool: a pool of connections to Postgres, safe for many goroutines.
- QueryRow and Scan: one row into Go variables or struct fields.
- pgx.ErrNoRows: the error that means "there was no row".
- Query and rows.Next: many rows, one at a time.
- Exec: a write, and how many rows it changed.
- $1 placeholders: values sent apart from the SQL, so injection cannot happen.
- Transactions: Begin, Commit, and Rollback with defer.
More in Services
Build "Postgres from Go" yourself
BijliGo, a Bengaluru EV charging network, drops chargers at the evening peak. You rewrite its backend in Go. A real editor and terminal in your browser, AI that explains, and a Check that says when it works.
The first 2 chapters of Go are free. This one comes with the all labs plan.