The SQL You Read Is the SQL That Runs

Part five of the boring stack. The data lives in SQLite. This post is about the code between the app and the database.

The usual choice is an ORM: you describe your tables as classes or structs, and the library writes the SQL for you. It feels productive until the first slow page, when you have to find out which of the forty queries the ORM generated is the problem and how to make it generate a different one.

I write the SQL myself.

sqlx: just enough help

Go’s standard library has database/sql, which runs queries and returns rows. Reading those rows into structs by hand is tedious. sqlx adds exactly that, and not much more:

var items []model.Item
err := r.db.SelectContext(ctx, &items,
    `SELECT id, title, created_at FROM items WHERE owner_id = ? ORDER BY created_at DESC`,
    ownerID)

The query is right there. If it’s slow, I copy it into the database console and look at the query plan. If it’s wrong, I can see why. There’s no layer in between that has its own opinion.

One repository per table

Every table gets a repository: an interface and one unexported struct that implements it, with hand-written queries. The rest of the app only talks to the interface. Services contain the business rules and call repositories. Handlers call services.

That gives every query exactly one home. When I change a table, I know which file to open.

Writes that can repeat

Some writes happen more than once by design: a webhook delivered twice, a form submitted again, a job retried after a crash. Those writes must arrive at the same result instead of failing or duplicating data.

In SQL that’s easy to express, and easy to see:

• INSERT … ON CONFLICT DO NOTHING for “make sure this exists”.
• Delete and insert in one transaction for “replace the whole list”.
• Webhook handlers apply the provider’s full current state, not just the change, so deliveries can arrive twice or out of order and still end up correct.

With an ORM, the same guarantees are often hidden behind options that read differently in every library.

Don’t store what you can compute

One rule I apply in every schema: never store a value that can be computed from other data. No stored counts, no stored “is pending” flags, no stored position numbers. Compute them when reading. A stored copy will eventually disagree with the data it was copied from, and then you have two truths.

SQL is very good at computing these on the fly. COUNT(*), a window function, a CASE: it’s usually one line.

What it costs

• More typing for simple CRUD. Inserting a struct means listing its columns. I accept that, because those lists are where mistakes become visible.
• No automatic relations. Loading an item with its comments is two queries or a join that I write. An ORM’s lazy loading would do it for me, and it would also produce the classic N+1 problem without telling me.
• You need to know SQL. I think every developer who works with a database should. The ORM doesn’t remove that need, it only postpones it to the worst moment.

When I’d choose differently

For a prototype that will be thrown away, an ORM can be faster to start. For a team that already knows one well, the switch may not be worth it. For anything I’ll maintain for years, I want to read the queries.

Next: how the schema changes over time.