Playing Rails Migrations

Last time I keep on playing Ruby on Rails, quite nice tool but migrations are something a little bit… well… Houston, we’ve got a problem. Databases in Rails are treated as a simple stupid bag full of data put here and there.
Main problems that I met during those simple migrations:

• No use of checks in databases.

I wrote a function that allows me adding checks from migrations, what of course could not be portable, but such a portability is a myth and can be used only within really small databases. What’s more… Rails Migrations should have such a function built in.

• No use of foreign keys.

I made some function for that on my own but that causes another problem.

• Problems with Slony replication.

As all DDL queries have to be run through slonik.execute, migrations can just stop migrating data and make slave databases useless.

• Migration down.

Default drop_table function performs just a simple `drop table`. Last time I tried to make a down migration on a table that has some foreign key. Of course that didn’t success as the proper way is to make `drop table cascade`. No way to do that otherwise than write a function on my own or place a query in a string and use the execute function. All ways of solving that problem are stupid and that’s not what migrations are about.

• No use of triggers.
  • the application is written in something other than Ruby,
  • it uses hand written sql queries for making database operations faster (e. g. while importing data or other batch loads).

All triggers are made in models classes – Rails gurus just say:

don’t make that in database, just write all that in the model class.

That’s nice for catching possible errors in Rails while manipulating data. But then suddenly appears another application that wants to use that database and there is a problem when all constraints are defined only in the Active Record classes. The reasons that I can find for not using AR classes in other applications are mainly two:

• Getting to larger tables.

Migrations for large tables are a kind of misunderstanding. Creating migration that adds a column to a table and sets values for all records (using default value or by counting that value) sometimes is not so easy. Example from my DBA life: Just imagine a production database (PostgreSQL) with not so big number of records… let’s say about 3 million of adverts that should be shown on a website. Those adverts are changed all the time by users, importers and some cron jobs. Running there any sql like this:

ALTER TABLE adverts
ADD COLUMN cf HSTORE
NOT NULL
DEFAULT ''::HSTORE;

ends up with updating all records in that table what causes locking them for writes. In fact during that everything stops. What’s more, in the real life example there was another query that sets the column some counted value for all records. All operations could last many hours. The quite nice solution that we found was to split that migration into some operations:

ALTER TABLE adverts
ADD COLUMN cf HSTORE;

after that we wrote a trigger that set proper value for new and changed records and run a script for setting the column for all records but using small portions, sth. like this:

UPDATE adverts
SET cf = …
WHERE id IN
  (
    SELECT id
    FROM adverts
    WHERE cf IS NULL
    LIMIT 100
  );

The limit value was set to a proper value so the query wouldn’t last longer than 1s. That script run for about four days and after such a long time all data was OK so we could finish that with these queries:

ALTER TABLE adverts
  ALTER COLUMN cf
  SET NOT NULL;
ALTER TABLE adverts
  ALTER COLUMN cf
  SET DEFAULT ''::HSTORE;

Unfortunatelly that’s not possible using migrations.

• What next?

I think that migrations are written by someone that is not DBA and doesn’t understand what databases are for. Migrations are good for some small databases and during tests and developing but not on production databases. And of course not for big production databases either. Of course some argue that there is not a problem, migrations are the best thing since sliced bread and what I write is just a heresy because you can always use migrations and for such big databases write queries on your own. That’s true but the point is that I need just one tool for playing with databases, otherwise there will just be many different scripts and files with migrations, sqls tools for running migrations and sqls from files and so on. That’s what I suffered from for years and I’d like to have all of that in one place using one tool.

• Something else?

I just need something else and I think that the only way to do that is to write that on my own and I will… as soon as I find some time for that.

Bookmark It

Related posts:

1. Why Ruby on Rails Migrations Don’t Work Ruby on Rails Migrations Migrations in Ruby on Rails provide...
2. Why Use ORMs? Why Use ORMs? ORM aka Object Relational Mapping is a...
3. Ruby, Rails… AWESOME I really don’t get it, why almost each post or...
4. Testing Database – Small Reply On Ovid’s blog I found lately this entry. First of...
5. PostgreSQL Collation – part 1 PostgreSQL is (IMHO) much better than MySQL but unfortunately it ...
6. What a big database is and why there isn’t anything like that. Yea, simple question. Usually I hear something like this: “Help...