Test Super Graph with Cockroach DB

[dosco]

What would you like to be added:

Document what it would take to get Super Graph working with Cockroach DB a distributed database that is designed to be compatible with Postgres. Super Graph makes use of a bunch of Postgres specific features like querying for database tables and columns, lateral joins, json functions etc. I have never tried using Super Graph with Cockroach if someone can take the time to do this and document what worked and what did not. And maybe what it would take to get this working.

Why is this needed:

Since Cockroach DB is Postgres compatible this would help Super Graph work with a massively scalable distributed database and possibly help the Cockroach DB team see what gaps can be filled in their compatibility layer.

[howesteve]

Hello,

Would be a nice feature but several features are lacking:

https://www.cockroachlabs.com/docs/v19.2/postgresql-compatibility.html#unsupported-features

The authorization generator supergraph already implemented could cover the lack of RLS, and lateral joins appear to have recently been implemented.

What bothers me the most is lack of stored procedures, triggers and events. Will be very hard to implement subscriptions, requiring polling, what could overload the server...

[dosco]

I don't expect a distributed DB to match Postgres feature for feature mostly since implementing these features on a distributed system is much harder than a monolith like Postgres. I'm mostly looking to test if the SQL generated by Super Graph works on Cockroach DB. Lack of RLS, Triggers etc does not affect Super Graph. In almost all distributed DB's those features are implemented app side or using a separate service like Kafka.

[jinxmcg]

how about yugabyte it has stored Procedures and Triggers

I did not test it though so I don't know if it fits.

[dosco]

Sure sounds like a great idea. I did some digging and it seems Yugabyte is Postgres compatible and quite feature rich too. I created a separate ticket to track this. #29

[dosco]

@jinxmcg FYI Yugabyte works with Super Graph https://supergraph.dev/guide.html#yugabytedb

[paulm17]

How is Cockroach DB compatibility going? Thanks

[dosco]

I have a test in place but haven't gotten around to playing with it more.
https://github.com/dosco/graphjin/blob/00c26bab4c922073ebe09a5b2845fdb466d9832d/core/core_test.go

cd core
go test -v -db cockroach ./...

[dosco]

Thanks for bringing this up. I got curious seems like it's not too bad most tests pass (fake fail) the tests mostly all pass the failures are because cockcoach seems to randomly sort json key-values. Probably cause they use Go maps internally which has this behaviour. In short the result is correct the test fails to compare the values correctly.

subs_test.go:201: expected '{"users": {"id": 42, "email": "user42@test.com"}}' got '{"users": {"email": "user42@test.com", "id": 42}}'

[paulm17]

Sounds good. Right now I'm developing against Postgres. I'll take a look with CDB when I get some free time next week and will follow up.

[dosco]

this exists.