Let’s build a FullStack App with JavaScript! ~ Episode 4

This is a multipart series about building a Puppy Adoption Site with JavaScript. If you haven’t read all the articles please find them here: Episode 1| Episode 2 | Episode 3 | Episode 4 | Episode 5-Part 1 | Episode 5-Part 2 | Episode 6

Let’s Build a Database!

https://i.chzbgr.com/full/6791012096/hE7E77CAE/

After Episode 3, we’ve successfully planned our database schema. While this is nice we now need this database to exist.

Before we solve this problem I need you to download one required app from the web: SQLite Studio. For our app, we’ll be using an SQLite database and SQLite Studio will allow us to monitor and edit that database.

Once you have that download, go ahead and open our project and let’s dive right into some code!

Our Knex File

In episode 1, I mentioned that we’re going to use Knexjs as our ORM. ORMs deconstruct some of the complicated SQL syntaxes allowing us to build our APIs quicker.

One of the features of ORMs is that they can help us create, locate and define our databases. In our project, we’ll need to all that with knex but to begin let’s dive into our knexfile.js and define where our database connection is coming from.

When we open our knexfile.js we should see the development object that we tweak in one of the previous posts. Currently, that object should look like this:

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

If we dissect the syntax what it’s telling Knex is, when our code is in a development environment we want to be using sqlite3 as our database client. The server can connect to this database at ./dev.sqlite3. And we want any undefined values to be set to null(I explain this in more detail in Episode 1).

This object holds a lot of important information for our database and right now it’s not set up the way we’d like. We need to adjust where our database is connecting. We don’t want our database to mirror the demo one… but how do we change the connection if we don’t have a database?

I’ll tell you how that works our a bit later, but for now let’s, define our filename to be './data/doggo-land-db.sqlite3'. Once you adjust that, your development object should look like this:

development: {
  client: 'sqlite3',
  connection: {
    filename: './data/doggo-land-db.sqlite3'
  },
  useNullAsDefault: true
}

Nice! To recap, we’re telling our knex file to look in our data folder and find our databased named doggo-land-db.sqlite3 there. Uh oh… we don’t have a data folder. Go ahead and add one, then we can work on adding a database to it!

Let’s create our Database

Alright alright, the moment we’ve been waiting for! The cool thing about this is we can build our database two different ways:

Option 1: We use the SQLite Studio GUI and do it manually.

Option 2: We use knex migrations to do it for us.

Two options are always better than one, but for this demo let’s stick with knex.

To use knex we’ll be introducing migrations and seeding. Seeding isn’t as important at the start, but it’s what we use to add mock data to our databases. However, migrations are important right from the beginning because we use them to define our database schema.

I’m breezing over what migrations and seedings are because of how much content I’m covering in this post, but I encourage you to do some research on your own if you’re looking to learn more!

Let’s build some Tables

Right now we’re taking the approach that we’ll define all our database’s schema and then migrate that to a database, aka creating it. In order to do this, we’ll need to create some tables.

To do that, we’ll use a knex command called npx knex migrate:make table_name. This will end up generating a migration file for the specified table where inside we’ll define the specific elements of the table we’ll require.

For our app, we’ll need these tables: Kennels, Dogs, Breeds, Dogs_Breeds, Admins, Notifications. Let’s try making our kennels table by hopping in our terminal and running npx knex migrate:make kennels_table.

Holy smokes did you see that? You should’ve received a message back that reads, Created Migration:, followed by a bunch of info about the path to your migration. Take a look at your files now, there should be a brand new migrations folder.

Go ahead and open it, and you should find a file with a whole mess of numbers in front of it (that’s the date and time you created the file). Go ahead and open it up and you’ll see this:

exports.up = function(knex, Promise) {
};
exports.down = function(knex, Promise) {
};

These two exports can be looked at as transactions between us and the database. Inside our up export, we define database schema that we want to see changed when we migrate them to our database. As for our down export these are the changes we’d like to see if we rollback or undo our database.

To summarize we put schema to create or update our database in the exports.up transaction and to reverse or rollback we place that in our exports.down transaction.

Since we’re starting from scratch, we’ll need to create our table then define the columns inside of it. For our Kennels table, we wanted an Id, Name, Location, Bio, Email, Phone, and Image. Not only that but if we want to reverse our schema we should probably just delete our table completely. To create these tables we’ll use knex’s schema building syntax to define each column and create the table.

Like I’ve done in previous episodes, I’m going to show you the syntax first and then break it line by line down what each line means:

exports.up = function(knex, Promise) {
 return knex.schema.createTable('kennels', function(tbl){   
        tbl.increments('id').primary();    
        tbl.string("name",  255).notNullable()    
        tbl.string("bio", 500);
        tbl.string("location", 500);    
        tbl.string("email", 255);   
        tbl.string("phone", 255);  
        tbl.string("img_url", 255);
   })
};
exports.down = function(knex, Promise) {
  return knex.schema.dropTableIfExists('kennels');
};

***For those of you hoping for more than a url string to store an image, I’m sorry! We’ve got a lot to cover and the nuances of image hosting is a little tricky for this article. However, if you’re interested check out this article!***

• knex.schema.createTable('kennels', function(tbl) : This line allows us to create a new table in our database and provides us a callback function to then define the schema/columns within our table.
• tbl.increments('id').primary(): Here we define our id column. By using the .increments and .primary() methods we’re telling our database that every time we submit to this table the id should increment one from the most recent submission. This value should then act as our data entries primary key. This allows us to give every entry into our table a unique key that we can reference without any overlapping.
• tbl.string(“name”, 255).notNullable(): We’re defining our name column. We’re telling our database to expect a string that has a character limit of 255. In an SQLite database the character limit won’t be recognized but in case we migrate to Postgres or any other type of database for deployment it’s best to define that in now. Then adding the notNullable() ensures that when submitting our whole data object to this table it must include a name value. If not our database will throw an error preventing the whole object from being added.
• tbl.string(“value”, 255): Very similar to above, but it does not include notNullable. This means that it is not required when we send that information to the database.
• knex.schema.dropTableIfExists(‘kennels’): This last line tells our database that if we run a rollback we want you to remove our kennels table.

Awesome, we’re on a roll now! Next, we need to repeat this process for all the other tables we plan to implement. To save time I’ve provided the code for each migration and then all you need to do is npx knex migrate:make 'table-name' and add the code for each!

Table Files — Alphabetical Order: Admins, Breeds, Dog_Breeds, Dogs, Notifications

There you have it! If you have any questions about the methods we are chaining for our schema, the best way to learn them is to dig through the documentation. I suggest checking out the Knexjs docs or my personal favorite from devhints.io the Knex Cheatsheet.

Ok now that our migration files are all set and ready to roll, let’s migrate them and see our database come to life!

We can do this with one simple command: npx knex migrate:latest. Go ahead and try it and see what happens!

Where’s the action?? One might even say, it’s quiet…

Let’s get to the bottom of this! Go ahead and open up your data folder. See anything new? Our database has been created! There should be a file there named doggo-land-db.sqplite3. If you try and click on it, most IDEs will say the file is too big and can’t be seen in the editor. Thankfully, we have SQLite Studio to save the day.

Go ahead and open your SQLite Studio and follow along. I thought a brief video would be easier to visualize the process of navigating SQLite studio, so please take a few seconds and watch the video below:

How to open and connect your database on SQLiteStudio:

The Steps I took in the video above:

1. Open SQLiteStudio
2. Hit the Green add database button, can also be done via the Menu: Database → Add a database.
3. Clicked the manila folder icon to open our made database.
4. Navigated to our database and hit open.
5. **You can adjust what the display Name on your sidebar will be in the Name(on the list) input field.
6. Lastly, I hit the little power connector to connect to our database and then I begin browsing our tables!

Woohoo! Our database is alive and our tables are rendered. This is super cool but wouldn’t it be nice if we added some mock data in there? That way when we get rolling on the frontend we can have some data to pull from!

Time to get Seeding

We will use knex seeding to help seed our tables. This runs very similarly to knex migrations so it should be like déjà vu!

Making our seed files

Like our migrations, we need seed files. In order to do that we’ll use the knex command, npx knex seed:make file_name, to build our seed file.

Something to keep in mind when we make seed files is that when there’s multiple they will execute in alphabetical order, so to ensure that the files are executing in the correct order using the syntax, 001_file_name, 002_file_name... is best practice. The order is important because many of the tables we make include a foreign_key and if that is not defined/seeded before then an error will occur.

Looking at our data and the foreign_keys we have present, this is the order I’m going to seed:

1. 001_kennels
2. 002_dogs
3. 003_breeds
4. 004_dog_breeds
5. 005_admins
6. 006_notifications

Creating the files in this order will prevent any potential hiccups along the way.

Go ahead and create each file, and you should see them populate in your seeds folder. Look in your 001_kennels.js file and you should see this:

exports.seed = function(knex, Promise) {
  // Deletes ALL existing entries
  return knex('table_name').del()
  .then(function () {
  // Inserts seed entries
  return knex('table_name').insert([
     {id: 1, colName: 'rowValue1'},
     {id: 2, colName: 'rowValue2'},
     {id: 3, colName: 'rowValue3'}
   ]);
  });
};

A little busy but the comments do a good job describing what’s going on. What we’ll need to do to get this file ready for seeing is:

1. alter table_name to reflect our table’s name
2. replace .del() to .truncate(): every time we seed our table this will truncate the existing one so that our table restarts at an id of 1. Whereas .del() would remove the current table but it would not reset the increments. So if our previous table had 20 data points, after we reseed we would end up being the 21st data point, not the first as we normally intend.
3. Insert seed entries: Using a insert method we are posting whatever we define within the array to our defined table.

To do create our datapoint I’ll be using a library called fakerjs that auto-generates random data such as names, emails, and much more. Go ahead and add it to our build with the command yarn add faker.

At the top of our seed files we’ll need to import faker with this line of code:const faker = require(‘faker’);

Now that we had faker at our disposal, let’s go ahead and create some objects to seed our kennel table with. If we look at our migrations we’ll see that a kennel’s object would have an id, name, bio, location, email, phone number and image url. So inside of our insert array, we’ll make one object using faker syntax and then copy and paste it a few more times.

One great feature of our database schema is that we don’t need to define an id in the object we send it. This is taken care of by the increments primary key we set earlier. Therefore, when we send an object no id is necessary.

When we’re ready to create that object, it should look like this:

{
  "name": faker.name.findName(),
  "bio": faker.lorem.paragraph(),
  "location": faker.address.state() ,
  "email": faker.internet.email(),
  "phone": faker.phone.phoneNumberFormat(),
  "img_url": faker.image.city(),
}

We’re taking each of our required fields and generating some dummy content using the faker library. Super smooth, easy and fun! If you want to see all the faker data options check out this site!

Ok so we have our kennel object ready with some mock data so let’s add it to our code. Right now we have this code taking up our insert space:

{id: 1, colName: 'rowValue1'},
{id: 2, colName: 'rowValue2'},
{id: 3, colName: 'rowValue3'}

Go ahead and delete those and paste our new kennels object three times. When it’s completed your kennels seed file should look like this:

//Kennels seed file 
const faker = require('faker');
exports.seed = function(knex, Promise) {
  // Deletes ALL existing entries
  return knex('kennels').truncate()
    .then(function () {
      // Inserts seed entries
      return knex('kennels').insert([
        {
          "name": faker.name.findName(),
          "bio": faker.lorem.paragraph(),
          "location": faker.address.state() ,
          "email": faker.internet.email(),
          "phone": faker.phone.phoneNumberFormat(),
          "img_url": faker.image.city(),
        },
        {
          "name": faker.name.findName(),
          "bio": faker.lorem.paragraph(),
          "location": faker.address.state() ,
          "email": faker.internet.email(),
          "phone": faker.phone.phoneNumberFormat(),
          "img_url": faker.image.city(),
        },
        {
          "name": faker.name.findName(),
          "bio": faker.lorem.paragraph(),
          "location": faker.address.state() ,
          "email": faker.internet.email(),
          "phone": faker.phone.phoneNumberFormat(),
          "img_url": faker.image.city(),
        }
      ]);
    });
};

Let’s recap what we did to create this seed file and the data objects to be seeded:

• Use knex seed:make commands to create our files.
• Added fakerjs and imported it into our files.
• Replaced table_name with kennels, our tables name.
• Replaced .del() method with .truncate().
• Created mock objects using the faker library.

At this point in time we’re ready to run our kennels seed file, but unfortunately, it will not work. That’s because we have 5 other seed files that we have not formatted yet.

If we attempt to run our seeds now our systems will throw an error. To solve this we can do two things, delete all our other files or follow the steps above in each of our seed files.

I like option two because we’re going to need to do that anyways! I challenge you to go ahead and try formatting your seed files on your own, but if you’d like to save some time here’s the code I used to format my seeds:

Ok, we’re so close to being finished! All we need to do is to run our seed files so that our database is populated. Let’s hop in the terminal and run the command npx knex seed:run if successful you should see a message that reads ran 6 seed files.

To double check and view our new data, navigate to our SQLite Studio refresh your database and open up one of your tables. You’ll see a Data tab between theStructure and Constr4aints tabs. Click on that and look at all your data!

Here’s a screenshot of my kennels table data:

Wrap Up

We’ve done it, we’ve built out a database schema and seeded it with data! This is super cool, but now we need to learn how to manipulate and access that data!!

Unfortunately, that will need to come in my next blog post. I hope you enjoyed this episode and please reach out if you run into any errors or need any help along the way!