Skip to main content

Type-safe SQL in Go: the options

A query can go wrong in three ways that a type system could catch: a column that doesn't exist, a value of the wrong type for it, and a result scanned into the wrong type. Go libraries catch these at different times, at build time, when a generator runs, or only when the query runs, and they ask for different things in return. There are three broad approaches.

Each example below asks for users over 18, sorted by name, ten at a time.

Generate code from a schema or from SQL​

A tool reads a description of your data and writes Go code that knows every table and column. Mistakes surface when the generator runs or when the generated code is compiled. In return, the generator has to run again after each change.

  • sqlc starts from SQL. You write each query in a .sql file, and sqlc generate checks it against your schema and writes a typed Go function for it.
  • ent starts from Go. You describe the schema with ent's DSL, and go generate writes a query builder with a method for each field.
  • jet and bob start from the database. Their generators read your schema and write typed tables and columns for a query builder that mirrors SQL.
sqlc: query.sql
-- name: AdultUsers :many
SELECT * FROM users
WHERE age > $1
ORDER BY name
LIMIT 10;

From this, sqlc generate writes an AdultUsers method that takes the age and returns a slice of User rows. A misspelled column fails sqlc generate.

ent 0.14
users, err := client.User.Query().
Where(user.AgeGT(18)).
Order(user.ByName()).
Limit(10).
All(ctx)

user.AgeGT exists because ent generated it from the schema, and it takes an int.

Build queries with strings​

A builder assembles the SQL, and you name columns with strings. Nothing is generated and nothing is checked before the query runs: a typo is an error from the database, found by a test or in production. Values are still passed as parameters, so this is about mistakes, not injection.

squirrel 1.5
rows, err := sq.Select("id", "name").
From("users").
Where(sq.Gt{"age": 18}).
OrderBy("name").
Limit(10).
PlaceholderFormat(sq.Dollar).
RunWith(db).
QueryContext(ctx)
GORM 1.31
var users []User

err := db.WithContext(ctx).
Where("age > ?", 18).
Order("name").
Limit(10).
Find(&users).
Error
Bun 1.3
var users []User

err := db.NewSelect().
Model(&users).
Where("age > ?", 18).
Order("name").
Limit(10).
Scan(ctx)

GORM and Bun scan rows into your own structs, but the columns in the query are strings. GORM also has a generics API and an optional CLI that generates typed field helpers.

Name columns with field pointers​

ant takes a pointer to a field of your own struct as the column. The compiler checks that the field exists and that the value compared with it has the field's type. Nothing is generated: the struct is the schema.

ant
var Users = ant.Fields[User]()

users, err := db.Users.
Where(ant.Gt(&Users.Age, 18)).
OrderBy(&Users.Name).
Limit(10).
All(ctx)
go build, with &Users.Agee
Users.Agee undefined (type *User has no field or method Agee)

The approach needs Go 1.27's generic methods. A few mistakes still compile, such as a field pointer from another model, and ant reports those when the query runs, or earlier with antvet. How &Users.Age becomes a column shows how it works.

Side by side​

Code generationMisspelled columnWrong value typeYou keep in step
sqlcsqlc generateAt generate timeAt compile timeSQL files and the schema
entgo generateAt compile timeAt compile timeent's schema
jet, bobFrom the databaseAt compile timeAt compile timeThe generated code and the database
squirrel, GORM, BunNoneWhen the query runsNot checkedStrings and the schema
antNoneAt compile timeAt compile timeStructs and the schema

Which to choose​

  • You think in SQL and want every query written out: sqlc.
  • You want the strongest checks and don't mind a generator: ent, or jet and bob if the database is the source of truth.
  • You want plain structs and no generator, and can live with strings: GORM or Bun.
  • You want plain structs, no generator, and checked columns: ant.

ant vs GORM, ent, Bun and sqlc compares those libraries in more depth, including speed and migrations.