This is the third part of the series where we create service to interact with mysql server in rails using mysql2 gem.
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 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 insert and update query with 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 single query while we need to use placeholder (?) and perform 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 statement.
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. Following is happening inside the method:
- Get column names by formatting key part of attributes
- Format column names and add comma (,)
- Format column names and add placeholder (?) then add 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 hash with prepared query and values needed for insert operation - Prepare query with
prepare
method provided by mysql2 gem - Insert record to 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 database withexecute
method.
Prepared Update Query
Insert and update query has only one difference when query is prepared so we want to use 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 operation. - In
prepare_query
, we will addtype
params which can differentiate between insert and update operation. - We will extract prepared statement for insert operation to new method
prepare_insert_query
and addprepare_update_query
for formatting update query. - Depending on
type
param, we will call related method that is formatting the prepared queries. - Update
update
method to perform prepared statement.
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 database. It is getting prepared query and values for updating in database, concept is same as insert
with 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 placeholder for
id
, we will need to add 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 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 error in even one of the operation. Thank you and stay tuned!
Image Credits: Cover Image by Ian Battaglia on Unsplash
Top comments (0)