goqu

To execute a delete against a database, use .Executor() on your DeleteDataset to get an executor, then call .Exec() to perform the operation. You can use .RowsAffected() to see how many rows were deleted.

If you used a .Returning() clause, you can use .ScanVals() to scan the returned values into variables or slices.

// Basic execution
db := getDb()
de := db.Delete("goqu_user").
	Where(goqu.Ex{"first_name": "Bob"}).
	Executor()

if r, err := de.Exec(); err != nil {
	fmt.Println(err.Error())
} else {
	c, _ := r.RowsAffected()
	fmt.Printf("Deleted %d users", c)
}

// Execution with Returning
de := db.Delete("goqu_user").
	Where(goqu.C("last_name").Eq("Yukon")).
	Returning(goqu.C("id")).
	Executor()

var ids []int64
if err := de.ScanVals(&ids); err != nil {
	fmt.Println(err.Error())
} else {
	fmt.Printf("Deleted users [ids:=%+v]", ids)
}

The TxDatabase.Wrap method is a convenience function that automatically handles COMMIT and ROLLBACK logic. You pass a function containing your database operations to Wrap; if the function returns an error, the transaction is rolled back, otherwise it is committed.

tx, err := db.Begin()
if err != nil{
   return err
}
err = tx.Wrap(func() error{
  update := tx.From("user").
      Where(goqu.Ex{"password": nil}).
      Update(goqu.Record{"status": "inactive"})
  return update.Exec()
})
//err will be the original error from the update statement, unless there was an error executing ROLLBACK
if err != nil{
    return err
}

In v8, goqu introduced a major API change to separate concerns between different SQL statement types. Instead of a single dataset type, there are now five distinct dataset types: SelectDataset, InsertDataset, UpdateDataset, DeleteDataset, and TruncateDataset.

Key changes include:

• Specific entry points for each statement type (e.g., goqu.Insert, goqu.Update).
• Removal of ToInsertSQL, ToUpdateSQL, ToDeleteSQL, and ToTruncateSQL from SelectDataset. Use the ToSQL method on the respective dataset type instead.
• Introduction of the Executor() method to handle execution for non-SELECT statements.

You can start a transaction using db.Begin(), which returns a TxDatabase. To ensure operations occur within the transaction, use tx.From to obtain a dataset. You must manually call tx.Commit() to save changes or tx.Rollback() to abort them if an error occurs.

tx, err := db.Begin()
if err != nil{
   return err
}
//use tx.From to get a dataset that will execute within this transaction
update := tx.From("user").
    Where(goqu.Ex{"password": nil}).
    Update(goqu.Record{"status": "inactive"})
if _, err = update.Exec(); err != nil{
    if rErr := tx.Rollback(); rErr != nil{
        return rErr
    }
    return err
}
if err = tx.Commit(); err != nil{
    return err
}
return

To execute an update in goqu, use the goqu.Database#Update method to build your dataset. You must call .Executor() at the end of your builder chain to obtain an executor that can run the query.

Common steps include:

1. Calling db.Update("table_name").
2. Using .Where() to specify the target rows.
3. Using .Set() with a goqu.Record to define the new values.
4. Calling .Executor().
5. Calling .Exec() on the resulting executor to perform the operation and receive a sql.Result.

db := getDb()

update := db.Update("goqu_user").
	Where(goqu.C("first_name").Eq("Bob")).
	Set(goqu.Record{"first_name": "Bobby"}).
	Executor()

if r, err := update.Exec(); err != nil {
	fmt.Println(err.Error())
} else {
	c, _ := r.RowsAffected()
	fmt.Printf("Updated %d users", c)
}

By default, goqu interpolates all parameters into the SQL string. To prevent interpolation and instead use placeholders (e.g., ? for MySQL/SQLite or $1, $2 for Postgres), use the .Prepared(true) method on a dataset. This is useful for using prepared statements in your database driver to improve security and performance.

When .Prepared(true) is set, all subsequent query operations (Select, Insert, Update, Delete) will generate SQL with placeholders and return the arguments separately.

To install goqu using Go modules, run the following command:

go get -u github.com/doug-martin/goqu/v9

If you are not using Go modules (but using Go version >v1.10), you must drop the version from the import path. Use github.com/doug-martin/goqu instead of github.com/doug-martin/goqu/v9.