Database Operations

In this chapter, you'll learn how to perform database operations in your module's service, such as running queries and wrapping several operations in a transaction.

Run Queries#

The service factory covers most of what a module's service needs: its generated methods and the internal service of each data model list, create, update, and delete records. Use them first.

For other needs, MedusaGo gives you three lower-level options, which you can use in the methods of your service.

Query With a Repository#

The internal service of a data model wraps its repository, a *dal.Repository that you can use directly. Its Find method takes a FindOptions value, with the filters in Where, and the Select, Relations, Order, Take, Skip, and WithDeleted options:

// postsFromRepository uses the repository of the Post model, which the
// internal service wraps.
func postsFromRepository(ctx context.Context, s *BlogModuleService) ([]*dal.Record, error) {
	take := 5
	return s.Internal("Post").Repository.Find(ctx, dal.FindOptions{
		Where:  rec("status", "published"),
		Select: []string{"id", "title"},
		Order:  []dal.OrderBy{{Field: "published_at", Direction: "DESC"}},
		Take:   &take,
	})
}

The repository also has the methods FindAndCount, Create, Delete, SoftDelete, Restore, Upsert, and UpsertWithReplace, which the internal service calls.

Run Raw SQL#

To run a SQL statement of your own, use the dal.QueryRows function with the database handle of the service, returned by its DB() method. The statement takes its arguments as $1, $2, and so on.

For example, to increase the views of a post with one statement:

// IncrementViews adds one view to a post with a single SQL statement and
// returns the new count.
func (s *BlogModuleService) IncrementViews(ctx context.Context, id string) (float64, error) {
	rows, err := dal.QueryRows(ctx, s.DB(),
		`update "post" set "views" = "views" + 1 where "id" = $1 and "deleted_at" is null returning "views"`, id)
	if err != nil {
		return 0, err
	}
	if len(rows) == 0 {
		return 0, fmt.Errorf("post %s was not found", id)
	}
	return rows[0]["views"].(float64), nil
}

QueryRows returns a []dal.Row. A dal.Row is a map[string]any of the row's columns. The values are converted the way Medusa converts them, so the types match its own: integer columns are float64, text columns are string, boolean columns are bool, timestamptz columns are time.Time, jsonb columns are decoded, and numeric and bigint columns stay string, to avoid losing precision.

The handle also has an Exec(ctx, sql, args...) method for statements that return no rows.

Perform Database Operations#

Each generated method that changes data runs in its own transaction: a call to createPosts either creates all its posts, or none. When a method of your service makes several calls that must succeed or fail together, wrap them in a transaction.

Wrap Database Operations in Transactions#

The InTransaction method of the service runs a function in a database transaction. The function receives a copy of the service that's bound to the transaction:

// SetPostTags replaces the tags of a post with the given values, creating
// the tags that do not exist yet. Everything runs in one transaction.
func (s *BlogModuleService) SetPostTags(ctx context.Context, postID string, values []string) error {
	return s.InTransaction(ctx, func(tx *utilsmodulessdk.MedusaService) error {
		tags := tx.Internal("Tag")

		wanted := make([]any, len(values))
		for i, v := range values {
			wanted[i] = v
		}
		existing, err := tags.List(ctx, rec("value", rec("$in", wanted)), utilsmodulessdk.FindConfig{})
		if err != nil {
			return err
		}

		ids := map[string]any{}
		for _, tag := range existing {
			value, _ := tag.Get("value")
			id, _ := tag.Get("id")
			ids[value.(string)] = id
		}

		var missing []*dal.Record
		for _, v := range values {
			if _, ok := ids[v]; !ok {
				ids[v] = nil
				missing = append(missing, rec("value", v))
			}
		}
		created, err := tags.Create(ctx, missing)
		if err != nil {
			return err
		}
		for _, tag := range created {
			value, _ := tag.Get("value")
			ids[value.(string)], _ = tag.Get("id")
		}

		tagIDs := make([]any, 0, len(ids))
		for _, id := range ids {
			tagIDs = append(tagIDs, id)
		}
		return tx.Internal("Post").ReplaceManyToMany(ctx, postID, "tags", tagIDs)
	})
}

In the SetPostTags method:

1. InTransaction starts a transaction and calls the function with tx.
2. The function lists the tags that exist, creates the missing ones, and replaces the tags of the post. It uses tx.Internal(...), so every operation is part of the transaction.
3. If the function returns an error, the transaction is rolled back and InTransaction returns the error. If it returns nil, the transaction commits.

Raw SQL in a transaction uses the handle of tx, tx.DB(). For example, the following method locks a post and moves the comments of another post to it:

// MoveComments moves the comments of a post to another post, and returns how
// many it moved. The target post is locked for the duration of the
// transaction, so it can't be deleted while its comments move.
func (s *BlogModuleService) MoveComments(ctx context.Context, fromPostID, toPostID string) (int, error) {
	moved := 0
	err := s.InTransaction(ctx, func(tx *utilsmodulessdk.MedusaService) error {
		posts, err := dal.QueryRows(ctx, tx.DB(),
			`select "id" from "post" where "id" = $1 and "deleted_at" is null for update`, toPostID)
		if err != nil {
			return err
		}
		if len(posts) == 0 {
			return fmt.Errorf("post %s was not found", toPostID)
		}

		rows, err := dal.QueryRows(ctx, tx.DB(),
			`update "comment" set "post_id" = $1 where "post_id" = $2 and "deleted_at" is null returning "id"`,
			toPostID, fromPostID)
		if err != nil {
			return err
		}
		moved = len(rows)
		return nil
	})
	return moved, err
}

The for update clause locks the target post's row until the transaction ends.

Nested Transactions#

When you call InTransaction while a transaction is already running, for example from a method that another transactional method calls, MedusaGo uses a savepoint. If the inner function fails, only its changes are rolled back, and the error propagates to the outer function, which rolls back the whole transaction if it returns the error.

Methods that call the generated methods on tx are part of the outer transaction as well.

Events and Transactions#

The events that the service emits for the changes of a transaction, such as blog.post.updated, are emitted when the outermost transaction commits. If it rolls back, they aren't emitted.

Transaction Options#

Medusa lets you pass options such as an isolation level to a transactional method. MedusaGo's InTransaction takes no options: it starts a transaction with the database's default isolation level. If you need a stricter guarantee, lock the rows you work on with for update in a raw statement, as in MoveComments.