Follow odata v4 specifications?

[FGRibreau]

We might want to follow that in postgrest for pagination, field expansion, field selection etc... https://www.odata.org/documentation/

• pagination is done through $top & $skip https://docs.oasis-open.org/odata/odata/v4.01/csprd06/part2-url-conventions/odata-v4.01-csprd06-part2-url-conventions.html#_Toc21425354
• operators on strings are standardized https://docs.oasis-open.org/odata/odata/v4.01/csprd06/part2-url-conventions/odata-v4.01-csprd06-part2-url-conventions.html#_Toc21425292
• etc..

Just posting it here because I did not see any discussion around it :)

[steve-chavez]

What would be the advantages of adopting those specifications?

[FGRibreau]

Instead of postgrest own way of declaring filter operators and pagination we can rely on a standardized way to do things.

Thus promoting standard instead of doing/maintaining our own ^^'

[steve-chavez]

I had a glimpse of that spec. It uses url unsafe characters(the query string contains slashes and lots of spaces) which implies not being url friendly and needing to urlencode to share. The usage of $ makes the url harder to read too. Overall that spec doesn't look like an improvement to what we have.

Also I don't think of it as a standard — since that would imply it's normally used/accepted — because I haven't seen any API in the wild following that spec(unlike, say, graphql apis).

Now, we do follow several standards. For example, for pagination, we follow RFC 7233. The query string syntax does follow our own way.

Instead of being caught up in the Fire and Motion of trying to comply with other specs, what I think we should do is to properly document our query string syntax(some advances in PostgREST/postgrest-docs#228) and do a draft spec in our docs.

I say this(and think it's worth doing) because PostgREST has influenced REST implementations(blockapps API) and possibly other specs.

That would be much more easier to do and maintain for us.

[ako]

Benefit of odata is that there are a number of useful tools that already have odata support built in.

OData is used heavily by SAP and Microsoft to expose data. On the consuming side you have tools like PowerBI, Excel, Tableau, Lyncpad, Microsoft SSIS (ETL) that can directly read odata. Odata provides users the option to do analytics on data where the database is not directly available.

[steve-chavez]

Realistically speaking, if compatibility with another spec is desired, the best way to do it would be translating the request at the proxy layer. In a simillar manner to what ppKrauss/PostgREST-writeAPI does.

[wolfgangwalther]

It seems like the result of this discussion is the goal to better document the existing query string syntax. PostgREST/postgrest-docs#228 is still open for that, so I am closing this here.

[shayneoneill]

Was this going to be a wont-do ? Theres an integration I have that needs odata but the closest to a solution I've found is one of those awful random cloud offerings where they wont even tell you a price unless you talk to a salesman (Thats an instant "NOPE" from me. hidden prices means something to hide.)

[steve-chavez]

Theres an integration I have that needs odata but the closest to a solution I've found is one of those awful random cloud offerings where they wont even tell you a price unless you talk to a salesman

Yeah, I see those too. No true OSS alternative. Also now some products offer OData integrations.

Nowadays we have better separation of concerns in our codebase. We have ASTs for the different operations:

postgrest/src/PostgREST/Plan/ReadPlan.hs

Lines 30 to 48 in 60fe59c

	data ReadPlan = ReadPlan
	{ select :: [(CoercibleField, Maybe Cast, Maybe Alias)]
	, from :: QualifiedIdentifier
	, fromAlias :: Maybe Alias
	, where_ :: [CoercibleLogicTree]
	, order :: [CoercibleOrderTerm]
	, range_ :: NonnegRange
	, relName :: NodeName
	, relToParent :: Maybe Relationship
	, relJoinConds :: [JoinCondition]
	, relAlias :: Maybe Alias
	, relAggAlias :: Alias
	, relHint :: Maybe Hint
	, relJoinType :: Maybe JoinType
	, relIsSpread :: Bool
	, depth :: Depth
	-- ^ used for aliasing
	}
	deriving (Eq, Show)

postgrest/src/PostgREST/Plan/MutatePlan.hs

Lines 19 to 46 in 60fe59c

	data MutatePlan
	= Insert
	{ in_ :: QualifiedIdentifier
	, insCols :: [CoercibleField]
	, insBody :: Maybe LBS.ByteString
	, onConflict :: Maybe (PreferResolution, [FieldName])
	, where_ :: [CoercibleLogicTree]
	, returning :: [FieldName]
	, insPkCols :: [FieldName]
	, applyDefs :: Bool
	}
	| Update
	{ in_ :: QualifiedIdentifier
	, updCols :: [CoercibleField]
	, updBody :: Maybe LBS.ByteString
	, where_ :: [CoercibleLogicTree]
	, mutRange :: NonnegRange
	, mutOrder :: [CoercibleOrderTerm]
	, returning :: [FieldName]
	, applyDefs :: Bool
	}
	| Delete
	{ in_ :: QualifiedIdentifier
	, where_ :: [CoercibleLogicTree]
	, mutRange :: NonnegRange
	, mutOrder :: [CoercibleOrderTerm]
	, returning :: [FieldName]
	}

And the query string parsing is done on

postgrest/src/PostgREST/ApiRequest.hs

Line 143 in 60fe59c

qPrms <- first QueryParamError $ QueryParams.parse (pathIsProc && act `elem` [ActionInvoke InvGet, ActionInvoke InvHead]) $ rawQueryString req

In theory we could have an external Haskell library that translates the query string into that AST.

Likely I miss some details, but overall it seems more feasible now.

[HoldYourWaffle]

I would love to have an OData interface to PostgreSQL for similar reasons as @ako. Excel's "native" support is dreadful, OData tends to be more pleasant to use.

In theory we could have an external Haskell library that translates the query string into that AST.

@steve-chavez is this still the current state of things? I haven't touched Haskell before, but contributing in this area sounds much more fun than writing and maintaining my own custom solution :)

[steve-chavez]

@HoldYourWaffle That sounds great! Currently we do the query param parsing in https://github.com/PostgREST/postgrest/blob/c4ef9c09954a7695e215f18285c150fa13e41614/src/PostgREST/ApiRequest/QueryParams.hs. There's no separate library yet, but if you'd like to take a stab at this for now you could create another module. I assume we would also need a config since Odata and PostgREST query grammar could clash.

[steve-chavez]

I also recall @develop7 did some investigation on this too. IIRC, there's no haskell library that parses Odata currently.