# Exporting MySQL to sqlite3

**URL:** <https://www.ruby-forum.com/t/exporting-mysql-to-sqlite3/137931>\
**Category:** Rails\
**Created:** [May 18, 2008, 2:43pm UTC](https://www.ruby-forum.com/t/exporting-mysql-to-sqlite3/137931 "2008-05-18T14:43:45Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![neil](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/n/839c29/32.png) [@neil](https://www.ruby-forum.com/u/neil)\
**Post date:** [May 18, 2008, 2:43pm UTC](https://www.ruby-forum.com/t/exporting-mysql-to-sqlite3/137931/1 "2008-05-18T14:43:45Z")

</div>

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?

---

<div class="post-metadata">

**Author:** ![neil](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/n/839c29/32.png) [@neil](https://www.ruby-forum.com/u/neil)\
**Post date:** [May 18, 2008, 3:14pm UTC](https://www.ruby-forum.com/t/exporting-mysql-to-sqlite3/137931/2 "2008-05-18T15:14:21Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![neil](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/n/839c29/32.png) [@neil](https://www.ruby-forum.com/u/neil)\
**Post date:** [June 27, 2008, 9:56pm UTC](https://www.ruby-forum.com/t/exporting-mysql-to-sqlite3/137931/3 "2008-06-27T21:56:02Z")

</div>

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…