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.
Top comments (2)
Hi Cesare! Thanks for the very well written article, so helpful!
I have two questions I'd appreciate your take on:
Why are you returning the result of the seed? Don't these functions return void?
Silly question... You say, "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." If this is the case, why do we need to truncate() the table? Is that just to make sure that we don't have duplicate data? I assume seed files are not run automatically on application build, then?
Thanks again!
Hi! I doubt this answer is still useful for you, but regarding section 2 of your question: yes, the truncate is for the table to lose all its data before making the seed, so you only have the data you created with that seed. Normally you don't use seeds on production, so truncating it is often the way for testing purposes.