Skip to content
Tutorials Center
My learning

Topics

Lesson 121 of 147 ยท Databases

GORM, sqlx and Query Builders

Compare GORM, sqlx and SQL builders with plain database/sql - models, joins, preloading, pagination, concurrent queries, and Postgres versus MySQL differences, with runnable stand-ins.

In database/sql you wrote SQL by hand, scanned rows into variables and managed transactions yourself. That is precise and fast, but repetitive. Go has a range of tools that reduce the repetition, from thin helpers to full object-relational mappers (ORMs). This lesson compares the main options, shows their typical code, and explains the problems every one of them has to solve: joins, the N+1 query trap, pagination and concurrent queries.

The code runner on this site has no database driver and no third-party modules. GORM, sqlx and squirrel code is therefore read-only reference code, while the ideas are demonstrated with runnable programs that use only the standard library (a tiny query builder and an in-memory fake database that counts queries). Check each library's documentation for current versions and exact signatures.

The spectrum of tools

Approach Example You write The tool does
Plain database/sql standard library all SQL and all Scan calls connections, pooling, transactions
Helper over database/sql sqlx, scany all SQL scanning rows into structs
Query builder squirrel, goqu, bun builder SQL in Go method calls placeholders, dialect differences
Code generator sqlc SQL files type-safe Go functions generated at build time
Full ORM GORM, ent Go structs and method chains SQL generation, relations, migrations, hooks

There is no single right choice. Many Go teams prefer the middle of the table, because SQL stays visible and the generated or scanned code stays simple. The Go community has a long tradition of being careful with ORMs, since magic that hides queries makes performance problems hard to find.

sqlx: scanning into structs

sqlx extends database/sql with struct scanning by column name. The query is still your own SQL:

Go
type User struct {
	ID    int64  `db:"id"`
	Name  string `db:"name"`
	Email string `db:"email"`
}

var users []User
err := db.SelectContext(ctx, &users,
	`SELECT id, name, email FROM users WHERE active = $1 ORDER BY name`, true)

var u User
err = db.GetContext(ctx, &u, `SELECT id, name, email FROM users WHERE id = $1`, 7)

// Named parameters map struct fields to :placeholders
_, err = db.NamedExecContext(ctx,
	`INSERT INTO users (name, email) VALUES (:name, :email)`, u)

This removes the rows.Next() and Scan boilerplate and nothing else, which is exactly why people like it. The db struct tag plays the same role as the json tag does for encoding/json.

GORM: models and method chains

GORM is the most widely used ORM. You describe tables as structs and build queries with chained methods:

Go
type Author struct {
	ID    uint
	Name  string
	Books []Book // has-many
}

type Book struct {
	ID       uint
	Title    string `gorm:"size:200;not null"`
	AuthorID uint
	Author   Author // belongs-to
	Price    int
}

db.AutoMigrate(&Author{}, &Book{})

// Create
db.Create(&Author{Name: "Ursula", Books: []Book{{Title: "Earthsea", Price: 1200}}})

// Read with a condition, ordering and a limit
var books []Book
db.Where("price < ?", 2000).Order("title").Limit(10).Find(&books)

// Preload a relation: one extra query instead of one per row
db.Preload("Author").Find(&books)

// Join in SQL and select columns
db.Joins("JOIN authors ON authors.id = books.author_id").
	Where("authors.name = ?", "Ursula").Find(&books)

// Update and delete
db.Model(&Book{}).Where("id = ?", 1).Update("price", 1500)
db.Delete(&Book{}, 3)

// Transaction
err := db.Transaction(func(tx *gorm.DB) error {
	if err := tx.Create(&Author{Name: "Octavia"}).Error; err != nil {
		return err // rolls back
	}
	return nil // commits
})

GORM brings migrations, hooks (BeforeSave), soft deletes (a DeletedAt field), associations and many dialects. The price is that you have to know what SQL it generates; turn on its logger in development and read what it prints. Errors are on result.Error, not in a second return value, so it is easy to forget to check them. Never pass user input directly into the first argument of Where as a string: use the ? placeholder form, otherwise you create an SQL injection.

Query builders and sqlc

A query builder assembles SQL from method calls and keeps the values separate as arguments. squirrel looks like this:

Go
sql, args, _ := sq.Select("id", "title").
	From("books").
	Where(sq.Lt{"price": 2000}).
	OrderBy("title").
	Limit(10).
	PlaceholderFormat(sq.Dollar). // $1, $2 for Postgres
	ToSql()

Builders shine for dynamic queries, such as a search form where any filter may be absent. sqlc goes the other direction: you write real SQL in .sql files with a name comment, and it generates type-checked Go methods at build time, so a typo in a column name fails the build instead of production. Both keep the SQL honest. Next, a runnable query builder written with the standard library shows what such tools do under the hood.

Go A tiny query builder with placeholders

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with bufio.Scanner or fmt.Scan), it gets the next line from this box.

The first query has no WHERE at all, the second has one condition, and the third has two. The hostile author name appears only in the args list, never inside the SQL text; that separation is the whole point of placeholders and the reason query builders and ORMs are safe against injection as long as you use them correctly. The last line shows the dialect difference: Postgres numbers its placeholders $1, $2, whereas MySQL and SQLite use ?.

The N+1 query problem and preloading

An ORM makes it easy to write code that is correct and terribly slow. Suppose you list 100 books and print each author's name. If the author is loaded lazily, row by row, you run one query for the books and then one for each author: 101 queries. This is the N+1 problem. The cure is to load all the authors with a single query, either with a JOIN or with a second query using WHERE id IN (...), which GORM calls preloading.

Go N+1 queries versus one batched query

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with bufio.Scanner or fmt.Scan), it gets the next line from this box.

The lazy version prints 13 (one plus twelve) and the preloaded version prints 2. The gap grows with the data: for 1,000 books the lazy version would run 1,001 queries. When a page feels slow, the first thing to check is the number of queries per request. SQL patterns shows more ways to look at this.

Pagination

Never return an unlimited list. Two techniques exist. Offset pagination uses LIMIT 20 OFFSET 40; it is simple and allows jumping to page N, but the database still reads and discards the skipped rows, and results can shift if rows are inserted meanwhile. Keyset pagination (also called cursor pagination) remembers the last seen key and asks for rows after it: WHERE id > $1 ORDER BY id LIMIT 20. It uses an index and stays fast on page 10,000.

Go Offset versus keyset pagination

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with bufio.Scanner or fmt.Scan), it gets the next line from this box.

The offset pages print [40 50 60], [100] and []. The keyset loop prints three pages, [10 20 30 40], [50 60 70 80] and [90 100], each started from the last id of the page before. An API using keyset pagination returns a next_cursor value in its response instead of a page number.

Concurrent queries

A request that needs three independent pieces of data (the user, their orders, their notifications) does not have to fetch them one after the other. A database/sql pool is safe for concurrent use, so you can run the queries in goroutines and wait for all of them. The errgroup package from golang.org/x/sync is the usual helper; the standard-library version below does the same with a WaitGroup, and cancels on the first error through a context.

Go Running independent queries concurrently

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with bufio.Scanner or fmt.Scan), it gets the next line from this box.

The first call finishes in about the time of the slowest query (400 ms), not the sum of all three, and prints [user ok orders ok notifications ok] <nil>. In the second call orders fails after 60 ms; the first error is kept and the context is canceled, so notifications stops early and the lesson prints the orders failed error and the partial results [user ok ]. Pool size matters here: a database/sql pool holds a limited number of connections (SetMaxOpenConns), so ten thousand goroutines will wait for connections rather than run in parallel.

Postgres and MySQL notes

Whatever tool you pick, the dialect still shows through:

Topic PostgreSQL MySQL
Placeholders $1, $2 ?
Return the new id INSERT ... RETURNING id result.LastInsertId()
Upsert ON CONFLICT (col) DO UPDATE ON DUPLICATE KEY UPDATE
Quoting identifiers "name" `name`
Typical driver pgx (github.com/jackc/pgx) go-sql-driver/mysql
Case-insensitive match ILIKE LIKE (depends on collation)

ORMs and builders hide many of these, but not all, so test against the database you deploy on. Use migrations (GORM's AutoMigrate is fine for prototypes, versioned migration files with a tool such as goose or golang-migrate are better for production), and always pass a context so slow queries can be canceled.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What does sqlx add to database/sql?

    sqlx is a thin helper. It removes the Scan boilerplate but leaves the SQL in your hands.
  2. 2. Why is it safe that the hostile author name only shows up in the args list of the query builder?

    Placeholders separate SQL text from data. The driver or server treats the arguments strictly as values, which prevents SQL injection.
  3. 3. A page lists 50 orders and loads each customer lazily. How many queries run?

    That is the N+1 problem: one query for the orders plus one per order. Preloading or a JOIN brings it back to 1 or 2.
  4. 4. Which pagination style stays fast on a very deep page?

    Keyset pagination uses an index to jump straight to the next rows, while a large OFFSET makes the database read and discard all skipped rows.

Summary

  • Go offers a spectrum: plain database/sql, helpers such as sqlx, query builders, sqlc code generation and full ORMs such as GORM.
  • Pick the thinnest tool that removes your pain, and keep the generated SQL visible.
  • Always use placeholders for values; never build SQL by concatenating user input.
  • Watch the number of queries per request: preload relations or join to avoid N+1.
  • Paginate with LIMIT/OFFSET for small lists and keyset cursors for deep ones.
  • Independent queries can run concurrently with goroutines and a context, within the connection pool's limit.
  • Placeholders, RETURNING, upserts and quoting differ between PostgreSQL and MySQL, so test on your real database.

Next: Redis and NoSQL moves beyond relational databases.