Repository pattern and Joining table in go

As for join part your question is quite trivial to answer, however for DDD there are a lot of hurdles from current language possibilities. But I will give a try..

Ok, let's imagine that we are developing an educational courses backend with multilanguage support where we need to join two tables and subsequently map to object. We have two tables (the first one contains language-independent data and second which contains language-dependent) If you are a repository advocate, then you will have something like that:

// Course represents e.g. calculus, combinatorics, etc.
type Course struct {
    ID     uint   `json:"id" db:"id"`
    Name   string `json:"name" db:"name"`
    Poster string `json:"poster" db:"poster"`
}

type CourseRepository interface {
    List(ctx context.Context, localeID uint) ([]Course, error)
}

then implementing it for sql db we will have something like that:

type courseRepository struct {
    db *sqlx.DB
}

func NewCourseRepository(db *sqlx.DB) (CourseRepository, error) {
    if db == nil {
        return nil, errors.New("provided db handle to course repository is nil")
    }

    return &courseRepository{db:db}, nil
}

func (r *courseRepository) List(ctx context.Context, localeID uint) ([]Course, error) {

    const query = `SELECT c.id, c.poster, ct.name FROM courses AS c JOIN courses_t AS ct ON c.id = ct.id WHERE ct.locale = $1`
    var courses []Course
    if err := r.db.SelectContext(ctx, &courses, query, localeID); err != nil {
        return nil, fmt.Errorf("courses repostory/problem while trying to retrieve courses from database: %w", err)
    }

    return courses, nil
}

The same applies to different related objects. You just need patiently model a mapping of your object with underlying data. Let me give another example.

type City struct {
    ID                      uint            `db:"id"`
    Country                 Country         `db:"country"`
}

type Country struct {
    ID   uint  `db:"id"`
    Name string `db:"name"`
}

// CityRepository provides access to city store.
type CityRepository interface {
    Get(ctx context.Context, cityID uint) (*City, error)
}

// Get retrieve city from database by specified id
func (r *cityRepository) Get(ctx context.Context, cityID uint) (*City, error) {

    const query = `SELECT 
    city.id, country.id AS 'country.id', country.name AS 'country.name',
    FROM city JOIN country ON city.country_id = country.id WHERE city.id = ?`

    city := City{}
    if err := r.db.GetContext(ctx, &city, query, cityID); err != nil {
        if err == sql.ErrNoRows {
          return nil, ErrNoCityEntity
        }
        return nil, fmt.Errorf("city repository / problem occurred while trying to retrieve city from database: %w", err)
    }

    return &city, nil
}

Now, everything looks clean until you realize that Go actually (as for now) does not support generics and in addition people in most situations discourage to utilize reflect functionality because it makes your program slower. In order to completely upset you imagine that from this moment you need transactional functionality....

If you came from other languages, you can try achieving it with something like that:

// UnitOfWork is the interface that any UnitOfWork has to follow
// the only methods it as are to return Repositories that work
// together to achieve a common purpose/work.
type UnitOfWork interface {
    Entities() EntityRepository
    OtherEntities() OtherEntityRepository
}

// StartUnitOfWork it's the way to initialize a typed UoW, it has a uowFn
// which is the callback where all the work should be done, it also has the
// repositories, which are all the Repositories that belong to this UoW
type StartUnitOfWork func(ctx context.Context, t Type, uowFn UnitOfWorkFn, repositories ...interface{}) error

// UnitOfWorkFn is the signature of the function
// that is the callback of the StartUnitOfWork
type UnitOfWorkFn func(ctx context.Context, uw UnitOfWork) error

I intentionally missed an implementation because it looks monstrous for sql and deserves its own question (the idea is that unit of work has its versions of repositories decorated with started tx under the hood) and after you beat this problem you will have more or less

err = svc.startUnitOfWork(ctx, uow.Write, func(ctx context.Context, uw uow.UnitOfWork) error {

            // _ = uw.Entities().Store(entity)
            // _ = uw.OtherEntities().Store(otherEntity)

            return nil
        }, svc.entityRepository, svc.otherEntityRepository)

so here you reach the final and in most cases people started to say that you write code which seems not idiomatic referring something like that. The point is that concepts are written too abstract and it is a philosophical question whether materialized DDD is even applicable in Golang or you can just partially mimic it. If you want flexibility, choose database once and operate with pure db handle

Depending on the data you want to read the solution will be different:

If the tables you want to join form a single aggregate, then just join them in your query and always return and store the full aggregate. In this case, you have only a repository for the root entity. This is likely not your scenario because you said you have repositories for the other entities you want to join (unless you have a design issue).

If the tables you want to join belong to different bounded contexts, you shouldn't join them. A better approach is to submit one query on each bounded context so that they remain decoupled. These multiple queries will come from different places, depending on your architecture: directly from the client, from an API gateway, from some sort of application service, etc.

If the tables belong to a single bounded context, but from multiple aggregates, then the cleanest approach is to follow CQRS (Command/Query segregation). In simple terms, you define a specific interface for your query, with the inputs and outputs that you need for the use case that you are implementing. This separation frees you from the limitations that you find trying to use your Commands infrastructure for queries (the 1 to 1 entity/repository relationship that you have). The naive implementation of this query interface could be a query that does a join of your existing tables. This is quick and easy to implement, but it means that your Commands and Queries are separated in code, but not at the DB level. Ideally, you would create a (denormalized) read model table in your database, with all the columns that you need for that specific query and updated every time one of the source tables is updated (this is normally done through domain events). This allows you to optimize your tables for your queries, with the right columns, data format, and indexes, but as a downside, it introduces some complexity and eventual consistency between the write and the read model.