Migrations refactoring

Good day to you, colleagues. Recently I’ve landed a new job as a Rails developer. Unfortunately I was not quite aware for what I’m signing for—it is absolute hell. But it’s ok, I’m a man I can handle it, with more or less of your help.

Particularly I need help with migrations. To my shame, I have to admit I accumulated knowledge debt on such topics as SQL and DBs. I was able to guess how DB is being set up for my particular case.

  1. rake db:create — obviously creates bases for desired environment
  2. mysql < db/structure.sql — not sure why the hell I need this, but otherwise Rails won’t start
  3. rake db:migrate — the longest and the most risky part of DB set up

First question: why do I need to apply that structure.sql file? Can I avoid it, by transforming it into a migration?

Second: currently the migrate dir took around 1.2 Mb on disk with 290 items in it. Is it normal? Should I refactor it? May I delete all previous migrations, and write one which represents current state of tables?

Third: I’m fan of MongoDB, please dissuade me from moving toward this seemingly perfect solution.

I’d very much obliged for each response, please direct me and share some knowledge. Thank you in advance!

Congrats on the new job @ubermonkey!

There are a few components to the way Rails handles relational databases.

Schemas

SQL databases require their structure to be specified up front. For example “the users table has two columns, an integer column named ‘age’ and a boolean column named ‘admin’”. This specification is called the database’s schema. Without it, you just have a blank database.

You can build this schema manually using SQL commands like CREATE TABLE. This is incredibly tedious and you would need to do it all over again every time you create a new database. To make the process easier, Rails captures a snapshot of your schema in a file named either schema.rb or structure.sql.

schema.rb contains a Ruby DSL describing your database schema while structure.sql is an actual SQL dump of your schema. Because of this, structure.sql allows you to use complex custom SQL that is not supported by the Ruby DSL.

You can load the schema by running rake db:schema:load (this is the equivalent to you manually reading the structure.sql file into the mysql command). Hurray for automation! No more need to manually create our database schemas anymore.

In the beginning…

When you start a project, you have a blank database and no schema. You could build your schema manually with CREATE TABLE and then have Rails generate a schema file from your database. However, Rails gives us migrations which provide a nice DSL for creating a schema. You can run rake db:migrate execute the migration script and create the schema in your database. This also generates the schema.rb / structure.sql file for you.

Changing the schema

Your schema is not a static thing. You want to change the columns in a table, add new tables, remove old tables, etc. You could do this manually on every database with commands like ALTER TABLE but you can use Rails’ migrations to do this in a scripted way using a Ruby DSL. You can also execute arbitrary SQL as part of the migration if needed. The migration system gives you a sort of versioning system for your schema and allows you to roll back to a previous schema state.

Some migrations are written with dependencies on other code in the application such as a model. This can lead to errors when the migration is run years later and the method invoked on your model doesn’t exist anymore.

Actually answering your questions

  1. I’m not sure why you need to load the schema instead of migrating? You should be able to run rake db:create and then rake db:migrate.
  2. On an older project that’s not abnormal. Unless the value of changing/refactoring the the migrations is high, I’d suggest spending your refactoring time elsewhere in the app.
  3. MongoDB is not a perfect solution :slight_smile: . Are you attracted to it’s schema-less approach due to pain with schema/migrations here? The type of data modeled by most Rails applications generally works best with a schema. Document stores such as MongoDB are best suited for very different types of data than Relational databases (such as MySql). Things get tricky in Mongo when your document is not self-contained and you need to reference other documents.

Some useful links

1 Like