Golang Database Library Orm Example - Delete
In this post, we will look at how deleting a single record in a table works for these various libraries and ORMs.
In general, this operation is very simple because we only care about deleting and checking for an error.
In short, we want to achieve
DELETE FROM users where id=$1;
Table of Contents
This is part of a series of blog posts including:
- Introduction
- Create - Create a single record
- List
- Get - Get a single record
- Update - Update a single field
- Delete - Delete a record
- One-To-Many - Eager load one to many relationship between two tables
- Many-To-Many - Eager load many to many relationships between two tables using a pivot table
- Dynamic List - Return a record with a list of names, sorting, and pagination
- Transaction - Handle transaction, rollback on error
-
SQL Injection - Try to list all users by using malicious query parameters
- and Conclusion
sqlx
There is no drama here. We simply write an sql query and call ExecContext().
const delete = "DELETE FROM users where id=$1"
func (r *database) Delete(ctx context.Context, userID int64) (sql.Result, error) {
return r.db.ExecContext(ctx, delete, userID)
}
sqlc
Same goes with sqlc. We write sql query up front in our query.sql and run sqlc generate.
-- name: DeleteUser :exec
DELETE
FROM users
WHERE id = $1;
It generates a DeleteUser() method that wraps am ExecContext() method.
const deleteUser = `-- name: DeleteUser :exec
DELETE
FROM users
WHERE id = $1
`
func (q *Queries) DeleteUser(ctx context.Context, id int64) error {
_, err := q.db.ExecContext(ctx, deleteUser, id)
return err
}
We simply call that method
func (r *database) Delete(ctx context.Context, id int64) error {
return r.db.DeleteUser(ctx, id)
}
Squirrel
Deleting a record reads like a SQL statement which is, like others so far, pretty simple.
_, err := r.db.Delete("users").
Where(sq.Eq{"id": id}).
ExecContext(ctx)
gorm
Like last operations, we need to remember chain Error because it is not compulsory.
func (r *repo) Delete(ctx context.Context, userID int64) error {
return r.db.WithContext(ctx).Delete(&User{}, userID).Error
}
We pass a &User{} struct which indicated gorm that it is deleting this users table. Gorm
magically figure out the name of the table you want using
the name of the struct.
sqlboiler
In sqlboiler, deleting is opposite to gorm when compared to doing an update operation. Here, sqlboiler
deletes a record through query building which means
two queries are made. Delete() method must be done on the models.User struct.
func (r *database) Delete(ctx context.Context, userID int64) error {
u, err := r.Get(ctx, userID)
if err != nil {
return fmt.Errorf("error getting user")
}
_, err = u.Delete(ctx, r.db)
if err != nil {
return fmt.Errorf("error deleting user")
}
return nil
}
By setting boil.SetDebug = true we can see the queries being made:
select * from "users" where "id"=$1
[3]
DELETE FROM "users" WHERE "id"=$1
ent
Ent does a single query to delete.
func (r *database) Delete(ctx context.Context, userID int64) error {
return r.db.User.DeleteOneID(uint(userID)).Exec(ctx)
}
No drama here. Except for sqlboiler, deleting only needs a single sql query, and Gorm for its many pitfalls.