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
to get a query that can directly be used for the insert operationformat_insert_query
- Insert to database
Practically:
will be received as the{first_name: 'John', last_name: 'Doe'}
parameter, which will be sent toattributes
to get formatted queryformat_insert_query
- Inside
,format_insert_query
will have valuecolumns
key part of the"first_name,last_name";
hashattributes
will have thevalues
value part of the"'John','Doe'";
hash.attributes
- Lastly, if
wastable
it will returnusers
"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 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
to 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
andid=1
same as insert query,attributes
will returnformat_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 withupdate
in the database.id=1
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.