Sqlite subpackage: best practice to create associated data

[luzzi8]

Hello there.

I'm trying to build an app according to the proposed "wtf" architecture.

In your blog post Real World SQL Part 1 you wrote:

Working with associated data (aka joins) is a complicated topic and I'll be covering that in the next post.

This is exactly where I am a bit stuck.

Specifically I am wondering how you would create associated data in a given sqlite subpackage service.

For example, sqlite.DialService.FindDialByID() method uses the helper attachDialAssociations().

https://github.com/benbjohnson/wtf/blob/main/sqlite/dial.go#L40

However, how would you implement this if it was about creating associated data?

To give a more natural example to talk about, let's assume a relation "Person has many Addresses".

What would sqlite.PersonService.CreatePerson() look like?
Would it use a helper createPersonAssociations() which would use an instance of sqlite.AddressService to call CreateAddress()? How to do this in one db transcation, so changes can be rolled back in case of error?

To make this the example more advanced and introduce an actual "join table" we could say "Person has many Addresses through AddressDetails",

Person: name: John
AddressDetail: description: "apartment 7"
Address: label: "Times Square 8, New York"

Person: name: Sarah
AddressDetail: description: "53rd floor"
Address: label: "Times Square 8, New York"

Thank you kindly,

luzzi

[benbjohnson]

Hi @luzzi8. Good question and sorry for the delay getting back to you. For creation, I would use the helper functions (e.g. createAddress()) within the CreatePerson() method:

func (s *PersonService) CreatePerson(ctx context.Context, person *wtf.Person) error {
	tx, err := s.db.BeginTx(ctx, nil)
	if err != nil {
		return err
	}
	defer tx.Rollback()

	// Create root person object here.
	if err := createPerson(ctx, tx, person); err != nil {
		return err
	}

	// Loop over associated addresses and create them here.
	for _, address := range person.Addresses {
		address.PersonID = person.ID // associate one-to-many relationship here.
		if err := createAddress(ctx, tx, address); err != nil {
			return err
		}
	}

	// Go ahead and rebuild associations here so we return an object that's
	// consistent with what FindPersonByID() would return.
	if err := attachPersonAssociations(ctx, tx, person); err != nil {
		return err
	}
	return tx.Commit()
}