Building a social app with Spin (3.5/4): Go Postgres Usage
Justin Pflueger
social
spin
wasm
go
postgres
Hello Fermyon friends! I’m back with the follow-up to our last blog post where we covered setting up a component with the Go SDK and generated some SDK bindings for outbound Postgres support. In this blog post we’ll build on our previous work by utilizing the Postgres bindings to persist the social posts to the database.
If you haven’t read the previous blog posts, I would recommend taking a few minutes to read those as we have been continually building on top of our previous work. In the last blog post we setup the HTTP router, serialization and handler methods in our component. All we need to do now is to add implementations for the insert, read by id, read all, update and delete operations.
Setup Placeholder Functions
Let’s start off by adding some placeholder functions for each of our database operations. I’ve also made some (boring) changes to the HTTP handlers so the responses are more typical of a REST API like setting better status codes and headers. I don’t think we need to cover those here because they’re pretty common to any Go and REST API. So let’s take a peek at the placeholder functions that I’ve added and we’ll begin to implement one-by-one:
// Database Operations
func DbInsert(post *Post) error {
//TODO: implement
post.ID = 1
return nil
}
func DbReadAll() ([]Post, error) {
//TODO: implement
return []Post{}, nil
}
func DbReadById(id int) (Post, error) {
//TODO: implement
return Post{
ID: id,
}, nil
}
func DbUpdate(post Post) error {
//TODO: implement
return nil
}
They are pretty basic signatures and wiring them up to our HTTP handler methods is pretty straightforward. Here’s an example for the social media post creation handler:
func createPost(res http.ResponseWriter, req *http.Request) {
post := req.Context().Value(postCtxKey{}).(Post)
err := DbInsert(&post)
if err == nil {
renderJsonResponse(res, post.ToJson())
res.Header().Add("location", fmt.Sprintf("/api/post/%v", post.ID))
res.WriteHeader(http.StatusCreated)
} else {
http.Error(res, err.Error(), http.StatusInternalServerError)
}
}
Database Insert
We’re ready to implement the first database operation, insert. First things first, we need to wire up our database connection string to our - this is a similar pattern across all languages using the Spin SDK(s): add component.config to spin.toml and read that configuration in our application. With the use of variables in spin.toml, this is pretty simple and done in the same way as the profile component:
[component.config]
db_url = "host={{db_host}} user={{db_user}} password={{db_pswd}} dbname={{db_name}}"
To read the configuration item in go, we make a call using the config module in the Spin Go SDK. I’ve chosen to panic here instead of gracefully failing because without the database connection string, nothing about this module is going to work:
func getDbUrl() string {
db_url, err := config.Get("db_url")
if err != nil {
panic(fmt.Sprintf("Unable to retrieve 'db_url' config item: %v", err))
}
return db_url
}
Now we’re ready to actually implement the DbInsert function. Similar to how we performed an insert in the profile component using the Spin Rust SDK, we need to write the insert statement, build the parameters and execute the statement. The one tricky part here is that we need to query the last inserted identifier to populate the ID after it has been inserted. Because Spin doesn’t support executing multiple statements we need to do this as a separate query. If your application has significant usage this method may lead to issues later because you could populate and incorrect identifier. One workaround for this would be to wrap this logic up in a stored procedure but we’ll keep it simple for now:
func DbInsert(post *Post) error {
db_url := getDbUrl()
statement := "INSERT INTO posts (author_id, content, type, data, visibility) VALUES ($1, $2, $3, $4, $5)"
params := []postgres.ParameterValue{
postgres.ParameterValueStr(post.AuthorID),
postgres.ParameterValueStr(post.Content),
postgres.ParameterValueStr(post.Type),
postgres.ParameterValueStr(post.Data),
postgres.ParameterValueStr(post.Visibility),
}
_, err := postgres.Execute(db_url, statement, params)
if err != nil {
return fmt.Errorf("Error inserting into database: %s", err.Error())
}
// we need to get the identifier for the inserted post
rowset, err := postgres.Query(db_url, "SELECT lastval()", []postgres.ParameterValue{})
if err != nil || len(rowset.Rows) != 1 || len(rowset.Rows[0]) != 1 {
return fmt.Errorf("Error querying id from database: %s", err.Error())
}
// populate the identifier
id_val := rowset.Rows[0][0]
if id_val.Kind() == postgres.DbValueKindInt64 {
post.ID = int(id_val.GetInt64())
} else {
fmt.Printf("Failed to populate created post's identifier, invalid kind returned from database: %v", id_val.Kind())
}
return nil
}
Database Read By Id
Moving on we can now implement the DbReadById function. The process remains the same as before: write the query, build the parameters and execute the query.
func DbReadById(id int) (Post, error) {
db_url := getDbUrl()
statement := "SELECT id, author_id, content, type, data, visibility FROM posts WHERE id=$1"
params := []postgres.ParameterValue{
postgres.ParameterValueInt32(int32(id)),
}
rowset, err := postgres.Query(db_url, statement, params)
if err != nil {
return Post{}, fmt.Errorf("Error reading from database: %s", err.Error())
}
if rowset.Rows == nil || len(rowset.Rows) == 0 {
return Post{}, nil
} else {
return fromRow(rowset.Rows[0])
}
}
The only difference for this function is that we need to translate the query result into our Post struct. I’ve pulled this logic out into two helper functions. The fromRow function helps us populate values in our social media Post struct from the Spin Go SDK’s row type, in this case an array of DbValue structs.
func fromRow(row []postgres.DbValue) (Post, error) {
var post Post
if val, err := assertValueKind(row, 0, postgres.DbValueKindInt32); err == nil {
post.ID = int(val.GetInt32())
} else {
return post, err
}
if val, err := assertValueKind(row, 1, postgres.DbValueKindStr); err == nil {
post.AuthorID = val.GetStr()
} else {
return post, err
}
if val, err := assertValueKind(row, 2, postgres.DbValueKindStr); err == nil {
post.Content = val.GetStr()
} else {
return post, err
}
if val, err := assertValueKind(row, 3, postgres.DbValueKindStr); err == nil {
post.Type = val.GetStr()
} else {
return post, err
}
if val, err := assertValueKind(row, 4, postgres.DbValueKindStr); err == nil {
post.Data = val.GetStr()
} else {
return post, err
}
if val, err := assertValueKind(row, 5, postgres.DbValueKindStr); err == nil {
post.Visibility = val.GetStr()
} else {
return post, err
}
return post, nil
}
You might have noticed another helper function assertValueKind. When writing the fromRow function I found myself repeating the same logic so I pulled as much of that logic out into its own function. Given Go’s lack of generics I couldn’t find a clean way to call methods like .GetStr() and .GetInt32() in a repeatable way but if we have any Gophers reading this, feel free to let me know in the pull request or on Discord how I could improve this logic. The assertValueKind function also helps us to surface any errors when our query returns a type that doesn’t match our expectations. For the most part those errors just happened during development because I’ve marked most of the columns as NOT NULL so we really don’t have to deal with null (nil in go) values here.
func assertValueKind(row []postgres.DbValue, col int, expected postgres.DbValueKind) (postgres.DbValue, error) {
if row[col].Kind() != expected {
return postgres.DbValue{}, fmt.Errorf("Expected column %v to be %v kind but got %v\n", col, expected, row[col].Kind())
}
return row[col], nil
}
Database Read All
Implementing the DbReadAll function will re-use the logic we just covered to translate a row back into our Post struct which makes this implementation fairly straightforward. Not much different here, just need to process multiple rows from the RowSet. Let’s take a look at the logic:
func DbReadAll() ([]Post, error) {
db_url := getDbUrl()
statement := "SELECT id, author_id, content, type, data, visibility FROM posts"
rowset, err := postgres.Query(db_url, statement, []postgres.ParameterValue{})
if err != nil {
return []Post{}, fmt.Errorf("Error reading from database: %s", err.Error())
}
posts := make([]Post, len(rowset.Rows))
for i, row := range rowset.Rows {
if post, err := fromRow(row); err != nil {
return []Post{}, err
} else {
posts[i] = post
}
}
return posts, nil
}
Database Update & Delete
The DbUpdate and DbDelete functions were so simple to implement that I just did them both in one go. The DbUpdate function is really similar to the DbInsert function, the only differences are the SQL statement and that we don’t need to populate the identifier after the update:
func DbUpdate(post Post) error {
db_url := getDbUrl()
statement := "UPDATE posts SET author_id=$1, content=$2, type=$3, data=$4, visibility=$5 WHERE id=$6"
params := []postgres.ParameterValue{
postgres.ParameterValueStr(post.AuthorID),
postgres.ParameterValueStr(post.Content),
postgres.ParameterValueStr(post.Type),
postgres.ParameterValueStr(post.Data),
postgres.ParameterValueStr(post.Visibility),
postgres.ParameterValueInt32(int32(post.ID)),
}
_, err := postgres.Execute(db_url, statement, params)
if err != nil {
return fmt.Errorf("Error updating database: %s", err.Error())
}
return nil
}
The DbDelete function is even simpler because we just need to execute the DELETE statement and return any error that the Spin SDK surfaces:
func DbDelete(id int) error {
db_url := getDbUrl()
statement := "DELETE FROM posts WHERE id=$1"
params := []postgres.ParameterValue{
postgres.ParameterValueInt32(int32(id)),
}
_, err := postgres.Execute(db_url, statement, params)
return err
}
Pagination
One last improvement to make here is that our DbReadAll function currently dumps the entire table out into the response. Let’s add some simple pagination to that. My naive implementation will assume two query parameters limit and offset. The user of the API will be responsible for maintaining their pagination state; I’m sure there is a more complete implementation but this will at least get us started. Here’s how I was able to get the parameters (with defaults) from the URL:
func getPaginationParams(req *http.Request) (limit int, offset int) {
// helper function to clamp the value
clamp := func(val int, min int, max int) int {
if val < min {
return min
} else if val > max {
return max
} else {
return val
}
}
// get the limit from the URL
limit_param := chi.URLParam(req, "limit")
if limit_val, err := strconv.Atoi(limit_param); err != nil {
// error occurred, just use a default value
fmt.Printf("Failed to parse the limit from URL: %v", err)
limit = 5
} else {
// clamp the value in case of invalid parameters (intentional or otherwise)
limit = clamp(limit_val, 0, 25)
}
// get the offset from the URL
offset_param := chi.URLParam(req, "offset")
if offset_val, err := strconv.Atoi(offset_param); err != nil {
// error occurred, just use a default value
fmt.Printf("Failed to parse the offset from URL: %v", err)
offset = 0
} else {
// clamp the value in case of invalid parameters (intentional or otherwise)
// limiting this one to 10,000 because I find it unlikely that anyone will post 10k times :)
offset = clamp(offset_val, 0, 10000)
}
return limit, offset
}
Like most APIs with pagination, we should take care to clamp the arguments in case a bad actor tries to write a url like /api/post?limit=1000000000000 to try and implement a denial of service attack. All that is left is to pass those parameters into our DbReadAll function and pass those as parameters to our Postgres query:
func DbReadAll(limit int, offset int) ([]Post, error) {
db_url := getDbUrl()
statement := "SELECT id, author_id, content, type, data, visibility FROM posts ORDER BY id LIMIT $1 OFFSET $2"
params := []postgres.ParameterValue{
postgres.ParameterValueInt64(int64(limit)),
postgres.ParameterValueInt64(int64(offset)),
}
rowset, err := postgres.Query(db_url, statement, params)
if err != nil {
return []Post{}, fmt.Errorf("Error reading from database: %s", err.Error())
}
posts := make([]Post, len(rowset.Rows))
for i, row := range rowset.Rows {
if post, err := fromRow(row); err != nil {
return []Post{}, err
} else {
posts[i] = post
}
}
return posts, nil
}
Summary
Overall the experience of using outbound Postgres in Go is similar to the Spin Rust SDK. I’m pretty happy with where we ended up for this component. One challenge I did run into was finding a library that can verify our JWT token in tinygo. This is a limitation of tinygo because it lacks implementations for the reflect system package. I’ll have to pull the logic out of our profile Spin Component into its own component so that I can execute the same logic in Go using an outbound HTTP call to the component. That’s an improvement I’ll have to make another day.
Hopefully you found this blog post and the previous posts helpful in your journey to build applications using Fermyon’s Spin SDK. As always, feel free to add comments to the pull request (link above) or reach out to me on Discord to chat.
Next time we’ll take a look at deploying this application to Fermyon Cloud, how we can automate that process using GitHub actions and we might have some new features coming out with Spin and Fermyon cloud that we’ll be able to utilize wink wink. Stay classy, Fermyon friends.