DEV Community

Bryan Oyloe
Bryan Oyloe

Posted on

Creating an Express server with Node.js Part 3: Setting up Relationships Between Tables

So far in the series we have set up our Express app and built a table to store our 'owners' in our database with Knex. If you have been following along, our app now has a table of 3 pet owners, each has a name and a unique id. In this article I am going to show you how to create a table that will contain pets that belong to the owners. Each pet will have a name, age, and a species attribute assigned to them. We will also be giving each pet a unique id. This will all be familiar as it is exactly what we did with our owners. The difference is going to be in how we relate each pet to a specific owner in our database.

First Steps

If you are just starting this series from here, you will need to go back to part 1 and work through the previous installments to get your project up to speed. Open up your terminal and navigate into your project directory, then open your project in your text editor. We currently have a few owners but they are lonely, so we need to give them some pets.

Creating our Pets

Like we did in the previous post with our owners table, we are going to create a migration for our pets table and then seed some data for it. First we need to create the migration. In the terminal type

npx knex migrate:make create-pets-table
Enter fullscreen mode Exit fullscreen mode

Navigate into our newly created migration file. We want to define our exports.up function to create the pets table. For this exercise, we want each of our pets to have a string for their name and for their species. We also want to give our pets an age attribute that will be an integer. Also, like we did with our owners table, we want Knex to give each pet a unique id. Copy this code into your new pets migration

exports.up = function(knex) {
    return knex.schema.createTable('pets', table => {
        table.increments('id');
        table.string('name');
        table.string('species');
        table.integer('age');


    })
};

exports.down = function(knex) {
    return knex.schema.dropTableIfExists('pets');
};
Enter fullscreen mode Exit fullscreen mode

We also define our exports.down function in case we need to rollback our migration. Remember, if you don't define both up and down, Knex will throw an error message.

This looks very similar to the work we have already done. This is good but it doesn't give us a way of assigning each pet we create to a specific owner. To do that we need to create a reference on every pet. This is done like so

exports.up = function(knex) {
    return knex.schema.createTable('pets', table => {
        table.increments('id');
        table.string('name');
        table.string('species');
        table.integer('age');        
        table.integer('owner_id')
          .references('id')
          .inTable('owners')

    })
};

exports.down = function(knex) {
    return knex.schema.dropTableIfExists('pets');
};
Enter fullscreen mode Exit fullscreen mode

Looking at the new line in our exports.up function, this actually reads pretty easily. Knex is going to create an integer column for our pets table called 'owner_id'. This column will be a reference to the 'id' column in the 'owners' table. Every time we create a new pet, we will now also have to specify which owner it belongs to. This is how we will map our relationships. Go ahead and run your new migration.

npx knex migrate:latest
Enter fullscreen mode Exit fullscreen mode

Seeding our pets

We are going to be using the same seed file that we created for our owners but making some changes to it so that we can create our pets too. When creating tables that contain references, we have to remember to always make sure that the table we are referencing actually exists or we are going to get errors. In other words, we can't create a pets table if the owners table doesn't exist or more specifically, we can't give a pet to an owner that doesn't exist. So we will always have to make sure that our owners get created before the pets. Our new seed file will look like this

exports.seed = async function(knex) {
  await knex('pets').del()
  await knex('owners').del()

  const bryan = await knex('owners').insert([
    {
      name: 'Bryan'
    }
  ])
  const shelly = await knex('owners').insert([
    {
      name: 'Shelly'
    }
  ])
  const catalina = await knex('owners').insert([
    {
      name: 'Catalina'
    }
  ])

  await knex('pets').insert([
    {
      name: 'Fluffy',
      species: 'Dog',
      age: 4,
      owner_id: bryan
    },
    {
      name: 'Spot',
      species: 'Dog',
      age: 12,
      owner_id: bryan
    },
    {
      name: 'Mittens',
      species: 'Cat',
      age: 2,
      owner_id: shelly
    },
    {
      name: 'Bandit',
      species: 'Cat',
      age: 4,
      owner_id: catalina
    },
    {
      name: 'Rufus',
      species: 'Dog',
      age: 8,
      owner_id: catalina
    },

  ])
}
Enter fullscreen mode Exit fullscreen mode

Just like before we will be using async/await to control the flow of our program. Remember we don't want to create any pets until we have created owners for them. Async/await gives us that control. Notice that we set each of the owners to a variable so we can reference them when creating our pets. If we were to statically enter the current id for the owner (i.e. typing 1 instead of bryan) it will work the first time we seed. However, if we ever go back and re-seed, the id for "Bryan" will be re-incremented by Knex and we will get an error since owner_id: 1 will no longer exist. Feel free to change the names and species to whatever you want, it will not affect the program as long as you make them strings. Let's run our seed file.

npx knex seed:run
Enter fullscreen mode Exit fullscreen mode

Now we can check out the database to make sure that we have created all of our new pets.

sqlite3 dev.sqlite3
Enter fullscreen mode Exit fullscreen mode
sqlite> .headers on
sqlite> .mode column
sqlite> SELECT * FROM pets;
id          name        species     age         owner_id  
----------  ----------  ----------  ----------  ----------
11          Fluffy      Dog         4           16        
12          Spot        Dog         12          16        
13          Mittens     Cat         2           17        
14          Bandit      Cat         4           18        
15          Rufus       Dog         8           18 
Enter fullscreen mode Exit fullscreen mode

As you can see, we now have 3 new dogs and 2 new cats. Each pet is tied to a specific owner through the owner_id column. Your id and owner_id numbers may be different depending how many times you run your seed file. Remember, every time the seed file runs, it deletes all previous entries and re-inserts them with a newly incremented id.

Conclusion

Congrats, you now have a database containing tables for your pets, and for their owners. In the final part of the series we are going to use the Objection package to bring it all together so that we can access our data easily in a format that we can use in building out a website.

Top comments (0)