Creating a Database — Part 2 of 3: Database tables and seeding

In the first part of this post, we went over the file hierarchy composition of the ‘Creating a Database’ project, describing how each file contributes for the backend development and their configuration importance.

→ Project Composition:

• 1st part;
• 2nd part (this post);
• 3rd part.

Now in part 2 we will be actually making use of what we have already created in part 1 to migrate, build and seed the database. If you didn’t have a chance to read part 1, you can find it here.

Let’s check again how our project structure currently looks like, after going through the steps in part 1 of creating each file and their configuration, organizing them into directories representing a file hierarchy tree:

We also saw the one-to-many table association that we have for the project:

• Customers → Has many orders;
• Order → Belongs to a customer.

Now that we have our files set up a visualization of our database, let’s create it! We will be using Ruby OO language to perform interactions, SQLite database engine, methods and tasks from very handy gems like Active Record and Rake — if you are curious about them, you can check the first part of this post at ‘Creating a Database — Part 1: File structure’.

You can follow along with the sections below, that describe a step-by-step of creating the database, seeding and interacting with it.

Creating tables

In the first part of the project, we performed two important steps for creating the database tables:

• We created a migration to represent the creation of the customers and orders tables, running the following commands in the terminal:

$ bundle exec rake db:create_migration NAME=create_customers

$ bundle exec rake db:create_migration NAME=create_orders

Executing the commands, generated two migrations version controls:

The files have a timestamp indicating the date and time in which the migrations were created.

Taking a look at the files that represent the migration for creating the customers and orders tables, we can notice that some code was automatically generated:

create_customers migration

create_orders migration

We have a class of CreateCustomers and CreateOrders that correspond to the exact same snaked case name of when we created these migrations with NAME=create_customers and NAME=create_orders, but now upper camel cased.

Note: This is a very important convention to follow as if the migration file name and the class name are different from each other, you will run into a NameError.

Both classes are inheriting from the ActiveRecord::Migration module from when we executed the migrations. It also auto generated a change method which is the most common for creating tables. In the block of this method, we will be creating the customers and orders tables structures respectively, with the create_table method and giving them columns:

create_customers migration

create_orders migration

Above, we created the customers and orders tables following the name convention that the table is the plural of its corresponding class, in this case the Customer and Order classes from the Ruby model files below:

Back with the migrations, we specified the data type of each column and the column’s names. On line 4 from the create_customers migration file for example, t.string represents a method for creating a column of String datatype and the symbol right next to it is the method argument, representing the name of the column, in this case “name”.

But writing that structure in the migration files still doesn’t create the tables we want. To do so, we need to migrate those changes through the terminal, running the following command:

$ bundle exec rake db:migrate

Doing that, Active Record creates a new file inside the migrate directory, called schema.rb:

The file above is auto-generated from the current state of the database and you should NOT edit it, as it’s just a mirror of how our database currently looks. Making changes to the database will automatically change the schema with its current version control date and time.

Note: If you’re working with Visual Studio code, make sure you have installed the SQLite extension so you can visualize the database while you’re developing it. You can browser and install the extension below:

SQLite extension for Visual Studio

In the db/migrate file, if you right click on ‘development.sqlite3’ and then ‘open database’, a new tab called SQLITE EXPLORER will be displayed. Clicking on it, we have:

You can notice that our last migration command created the two database tables of customers and orders, along with the columns names and types we specified.

We have also the schema_migrations, that shows us the version controls corresponding to the migrations created in the database:

Notice that the version timestamps are the same ones from our migration files.

Now we have our tables’ structure all set up and ready to receive data, so we can interact with them retrieving, sending and updating information.

Seeding tables

The next step we will go through is generating data to the tables we just created. After all, what is a database without data, right?

With the Active Record gem installed and required in our config/environment.rb file, we can use the pry console Rake task to add some data to the tables. To start the pry console, run the command in the terminal:

$ bundle exec rake console

This will start a pry session. You can check that the Customer and Order classes exist:

As an Object Relational Mapper, Active Record is mapping the customers and orders tables. Using built-in Active Record methods, we can check up the columns names of the tables:

Great! We now visualize what kind of attributes we are going to give to both a customer and an order.

To create an instance of a class, we will be using the .create built-in Active Record method, which will automatically save the data to the table. Let’s create a customer and an order:

On lines [2] and [3] of pry, we are creating instances of a customer and an order, respectively. Upon the creation we also assigned values of a name attribute for the customer and a number for the order, saving the instances into variables. Let’s check how our customers and orders tables currently look:

customers table

orders table

If you noticed the other attributes are NULL as we didn’t assign any values yet. We can still do it:

Even though we don’t have an attr_accessor macro that defines the method of assigning a value to an instance attribute, Active Record does the job for us, including the method without us needing to write down the macro.

Using the c1 variable created to store the customer instance, we are assigning values to the rest of the customer attributes and performing the .save method to save the changes to the database. Reloading the customers table, we have:

Now, let’s do the same for an order:

Reloading the orders table through SQLite Explorer, we have:

Looking great. Now you can repeat the process to generate more data if you would like to. You can also check this post I made about generating sample data for tables using the Faker gem, which is very helpful.

Conclusion

This post was the second part of the Creating a Database project, consisting of 3 parts. In the 1st part we went through the file structure, configuring and setting up the development environment along with the database connection.

In this part 2 of 3 we:

• Created migrations for the customers and orders tables;
• Created orders and customers tables and migrated those changes to the database;
• Seeded the table rows with sample data using Active Record through the Pry console.

In the 3rd and final part of this project, we will be making the tables association and then use Active Record built-in methods to retrieve, send and manipulate data from the tables.