Masterminds/squirrel: Difference between revisions

From NovaOrdis Knowledge Base
Jump to navigation Jump to search
 
(8 intermediate revisions by the same user not shown)
Line 4: Line 4:
=Internal=
=Internal=
* [[PostgreSQL_Support_in_Go#Overview|PostgreSQL Support in Go]]
* [[PostgreSQL_Support_in_Go#Overview|PostgreSQL Support in Go]]
=TODO=
<font color=darkkhaki>
* PARSE: https://github.com/Masterminds/squirrel
* PARSE: https://medium.com/nerd-for-tech/postgres-libraries-to-try-with-go-8f80191edbff
</font>


=Overview=
=Overview=
Line 15: Line 20:


=Programming Model=
=Programming Model=
==Queries==
<syntaxhighlight lang='go'>
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)
}
</syntaxhighlight>
<code>getDbHandle()</code> can be implemented as shown in: {{Internal|PostgreSQL_Support_in_Go#With_database/sql_API_and_a_pgx_Driver|Connect to a PostgreSQL Database with <tt>database/sql</tt> API and a pgx Driver}}
==Parameterized Statements==
The format of the [[SQL#Parameterized_SQL_Statements|parameterized statements]] depends on the database. For example, in PostgreSQL, the placeholders are introduced by <code>$</code> and have numerical values, etc.
===PostgreSQL Parameterized Statements===
The parameterized statement placeholders in Squirrel are <code>?</code>. To configure it to generate correct PostgreSQL parameterized statements, we must configure the specific [[PostgreSQL_Concepts#Placeholder_Format|PostgreSQL placeholder format]], which is <code>$<number></code>.
<syntaxhighlight lang='go'>
import sq "github.com/Masterminds/squirrel"
query :=
sq.Select("*").
From("person").
Where("name IN (?, ?)", "Alice", "Bob").
PlaceholderFormat(sq.Dollar)
</syntaxhighlight>
Also see: {{Internal|PostgreSQL_Concepts#Parameterized_SQL_Statements|Parameterized PostgreSQL SQL Statements}}

Latest revision as of 18:32, 31 May 2024

External

Internal

TODO

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