Can't pass a version to Knex

[joeinnes]

Preflight Checklist

• I have completed all Troubleshooting Steps.
• I'm on the latest version of Directus.
• There's no other issue that already describes my problem.

Describe the Bug

DB_VERSION does not get passed to Knex. This is not strictly a bug I suppose, but more of a quirk. Knex considers the version not to be part of the connection info, but top-level. This means that setting DB_VERSION results in the property getting added to the connection configuration rather than top level.

Options I can see:

• Do not support Postgres DBs which don't talk properly (NB: I was trying to set this up on bit.io, which responds to select version(); without the version column name, meaning that this line throws an error because version is not defined: https://github.com/knex/knex/blob/4c589a3bfa4b0c734d7d059a6b61cf9fbbc0c9f2/lib/dialects/postgres/index.js#L129
• Add version: env.DB_VERSION here:

  directus/api/src/database/index.ts

  Line 55 in 1481380

  client: env.DB_CLIENT,

Happy to quickly test and then put in a PR for the latter if it helps.

From the docs:

All DB_* environment variables are passed to the connection configuration of a Knex instance (opens new window). Based on your project's needs, you can extend the DB_* environment variables with any config you need to pass to the database instance.

From Knex's docs:

Note: The database version can be added in knex configuration, when you use the PostgreSQL adapter to connect a non-standard database.

const knex = require('knex')({
  client: 'pg',
  version: '7.2',
  connection: {
    host : '127.0.0.1',
    port : 3306,
    user : 'your_database_user',
    password : 'your_database_password',
    database : 'myapp_test'
  }
});

To Reproduce

N/A

Errors Shown

N/A

What version of Directus are you using?

9.4.3

What version of Node.js are you using?

14.16.0

What database are you using?

Postgres 12.5 (via https://bit.io)

What browser are you using?

Brave

What operating system are you using?

macOS

How are you deploying Directus?

Docker

[joeinnes]

FWIW I also contacted the folks at bit.io and suggested they fix their Postgres.

[Oreilles]

Add version: env.DB_VERSION here:

This is a good solution, I'd gladly review your PR.