Introduction
I have been programming web backends for longer than I care to remember. Up until recently, I used C# extensively because it came closest to the sweet spot I was looking for: a language that was relatively easy to learn but still offered good performance. However, a few years ago, I discovered Go. For me, Go represents the ideal combination of a highly performant language that remains accessible and relatively easy to pick up.
When I recently sat down to build a project—a web API for a library system —I chose PostgreSQL to store my users and books. PostgreSQL is incredibly powerful and has become the standard for many modern projects. But when it came to connecting my shiny new Go API to this database, I made a deliberate architectural choice: I decided not to use an ORM.
Here is why I left ORMs behind, and how I use sqlc to make working with raw SQL an absolute breeze.
However this article just my opinion, and I personally won’t be ditching ORMs completely as in some situations they can be quite time-saving and handy.
If you want to get more Developer news, please subscribe to my newsletter and receive a free PDF
The Case Against ORM
Object-Relational Mapping (ORM) tools are designed to make a developer’s life easier. By mapping database tables and views directly to objects within your code, ORMs make writing actual SQL almost obsolete.
However, for my Go projects, I have chosen to bypass them entirely. My reasoning boils down to three main points:
- Ultimate Expressiveness: In my experience, writing raw SQL can be much more expressive than wrestling with complex ORM abstractions.
- Raw Efficiency: Using raw SQL is in many cases much more efficient. By dropping the ORM we eliminate the heavy translation step which usually occurs between thge object model and the database, which can increase the performance.
- Flexibility: In the system that I built I came across two problems: the first one is one of concurrency, a so-called TOCTOU problems, or ‘Time Of Check, Time Of Use’ condition, which in our concrete case meant that if a book looked available to be borrowed that could change once the borrow action was done, and the book could be be borrowed to two users. That problem was solved using transactions, but to do that I had to lock the book row, so I could update its status. Ever heard of ‘SELECT * FROM table FOR UPDATE’? Well, that was easily implemented using raw SQL. The other problem was that I wanted to keep a borrow history, even when the user or the book had been deleted. This meant that I had to introduce a soft delete. By adding a
deleted_atfield to two tables and slightly changing two queries I could implement that without changing a bit of the Go code, very handy and quite impressive.
The Sweet Spot: pgx and sqlc
Instead of a bulky ORM, my toolchain of consists of sqlc and pgx, with goose to handle migrations.
First, we need a way to talk to PostgreSQL. For that I use pgx/v5 which is a high-performance PostgreSQL driver for Go. It provides excellent features out of the box including connection pooling for efficient database access.
The real magic, however, comes from sqlc. It is important to that sqlc is a command-line tool, not a module or a Go package. It allows you to write raw SQL while still giving you the safety and convenience of native Go code.
How sqlc Works in Practice
To keep things organized, it is best practice to keep your SQL queries separate from the rest of your Go application code.
With sqlc, you start by creating a simple sqlc.yaml configuration file in the root of your project. This file tells sqlc exactly where to find two critical things:
- Your schema: The directory containing your database migration files (like your table definitions).
- Your Queries: The directory containing your raw SQL files (your
SELECT,INSERT,UPDATE, andDELETEstatements).
Instead of chaining Go methods to build a query, you write pure SQL. For example, in a books.sql file, you might write an INSERT statement and simply tag it with a comment like -- name: CreateBook :one.
Once your SQL is written, you simply drop into your terminal and run:
sqlc generateThe Magic of Code Generation
When you run that command, sqlc reads both your schema and your SQL queries to fully understand your database structure. It then automatically generates the appropriate, type-safe Go code for you.
By default, I have it output to an internal/database directory, where it creates files like models.go and querier.go.
This generated code is incredibly powerful. Depending on how you configure your sqlc.yaml, it can do the following:
- Handle Nulls Gracefully: It utilizes
pgx-specific types, such aspgtype.Textandpgtype.Int4, to safely manage nullable database fields. - API-Ready Structs: It can automatically append JSON tags to all generated structs, making it trivial to serialize your database models directly to or from JSON in your HTTP handlers.
- Built-in Security & Speed: It generates code that utilizes prepared statements. This allows the database to cache query execution plans for better performance, while simultaneously preventing SQL injection attacks.
- Effortless Testing: It can create a
Querierinterface that contains all of your generated database methods. This is a massive win for testing, as you can easily create mock implementations of this interface without ever touching a live database.
Conclusion
Writing Go code is an absolute joy. By pairing Go with PostgreSQL, pgx, and sqlc, you get the best of both worlds. You retain the absolute power, expressiveness, and performance of raw SQL, without sacrificing type safety or spending hours writing boilerplate data-access code. If you are starting a new Go API, I highly recommend leaving the ORM behind and giving this stack a try.
Want to know more?
I have written a series of three books about setting up a Web API with Go, from scratch to a Kubernetes deployment, and it touches on subject such as hardening the API, concurrency, testing, graceful shutdown etc.:
