ORM query service

[aaronc]

User Story

As a module developer, I would like the ORM to auto-generate a query service for clients so that I don't have to manually implement gRPC queries.

Possible Designs

None of these are mutually exclusive, it's a matter of prioritizing where we focus energy.

A) Generic gRPC service

This would involve creating a single gRPC endpoint that allows querying all the ORM tables in an app and returns responses using protobuf Anys. One possible approach is to support a set of operators similar to what is supported in mongodb.

Ex:

service Query {
  rpc Find(FindRequest) returns (FindResponse);
}

message FindRequest {
  string table = 1;
  repeated QueryOperator operators = 2;
  repeated Ordering order = 3;
  cosmos.base.query.v1beta1.PageRequest page = 4;
}

message FindResponse {
  repeated google.protobuf.Any results = 1;
  cosmos.base.query.v1beta1.PageResponse page = 2;
}

B) Generic JSON REST service

Same as the above but with JSON instead of protobuf to allow for a simpler more flexible query language along the lines of what is provided in mongo.

C) Generate gRPC .proto files for all tables

We could generate a query service for every ORM .proto file and dynamically serve it. For instance, for the bank example in the ORM tests, we would generate a query service somewhat like this:

service Query {
  rpc Balance(QueryBalanceRequest) returns (QueryBalanceResponse);
  rpc Balances(QueryBalancesRequest) returns (QueryBalancesResponse);
...
}

This would require another layer of codegen and is more similar to what we currently have with gRPC.

D) Graphql

While graphql is a departure from gRPC, it is likely better for building client apps and would allow for getting a bunch of objects in a single query and also traversing some joins (which could be done with additional protobuf annotations). A proposed design to follow is https://www.graphile.org/postgraphile/introduction/ which automatically generates Graphql services just from SQL table definitions.

Design Considerations

Some issues to address when designing this are:

Storage Types vs Presentation Types

See regen-network/regen-ledger#930. In particular, in lots of state tables we store addresses as bytes but users are used to seeing these usually as bech32 strings. All of these fields should be annotated with cosmos.AddressBytes so a smart query layer could do the conversion.

Logical Queries vs Direct Index Access

ORM queries in the state machine are currently done with direct index access and don't really have a separation between a logical and physical plan as more robust databases do. This is probably good for state machines generally because it is more predictable in terms of performance and gas consumption. gRPC queries are also generally written as direct index access just because that's easier to do.

An auto-generated client query layer could expose indexes directly as we currently do or include a more generic set of logical query filters like MongoDB or SQL. The latter would require some query planner even if it is fairly naive. The advantage of full logical queries are:

• allows for the same queries regardless of which indexes exist
• is stable between different versions of a state machine which may add/remove indexes
• allows for the same queries to be retargeted against a traditional DB so that clients can write the same app where the backing data store is direct state or something like Postgres or Mongo with indexed data

For these reasons, I'm leaning towards supporting logical queries with the most naive query planner that is non-trivial (meaning that indexes are selected based on some very simple rule-based heuristics and in memory filtering and sorting are the fallback for all non-trivial cases).

[robert-zaremba]

I know that my opinion may not be the most popular one, but honestly, if we knock out an out of the box integration with a DB with tonnes of integrations from the get go, like: Postgresql (graphql) or Mongodb (mongodb cli), then instead of reinventing and maintaining solutions we embrace existing leading solutions with web2 world with tonnes of integration and tooling.

@aaronc mention that we still need historical queries (tbh, I don't know any app except some bridges which may require it). So maybe only querying by a primary key is enough?

[aaronc]

I know that my opinion may not be the most popular one, but honestly, if we knock out an out of the box integration with a DB with tonnes of integrations from the get go, like: Postgresql (graphql) or Mongodb (mongodb cli), then instead of reinventing and maintaining solutions we embrace existing leading solutions with web2 world with tonnes of integration and tooling.

@aaronc mention that we still need historical queries (tbh, I don't know any app except some bridges which may require it). So maybe only querying by a primary key is enough?

I totally agree we should make it really easy to index state in conventional databases like Postgres and Mongo. However, I also think the SDK should provide decent out of the box querying which is the current status quo. The reasons in my mind for this are:

• it's a much simpler deployment - a single binary without lots of devops. Especially in testing and experimental scenarios needing to deploy postgres &/or mongo could be a deal breaker
• people are already used to it
• IAVL and store/v2 support historical queries which is actually pretty hard to engineer into a major conventional DB, and the only DBs that do support this would require a fair amount of extra work and involve complex deployments and other limitations (see Datomic or Crux)
• proofs and queries supported at the same layer

[aaronc]

My thinking right now is to move forward with approach A & D together, starting with A. Not sure about the logical query layer. On one hand it is sort of better and it's not that hard to implement it naively. But also it obscures performance problems if there's no plan to improve it. So direct index access may be the simple way to get the most bang for the buck and further efforts can be placed on indexing in other dbs as @robert-zaremba suggests.

[aaronc]

#11791 proposes .proto definitions for a generic gRPC query service (approach A) that uses direct index access rather than logical queries

[aaronc]

I discussed this a bit with @robert-zaremba today and I think I agree with his point that there's a risk of postponing the inevitable need of a lot of projects to break queries out into a secondary layer as the project grows.

I still think we should provide out-of-the-box queries as that will be desirable at the experimental stage. But I think it may be worth the effort to define a stable API with logical queries that can be retargetted against other DBs as a project grows.

In particular, there's a risk with direct index access of encouraging clients to build too much around available on-chain indexes. And I think we actually should allow a module remove an index if it is only used by clients but not the state machine, with the understanding that this can easily be offloaded to other DBs.

So I'm thinking of closing #11791 and proposing a logical query layer that is sufficent but also not too complex probably in an ADR.