Documentation for knex and connections

[MarcGodard]

If you are using a free postgress, or a low connection database, make sure to change your pool settings.

Actually, you should change the min either way.

This is my postgresql.js file. I will be moving these connection variable into my config files, this is only used for dev (this is why the connections are low), but you should adjust the max to your max connections as this will speed stuff up.

import knex from 'knex'
import _ from 'lodash'

export const postgresql = (app) => {
  let config = app.get('postgresql')
  config = _.assign(config, {
    pool: {
      min: 0,
      max: 7
    }
  })
  const db = knex(config)

  app.set('postgresqlClient', db)
}

[nathanbrizzee-cdcr]

Thanks @MarcGodard for the pool information. I was looking for that. I also was looking for additional information on how to create the Database if it doesn't exist. For the V5 Dove release, I have some additional information that might be helpful for others. I struggled with how to get the Database created since that part is no longer provided out of the box. Here are the basic changes I added to get Knex to create the Database as part of the migrations. I hope something like this makes it into the Dove release.

config/default.json

 "postgresql": {
    "client": "pg",
    "connection": {
      "host" : "localhost",
      "port" : 5432,
      "user" : "postgres",
      "password" : "postgres",
      "database" : "pgtest"
    },
    "pool": {
      "min": 1,
      "max": 7
    }      
  }

src/schemas/configuration.js

export const configurationSchema = Type.Intersect([
  defaultAppConfiguration,
  Type.Object({
    host: Type.String(),
    port: Type.Number(),
    public: Type.String(),
    postgresql: Type.Object({
      client: Type.String(),
      connection: Type.Object({
        host: Type.String(),
        port: Type.Number(),
        user: Type.String(),
        password: Type.String(),
        database: Type.String()
      }),
      pool: Type.Object({
        min: Type.Number(),
        max: Type.Number()
      }),
    })
  })
])

./createdatabase.js (new file)

import config from './knexfile.js';
import knex from 'knex'

async function createDatabase() {
  const databaseName = config?.connection?.database || null;
  if (!databaseName) process.exit;

  config.connection.database = null;
  const _knex = knex(config)

  try {
    await _knex.raw('CREATE DATABASE ??', databaseName);
  } catch (e) {
    if (e && e.message && String(e.message).includes('already exists')) {
      console.log(`Database ${databaseName} already exists`)
    } else {
      console.error(e)
    }
  }
  await _knex.destroy();
}

createDatabase();

./package.json (added the postinstall and migrate:list scripts)

  "scripts": {
    "start": "node src",
    "dev": "nodemon src/",
    "prettier": "npx prettier \"**/*.js\" --write",
    "mocha": "cross-env NODE_ENV=test mocha test/ --recursive --exit",
    "test": "cross-env NODE_ENV=test npm run migrate && npm run mocha",
    "postinstall": "node createdatabase.js",
    "migrate:list": "knex migrate:list",
    "migrate": "knex migrate:latest",
    "migrate:make": "knex migrate:make"
  },

[daffl]

Don't the migrations already create the database? If not we could add it in the first migration since it should be run everywhere anyway.

[nathanbrizzee-cdcr]

Hi @daffl , The problem I ran into was the application would not connect to Postresql because the database name is part of the connection string and since the database doesn't exist, the connection fails. I did some digging to see how others handled it and found an article about running a postinstall script that connects to postgresql without the database name in the connection string. The code example they had was to read use the knexfile as is, but then remove the database name from the connection string and use that to connect to postgresql and create the database. Then when you run the migrations or the feathers app, the database will exist so that any connection created (that specifies the database name) going forward will be successful. There's probably a better/easier way to handle this. This was my first attempt that worked.

[mrobst]

src/schemas/configuration.js

export const configurationSchema = Type.Intersect([
  defaultAppConfiguration,
  Type.Object({
    host: Type.String(),
    port: Type.Number(),
    public: Type.String(),
    postgresql: Type.Object({
      client: Type.String(),
      connection: Type.Object({
        host: Type.String(),
        port: Type.Number(),
        user: Type.String(),
        password: Type.String(),
        database: Type.String()
      }),
      pool: Type.Object({
        min: Type.Number(),
        max: Type.Number()
      }),
    })
  })
])

This helped me - I created a fresh v5 install using the CLI and then wanted to copy parts of my configuration over from an existing v4 application config. I struggled for a while with the database connection (mssql) as the CLI sets up a connection string and when I tried to use a connection object like above it failed validation on the configuration schema which I didn't know how to fix. Additionally the connection string format of mssql://user:pass@server/database breaks if the password has special characters in (e.g. #) and I couldn't get round this so ended up changing the password in dev just to be able to carry on. I'll check more in the docs if I missed something in this area but just wanted to flag it up in context here.

[daffl]

I think we should add the object based connection and pool configuration to the default application configuration. I've seen this be an issue with MSSQL before.

[MarcGodard]

@daffl I agree, but there are other issues with doing so... I use a cert... I had to do the following to make it work...

in my config files:

  "postgresql": {
    "connection": {
      "connectionString": "DATABASE_URL"
    }
  },

And my full postgresql.js file:

import knex from 'knex'
import _ from 'lodash'
import fs from 'fs'

export const postgresql = (app) => {
  let config = app.get('postgresql')
  let databaseKey = app.get('postgresKey')
  config = _.assign(config, {
    pool: {
      min: 0,
      max: 5,
      afterCreate: (connection, callback) => {
        connection.query('SET timezone = "UTC";', err => {
          callback(err, connection)
        })
      }
    }
  })
  if (databaseKey !== 'DATABASE_KEY') {
    config = _.merge(config, {
      connection: {
        ssl: {
          rejectUnauthorized: false,
          ca: fs.readFileSync(databaseKey).toString()
        }
      }
    })
  }
  const db = knex(config)

  app.set('postgresqlClient', db)
}

Not sure if the set as UTC is needed, but ran into an issue with a DB provider that for some reason didn't default set it.