Database seeding with Knex

Why and how to seed a database in a REST API application

In previous articles in this series we have used Knex migrations to create a database and to add tables and columns to it. We have also seen how to modify tables, add a column, and rollback migrations.

Migrations operate on the database schema itself, but what about the actual data?

Knex can be of help in pre-populating the database programmatically with actual data, so we don't have to do it manually every time we set up a new database. This operation is called seeding the database.

Why seed data?

There are a couple of instances while developing our application, where we may want to work with live data.

One instance is during the testing phase.
Testing is a huge subject and we won't go into much detail here, but typically tests need to run with some set of pre-defined data to make sure the application logic is correct.

Another instance is for configuring the application in development.
We almost always need sample data while building and deploying the application to make sure our database is configured properly.

Seeding

Files that contain data for pre-populating our database are called seed files.
Knex needs to know where seed files live, so we need to first configure knexfile.js by adding a new property called seeds that specifies the seed directory:

// knexfile.js

development: {

  seeds: {
    directory: './data/seeds'
  }

}

Similar to when we created migration files, Knex gives us a tool to create seed files on the command line. We use this command:

knex seed:make

followed by the seed file name.

If you remember, Knex automatically adds a timestamp in front of migration files, to keep them in the proper order. However, it won't add any timestamps to seed files.

Generally speaking, we should also keep seed files in order, because we may have seed files that rely on previous seeds for their data. For this reason, it's up to us to add an order to the file names.

The easiest way of doing this is to add a progressive number at the beginning of the seed files. So, our first seed file will be called 01-fruits and will be created with this command:

knex seed:make 01-fruits

Additional seed files will begin with 02-, 03-, etc.

An example of a seed file

Upon running the command, Knex will create a file and pre-populate it with some boilerplate code to get us started:

exports.seed = function(knex) {
  // Deletes ALL existing entries
  return knex('table_name').del()
    .then(function () {
      // Inserts seed entries
      return knex('table_name').insert([
        {id: 1, colName: 'rowValue1'},
        {id: 2, colName: 'rowValue2'},
        {id: 3, colName: 'rowValue3'}
      ]);
    });
};

In a seed file we don't need up and down methods like in a migration, because seeds are just meant to add data to the table, not remove it.

Changes to the template

The templated code generated by Knex provides some placeholders that we need to replace before running the seed.

The first thing we need to replace is table_name with the actual name of the table we want to operate on, in our case fruits.

The second thing we need to change is the method that deletes data from the table. The Knex template uses the del() method, but in general it's better to use the truncate() method instead.
While del() will remove all the existing records, truncate() will also reset the primary key, so the table will be brought back to its pristine state.

Finally, we need to add objects that represent data in the correct format for our specific table.

Having made these changes, this is the code we will use:

exports.seed = function(knex) {
  // Deletes ALL existing entries
  return knex('fruits').truncate()
    .then(function () {
      // Inserts seed entries
      return knex('fruits').insert([
        {id: 1, name: 'apple', weight: 20, delicious: true, color: 'red'},
        {id: 2, name: 'pinapple', weight: 40, delicious: true, color: 'yellow'},
        {id: 3, name: 'grape', weight: 4, delicious: false, color: 'purple'},
      ]);
    });
};

We run the seed file with this command:

knex seed:run

After running the command, if we look at the database we'll see that the fruits column has now been populated with the data we entered.

I write daily about web development. If you like this article, feel free to share it with your friends and colleagues.

You can receive articles like this in your inbox by subscribing to my newsletter.