Rails And Sqlite3 Database

Ruby On Rails 4 On Windows Mysql Database

Rails And SQlite3 Database

SQlite3 database is default database used when new Ruby on Rails application is created. This can be changed with the -d or --database option with allowed values: mysql, oracle, postgresql, sqlite3, frontbase, ibm_db, sqlserver, jdbcmysql, jdbcsqlite3, jdbcpostgresql, jdbc. We'll stick with default value and create new Rails application:

C:\projects>rails new rwin_book
DL is deprecated, please use Fiddle
      create
      create  README.rdoc
      create  Rakefile
      create  config.ru
      create  .gitignore
      create  Gemfile
      create  app
      create  app/assets/javascripts/application.js
      create  app/assets/stylesheets/application.css
      create  app/controllers/application_controller.rb
      create  app/helpers/application_helper.rb
      create  app/views/layouts/application.html.erb
      create  app/assets/images/.keep
      create  app/mailers/.keep
      create  app/models/.keep
      create  app/controllers/concerns/.keep
      create  app/models/concerns/.keep
      create  bin
      create  bin/bundle
      create  bin/rails
      create  bin/rake
      create  bin/setup
      create  config
      create  config/routes.rb
      create  config/application.rb
      create  config/environment.rb
      create  config/secrets.yml
      create  config/environments
      create  config/environments/development.rb
      create  config/environments/production.rb
      create  config/environments/test.rb
      create  config/initializers
      create  config/initializers/assets.rb
      create  config/initializers/backtrace_silencers.rb
      create  config/initializers/cookies_serializer.rb
      create  config/initializers/filter_parameter_logging.rb
      create  config/initializers/inflections.rb
      create  config/initializers/mime_types.rb
      create  config/initializers/session_store.rb
      create  config/initializers/wrap_parameters.rb
      create  config/locales
      create  config/locales/en.yml
      create  config/boot.rb
      create  config/database.yml
      create  db
      create  db/seeds.rb
      create  lib
      create  lib/tasks
      create  lib/tasks/.keep
      create  lib/assets
      create  lib/assets/.keep
      create  log
      create  log/.keep
      create  public
      create  public/404.html
      create  public/422.html
      create  public/500.html
      create  public/favicon.ico
      create  public/robots.txt
      create  test/fixtures
      create  test/fixtures/.keep
      create  test/controllers
      create  test/controllers/.keep
      create  test/mailers
      create  test/mailers/.keep
      create  test/models
      create  test/models/.keep
      create  test/helpers
      create  test/helpers/.keep
      create  test/integration
      create  test/integration/.keep
      create  test/test_helper.rb
      create  tmp/cache
      create  tmp/cache/assets
      create  vendor/assets/javascripts
      create  vendor/assets/javascripts/.keep
      create  vendor/assets/stylesheets
      create  vendor/assets/stylesheets/.keep
         run  bundle install
DL is deprecated, please use Fiddle
Fetching gem metadata from https://rubygems.org/..........
Fetching version metadata from https://rubygems.org/..
Resolving dependencies...
Installing rake 10.4.2
Using i18n 0.7.0
Installing json 1.8.2
Using minitest 5.5.1
Using thread_safe 0.3.5
Using tzinfo 1.2.2
Using activesupport 4.2.1
Using builder 3.2.2
Using erubis 2.7.0
Using mini_portile 0.6.2
Using nokogiri 1.6.6.2
Using rails-deprecated_sanitizer 1.0.3
Using rails-dom-testing 1.0.6
Using loofah 2.0.1
Using rails-html-sanitizer 1.0.2
Using actionview 4.2.1
Using rack 1.6.0
Using rack-test 0.6.3
Using actionpack 4.2.1
Using globalid 0.3.3
Using activejob 4.2.1
Using mime-types 2.4.3
Using mail 2.6.3
Using actionmailer 4.2.1
Using activemodel 4.2.1
Using arel 6.0.0
Using activerecord 4.2.1
Installing debug_inspector 0.0.2
Installing binding_of_caller 0.7.2
Using bundler 1.9.1
Installing columnize 0.9.0
Installing byebug 4.0.4
Installing coffee-script-source 1.9.1
Installing execjs 2.4.0
Installing coffee-script 2.3.0
Using thor 0.19.1
Using railties 4.2.1
Installing coffee-rails 4.1.0
Using hike 1.2.3
Using multi_json 1.11.0
Installing jbuilder 2.2.12
Installing jquery-rails 4.0.3
Using tilt 1.4.1
Using sprockets 2.12.3
Installing sprockets-rails 2.2.4
Using rails 4.2.1
Installing rdoc 4.2.0
Installing sass 3.4.13
Installing sass-rails 5.0.1
Installing sdoc 0.4.1
Installing sqlite3 1.3.10
Installing turbolinks 2.5.3
Installing tzinfo-data 1.2015.2
Installing uglifier 2.7.1
Installing web-console 2.1.2
Bundle complete! 12 Gemfile dependencies, 55 gems now installed.
Use `bundle show [gemname]` to see where a bundled gem is installed.
Post-install message from rdoc:
Depending on your version of ruby, you may need to install ruby rdoc/ri data:

<= 1.8.6 : unsupported
= 1.8.7 : gem install rdoc-data; rdoc-data --install
= 1.9.1 : gem install rdoc-data; rdoc-data --install
>= 1.9.2 : nothing to do! Yay!

Rails has created basic structure of our application. If you open config\database.yml file in the editor you will see that application is completely configured for SQlite3 database:

# SQLite version 3.x
#   gem install sqlite3
#
#   Ensure the SQLite 3 gem is defined in your Gemfile
#   gem 'sqlite3'
#
default: &default
  adapter: sqlite3
  pool: 5
  timeout: 5000

development:
  <<: *default
  database: db/development.sqlite3

# Warning: The database defined as "test" will be erased and
# re-generated from your development database when you run "rake".
# Do not set this db to the same as development or production.
test:
  <<: *default
  database: db/test.sqlite3

production:
  <<: *default
  database: db/production.sqlite3

All three environments: development, test and production will use different SQlite3 databases named after the name of the corresponding environment with extension .sqlite3.

You can now start application. If you followed all instructions from the book you will see server's log messages in the Command Prompt:

C:\projects\rwin_book>rails server
DL is deprecated, please use Fiddle
=> Booting WEBrick
=> Rails 4.2.1 application starting in development on http://localhost:3000
=> Run `rails server -h` for more startup options
=> Ctrl-C to shutdown server
[2015-03-29 19:22:11] INFO  WEBrick 1.3.1
[2015-03-29 19:22:11] INFO  ruby 2.1.5 (2014-11-13) [i386-mingw32]
[2015-03-29 19:22:11] INFO  WEBrick::HTTPServer#start: pid=26020 port=3000

But if you jumped right to this chapter there are few important things I must emphasize. If you built sqlite3 gem from the sources it is linked against libsqlite3-0.dll and this shared library must be in the path. In the chapter Installing Native Gems we copied it to the C:\tools folder and added this directory to the path. You have to copy sqlite3.exe file to that folder too since we will need it soon.

On the other hand if you just installed binary version of sqlite3 gem it is linked against sqlite3.dll. This is actually the same shared library but with different name. In that case you have two solutions. The first one is to just rename built dll or you can download official version from SQlite3 site and unpack it to the folder that is in the path. Nevertheless SQlite3's shared library and executable must be in the path. This is common mistake of Rails on Windows beginners so keep that on your mind.

Now open Web browser and go to Rails welcome page at http://localhost:3000. It gives you few hints how to start developing Rails application and, if you click on the top link, it will show you information about your application's environment.

So far, so good. But we still haven't do anything with the database so we cannot be sure the SQlite3 gem is working as expected. Open new Command Prompt, go to the root folder of your new Rails application and execute following rake task:

C:\projects\rwin_book>rake db:create

If you get warning messages that database files already exist do not worry since this is just an information that application has found newly created database. Namely for SQlite3 there is no need to create database in advance. Rails will do that for you if database does not exist. Finally start Rails database console and check a list of all tables in the database:

C:\projects\rwin_book>rails dbconsole
DL is deprecated, please use Fiddle
SQLite version 3.8.8.3 2015-02-25 13:29:11
Enter ".help" for usage hints.
sqlite> .tables
sqlite>

Of course we didn't create any table so the list is empty. Time is to create some. Since we are working with Ruby on Rails we will not create tables directly but will do it the Rails way. The most simple approach is to generate model, view and controller with Rails scaffold generator:

C:\projects\rwin_book>rails generate scaffold RubyWinSource name:string \
 author:string url:string
      invoke  active_record
     create    db/migrate/20150329172834_create_ruby_win_sources.rb
     create    app/models/ruby_win_source.rb
     invoke    test_unit
     create      test/models/ruby_win_source_test.rb
     create      test/fixtures/ruby_win_sources.yml
     invoke  resource_route
      route    resources :ruby_win_sources
     invoke  scaffold_controller
     create    app/controllers/ruby_win_sources_controller.rb
     invoke    erb
     create      app/views/ruby_win_sources
     create      app/views/ruby_win_sources/index.html.erb
     create      app/views/ruby_win_sources/edit.html.erb
     create      app/views/ruby_win_sources/show.html.erb
     create      app/views/ruby_win_sources/new.html.erb
     create      app/views/ruby_win_sources/_form.html.erb
     invoke    test_unit
     create      test/controllers/ruby_win_sources_controller_test.rb
     invoke    helper
     create      app/helpers/ruby_win_sources_helper.rb
     invoke      test_unit
     invoke    jbuilder
     create      app/views/ruby_win_sources/index.json.jbuilder
     create      app/views/ruby_win_sources/show.json.jbuilder
     invoke  assets
     invoke    coffee
     create      app/assets/javascripts/ruby_win_sources.coffee
     invoke    scss
     create      app/assets/stylesheets/ruby_win_sources.scss
     invoke  scss
     create    app/assets/stylesheets/scaffolds.scss

Scaffold generator will create model, migration file which will create database table that corresponds to it, controller and few basic views. We will not dive into Rails internals and will focus on the database related parts instead. Going on the Rails way let's start database migration:

C:\projects\rwin_book>rake db:migrate
== 20150329172834 CreateRubyWinSources: migrating =============================
-- create_table(:ruby_win_sources)
   -> 0.0156s
== 20150329172834 CreateRubyWinSources: migrated (0.0156s) ====================

If you want you can also list existing tables in Rails database console:

C:\projects\rwin_book\rails dbconsole
SQLite version 3.8.8.3 2015-02-25 13:29:11
Enter ".help" for usage hints.
sqlite> .tables
ruby_win_sources   schema_migrations

In order to simplify things lets remove some Rails features before we enter some data. Delete following two lines from rwin_book\app\views\layouts\application.html.erb file:

<%= stylesheet_link_tag    'application', media: 'all', 'data-turbolinks-track' => true %>
<%= javascript_include_tag 'application', 'data-turbolinks-track' => true %>

Finally let's add few good Web sources related to Ruby on Windows. Start Rails server and go to http://localhost:3000/ruby_win_sources and click on "New Ruby win source" link, fill in the data and click on the button "Create Ruby win source". On the image below you can see result with my sources.

What we've accomplished so far? We created new Rails application, tested SQlite3 connection through Rails database console, created new model and inserted few records in the database. Now when we know that Rails and SQlite3 are working on Windows, it is time to move on and explore other database adapters.

Ruby On Rails 4 On Windows Mysql Database

Ruby on Windows Guides

by Boško Ivanišević

Rails And SQlite3 Database