GoLang Sqlx CRUD | Postgres

Created At: 2023-05-04 07:18:53 Updated At: 2023-05-04 21:45:31

In this tutorial, we will see how to create CRUD operation for Postgres using sqlx. This tutorial is based on earlier tutorial postgres connection.

There we have a database struct which contains sqlx.DB

I strong suggest to see the earlier tutorial, otherwise you won't understand much. 

Let's get started. First make sure, we create a new package name comment. It may look like this 

package comment

//this is a service layer file. This would talk to the database through repository layer.

type Comment struct {
	ID     string
	Slug   string
	Body   string
	Author string
}

This package just about struct, which is about Comment. We named it Comment struct, since we will be dealing with comment, like creating, retreive, update and delete.

Now, we will do the actual work of doing CRUD. Let's create a new package name db.

This package will contain all our CRUD methods and another comment struct. We will name it CommentRow struct. 

package db

type CommentRow struct {
	ID     string
	Slug   sql.NullString
	Body   sql.NullString
	Author sql.NullString
}

This struct would help us covert our data to nullable types which database can take. Only then we would be able to store the comment in the database.

We will need another function to stay or bridge between Comment and CommentRow. Le'ts take a look

package db

........................
type CommentRow struct{...}
........................

func convertCommentRowToComment(c CommentRow) comment.Comment {
	return comment.Comment{
		ID:     c.ID,
		Slug:   c.Slug.String,
		Author: c.Author.String,
		Body:   c.Body.String,
	}
}

The above function used when retrieve a comment and used when store a comment.

Retrieve a comment

To get a comment from database, we will need a context and a id. Context is always present there when we work with Go,  but id would be coming from incoming request. It would be actually uuid

For retrieving comment, we need RowQueryContext() method. This method will look for a comment based on uuid and return a row. 

Then we will use Scan() function for the returned row object. We will save the row fields in cmt CommentRow object.

If we have no error, we will get a comment and convert to Comment struct using the convertCommentRowToComment function.

func (d *Database) GetComment(
	ctx context.Context,
	uuid string,
) (comment.Comment, error) {
	var cmtRow CommentRow
	row := d.Client.QueryRowContext(
		ctx,
		`SELECT id, slug, body, author FROM comments WHERE id =$1`,
		uuid,
	)
	err := row.Scan(&cmtRow.ID, &cmtRow.Slug, &cmtRow.Body, &cmtRow.Author)
	if err != nil {
		return comment.Comment{}, err
	}

	return convertCommentRowToComment(cmtRow), nil
}

Post a comment

Posting a comment requires you to send a comment from main.go file. Make sure you send it from the main.go like below

db.PostComment(
		context.Background(),
		comment.Comment{
			ID:     "dc9076e9-2fda-4019-bd2c-900a8284b9c4",
			Slug:   "manual-test",
			Author: "dbestech",
			Body:   "Hello World",
		},
	)

You should also notice that we calling comment.Comment struct. This struct resides in our comment package. That means db.PostComment() calls comment.Comment.

Here we pass all the value necessary for the PostComment() function. It does not exist yet. Let's create it. To create it, we will need

  1. uuid
  2. NamedQueryContext

We will generate uuid using a package called 

"github.com/satori/go.uuid"
And NamedQueryContext will come from sqlx.DB package. Now our PostComment() function looks like below
func (d *Database) PostComment(ctx context.Context, cmt comment.Comment) (comment.Comment, error) {
	cmt.ID = uuid.NewV4().String()
	postRow := CommentRow{
		ID:     cmt.ID,
		Slug:   sql.NullString{String: cmt.Slug, Valid: true},
		Author: sql.NullString{String: cmt.Author, Valid: true},
		Body:   sql.NullString{String: cmt.Body, Valid: true},
	}
	rows, err := d.Client.NamedQueryContext(
		ctx,
		`INSERT INTO comments
		(id, slug, author, body)
		VALUES
		(:id, :slug, :author, :body)`,
		postRow,
	)
	if err != nil {
		return comment.Comment{}, fmt.Errorf("failed to insert comment %w", err)
	}
	if err := rows.Close(); err != nil {
		return comment.Comment{}, fmt.Errorf("failed to close rows: %w", err)
	}
	return cmt, nil
}

Here you notice that satori package is generating the uuid for us.

cmt.ID = uuid.NewV4().String()

Update a comment

Updating a comment is similar to PostComment() function. In this case we have to send the uuid before hand. So we will pass id and comment at the same time, wherever we call the function from.

func (d *Database) UpdateComment(ctx context.Context, id string, cmt comment.Comment) (comment.Comment, error) {
	cmtRow := CommentRow{
		ID:     id,
		Slug:   sql.NullString{String: cmt.Slug, Valid: true},
		Author: sql.NullString{String: cmt.Author, Valid: true},
		Body:   sql.NullString{String: cmt.Body, Valid: true},
	}
	rows, err := d.Client.NamedQueryContext(
		ctx,
		`UPDATE comments SET
		slug= :slug,
		author= :author,
		body= :body
		WHERE id= :id`,
		cmtRow,
	)
	if err != nil {
		return comment.Comment{}, fmt.Errorf("failed to update comment")

	}
	if err := rows.Close(); err != nil {
		return comment.Comment{}, fmt.Errorf("failed to close rows: %w", err)

	}
	return convertCommentRowToComment(cmtRow), nil
}

Here once again, we are calling NamedQueryContext(). This is a common method between PostComment and UpdateComment. 

Delete a comment

It's the easiest of all. All you need to do is to pass a id, and call ExecContext() method.

func (d *Database) DeleteComment(ctx context.Context, id string) error {
	_, err := d.Client.ExecContext(
		ctx,
		`DELETE FROM comments WHERE id=$1`,
		id,
	)
	if err != nil {
		return fmt.Errorf("failed to delete comment: %w", err)
	}
	return nil
}

Comment

Add Reviews