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
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');
};
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');
};
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
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
},
])
}
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
Now we can check out the database to make sure that we have created all of our new pets.
sqlite3 dev.sqlite3
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
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)