paint-brush
How to Connect With External MySQL Servers (Part 1: Select Operations)by@Kiran
238 reads

How to Connect With External MySQL Servers (Part 1: Select Operations)

by KiranJanuary 14th, 2021
Read on Terminal Reader
Read this story w/o Javascript
tldt arrow

Too Long; Didn't Read

How to Connect With External MySQL Servers (Part 1: Select Operations) by Kiran Kiran Content Writer at Truemark Technology. How to connect with external databases (Part 2) How to use the tutorial is very long, so we will split this blog into various parts. For security reasons, we are making sure that connection to the external database is closed once all the query operation is completed. We will be using the following from application.yml. We are using a method that accepts a block of our code, catch errors, and ensure the connection is closed after the query is completed.

Company Mentioned

Mention Thumbnail
featured image - How to Connect With External MySQL Servers (Part 1: Select Operations)
Kiran HackerNoon profile picture

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 projects 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_connect

      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_connect = connect_to_db

          yield
        rescue StandardError => e
          raise e
        ensure
          mysql_connect&.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_connect, :table

  def initialize(table)
    @table = table
  end

  def fetch_all
    perform_mysql_operation do
      result = mysql_connect.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_connect, :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_connect.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_connect, :table, :primary_column

      def initialize(table, primary_column)
        @table = table
        @primary_column = primary_column
      end

def fetch_all
        perform_mysql_operation do
          result = mysql_connect.query("SELECT * from #{table}")

          result.entries
        end
      end

def fetch_one(id)
        perform_mysql_operation do
          result = mysql_connect.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_connect = connect_to_db

          yield
        rescue StandardError => e
          raise e
        ensure
          mysql_connect&.close
        end
      end
    end
  end
end

We created a service that connects to an external MySQL server and performs basic select operations in this part. We will learn how to perform basic insert and update operations next week.

This post was first published on DevPostbyTruemark.