Creating a Database — Part 1 of 3: File structure
When you want to create a database with tables and information that you can retrieve and manipulate through code, you need to first set up files, gems and configurations in order to make your database working and well structured.
→ Project Composition:
This post is the 1st out of 3 parts of a project on how to create a database that has 2 tables in a one-to-many association. In this 1st part we will cover the file hierarchy composition and explain the functionality of each file of our database project, installing gems and configuring the development environment. In the 2nd part of the ‘Creating a Database’ project we will be creating migrations, tables and seeding those tables. In the 3rd part, we will establish the tables connection and interact with them through Active Record built in methods and tables relationship.
Following along the steps from each section below is extremely important to have your database up and running without breaking, so we can — in the 2nd and 3rd part s— start making use of it.
The database engine I will be working with is SQLite and for our communication with the database I will be using the Ruby Object Oriented programming language.
Introduction
In this project we will be creating 2 tables with a one-to-many association:
Above we can see the relationship between customers and orders:
- A customer has many orders;
- An order belongs to a customer.
First we need to create the files for our database project’s main directory. In the end our file hierarchy should look like this:
In the next sections I will go over the project’s files and summarize what each one of them represents.
Gemfile
This file contains the list of all gem dependencies required for our code written with Ruby — the code to manipulate our data — to work and should be located in the project directory root.
In the first line of the Gemfile, we need to specify the source of the gems which needs to be a valid RubyGems source. Most of the time it will be the URL “https://rubygems.org”.
The next step is to list all the required gems. Along with Ruby, these gems will enable access to libraries and programs, adding more functionality and improving our code:
Let’s go through each one of them:
- pry → we will be using ‘pry’ with Ruby for debugging, looking up values and params.. it’s a pretty useful tool;
- require_all → used to run all Ruby code from a specified directory — in our case we will be requiring all the Ruby code from the “app” directory as you will see later on this post;
- activerecord → gem that installs the Active Record library used for mapping created objects in Ruby into tables in our database — the number next to the gem’s name indicates its version;
- sinatra-activerecord → used to run our Rake tasks from our Rakefile;
- sqlite3 → used along with SQLite engine — you must have it already installed — to interface with the SQLite database;
- rake → installs Rake which allows us to build tasks in a Rakefile.
Note: In order to install all the gems above, you need to run the following command in your terminal:
$ bundle install
Rakefile
A Rakefile is written in Ruby syntax and its main purpose is to create Rake tasks. In this project, Rake is used with sinatra-activerecord gem (required on line 2 below) to create migrations, check migration status, seed the database, rollback and other handful tasks for our database structure:
On line 1 we are requiring our configuration file which I will go over it later in this post. Rake will also be used to start a pry session to lookup code, variable values and for debugging — this task is defined from line 4.
Note: If you don’t want to see the SQL logged in the terminal, you can comment out line 6. Also to check all rake tasks available, run in your terminal:
$ bundle exec rake -T.
app/models
The ‘app’ directory has a subdirectory ‘models’ which contains the Ruby files that will be created to map the database tables:
Each file represents the class of a table that we will be mapping. Let’s first create our classes and inherit them from ActiveRecord::Base so we can make use of Active Record’s built-in methods:
Notice that the files have the same name as their respective classes and that they are lowercased. This is because we are following Ruby and Active Record name conventions so our program doesn’t break. It’s very important to stick to these conventions throughout our project.
config
The config directory is responsible for our project configuration, for example to require files and connect Ruby to the database. Let’s go over each one of its subdirectories:
database.yml
File where we specify the information regarding the database. For example, in our project we will be using the SQLite database as the adapter in a development environment — we can also specify here other environments like test and production.
environment.rb
This is our project environment file, where we are going to be requiring all files and gems:
On lines 1 and 2 above, we are requiring all the gems from Gemfile. On line 4, we are requiring our Ruby models from the ‘app’ top level directory, which has ‘models’ as a subdirectory — already mentioned earlier in this post.
db
The ‘db’ directory represents the database files and has a ‘migrate’ subdirectory which contains the migration files. The migrations represent a version control of the alterations made on the database structure.
Once we have a migration to represent for example the creation of a customers table, the migration file will be displayed inside the ‘migrate’ directory. Let’s go over on how that happens:
- 1st → we have an empty ‘migrate’ directory (subdirectory of ‘db’, located on the top level directory);
- 2nd → we create a migration running the following Rake task: $ bundle exec rake db:create_migration NAME = name_of_migration;
- 3rd → after running the Rake task, a file with date and time stamps is created inside the ‘migrate’ directory, representing the migration just created:
The name syntax of the migration files corresponds to:
- year/month/day/hours/minutes/seconds.
This way, using migrations with Rake allows developers to have a database version control with a timeline since it was created and all of the database structure’s changes.
Conclusion
Building a database requires many important steps like creating files, configurations and name conventions in order to make a database working smoothly. In this post it was covered:
- The one-to-many table association that will be created in the part 2 of this database project;
- File hierarchy composition of the project;
- Functionality and usage of each file, along with conventions and file requirements.
Going through all these first steps to set up the database project might not catch your eye at first, as we are not making use of the database yet. However, they are essential to make sure that the project is in good form to receive commands — in parts 2 and 3— and interact with.