Getting Chirpstack working with CockroachDB

[proffalken]

CockroachDB is a PGSQL protocol-compatible database that has better replication/scaling facilities.

I'd like to switch over to using it as it's working well with some other things I'm doing, and would help make my LoRaWAN clusters more resilient.

At the moment, I'm stuck on https://github.com/brocaar/chirpstack-application-server/blob/master/migrations/0002_join_accept_params.sql#L9 throwing an "unsupported operation" error within CockroachDB (see cockroachdb/cockroach#43140 and go-gorp/gorp#359 for more on this), but whilst I chase those to be patched, I'm wondering if there are changes that can be made to the schema in order to move the reference configuration somewhere else and avoid the overlap?

I'm happy to keep this in a local working copy, and push for the gorp provider to be updated, however I'd love to get chirpstack working using this DB! :)

[brocaar]

I think the issue might not be with sql-migrate / gorp as in the end, it just executes the SQL statements that are in the .sql files. I expect when you execute the following within a CockroachDB shell, that you get exactly the same error:

create table channel (
	id bigserial primary key,
	channel_list_id bigint references channel_list on delete cascade not null,
	channel integer not null,
	frequency integer not null,
	check (channel >= 3 and channel <= 7 and frequency > 0),
	unique (channel_list_id, channel)
);

If you are able to solve this "unsupported operation", I expect you will see more errors (either in the migrations during runtime). I'm not sure if ClockroachDB supports all data types and extensions (hstore and pg_trgm for example).

PGSQL protocol-compatible database that has better replication/scaling facilities.

I think you should not read protocol-compatible as feature compatible. While this allows you to use the same database drivers, it doesn't mean that CockroachDB is a drop-in replacement.

[proffalken]

Thanks @brocaar - it looks like you're right given the comment in cockroachdb/cockroach#43140 (comment)

I'll keep hammering away at it, as it makes scaling the backend a lot easier, but I'll close this for now as I don't think it's Chirpstack related.