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
We will generate uuid using a package called
"github.com/satori/go.uuid"
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
}
