Forem: Andrei Kaleshka

Cheaper and Risk-Free Ruby on Rails App Redesign

Andrei Kaleshka — Wed, 30 Aug 2023 04:00:00 +0000

Ruby on Rails app redesigning challenges

Redesigning a Ruby on Rails application is a well-known challenge for many projects. Any project UI gets outdated. It needs to get a fresh look that’s more appealing to the users. It may not be an issue for web apps without live users. But it’s a tricky task for already launched businesses serving thousands of users per day.

While a development team is redesigning, the application should continue functioning. At the development time, the new design gets validated and tested against the back-end. If the back-end is incompatible, it must be adapted to the new realm. These changes should not break the old functionality. Overcoming these issues is essential in a Ruby on Rails application redesign.

This article shares the approach we took to apply the new design in one of our projects. The changes we made increased the project revenue by 30%. As a bonus, the implemented changes unleashed many other opportunities, including:

Building a modern mobile application.
Making the code more fault-tolerant, performant, and stable.
Adding new features, such as preventing account sharing, that can increase the app revenue.
Making the tech stack upgrade easier.
Implementing traffic control and advanced caching.
Making SEO optimization easier.

Why redesigning a Ruby on Rails app

There are several reasons why an app may consider implementing a new UI/UX design. Regardless of the motive, the ultimate goal is always the same - to increase revenue. Note, that keeping users using the app and not going away is the same goal. Due to an old and unhandy design, an app can make the customers leave. In this case, redesign also can help. If it’s clear that a redesign won’t have a positive impact on revenue, it may be an unnecessary expense. Redesigning is usually an expensive change. So learn from the users if the design is a problem before making this decision.

What is the new design

A new design consists of screens (mockups) created by a designer using software like Figma or Adobe Photoshop.

In our case, the client provided the mockups, and our task was to turn them into code and integrate them with the existing back-end. We aimed to minimize downtime during the transition and reduce development efforts.

Different technical approaches of a Ruby on Rails app redesign

Nowadays, web projects must be responsive and have a mobile application. Modern web app design features a rich UI with many elements on one page. The old-fashioned way of generating HTML on the back-end is going away. Instead, a separate web front-end or mobile app handles the front-end, while the back-end serves data via API.

In pure Ruby On Rails applications, the front-end code lives alongside the back-end. That can fulfill modern web app requirements and is cheap in the beginning. However, it becomes hard to maintain due to the mix of different technologies in one place. It makes it difficult to find developers who can understand and maintain the system. We prefer separate back-end and front-end. The specialists can do their job quickly with high quality on their end.

Risk-free approach of a Ruby on Rails app redesign

The project we received was a Ruby On Rails application with a Rest API implemented on Grape. An iOS app used this API. The web version used ERB and Slim templates with a UI from the previous decade. Some dynamic features on the web used Knockout.js, which is no longer maintained.

To update the UI, we used the Next.js framework with TypeScript and implemented GraphQL for the API. To avoid errors, we created a separate repository for the new UI and extracted functionality that could be reused in both the old app and the new UI.

The project took almost a year with 2 developers and 1 project manager working on it. To deploy the changes, we used CloudFront as a proxy on top of the old app and the new front-end app. We gradually switched web requests to the new UI using a feature flag to reduce the risk of failures or outages. The transition went smoothly without major issues.

As a bonus, we got all requests geolocated since they pass through the AWS CloudFront. That allowed us to control the users’ network traffic and fight against sharing accounts.

Our results of the Ruby on Rails app redesign

Switching to the new design made the project more attractive to users, resulting in more signups and increased satisfaction among old users. This allowed us to increase charges by 20% and revenue by 30%. The redesign expenses were paid off within the first 3 months after release. See below for the paid user dynamics analysis.

The release date was on the 1st of February 2021.

Why our approach of Ruby on Rails app redesign is cheaper and less risky

Redesigning apps can be painful and distracting. The Rails assets pipeline changes often, making sticking with it turn the code into legacy quickly. Finding specialists to maintain this code without sacrificing quality on both front-end and back-end is challenging. Separating the front-end and leaving only the Rails back-end for the API makes redesigning and maintenance smoother, allowing for a diversified team with good specialists on both ends.

Next.js was a wise choice for the front-end, improving SEO with image optimization and cache facilities out of the box.

Acknowledges

I’m grateful and proud to have worked with the following people on this project at different times:

Daniel Dauwe
Vadzim Jakushau
Illia Pruskyi
Soltan Yangibayev
Svetlana Zhuravitskaya
Alexey Mikitsik

Cheaper and Risk-Free Ruby on Rails App Redesign was originally published by Andrei Kaleshka at Build Ruby On Rails applications with us on August 30, 2023.

Select unique latest grouped records from DB

Andrei Kaleshka — Thu, 13 Jul 2023 04:00:00 +0000

Nowadays, almost every Ruby on Rails application has a so-called recent records block. This block usually shows statistics or a list of recent things within the project during the last few days searched by some criteria. It can be something like “top 10 products”, “the most popular projects”, or “the most relevant apartments”. Read this blog post the learn how to efficiently build data for these blocks using SQL and window functions in Ruby on Rails app.

Recent records - the task overview

Assume that you’ve got an app that has Project model. It has many ratings. The Rating model has just a value from 1 to 5 assigned by users to some projects.

For the task understanding it’s enough to have a look into the table definition:

db-# \d ratings
                                          Table "public.ratings"
   Column │ Type │ Collation │ Nullable │ Default
═════════════╪════════════════════════════════╪═══════════╪══════════╪═════════════════════════════════════
 id │ bigint │ │ not null │ nextval('ratings_id_seq'::regclass)
 reviewer_id │ bigint │ │ │
 reviewee_id │ bigint │ │ │
 rating │ integer │ │ │
 review │ text │ │ │
 project_id │ bigint │ │ │
 created_at │ timestamp(6) without time zone │ │ not null │
 updated_at │ timestamp(6) without time zone │ │ not null │
Indexes:
    "ratings_pkey" PRIMARY KEY, btree (id)

This table has the following data:

db-# select id, reviewer_id, reviewee_id, rating, project_id, created_at from ratings;
 id │ reviewer_id │ reviewee_id │ rating │ project_id │ created_at
════╪═════════════╪═════════════╪════════╪════════════╪════════════════════════════
  8 │ 9 │ 10 │ 5 │ 27 │ 2022-12-05 21:46:01.583185
 12 │ 7 │ 6 │ 5 │ 26 │ 2022-12-23 14:35:11.047002
 13 │ 6 │ 7 │ 5 │ 26 │ 2022-12-23 14:36:48.366411
 18 │ 9 │ 10 │ 5 │ 39 │ 2023-03-01 23:27:52.68548
 19 │ 10 │ 9 │ 5 │ 39 │ 2023-03-01 23:28:32.880234
 20 │ 9 │ 10 │ 5 │ 86 │ 2023-03-01 23:35:15.564763
(6 rows)

Our task is to return the latest reviews per project. So the resulting records should be these:

 id │ reviewer_id │ reviewee_id │ rating │ project_id │ created_at
════╪═════════════╪═════════════╪════════╪════════════╪════════════════════════════
  8 │ 9 │ 10 │ 5 │ 27 │ 2022-12-05 21:46:01.583185
 13 │ 6 │ 7 │ 5 │ 26 │ 2022-12-23 14:36:48.366411
 19 │ 10 │ 9 │ 5 │ 39 │ 2023-03-01 23:28:32.880234
 20 │ 9 │ 10 │ 5 │ 86 │ 2023-03-01 23:35:15.564763
(4 rows)

Note the project_id is distinct compared to the all records. And the timestamps are the most recent for those duplicated projects (their id = 26, 39).

There is no way to solve this task efficiently using only ActiveRecord functionality and pure Ruby. But SQL can solve this with the window function technique.

How window function with row number partition works

The idea is the following - we rank all records inside the table from 1 no N for the duplicated records of our search criteria. The most recent record gets 1, older one gets higher rank. The distinct rows will have 1.

For example:

 id │ project_id │ reviewee_id │ created_at │ row_number
════╪════════════╪═════════════╪════════════════════════════╪════════════
  8 │ 27 │ 10 │ 2022-12-05 21:46:01.583185 │ 1
 12 │ 26 │ 6 │ 2022-12-23 14:35:11.047002 │ 2
 13 │ 26 │ 7 │ 2022-12-23 14:36:48.366411 │ 1
 18 │ 39 │ 10 │ 2023-03-01 23:27:52.68548 │ 2
 19 │ 39 │ 9 │ 2023-03-01 23:28:32.880234 │ 1
 20 │ 86 │ 10 │ 2023-03-01 23:35:15.564763 │ 1
(6 rows)

The ratings with id = 8, 20 receive row number 1 because these projects are distinct (27, 86). But projects with id = 26, 39 have several ratings that’s why the rows with this project id have row_number 1 and 2. The most recent ratings per project receive 1, and the older ones receive row number 2.

Use subselect to filter correct results

If we filter out those row numbers greater 1 we get the required result. If that would be a table we could use the SQL’s where clause. For example, a view (virtual table) could be created for that. But we will keep it simple. We will use subselect: initially we prepare select to return the data as above and immediately use select statement to filter out the correct result.

But first, let’s see how to write SQL statement to assign the row number using the already noticed window function :

db-# select
        id,
        project_id,
        reviewee_id,
        created_at,
        row_number() over (partition by project_id order by created_at desc)
        from ratings
        order by created_at;

 id │ project_id │ reviewee_id │ created_at │ row_number
════╪════════════╪═════════════╪════════════════════════════╪════════════
  8 │ 27 │ 10 │ 2022-12-05 21:46:01.583185 │ 1
 12 │ 26 │ 6 │ 2022-12-23 14:35:11.047002 │ 2
 13 │ 26 │ 7 │ 2022-12-23 14:36:48.366411 │ 1
 18 │ 39 │ 10 │ 2023-03-01 23:27:52.68548 │ 2
 19 │ 39 │ 9 │ 2023-03-01 23:28:32.880234 │ 1
 20 │ 86 │ 10 │ 2023-03-01 23:35:15.564763 │ 1
(6 rows)

The row_number() over (partition by project_id order by created_at desc) is a window function that assigns row number from 1 to N for the records duplicated by some criteria. In this case the criteria is distinct project_id sorted by created_at desc.

Running this query inside DB console will produce the result above.

Wrap this select with another select and filter only rows with number = 1:

db-# select
  id,
  reviewer_id,
  reviewee_id,
  rating,
  project_id,
  created_at from (
    select *, row_number() over (partition by project_id order by created_at desc)
    from ratings
    order by created_at
  )
  as ratings where row_number = 1;

 id │ reviewer_id │ reviewee_id │ rating │ project_id │ created_at
════╪═════════════╪═════════════╪════════╪════════════╪════════════════════════════
  8 │ 9 │ 10 │ 5 │ 27 │ 2022-12-05 21:46:01.583185
 13 │ 6 │ 7 │ 5 │ 26 │ 2022-12-23 14:36:48.366411
 19 │ 10 │ 9 │ 5 │ 39 │ 2023-03-01 23:28:32.880234
 20 │ 9 │ 10 │ 5 │ 86 │ 2023-03-01 23:35:15.564763
(4 rows)

Voila, we’ve got what we want!

Use ActiveRecord.from to return the results as Ruby objects

Since we’ve got the SQL query it’s easy to port it into ActiveRecord and get eventually the list of Ruby objects. We will use the ActiveRecord.from to write the subselect:

> Rating
  .select("id, reviewer_id, reviewee_id, rating, project_id, created_at")
  .from("(select *, row_number() over (partition by project_id order by created_at desc) from ratings group by project_id, reviewee_id, created_at, id order by created_at) as ratings")

  Rating Load (41.9ms) SELECT id, reviewer_id, reviewee_id, rating, project_id, created_at FROM (select *, row_number() over (partition by project_id order by created_at desc) from ratings group by project_id, reviewee_id, created_at, id order by created_at) as ratings WHERE "ratings"."row_number" = $1 [["row_number", 1]]
=> [#<Rating:0x000000011190d410 id: 8, reviewer_id: 9, reviewee_id: 10, rating: 5, project_id: 27, created_at: Mon, 05 Dec 2022 21:46:01.583185000 UTC +00:00>,
 #<Rating:0x000000011190d348 id: 13, reviewer_id: 6, reviewee_id: 7, rating: 5, project_id: 26, created_at: Fri, 23 Dec 2022 14:36:48.366411000 UTC +00:00>,
 #<Rating:0x000000011190d280 id: 19, reviewer_id: 10, reviewee_id: 9, rating: 5, project_id: 39, created_at: Wed, 01 Mar 2023 23:28:32.880234000 UTC +00:00>,
 #<Rating:0x000000011190d1b8 id: 20, reviewer_id: 9, reviewee_id: 10, rating: 5, project_id: 86, created_at: Wed, 01 Mar 2023 23:35:15.564763000 UTC +00:00>]

You can run this experiment yourself on this demo app.

Conclusion

Advanced SQL understanding allows you to write performant advanced functionality in a Ruby on Rails application efficiently.

If you like this article and would like to see more examples of how SQL can improve your software development life read these articles:

Have a good day ahead and happy coding!

Select unique latest grouped records from DB was originally published by Andrei Kaleshka at Build Ruby On Rails applications with us on July 13, 2023.

Use timestamp for predicates in Rails

Andrei Kaleshka — Thu, 15 Jun 2023 04:00:00 +0000

Why use timestamp field instead of boolean for checkbox

You are about to add a new boolean attribute into your model inside a Ruby on Rails application. It’s simple as is - add a boolean column in DB and call it a day. But stop and think - consider a timestamp type for the corresponding columns in the database.

This approach allows for tracking the time when the value change. That can be helpful in debugging, especially when dealing with production issues.

Ok, it’s preferable to use the timestamp field type on the back-end. But it is still convenient to have a checkbox on the user interface. Thus, the timestamp column type does not match the UI field type (checkbox). That means the value (such as true/false, yes/no, or any other) from the front-end should be transformed into a timestamp or nil on the back-end. That’s needed, to ensure compatibility with the mass-assignment methods (.new or #update).

Avoid code duplicating with a universal solution

One could write this type of transformation logic ad-hoc inside controllers. But you can create a universal solution. One way to achieve this is by defining a helper method in the base model class. In Rails, the base model class is typically ApplicationRecord. By doing this, you can ensure its availability across all models in the application. See the following example how to do this:

class ApplicationRecord < ActiveRecord::Base
  primary_abstract_class

  # Defines a predicate method and a setter method for the specified attributes.
  #
  # To use this code in the User model, call the following:
  # timestamp_as_boolean :muted
  #
  # In this scenario, it assumes that a "muted_at" column is defined in the table.
  # After calling the code, the following methods are defined on the User model:
  # muted? - checks if a user is muted
  # muted=(value) - sets the muted_at timestamp if the value is equivalent to boolean "true"
  def self.timestamp_as_boolean(*fields)
    fields.each do |field|
      timestamp_field = "#{field}_at"
      predicate = "#{field}?"
      define_method(predicate) do
        public_send(timestamp_field).present?
      end
      define_method("#{field}=") do |value|
        new_value = ActiveModel::Type::Boolean.new.cast(value) ? Time.current : nil
        public_send("#{timestamp_field}=", new_value) if new_value.nil? || !public_send(predicate)
      end
    end
  end
end

We have now implemented a universal solution using some meta-programming techniques in Ruby. Here’s how you can use it. Let’s take the example of the User model and convert the muted_at column into a boolean attribute using the following approach:

class User < ApplicationRecord
  timestamp_as_boolean :muted
end

To utilize this solution in an ERB template on the user interface, follow these steps:

<%= form.check_box :muted, { checked: @user.muted? } %>

Let’s verify its functionality:

> user = User.last
> user.muted? # => true
> user.update!(muted: false)
> user.muted? # => false
> user.update!(muted: true) # => true
> user.muted? # => true
> user.muted_at # => Thu, 15 Jun 2023 15:16:54.582848000 UTC +00:00
> user.update!(muted: true) # => true
> user.muted_at # => Thu, 15 Jun 2023 15:16:54.582848000 UTC +00:00

It is important to note that after the second assignment of muted = true, the timestamp did not change. This behavior is expected and logical because the value itself was not modified. The purpose of tracking the timestamp is to capture the moment when a change in the value occurs. In this case, since the value remains the same, there is no need for the timestamp to be updated. This behavior aligns with the intended functionality. It ensures that the timestamp accurately reflects when a change in the value of the attribute occurs.

Conclusion

Use a timestamp type for columns in the database. Create a universal solution to transform boolean values. As a result, enhance the functionality and debugging capabilities of our models. The timestamp type allows us to track the values change time. That can aid in identifying and troubleshooting issues, particularly in production environments. Avoid code duplication and ensure consistent behavior across models with a universal solution. Metaprogramming can help with that. These improvements contribute to a more robust and efficient application.

Happy coding!

Use timestamp for predicates in Rails was originally published by Andrei Kaleshka at Build Ruby On Rails applications with us on June 15, 2023.

Optimize Rails app performance with ChatGPT

Andrei Kaleshka — Wed, 07 Jun 2023 04:00:00 +0000

The previous article Make your Ruby on Rails app 80x faster with SQL shows how SQL knowledge can help to optimize your Rails application performance. We discussed it within Belarus User Group community. Not everyone agreed with the point and didn’t find SQL knowledge a good asset for investment. That’s something expected. There is no revelation here. But that meeting had something that everyone was impressed with. We experimented and found out how ChatGPT is good with code optimization. We saw how it transforms Ruby code into performant SQL. The results were great. Check that out within this article.

ChatGPT request to transform Ruby code into SQL

As before, we are going to use this experimental repository.

We took the following code snippet, the slowest and original version, and asked to improve its performance by rewriting it into SQL:

# the original code

projects_full = []
projects_empty = []
Service.where(user: user, status: "approved", active: true).order(category_id: :asc).each do |service|
  ratings_average = 0
  ratings_count = 0
  ratings_total = 0
  Rating.where(reviewee: user).each do |rating|
    project = Project.find(rating.project_id)
    if project.category_id == service.category_id
      ratings_count += 1
      ratings_total += rating.rating
    end
  end
  ratings_average = (ratings_total / ratings_count.to_f).round(1).to_s if ratings_count != 0 && ratings_total != 0
  completed_projects_count = Project.where(vendor: user, status: "Complete", category_id: service.category_id).size
  service_hash = {
    category_id: service.category_id,
    category_name: Category.find(Category.find(service.category_id).parent_id).name,
    subcategory_name: Category.find(service.category_id).name,
    completed_projects_count: completed_projects_count,
    ratings_count: ratings_count,
    ratings_average: ratings_average,
  }
  resolved_hash = service_hash.transform_keys{ |k| k.to_s.camelize(:lower) }
  if completed_projects_count > 0
    projects_full.push(resolved_hash)
  else
    projects_empty.push(resolved_hash)
  end
end
projects_full.sort_by!{ |k| -k["completedProjectsCount"] }
projects_full.push(*projects_empty)

projects_full

And we got the following response from ChatGPT:

SELECT
  c1.id AS category_id,
  c2.name AS category_name,
  c1.name AS subcategory_name,
  COUNT(DISTINCT p.id) FILTER (WHERE p.status = 'Complete') AS completed_projects_count,
  COUNT(r.id) AS ratings_count,
  COALESCE(AVG(r.rating), 0) AS ratings_average
FROM
  services s
  JOIN categories c1 ON s.category_id = c1.id
  JOIN categories c2 ON c1.parent_id = c2.id
  LEFT JOIN projects p ON s.user_id = p.vendor_id AND p.category_id = c1.id
  LEFT JOIN ratings r ON s.user_id = r.reviewee_id AND p.id = r.project_id
WHERE
  s.user_id = :user_id AND s.status = 'approved' AND s.active = true
GROUP BY
  c1.id, c2.name, c1.name
ORDER BY
  completed_projects_count DESC, c1.id ASC;

And these are the performance measurement results:

Warming up --------------------------------------
                ruby 1.000 i/100ms
                 sql 54.000 i/100ms
             sql_gpt 59.000 i/100ms
Calculating -------------------------------------
                ruby 6.846 (±14.6%) i/s - 34.000 in 5.000338s
                 sql 540.320 (± 8.5%) i/s - 2.700k in 5.038711s
             sql_gpt 609.335 (± 6.9%) i/s - 3.068k in 5.059364s

Comparison:
             sql_gpt: 609.3 i/s
                 sql: 540.3 i/s - same-ish: difference falls within error
                ruby: 6.8 i/s - 89.01x slower

We also checked that the result of the query service is the same for both calls. That means there are no bugs in the produced SQL by the AI.

The ChatGPT result is the fastest one. At the same time, no business logic was defective. That’s a very good result!

Should one use ChatGPT to optimize code performance or not

While the AI results are good, it’s still doubtful who and how to use the tool. Not seasoned developers might don’t understand where the bottleneck is. They would struggle with finding a correct question for ChatGPT. They would not understand how to fix the generated code if it has bugs. Some time later I tried to repeat the experiment and know the machine produced a very different SQL that has bugs inside:

-- Get average ratings and count for each service
WITH rating_summary AS (
  SELECT p.category_id, COUNT(r.id) AS ratings_count, COALESCE(AVG(r.rating), 0) AS ratings_average
  FROM ratings r
  INNER JOIN projects p ON p.id = r.project_id
  WHERE r.reviewee = <user_id>
  GROUP BY p.category_id
),

-- Get completed projects count for each service
completed_projects AS (
  SELECT category_id, COUNT(*) AS completed_projects_count
  FROM projects
  WHERE vendor = <user_id> AND status = 'Complete'
  GROUP BY category_id
)

-- Combine the results
SELECT
  s.category_id,
  c.name AS category_name,
  p.name AS subcategory_name,
  COALESCE(cp.completed_projects_count, 0) AS completed_projects_count,
  COALESCE(rs.ratings_count, 0) AS ratings_count,
  ROUND(COALESCE(rs.ratings_average, 0), 1) AS ratings_average
FROM services s
JOIN categories c ON c.id = s.category_id
JOIN categories p ON p.id = c.parent_id
LEFT JOIN rating_summary rs ON rs.category_id = s.category_id
LEFT JOIN completed_projects cp ON cp.category_id = s.category_id
WHERE s.user = <user_id> AND s.status = 'approved' AND s.active = true
ORDER BY completed_projects_count DESC;

While the AI results are good, it’s still doubtful who and how to use the tool. Not seasoned developers might don’t understand where the bottleneck is. They would struggle with finding a correct question for ChatGPT. They would not understand how to fix the generated code if it has bugs. Later, I tried to repeat the experiment. This time the machine produced a very different SQL:

Warming up --------------------------------------
                ruby 1.000 i/100ms
                 sql 52.000 i/100ms
             sql_gpt 40.000 i/100ms
         sql_gpt_new 38.000 i/100ms
Calculating -------------------------------------
                ruby 5.754 (±17.4%) i/s - 28.000 in 5.039290s
                 sql 433.067 (±20.3%) i/s - 2.080k in 5.029710s
             sql_gpt 561.018 (±14.4%) i/s - 2.760k in 5.044415s
         sql_gpt_new 514.242 (±19.4%) i/s - 2.432k in 5.013023s

Comparison:
             sql_gpt: 561.0 i/s
         sql_gpt_new: 514.2 i/s - same-ish: difference falls within error
                 sql: 433.1 i/s - same-ish: difference falls within error
                ruby: 5.8 i/s - 97.50x slower

However, it’s still faster than the original rewrite to SQL.

Conclusion

ChatGPT is very good at code refactoring and transforming Ruby code into SQL. Even though the results are impressive it still needs an expert communicating with the tool to form a correct question, check the produced results, and fix minor issues. The results can be a good start in code refactoring and optimization.

That was a great meeting. We had a memorable time together. Thanks to everyone who participated. I am looking forward to our weekly calls and I invite everyone to join the community.

Happy coding!

Optimize Rails app performance with ChatGPT was originally published by Andrei Kaleshka at Build Ruby On Rails applications with us on June 07, 2023.

How to write SQL query in Ruby On Rails

Andrei Kaleshka — Thu, 25 May 2023 04:00:00 +0000

ActiveRecord is a central component of a Rails application. It’s a wrapper for a database used behind the scene. And the database is another essential thing in almost any web application. Understanding how these two things go along is crucial to develop a fast, performant, and correct project. Missing knowledge here leads to over-engineered solutions, performance issues, and bad user experience.

In this article, you will see how to write SQL and use that knowledge in a Rails application.

How to see which SQL generated by ActiveRecord

The post uses this app to play with examples.

To see which SQL is generated by queries to models in a common Rails app, use .to_sql method. We can check that in the Rails console (run rails console and then write your experimental Ruby code there):

> Project.all.to_sql
=> "SELECT \"projects\".* FROM \"projects\""

Note, it works for any query, for example, when it uses conditions or joins:

> Project.joins(:ratings).to_sql
=> "SELECT \"projects\".* FROM \"projects\" INNER JOIN \"ratings\" ON \"ratings\".\"project_id\" = \"projects\".\"id\""

> Project.joins(:ratings).where(ratings: {rating: 5}).to_sql
=> "SELECT \"projects\".* FROM \"projects\" INNER JOIN \"ratings\" ON \"ratings\".\"project_id\" = \"projects\".\"id\" WHERE \"ratings\".\"rating\" = 5"

The query can be very complex. It can be constructed from different parts of the app using scopes, and separate classes implementing some patterns like Query Object, etc. In the end, the generated SQL query defines a related feature correctness. Hence, use this trick while debugging code to find a bug in the generated query.

How to write raw SQL in a Rails application

ActiveRecord comes with a handy interface and abstraction to write raw SQL queries. Why should one do that? Well, there can be many reasons for that:

Improving query performance.
Lack of knowledge in ActiveRecord.
Lack of functionalities in ActiveRecord.
Avoiding Arel queries (this thing is used behind the scene of ActiveRecord to generate SQL queries out of the Ruby constructions).

Whatever the reason, it’s reduced to just one desire - demand of controlling SQL queries. If you are one of this kind of person having this desire, use the following approach:

> ActiveRecord::Base.connection.execute("select * from projects").to_a
=> [
{"id"=>1,
  "description"=>"it's a test project",
  "user_id"=>3,
  "created_at"=>2022-09-29 20:10:46.751835 UTC,
  "updated_at"=>2023-01-17 23:22:42.275697 UTC,
  "status"=>"Open",
  "category"=>"Website and landing page design",
  "experience"=>"New to Widefix — under one year",
  "existing_website"=>"Yes, I already have a website set up and live for customers",
  "existing_website_platform"=>"Widefix",
  "category_tasks"=>nil,
  "delivery_timeline"=>"11-21 days",
  ...
}, ...]

Also, this API allows to write SQL queries inside migrations. It’s even easier in migrations as the execute method is available there.

To construct correct SQL you would propaply need a db console. It’s the fastest way to write pure and correct SQL related to your Rails app DB. Use rails db command to open DB console and do your experiments with SQL.

How to measure SQL query performance in Rails

To understand why SQL query is slow you can use explain analyze built-in functionality that every SQL DB has:

Project.joins(:ratings).where(ratings: {rating: 5}).explain
  Project Load (2.1ms) SELECT "projects".* FROM "projects" INNER JOIN "ratings" ON "ratings"."project_id" = "projects"."id" WHERE "ratings"."rating" = $1 [["rating", 5]]
=>
EXPLAIN for: SELECT "projects".* FROM "projects" INNER JOIN "ratings" ON "ratings"."project_id" = "projects"."id" WHERE "ratings"."rating" = $1 [["rating", 5]]
                                      QUERY PLAN
---------------------------------------------------------------------------------------
 Nested Loop (cost=0.14..9.63 rows=1 width=1694)
   -> Seq Scan on ratings (cost=0.00..1.29 rows=1 width=8)
         Filter: (rating = 5)
   -> Index Scan using projects_pkey on projects (cost=0.14..8.16 rows=1 width=1694)
         Index Cond: (id = ratings.project_id)
(5 rows)

If the query was slow, we had to pay attention to the Seq Scan on ratings. That means the it didn’t use index to filter ratings by the rating = 5 condition. We can add a corresponding index to fix the issue.

Another problem that is frequently related to performance penalties is N+1 queries. Why many solutions can help with that, it’s pretty easy to detect the issues by looking into logs. Many repeated lines with the following format indicate that issue:

  Category Load (0.9ms) SELECT "categories".* FROM "categories" WHERE "categories"."id" = $1 LIMIT $2 [["id", 3], ["LIMIT", 1]]
  ↳ app/queries/services_stats_query.rb:48:in `block in call_ruby'
  Category Load (0.9ms) SELECT "categories".* FROM "categories" WHERE "categories"."id" = $1 LIMIT $2 [["id", 1], ["LIMIT", 1]]
  ↳ app/queries/services_stats_query.rb:48:in `block in call_ruby'
  Category Load (0.8ms) SELECT "categories".* FROM "categories" WHERE "categories"."id" = $1 LIMIT $2 [["id", 3], ["LIMIT", 1]]
  ↳ app/queries/services_stats_query.rb:49:in `block in call_ruby'
  Rating Load (0.9ms) SELECT "ratings".* FROM "ratings" WHERE "ratings"."reviewee_id" = $1 [["reviewee_id", 10]]
  ↳ app/queries/services_stats_query.rb:37:in `block in call_ruby'
  Project Load (1.0ms) SELECT "projects".* FROM "projects" WHERE "projects"."id" = $1 LIMIT $2 [["id", 27], ["LIMIT", 1]]
  ↳ app/queries/services_stats_query.rb:38:in `block (2 levels) in call_ruby'
  Project Load (0.9ms) SELECT "projects".* FROM "projects" WHERE "projects"."id" = $1 LIMIT $2 [["id", 39], ["LIMIT", 1]]
  ↳ app/queries/services_stats_query.rb:38:in `block (2 levels) in call_ruby'
  Project Load (0.9ms) SELECT "projects".* FROM "projects" WHERE "projects"."id" = $1 LIMIT $2 [["id", 86], ["LIMIT", 1]]

Usually, eager loading techniques fix this issue. But that’s a topic for a separate article.

Play with SQL in Rails apps with confidence

Now you know all the essential tricks to work with SQL in a Rails application.

Moving further, you can read the following articles that expand the related knowledge:

These articles show how to use SQL within Rails applications efficiently on practical examples. They also explain some useful but not very popular in the Rails community SQL tricks.

Happy coding!

How to write SQL query in Ruby On Rails was originally published by Andrei Kaleshka at WideFix Blog on May 25, 2023.

Improve NextJS application performance

Andrei Kaleshka — Wed, 24 May 2023 04:00:00 +0000

To improve the performance of your app built on React and Next.js using TypeScript, you can follow several strategies.

Code Optimization

Minimize unnecessary re-renders by using React.memo, useMemo, and useCallback hooks to memoize components and functions that don’t depend on changing data. Avoid unnecessary state updates and re-renders by optimizing the usage of useState and useEffect hooks.

Using TypeScript’s type checking can catch potential errors and improve code quality.

Bundle Size Optimization

Split your code into smaller chunks using dynamic imports and code splitting. This allows you to load only the necessary code for each page or component, reducing the initial bundle size and improving load times. Analyze your bundle using tools like Webpack Bundle Analyzer to identify and eliminate any unnecessary dependencies or large libraries. Compress and optimize your assets (images, CSS, etc.) to reduce their size and improve load times. Next.js has built-in Image component that allows to compress images on the fly. It also converts then to modern image formats that are lightweight.

Server-Side Rendering (SSR) and Static Site Generation (SSG)

Utilize Next.js’s built-in features for server-side rendering and static site generation to pre-render pages and improve initial load times. This reduces the amount of work required by the client’s browser. Identify pages that don’t require real-time data and generate them statically using Next.js’s getStaticProps or getStaticPaths functions. This eliminates the need for client-side rendering and improves performance.

Performance Monitoring and Optimization

Use performance monitoring tools like Lighthouse, WebPageTest, or Chrome DevTools to identify performance bottlenecks, such as slow-loading components, large files, or long JavaScript execution times. Optimize critical rendering paths by prioritizing the loading of essential content and deferring non-critical scripts or styles. Implement lazy loading for images and other non-essential assets to load them only when they become visible in the viewport. Implement caching mechanisms for API requests or frequently accessed data to reduce server load and improve response times.

SSR Caching and Incremental Static Regeneration

Utilize Next.js’s caching capabilities to cache rendered pages on the server-side and serve them directly for subsequent requests, reducing the need for re-rendering. Implement incremental static regeneration for pages that require dynamic data. This allows you to re-generate and update specific pages at predefined intervals, ensuring the content stays fresh while reducing the load on the server.

Performance Testing and Optimization Iteration

Regularly test your app’s performance using tools like Lighthouse or Chrome DevTools to measure metrics like First Contentful Paint (FCP), Time to Interactive (TTI), and Total Blocking Time (TBT). Set performance budgets and strive to stay within them. Continuously analyze and optimize critical paths, reducing the time required for JavaScript execution, network requests, and rendering. Remember to always profile and benchmark your optimizations to ensure they have a positive impact on your app’s performance.

Improve NextJS application performance was originally published by Andrei Kaleshka at WideFix Blog on May 24, 2023.

Make your Ruby on Rails app 80x faster with SQL

Andrei Kaleshka — Thu, 30 Mar 2023 04:00:00 +0000

SQL can improve the performance and efficiency of your Ruby on Rails application. No need for heavy technologies. No need for switching to another programming language or framework.

SQL is a powerful tool that can take your Ruby on Rails expertise to the next level. Nowadays many technologies claim to replace SQL or deem Ruby on Rails as “too slow”. But it remains a popular and efficient choice for many web projects. This article will explore the importance of SQL for Ruby on Rails experts. It will show why it’s a must-have skill in today’s development landscape.

Why Ruby on Rails can be slow

Imagine the following class that’s used in a Rails controller to feed data to a page in your application. This class may look familiar to you. I’ve encountered similar classes in real-world applications:

class ServicesStatsQuery
  attr_accessor :user

  def initialize(user)
    self.user = user
  end

  def call
    projects_full = []
    projects_empty = []
    Service.where(user: user, status: "approved", active: true)
      .order(category_id: :asc).each do |service|
      ratings_average = 0
      ratings_count = 0
      ratings_total = 0
      Rating.where(reviewee: user).each do |rating|
        project = Project.find(rating.project_id)
        if project.category_id == service.category_id
          ratings_count += 1
          ratings_total += rating.rating
        end
      end
      ratings_average = (ratings_total / ratings_count.to_f).round(1).to_s if ratings_count != 0 && ratings_total != 0
      completed_projects_count =
        Project.where(vendor: user, status: "Complete", category_id: service.category_id).size
      service_hash = {
        category_id: service.category_id,
        category_name: Category.find(Category.find(service.category_id).parent_id).name,
        subcategory_name: Category.find(service.category_id).name,
        completed_projects_count: completed_projects_count,
        ratings_count: ratings_count,
        ratings_average: ratings_average,
      }
      resolved_hash = service_hash.transform_keys{ |k| k.to_s.camelize(:lower) }
      if completed_projects_count > 0
        projects_full.push(resolved_hash)
      else
        projects_empty.push(resolved_hash)
      end
    end
    projects_full.sort_by!{ |k| -k["completedProjectsCount"] }
    projects_full.push(*projects_empty)

    projects_full
  end
end

Your application is running in production and serving the needs of hundreds of users. You’ve noticed that the related page is becoming slower and slower with each passing day. This can be a frustrating problem to solve. It’s natural to feel tempted to consider drastic measures. You can think of switching to a faster programming language. Or you might plan to use heavy technology.

Before taking such drastic steps take a step back and understand the root cause of the issue.

There are a few things that could be contributing to the slowness of this code:

N+1 queries: The code is making a separate database query for each rating. Then it gets the associated project. That can become slow if there are a large number of ratings.
Nested each loops: The code is using this method to iterate over the Service. For each service in the nested loop, it iterates through all related Rating records. That can also be slow if there are many records. Instead, it would be better to use SQL joins to fetch all the data in a single query.
Redundant queries: The code is calling Category.find many times. Instead, better to fetch all the category data using a single query. And then use it to populate the service_hash object.

Optimizing this code will need a combination of SQL query optimization. That should cut unnecessary database queries and improve the performance of the code.

How to analyze slow Ruby On Rails code

There are plenty of tools out there, like bullet, benchmark-ips, or any others.

But before taking these radical steps use what you already have. Open logs (use command tail -f log/development.log for local). Observe what happens there while you open the slow page in your browser. If you spot many repeating lines like this one you have an N+1 problem:

00:03:22 web.1 | Rating Load (1.6ms) SELECT "ratings".* FROM "ratings" WHERE "ratings"."user_id" = $1 [["user_id", 15]]

Pay attention to the phrase “Rating Load” as it serves as an indicator. If you notice “CACHE Rating Load”, there’s no need to worry. The object is already in memory, retrieved from there without accessing the database. This suggests that eager loading is already working.

Another tool is to visually inspect the code. You can feel discomfort and uncertainty when trying to understand how the code pieces relate to each other. Yet, this is not related to performance. The most crucial aspect is that the code contains a nested loop, which indicates that its complexity is squared. As more objects are iterated, a squared function grows rapidly:

Sorry for getting into the math, but it’s important to understand that your code should ideally grow linearly or at least logarithmically. This is the best way to ensure that your code’s performance remains optimal for many years to come.

If you are unsure about what all of that means, now is a good time to start learning about the theory of algorithms and data structures.

Use SQL to improve Rails application performance

To fetch all the required data in a single query while performing the necessary aggregation, sorting, and grouping, consider using this SQL query:

select
  s.category_id as "categoryId",
  pc.name as "categoryName",
  c.name as "subcategoryName",
  count(p.id) as "completedProjectsCount",
  r.count as "ratingsCount",
  r.avg as "ratingsAverage"
  from services s
  join categories c on c.id = s.category_id
  join categories pc on pc.id = c.parent_id
  left join lateral (
    select count(r.id), cast(round(coalesce(avg(r.rating), 0), 1) as text) as avg from ratings r
    join projects rp on rp.id = r.project_id and s.category_id = rp.category_id
    where r.reviewee_id = :user_id
  ) r on true
  left join projects p on p.vendor_id = :user_id and p.status = 'Complete' and p.category_id = s.category_id
  where s.status = 'approved' and s.active and s.user_id = :user_id
  group by s.category_id, pc.name, c.name, r.count, r.avg
  order by count(p.id) desc, s.category_id asc

One way to test this query is to replace the placeholder :user_id with an actual user id. Then, we can open the DB console and paste the query to see if it produces the expected results.

Make sure the query is successful and returns the desired results. Then we can replace the slow Ruby code with this efficient SQL query:

class ServicesStatsQuery
  SQL = <<~SQL
    select
      s.category_id as "categoryId",
      pc.name as "categoryName",
      c.name as "subcategoryName",
      count(p.id) as "completedProjectsCount",
      r.count as "ratingsCount",
      r.avg as "ratingsAverage"
      from services s
      join categories c on c.id = s.category_id
      join categories pc on pc.id = c.parent_id
      left join lateral (
        select count(r.id), cast(round(coalesce(avg(r.rating), 0), 1) as text) as avg from ratings r
        join projects rp on rp.id = r.project_id and s.category_id = rp.category_id
        where r.reviewee_id = :user_id
      ) r on true
      left join projects p on p.vendor_id = :user_id and p.status = 'Complete' and p.category_id = s.category_id
      where s.status = 'approved' and s.active and s.user_id = :user_id
      group by s.category_id, pc.name, c.name, r.count, r.avg
      order by count(p.id) desc, s.category_id asc
  SQL

  attr_accessor :user

  def initialize(user)
    self.user = user
  end

  def call
    sql = ActiveRecord::Base.sanitize_sql_array([SQL, user_id: user.id])
    ActiveRecord::Base.connection.execute(sql).to_a
  end
end

This SQL query can provide fast results for most Ruby On Rails applications. Although, it assumes that the DB has appropriate indexes defined.

Native SQL is much faster than the pure Ruby on Rails code

It’s time to perform measurements. First, install the benchmark-ips gem. Then you can prepare the following code and save it in a file. For example, t.rb, in the root directory of your Rails application:

require 'benchmark/ips'

user = User.find(10)
service = ServicesStatsQuery.new(user)

Benchmark.ips do |x|
  x.report("ruby") { service.call_ruby }
  x.report("sql") { service.call_sql }
  x.compare!
end

We change the ServicesStatsQuery a bit to have the two methods defined call_ruby and call_sql instead of one method call:

class ServicesStatsQuery
  SQL = <<~SQL
    select
      s.category_id as "categoryId",
      pc.name as "categoryName",
      c.name as "subcategoryName",
      count(p.id) as "completedProjectsCount",
      r.count as "ratingsCount",
      r.avg as "ratingsAverage"
      from services s
      join categories c on c.id = s.category_id
      join categories pc on pc.id = c.parent_id
      left join lateral (
        select count(r.id), cast(round(coalesce(avg(r.rating), 0), 1) as text) as avg from ratings r
        join projects rp on rp.id = r.project_id and s.category_id = rp.category_id
        where r.reviewee_id = :user_id
      ) r on true
      left join projects p on p.vendor_id = :user_id and p.status = 'Complete' and p.category_id = s.category_id
      where s.status = 'approved' and s.active and s.user_id = :user_id
      group by s.category_id, pc.name, c.name, r.count, r.avg
      order by count(p.id) desc, s.category_id asc
  SQL

  attr_accessor :user

  def initialize(user)
    self.user = user
  end

  def call_ruby
    projects_full = []
    projects_empty = []
    Service.where(user: user, status: "approved", active: true).order(category_id: :asc).each do |service|
      ratings_average = 0
      ratings_count = 0
      ratings_total = 0
      Rating.where(reviewee: user).each do |rating|
        project = Project.find(rating.project_id)
        if project.category_id == service.category_id
          ratings_count += 1
          ratings_total += rating.rating
        end
      end
      ratings_average = (ratings_total / ratings_count.to_f).round(1).to_s if ratings_count != 0 && ratings_total != 0
      completed_projects_count = Project.where(vendor: user, status: "Complete", category_id: service.category_id).size
      service_hash = {
        category_id: service.category_id,
        category_name: Category.find(Category.find(service.category_id).parent_id).name,
        subcategory_name: Category.find(service.category_id).name,
        completed_projects_count: completed_projects_count,
        ratings_count: ratings_count,
        ratings_average: ratings_average,
      }
      resolved_hash = service_hash.transform_keys{ |k| k.to_s.camelize(:lower) }
      if completed_projects_count > 0
        projects_full.push(resolved_hash)
      else
        projects_empty.push(resolved_hash)
      end
    end
    projects_full.sort_by!{ |k| -k["completedProjectsCount"] }
    projects_full.push(*projects_empty)

    projects_full
  end

  def call_sql
    sql = ActiveRecord::Base.sanitize_sql_array([SQL, user_id: user.id])
    ActiveRecord::Base.connection.execute(sql).to_a
  end
end

Run it with the command: rails runner t.rb and see the results:

Warming up --------------------------------------
                ruby 1.000 i/100ms
                 sql 48.000 i/100ms
Calculating -------------------------------------
                ruby 5.658 (± 0.0%) i/s - 29.000 in 5.147460s
                 sql 451.144 (± 8.6%) i/s - 2.256k in 5.042193s

Comparison:
                 sql: 451.1 i/s
                ruby: 5.7 i/s - 79.74x slower

Performance boost of almost 80 times! The slow-loading page that took over 30 seconds now loads within 300ms. This significant improvement is not the programming language or framework merit.

Demo: Faster SQL in Ruby on Rails App

I have developed a demo application using both Ruby and SQL versions and made it available on GitHub as an open source project. This means that you have the opportunity to experiment with the code and run benchmarks on your own.

Determine whether to use the measurement tools or not

You can analyze performance issues and measure optimization results without fancy tools. benchmark-ips is for demonstration purposes. The best tools are reading the code, and understanding it. Then analyze its complexity. Finally, use SQL knowledge. These tools are enough. Nothing can replace them. Well, except in the distant future, ChatGPT may offer more help.

There are always plenty of built-in tools by your hand. Learn how to use them. For example, moving further, the written SQL can be slow. You will have to check why. Use explain analyze for that. This is a built-in tool in all modern SQL databases.

Why Ruby on Rails expert should know SQL

Ruby on Rails is a powerful web application framework. It provides developers with high-level abstractions and conventions. That allows to build web applications quickly and efficiently. But, despite its powerful abstractions, Ruby on Rails experts need to know SQL.

SQL allows Ruby on Rails experts to write efficient and optimized database queries. That can improve the performance of a web application. By understanding SQL, a Ruby on Rails expert can design the database schema and data access layer. If that’s done well, the application maximizes performance, scalability, and maintainability.

SQL is a widely-used and powerful language. People use it beyond interacting with the database. Ruby on Rails experts use SQL to perform complex calculations and data transformations. They can generate reports that are challenging to achieve using Ruby code alone.

SQL allows Ruby on Rails experts to have more control over their web applications. They write more efficient and optimized code. They make better data-driven decisions.

How can a Ruby on Rails expert learn SQL

Nowadays, there are plenty of courses and books available on the subject. So it’s hard to recommend specific resources. You can choose whatever you like and suits your learning style.

But, if you are like me and prefer hands-on learning, you might find the following resource helpful - https://pgexercises.com/.

You might also find this article I wrote previously interesting - Financial plan on PostgreSQL.

Make your Ruby on Rails app 80x faster with SQL was originally published by Andrei Kaleshka at WideFix Blog on March 30, 2023.