SQL Database and Rails
understanding the basics of SQL language and Rails
So, the first question is…
1. What is a database?
Hmmm. The first thing that comes to my mind is about “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. But when you turn off the computer, you lose every data values that was stored in that array (the same as all data structures). So it’s not a good idea to store all my precious data.
Should I store all the data in a notepad? Just put all the data inside it separated by commas, save the txt file, so I can open it and get all the data I want. We can store data and get it later... problem solved!
We have all the data stored, but not well structured in the file. We would need a 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 organize all the data in tables?
So, what we have here: We have the table’s header (columns name: First Name, Last Name, Address, …) and it’ll have values that we’ll store. For example: if we want to store the string “Mickey” (the value), it’ll will be stored in the “First Name” column.
- Table: let’s say People
- Columns: First name, Last Name, Address, …
- 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!
2. How about get / delete / insert / update data?
We 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.
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 data stored, but we need to update it.
3. Using conditions in our queries
Now we can use SQL language to query (select, delete, insert, update).
- 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 records from people table but sorted by age?
Yeah, we use conditions like where and order by and operators like or and and. Let’s see some examples:
1. Deleting all records from people table with last name Kinoshita.
2. Updating all records from people table with first name Leandro and last name Kinoshita.
3. 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 a 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 cellphones, notebooks, etc) and a product can belongs to many categories (Product Cellphone belongs to Technology and Eletronics Categories). So in this example, we have table Products, table Categories and a n-n relationship.
4. Rails mode: on
We understood the meaning of databases, tried some basics queries and made relationship between tables. But how can we use that knowledge in the Ruby on Rails World?
First of all: Rails is Rails. Database is Database. Is it obvious? But people usually get confused about that.
An User model can represents an Users table. But the model isn’t the table.
- In the database, we have tables and rows.
- In the 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..
So we created a migration (Ruby on Rails code), run rake db:migrate command in the terminal and it generates a table “authors” with first_name, last_name, email, birthday, 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 rails c (a shortcut for rails console). Remember, we are in the RAILS console, so we have classes, objects, attributes, etc.
5. 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 about the post’s author (column author_id in the Posts table).
And how do we relate the models?
- author has_many posts
- post belongs_to author
- Using Rails Console:
6. Queries on Rails
We can query using ActiveRecord methods:
- all: Get all objects from a specific model.
- find: Using find we can get the object by the id (primary key).
- where: Get the objects that pass the conditions.
- order: Sort all objects based on a column.
7. Bye!
That’s it guys! I want to update (include more details) that blog post. The idea is to share a great content and the community helps improving this post! ☺
I hope you guys can appreciate the content and learn more about how database/model works in Rails.
If you enjoyed this piece, you might also like
