Is there a way I can model relationships manually without code generation?

[theoribeiro]

I'd love to use Bob's SQL builder and executor without using the code generation. To start with, we're using CockroachDB which is not yet supported for generation. Additionally, we'd like to be in control of all queries and manually create them just as we would with Jet, for example.

Being able to use the executor to scan into a relationship like we can do with Carta, would be a big plus! Is there a way I can build a model manually just to use the Bob Executor with .One(), .All(), etc?

My intent would be to write something like:

type User struct {
   ID string `db:"id"`
   Name string `db:"name"`
   Age int `db:"age"`
   Posts []Post `db:"-"`
}

type Post {
   ID string `db:"id"`
   Title string `db:"title"`
   CreatedAt time.Time `db:"created_at"`
}

func PrintUserPosts() {
   ctx := context.Background()
   db, err := bob.Open("postgres", "...")
   if err != nil {
    // ...
   }

   q := psql.Select(...)

   users, err := bob.All(ctx, db, q, scan.StructMapper[User]())
   if err != nil {
      // ...
   }
   for _, u := range users {
      for _, p := range u.Posts {
          fmt.Println(u.Name, p.Title, p.CreatedAt)
      }
   }
}

[stephenafamo]

The best way would be to comb through the generated code and write similar code yourself, although it may not be as easy to model relationships similar to Jet.

Here is what the final code would look like:

type User struct {
	ID    string `db:"id"`
	Name  string `db:"name"`
	Age   int    `db:"age"`
	Posts []Post `db:"-"`
}

type Post struct {
	ID        string    `db:"id"`
	UserID    string    `db:"user_id"` // needed to match the relationship
	Title     string    `db:"title"`
	CreatedAt time.Time `db:"created_at"`
}

func loadUserPosts(ctx context.Context, exec bob.Executor, retrieved any) error {
	users, ok := retrieved.([]User)
	if !ok {
		return fmt.Errorf("expected []*User, got %T", users)
	}

	PKArgs := make([]bob.Expression, len(users))
	for i, user := range users {
		PKArgs[i] = psql.ArgGroup(user.ID)
	}

	posts, err := bob.All(ctx, exec, psql.Select(
		sm.Where(psql.Quote("posts", "user_id").In(PKArgs...)),
	), scan.StructMapper[Post]())

	// Don't cause an issue due to missing relationships
	if errors.Is(err, sql.ErrNoRows) {
		return nil
	}

	if err != nil {
		return fmt.Errorf("failed to load posts: %w", err)
	}

	for _, user := range users {
		user.Posts = nil
	}

	for _, o := range users {
		for _, rel := range posts {
			if o.ID != rel.UserID {
				continue
			}

			o.Posts = append(o.Posts, rel)
		}
	}

	return err
}

func PrintUserPosts() {
	ctx := context.Background()
	db, err := bob.Open("postgres", "...")
	if err != nil {
		// ...
	}

	q := psql.Select(psql.Loader(loadUserPosts))

	users, err := bob.All(ctx, db, q, scan.StructMapper[User]())
	if err != nil {
		// ...
	}
	for _, u := range users {
		for _, p := range u.Posts {
			fmt.Println(u.Name, p.Title, p.CreatedAt)
		}
	}
}