This can be all done with knex library, a SQL query builder for JavaScript. In this post I’ll use MySQL (MariaDB) database, but knex also supports Postgres, MSSQL, SQLite3, and Oracle. I will also use faker for generating fake data for seeding the database.

Install knex, faker and mysql (or any other driver for database which knex supports).

npm install knex faker mysql

Create a new database in your database client (I use adminer for viewing and managing database content).

Initialize knex (which creates configuration file) and update development object accordingly to database driver you use.

$ node_modules/.bin/knex init
Created ./knexfile.js

// Update with your config settings.

module.exports = {
  development: {
    client: 'mysql',
    connection: {
      database: 'knexseed',
      user:     'root',
      password: ''
    },
    pool: {
      min: 2,
      max: 10
    },
    migrations: {
      tableName: 'knex_migrations'
    }
  },
...
}

Create a new migration, open newly created file and replace its content with the following (I create table called users with id (with auto increment property), username, email, created_at, updated_at columns – last two are created with timestamps method).

$ node_modules/.bin/knex migrate:make users
Using environment: development
Created Migration: /home/jernej/Code/knexseed/migrations/20160904154141_users.js

exports.up = function(knex, Promise) {
    return knex.schema.createTable('users', table => {
        table.increments('id')
        table.string('username')
        table.string('email')
        table.timestamps()
    });
};

exports.down = function(knex, Promise) {
    return knex.schema.dropTable('users')
};

Run migrations.

$ node_modules/.bin/knex migrate:latest
Using environment: development
Batch 1 run: 1 migrations 
/home/jernej/Code/knexseed/migrations/20160904154141_users.js

Create new seed file and replace its content with the following (here I create a loop which generates record with random data for users table)

$ node_modules/.bin/knex seed:make users
Using environment: development
Created seed file: /home/jernej/Code/knexseed/seeds/users.js

var faker = require('faker');

let createRecord = (knex, id) => {
  return knex('users').insert({
    id,
    username: faker.internet.userName(),
    email: faker.internet.exampleEmail(),
    created_at: new Date(),
    updated_at: new Date()
  })
}

exports.seed = (knex, Promise) => {
  return knex('users').del()
    .then(() => {
      let records = [];

      for (let i = 1; i < 10; i++) {
        records.push(createRecord(knex, i))
      }

      return Promise.all(records);
    });
};

Run seeds.

$ node_modules/.bin/knex seed:run
Using environment: development
Ran 1 seed files 
/home/jernej/Code/knexseed/seeds/users.js

Pro tip: If you prefer using ORM for quering the database, use Bookshelf.js.

Bookshelf is a JavaScript ORM for Node.js, built on the Knex SQL query builder. Featuring both promise based and traditional callback interfaces, providing transaction support, eager/nested-eager relation loading, polymorphic associations, and support for one-to-one, one-to-many, and many-to-many relations.