How to ensure the Knex connection in nodejs

Question

I am using NodeJS, Express, and MySQL for my project and want to use Bookshelf ORM with it.

Bookshelf uses Knex for querying, modeling, and suggests to setup the DB connection through Knex(http://bookshelfjs.org/#installation).

I am having trouble in establishing a successful DB connection with Knex. I want to start the server only if DB connection is successful, but it seems like it doesn't offer anything after establishing the connection to do so(no promise or property).

This is the code I have been using.

import _knex from "knex"; // npm install knex --save
import _bookshelf from "bookshelf"; // npm install bookshelf --save

let knex = _knex({
    client: "mysql",
    connection: {
        host: "127.0.0.1",
        database: process.env.DB,
        user: process.env.DB_USERNAME,
        password: process.env.DB_PASSWORD
    },
    debug: true
});

let bookshelf = _bookshelf(knex);

module.exports.knex = knex;
module.exports.bookshelf = bookshelf;

More reference: There is another ORM named Sequelize and it provides sequelize.authenticate() which return Promise and could be used as (http://docs.sequelizejs.com/en/latest/api/sequelize/#authenticate-promise)

sequelize.authenticate()
    .then( () => {
        console.log("Db successfully connected");
        app.listen(port, () => console.log(`App started at: ${port}`) );
    })
    .catch( err => console.log('Unable to connect to the database') );

The only solution I can think of is to perform a raw query like USE {DB_NAME} and use its output to decide whether to start the server or not. Is this solution good enough?

score 9 · Accepted Answer · answered Feb 03 '17 at 07:34

9

This was discussed earlier this week in knex issue tracker. https://github.com/tgriesser/knex/issues/1886

Making query is a good way to check that connection can be made to database. If no queries are made, pool doesn't necessary create any initial connections (depends on pool settings).

You may also also wire up pool's afterCreate callback to notify you when ever there is new connection made to database (https://github.com/knex/documentation/pull/17/files).

answered Feb 03 '17 at 07:34

Mikael Lepistö

18,909
3
68
70

How you should handle this case when the database close the connection? Do we need to manually destroy the pool? – probitaille Feb 06 '19 at 22:19
1

No. You dont have to do anything. Knex recognizes that connection was closed, discards it from the connection pool and next time when you do new query knex will try to create fresh connection which is added to the pool. – Mikael Lepistö Feb 08 '19 at 09:58

score 5 · Answer 2 · answered Aug 24 '21 at 12:36

Code explains itself

const config = require("../../config");

const pgsql = require('knex')({
    client: 'pg',
    connection: {
        host: config.PGSQL_HOST,
        port: config.PGSQL_PORT,
        user: config.PGSQL_USER,
        password: config.PGSQL_PWD,
        database : config.PGSQL_DB
    },
    useNullAsDefault: true
});

pgsql.raw("SELECT 1").then(() => {
    console.log("PostgreSQL connected");
})
.catch((e) => {
    console.log("PostgreSQL not connected");
    console.error(e);
});

module.exports = pgsql;

score 3 · Answer 3 · answered Jul 22 '20 at 00:37

Perhaps this can give some hint to your question.

I was recently debugging a bad knex connection to a postgresql database and found that (using your knex variable)

knex.client.connectionSettings

contains an object with the connection, er, settings. It was not set if the connection was not made. I don't know exactly when it's set so i don't know if it's an indication of intent or success. I was single stepping. Also, this isn't the same client you're using so ymmv. But I'd look for the info you want down in the client, not knex.

How to ensure the Knex connection in nodejs

3 Answers3