Table of Contents
Rails has made our lives easier. If we are talking in terms of querying the database, the active record has got us covered. But what if we had to communicate with an external database?
Recently in one of the project that I worked on, I had to perform insert, update, select, and other different queries to the external MariaDB server and I didn’t know what to do. I figured out that it would be easy if I created a service that can work like ORM to perform the query I wanted.
Skills required to follow the tutorial
Intermediate in:
- Rails
- SQL
Requirements
- Service to connect with external MySQL server
- Perform basic query: select, insert and update
- Prepared statement
- Perform transaction
- Perform join query
In this blog
Our requirement list is very long, so we will split this blog into various parts. We will be looking at the following requirements in this one:
- Service to connect with external MySQL server
- Perform basic query: select
Service to connect with external MySQL server
We will be using mysql2 gem for our purpose. Let’s first create a service to connect with an external MySQL server.
Create a file connect.rb inside lib/my_sql_server/database and add the following to it.
Code
require 'mysql2'
module MySqlServer
module Database
class Connect
attr_reader :mysql_client
private
def connect_to_db
host = ENV['MYSQL_SERVER_IP']
username = ENV['MYSQL_USERNAME']
password = ENV['MYSQL_PASSWORD']
database = ENV['MYSQL_DB_NAME']
Mysql2::Client.new(username: username, password: password, database: database, host: host)
end
def perform_mysql_operation
raise ArgumentError, 'No block was given' unless block_given?
begin
@mysql_client = connect_to_db
yield
rescue StandardError => e
raise e
ensure
mysql_client&.close
end
end
end
end
end
Explanation
Here, we are creating a service with a private method connect_to_db that connects to our external MySQL database. We are using the following from application.yml:
- host: IP address of external MySQL server
- username: User of the database
- password: Database password
- database: Database name
In perform_mysql_operation, for security reasons; we are making sure that connection to the external database is closed once all the query operation is completed.
Perform basic query: select
Select query
Select query lets us fetch row/s from our database.
Select all
Code
class Connect
attr_reader :mysql_client, :table
def initialize(table)
@table = table
end
def fetch_all
perform_mysql_operation do
result = mysql_client.query("SELECT * from #{table}")
result.entries
end
end
end
Explanation
We are initializing table variable, this is the name of the table that we want to perform queries on. We are adding it to the initializer so we can use the service with any table we want, it lets our code to be dynamic and flexible.
fetch_all method will execute the query to fetch all records from the external MySQL server. Inside the method, we are using perform_mysql_operation which accepts a block of our code, catch errors, and ensure the connection is closed after the query is completed.
We are saving the result to result which will return an instance of mysql2 class. And to get actual rows, we are using entries method.
Select one
Code
class Connect
attr_reader :mysql_client, :table, :primary_column
def initialize(table, primary_column)
@table = table
@primary_column = primary_column
end
def fetch_one(id)
perform_mysql_operation do
result = mysql_client.query("SELECT * from #{table} WHERE #{primary_column}=#{id}")
result.entries
end
end
end
Explanation
We have added primary_column to our initializer; this is the column name of the primary key in the table. Although, normally we use id as the primary key, that won’t always be the case. The primary key can be of any name when working on a real project, so we are handling that with primary_column.
fetch_one is fetching a single record from the table. We are passing id as the param, which should be the id of a record we want to fetch. We are using WHERE condition so as to only fetch a record with that particular id.
Final Code
require 'mysql2'
module MySqlServer
module Database
class Connect
attr_reader :mysql_client, :table, :primary_column
def initialize(table, primary_column)
@table = table
@primary_column = primary_column
end
def fetch_all
perform_mysql_operation do
result = mysql_client.query("SELECT * from #{table}")
result.entries
end
end
def fetch_one(id)
perform_mysql_operation do
result = mysql_client.query("SELECT * from #{table} WHERE #{primary_column}=#{id}")
result.entries
end
end
private
def connect_to_db
host = ENV['MYSQL_SERVER_IP']
username = ENV['MYSQL_USERNAME']
password = ENV['MYSQL_PASSWORD']
database = ENV['MYSQL_DB_NAME']
Mysql2::Client.new(username: username, password: password, database: database, host: host)
end
def perform_mysql_operation
raise ArgumentError, 'No block was given' unless block_given?
begin
@mysql_client = connect_to_db
yield
rescue StandardError => e
raise e
ensure
mysql_client&.close
end
end
end
end
end
We created a service that connects to an external MySQL server and perform basic select operations in this part. We will learn how to perform basic insert and update operation next week.
