Support new query type for returning a repeated message instead of a really short stream

[iamneal]

Right now the plugin supports 2 types of SELECT queries

• Select one
• Stream many

Select one query example from here

service UServ {
	option (persist.service_type) = SQL;
    rpc SelectUserById(User) returns (User) {
		option (persist.ql) = {
			query: ["SELECT id, name, friends, created_on FROM users WHERE id = $1"],
			arguments: ["id"],
		};
	};
}

Stream many query example from here

service UServ {
	option (persist.service_type) = SQL;
	rpc GetAllUsers(Empty) returns (stream User) {
		option (persist.ql) = {
			query: ["SELECT id, name, friends, created_on FROM users"]
		};
	};
}

Our Select one example will generate grpc bindings for a unary call. We will get one result User back.

Our Stream many example seems appropriate in this situation. We do not know how many Users our database has. We should
stream back the users so our application does not have to hold all the Users in memory.

But what if our query instead looked like this:

// for our user now looks like:
message User {
	int64 id = 1;
	string name = 2;
	Friends friends = 3;
	google.protobuf.Timestamp created_on = 4;
         // new
        int64 best_friend_id = 5;
}

rpc GetUserAndBestFriend(User) returns (stream User) {
	option (persist.ql) = {
		query: ["SELECT * FROM users WHERE id IN ($1, $2) "],
                 arguments: ["id", "best_friend_id"]
	};
};

This query seems inefficient. We know we have only two Users, but the only way to return mutiple rows from the database
is to stream them back. We cannot have the server group the two users up and send them back.

It would be nice if we could somehow introduce a new query type. A Select many type.

Select Many query

The plugin knows we want to generate a repeated output by how we structure the return message:

// a proto of our database schema for reference
message User {
	int64 id = 1;
	string name = 2;
	Friends friends = 3;
	google.protobuf.Timestamp created_on = 4;
        int64 best_friend_id = 5;
}

message Request {
    int64 id = 1;
    int64 best_friend_id = 2;
}
// NEW 
message UserAndFriend {
    message InnerUser {
    	int64 id = 1;
    	string name = 2;
    	Friends friends = 3;
    	google.protobuf.Timestamp created_on = 4;
    }
    repeated InnerUser buddies = 1;
}

rpc GetUserAndBestFriend(Request) returns (UserAndFriend) {
	option (persist.ql) = {
		query: ["SELECT * FROM users WHERE id IN ($1, $2) "],
                 arguments: ["id", "best_friend_id"]
	};
};

RULES

• the response proto must define an inner message
• the response proto of the rpc call must have exactly 1 field
• the above defined field must be a repeated type of the defined inner message

You see this in the example above with the UserAndFriend protobuf message.

We can generate the code in go to correctly collect our InnerUser in a slice, and return a UserAndFriend as a Unary request.

This feature would greatly reduce the complexity of communicating through protoc-gen-persist grpc services.

[iamneal]

@namtzigla

[namtzigla]

no, this is going to be somehow supported in the query result mapping.