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_client.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 attributes hash parameter from 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 insert method:
- Call
format_insert_queryto 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 asattributesparameter, which will be sent toformat_insert_queryto get formatted query- Inside
format_insert_query,columnswill have value"first_name,last_name"; key part of theattributeshash,valueswill have"'John','Doe'"; value part of theattributeshash. Lastly, iftablewasusersit will return"INSERT INTO users (first_name,last_name) VALUES ('John','Doe')" - Now the
insertmethod will send the query to the server and the new record will be inserted into the database.
Perform Update Query
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_client.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}"
endExplanation
Only change in update to insert is; it’s also taking id as parameters. id 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=1andattributessame as insert query,format_update_querywill return"UPDATE users SET first_name='John',last_name='Doe' WHERE id=1" - Now the
updatemethod will send the query to the server and update the record withid=1in 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_client, :table, :primary_column
def initialize(table, primary_column)
@table = table
@primary_column = primary_column
end
def fetch_all
perform_mysql_operation do
result = mysql_client.query("SELECT * from #{table}")
puts result.entries
end
end
def fetch_one(id)
perform_mysql_operation do
result = mysql_client.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_client.query(query)
puts 'Record inserted!'
end
end
def update(id, attributes)
query = format_update_query(id, attributes)
perform_mysql_operation do
mysql_client.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_client = connect_to_db
yield
rescue StandardError => e
raise e
ensure
mysql_client&.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
endAfter 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
