Find solution
How to Migrate from SQLite DB to MySQL?

If Heroku uses PostgreSQL, how can a local Rails app that utilizes a MySQL or SQLite database work seamlessly with it?

  • I'm a newbie working through the Rails Tutorial and even though it does a great job in teaching the basics of Ruby on Rails, I dont quite understand how a PostgreSQL based service like Heroku, can host MySQL/SQLite apps without problem. I mean, MongoDB apps arent compatible with Heroku (without addons). How come MySQL apps work just fine? What happens behind the scenes to my MySQL app when I press the "heroku db:push" command? Is it superimposed over the compatible part of the PostgreSQL schema? Can I treat Heroku, for all intents and purposes, as a MySQL service, while I work on my local Rails/MySQL app and improve my skills? Can somebody explain in layman's terms?

  • Answer:

    Connection: Heroku replaces your database.yml with their own, which contains information about how to connect to their database servers. Querying: Heroku states that as long as no database specific queries are used, your application will work fine. [1]. Rails applications typically use a layer called ActiveRecord to abstract access to the database. ActiveRecord adaptors for most common database engines are included with Rails by default [2], each of which are written to account for the idiosyncrasies of the database they represent. When your application first starts, Rails looks in your config directory for a database.yml file that defines which ActiveRecord adaptor to use for your application. When you access your data using the various methods of your models, the appropriate queries are executed behind the scenes by the adaptor that was specified before. This makes it possible to swap around different database.yml files on different systems: the application is not concerned with what database engine it is running on. Migrations: ActiveRecord also enables you to move your database scheme from one system to another. Because the schema is represented, not as a sequence of database queries, but rather as ruby code in the form of migration files or the schema.rb file, the application only needs to know which adaptor to use before generating and running the appropriate queries needed to recreate it. [1] http://devcenter.heroku.com/articles/built-on-mysql [2] https://github.com/rails/rails/tree/master/activerecord/lib/active_record/connection_adapters

Joey Carmello at Quora Visit the source

Was this solution helpful to you?

Other answers

The short answer is that an object-relational mapping provides an abstraction in your code over the actual database transactions. The ORM allows you to access a model's properties and relationships without writing the database query yourself. This abstraction is possible because of database drivers, written as gems, which provide the glue that connects the pretty ORM syntax to the underlying guts. So, why does MySQL just work? First, there is a driver for it, and it is interchangeable with the sqlite driver. Second, your config/database.yml file provides configurations for different deployments. When you are running on your home machine, you are likely working off of a development deployment, which defaults to using SQLite, because it is a minimal config database. When you push to heroku, you have "deployed" and heroku may switch your deployment to "production", which will have a different set of config options, such as changing your database drivers. As to why MongoDB does not "just work", this is due to the difference between the database architectures. SQLite, MySQL and PostgreSQL are all SQL databases: they require a rigidly defined schema and structure for all content. Example: every table has columns with a defined data type. All data must conform to a table. By contrast, MongoDB is a "NoSQL" database. NoSQL operates much differently in that it is schema-less (you may want to look up a more in depth answer if you are interested). Because of this difference, the same ORM does not apply cleanly, although other ORM gems are available (datamapper or mongoid?).

Andrew Purcell

Personally I recommend that you use PostgreSQL on Heroku. It is very simple and very powerful. PostgreSQL is a relational database, open source, and very powerful. It has over 20 years of development and has a proved and reliable architecture. It is commonly adopted in many applications because of the following advantages: An open-source SQL standard compliant RDBMS Strong community Strong third-party support Extensible Objective Heroku simplifies the infrastructure of web applications. With its architecture, it is possible to create robust, manageable, and scalable applications according to the needs of your business. The main idea of Heroku is to take away the pain of managing and scaling servers, so you can focus on the development of your product and deliver more functionality to your client, where Heroku is responsible for the infrastructure. You don't need knowledge of servers to build a robust application with millions of users around the world. If you want to Learning about Heroku Postgres I recommend this book: http://bit.ly/1xAfrxn

Patrick Espake

You don't have to use Postgres with Heroku. Other databases provided via https://addons.heroku.com/. For example; https://devcenter.heroku.com/articles/cleardb#configuring-your-ruby-application-to-use-cleardb

Azzie Elbab

Related Q & A:

Just Added Q & A:

Find solution

For every problem there is a solution! Proved by Solucija.

  • Got an issue and looking for advice?

  • Ask Solucija to search every corner of the Web for help.

  • Get workable solutions and helpful tips in a moment.

Just ask Solucija about an issue you face and immediately get a list of ready solutions, answers and tips from other Internet users. We always provide the most suitable and complete answer to your question at the top, along with a few good alternatives below.