Generated code returns nil slice instead of empty slice for zero results

[vdemcak]

What do you want to change?

When generating code for queries that return slices, the generated code initializes the slice as nil instead of an empty slice. When this nil slice is marshaled to JSON, it becomes null instead of [], which can be unexpected for API consumers who expect consistent array responses.

Current Behavior

Currently generated code:

func (q *Queries) ListUserOrganizations(ctx context.Context, userID string) ([]ListUserOrganizationsRow, error) {
    rows, err := q.db.Query(ctx, listUserOrganizations, userID)
    if err != nil {
        return nil, err
    }
    defer rows.Close()
    var items []ListUserOrganizationsRow  // initializes as nil
    for rows.Next() {
        var i ListUserOrganizationsRow
        if err := rows.Scan(&i.ID, &i.Name); err != nil {
            return nil, err
        }
        items = append(items, i)
    }
    return items, nil  // returns nil if no rows found
}

When marshaled to JSON:

{
    "data": null
}

Desired Behavior

Proposed generated code:

func (q *Queries) ListUserOrganizations(ctx context.Context, userID string) ([]ListUserOrganizationsRow, error) {
    rows, err := q.db.Query(ctx, listUserOrganizations, userID)
    if err != nil {
        return nil, err
    }
    defer rows.Close()
    items := []ListUserOrganizationsRow{}  // initialize as empty slice
    for rows.Next() {
        var i ListUserOrganizationsRow
        if err := rows.Scan(&i.ID, &i.Name); err != nil {
            return nil, err
        }
        items = append(items, i)
    }
    return items, nil  // returns empty slice if no rows found
}

When marshaled to JSON:

{
    "data": []
}

Feature Request

Add a configuration option to control whether slice results should be initialized as nil or empty slices. This would allow users to choose between:

• Current behaviour (nil slices, marshals to null)
• Empty slices (marshals to [])

Use Case

This is particularly important for REST APIs where consumers expect consistent types in responses. Having an array field sometimes return null and sometimes [] can cause issues with client-side type checking and handling.

However, having this configurable in sqlc would provide a cleaner solution and consistent behavior across generated code.

What database engines need to be changed?

PostgreSQL

What programming language backends need to be changed?

Go

[kimbuba]

You can change this behavior by setting emit_empty_slices to true.

[beanbocchi]

Curious, for those not using the emit_empty_slices setting, how do you handle API responses that come back as null?

[vdemcak]

Hey, in the end, I've ended up manually creating structs for API responses and just re-mapping the entities to them like so:

func GetEntities(c *fiber.Ctx) error {
	entities, err := database.Queries.ListEntities(
		context.Background(),
	)
	if err != nil {
		l.Error().Err(err).Msg("Failed to retrieve entities")
		return c.Status(fiber.StatusInternalServerError).JSON(utils.Response{
			Success: false,
			Message: "Failed to retrieve entities",
		})
	}

	var res = dto.ListEntitiesResponse{
		Success: true,
		Message: "Entities retrieved successfully",
		Data:    make([]dto.Entity, 0),
	}

	for _, en := range entities {
		res.Data = append(res.Data, dto.Entity{
			ID: en.ID,

			SerialNumber: en.SerialNumber,
			ModelID:      en.ModelID,

			Metadata:    en.Metadata,
			Description: en.Description,

			Archived:  en.Archived,
			Public:    en.Public,
			CreatedAt: en.CreatedAt.Time,
			UpdatedAt: en.UpdatedAt.Time,
		})
	}

	return c.JSON(res)
}

I've realized, that this allows me for a much better control of the outgoing requests and prevents me from accidentally leaking some internal data in exchange for a bit more boilerplate. Also allows me to update the internal queries without changing/breaking the API response structure.