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

by KiranJanuary 20th, 2021

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)

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

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.