paint-brush
How to Connect With External MySQL Servers (Part 2: Insert & Update Operations)by@Kiran

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

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

Too Long; Didn't Read

This is the second part of the series where we create a service to interact with MySQL server in Rails using MySQL2 gem. We will be adding additional methods to help us perform insert and update operations to MySQL server using Myserve gem. Next week we will be learning how we can perform queries with the prepared statement which helps us to avoid SQL injection issues. You will be able to perform basic queries in the external. database using the. service file: "Perform basic query: select, insert, update and perform transaction," "Prepared statement"
featured image - How to Connect With External MySQL Servers (Part 2: Insert & Update Operations)
Kiran HackerNoon profile picture

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.

  • If we are providing
    id=1
    and
    attributes
    same as insert query,
    format_update_query
    will return
    "UPDATE users SET first_name='John',last_name='Doe' WHERE id=1"
  • Now the
    update
    method will send the query to the server and update the record with
    id=1
    in the database.

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.