Setting up Rails and SQLITE

If you are interested in web, databases and the like then you probably already heard of SQLITE. If not, here’s a quick intro
SQLITE is a database engine that uses just one file per database. It’s tiny, simple to use and, most importantly, very easy to use with Ruby on Rails. All you need is the sqlite ruby gem, which is already included in most Ruby installations.

What is sqlite3? Nothing much than a newer and better version of sqlite. You should use it instead of sqlite if you have the chance.

How do you set it up in Rails?
Let’s say you want to make your own database-driven Rails application. Mysql is a big download, a bit difficult to install, etc. Plus, you don’t really need it; you just want a tiny sql database engine that works with Rails, so you can develop and test your application.
Nothing easier: if not already installed on your machine, then you download and install sqlite3.
Then you make sure you have the latest sqlite bindings in place : gem install sqlite3 .

Hmm.. what else?

oh… the config/database.yml file.

    adapter: sqlite3
    database: db/development.sqlite3

    adapter: sqlite3
    database: db/test.sqlite3


Well now; you are set up. Just go ahead, create your database structure. How? Edit db/schema.rb and start adding tables definitions in a ruby syntax; something like this:

ActiveRecord::Schema.define do

  create_table "users", :force => true do |t|
    t.column :login, :string, :limit => 80
    t.column :cryptpassword, :string, :limit => 40
    t.column :email, :string, :limit => 100
    t.column :created_at, :datetime
    t.column :updated_at, :datetime

  create_table :article, :force => true  do |t|
      t.column :title, :string
      t.column :created_at, :datetime
      t.column :updated_at, :datetime
      t.column :content, :text

Warning! Back up this file also to schema_backup.rb

To import this schema in your database, all you have to do is run rake db_schema_import

You see? All set now. You can start generating your scaffolding and code away.

How to take your data OUT of sqlite?
If at some point in the future, you want to get your data OUT of the sqlite3 database, all you have to do is a sqlite3 dump:

sqlite3 db/development.sqlite3
.output my_sql_dump.sql

PS. I learned about the commands to use by peeking at sqlite3’s help over here
You’ll have to remove some of the lines at the beginning and the end of the generated my_sql_dump.sql file. But the result is a SQL file that can be later imported into MYSQL or other SQL database engine, via classic import commands.

Wel… wasn’t this easy? I sure did learn a lot.

Similar Posts:


Leave a Reply

Your email address will not be published.