Exporting MySQL to sqlite3

Rails
1

I’ve been learning/developing with MySQL, and I’ve also taken up using
Git. Firstly, am I right in presuming that I could rely on Git to
version a database if I’m using sqlite3, i.e. in a file within the
Rails app folder?

If so, can I easily export the MySQL database and all the dev data and
import it straight in to sqlite3 without too much difficulty? Someone
suggested edge Rails has dbconsole, but I don’t really want to deal
with moving to edge Rails on my first proper app. Do I have any other
options for this?

2

Neil wrote:

I’ve been learning/developing with MySQL, and I’ve also taken up using
Git. Firstly, am I right in presuming that I could rely on Git to
version a database if I’m using sqlite3, i.e. in a file within the
Rails app folder?

Git must be physically capable of storing a binary file. However…

The only way you should ever consider versioning a Rails database is
with
migrations. You should be able to delete any database, reconstitute it
with
rake db:migrate, and pass all tests at any time.

At my day-job, our CruiseControl.rb installation does that each time we
integrate.

If so, can I easily export the MySQL database and all the dev data and
import it straight in to sqlite3 without too much difficulty? Someone
suggested edge Rails has dbconsole, but I don’t really want to deal
with moving to edge Rails on my first proper app. Do I have any other
options for this?

The data in your production database should be sacred, and stored on the
actual production server (with its own backups and such).

Your development and test environments should use test/fixtures/*.yml
files.
Use this rake task to build a set of fixtures, if you don’t have one yet

and remember to delete all the records you don’t need from your
development
database first.

namespace :test do
desc ‘Create YAML test fixtures from data in an existing database.
Defaults to development database. Set RAILS_ENV to override.’

task :extract_fixtures => :environment do
sql = “SELECT * FROM %s”
skip_tables = [“schema_info”]
ActiveRecord::Base.establish_connection
(ActiveRecord::Base.connection.tables - skip_tables).each do
|table_name|
i = “000”
File.open("#{RAILS_ROOT}/tmp/#{table_name}.yml", ‘w’) do |file|
data = ActiveRecord::Base.connection.select_all(sql %
table_name)
file.write data.inject({}) { |hash, record|
hash["#{table_name}_#{i.succ!}"] = record
hash
}.to_yaml
end
end
end

end

Don’t be fooled by imitations. Some versions of test:extract_fixtures
will
overwrite your test/fixtures folder. That is absurd, because you should
already have fixtures in there.

–
Phlip

3

Phlip, thanks for the excellent response. And sorry for my belated
response - Google decided not to inform me.

I’m finally working round this problem. I have an app running on
sqlite, tested via rspec, and hosted by GitHub. There’s another, more
experienced developer working on the app with me, but neither of us
have collaborated with another Rails developer before, so there’s a
lot of learning going on. We haven’t tried out using fixtures for the
development database yet - but obviously it is the way to go, and your
rake task should prove very useful.

There’s only one possible issue I can think of. We have site wide
pages which, for now at least, are stored in a Pages model. Should we
create fixtures for these too (considering they won’t change much and
hold no core functionality), or does there come a point when you can
have too much fixture data or the data required in some models just
shouldn’t be kept in fixtures? I can imagine a situation where people
would forget about a fixture containing important copy/text when all
the rest simply contain placeholder content, like ‘lorem ipsum’
comments or fake avatars…