Create and initialize your a directory for your Express application.
$ mkdir node-knex-demo
$ cd node-knex-demo
$ npm initKnex 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 globally on your local computer.
$ npm install knex -gThis 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 --saveFor 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.jsAt 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);$ 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
Does knex and express need to be installed on the server side or on the client side or both? Because my MySQL db is hosted using an online web service not sure if these can be installed on the server side.