Statements

Following statements are supported:

• SELECT
• INSERT
• UPDATE
• DELETE
• LOCK
• WITH

Executing statements

Statements can be executed with following methods:

• Query(db qrm.DB, dest interface{}) error - executes statement over database connection/transaction db and stores query result in destination dest. Destination can be either pointer to struct or pointer to a slice. If destination is pointer to struct and query result set is empty, method returns qrm.ErrNoRows.
• QueryContext(context context.Context, db qrm.DB, dest interface{}) error - executes statement with a context over database connection/transaction db and stores query result in destination dest. Destination can be either pointer to struct or pointer to a slice. If destination is pointer to struct and query result set is empty, method returns qrm.ErrNoRows.
• Exec(db qrm.DB) (sql.Result, error) - executes statement over database connection/transaction db and returns sql.Result.
• ExecContext(context context.Context, db qrm.DB) (sql.Result, error) - executes statement with context over database/transaction connection db and returns sql.Result.
• Rows(ctx context.Context, db qrm.DB) (*Rows, error) - executes statements over db connection/transaction and returns rows

Each execution method first creates parameterized sql query with list of arguments and then initiates appropriate call on database connection db qrm.DB.

Exec and ExecContext are just a wrappers around db qrm.DB call to Exec and ExecContext.

Query and QueryContext are Query Result Mapping (QRM) methods. They execute statement over db qrm.DB with Query or QueryContext methods while performing grouping of each row result into the destination.

Database connection db qrm.DB can be of any type that implements following interface:

type DB interface {
	Exec(query string, args ...interface{}) (sql.Result, error)
	ExecContext(ctx context.Context, query string, args ...interface{}) (sql.Result, error)
	Query(query string, args ...interface{}) (*sql.Rows, error)
	QueryContext(ctx context.Context, query string, args ...interface{}) (*sql.Rows, error)
}

These include but are not limited to:

• sql.DB
• sql.Tx
• sql.Conn

Debugging statements

SQL generated from the statement can be seen by:

• Sql() (query string, args []interface{}) - retrieves parameterized sql query with list of arguments
• DebugSql() (query string) - retrieves debug query where every parametrized placeholder is replaced with its argument textual represenatation.

Logging statements

Statements can be automatically logged, before they are executed, by setting a new global logger function. For example:

postgres.SetLogger(func(ctx context.Context, statement postgres.PrintableStatement) {
    loggedSQL, loggedSQLArgs = statement.Sql()
    fmt.Println(loggedSQL, loggedSQLArgs)

    loggedDebugSQL = statement.DebugSql()
    fmt.Println(loggedDebugSQL)
})

Raw Statements

It is possible to write raw SQL queries without using generated SQL builder types. Using raw sql queries all the benefits of type safety and code competitions are lost. Programmer has to be careful to correctly name statement projections, otherwise QRM will not be able to scan the result of the query. Every projection has to be aliased in destination type name.field name format.

stmt := RawStatement(`
	SELECT actor.actor_id AS "actor.actor_id",
		 actor.first_name AS "actor.first_name",
		 actor.last_name AS "actor.last_name",
		 actor.last_update AS "actor.last_update"
	FROM dvds.actor
	WHERE actor.actor_id IN (actorID1, #actorID2, $actorID3) AND ((actorID1 / #actorID2) <> (#actorID2 * $actorID3))
	ORDER BY actor.actor_id`,
	RawArgs{
		"actorID1": int64(1),
		"#actorID2": int64(2),
		"$actorID3": int64(3),
	},
)

var actor []model.Actor
err := stmt.Query(db, &actor)

RawArgs contains named arguments for a placeholders in raw statement query. Named arguments naming convention does not have to follow any format, it just have to match named arguments exactly from the raw query. It is recommended NOT to use ($1, $2, ...) for postgres queries.

Examples

Execute statement using QueryContext method

QueryContext can be used to map a statement result into into single object or to the slice destination:

1. Single object destination

stmt := SELECT(
	Actor.AllColumns,
).FROM(
	Actor,
).WHERE(
	Actor.ActorID.EQ(Int(2)),
)

actor := model.Actor{}
err := query.QueryContext(ctx, db, &actor)

2. Slice destination

stmt := SELECT(
	Actor.AllColumns,
).FROM(
	Actor,
).WHERE(
	Actor.ActorID.GT(Int(20)),
)

actors := []model.Actor{}
err := query.QueryContext(ctx, db, &actor)

Execute statement using ExecContext method

linkData := model.Link{
	ID:   1000,
	URL:  "http://www.duckduckgo.com",
	Name: "Duck Duck go",
}

stmt := Link.
	INSERT().
	MODEL(linkData)

res, err := stmt.ExecContext(ctx, db)
...

Execute statement using Rows method

stmt := SELECT(
	Inventory.AllColumns,
).FROM(
	Inventory,
).ORDER_BY(
	Inventory.InventoryID.ASC(),
)

rows, err := stmt.Rows(ctx, db)
defer rows.Close()

for rows.Next() {
	var inventory model.Inventory
	err = rows.Scan(&inventory)
        ...
}

err = rows.Close()
...

err = rows.Err()
...