CockroachDB support

[worldofgeese]

Any chance of supporting CockroachDB? I have a Flask project I'd like to use Piccolo in.

[dantownsend]

@worldofgeese I haven't personally tried it yet, but it might already be possible.

It looks like CockroachDB is largely Postgres compatible.

And asyncpg seems to have have some CockroachDB awareness built in.

I'd be interested to know if it does work.

[sinisaos]

@dantownsend I've never used CockroachDB and I don't know if this is relevant but I managed to connect to the database with PostgresEngine like this

DB = PostgresEngine(
    config={
        "database": "taskdb",
        "user": "root",
        "password": "",
        "host": "localhost",
        "port": 26257,
    }
)

I had to remove the uuid-ossp from this line because this extension is not currently supported in CockroachDB (uuid will not work but this could to be workaround with gen_random_uuid()). Also timestamptz does not work so no migration can be done for session_auth (possible workoround). Standard columns work and I could do migrations for user table and task table and after that I could add records to that tables. This is a screenshot from a very nice Cockroachdb web console.

I’m not an expert but I think support for CockroachDB could be made without too many changes, but you’ll know better.

[dantownsend]

@sinisaos Thanks for looking into it - that's very helpful.

It seems like we could support CockroachDB without too many changes.

I assume that Timestamptz fails because the default value of current_timestamp isn't supported. This could be worked around.

How easy did you find it to install CockroachDB locally? We would also need to work out how to run it in GitHub Actions for our test suite.

[sinisaos]

How easy did you find it to install CockroachDB locally?

Local installation is quite simple (linux, mac). For my attempt, I launched an insecure cluster, but a secure cluster should not be difficult to manage.

We would also need to work out how to run it in GitHub Actions for our test suite.

For Github Actions I didinn't find any example.

[dantownsend]

@sinisaos Thanks, I'll try and get it running locally.

[dantownsend]

I think this is a cool idea, and is definitely possible. I won't be able to pick it up immediately though, so if anyone wants to take a look feel free.

[dantownsend]

We now have CockroachDB support in the latest version of Piccolo.