Dec 29, 2017
SQL Database and Rails
Understanding the basics of SQL language and Rails
After learning about Ruby, the first step we took was to understand how the web and Ruby on Rails request-response cycle work.
Now it’s time to learn about databases and how does it connect with Ruby on Rails. Basically, the answer is the Model. The M from MVC as we learned here.
Before learning web development with Rails, I really recommend learning about Ruby first.
Let’s begin!
What is a database?
Hmmm… The first thought that comes to my mind is “something” that stores data, information.
But this “definition” is quite imprecise! An array, a hash, a linked list, or any data structure can be “something” that is able to store data.
When you turn off the computer, you lose every data values that were stored in that array (the same as all data structures). So it’s not a good idea to store all my precious data.
We need to solve two problems here:
- Store data and get it anytime we want.
- Store data in an organized and structured way, so we can get it in an easy way.
Should I store all the data in a notepad? Just put all the information inside it separated by commas, save the txt file, and done. Now I can open it and get all the data I want. We can store data and get it anytime… problem solved!
We solved this problem, but we missed the other. Now all the data is stored and we don’t lose it. But not well-structured in the file. We need the rule to store and get data in an organized and well-structured form.
Let’s think about how can we organize the data in a well structured way.
What about organizing all the data in tables?
So, what we have here: We have the table’s header (columns name: First Name, Last Name, Address, etc) and it’ll have values that we’ll store. For example, if we want to store the string “Mickey” (the value), it’ll be stored in the “First Name” column.
- Table: let’s say People
- Columns: First name, Last Name, Address, etc
- Rows: in this case, we can say that a row can be a person with, for example, first name “Mickey”, last name “Mouse”, address “123 Fantasy Way”, etc.
- Fields: all data stored in the database.
And now we have a well-structured way to store data: Table way!
How about get, delete, insert, and update data?
We’ll use SQL language (I’ll not mention NoSQL world!) to manipulate data. Let’s get the basics!
- GET: if we want to get all data (person) from People table, we need to select it from that table.
The (*) symbol means that it will select all columns from People table. If we can get all columns, we can specify which columns we need for this select.
2. DELETE: we want to delete all data from our People table.
But it’s not common to delete all data from a table. We usually use a condition to delete, like “I want to delete all people under age (less than 21 years old)”. We will learn it later in this post!
3. INSERT: we will insert/store data into the table.
or we can specify which columns we want to insert data.
4. UPDATE: we have stored data, but we want to update it.
Using conditions in our queries
Now we can use SQL language to query (select, delete, insert, update) data.
- But if we want to delete just records with last name Kinoshita?
- Or if we want to update a specific person with first name Leandro and last name Kinoshita?
- Or just select all data from people table and sorted by age from younger to older?
Yeah, we use conditions like where and order by and operators like or and and. Let’s see some examples:
- Deleting all records from people table with last name Kinoshita.
- Updating all records from people table with first name Leandro and last name Kinoshita.
- Selecting all records from people table but order by age. (in ascending order → ASC)
Relationship between tables
We know how to execute queries (with or without conditions). Let’s understand how the tables’ relationship works.
- One to One (1–1): it’s about a relationship between two tables in which one can only belong with the other. e.g. A person has one passport and that passport belongs to that specific person. So in this example, we have table People, table Passports and a 1–1 relationship.
- One to Many (1-n): it’s about a relationship between two tables in which a record from one table can reference many records from another. e.g. Imagine an e-commerce platform: users, orders, products, payments, etc. A user can have many orders, and each order belongs to that specific user. So in this example, we have table Users, table Orders, and a 1-n relationship.
- Many to Many (n-n): it’s about a relationship between two tables in which a record from one table can reference many records from another. And a record from another can also reference many records from the one. e.g. We have again the e-commerce platform: we divide products into categories. A category has many products (Category Technology has many products like cell phones, notebooks, etc) and a product can belong to many categories (Product Cellphone belongs to Technology and Electronics Categories). So in this example, we have table Products, table Categories, and an n-n relationship.
Rails Mode ON
We understood the meaning of databases, tried some basic queries, and made the relationship between tables. But how can we use that knowledge in the Ruby on Rails and web development World?
First of all: Rails is Rails. The Database is Database. Is it obvious? But people usually get confused about that.
A User model can represent a Users table. But the model isn’t the table.
- In the database, we have tables and rows.
- On rails, we have models (classes) and objects.
Imagine a blog site. The blog needs an author for each post. So we create an Authors table with some columns (first_name, last_name, etc)
.. in the migration, we add columns first_name, last_name, email, birthday, email, created_at, and updated_at. (created_at and updated_at are created by the t.timestamps code.
So we created a migration (Ruby code), run rake db:migrate command in the terminal and it generates a table Authors with first_name, last_name, email, birthday, email, created_at, and updated_at columns.
Back to Rails, we can create an Author model:
So now we have an Authors table with some columns and an Author model.
Using Rails Console
Open the terminal and type bundle exec rails c. Remember, we are in the RAILS console, so we have classes, objects, attributes, etc.
Relationship on Rails
We created an Authors table/model. What we need now is a Posts table/model. An author has many posts and a post belongs to a specific author. The relationship here is one to many (1-n). Remember?
So when we create a Posts table, we need to store a reference to the post’s author (column author_id in the Posts table). It’s known as the Foreign Key.
And how do we relate the models?
author has_many posts
post belongs_to an author
Using Rails Console
* Quick explanation about has_many and belongs_to. Both codes are methods defined on ActiveRecord class. If you see, we create our models inheriting from ActiveRecord::Base.
Remember on Ruby Foundation that we studied about Object Oriented Programming, the Inheritance part? This is why we can use has_many and belongs_to methods without defining it anywhere on our application. Rails handles it for us.
If you want to understand this concept deep, clone the Rails repo or understand the Behind the Scenes of the ‘Has Many’ Active Record Association.
Queries on Rails
We can query using ActiveRecord methods:
- all: Get all objects from a specific model.
Behind the scenes, it is executing the SELECT * FROM posts query.
- find: Using find we can get the object by the id (primary key).
Behind the scenes, it is executing SELECT * FROM posts WHERE id = 1 query.
- where: Get the objects that pass the conditions.
Behind the scenes, it is executing SELECT * FROM posts WHERE title = 'Database & Rails'query.
- order: Sort all objects based on a column.
Behind the scenes, it is executing SELECT * FROM posts ORDER BY created_at DESC query.
That’s all!
We learned a lot here. I hope you guys could appreciate the content and learn more about how the Databases and Rails models work.
This is one more step forward in my journey to learning and mastering Rails and web development. You can see the documentation of my complete journey here on my Renaissance Developer publication.
Have fun, keep learning and coding.
