Using Knex.js for Migrations and Seeding
Published: 2021-12-10 23:26:34

Knex.js is an npm package that can help you perform database operations in NodeJS.  It provides many useful features and makes it extremely simple to run queries against your database.  It removes the need for writing SQL queries, allowing you to replace them with a simple, function-like approach.  For example, you can simply join two tables with a query such as: `knex(‘users’).where({email: ‘example@example.com})`.  There is functionality for almost all SQL operations including table joins, ordering and grouping.

Knex is also useful for performing database schema migrations.  A database schema migration is when you change the schema of the tables in your database.  You can use a migration to add a new column or field to a table, delete a column or create a table.  Today, we’ll focus on using migrations to set up your local database so you can run it easily.

The first step is to design your data schema so you know what tables you want to create in your database.  For our example, we will have a single table, called users, with an id column and a name field.

Create a folder in your source code called migrations, and create a file in it called `20211210191043_create_user.js`.  This file will be a migration to create our users table.  The filename consists of a timestamp of when the migration was created, followed by the name of the migration.  Knex will run the migrations in order of the timestamps given by the filenames.  This is important for tables with relations, such as foreign keys, between them.  The migration file contents look as follows: 


![Knex Migration](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/v7i76oujak666tfmscz2.png)

Every migration contains two functions, up and down.  Up is the function that runs to create the changes that you want to make in your database.  In our example, we want to create a table in the database, so we call the create table function.  The knex [schema builder](https://knexjs.org/#Schema) contains many functions for different operations that you may want to perform on your database including functions like renameColumn, renameTable and alter.

The down function should do the opposite of whatever the up function did.  If the up function adds a column, the down function should remove it.  In our case the up function creates a table, so the down function should drop it.  This gives you the ability to quickly undo whatever migration you create so that you can easily rollback if your migration causes unforeseen issues.

Once you have created a migration for each of the tables in your schema, you can also use knex in order to seed the tables with data.  This is very useful if you want to start the database with some data to make local testing easier. If you have created an API that serves data from your database, you can begin using it directly without worrying about getting data into your database to use first.

In order to seed the database, first create a folder for the seeds files and one for your seed data.  Create a csv of seed data that you wish to insert into your table.  For my user table, the id field increments automatically, so there is a single column with content to insert, the name field.  So a csv of seed data could look like below.


![Seed Data](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/yyat0jb5g5hniva35dv8.png)

In your seed folder, create a single function, seed.  For our purpose, we want to delete the existing contents of the table before adding in the new data, so we first use the del function to delete all entries.  Then we parse the csv file and insert it directly into the database. As long as your csv data follows the format of the table you are inserting it into, seeding different tables follows an almost identical process and is very quick to set up.


![Seeding the Database](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ia408spc56rp5h1y510p.png)
 
Now that we have the migrations and the seed files created, how do we set up our database to use them?  One of the simplest ways is to simply run the migrations and seeding on app start.  Migrations can be run on app start both locally and on production.  When the migrations run, a table in the database called knex_migrations is created; an example is shown below.  This table stores all the migrations that have been run so far.  This ensures that no migration is run twice.  If you create a migration and run it, then decide to change the schema later, best practice is to create a new migration to edit the table.  If you edit the original migration, it will not run unless you delete the associated row in the knex_migrations table.  However, this is particularly error prone and not recommended.  It is especially difficult to do this if you are deploying an app that is currently running in production.


![knex_migrations table](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ahrfrg1e3sgtttmhxg1p.png)

If you have a server, it can be simple to run the migrations on app start, as shown below: 


![Running migrations and seeding on app start](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/v2umxm283tvzp2da1h2k.png)

We first wait for the db operations to complete, and then we serve our app.  Now we have a local db with all tables set up and containing data, ready to be used for local testing!  Once this is set up, there is no additional work to recreate the tables and data when running on a different computer with a new database.  Upon app start, the tables will be created and seeded if they haven’t already, leading to a greatly decreased amount of work to do before you can get to productive development.