Atomically insert records inside transactions (extend with go)?

[ORO42]

Use case: When a user creates a project, they MUST have a member association with the project. On fail, neither project nor member should be created. I also have several other uses for this pattern in my app.

I have defined a custom protected route for creating a project record. Inside a transaction, I need to create a new project record, get the returned project record, and then create a new member record which links the authenticated user with the project. Finally, I need to return project with its member to the client via json response.

I have tried just about everything I found in the docs to return the project record after it gets created, and I cannot get it working. I saw docs on using client side batch but it doesn't look like it provides guaranteed data integrity.

See code comments for pseudocode steps.

se.Router.POST("/projects", func(e *core.RequestEvent) error {
	authRecord := e.Auth
	isGuest := e.Auth == nil
	isSuperuser := e.HasSuperuserAuth() // the same as "e.Auth != nil && e.Auth.IsSuperuser()"

	if isGuest || isSuperuser {
		return e.JSON(http.StatusInternalServerError, map[string]bool{"success": false})
	}

	userId := authRecord.Id

	// access request info
	info, err := e.RequestInfo()
	if err != nil {
		return e.JSON(http.StatusInternalServerError, map[string]bool{"success": false})
	}

	// parse body params
	title, ok := info.Body["title"].(string)
	if !ok {
		return e.JSON(http.StatusInternalServerError, map[string]bool{"success": false})
	}

	err = app.RunInTransaction(func(txApp core.App) error {

		// TODO: Insert project
		// populate project `user` relation field with `userId`
		// populate project `title` field with `title`

		// TODO: Insert a member record linking the user to the project
		// populate member `user` relation field with `userId`
		// populate member `project` relation field with the newly created project id
		// populate member `role` field with "owner"
		// populate member `accepted` field with `true`

		return nil
	})
	if err != nil {
		return e.JSON(http.StatusInternalServerError, map[string]bool{"success": false})
	}

     // TODO: Return project with its member

	return e.JSON(http.StatusOK, map[string]bool{"success": true})
}).Bind(apis.RequireAuth())

[ganigeorgiev]

I saw docs on using client side batch but it doesn't look like it provides guaranteed data integrity.

Could you elaborate more on what is lacking, aka. what do you mean by "data integrity"?

The Batch Web API will execute its requests in a single transaction, essentially the same as in your custom route (with the additional benefit that API rules will be also checked).

The main difference is that the submitted fields are client-side controlled, meaning that if you want to set system fields or to set some defaults from the server-side then you'll have to use the hooks.

I have tried just about everything I found in the docs to return the project record after it gets created, and I cannot get it working.

I don't understand what is the issue. What is the returned error?

Or you are just not sure how to create the record(s) programmatically? If that's the case you can check https://pocketbase.io/docs/go-records/#create-new-record. The only difference when inside of a transaction is that instead of app.Save(record) you have to use txApp.Save(record).
There is also a transaction example in https://pocketbase.io/docs/go-records/#transaction.

If you are asking for how to return the record that is created inside the transaction then you can simply declare the variable(s) outside of it. For example:

var project *core.Record

err = app.RunInTransaction(func(txApp core.App) error {
    project = core.NewRecord(yourCollection)
    ...
}) 

Or if what you want to return is some other record that indirectly depend on the created one, then you can query the database and return its result - https://pocketbase.io/docs/go-records/#fetch-records.

If this doesn't help, please try to be more specific about the actual issue.

[ganigeorgiev]

As a side-note, I'd suggest for error responses to return actual error value and not just write a response because middlewares will not be aware of the failed request. See also https://pocketbase.io/docs/go-routing/#error-response.

[ORO42]

simply declare the variable(s) outside of it

Ok, it was as simple as this. My issue is solved. I now have:

var projectRecord *core.Record
var memberRecord *core.Record

err = app.RunInTransaction(func(txApp core.App) error {

// Insert project
projectsCollection, err := app.FindCollectionByNameOrId("projects")
if err != nil {
	return err
}
projectRecord = core.NewRecord(projectsCollection)
projectRecord.Set("title", title)
projectRecord.Set("user", userId)
if err := txApp.Save(projectRecord); err != nil {
	return err
}

// Insert a member record linking the user to the project
membersCollection, err := app.FindCollectionByNameOrId("members")
if err != nil {
	return err
}
memberRecord = core.NewRecord(membersCollection)
memberRecord.Set("user", userId)
memberRecord.Set("project", projectRecord.Id)
memberRecord.Set("role", "owner")
memberRecord.Set("accepted", true)
if err := txApp.Save(memberRecord); err != nil {
	return err
}

I'd suggest for error responses to return actual error value

Thanks for this tip.

---

Regarding the batch API, how would I create the project, then use the project id to create the member? Essentially achieving the same behavior as the above go code from the client instead.

batch.collection('projects').create({ ... }); // create project
batch.collection('members').create({ ... }); // create member that needs project's id as a field

[ganigeorgiev]

Regarding the batch API, how would I create the project, then use the project id to create the member?

You can explicitly set the project's id and use it in the relation (it can be any random string that satisfies your collection id field requirements). You can find a similar example in #6977.