knex.js has added support for cockroachDB and @adonisjs/lucid package uses knex internally so should work out of the box right? nope 

In knex one can simply do:

const config = {
  client: "cockroachdb",
  connection: process.env.DATABASE_URL,
}

// Connect to database
const client = Knex(config);

but that configuration in the database.ts file of adonis will error out. something about lucid not having the "cockroachdb" dialect mapped with knex.js.

So what can we do?

• isn't cockrochDB same as postgres? can we just use

postgres: {
      client: 'postgres',
      connection: {
        host: Env.get('DB_HOST'),
        ...
}

that'll also throw some really weird errors, but its a step in the right direction, the direction being a hack/workaround to move forward and get it working.

Problem 2

cockroachDB provides a connection string with 

/defaultdb?sslmode=verify-full&options=--cluster%3D[clustername]

how does one configure a cluster in adonis.js database.ts file? there is no documentation around this. If you search for this you will come across one guy trying to attempt this here https://github.com/adonisjs/lucid/issues/540 and that's around May 2020 without a resolution but that issues only addresses  mongo's connection config. Back to our task - for this problem the solution is simple, so let's get out of the way.

Let's configure the connection in the following way

postgres: {
      client: 'postgres',
      connection: {
        host: Env.get('DB_HOST'),
        port: Env.get('DB_PORT'),
        user: Env.get('DB_USER'),
        password: Env.get('DB_PASSWORD'),
        database: 'culster-name.defaultdb', // clusterName + '.' + dbName
        ssl: { rejectUnauthorized: false } //this takes care of the SSL option
      },
      healthCheck: true,
      debug: true
    }

with that we are done with the database config for problem 2. Now let's move on to problem 1.

It's hard to believe that the blocker here is knex.js npm package. If you look at the source code of the package you will notice that when lucid tries to connect to a postgres DB via knex, knex internally runs a query to get the postgres version.

this happens in the following file :

node_modules/knex/lib/dialects/postgres/index.js

You can also see this in their source code here https://github.com/knex/knex/blob/master/lib/dialects/postgres/index.js#L125

So what's the problem? the problem is that cockroachDB doesn't support the query that knex is running to get the version. So we need to override that query and make it work for cockroachDB.

So how do we do that? well there might be an Adonis.js way but we were unable to find any so, first thought was : how can we edit the knex.js npm package cuz at this point we just wanted to get it working

What's the best way to edit a npm package? There are many way but the one we chose was to use an npm package called patch-package. This package allows you to edit the source code of a npm package and then it will create a patch file for you. You can then commit that patch file and it will be applied when you run npm install.

So let's do that. First install the patch-package npm package

yarn add patch-package postinstall-postinstall --dev

To make sure you are able to run the patch-package command after you deploy, add the following line to your package.json file

"scripts": {
  "postinstall": "patch-package"
 }

then edit the following file :

node_modules/knex/lib/dialects/postgres/index.js

//line 125 to 129
checkVersion(connection) {
    return new Promise((resolve, reject) => {
      resolve(22) //random high number for now
    });
  } 

What's the impact of returning a random high number? it helps the caller decide between jsonb and json as you can see here https://github.com/knex/knex/blob/0918bf9bdea3e0a4bb5cb0e9013e09fc75a1dd92/lib/dialects/postgres/schema/pg-columncompiler.js#L145

then run the following command

yarn patch-package knex

that will create a patch file for you and you can commit that file. Now when you run yarn install it will apply the patch.

Now let's try to run the app again. It should work now.