Hooking up a Rails App with an Existing MySQL Database

Context:

Having worked with SQL, get up and running with MySQL which is a relational database management system shouldn't have to be a problem. For this exercise I am thinking of using Rails as the backend; ActiveRecord does a great job in handling the abstraction for SQL. After a successful connection has been established, I will use Ember.js for the client side. And finally host it on Heroku.

If someone is familiar with these technologies, you can see we're going against most of the prescribed database conventions: Rails (SQLite), Ember.js(JSON API), Heroku (Postgresql). But swimming against the current or configuring when we should be following convention, is a great way to build muscle.

Introduction

Once the Rails app successfully accesses the data, it would be sufficient to slap the front-end in bootstrap, and work with some custom jQuery and whatever other libraries you want to use.

Suggestions: D3, Polymer.js, Charts.js, Chartlist.js

But I'd prefer to force Ember to bend to my needs, it’s a client side API that better deliver. It runs in the browser and it needs to run on an external API.

Ember Options: Is a Rails API the only way to access the MySQL data in Ember? No, there are a couple of different ways. Brainstorming different paths:

• Fake the data using mirage;
• Scrape/Download the data, and add it manually to the EmberStore;
• Can use [Google spreadsheet as a JSON backend](https://coderwall.com/p/duapqq/use-a-google-spreadsheet-as-your-json-backend);
• Can also download data and [import as CSV data which reads in Ruby](https://www.driftingruby.com/episodes/importing-and-exporting-csv-data)

I'm sure there are more optimal and crazier approaches to take. But Rails still seems like a solid backend option. Working with Ember might not be the best approach for this, but I definitely want to solidify my understanding of the framework. And if the JSON looks right coming in, then Ember don't care.

Step: Install MySQL on Mac

• Need an Oracle account set up to download MySQL.
  • Download the .dmg package locally on your system. The recommended download will take about 1.6GB worth of space. There are lighter options available.
• Access MySQL database on Terminal (Mac Users)

$ mysql.server start (note 'mysql2' name needed)

=>Should get a success message

$ require 'mysql2' (this is IRB)

=> Should get a true message.

A common gotcha in Mac, is that sometimes you need to turn on the MySQL server instance by going to:
System Preferences -> MySQL -> Start MySQL Server

All that above is good to know. BUT: If you have Homebrew you can ignore tha madness. In fact never install software of your Mac if you can use Homebrew instead. I rue the hours I waste when I forget I have Homebrew:
$brew install mysql

$brew services start mysql

$brew services stop mysql

Step: Integrating Rails with MySQL

The easiest Rails approach would be to use the new Rails 5 API. It makes creating an API an easy and fantastic job:
https://github.com/rails-api/rails-api
Creating a Rails API - official docs
https://github.com/rails-api/activemodel_serializers (also worth reading)

Another interesting tidbit to read is the Rails Pull Request 21110:
https://github.com/rails/rails/pull/21110
"Add a native JSON data type support in MySQL"- this was a recent PR introduced into Rails. MySQL supports the JSON data type, only for Rails 5 though.

However, instead of going the API route I can live with the bloat of a full-scale Rails app. Plus there are a few templating techniques I'd like to test before passing all the data to Ember.

$ rails new name --database=mysql

Good to know rails supports MySQL out of the box, and has some adapters and plugins for our use. Main changes go into the development portion of the config/database.yml. To get the connection to work you only need to work in one file: config/database.yml

Where Ember feeds on the JSON API, Rails defaults on using SQLite. Creating a new MySQL DB from scratch is a lot easier and cleaner. But here we want to get a pre-existing server hooked up with Rails.

The official documentation shows it's a pretty straight-forward process.
http://guides.rubyonrails.org/configuring.html#configuring-a-database

Usage of the Mysql gem library. (Documentation is helpful)
https://github.com/brianmario/mysql2

I prefer to use Homebrew for all the heavy lifting. Suggested approach:

Rails recommends databases at 3 stages: Development, Production & Test

Rails Console/Ruby IRB
The console is your friend. Play around with the database, especially if you plan on creating objects to access them. If so, I recommend using the adapter design pattern. One thing to definitely check up are the table names.

Another useful thing about the console is for being able to test the connection and also play around safely with the data. See what queries you can make, and the logic your app can display.

$ connection = ActiveRecord::Base.establish_connection(adapter: 'mysql2', database: '', host: '', username: '', password: '')

=> Saving var of connection

$ ActiveRecord::Base.connection.tables

=> output of table names

$ results = ActiveRecord::Base.connection.execute('select * from table-name')

Or to iterate through the results:

Also tried- and it works!

$ require 'mysql2'

$ client = Mysql2::Client.new(host: "", username: "", password: "", database: "")

$ results = client.query("SELECT * FROM table-name")

=> You've just saved your whole DB in a variable, as a MySQL result.

So a connection has been made.
$ results.each{|r| puts r}

=> Depending on the size of your DB, you should now see a massive hash that is your data. Hashes work in key-value pairs.

$data = results.first

And now, can access via keys because results is a Mysql hash object of all the DB results.

Existing MySQL into Rails

After you've created your rails app, run $rails s and you shouldn't see any error messages if everything hooked alright.

Some of the online tutorials I referenced: