How To Grab Data Faster in Rails
Using SQL and Active Record Queries
SQL stands for structured query language and is a programming language that communicates with databases. Within a Rails app, we don’t have to write much code in SQL since Rails has built-in methods that translate commonly-used SQL commands into Ruby methods. However, we can use SQL to search our databases using a program such as Postico.
It’s important to note that there are many different implementations of SQL, with each being slightly different. The implementation we use in Bootcamp is PostgreSQL, but there are other implementations such as MySQL, NoSQL, and SQLite. When writing SQL, it’s important to make sure you know which database/ implementation you’re using, since the syntax may be a little different.
Using Postico with PostgreSQL Databases
Postico is an open-source software that can be used to view and edit Postgres databases. You can use it to open, view, and edit your database in a manner more similar to Excel.
Since Rails adds and manages additional fields to each Model instance (specifically, id, created_at, and updated_at fields), for a Rails app, it’s still better to generate new Model instances via the rails console or within the app itself. However, Postico makes it easy to update that data quickly and easily.
SQL Queries in Postico
You can also perform SQL queries to view your database in different ways. Examples of SQL commands you can use in Postico include:
SELECT * FROM orders ;
# pulls up all fields in your “orders” tableSELECT name FROM products ;
# pulls up the “name” column from the “products” tableSELECT first_name, email FROM users ;
# pulls up the first_name and email columns from the users tableSELECT * FROM products WHERE name LIKE ‘%Milk%’ ;
# pulls up all fields in the products table but only for the products that contain the string “Milk”.
You can even use SQL queries in Postico to view more complicated relationships between related tables. For example, in class we created an app that models an online store. The app’s database has users, orders, products, and carted_products (which is just a shopping cart model that links orders, products, and users). If I wanted to view all the products a specific user has ever bought, and how many they bought, I could create a table to easily see this data by using the following SQL query:
SELECT users.name, products.name, carted_products.quantity
FROM users
JOIN carted_products ON users.id = user_id
JOIN products ON products.id = product_id;Commonly Used PostgreSQL Commands
SELECT — Declares which columns you want to see
* — Wild Card = everything. Similar to the .all command in Rails
FROM — Declares which table you want to pull the data from
ORDER BY xxxx — Declares which column you want the data ordered by
SELECT
# declare which columns you want it to display*
# wild card = everything. Similar to the .all command in RailsFROM
# declare which table you want to pull the data fromORDER BY xxx
# declare which column you want the data ordered byWHERE
# define search conditionsLIKE ‘x’ #ILIKE ‘x’ #
%words% # all a search to only include the words specifiedJOIN … ON xxxs.id = xxx_id # add data from a related table & define the relationship
Rails Active Record Queries
Active Record Queries is the part of Rails that manages the database. For full documentation on active record queries, consult the Ruby on Rails Guide: http://guides.rubyonrails.org/active_record_querying.html.
Every Rails Model we create inherits from Active Record, which converts Rails methods to SQL. In order to use SQL within our Rails app, we need to use Active Record Queries. Let’s take a look at common Active Record Queries and translate them to the corresponding SQL commands.
To retrieve all the fields for all instances of your Contact Model
#Rails Active Query: Contact.all# Corresponding SQL Command: SELECT * FROM contacts ;
#Rails Active Query: Contact.Find_by(email: “example@email.com”)# Corresponding SQL Command: SELECT * FROM contacts WHERE email = ‘example@email.com’ ;
#Rails Active Query:Contact.all.order(:last_name)# Corresponding SQL Command:SELECT * FROM contacts ORDER BY last_name;
#Rails Active Query:Contact.where(“bio ILIKE ?”, “%triathlon%”)
# Corresponding SQL Command:SELECT * FROM contacts WHERE bio ILIKE ‘%triathlon’;
SQL Databases have been optimized to be very fast and efficient in terms of data retrieval and manipulation. In general, using SQL to retrieve and sort data will be much faster than using a similar command in Ruby.
For exambple, in order to retrieve a list of contacts in order of last name, it’s more efficient to use the code Contact.all.order(:last_name) — which, as an Active Record Query, uses SQL to sort the data, instead of:Contact.all.sort_by(last_name) — which uses Ruby to sort the data.
A Few Words on Syntax
In general, SQL is fairly straightforward. For someone coming from a Ruby on Rails background, the syntax seems fairly similar at first glance. But there are a few slight differences that are worth noting.
- When defining search terms, use a single equal sign (=) rather than a double equal sign (==).
- When naming a particular table & column, the table name is plural. This differs from Rails, where the table (aka Model) name is singular. This makes sense because usually for SQL, you want to see a column for all the rows (instances) in the table, whereas in Rails, you are generally dealing with a single instance.
As an example: in SQL you’d ask forusers.idand expect to see a table of all the id’s for all users. On the contrary, in Rails if you useuser.id, you’re generally asking for the id of one specific user (such as the current user who is logged into your website). - In SQL, you must use single quotation marks ‘’, whereas in Ruby/ Rails single and double quotes are interchangeable for the most part.
- Casing doesn’t matter in SQL. In class, we generally wrote the commands in UPPERCASE and all other terms in lowercase, just to see it easier. However, the commands execute the same regardless. On the other hand, casing matters in Ruby!
Being proficient in SQL and Active Record Queries will allow you to access and manipulate data faster than simply writing all your logic in Ruby. The nature of databases inherently makes them more efficient at sorting, ordering, and pulling information. Whenever possible, injecting SQL commands into your app in order to deal with data will make your app run faster than writing everything in Ruby.
