DEV Community: rbglod

How to monitor your app's health

rbglod — Thu, 18 Dec 2025 14:36:06 +0000

Monitoring in web apps is crucial element in terms of maintaining stability and ability to quickly react to critical incidents.
When an incident happens, you or even worse - your customer - finds a bug, you drop your feature work and hop on a debugging train. Having metrics and monitoring in place is incredibly helpful to trace where the issue lies, what's the impact and how it can be fixed quickly.

In this blog post I'd like to share some ideas around health metrics that can be added to crucial parts of the app to increase visibilty of how our code works, what's working as expected and what code paths fail most often. I'm not gonna dive into tools like Airbrake or Sentry, which are used to catch errors and report them. I'll explore what we can implement in our code to have as much useful information in our logs (or tools like Datadog) instead.

Health metrics and where to put them

Start with finding crucial parts of the app without which the project is broken. For me, the first thing that comes to my mind is payment processing. If we fail to process a payment, business is not making money, which means that we're losing it. We pay for hosting the app, but fail to satisfy users needs, which may lead to users abandoning our app - reducing our income, increasing our costs.

attempt, success, failure

So we have our most vulnerable area - payment processing. Implementing health metrics is extremely easy, but also extremely useful. Let's have a look on this example class.

class PaymentProcessor
  ...
  def perform(amount, user)
    payment = create_payment(amount, user)
    payment.process_payment
    payment.mark_as_finished
  rescue Payments::Errors::PaymentFailed => e
    payment.mark_as_failed(e)
  ensure 
    set_result(payment.status)
  end
  ...
end

We can define three phases of what's going on here.

A payment is being created, we attempt to process a payment.
If payment processing succeeds, we mark it as finished and return payment's status.
If payment processing fails, we mark it as failed, save error message and return payment's status.

This gives us an idea of what health metrics we can implement here. And this rule is rather universal. We attempt to perform an action, then we either receive success or failure from the operation.

Here's how the metrics may look like in the most simple way.

class PaymentProcessor
  ...
  def perform(amount, user)
    metric_recorder.record_attempt
    payment = create_payment(amount, user)
    payment.process_payment
    payment.mark_as_finished
    metric_recorder.record_success
  rescue Payments::Errors::PaymentFailed => e
    payment.mark_as_failed(e)
    metric_recorder.record_failure(e.error_code)
  ensure 
    set_result(payment.status)
  end

  private

  def metric_recorder
    @metric_recorder ||= MetricRecorder.new(domain: 'payment_processing')
  end
end

This would already give us some insight on how much payment processing attempts we had, how many of them succeeded and how many failed.

attempts = success + failed

If the numbers don't add up, it means we have some issue in-between the metrics being recorded. Maybe we get an error in payment.mark_as_finished that is not handled, hence we don't get ending metric properly? That's also valuable information for debuggin purposes. We can see how far the code execution went.

Wrapper

This was the simpliest version, but it pollutes the code, and we need to remember about adding it each time we implement new service. Ruby let's us make it much more cleaner way and makes it possible to reuse the code. We can add a method to our MetricRecorder module that will accept a block and handle the rest.

module MetricRecorder
  def record_health(&block)
    record_attempt
    block.call
    record_success
  rescue StandardError => e
    record_failure(e)
    raise e # we want to re-raise error to handle it outside of the recorder
  end
end

And here's how record_health wrapper method can be used in the PaymentProcessor.

class PaymentProcessor
  include MetricRecorder

  def perform(amount, user)
    record_health do
      payment = create_payment(amount, user)
      payment.process_payment
      payment.mark_as_finished
    rescue Payments::Errors::PaymentFailed => e
      payment.mark_as_failed(e)
      raise e # we re-raise the error to propagate it to recorder and further
    ensure
      set_result(payment.status)
    end
  end
end

This makes our class clean again, reduces need to think where we should put metric loggers, and moves metric recorders outside of this class, making it reusable in the whole project. Once we want to add some extra data to metrics, we can update just MetricRecorder instead of all classes that use record_... methods.

The recorder module

Okay, we know how this should look from the code execution perspective, but what the record_attempt, record_success and record_failure should actually do?

Metrics are designed to just give an idea of the traffic in the app. If we want to have more detailed data, we should incorporate loggers probably.

If we're using Datadog, then we'd probably just increment attempt, success, failure metrics, with adding error_code to failure probably. It's due to costs and metrics design in DD - we can only send defined set of metrics. We can't propagate IDs, amount, or some specific user data in those metrics, as those are infinite.

module MetricRecorder
  def record_health(metric_name, &block)
    record_attempt(metric_name)
    block.call
    record_success(metric_name)
  rescue StandardError => e
    record_failure(metric_name, e)
    raise e
  end

  private

  def record_attempt(metric_name)
    DatadogService.increment(metric_name, 'attempt')
  end

  def record_success(metric_name)
    DatadogService.increment(metric_name, 'success')
  end

  def record_failure(metric_name, e)
    DatadogService.increment(metric_name, 'failure', error_code: e.error_code)
  end
end

To get more data and really monitor our app, we could also write a logger module.

module LogsRecorder
  def record_log(method_name, &block)
    log(method_name, { action_type: 'attempt' })
    result = block.call
    log(method_name, { action_type: 'success', result: result })
    result
  rescue StandardError => e
    log(method_name, { action_type: 'failure', result: { error_class: e.class.name.demodulize, error_message: e.message } })
    raise e
  end

  private

  def log(method_name, additional_tags)
    Rails.logger.info(
      class_name: self.class.name.demodulize.underscore,
      method_name: method_name,
      additional_tags: additional_tags
    )
  end
end

And here's how we could incorporate both modules - having metrics and logs in place. This would let us quickly monitor metrics from Datadog web dashboard for example, and if something feels off - we can jump into detailed logs, grep for action_type: 'failure' and see what went wrong.

class PaymentProcessor
  include MetricRecorder
  include LogsRecorder

  def perform(amount, user)
    record_health('payment_processing') do
      record_log('perform') do
        payment = create_payment(amount, user)
        payment.process_payment
        payment.mark_as_finished
      rescue Payments::Errors::PaymentFailed => e
        payment.mark_as_failed(e)
        raise e
      ensure
        set_result(payment.status)
      end
    end
  end
end

Untangling the Rails Monolith - quick look at the code

rbglod — Thu, 11 Dec 2025 22:05:47 +0000

Last time I wrote about the data separation at the database level. As we already know each component should work as an (almost) independent service. How can that be achevied at the codebase level? What to do when for example in Accounting we need address data for an Employee? How can we get the Organization subscription plan if we're in the Checkout component?

Each of those components should work as a separate app ideally. If we imagine them as a standalone apps, it might be easier to design future code and not couple services with each other.

As in the previous blog post, let's focus on some kind-of real life example.

Marketplace

Let's say we have a simple app in which a User can register an account, select given subscription plan, put ads with their Goods and also buy Goods from other users. Depending on the subscription type, we offer them better shipping options, discounts, etc.

We can describe few components in this scenario.

app/
|- components/
  |- users/
  |- marketplace/
  |- payments/
  |- subscriptions/

Each of the components has their own controllers/, models/, services/, etc. In more complex apps we might see infrastructure/, domain/, repositories/ and others, but let's stick to simple example.

In ideal world, each of the component lives on it's own - Users know only about the Users model (and other related to Users), they check if User is logged in, update their passwords, account data, etc.

But at some point user hits a Marketplace and want to order something. In Marketplace component we want to know where the order should go, who is the recipient and what possible discounts we should offer.

Instead of doing something like this

# app/components/marketplace/controllers/orders_controller.rb

def create
  @order = Marketplace::Models::Order.new(
    user_id: current_user.id,
    shipping_address: current_user.shipping_address,
    offering: current_user.subscription_plan,
    discounts: current_user.discounts,
    items: order_params[:items_ids]
  )
  ...
end

We don't want to couple Marketplace with Users. We definitely shouldn't use user relations here, as they might change in future and this code would break.
The default scope of user.discounts might change and it'd be hard to debug, why suddenly the behavior of our endpoint changed.

Instead we should use Facades or APIs or any other approach that will let us put the query to another component in one place and make sure we always get the same result.

What I mean through that is the users/ domain could expose their public API for other domains to fetch users' data.

# app/components/users/public/api.rb

class << self
  UserResponse = Data.define(:shipping_address, :subscription_plan, :discounts)

  def fetch_user_data(user_id:)
    user = Users::Models::User.find_by(user_id:)
    UserResponse.new(
      shipping_address: user.shipping_address,
      subscription_plan: user.subscription_plan,
      discounts: user.discounts.active.sum(:discount_amount)
    )
  end
end

# app/components/marketplace/controllers/orders_controller.rb

def create
  user_data = ::Users::Public::Api.fetch_user_data(user_id: current_user.id)
  order = Marketplace::Models::Order.new(
    user_id: current_user.id,
    shipping_address: user_data.shipping_address,
    offering: user_data.subscription_plan,
    discounts: user_data.discounts,
    items: order_params[:items_ids]
  )
end

What this changes is that this API is the only source of truth. If every component uses Users::Public::Api to fetch User data, we don't need to worry about checking all references to users.shipping_address when changing something around users' address. The only thing we need to ensure is working as expected is the API we wrote - if it's still returning same values as before our refactor of shipping_address, then we're golden.

All user.shipping_address occurrences should happen within users/ component, all other components should access it through Users::Public::Api.

The users/ component is the owner of the code, and they know that they can't change it, break it, or do whatever that affects other domains that consume that data.

If they want to implement a change in a way how discounts are returned, then a new version of API might be implemented while the old one remains the same. Or, if everyone (all components that use that data) agree and synchronize, then the change is possible in existing API.

Why is it that important

This prevents from calling directly ActiveRecord models from different domains. Direct calls create strong coupling when it's not needed. We just need data from three columns, we don't need whole User record. What if those columns got migrated to different model? We'll try to read user.shipping_address, but it's no longer there and we need to debug. If we're using given component's API, if they migrate data to another table, they also make sure that the API returns the same values but from another data source.

For us (consumers) it doesn't matter from where the data comes from. We just want the address and the users/ should give it to us if we're calling their API. If we call their models directly, we need to know where exactly the data is - and that's not needed if we're in marketplace/.

Untangling the Rails Monolith - quick look at the database

rbglod — Thu, 11 Dec 2025 11:51:00 +0000

Having worked in a few quite big Rails projects already, I started to appreciate domains separation.

Each component should work as an (almost) independent service. This reduces complexity and dependency on other areas. This reduces development time and allows the team to operate faster.

Simplier code = quicker code reviews = faster releases

I have a monolith, what do I do

Every project has some areas that it can be split into. In most apps there're probably areas of Users management, Payments processing, Reports generators, X management, etc...

Of course we need to know if User X has already paid for theirs subscription, or what bank account was used to process last payment, but not necessarily all of this data has to be kept in one or two models.

We can manage Users - their personal info, contact data, preferences - without knowing on which subscription they currently are. When changing profile picture we don't have to know if they already added a credit card, or how many resources of X they already added to the system.

Where I'm going with this is - each area can operate independently from the others - and we should always try to decouple as much as possible and be diligent about keeping the context boundaries clear.

It's rather rare scenario that we end up in greenfield project where we can design everything from scratch (which btw isn't that easy as it might seem), so let's have a look on some legacy app and how we could implement small changes that would benefit in future.

Untangling legacy monolith - database level

I guess it's common scenario - let's prepare some MVP and check if it works...

Oh, it actually makes sense and there's demand for that! Let's build something more on top of it!

Oooh, there's even more demand, let's add some more features...

And the development lifecycle goes on and on, but the foundation stay the same - the small MVP project that wasn't prepared to scale that much. So we might end up with a tables similar to this:

# schema.rb

create_table "employees", ... do |t|
    t.string "uuid"
    t.string "name"
    t.string "email"
    t.integer "organization_id"
    t.boolean "active"
    t.string "onboarding_status"
    t.string "preferred_working_days"
    t.string "contact_preference"
    t.boolean "contract_up_to_date"
    t.string "payment_method"
    t.string "payment_details"
    t.string "address_street"
    t.string "city"
    t.string "zip"
    t.string "country"
    t.string "phone_number"
    t.string "notes"
end

It's a mess, but since business demanded, the devs had to deliver. But it could be done in a slightly more future-proof way.

We could split this table into at least three smaller ones, making them faster to operate with (in terms of performing queries and cognitive load for developers).

# schema.rb

create_table "employees", ... do |t|
    t.string "uuid"
    t.string "name"
    t.string "email" # might be a contact_detail but it's often used to find given employee by it's email
    t.integer "organization_id"
    t.boolean "active"
    t.string "notes"
    t.index ["email"]
end

create_table "employee_settings", ... do |t|
    t.string "preferred_working_days"
    t.boolean "contract_up_to_date"
    t.integer "employee_id"
    t.string "onboarding_status"
end

create_table "employee_payments", ... do |t|
    t.string "payment_method"
    t.string "payment_details"
    t.integer "employee_id"
end

create_table "employee_contact_details", ... do |t|
    t.string "address_street"
    t.string "city"
    t.string "zip"
    t.string "country"
    t.string "phone_number"
    t.string "contact_preference"
    t.integer "employee_id"
end

Each table represents separate area of employee "details". Those are related to payments, contact details or some generic ones (which with time might grow up to another big table that could be split into smaller ones).

How can we strive to achieve this kind of structure in a legacy app?

Every time we need to add some new setting, detail, boolean, etc. we should ask a question - can it be moved into a separate table? Is it crucial to keep in the same table as other data?

Every time we add new column to existing table, it might be an opportunity to initiate decoupling existing monolith. Start with small steps, but with time you'll see the benefit.

Need to store employee's t-shirt size that should be ordered? Let's launch a new table with employee_equipment - maybe in future you'll have to store their shoe size, or glove size if they work in a warehouse.

Look for opportunities to start with clean tables. Adding new column to existing ones is the easiest, but migrating data to new tables is a lot of work to do it safely and with no disruptions for the whole system.

Apart from reading task requirement "store employee's tshirt size", think about the big picture - how in future this might impact employees table? How can it be done in a more efficient way?

Untangling the code

In the next post I'll explore an approach which I find very usefull in setting clear boundaries of domains, while keeping the data flow between them. This, again, reduces the code coupling, complexity and probability of unexpected code behaviour. I'll talk about APIs inside the monolith.

Sensitive data encryption in Rails

rbglod — Wed, 29 Jul 2020 18:40:28 +0000

Recently I received a request to encrypt some part of data that was already in my client’s app. I don’t want to share too many details, so let’s say it was few columns of an existing table that already contained some data.

So my task was to enrypt all existing records, delete non-encrypted data and save all future records as encrypted. Sounds cool!

Lockbox
What I needed was a simple ruby gem that would help me with all above tasks. I found Lockbox or - the Lockbox found me! I’ve read about it some time ago in RubyWeekly newsletter. It offers migrating existing data, encrypting columns, decrypting fields on the fly if you need to display them somewhere in the views, etc.

The Plan
Since our app is used 24/7 we didn’t want to put it in the maintenance mode, we had to split update into few steps without causing any downtime.
I came up with this solution:

Add Lockbox to gemfile, add new columns to persist encrypted data, update model to migrate existing data
Add ignored_columns attribute to model (to remove columns in next step without any downtime)
Remove non-encrypted columns from table
Remove ignored_columns attribute

This required 4 pull requests creation, 1 task execution and zero downtime :)

Execution
I started by adding gem 'lockbox', '~> 0.4.6' to the Gemfile and generating Lockbox master key.

Lockbox.generate_key

This key had to be added to all secrets (in dev, staging, production and test environments). I also created lockbox.rb file inside initializers/ folder.

After that Lockbox was ready to go, so I could start work on encryption of my desired model. Let’s say that I wanted encrypt our clients’ correspondence data. Inside Client::Address model, I had to encrypt following fields: street, city, postal_code, country, phone_number

So inside models/client/address.rb I had to add:

class Client::Address < ActiveRecord
  encrypts :street, :city, :postal_code, :country, :phone_number, migrating: true
end

Notice last part of it - migrating: true. This is crucial for next step - migrate existing data. But before that I had to create new columns for encrypted data. So I came up with following migration:

def up
  add_column :client_address, :street_ciphertext, :text
  add_column :client_address, :city_ciphertext, :text
  add_column :client_address, :postal_code, :text
  add_column :client_address, :country, :text
  add_column :client_address, :phone_number, :text

  change_column :client_address, :street, :string, null: true
  change_column :client_address, :city, :string, null: true
  change_column :client_address, :postal_code, :string, null: true
  change_column :client_address, :country, :string, null: true
  change_column :client_address, :phone_number, :string, null: true
end

At this point no null: false constraint could take place - adding not_null columns to existing records would paralyze app. Also null: true had to be added to existing columns - from now on we would save new records only to _ciphertext columns.

After successful migration, there was only one thing missing. Data migration itself!
Thanks to Lockbox, this is done by running simple command in rails console. After merging above changes, I had to ssh to production environment and run

Lockbox.migrate

At this point all existing data was migrated to _ciphertext columns. But I needed more - I needed to remove non-encrypted columns with sensitive data saved in plain text!

Step II
First of all I removed migrating: true from models/client/address.rb model - this was no longer needed.
Next thing was migration:

def up
  change_column :client_address, :street, :text, null: false
  ...
end

For all _ciphertext columns I added not_null constraint, since we’re already saving records to that columns and none of them was empty anymore.
And the most important part of this pull request was adding ignored_columns to Address model. Since in next PR I wanted to remove all unnecessary columns this was crucial at to add this at this point.

class Client::Address
  ...
  ignored_columns: %w[street city postal_code country phone_number]
  ...
end

Step III
This part is only about removing non-encrypted columns. So it included only this migration:

def up
  remove_column :client_address, :street
  remove_column :client_address, :city
  remove_column :client_address, :postal_code
  remove_column :client_address, :country
  remove_column :client_address, :phone_number
end

Thanks to ignored_columns this could be deployed without causing any downtime to app. From this point there were no decrypted clients’ address data in our database.

Last step was to remove ignored_columns attribute from the Address model.

Conclusion
Encryption was definitely worth adding and considering fact that this update was deployed at Friday after 2a.m. with no issues, this shows that process is very easy.
At least on that set of data that I had to encrypt.

How I learned to make use of rake when blogging

rbglod — Fri, 08 Nov 2019 17:36:47 +0000

When I was using Hugo as my blogging engine I get used to automated post generator. I just opened console, typed one command and had ready-to-extend post template.
When I switched to Jekyll, I spent maybe like 10 seconds to search for that command and - as you may expect - didn't found any. I forgot about it and kept using Jekyll, but when I wanted to write new post today something switched in my head!

Man, you're programmer,
why don't you write your own ruby command to create that dummy post file for you?

Since I'm mainly Rails developer Rake came to my mind at first place.

Rakefile

I opened my blog/ directory and created Rakefile. It is needed for rake to load commands (or tasks, as they are called in rake). I started with classic Hello World example, just to check how things work!

task :default => ['hello']

desc 'Greeting from rake task'
task :hello do
  puts 'Hello World from rake task!'
end

and that's it. Run rake hello from command line and (if you have rake gem installed) you'll be greeted by your ruby code!

One thing needs explanation, though. That task :default line. It is said, that's that good practice to always set a default task, so when rake is invoked without argument it will fallback to the default one. So, if we put nothing after rake, it will greet us, isn't it nice?

How to use arguments in rake tasks

So yeah, that was easy one. But when I want to create new post, I would like to name new file with text provided by me, not always the same, default one. So now, I had to learn how to pass arguments to task.
As you may expect, it is easy as previous example. Let's assume that we're still expanding our Rakefile so I won't need to repeat myself with that task :default code.

desc 'Greet someone with his name!'
task :hello_dear, [:name] do |t, args|
  args.with_defaults(name: 'Dude')
  puts "Hello, #{args.name.capitalize}!"
end

Now, if you run rake hello_dear you'll see wonderful greeting:

Hello, Dude!

But to make use of that cool method, you can now run rake hello_dear[rob] and see:

Hello, Rob!

Yeah, we did it! We know how to use arguments in rake tasks. Let's create some files!

Using bash commands from ruby

It may not be suprising, but... invoking bash commands from ruby is very easy. And pleasant.
To make use of all that cd, touch, cat, etc. you just need to surround it with backtick sign. Check it out!

desc 'Touch command ruby wrapper'
task :touch, [:file_name] do |t, args|
  args.with_defaults(file_name: 'new_file')
  `touch #{args.new_file}`
  puts "Created new file with name: #{args.new_file}"
end

And here you go, here's your file maker. Create few files with theese commands:

rake touch
rake touch[thats_my_file]

With backticks you can use string interpolation same as with '\"\"'. Isn't it cool?

The final purpose of this post

So finally we got all needed skills to generate new post template with rake. Let's see my code!

desc 'Create new post with given name'
task :new_post, [:post_name] do |t, args|
  args.with_defaults(post_name: 'new_post')
  time_now = Time.new.strftime('%Y-%m-%d')
  safe_post_name = args.post_name.gsub(' ', '_')
  post_name_with_timestamp = [time_now, safe_post_name].join('-') + '.md'
  post_header = [
    "---",
    "layout: post",
    "title: #{args.post_name}",
    "date: #{Time.new}",
    'keywords: ',
    "---"
  ]

  puts "Timestamp: #{time_now}"
  puts "Creating new post with file name: #{post_name_with_timestamp}"
  `touch ./_posts/#{post_name_with_timestamp}`

  post_header.each do |string|
    `echo #{string} >> ./_posts/#{post_name_with_timestamp}`
  end

  puts "You can find it in ./_posts/#{post_name_with_timestamp}"
end

Few words about that script. At first I needed timestamp, to use it in filename. Jekyll makes use of that to order posts and display them correctly. So i used built-in Time module and formatted it nicely, to produce Jekyll-friendly date format.

Next line, with safe_post_name is 'just to make sure' that the file name won't have spaces. So with this 'feature', you can pass arguments like that rake new_post['thats new post with spaces in title'] and it won't raise any errors nor exceptions. And, what is even more cool, in "title" tag it will provide name with spaces, ready to display on webpage!

Next, I combine safe post name with timestamp and .md file extension - Jekyll uses markdown files, so I need to create file with that extension. Then I create post_header, which is used by Jekyll to provide proper page layout (it may be post or page or whatever I implement in future), display title, date and make use of keywords.

Actually I don't use keywords anywhere in my blog I suppose, but maybe in future it will be useful to have it. You never know!

Then, few puts to inform user (me) what is going on, when I run that task, and then bash command - touch - to create file with given filename.

And at the end, I fill that new file with post_header data, to finally get that ready-to-use post template file.

At the last line I just tell myself where to find new file, in case I would forget the structure of Jekyll project (which is impossible I think, it is really very straight forward).

That's it! Now you can grab that code, improve, tune to your needs and make use of that file generator made in ruby lang! Cheers!

redis-namespace: when one sidekiq process is not enough

rbglod — Wed, 24 Jul 2019 18:13:06 +0000

Few months ago I was working in a project in which we had two applications communicating with each other.
Each of them had to has its own sidekiq process, but they were deployed to one server. After some time, I realized that after deploying one app, the second one has problems with invoking its workers. The problem was that sidekiq was restarting and loading workers only from one (last deployed) app. So after each deploy I had many errors saying that

NameError: uninitialized constant AppOneWorker

and another time

NameError: uninitialized constant AppTwoWorker

With rescue came gem called redis-namespace.

redis-namespace

As pointed in it's repo description, redis-namespace:

...adds a Redis::Namespace class which can be used to namespace Redis keys.

What does it mean? It means that we can have plenty of apps using one Redis server, with many sidekiq processes not conflicting with each other.

All you need to do is to add redis-namespace to your Gemfile and configure it in app/config/initializers/sidekiq.rb like this:

# frozen_string_literal: true

require 'sidekiq'

Sidekiq.configure_server do |config|
  config.redis = {
    url: 'redis://127.0.0.1:6379',
    namespace: 'my_first_app_name'
  }
end

Sidekiq.configure_client do |config|
  config.redis = {
    url: 'redis://127.0.0.1:6379',
    namespace: 'my_first_app_name'
  }
end

And do the same for the second app (with its proper name, of course).

After that, when you visit your app's /sidekiq route (if you mounted it in routes.rb), you can see that sidekiq process has namespace added at the beginning of it's name.
Other way to check if everything is OK is to launch redis-cli in command line, and look up for keys containing your namespace.

redis 127.0.0.1:6379> keys *app_name*
 1) "my_first_app_name:queues"
 2) "my_second_app_name:queues"

Credits

When I had that problem, I wouldn't resolve it without Maciej Mensfeld's post about redis-namespace. Thanks!

You can even find my enthusiastic comment there, I left back then, haha.

SQL joins recall - a little bit too late

rbglod — Tue, 23 Jul 2019 06:39:08 +0000

Some time ago I had an interview with really cool company. I totally messed it up, despite the fact that... questions were really basic.

There were two tasks: one in Ruby and one with SQL. Ruby took me less than 10 minutes, but SQL... It's embarrassing to talk about it.

The question was about joining three tables in one query... With ActiveRecord I did it on the fly, but then I realized that I'm Rails developer without SQL skills. Shame. So let's start with simple joins.

JOINS

Let's say, we have 2 tables:

Books,
Authors,

with following structure:

books |   
------|   
id    |   
title |
author_id|

authors|
-------|
id     |
name   |

And following data:

test=# select * from books;
 id |       title       | author_id 
----+-------------------+-----------
  1 | Sapiens           |         3
  2 | Homo Deus         |         3
  3 | Harry Potter      |         2
(3 rows)

test=# SELECT * FROM authors;
 id |     name     
----+--------------
  1 | Mr. Hopkins
  2 | J.K. Rowling
  3 | Yuval Noah Harari
(3 rows)

You see that associations between them, right? Author has many books, book belongs to author.

INNER JOIN

So now, what we want is to select only authors who have any books assigned to them.

SELECT authors.* FROM authors
  INNER JOIN books ON books.author_id = authors.id;

What we did here is INNER JOIN - we joined two tables and printed out records, which have matching values (authors.id equals books.author_id).
If we'd like not to repeat authors printed out, we should append DISTINCT keyword after SELECT.

FULL OUTER JOIN / FULL JOIN

To select all data from both tables we should use FULL OUTER JOIN:

SELECT * FROM authors
  FULL OUTER JOIN books ON books.author_id = authors.id;

It prints out all data - all records from books table and all records from authors table. It is the same as FULL JOIN

LEFT JOIN / LEFT OUTER JOIN

Left join returns all records from authors (the table on the left) - if we start with books, it would be books of course - and matched records from joined table - books.
So, let's print all authors, and their book's count!

SELECT authors.name, COUNT(authors.name) AS books_count FROM authors
  LEFT OUTER JOIN books
  ON authors.id = books.author_id
  GROUP BY authors.name;

It prints out nice, grouped data:

     name          | books_count 
-------------------+-------------
 Yuval Noah Harari |     2
 J.K. Rowling      |     1
 Hopkins           |     1
(3 rows)

What about more than two tables?

At some point (at recruitment interview for example) you will need to join more than two tables and fetch data from them.

Let's add another table to the previous ones.

authors_books
-------------
author_id
book_id

And remove column books.author_id. It gives us many to many association.

Now, records in this table should look like this:

test=# SELECT * FROM authors_books;
 id | author_id | book_id 
----+-----------+---------
  1 |         3 |       1
  2 |         3 |       2
  3 |         2 |       3
(3 rows)

To print authors with corresponding books, we need to join authors with authors_books and then authors_books with books to match given ids.
Let's try with this SQL query:

SELECT authors.name, books.title FROM authors 
  JOIN authors_books ON authors.id = authors_books.author_id
    JOIN books ON authors_books.book_id = books.id;

        name        |       title       
--------------------+-------------------
 Yuval Noah Harari  | Sapiens
 Yuval Noah Harari  | Homo Deus
 J.K. Rowling       | Harry Potter
(3 rows)

Now, we can count their books as well:

test=# SELECT authors.name, COUNT(authors.name) FROM authors
         JOIN authors_books ON authors.id = authors_books.author_id
           JOIN books ON authors_books.book_id = books.id GROUP BY authors.name;

        name        | count 
--------------------+-------
 Yuval Noah Harari  |     2
 J.K. Rowling       |     1
(2 rows)

Enough joins for now

As you can see, SQL join queries aren't that hard and scary as they seem to be. ActiveRecord made Rails developers lazy and sql-disabled (as I am), but sometimes it is worth-knowing how to write some complex joins in raw SQL.