syntax.us Let the syntax do the talking
Blog Contact Posts Questions Tags Hire Me

Question:
On Heroku/Rails how do I implement sqlite3?

By default, Heroku does not support my use of the sqlite3 gem when I deploy Rails applications there.

Many use-cases though, find that deploying a read-only Sqlite3 database embedded within the git-repo of my Rails app is useful.

For example, the Heroku free tier limits the number of records I can have in my Postgres database there.

If I architect my Rails app so that I use Postgres for update-able data and Sqlite3 for read-only data, I can squeeze more value out of Heroku.

Another use-case might ask me to check some read-only data into Git version control so I can track who changes it and when.

This use-case is addressed nicely if I place the data into a Sqlite3 database (which is just a simple binary file) and then commit the database to the Rails git-repo.

To start my deployment of Sqlite3 enabled Rails to Heroku, I read this post:

On Heroku, how do I install the sqlite3 gem?

Next, I ran these shell commands:

cd /tmp/
ruby -v
rails -v
rails new sqlite103 -d postgresql
cd sqlite103
sed -i '1a ruby "2.1.5"'   Gemfile
sed -i '2a gem  "sqlite3"' Gemfile
mkdir myvendor
mkdir heroku_buildpack_scripts
ln -s heroku_buildpack_scripts hbs
bundle install

cat > app/models/question.rb <<EOF
class Question < ActiveRecord::Base
  establish_connection({:adapter=>"sqlite3",:database=>"db/questions.db"})
end
EOF

cd db
sqlite3 questions.db <<EOF
create table questions(id integer, question string);
insert into questions(id,question) values(1,'Is Ruby easy to learn?');
EOF
Then, I added syntax and files to my Rails app which would force the Heroku buildpack to install Sqlite3 and the sqlite3 gem when I deploy my app to Heroku.

How?
First, I got a copy of the sqlite software from here:

http://www.sqlite.org/2014/sqlite-autoconf-3080702.tar.gz

And I put it here:
/tmp/sqlite103/myvendor/sqlite-autoconf-3080702.tar.gz
Using these shell commands:
cd /tmp/sqlite103/myvendor/
wget http://www.sqlite.org/2014/sqlite-autoconf-3080702.tar.gz
Then, I placed a shell script in directory:
/tmp/sqlite103/heroku_buildpack_scripts/
#!/bin/bash

# hbs/dothis.bash

# I use this script to help me enhance the ability of 
# heroku-buildpack

# heroku-buildpack is software used by heroku to help me deploy applications.

rails_root=`pwd`

echo rails_root is
echo $rails_root
cd ${rails_root}/myvendor/

# I should install sqlite3 software locally under Rails.root

mkdir -p ${rails_root}/myvendor/sqlite3
tar zxf  ${rails_root}/myvendor/sqlite-autoconf-3080702.tar.gz 
cd sqlite-autoconf-3080702/
./configure --prefix=${rails_root}/myvendor/sqlite3
make
make install

# Heroku sets GEM_HOME to a relative path so I cd to the right place.
cd ${rails_root}
# I should be able to gem install sqlite3 now
gem install sqlite3 -- --with-sqlite3-dir=${rails_root}/myvendor/sqlite3
gem list    sqlite3

exit
Next, I ran these shell commands:
cd /tmp/sqlite103
git init
git add .
git commit -am hello
heroku apps:create sqlite103 --stack cedar --buildpack https://github.com/danbikle/heroku-buildpack-ruby
git push heroku master
Then I queried the questions table in my sqlite3 database:
dan@hp /tmp/sqlite103 $
dan@hp /tmp/sqlite103 $ heroku run console
Running `console` attached to terminal... up, run.8955
Loading production environment (Rails 4.1.8)
irb(main):001:0> myc = Question.connection
snip...
irb(main):002:0> questions = Question.find_by_sql('select question from questions')
<= Question.find_by_sql('select question from questions')
=> [#<Question question: "Is Ruby easy to learn?">]
irb(main):003:0> quit
dan@hp /tmp/sqlite103 $
dan@hp /tmp/sqlite103 $
dan@hp /tmp/sqlite103 $
So, that is a demo of how to deploy a Sqlite3 read-only database to a Rails app running on Heroku.
syntax.us Let the syntax do the talking
Blog Contact Posts Questions Tags Hire Me