By default, Rails utilizes SQLite 3 as its database engine. While this is fine for most tasks, sometimes it might be necessary to use a real database server. In this article we will cover the basics of working with one such server, MySQL. The primary means to do this is by using the mysql2 gem. The mysql2 gem provides a simple, modern, fast means to access MySQL database servers.
Installation on Linux
Before we begin, we need to install a number of prerequisites. If you are on Linux, you will need to install the MySQL development packages prior to installing the gem. Depending on your system, this will be called libmysqlclient-dev, mysql-devel, or something similar.
On Ubuntu and other debian based systems such as Linux Mint, this is called libmysqlclient-dev and can be installed with the following command:
sudo apt-get install libmysqlclient-dev
On Redhat based systems like Amazon Linux, CentOS, and Fedora, this package is called mysql-devel and can be installed with the following command:
sudo yum install mysql-devel
Installation on a Mac
Installation on a Mac varies depending on whether you use Homebrew or not. If you have issues, take a look at this blog post.
Installation on Windows
If you are on Windows you will need the MySQL C connector. Typically you will want the Windows 32 bit zip archive, however if you are running 64 bit Ruby (not common), you will want to grab the 64 bit version of the connector. You can obtain the MySQL C Connector from here. Once you download it, extract it to an easily accessible folder. Then, open a command prompt and type the following command, replacing C:\path\to\your\connector with the location where you extracted the MySQL C Connector to.
gem install mysql2 -- --with-mysql-dir=c:\path\to\your\connector
Usage in Ruby on Rails
Using the mysql2 gem in Ruby on Rails is easy. You simply need to modify your database config file to connect to your new database. An example database config file is listed below. You can take this example and modify it to suit your needs.
development: adapter: mysql2 database: myapp_development pool: 20 encoding: utf8 username: user password: password host: localhost test: adapter: mysql2 database: myapp_test pool: 20 encoding: utf8 username: user password: password host: localhost production: adapter: mysql2 database: myapp pool: 20 encoding: utf8 username: dbuser password: dbpassword host: mydomain.com
After you change the database.yml file, be sure to restart your Rails server if necessary. Beyond that you should be all set.
Usage Outside of Rails
You don't need to use Rails. You can also do queries at the irb prompt or in Ruby scripts. First, connect to the database:
require 'mysql2' client = Mysql2::Client.new(host: "mydomain.com", username: "myuser", password: "mypassword", database: "ecommerce")
Now that you are connected, you can do basic queries:
results = client.query("SELECT * FROM products WHERE id = 54")
You can also escape strings:
client.escape("O'Donnel") # returns "O\\'Donnel"
You can iterate over the results easily since they are hashes:
results = client.query("SELECT * FROM products") results.each do |result| puts result["name"] end
Finally, you can get the headers using
results = client.query("SELECT * FROM products") results.fields # returns an array with the field names ["id", "name", "weight", "price",...]
For more information, please be sure to check out the Mysql2 Github Page. Thanks for reading!