Tuesday, 5 May 2015

knex seed using nodejs example

Creating Seed for using Migrations in Knex

Migrations allow for you to define sets of schema changes so upgrading a database is a breeze.

Migration CLI

The migration CLI is bundled with the knex install, and is driven by the node-liftoffmodule. To install globally, run:
$ npm install knex -g
The 0.6 migrations use a knexfile, which specify various configuration settings for the module.
To create a new knexfile, run the following:
$ knex init

will create a sample knexfile.js - the file which contains our various database configurations. Once you have a knexfile.js, you can use the migration tool to create migration files to the specified directory (default migrations). Creating new migration files can be achieved by running:
$ knex migrate:make your_migration_name
Once you have the migrations in place you wish to run, you can run:
$ knex migrate:latest
To update your database to the latest version.

Seed files

Seed files allow you to populate your database with test or seed data independent of your migration files.

Seed CLI

To create a seed file, run:
$ knex seed:make your_seed_name
Seed files are created in the directory specified in your knexfile.js for the current environment.
A sample seed configuration looks like:
development: {
  client: ...,
  connection: { ... },
  seeds: {
      directory: './seeds/dev'
If no seeds.directory is defined, files are created in ./seeds.
Note that the seed directory needs to be a relative path. Absolute paths are not supported (nor is it good practice).
Your seed file will contain the code like
'use strict';
exports.seed = function(knex, Promise) {
    return Promise.join(
        // Deletes ALL existing entries

        // Inserts seed entries
        knex('users').insert({user_name: 'testUserBySeed', passkey: 'passkey'})
To run seed files, execute:
$ knex seed:run
Seed files are executed in alphabetical order. Unlike migrations, every seed file will be executed when you run the command. You should design your seed files to reset tables as needed before inserting data.


A knexfile.js or knexfile.coffee generally contains all of the configuration for your database. It can optionally provide different configuration for different environments. You may pass a --knexfile option to any of the command line statements to specify an alternate path to your knexfile.

Basic configuration:

module.exports = {
  client: 'pg',
  connection: process.env.DATABASE_URL || {
    username: 'me',
    database: 'my_app'

Environment configuration:

module.exports = {
  development: {
    client: 'pg',
    connection: {
      username: 'me',
      database: 'my_app'
  production: {
    client: 'pg',
    connection: process.env.DATABASE_URL

Migration API

knex.migrate is the class utilized by the knex migrations cli. Each method takes an optional config object, which may specify specifies the database, directory,extension, and tableName for the migrations.
makeknex.migrate.make(name, [config]) 
Creates a new migration, with the name of the migration being added.
Runs all migrations that have not yet been run.
Rolls back the latest migration group.
Retrieves and returns the current migration version, as a promise. If there aren't any migrations run yet, returns "none" as the value for the currentVersion.

Seed API

knex.seed is the class utilized by the knex seed CLI. Each method takes an optionalconfig object, which may specify the relative directory for the migrations.
makeknex.seed.make(name, [config]) 
Creates a new seed file, with the name of the seed file being added.
Runs all seed files for the current environment.

Source code available at https://github.com/jayesh36/RESTFull-API-with-express-knex-promises-with-testcase-in-mocha