Table of Contents
This is the third part of the series where we create a service to interact with MySQL server in rails using mysql2 gem. You can read other parts by following the links below:
- Interact with MySQL server using mysql2 gem – select operations
- Interact with MySQL Server using mysql2 gem [Part 2] – Insert and Update Operations
Requirements
- [x] Service to connect with external MySQL server
- [x] Perform basic query: select, insert and update
- [ ] Prepared statement
- [ ] Perform transaction
- [ ] Perform join query
In the previous two articles, we created a service and added methods to help us perform select, insert and update operations. Today we will be looking at performing prepared statements to MySQL server using mysql2 gem.
In this blog
We will be learning the following in this blog:
- Perform prepared statement
Prepared Statement
From Wikipedia:
In database management systems (DBMS), a prepared statement or parameterized statement is a feature used to execute the same or similar database statements repeatedly with high efficiency. Typically used with SQL statements such as queries or updates, the prepared statement takes the form of a template into which certain constant values are substituted during each execution.
What it means for our service is we will replace the actual value in the insert and update query with a question mark(?) and send the actual values only the second time. Let’s refactor the code.
Prepared Insert Query
Here is what we will do for supporting prepared statements in our insert operation:
- Remove the method
format_insert_query
because it is dumping all attributes and values in a single query while we need to use a placeholder (?) and perform an operation in two phases; one, prepare the query, and two, send values to create in database. - Create
prepare_query
method which will format the query as needed and provide us the hash with query and values. - Update
insert
method to perform prepared statements.
Code
def insert(attributes)
query = prepare_query(attributes)
perform_mysql_operation do
mysql_connect.prepare(query[:prepared_query])
mysql_connect.execute(*query[:values])
puts 'Record inserted!'
end
end
private
def prepare_query(attributes)
raise 'Attributes cannot be empty' if attributes.empty?
keys = attributes.keys
columns = keys.join(', ')
substituted_columns = keys.map { '?' }.join(', ')
prepared_query = "INSERT INTO #{table} (#{columns}) VALUES (#{substituted_columns})"
values = attributes.values
{
prepared_query: prepared_query,
values: values
}
end
Explanation
prepare_query
is taking attributes
hash parameter from insert
method and returning hash with prepared query and values to insert to database. The following is happening inside the method:
- Get column names by formatting key part of attributes
- Format column names and add a comma (,)
- Format column names and add placeholder (?) then add a comma (,)
- Prepare insert query
- Collect only values of attributes hash
- Return a new hash with prepared query and values
Following is happening inside insert
method:
- Call
prepare_query
which returns a hash with prepared query and values needed for the insert operation - Prepare query with
prepare
method provided by mysql2 gem - Insert record to the database with
execute
method
Practically:
{first_name: 'John', last_name: 'Doe'}
will be received asattributes
parameter, which will be sent toprepare_query
to get hash having formatted query and values- Inside
prepare_query
,columns
will have"first_name, last_name"
,substituted_columns
will have"?, ?"
i.e. the number of values that will be inserted. Iftable
wasusers
,prepared_query
will be"INSERT INTO users (first_name, last_name) VALUES (?, ?)"
andvalues
will have['John', 'Doe']
- After receiving hash from
prepare_query
,insert
method will now prepare the query withprepare
method and insert to the database withexecute
method.
Prepared Update Query
Insert and update query has only one difference when the query is prepared so we want to use the same prepare_query
method used in insert operation/. To do that we will update the code and do the following:
- Remove the method
format_update_query
. - Update
prepare_query
method to support both insert and update operations. - In
prepare_query
, we will addtype
params that can differentiate between insert and update operation. - We will extract the prepared statements for insert operation to the new method
prepare_insert_query
and addprepare_update_query
for formatting update query. - Depending on
type
param, we will call the related method that is formatting the prepared queries. - Update
update
method to perform prepared statements.
Code
def update(id, attributes)
query = prepare_query(attributes, 'update')
values = query[:values]
values.push(id)
perform_mysql_operation do
mysql_connect.prepare(query[:prepared_query])
mysql_connect.execute(*values)
puts 'Record Updated!'
end
end
private
def prepare_insert_query(keys)
columns = keys.join(', ')
substituted_columns = keys.map { '?' }.join(', ')
"INSERT INTO #{table} (#{columns}) VALUES (#{substituted_columns})"
end
def prepare_update_query(keys)
columns = keys.map { |key| "#{key} = ?" }.join(', ')
"UPDATE #{table} SET #{columns} WHERE #{primary_column} = ?"
end
def prepare_query(attributes, type)
raise 'Attributes cannot be empty' if attributes.empty?
keys = attributes.keys
prepared_query = type == 'insert' ? prepare_insert_query(keys) : prepare_update_query(keys)
values = attributes.values
{
prepared_query: prepared_query,
values: values
}
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 the database. It is getting prepared query and values for updating in the database, the concept is same as insert
with a change in query and values where id
value is added to the values that are returned from prepare_query
hash.
Practically:
- If we are providing
id=1
andattributes
same as insert query,prepare_query
will return query"UPDATE users SET first_name = ?,last_name = ? WHERE id = ?"
and values['John', 'Doe']
- Since we also have a placeholder for
id
, we will need to add the id to the values, so values will now contain['John', 'Doe', 1]
- After this, as with insert operation, first queries are prepared and then values are updated in 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_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 = prepare_query(attributes, 'insert')
perform_mysql_operation do
mysql_connect.prepare(query[:prepared_query])
mysql_connect.execute(*query[:values])
puts 'Record inserted!'
end
end
def update(id, attributes)
query = prepare_query(attributes, 'update')
values = query[:values]
values.push(id)
perform_mysql_operation do
mysql_connect.prepare(query[:prepared_query])
mysql_connect.execute(*values)
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 prepare_insert_query(keys)
columns = keys.join(', ')
substituted_columns = keys.map { '?' }.join(', ')
"INSERT INTO #{table} (#{columns}) VALUES (#{substituted_columns})"
end
def prepare_update_query(keys)
columns = keys.map { |key| "#{key} = ?" }.join(', ')
"UPDATE #{table} SET #{columns} WHERE #{primary_column} = ?"
end
def prepare_query(attributes, type)
raise 'Attributes cannot be empty' if attributes.empty?
keys = attributes.keys
prepared_query = type == 'insert' ? prepare_insert_query(keys) : prepare_update_query(keys)
values = attributes.values
{
prepared_query: prepared_query,
values: values
}
end
end
end
end
After this our service should be able to perform all basic and prepared operations in and to the external MySQL server. Next week we will learn to perform transaction operations i.e. we will be performing multiple queries and rollback all operations if there is an error in even one of the operations. Thank you and stay tuned!