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 operations. Today we will be adding additional methods to help us perform insert and update operations to MySQL server using MySQL2 gem. select 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 mysql_connect.query(query)

    puts end
end

private

def format_insert_query(attributes)
  raise attributes.empty?

  columns = attributes.keys.join( )

  values = attributes.values.collect! { |value| }.join( ) end do 'Record inserted!' 'Attributes cannot be empty' if ',' "'#{value}'" ',' "INSERT INTO #{table} (#{columns}) VALUES (#{values})" Explanation is taking the hash parameter from the method. The following is happening inside the method: format_insert_query attributes insert 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 method: insert Call to get a query that can directly be used for the insert operation format_insert_query Insert to database Practically: will be received as the parameter, which will be sent to to get formatted query {first_name: 'John', last_name: 'Doe'} attributes format_insert_query Inside , will have value key part of the hash format_insert_query columns "first_name,last_name"; attributes will have the value part of the hash. values "'John','Doe'"; attributes Lastly, if was it will return table users "INSERT INTO users (first_name,last_name) VALUES ('John','Doe')" Now the method will send the query to the server and the new record will be inserted into the database. insert 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 mysql_connect.query(query)

    puts end
end

private

def format_update_query(id, attributes)
  raise attributes.empty?

  formatted_attributes = attributes.map { |key, value| }.join( ) end do 'Record Updated!' 'Attributes cannot be empty' if "#{key}='#{value}'" ',' "UPDATE #{table} SET #{formatted_attributes} WHERE #{primary_column}=#{id}" Explanation There is only one change in to . It’s taking as parameters. The 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. update insert id id has a slight difference to that of ; it is converting differently. Let’s see that with a practical example below. format_update_query format_insert_query attributes If we are providing and same as insert query, will return id=1 attributes format_update_query "UPDATE users SET first_name='John',last_name='Doe' WHERE id=1" Now the method will send the query to the server and update the record with in the database. update id=1 Final Code If you have been following the tutorial from , you will have the following in your service file: Part 1 MySqlServer Database = table
        @primary_column = primary_column
      end

def fetch_all
        perform_mysql_operation result = mysql_connect.query( )

          puts result.entries
        end
      end

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

          puts result.entries
        end
      end

def insert(attributes)
        query = format_insert_query(attributes)

        perform_mysql_operation mysql_connect.query(query)

          puts end
      end

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

        perform_mysql_operation mysql_connect.query(query)

          puts end
      end

private

      def connect_to_db
        host = ENV[ ]
        database = ENV[ ]
        username = ENV[ ]
        password = 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

def format_insert_query(attributes)
        raise attributes.empty?

        columns = attributes.keys.join( )

        values = attributes.values.collect! { |value| }.join( ) end

def format_update_query(id, attributes)
        raise attributes.empty?

        formatted_attributes = attributes.map { |key, value| }.join( ) 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}" do 'Record inserted!' do 'Record Updated!' 'MYSQL_SERVER_IP' 'MYSQL_DB_NAME' 'MYSQL_USERNAME' 'MYSQL_PASSWORD' password database host 'No block was given' yield 'Attributes cannot be empty' if ',' "'#{value}'" ',' "INSERT INTO #{table} (#{columns}) VALUES (#{values})" 'Attributes cannot be empty' if "#{key}='#{value}'" ',' "UPDATE #{table} SET #{formatted_attributes} WHERE #{primary_column}=#{id}" 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. Cover Image by on Image Credits: Kelvin Yang Unsplash This post was first published on DevPostbyTruemark .

Humans Neglecting Maintenance: Causes And Consequences

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

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

Read My Stories

Too Long; Didn't Read

Make resilience your competitive advantage

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

About Author

Comments

TOPICS

THIS ARTICLE WAS FEATURED IN

Related Stories

Untitled Story

23 Cybersecurity Tips to Level up Your Data Privacy Game

How to Install MySQL 8 on macOS Using Homebrew

How to Stream Data from MySQL to Postgres

Running Up MySQL export using Docker: A How-To Guide

Upgrading MySQL 5.5 to MySQL 8: A Step by Step Guide

23 Cybersecurity Tips to Level up Your Data Privacy Game

How to Install MySQL 8 on macOS Using Homebrew

How to Stream Data from MySQL to Postgres

Running Up MySQL export using Docker: A How-To Guide

Upgrading MySQL 5.5 to MySQL 8: A Step by Step Guide

Light-Mode

Classic

Newspaper

Dark-Mode

Neon Noir

Minty

HN StartUps