Persisting data

Json serialization

We recommend persisting objects using a hybrid relational and nosql model where nosql data is stored as json blobs in a database. You can use different strategies such as pure-relational and you can also use an ORM. We have written a blog post on why we recommend using a hybrid approach Sql DB, NoSql Schema that you may find interesting.

In the file /internal/app/list/list.go lets prepare the List struct for persistence by adding json serialization annotations.

type List struct {
	ID    string    `json:"id"`
	Name  string    `json:"name"`
	Items []string  `json:"items"`
}

Create a table

Add the create table sql statement to the database migration that is performed at server startup. The migrations can be found at /internal/db/migrations/migrations.sql.

CREATE TABLE IF NOT EXISTS lists (
    id TEXT PRIMARY KEY,
    blob JSONB NOT NULL,
);

Database CRUD functions

Lets create a new file called /internal/app/list/list_database.go for the database CRUD functions.

mytodo
|--internal
|  |--app
|     |--list
|        |--list.go
|        |--list_db.go

The database CRUD functions will take a context and transaction, and execute sql commands to perform the functions. Here is an example of /internal/app/list/list_database.go. Note that these functions are private.

package list

import (
	"context"
	"database/sql"
	"encoding/json"
)

func createList(ctx context.Context, tx *sql.Tx, list *List) error {
	jsonb, err := json.Marshal(&list)
	if err != nil {
		return err
	}
	_, err = tx.ExecContext(ctx, `
		INSERT INTO lists (
			id,
			blob
		) VALUES (
		 	$1, $2
		)`,
		list.ID,
		jsonb,
	)
	return err
}

func getListByID(ctx context.Context, tx *sql.Tx, ID string) (*List, error) {
	var data []byte
	err := tx.QueryRowContext(ctx, `
		SELECT blob 
		FROM lists
		WHERE id = $1`,
		id).Scan(&data)
	if err != nil {
		return nil, err
	}
	var list List
	return &v, json.Unmarshal(data, &v)
}

func updateList(ctx context.Context, tx *sql.Tx, list *List) error {
	jsonb, err := json.Marshal(&list)
	if err != nil {
		return err
	}
	_, err = tx.ExecContext(ctx, `
		UPDATE lists
		SET 
			blob = $1
		WHERE id = $2`,
		jsonb,
		list.ID)
	return err
}

func getLists(ctx context.Context, tx *sql.Tx, Name string) ([]List, error) {
	rows, err := tx.QueryContext(ctx, "SELECT blob FROM lists LIMIT 5000",
		Name)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	vs := []List{}
	for rows.Next() {
		var data []byte
		if err = rows.Scan(&data); err != nil {
			return nil, err
		}
		var v List
		if err = json.Unmarshal(data, &v); err != nil {
			return nil, err
		}
		vs = append(vs, v)
	}
	return vs, nil
}

Update the API

Now we can update the API functions in /internal/app/list/list.go to call the database functions.

func NewList(ctx context.Context, tx *sql.Tx, name string) (*List, error) {
	l := &List{
		ID:   uuid.NewString(),
		Name: name,
	}

	err := createList(ctx, tx, l) // call the private persistence function
	if err != nil {
		return nil, err
	}

	return l, nil
}

func AddItem(ctx context.Context, tx *sql.Tx, listID string, item string) error {
	l, err := getListByID(ctx, tx, listID)
	if err != nil {
		return err
	}

	// Check if the list is full
	if len(l.Items) >= MaxItems {
		return fmt.Errorf("todo list is full")
	}

	l.Items = append(l.Items, item)

	err = updateList(ctx, tx, l) // call the private persistence function
	if err != nil {
		return err
	}

	return nil
}