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 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 .