This is the second part of the series where we create 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 previous blog, we created a service and also added 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 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. 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_query
to get a query that can directly be used for insert operation - Insert to database
Practically:
-
{first_name: 'John', last_name: 'Doe'}
will be received asattributes
parameter, which will be sent toformat_insert_query
to get formatted query - Inside
format_insert_query
,columns
will have value"first_name,last_name"
; key part of theattributes
hash,values
will have"'John','Doe'"
; value part of theattributes
hash. Lastly, iftable
wasusers
it will return"INSERT INTO users (first_name,last_name) VALUES ('John','Doe')"
- Now the
insert
method will send the query to server and new record will be inserted to the database.
Perform Update Query
Update query is used to update existing record 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}"
end
Explanation
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 database. It is getting formatted query and updating in database, concept is same as insert
with only change in query.
format_update_query
has slight difference to that of format_insert_query
; it is converting attributes
differently, let's see that with practical example below.
- If we are providing
id=1
andattributes
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 server and update the record withid=1
in the database.
Final Code
If you have been following the tutorial from part 1, you will have 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
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 query with prepared statement which helps us to avoid sql injection issues.
Image Credits: Cover Image by Kelvin Yang on Unsplash
Top comments (0)