How to Connect With External MySQL Servers (Part 2: Insert & Update Operations) | Hacker Noon

Author profile picture

@KiranKiran

Content Writer at Truemark Technology. Company Website Link – https://www.truemark.dev/

This is the second part of the series where we create a service to interact with MySQL server in Rails using MySQL2 gem. You can read the first part here.

Requirements

  • [x] Service to connect with external MySQL server
  • [ ] Perform basic query: select, insert and update
  • [ ] Prepared statement
  • [ ] Perform transaction
  • [ ] Perform join query

In the previous blog, we created a service and also added a method to perform

select

operations. Today we will be adding additional methods to help us perform insert and update operations to MySQL server using MySQL2 gem.

In this blog

We will be learning the following in this blog:

  • Perform insert query
  • Perform update query

Perform Insert Query

Insert query is used to create a new record in the database.

Code

def insert(attributes)
  query = format_insert_query(attributes)

  perform_mysql_operation do
    mysql_connect.query(query)

    puts 'Record inserted!'
  end
end

private

def format_insert_query(attributes)
  raise 'Attributes cannot be empty' if attributes.empty?

  columns = attributes.keys.join(',')

  values = attributes.values.collect! { |value| "'#{value}'" }.join(',')

  "INSERT INTO #{table} (#{columns}) VALUES (#{values})"

end

Explanation

format_insert_query

is taking the

attributes

hash parameter from the

insert

method. The following is happening inside the method:

  • Get column names by formatting key part of attributes param
  • Get values to insert by formatting value part of attributes param
  • Construct and return an insert query

Following is happening inside the

insert

method:

  • Call
    format_insert_query

    to get a query that can directly be used for the insert operation

  • Insert to database

Practically:

  • {first_name: 'John', last_name: 'Doe'}

    will be received as the

    attributes

    parameter, which will be sent to

    format_insert_query

    to get formatted query

  • Inside
    format_insert_query

    ,

    columns

    will have value

    "first_name,last_name";

    key part of the

    attributes

    hash

  • values

    will have the

    "'John','Doe'";

    value part of the

    attributes

    hash.

  • Lastly, if
    table

    was

    users

    it will return

    "INSERT INTO users (first_name,last_name) VALUES ('John','Doe')"
  • Now the
    insert

    method will send the query to the server and the new record will be inserted into the database.

Perform Update Query

An update query is used to update existing records in the database.

Code

def update(id, attributes)
  query = format_update_query(id, attributes)

  perform_mysql_operation do
    mysql_connect.query(query)

    puts 'Record Updated!'
  end
end

private

def format_update_query(id, attributes)
  raise 'Attributes cannot be empty' if attributes.empty?

  formatted_attributes = attributes.map { |key, value| "#{key}='#{value}'" }.join(',')

  "UPDATE #{table} SET #{formatted_attributes} WHERE #{primary_column}=#{id}"
end

Explanation

There is only one change in

update

to

insert

. It’s taking

id

as parameters. The

id

parameter lets us know which existing record we want to update in the database. It is getting formatted query and updating in a database, the concept is the same as insert with only change in the query.

format_update_query

has a slight difference to that of

format_insert_query

; it is converting

attributes

differently. Let’s see that with a practical example below.

Final Code

If you have been following the tutorial from Part 1, you will have the following in your service file:

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}")

          puts result.entries
        end
      end

def fetch_one(id)
        perform_mysql_operation do
          result = mysql_connect.query("SELECT * from #{table} WHERE #{primary_column}=#{id}")

          puts result.entries
        end
      end

def insert(attributes)
        query = format_insert_query(attributes)

        perform_mysql_operation do
          mysql_connect.query(query)

          puts 'Record inserted!'
        end
      end

def update(id, attributes)
        query = format_update_query(id, attributes)

        perform_mysql_operation do
          mysql_connect.query(query)

          puts 'Record Updated!'
        end
      end

private

      def connect_to_db
        host = ENV['MYSQL_SERVER_IP']
        database = ENV['MYSQL_DB_NAME']
        username = ENV['MYSQL_USERNAME']
        password = ENV['MYSQL_PASSWORD']

        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

def format_insert_query(attributes)
        raise 'Attributes cannot be empty' if attributes.empty?

        columns = attributes.keys.join(',')

        values = attributes.values.collect! { |value| "'#{value}'" }.join(',')

        "INSERT INTO #{table} (#{columns}) VALUES (#{values})"
      end

def format_update_query(id, attributes)
        raise 'Attributes cannot be empty' if attributes.empty?

        formatted_attributes = attributes.map { |key, value| "#{key}='#{value}'" }.join(',')

        "UPDATE #{table} SET #{formatted_attributes} WHERE #{primary_column}=#{id}"
      end
    end
  end
end

After this, our service should be able to perform basic queries in the external MySQL server using MySQL2 gem. Next week we will be learning how we can perform queries with the prepared statement which helps us to avoid SQL injection issues.

Image Credits: Cover Image by Kelvin Yang on Unsplash

This post was first published on DevPostbyTruemark.

Author profile picture

Read my stories

Content Writer at Truemark Technology. Company Website Link – https://www.truemark.dev/

Tags

Join Hacker Noon

Create your free account to unlock your custom reading experience.

read original article here