Setup Knex with Node.js

Knex-Setup.md

Knex Setup Guide

Create your project directory

Create and initialize your a directory for your Express application.

$ mkdir node-knex-demo
$ cd node-knex-demo
$ npm init

Knex

Knex is a SQL query builder, mainly used for Node.js applications with built in model schema creation, table migrations, connection pooling and seeding.

Install Knex and Knex Command Line Tool

Install knex globally on your local computer.

$ npm install knex -g

This will allow us to use knex as a command line tool that helps you create and manage your knex files.

In addition, you will need to also install the knex module locally to use in your project.

$ npm install knex --save

Configuring your database

For our example, we're going to be connecting to a PostgreSQL database, we'll need to install the pg module.

$ npm install pg --save

We can start by creating a knexfile.js in the root of your project which will act as our configuration for different environments, (e.g. – local development vs production).

$ knex init

This will create a knexfile.js with the different configurations for the different environments.

Generated output knexfile.js.

module.exports = {
  development: {
    client: 'sqlite3',
    connection: {
      filename: './dev.sqlite3'
    }
  },
  staging: {
    client: 'postgresql',
    connection: {
      database: 'my_db',
      user:     'username',
      password: 'password'
    },
    pool: {
      min: 2,
      max: 10
    },
    migrations: {
      tableName: 'knex_migrations'
    }
  },
  production: {
    client: 'postgresql',
    connection: {
      database: 'my_db',
      user:     'username',
      password: 'password'
    },
    pool: {
      min: 2,
      max: 10
    },
    migrations: {
      tableName: 'knex_migrations'
    }
  }
};

Edit your development settings in knexfile.js to point to your postgres database, using your db username and password. DON'T FORGET TO CREATE YOUR DATABASE LOCALLY!

Example development config object

{
  development: {
    client: 'pg',
    connection: {
      host : '127.0.0.1',
      user : '[db_username]',
      password : '[db_password]',
      database : '[db_name]',
      charset: 'utf8'
    },
    migrations: {
      directory: __dirname + '/knex/migrations',
    },
    seeds: {
      directory: __dirname + '/knex/seeds'
    }
  }
}

We want to create a knex directory at the root of our project to hold our migrations and seeds scripts. Inside of the knex directory, we need a knex.js file to hold the single instance of the knex module with the correct environment config.

$ mkdir knex
$ mkdir knex/migrations
$ mkdir knex/seeds
$ touch knex/knex.js

At this point, our project structure should look like this:

.
├── knex
│   └── migrations
│   └── seeds
│   └── knex.js
└── knexfile.js
└── package.json

For more information on migrations and seeds with knex, checkout the knex migrations and seeds guide.

Example knex.js

const environment = process.env.ENVIRONMENT || 'development'
const config = require('../knexfile.js')[environment];
module.exports = require('knex')(config);

Create your Express application

$ npm install express --save

Now let's create a server.js file in the root of your project. Create your express application how you normally would, for this example the server listening on port 3001. Let's also create a super basic GET endpoint to query our db.

const express = require('express');
const PORT = process.env.PORT || 3001;
const knex = require('./knex/knex.js');
const app = express();

app.get('/tasks', (req, res) => {
  // use the knex variable above to create dynamic queries
});

app.listen(PORT, () => {
  console.log(`Listening on port: ${PORT}`);
});

Start the server:

$ node server.js

See if this solves your problem.

knex service

const options = {
  client: 'xxx',
  connection: {
    host : 'xxx',
    user : 'xxx',
    password : '',
    database : 'xxx'
  }
};

const knex = require('knex')(options);
module.exports = knex;

Then in your do it like this

const knex = require('./service');

const myFunction = () => {
  const tableName = 'xxxx';
  const query = knex(tableName);

  const myquery = await query.where....
};

knex has some config to override the migrate file default?

from

exports.up = async function(knex) {

};

exports.down = async function(knex) {

};

to

exports.up = async (knex) => {
  
};

exports.down = async (knex) => {

};

If you want to keep sensitive information out of your repo, you could instead set up the following environment variables: DB_PORT, DB_NAME, DB_HOST, DB_USER, DB_PASSWORD, DB_FILE, and DB_ENGINE. When you leave these environment variables unset, it uses the defaults which will connect to your local database. This way only local database connection info is in your repo which isn't really sensitive.

When you set the environment variables, it could connect to anything else you want which leaves it much more flexible. But it does create some work on the devops side. Here I show selecting based on preferred engine rather than based on environment.

// knexfile.js
module.exports = {
  sqlite: {
    client: 'sqlite3',
    connection: {
      filename: process.env.DB_FILE   || './dev.sqlite3'
    }
  },
  mysql: {
    client: 'mysql',
    connection: {
      host:     process.env.DB_HOST     || '127.0.0.1',
      port:     process.env.DB_PORT     || 3306,
      database: process.env.DB_NAME     || 'local_my_db',
      user:     process.env.DB_USER     || 'local_username',
      password: process.env.DB_PASSWORD || 'local_password'
    },
    pool: {
      min: 2,
      max: 10
    },
    migrations: {
      tableName: 'knex_migrations'
    }
  },
  postgresql: {
    client: 'pg',
    connection: {
      host:     process.env.DB_HOST     || '127.0.0.1',
      port:     process.env.DB_PORT     || 3306,
      database: process.env.DB_NAME     || 'local_my_db',
      user:     process.env.DB_USER     || 'local_username',
      password: process.env.DB_PASSWORD || 'local_password'
    },
    pool: {
      min: 2,
      max: 10
    },
    migrations: {
      tableName: 'knex_migrations'
    }
  }
};

And then the knex.js file:

// knex.js

// Valid DB_ENGINE values based on knexfile.js are 'sqlite', 'mysql', or 'postgresql'
const engine = process.env.DB_ENGINE || 'sqlite'
const config = require('../knexfile.js')[engine];
module.exports = require('knex')(config);

knex has some config to override the migrate file default?

yes
just create a file with your new content, for example migrate.stub

// new initial cotent of the migrations \o/

exports.up = async (knex) => {
  await knex.schema
  ...
};

exports.down = async (knex) => {
  await knex.schema
  ...
};

and pass it on migrate:make command

"migrate:make": "env-cmd .env knex migrate:make --stub ./migration.stub"

Is it possible to set global table.engine('InnoDB') for mysql?

exports.up = knex => {
  return knex.schema
    .createTable('roles', table => {
      table.engine('innoDB') // <-- I want to set it globally and not on every table
      table.increments('id')
      table.string('name').notNullable().unique()
    })
}

exports.down = knex => {
  return knex.schema.dropTable('roles')
}

Is there an updated version that would work with Modern ES Modules? I am trying to convert over from module.exports/require() to import/export syntax but am now seeing issues with knex configurations, not sure how to resolve this. Mainly this line I can't see a way past: const knex = require('knex')(connections[envName]);. Is there a solution for this?

@Epicnessism I fell you. I cannot configure it in ES5/6 as well.