Golang Database Library Orm Example - Delete

Comparison between popular go libraries and ORM for database access layer.

December 15, 2021

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;

This is part of a series of blog posts including:

Introduction
Create - Create a single record
List
- Returns the first 30 records of a resource
- Use WHERE IN(?)
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

The runnable code is available in the repository https://github.com/gmhafiz/golang-database-library-orm-example

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.

Next > Golang Database Library ORM Example One To Many