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())MySQL Dialect Delete
Use db.DeleteDialect when the MySQL delete statement is stored in db.DialectSQL.
The query uses ? placeholders.
--DeleteUser
DELETE FROM users
WHERE id = ?func DeleteUser(ctx context.Context, conn db.Conn, queries Queries, id int64) (db.Result, error) {
res, err := db.DeleteDialect(ctx, conn, queries.DeleteUser, id)
if err != nil {
return nil, err
}
return res, nil
}Load the query model before calling the helper.
queries, err := LoadQueries(conn)
if err != nil {
log.Fatal(err)
}
result, err := DeleteUser(ctx, conn, queries, 28)
if err != nil {
log.Fatal(err)
}
fmt.Printf("rows_affected=%d\n", result.RowsAffected())MySQL returns a db.Result, so the caller can inspect 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())PostgreSQL Dialect Delete
Use db.DeleteDialect when the PostgreSQL delete statement is stored in db.DialectSQL.
The query uses numbered placeholders.
--DeleteUser
DELETE FROM users
WHERE id = $1func DeleteUser(ctx context.Context, conn db.Conn, queries Queries, id int64) (db.Result, error) {
res, err := db.DeleteDialect(ctx, conn, queries.DeleteUser, id)
if err != nil {
return nil, err
}
return res, nil
}Load the query model before calling the helper.
queries, err := LoadQueries(conn)
if err != nil {
log.Fatal(err)
}
result, err := DeleteUser(ctx, conn, queries, 28)
if err != nil {
log.Fatal(err)
}
fmt.Printf("rows_affected=%d\n", result.RowsAffected())The Go call is the same as MySQL.
Only the SQL placeholder style changes.
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
}Scylla Dialect Delete
Use db.DeleteDialect when the Scylla delete statement is stored in db.DialectSQL.
Scylla uses ? placeholders.
--DeleteUser
DELETE FROM users_by_id
WHERE id = ?func DeleteUser(ctx context.Context, conn db.Conn, queries Queries, id string) error {
_, err := db.DeleteDialect(ctx, conn, queries.DeleteUser, id)
if err != nil {
return err
}
return nil
}Load the query model before calling the helper.
queries, err := LoadQueries(conn)
if err != nil {
log.Fatal(err)
}
err = DeleteUser(
ctx,
conn,
queries,
"01efebf6-64d4-11f1-9b56-4ac3b511b961",
)
if err != nil {
log.Fatal(err)
}Scylla does not return a useful affected-row count.
For Scylla, treat a successful call as a successful statement execution and do not check RowsAffected().
For query-driven Scylla models, the same logical delete may need to be executed against multiple query tables.
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
Use db.DeleteDialect when delete statements are stored in db.DialectSQL.
This does not replace db.Delete.
Use db.Delete when you pass a direct SQL or CQL query string.
Use db.DeleteDialect when you pass a db.DialectSQL value loaded from query models.
type Queries struct {
DeleteUser db.DialectSQL `json:"DeleteUser"`
}Load the query model before calling dialect helpers.
queries, err := LoadQueries(conn)
if err != nil {
log.Fatal(err)
}See Loading Queries From SQL Files for the complete query-loading setup.
| Driver | Direct query helper | Dialect helper | Result handling |
|---|---|---|---|
| MySQL | db.Delete | db.DeleteDialect | inspect db.Result |
| PostgreSQL | db.Delete | db.DeleteDialect | inspect db.Result |
| Scylla | db.Delete | db.DeleteDialect | ignore the result object |
MySQL and PostgreSQL return a db.Result, so the caller can inspect RowsAffected().
Scylla does not return a useful affected-row count, so its result is ignored.
_, err := db.DeleteDialect(ctx, conn, queries.DeleteUser, id)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")
}