Masterminds/squirrel

External

• https://github.com/Masterminds/squirrel
• https://medium.com/nerd-for-tech/postgres-libraries-to-try-with-go-8f80191edbff

Internal

• PostgreSQL Support in Go

Overview

Squirrel is a fluent SQL generator for Go. It helps build SQL queries from composable parts representing the SQL SELECT query clauses: SELECT, FROM, JOIN, WHERE, etc. and also other DML statements.

Installation

go get github.com/Masterminds/squirrel

Programming Model

Queries

import sq "github.com/Masterminds/squirrel"

// Get a database/sql database handle
dbHandle = getDbHandle()

// Build the query with a fluent API
query = sq.Select("*").From("person")

// execute the query with the database handle; the syntax is identical to that used with dbHandle.Query()
rows, err := query.RunWith(dbHandle).Query()
if err != nil { ... }
defer func() {
	if err = rows.Close(); err != nil { ... }
}()
for rows.Next() {
	var (
		id   int
		name string
		city sql.NullString
	)
	if err = rows.Scan(&id, &name, &city); err != nil { ... }
	fmt.Printf("id: %d, name: %s, city: %s\n", id, name, city.String)
}

getDbHandle() can be implemented as shown in:

Connect to a PostgreSQL Database with database/sql API and a pgx Driver

Parameterized Statements

The format of the parameterized statements depends on the database. For example, in PostgreSQL, the placeholders are introduced by $ and have numerical values, etc.

PostgreSQL Parameterized Statements

The parameterized statement placeholders in Squirrel are ?. To configure it to generate correct PostgreSQL parameterized statements, we must configure the specific PostgreSQL placeholder format, which is $<number>.

import sq "github.com/Masterminds/squirrel"

query :=
	sq.Select("*").
		From("person").
		Where("name IN (?, ?)", "Alice", "Bob").
		PlaceholderFormat(sq.Dollar)

Also see:

Parameterized PostgreSQL SQL Statements