Delete
Delete rows using the shared DB API across supported drivers.
Use db.Delete when you want to execute a delete statement.
Delete is a semantic wrapper around Exec.
It does not generate SQL, inspect the statement, or validate that the statement is a DELETE.
It only makes application code easier to read.
result, err := db.Delete(ctx, conn, deleteUserQuery, id)The same db.Delete helper can be used with MySQL, PostgreSQL, and Scylla.
Only the SQL or CQL syntax and placeholder style differ by driver.
Overview
| Driver | Placeholder style | Common usage |
|---|---|---|
| MySQL | ? | delete rows and read RowsAffected() |
| Postgres | $1, $2 | delete rows and read RowsAffected() |
| Scylla | ? | delete rows from query tables using CQL |
MySQL Delete
MySQL uses ? placeholders.
const deleteUserQuery = `
DELETE FROM users
WHERE id = ?
`Use db.Delete:
func DeleteUser(ctx context.Context, conn db.Conn, id int64) (db.Result, error) {
return db.Delete(ctx, conn, deleteUserQuery, id)
}Usage:
result, err := DeleteUser(ctx, conn, 1)
if err != nil {
return err
}
fmt.Printf("rows_affected=%d\n", result.RowsAffected())PostgreSQL Delete
PostgreSQL uses numbered placeholders.
const deleteUserQuery = `
DELETE FROM users
WHERE id = $1
`Use db.Delete the same way:
func DeleteUser(ctx context.Context, conn db.Conn, id int64) (db.Result, error) {
return db.Delete(ctx, conn, deleteUserQuery, id)
}Usage:
result, err := DeleteUser(ctx, conn, 1)
if err != nil {
return err
}
fmt.Printf("rows_affected=%d\n", result.RowsAffected())Scylla Delete
Scylla uses ? placeholders.
const deleteUserByIDQuery = `
DELETE FROM users_by_id
WHERE id = ?
`Use db.Delete:
func DeleteUserByID(ctx context.Context, conn db.Conn, id string) (db.Result, error) {
return db.Delete(ctx, conn, deleteUserByIDQuery, id)
}For query-driven Scylla models, the same logical delete may need to be applied to more than one query table.
const deleteUserByEmailQuery = `
DELETE FROM users_by_email
WHERE email = ?
`
func DeleteUserByIDAndEmail(
ctx context.Context,
conn db.Conn,
id string,
email string,
) error {
if _, err := db.Delete(ctx, conn, deleteUserByIDQuery, id); err != nil {
return err
}
if _, err := db.Delete(ctx, conn, deleteUserByEmailQuery, email); err != nil {
return err
}
return nil
}RowsAffected
db.Delete returns db.Result.
type Result interface {
RowsAffected() int64
LastInsertId() int64
}Use RowsAffected to inspect how many rows were removed.
result, err := db.Delete(ctx, conn, deleteUserQuery, id)
if err != nil {
return err
}
fmt.Println(result.RowsAffected())LastInsertId is not relevant for delete statements.
Delete vs Exec
Delete is equivalent to Exec.
result, err := db.Delete(ctx, conn, query, args...)is equivalent to:
result, err := db.Exec(ctx, conn, query, args...)Use Delete when you want the code to communicate delete intent.
Use Exec when the statement is generic.
Dialect Delete
For applications that support multiple drivers, keep delete statements in db.DialectSQL.
type Queries struct {
DeleteUser db.DialectSQL `json:"DeleteUser"`
}Then select the correct SQL or CQL for the active driver:
q, err := db.Dialect(conn, queries.DeleteUser, id)
if err != nil {
return err
}
result, err := db.ExecQuery(ctx, conn, q)
if err != nil {
return err
}This keeps repository code independent from the selected driver while keeping SQL explicit.
When to Use Delete
Use db.Delete when:
- the operation removes rows
- you want readable repository code
- the statement does not return rows
- you want access to
RowsAffected - the operation should communicate delete intent
When Not to Use Delete
Do not use db.Delete for statements that return rows.
For PostgreSQL statements using RETURNING, use db.Get, db.Value, or query-based helpers instead.
Example:
deletedID, found, err := db.Value[int64](ctx, conn, `
DELETE FROM users
WHERE id = $1
RETURNING id
`, id)Use db.Exec when the statement is not clearly an insert, update, or delete.
Recommended Style
Define the query as a constant:
const deleteUserQuery = `
DELETE FROM users
WHERE id = ?
`Wrap it in a small function:
func DeleteUser(ctx context.Context, conn db.Conn, id int64) (db.Result, error) {
return db.Delete(ctx, conn, deleteUserQuery, id)
}Use the returned result when you need to inspect affected rows:
result, err := DeleteUser(ctx, conn, id)
if err != nil {
return err
}
if result.RowsAffected() == 0 {
return errors.New("user was not deleted")
}