sqlc.embed(<right_join_table>) should generate NULLable fields in LEFT/RIGHT JOIN

[lordvidex]

Version

1.25.0

What happened?

I have some table A and I made a LEFT JOIN on table B, but i used the sqlc.embed(b) on the table that I joined and unfortunately, I got non-nullable types. The table that is OUTER JOINED should be always nullable but it seems the sqlc.embed somehow doesn't support this yet.

I worked around this by removing sqlc.embed for now, but the embed feature is really useful for my usecase.

Relevant log output

No response

Database schema

CREATE TABLE IF NOT EXISTS workflow(
   id BIGSERIAL PRIMARY KEY,
   org_id BIGINT NOT NULL,
   name TEXT NOT NULL,
   description TEXT NOT NULL
);
CREATE TABLE IF NOT EXISTS step (
    id BIGSERIAL PRIMARY KEY,
    workflow_id BIGINT NOT NULL REFERENCES workflow(id) ON DELETE CASCADE,
    index INT NOT NULL,
    title TEXT NOT NULL,
    description TEXT NOT NULL
);

SQL queries

SELECT wf.*, sqlc.embed(s) FROM (
    SELECT * from workflow w WHERE w.id = $1 LIMIT 1) wf 
LEFT JOIN step s ON s.workflow_id = wf.id

Configuration

No response

Playground URL

No response

What operating system are you using?

macOS

What database engines are you using?

PostgreSQL

What type of code are you generating?

Go

[sourcec0de]

I ran into this just now. A related consequence is that you end up with scan errors like this.

converting NULL to string is unsupported

[nikoprotic]

I just took down prod so I really could have used this.

[matthewpull]

+1 on this feature request.

In the example above you get models like:

type Step struct {
	ID          int64
	WorkflowID  int64
	Index       int32
	Title       string
	Description string
}

type Workflow struct {
	ID          int64
	OrgID       int64
	Name        string
	Description string
}

type WorkflowRow struct {
	ID          int64
	OrgID       int64
	Name        string
	Description string
	Step        Step
}

And the query:

func (q *Queries) GetWorkflow(ctx context.Context, id int64) (WorkflowRow, error) {
	row := q.db.QueryRow(ctx, workflow, id)
	var i WorkflowRow
	err := row.Scan(
		&i.ID,
		&i.OrgID,
		&i.Name,
		&i.Description,
		&i.Step.ID,
		&i.Step.WorkflowID,
		&i.Step.Index,
		&i.Step.Title,
		&i.Step.Description,
	)
	return i, err
}

Ideally what I'd want is having the Step in WorkflowRow be a pointer (i.e. nullable), so:

type WorkflowRow struct {
	ID          int64
	OrgID       int64
	Name        string
	Description string
	Step        *Step
}

The genereated query code would be a little more complex, but something like:

// This is only an example and I haven't tested it, but hopefully it conveys the idea.
func (q *Queries) GetWorkflow(ctx context.Context, id int64) (WorkflowRow, error) {
	row := q.db.QueryRow(ctx, workflow, id)
	var (
		i               WorkflowRow
		stepID          *int64
		stepWorkflowID  *int64
		stepIndex       *int32
		stepTitle       *string
		stepDescription *string
	)
	err := row.Scan(
		&i.ID,
		&i.OrgID,
		&i.Name,
		&i.Description,
		&stepID,
		&stepWorkflowID,
		&stepIndex,
		&stepTitle,
		&stepDescription,
	)
	if err != nil {
		return i, err
	}
	if stepID != nil {
		i.Step = &Step{
			ID:          *stepID,
			WorkflowID:  *stepWorkflowID,
			Index:       *stepIndex,
			Title:       *stepTitle,
			Description: *stepDescription,
		}
	}
	return i, nil
}

That way I can use it in my code like:

wrf, err := q.GetWorkflow(ctx, id)
if err != nil {
	return err
}
if wrf.Step == nil {
	fmt.Println("No step!")
} else {
	fmt.Printf("Got step with title: %s", wrf.Step.Title)
}

That implementation is very much just a rough example - I'm sure there are much better ways of doing it - but ultimately want I want is to be able to have a potentially-nil reference to the inner object. This way I can still still use very ergonomic struct embedding, while still being able to handle the case of the inner object sometimes being null.

Interested in other people's thoughts on all this!