Getting Started With Ruby On Rails Database Development

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.

How to start working with a database on Ruby on Rails?

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. 

• SQL databases follow a precise order. If a file isn’t described well or contains errors, the database will immediately highlight the error. All relations follow the same logic. SQL databases are easy to scale and manage. 
• NoSQL databases are less strict, which is why they are a go-to choice for managing tons of qualitative data. If information can’t be easily broken down into tables, setting up a non-relational database might be the way to go. NoSQL databases are easier to set up but harder to maintain – you need to avoid duplication, file errors and take responsibility for establishing relations. 

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. 

Which SQL database to choose for Ruby on Rails project?

The choice of a database doesn’t depend that heavily on the framework. Let’s take a look at the most common options. 

• PostgreSQL: it’s one of the most cost-efficient, performing, and versatile SQL databases out there. Its main strong suit is the ability to handle large amounts of data and complex operations. It has a reputation for being a strict database – meaning it doesn’t let developers input non-sense data and always adheres to data quality constraints. However, that’s what makes data quality management easy in the long run, especially in complex projects – like online marketplaces. 
• SQLite is supported by Ruby on Rails by default as a highly compatible database. It’s known as an internal database, used mainly to cover the needs of production and testing. It’s a common one for MVPs, local projects, and internal builds. SQLite is often used to set up the basic data structure and then replaced with a more powerful alternative.
• MySQL: arguably the most popular SQL database right now. Its requirements are less rigid than those of PostgreSQL – the software doesn’t return errors as often. Because of that, it’s easier to set up, although you need to be aware of issues in the long run. 

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.

Beginner: How to make a PostgreSQL database in Ruby

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. 

Step 1 – Creating a PostgreSQL user

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.

Step 2 – Create a password for your user

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. 

1. Open PostgreSQL prompt with sudo -u postgres psql
2. Enter a command to set a password: \password [username]
3. Enter the command again to confirm the password: \password [username]

Step 3 – Setting Postgres database with Ruby on Rails

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. 

1. Create a Ruby on Rails application rails new [application name] -d postgresql. The -d flag indicates to RoR that you’ll be using PostgreSQL to work with the application. Now both tools have the permission to interact. 
2. Open the directory with RoR application and create a new database there. To open a directory, enter cd application-name. To create a database, enter nano config/database.yml. You should get the following message:

# 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-name2

Don’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

Step 4 – Creating a database

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.

Step 5 – Testing a database inside the application 

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.0

You 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:3000

Use 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.

How to integrate MySQL with Ruby on Rails

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.

Step 1 – Adding MySQL gem to your RoR code

To connect MySQL to Ruby on Rails, enter these commands:

sudo apt-get update
sudo apt-get install mysql-client libmysqlclient-dev

You 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 mysql2

Step 2 – Preparing the Ruby on Rails application

Now you need to write comments in your app with Ruby to connect to database. 

• Enter the d-flag to allow MySQL’s access to the app: rails new [application name] -d mysql
• Define and secure database access by rooting a password. The application will attach a password to your username. Enter mysql -u root -p. This command gives a user the right to input a password.
• Log out of MySQL by pressing the quit command. 

Step 3 – Editing App’s Config File

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. 

• Open the directory with the application by entering cd my-app
• Open the database configuration file in the same directory. You can do it with a command: nano config/database.yml
• Set and confirm the password to your application and database: password: [MySQLpassword]. Instead of MySQLpassword, enter your own combination (for instance AFOR39093)
• Create new database. To create databases on Ruby on Rails, we use rake database commands – a series of commands that govern database migration Ruby and creation of the new ones. To start, enter the following command: rake db:create

Step 4 – Check the application

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.0

You 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 stop

For 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. 

How to integrate SQLite with Ruby 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-doc

For Ubuntu, enter:

apt install ruby-sqlite3

Where to go from here?

The 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: 

• MySQL official documentation: your go-to source for exploring features and constraints of MySQL;
• The official documentation of PostgreSQL: describes all the ins and outs of one of the most versatile and complex database management systems;
• The official documentation of SQLite: docs on the default Ruby database, frequently used for internal projects and small tasks;
• An ultimate guide to SQL: informative tutorials to get you started on SQL’s functionality and terminology
• The official guide to rake commands on Ruby on Rails: the official Ruby on Rails database tutorial describes rake commands, their use cases, and best syntax practices
• Ruby database cleaner: an open-source tool for managing your data quality and early error detection.

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.

Intermediate: Best practices of Ruby database development

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. 

Practice #1 – Integrate databases as early as possible

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. 

Practice #2 ― Reduce the number of calls to a database

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. 

Practice #3 – Use indexes to quickly find data

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:

Source

Practice #4 – Adopt consistent datatypes early on 

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 

• Preserving case but running case-insensitive companions – use a Citext data type. 
• Capturing a table independently together with a set of strings is possible with an array type. 
• Fetching a globally unique ID can be done with UUID type. 
• JSON pops are stored in JSON data types, specified in Ruby on Rails documentation. 

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).

Advanced: typical mistakes of Ruby on Rails database development

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. 

Getting multiple data with one query

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. 

Source

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. 

Differentiating between LENGTH, COUNT, and SIZE

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. 

• LENGTH loads all the records from the Ruby on Rails database first and determines their size
• COUNT defines the number of records by running an SQL query
• SIZE works only for loaded records and calls for the LENGTH method to determine the queries’ size. 

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}"
end

SELECT 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" = 1

Calculating on Ruby’s side

Determining 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
end

The 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]
end

The 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. 

Conclusion

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.