When you are building an app with Ruby on Rails development, chances are, you will handle a lot of data. Any kind of professional project can’t store data simply in the notepad – without proper structure, it’ll quickly become a mess.
So, in this post, we’ll talk about the most popular databases in RoR web development, walk you through the process of integrating them, and describe the best practices. If you are building a backend in Ruby on Rails, create a database that will take care of data-based processes and keep the app’s information structured.
A database is a file that stores organized information.” Organization” is a keyword – databases group objects according to their values, characteristics, hierarchy. Databases use formal structure and models to show relationships between data. When your application scales, you can still easily trace every file.
Databases can be SQL and NoSQL. SQL stands for Structured Query Language, and it uses tables to store data and display relations between them. Each table is connected to at least one other table, and all information, therefore, is a part of a defined structure. Because of this emphasis on relations, SQL databases are often called relational.
NoSQL databases don’t use Structured Query Language. Unlike relational databases that use the same language, no matter which management system you choose, noSQL ones depend on tools. If you work with Mongo-DB, one of the most popular non-relational databases, you have to learn its logic and terminology. If the team then switches to another non-SQL tool, they relearn most aspects from scratch.
Choosing between relational and non-relational databases for Ruby on Rails
Deciding between SQL and noSQL databases is the first step. Both have specific advantages.
We prefer relational databases because they are more scalable. In the long run, it’s easier to recruit developers, add new data, and control data flow.
The choice of a database doesn’t depend that heavily on the framework. Let’s take a look at the most common options.
Our usual choice is PostgreSQL because it’s stable, versatile, and popular. Our clients can easily add elaborate functionality and make changes to the data structure. On top of that, there are many resources on Ruby on Rails and Postgre combination, which also makes a difference in the long run.
In this Ruby database tutorial, we’ll focus on integrating 3 most popular RoR databases: PostgreSQL, MySQL, and SQLite.
To start, you need to set up a Cloud Server on Linux (Ubuntu 16.04), install and open PostgreSQL. You need a basic background of Ruby on Rails and a solid understanding of Ruby’s syntax.
The database commands here will be given from a user – so be sure to use the same account that you used for the installation of RoR.
You need to create a user account on PostgreSQL. This profile will be synchronized with Ruby on Rails page and used to issue back-and-forth commands. Here’s the command:
sudo -u postgres createuser -s [username]
Just a reminder: before Ruby database connection, make sure that your PostgreSQL matches the RoR one to a T. It’s going to be very important as you create a Postgres database in ruby.
To define access and protect database security, you need to assign a password to your user. It doesn’t have to be the same as for the application’s compatibility RoR account.
Next, you need to build a bridge between your database management system and RoR application. Here’s how to do Ruby database connection for a new application.
# The specified database role being used to connect to postgres.
# To create additional roles in postgres see `$ createuser --help`.
# When left blank, postgres will use the default role. This is
# the same name as the operating system user that initialized the database.
#username: application-name2Don’t forget to update your user name. In the last row, change the username to the one that corresponds to your RoR and PostgreSQL user.
In this file, add a password to your account. This way, it’ll be stored in the system and you won’t lose access credentials.
After the last row (the #username one) add this:
# The password associated with the postgres role (username).
password: XPmMxZf
How to create a new database table in Ruby on Rails? As usual, Ruby on Rails uses rake comments to create, migrate, and manage databases. To create a database, enter
rake db:create.
To see if the integration was successful, open the application with your browser. Go to the application directory and enter the following command:
bin/rails s --binding=0.0.0.0You should see the following message:
[user@localhost my-app]$ bin/rails server
=> Booting Puma
=> Rails 5.0.0.1 application starting in development on http://localhost:3000
=> Run `rails server -h` for more startup options
Puma starting in single mode...
* Version 3.6.0 (ruby 2.3.1-p112), codename: Sleepy Sunday Serenity
* Min threads: 5, max threads: 5
* Environment: development
* Listening on tcp://localhost:3000Use Ctrl-C to stop
The application is running with no errors. To be 100% sure, you can go to http://your-IP:3000/ (instead of your-IP enter the numbers that correspond to your address). If you established the Ruby connect to postgres database, you should see the Rails welcome message.
If you are using ruby on rails, connect it to mysql database with a similar method. You need to prepare installed MySQL, open the root password from MySQL, and run Ruby on Rails.
To connect MySQL to Ruby on Rails, enter these commands:
sudo apt-get update
sudo apt-get install mysql-client libmysqlclient-devYou connected the MySQL client to the file, now you need to download a gem that will be used by Ruby on Rails to interact with a database. To do that, enter:
gem install mysql2Now you need to write comments in your app with Ruby to connect to database.
How do I create MySQL database with Ruby on Rails? Now, you’ll create a new Ruby on Rails application and connect it to your MySQL database.
You can verify the compatibility of an application with a database by closing the app config file and opening the application in your browser.
In the app’s directory, enter:
bin/rails s --binding=0.0.0.0You should get the following message:
[user@localhost my-app]$ bin/rails server
=> Booting Puma
=> Rails 5.0.0.1 application starting in development on http://localhost:3000
=> Run `rails server -h` for more startup options
Puma starting in single mode...
* Version 3.6.0 (ruby 2.3.1-p112), codename: Sleepy Sunday Serenity
* Min threads: 5, max threads: 5
* Environment: development
* Listening on tcp://localhost:3000
Use Ctrl-C to stopFor a final verification, visit http://IP-address:3000. Delete ‘IP-address’ and enter the numbers that correspond to your IP instead. If the integration was successful, the link will redirect you to Ruby’s welcome page on Rails.
SQLite is a default Ruby database – it comes in the package with Ruby itself. So good news – its integration takes only several commands.
Note: the Linux package of Ruby comes with SQLite, so you can use commands to manage it. On Windows, you need to install DevKit first.
Then, you need to call the database with a command gem install sqlite3
The Linux distribution doesn’t require install commands – you can start entering the rake commands for Ruby database immediately.
For Fedora, enter:
dnf install rubygem-sqlite3 rubygem-sqlite3-docFor Ubuntu, enter:
apt install ruby-sqlite3The next step to database development is learning the functionality of your database. Most data-based processes will now be handled there, not in Ruby. So, we recommend to focus on looking into official documentation of your database and learning SQL (at least the basics of it).
You don’t need to be an expert database developer to make basic processes work, however, we’d recommend learning Ruby commands and database features separately – to avoid confusion.
So, here are some resources that will be helpful in your journey:
Even if you are only getting around database development in Ruby on Rails, we recommend checking out our next sections, for intermediate and advanced developers and teams. You’ll get a basic idea of what database development standards entail. In our experience, paying attention to the best practices early on is a skill of a professional database developer.
If you aren’t just getting started with Ruby on Rails database development but already have it up and running, you might be interested in practices that could improve your efficiency. We asked our Ruby on Rails database engineers to share their best practices. They apply to most databases and discuss general principles rather than DBMS-specific technicalities.
The main point of a database is to handle data. They have versatile features for organizing, sorting information, detecting errors, and cleaning up the entire structure. Sometimes, intermediate developers and small teams prefer to handle small data-related tasks with Ruby and RoR alone.
So, the rule of thumb is, if you want to carry out some data-based process on Ruby, consider that it definitely can be accomplished in a database. Also, much faster.
Ruby on Rails and Active Record make it easy for developers to work with databases. Developers can be tempted to create many small datasets, because they are easier to set up and manage. Then, they are forced to send multiple data queries to manage each of these sets.
However, once the application is released, the amount of data is constantly growing. If a page, to work, sends multiple calls to a Ruby on Rails database, it will become really slow. When an application hosts thousands of users simultaneously, servers might not be able to handle that many requests.
The best practice: don’t forget to use includes and joins to request multiple data in one query. However, if you just ask for data without using proper syntax, you might be stuck in a loop and block the entire operation. We’ll talk more about that in the advanced section.
To avoid going through more data than necessary, we recommend adding indexes into your columns. Here’s an example of how you can mark your code for better navigation:
If you want to write code that will be easy to maintain, the best way to do it is promoting strong adherence to data types within your team. You’ll have consistent rules for naming and hierarchy, and your codebase will become much more readable.
In particular, take your time to explore the lesser-known data types that will help you to order your data in many scenarios.
Examples of Postgre data types
You can take a look at Understanding the data types – PostgreSQL’s secret weapon – an excellent compilation of all the most common and practical data types (and some lesser-known ones as well).
When you handle large amounts of data, you have to consider the best development practices carefully. One additional loop or poorly handled command can result in massive delays. Unnoticeable at first, these issues will pile up and sabotage your performance quality.
Even if you don’t have that much Ruby on Rails development experience, we recommend looking through this section – without paying much attention to technical terms. Even without a profound understanding, it’ll give you an idea of what issues developers typically face while integrating Ruby on Rails databases.
Often, developers prefer incorporating a lot of data in a single query to speed up the development process and write shorter code. To do that, they end up creating a loop – instead of fetching data once, the application goes through the same process several times.
To gain more control over performance and prevent over-fetching, try to split data requests into different queries. Also, always look out for unwanted loops.
The correct version would be
By including the “includes” command, you let the application know that all data should be fetched with a minimal number of queries. This issue is also known as an N+1 problem and even addressed in official Ruby’s documentation.
One of the most typical operations in the database is calculating how many records it contains. Essentially, there are three ways of approaching this task.
COUNT is the fastest one, but it gives you less information. The choice between LENGTH and SIZE depends on whether the files have been downloaded or not. If you are not sure, use Size.
Examples
users = User.where(hotel_id: 1)
users_count = users.size
users.each do |user|
puts "#{user.full_name}"
endSELECT COUNT (count_column) FROM ❪SELECT 1 AS count_column FROM "users" WHERE "users"."hotel_id" = 1❫ subquery_for_count SELECT "users".* FROM "users" WHERE "users"."hotel_id" = 1Determining where to perform data-related calculations (such as calculating the size of all records, for example) has a key impact on performance. Unfortunately, the difference between the two is often ignored – which results in serious processing delays.
The gold standard is to perform calculations on SQL size instead of Ruby. Sure, the code is more extensive, but the performance speed improves by 5-10 times.
Here’s an example:
#1 – Processing on Ruby’s side
companies = Company.includes(:users).limit(100)
companies.each do |company|
puts company.users.map(&:hotel_id).uniq.count
endThe return of a benchmark: 1.771338 seconds
#2 – Processing on SQL’s side
companies = Company.limit(100)
.select('companies.*, companies_hotels.hotels_count as hotels_count')
.joins('
INNER JOIN (
SELECT companies_users.company_id,
COUNT(DISTINCT users.hotel_id) as hotels_count
FROM users
INNER JOIN "companies_users" ON "users"."id" = "companies_users"."user_id"
GROUP BY companies_users.company_id
) as companies_hotels ON companies_hotels.company_id = companies."id"
')
companies.each do |company|
puts company[:hotels_count]
endThe return of a benchmark: 0.127908
In large projects, these differences accumulate and become a decisive factor in determining final performance speed and User Experience.
In this guide to Ruby on Rails database development, we summarized the best practices and resources for getting started with database integration and management. In a nutshell, integrating databases is one of the most significant decisions for any backend – because the database will end up handling the majority of your data-based process.
If you look for a team of experienced data engineers with an in-depth knowledge of Ruby on Rails or want to build an RoR-based backend for your web application, reach out to our team. Our developers will take a look at your challenge and get back to you with an actionable plan.
Contact Syndicode for Ruby on Rails development.