Creating a Backend Server using Node Part 2, Adding Knex

In the last part of this series I walked through how to get a Node Express server running and have it return a text response to an HTTP GET request. In this article, I will show you how to add Knex to your project so that you can create migrations to your database and create seed data.

First Steps

Starting with the "Hello World" project that we set up before in part 1 we want to add a database to our project as well as the Knex package. We are going to be using sqlite3 as our database because it is lightweight and easy to work with for development. In your terminal type

npm i sqlite3 knex 

Now that we have installed our database and knex package we can get to work. First we need to create our knex file. Run this command to generate knexfile.js:

npx knex init

This will create a new JavaScript file that looks like this

// Update with your config settings.

module.exports = {

  development: {
    client: 'sqlite3',
    connection: {
      filename: './dev.sqlite3'
    }
  },

  staging: {
    client: 'postgresql',
    connection: {
      database: 'my_db',
      user:     'username',
      password: 'password'
    },
    pool: {
      min: 2,
      max: 10
    },
    migrations: {
      tableName: 'knex_migrations'
    }
  },

  production: {
    client: 'postgresql',
    connection: {
      database: 'my_db',
      user:     'username',
      password: 'password'
    },
    pool: {
      min: 2,
      max: 10
    },
    migrations: {
      tableName: 'knex_migrations'
    }
  }

};

We can clean this up a little since we won't be using everything in this file. Delete everything except the development object so that your file looks like this

// Update with your config settings.

module.exports = {

  development: {
    client: 'sqlite3',
    connection: {
      filename: './dev.sqlite3'
    }
  },
}

Migrating

Now that we have taken care of that, it is time to start talking to our database. Go ahead and run the following command

npx knex migrate:make create-owners-table

We are going to make a table of owners where each owner will eventually be able to have pets. Now Knex has created a migration folder with a .js file for our new migration that looks like this

exports.up = function(knex) {

};

exports.down = function(knex) {

};

Knex wants us to define the exports up and down functions. The up function controls what happens when we run our migration and the down function controls what happens when we want to roll back the migration. We must define both or we will get an error. We want to give our owners a name attribute and a unique id. Knex gives us an easy method to assign an id. Add this code to your migration file

exports.up = function(knex) {
    return knex.schema.createTable('owners', table => {
        table.increments('id')
        table.string('name')
    }
  )
};

exports.down = function(knex) {
    return knex.schema.dropTableIfExists('owners')
};

In our exports.up we are asking knex to create a table named 'owners'. The table.increments('id') is creating a column on that table called id, and Knex will automatically increment it by one every time a new owner is created so we always have a unique identifier for each owner, even if two owners have the same name. Then Knex is creating another column for the owners name as a string. Finally, in the exports.down we are telling Knex to delete the 'owners' table if it exists. This will be run if we need to rollback our migration.

Now we are ready to run our migrations. In your terminal type

npx knex migrate:latest

Time to make some seed data for our new migration!

Seeds

Our database is no good unless it has some data in it. So let's make some owners to go in our database.

npx knex seed:make 01-create-owners

This command will create a seed folder with a new .js file for us to create seeds. The "01" part of the name is just for tracking purposes if you later on want to create more seed files. In this new file there is some boiler plate code to get started. We are going to throw most of it away and turn this into an async/await function. If you want to learn more about how async/await works there is plenty of documentation available but for this example it is not necessary to understand how it works. Enter the following into your new 01-create-owners.js file

exports.seed = async function(knex) {
  await knex('owners').del()
  await knex('owners').insert([
    {
      name: 'Bryan'
    },
    {
      name: 'Shelly',
    },
    {
      name: 'Catalina'
    }

  ])
}

When our seed file runs, this will first delete all current entries in our owners database and will then insert our seeded owners. You can change the names to whatever strings you want. You don't have specify the 'id' column because Knex will automatically populate that with a unique integer. Our seeds are ready to go. Go ahead and run

npx knex seed:run

Now you have a database that contains a table with your new owners names and their respective id's. If you want to check that our owners now exist you can open the sqlite3 shell

sqlite3 dev.sqlite3

This will access our new database dev.sqlite3 and allow us to search it with SQL commands.

SELECT * FROM owners;

This command will select everything from the owners table and print it to your terminal like so

1|Bryan
2|Shelly
3|Catalina

Voila, we have our 3 owners!

In the next installment of this series, I will walk you through making a pets table and connecting it with our owners table so that we can look at an owner to see what pets they have and look at a pet to see who is their owner. Stay Tuned!