Creating a Backend Server using Node Part 2, Adding Knex
Published: 2020-11-09 15:28:31

In the [last part](https://dev.to/boyloe/creating-a-express-server-using-node-part-1-hello-world-b2j) 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](https://dev.to/boyloe/creating-a-express-server-using-node-part-1-hello-world-b2j) 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
```javascript
// 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
```javascript
// 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 
```javascript

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 
```javascript
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
```javascript

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. 
```sql
SELECT * FROM owners;
```
This command will select everything from the owners table and print it to your terminal like so
```sql
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!