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. All the benefits of type safety and code competitions are lost, and the programmer has to be careful to correctly name statement projections, so that QRM can scan query result.

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 exactly named arguments in the raw query. For postgres queries it is recommended NOT to use ($1, $2, ...) for named arguments.

Examples

Execute statement using QueryContext

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

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

Execute statement using ExecContext

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

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()
...