This article will cover the basics of working with MySQL and Ruby on Rails.

---

Published on:November 24, 2013

---

Introduction

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 install -y 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. 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: user
  password: password
  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.fields:

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!