How to perform DB write health checks in Ruby?

Question

I want to setup MySQL database health check for a ruby app, basically the response should be

{
    "read_success": true,
    "write_success": true,
    "exception": null
}

My health check should perform the following operation:

Read a table from the database Write something to Database If any of the above operation fails, it should throw the exception as mention in the response.

module API
  ApplicationName.controllers :health_check do
    get :index do
      status = {
          read_success: read_successful,
          write_success: write_successful,
          exception: check_exception
      }
       [200, {}, [status.to_json]]
    end
  end
end

def read_successful
  begin
    ActiveRecord::Base.connection.execute("SELECT 1")
    true
  rescue
    false
  end
end

def write_successful
  begin
    # logic to check write to database, which table should i write to?
    true
  rescue
    false
  end
end

def check_exception
  begin
    ActiveRecord::Base.connection.execute("SELECT 1")
    nil
  rescue Exception => e
    return e.message
  end
  begin
    # logic to check write to database, which table should i write to?
    nil
  rescue Exception => e
    return e.message
  end
end

I have tried implementing read health checks as above but dont know how to implement write health checks? Is there any way to implement write health check without creating a new table for healthcheck in the database.

What should be my logic to implement write health check?

While it's nice to have, a write health check is overkill. 96 times out of 100 when you have a database issue it's a connection issue and therefore the read health check is more than sufficient. In the rare instances where there is a write issue initially (ie, the disk has filled up), a connection issue soon follows. — dbugger, Sep 08 '20 at 23:39
So You mean to say that if I can read a database, I will definitely be able to write to it? Any reasons to support the statement? — Aditya Verma, Sep 08 '20 at 23:46
You will be very likely to be able to write if you can read. Long experience. — dbugger, Sep 08 '20 at 23:47
Okay understood, but could you help me with writing a write db health check? — Aditya Verma, Sep 08 '20 at 23:49
Select either a known row from a known table or a count from a known table. If you get the proper value, the check passes. — dbugger, Sep 09 '20 at 00:05

score 1 · Accepted Answer · answered Sep 10 '20 at 08:54

Finally Implemented it, posting the answer so that it mighgt help someone later, for this to work you will have to create a table health_check in your database.

CREATE TABLE `health_check` (   `id` int(11) NOT NULL AUTO_INCREMENT,   `date` varchar(40) NOT NULL,   `status` varchar(40) NOT NULL,   PRIMARY KEY (`id`) );

API

ApplicationName.controllers :deep_health_check do
  get :index do
    time_stamp = Time.now.strftime('%Y-%m-%d %H:%M:%S')
    read_error_msg = "Exception While Reading To Database: "
    write_error_msg = "Exception While Writing to Database: "
    read_success = read_successful
    write_success = write_successful(time_stamp)
    exception = nil

    str = String.new("")

    if read_success != true && write_success != true
      str = read_error_msg + read_success + "  and  " + write_error_msg + write_success
      read_success = false
      write_success = false

    elsif read_success != true && write_success == true
      str = read_error_msg + read_success
      read_success = false
    else
      if read_success == true && write_success != true
        str = write_error_msg + write_success
        write_success = false

      end
    end

    if str != ""
      exception = str
    end


    status = {
        read_success: read_success,
        write_success: write_success,
        exception: exception
    }
    [200, {}, [status.to_json]]
  end
end

def read_successful
  begin
    ActiveRecord::Base.connection.execute("SELECT 1")
    return true
  rescue Exception => e
    return e.message
  end
end

def write_successful(time_stamp)
  begin
    write_to_a_table(time_stamp)
    return true
  rescue Exception => e
    return e.message
  end
end

def write_to_a_table(time_stamp)
  ActiveRecord::Base.transaction do
    ActiveRecord::Base.connection.execute("INSERT INTO health_check (date,status) VALUES ('#{time_stamp}', 'fine');")
  end
end

How to perform DB write health checks in Ruby?

1 Answers1