Squirrel

Squirrel is a Go library for building SQL queries programmatically instead of concatenating strings by hand. Rather than acting as an ORM, it gives you a fluent, chainable API for constructing SELECT, INSERT, UPDATE, and DELETE statements out of composable parts, so conditional query logic (optional filters, dynamic joins, varying WHERE clauses) can be expressed as plain Go code rather than fragile string templates.

Each builder method returns a new, immutable builder value, so queries can be built up incrementally, branched, and reused safely. Squirrel supports pluggable placeholder formats (? for MySQL/SQLite, $1-style for PostgreSQL, plus colon and @p variants), can bind directly to a database/sql-compatible runner via RunWith for one-step Exec/Query/QueryRow calls, and includes a statement cache (stmtcacher) for reusing prepared statements.

The project has been in maintenance mode since around 2020 — the maintainer describes it as “complete,” merging bug fixes slowly but not adding new features. It remains widely used and is a common building block behind higher-level query-builder and repository-pattern libraries in the Go ecosystem.

What You Get

• Fluent query builders - Select, Insert, Update, and Delete builders with chainable methods for columns, joins, WHERE/HAVING clauses, ordering, limits, and offsets.
• Composable WHERE conditions - Eq, Gt, Lt, Like, Or, And and raw Expr() fragments that combine into complex predicates without manual string building.
• Pluggable placeholder formats - Question, Dollar, Colon, and AtP formats let the same builder target MySQL/SQLite (?) or PostgreSQL ($1, $2, …) style placeholders.
• Direct execution via RunWith - bind a builder to any database/sql-compatible runner (or context-aware equivalent) and call Exec, Query, QueryRow, or Scan directly on the built query.
• Prepared statement caching - the stmtcacher package wraps a runner to cache and reuse prepared statements across repeated query executions.
• Context-aware variants - *_ctx.go builders and a StdSqlCtx runner interface for executing queries with context.Context support.

Common Use Cases

• Dynamic filter building - construct a query’s WHERE clause incrementally based on which optional filters a user supplied, without string-concatenation bugs.
• Backend for higher-level data-access layers - used as the SQL-generation engine underneath repository or table-struct-mapper libraries (e.g. structable) that add struct-to-row mapping on top.
• Multi-database applications - write query-building logic once and swap PlaceholderFormat (Question vs Dollar) to target either MySQL/SQLite or PostgreSQL.
• Complex reporting queries - compose SELECT statements with multiple joins, GROUP BY/HAVING clauses, and subqueries (FromSelect) that would be error-prone to hand-write as strings.

Under The Hood

Architecture Squirrel is organized around per-statement-type builder files (select.go, insert.go, update.go, delete.go, plus _ctx.go context-aware counterparts) that each define an immutable data struct (e.g. selectData) and a lightweight Builder wrapper type registered with the external github.com/lann/builder package, which implements copy-on-write mutation: every chained method (.Where(...), .From(...), .Limit(...)) returns a new builder value rather than mutating shared state, so builders can be branched and reused safely. squirrel.go defines the core Sqlizer interface (ToSql() (string, []interface{}, error)) that every buildable part implements, plus Runner/Execer/Queryer/QueryRower interfaces that abstract over database/sql.DB-like types via RunWith. part.go, expr.go, and where.go provide the composable predicate and expression primitives (Eq, Expr, And/Or) that plug into any builder’s WHERE/HAVING/JOIN clauses through the shared Sqlizer contract. The core abstraction is Sqlizer itself; if it changed, every builder, predicate type, and the placeholder-substitution logic in placeholder.go would need to change with it.

Tech Stack Squirrel targets Go 1.14+ (per go.mod) with a minimal dependency footprint: github.com/lann/builder for the immutable builder pattern and github.com/lann/ps (persistent-map, indirect) as its backing data structure, github.com/stretchr/testify for test assertions, and github.com/davecgh/go-spew/github.com/pmezard/go-difflib as testify’s indirect dependencies. It has no runtime dependency on any specific database driver — it works with anything satisfying Go’s standard database/sql interfaces (or a slimmed-down StdSql/StdSqlCtx subset), and a separate integration/ module runs the same builder logic against real sqlite3, mysql, and postgres drivers in CI (.travis.yml) to verify generated SQL executes correctly.

Code Quality The project has extensive test coverage: 140+ Test* functions across *_test.go files sitting alongside their corresponding implementation files (select_test.go, expr_test.go, stmtcacher_test.go, etc.), plus a dedicated integration/ module with integration_test.go that exercises builders against live sqlite3, MySQL, and PostgreSQL databases in CI. Error handling is explicit and idiomatic Go — builder methods that can fail return (string, []interface{}, error) triples rather than panicking (though MustSql offers an explicit panic-on-error convenience variant), and sentinel errors like RunnerNotSet are exported for callers to check against. Naming is consistent and conventional for the Go ecosystem (exported Sqlizer, PlaceholderFormat, builder methods matching SQL clause names). No linter config or GitHub Actions workflow is present in the repo — CI is defined via a legacy .travis.yml — and the project is explicitly in low-activity maintenance mode, so quality is high but the toolchain around it (CI provider, Go version pins) is dated.

API Design The public API reads close to the SQL it generates: sq.Select("*").From("users").Where(sq.Eq{"id": 1}) mirrors SELECT * FROM users WHERE id = ? almost one-to-one, which keeps the learning curve low for anyone who already knows SQL. Method chaining plus copy-on-write builders means queries can be partially built, stored in a variable, and branched into multiple final queries safely — a pattern the README demonstrates directly (building a base users query, then deriving active and three_stooges variants from it). Getting started requires zero boilerplate beyond an import and a RunWith(db) call to attach a driver; the README’s own quickstart snippets execute as shown. Documentation is concentrated in GoDoc comments on exported methods rather than a separate docs site, and the README’s FAQ section addresses common gotchas (composite-key IN queries, []byte vs []uint8 ambiguity) directly, which lowers the friction of edge cases that aren’t obvious from the API alone.