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 gem for our purpose. Let’s first create a service to connect with an external MySQL server. mysql2 Create a file inside and add the following to it. connect.rb lib/my_sql_server/database Code MySqlServer Database = ENV[ ]
        username = ENV[ ]
        password = ENV[ ]
        database = ENV[ ]

        Mysql2::Client.new(username: username, : password, : database, : host)
      end

      def perform_mysql_operation
        raise ArgumentError, unless block_given?

        begin
          @mysql_connect = connect_to_db rescue StandardError => e
          raise e
        ensure
          mysql_connect&.close
        end
      end
    end
  end
end require 'mysql2' module module : class Connect attr_reader mysql_connect private def connect_to_db host 'MYSQL_SERVER_IP' 'MYSQL_USERNAME' 'MYSQL_PASSWORD' 'MYSQL_DB_NAME' password database host 'No block was given' yield Explanation Here, we are creating a service with a private method that connects to our external MySQL database. We are using the following from : connect_to_db application.yml host: IP address of external MySQL server username: User of the database password: Database password database: Database name In , for security reasons; we are making sure that connection to the external database is closed once all the query operation is completed. perform_mysql_operation Perform basic query: select Select query Select query lets us fetch row/s from our database. Select all Code = table
  end

  def fetch_all
    perform_mysql_operation result = mysql_connect.query( )

      result.entries
    end
  end
end : , : ( )
    @ class Connect attr_reader mysql_connect table def initialize table table do "SELECT * from #{table}" Explanation We are initializing 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. table method will execute the query to fetch all records from the external MySQL server. Inside the method, we are using which accepts a block of our code, catch errors, and ensure the connection is closed after the query is completed. fetch_all perform_mysql_operation We are saving the result to which will return an instance of mysql2 class. And to get actual rows, we are using method. result entries Select one Code = table
    @primary_column = primary_column
  end

  def fetch_one(id)
    perform_mysql_operation result = mysql_connect.query( )

      result.entries
    end
  end
end : , : , : ( , )
    @ class Connect attr_reader mysql_connect table primary_column def initialize table primary_column table do "SELECT * from #{table} WHERE #{primary_column}=#{id}" Explanation We have added to our initializer; this is the column name of the primary key in the table. Although normally we use 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 id primary_column is fetching a single record from the table. We are passing as the param, which should be the id of a record we want to fetch. We are using condition so as to only fetch a record with that particular id. fetch_one id WHERE Final Code MySqlServer Database = table
        @primary_column = primary_column
      end

def fetch_all
        perform_mysql_operation result = mysql_connect.query( )

          result.entries
        end
      end

def fetch_one(id)
        perform_mysql_operation result = mysql_connect.query( )

          result.entries
        end
      end

private

      def connect_to_db
        host = ENV[ ]
        username = ENV[ ]
        password = ENV[ ]
        database = ENV[ ]

        Mysql2::Client.new(username: username, : password, : database, : host)
      end

def perform_mysql_operation
        raise ArgumentError, unless block_given?

        begin
          @mysql_connect = connect_to_db rescue StandardError => e
          raise e
        ensure
          mysql_connect&.close
        end
      end
    end
  end
end require 'mysql2' module module : , : , : ( , )
        @ class Connect attr_reader mysql_connect table primary_column def initialize table primary_column table do "SELECT * from #{table}" do "SELECT * from #{table} WHERE #{primary_column}=#{id}" 'MYSQL_SERVER_IP' 'MYSQL_USERNAME' 'MYSQL_PASSWORD' 'MYSQL_DB_NAME' password database host 'No block was given' yield 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 .

Fetch

Your business deserves Quality Focused Software Development. Request A Quote.

Read My Stories

Too Long; Didn't Read

In your car, at home, or at work — Bosch technology shapes many areas of life.

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

Too Long; Didn't Read

Company Mentioned

Kiran

About Author

TOPICS

THIS ARTICLE WAS FEATURED IN...

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

Too Long; Didn't Read

Company Mentioned

Kiran

About Author

TOPICS

THIS ARTICLE WAS FEATURED IN...

RELATED STORIES