Ruby on Rails issue about Sqlite 3 Gem

Ruby on Rails

Sqlite 3 Gem

I'm writing about the issues that I found when trying to install a new Ruby on Rails environment in Windows. Especially about the errors, I got trying to install SQLite3 Gem for a new project.
I have installed Ruby on Rails multiple times on the past using as always Ruby Installer from https://rubyinstaller.org/ and Rails Installer (Ruby 2.3 and Ruby 2.2) from http://railsinstaller.org
but this time it did not go so smoothly as always.
With SQLite 3 already installed version 3.26.0

C:\Sites>sqlite3 -version
3.26.0 2018-12-01 12:34:55 bf8c1b2b7a5960c282e543b9c293686dccff272512d08865f4600
fb58238b4f9

I installed Rails with railsinstaller-3.4.0.exe and after this, installed Ruby Devkit rubyinstaller-devkit-2.6.1-1-x64.exe. 

C:\Sites>ruby -v
ruby 2.6.1p33 (2019-01-30 revision 66950) [x64-mingw32]

C:\Sites>rails -v
Rails 5.2.2

Open the Command Terminal and ran the famous 

C:\Sites>rails new blog

It started well but I got an error in some minutes later

Fetching sqlite3 1.4.0
Installing sqlite3 1.4.0 with native extensions
Gem::Ext::BuildError: ERROR: Failed to build gem native extension.

current directory:
C:/RailsInstaller/Ruby2.3.3/lib/ruby/gems/2.3.0/gems/sqlite3-1.4.0/ext/sqlite3
C:/RailsInstaller/Ruby2.3.3/bin/ruby.exe -I
C:/RailsInstaller/Ruby2.3.3/lib/ruby/site_ruby/2.3.0 -r
./siteconf20190217-4316-6m19zc.rb extconf.rb
checking for sqlite3.h... no
sqlite3.h is missing. Install SQLite3 from http://www.sqlite.org/ first.
*** extconf.rb failed ***
Could not create Makefile due to some reason, probably lack of necessary
libraries and/or headers.  Check the mkmf.log file for more details.  You may
need configuration options.

Provided configuration options:
        --with-opt-dir
        --without-opt-dir
        --with-opt-include
        --without-opt-include=${opt-dir}/include
        --with-opt-lib
        --without-opt-lib=${opt-dir}/lib
        --with-make-prog
        --without-make-prog
        --srcdir=.
        --curdir
        --ruby=C:/RailsInstaller/Ruby2.3.3/bin/$(RUBY_BASE_NAME)
        --with-sqlcipher
        --without-sqlcipher
        --with-sqlite3-config
        --without-sqlite3-config
        --with-pkg-config
        --without-pkg-config
        --with-sqlcipher
        --without-sqlcipher
        --with-sqlite3-dir
        --without-sqlite3-dir
        --with-sqlite3-include
        --without-sqlite3-include=${sqlite3-dir}/include
        --with-sqlite3-lib
        --without-sqlite3-lib=${sqlite3-dir}/lib

To see why this extension failed to compile, please check the mkmf.log which can

be found here:

C:/RailsInstaller/Ruby2.3.3/lib/ruby/gems/2.3.0/extensions/x86-mingw32/2.3.0/sql
ite3-1.4.0/mkmf.log

extconf failed, exit code 1

Gem files will remain installed in
C:/RailsInstaller/Ruby2.3.3/lib/ruby/gems/2.3.0/gems/sqlite3-1.4.0 for
inspection.
Results logged to
C:/RailsInstaller/Ruby2.3.3/lib/ruby/gems/2.3.0/extensions/x86-mingw32/2.3.0/sql
ite3-1.4.0/gem_make.out

An error occurred while installing sqlite3 (1.4.0), and Bundler cannot continue.

Make sure that `gem install sqlite3 -v '1.4.0' --source 'https://rubygems.org/'`

succeeds before bundling.

In Gemfile:
  sqlite3

C:\Sites>

So about it these are the solutions I tried and DIDN'T worked out for me but maybe for other cases they're useful

1* Download and compile the files from https://www.sqlite.org/download.html 
The official page have a well described explanation of how and what files compile.
https://www.sqlite.org/howtocompile.html
Also for those that are starting 

If using the MinGW compiler, the command-line is this:

gcc -shared sqlite3.c -o sqlite3.dll

The MinGW compiler is located on C:\Ruby26-x64\msys64 or C:\Ruby26-x32\msys32 (depending on your system and Ruby installation) it's a .exe file that opens a command line windows.
It's recommended to put the SQLite folder to compile in C:/ directory because this command line returns an error when you try to open a directory that contains spaces on the name

-bash: cd: too many arguments

Usually that is enough but if it does not work for you, you can download the MinGW Installer Manager to get the missing files
http://www.mingw.org/wiki/Getting_Started

2* Adding the SQLite 3 Compiled Folder directory to the path (not only the sqlite3.dll but the files of the sqlite-tools-win32-x86-3260000 folder also)

3* Running the command for the creating of the project and after it fails modify the Gemfile 

gem 'sqlite3',  '1.3.11'

Line by one of the bellow ones:

gem 'sqlite3', git: "https://github.com/sparklemotion/sqlite3-ruby"

gem 'sqlite3', git: "https://github.com/sparklemotion/sqlite3-ruby", branch: "add-gemspec"

gem 'sqlite3', git: "https://github.com/larskanis/sqlite3-ruby", branch: "add-gemspec"

Also tried removing

group :development do

# Access an interactive console on exception pages or by calling 'console' anywhere in the code.
gem 'web-console', '>= 3.3.0'

end

And ran this command

C:\Sites>cd project_name
C:\Sites\project_name>bundle install

About this fix, I should say that it works for some environments. If you have MinGW correctly installed and about it, it is included on the Ruby Devkit but when I executed Rails Installer and after Ruby Installer, it seems that system cannot find it.

Even after modified the PATH to point to C:\Ruby26-x64 instead of C:/RailsInstaller/Ruby2.3.3/, the system still uses the ruby version of C:/RailsInstaller/Ruby2.3.3/

It's quite a pain because if you want to install a gem for all the environment instead of only a project it'll return an error "Gemfile not found"
so if you've it correctly configured it'll work for you but only running it for each project that you creates.
4* Tried downloading the gem from https://rubygems.org/ and installing using the parameter --local = 'path'

gem install --local C:\gemdownloadfolder\sqlite3-1.4.0.gem

It didn't worked because of the chaos caused by installing rails installer first and later Ruby DevKit

Final solution

5* Run the uninstaller tool inside C://RailsInstaller/ after it finishes remove the remain folders too or when you try to call ruby command it'll return a message that says "C:/RailsInstaller/Ruby2.3.3/bin" folder is missing(the system will still be looking for ruby inside RailsInstaller folder not for "C:\Ruby26-x64\bin" folder).
Ok, now everything you have is Ruby DevKit and SQLite 3 installed.
Execute the following commands

C:\Sites>gem install rails

Let it finish and install mingw

C:\Sites>ridk exec pacman -S mingw-w64-x86_64-dlfcn

If Mingw isn’t installed yet, and you try to install SQLite, you will see this error

        ERROR: Failed to build gem native extension.

    current directory: C:/Ruby26-x64/lib/ruby/gems/2.6.0/gems/sqlite3-1.4.0/ext/
sqlite3
C:/Ruby26-x64/bin/ruby.exe -I C:/Ruby26-x64/lib/ruby/2.6.0 -r ./siteconf20190221
-4120-l5mva9.rb extconf.rb
checking for sqlite3.h... yes
checking for pthread_create() in -lpthread... yes
checking for -ldl... no
checking for dlopen()... no
missing function dlopen
*** extconf.rb failed ***
Could not create Makefile due to some reason, probably lack of necessary
libraries and/or headers.  Check the mkmf.log file for more details.  You may
need configuration options.

Download the SQLite3 gem from https://rubygems.org/ and save it locally. Then run the command to install from local path. You can run it for each project or globally like this.

C:\Sites>gem install --local C:\sqlite3-1.4.0.gem
Temporarily enhancing PATH for MSYS/MINGW...
Installing required msys2 packages: mingw-w64-x86_64-sqlite3
warning: mingw-w64-x86_64-sqlite3-3.27.0-1 is up to date -- skipping
Building native extensions. This could take a while...
Successfully installed sqlite3-1.4.0
Parsing documentation for sqlite3-1.4.0
Installing ri documentation for sqlite3-1.4.0
Done installing documentation for sqlite3 after 2 seconds
1 gem installed

C:\Sites>

If you've ran it in the folder Sites instead of inside one project the next time you run the creation project command you won't see any error with slite3 gem anymore 

C:\Sites>rails new demo5
      create
      create  README.md
      create  Rakefile
      create  .ruby-version
      create  config.ru
      create  .gitignore
      create  Gemfile
         run  git init from "."
Initialized empty Git repository in C:/sites2/demo5/.git/
      create  package.json
      create  app
      create  app/assets/config/manifest.js
      create  app/assets/javascripts/application.js
      create  app/assets/javascripts/cable.js
      create  app/assets/stylesheets/application.css
      create  app/channels/application_cable/channel.rb
      create  app/channels/application_cable/connection.rb
      create  app/controllers/application_controller.rb
      create  app/helpers/application_helper.rb
      create  app/jobs/application_job.rb
      create  app/mailers/application_mailer.rb
      create  app/models/application_record.rb
      create  app/views/layouts/application.html.erb
      create  app/views/layouts/mailer.html.erb
      create  app/views/layouts/mailer.text.erb
      create  app/assets/images/.keep
      create  app/assets/javascripts/channels
      create  app/assets/javascripts/channels/.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  bin/update
      create  bin/yarn
      create  config
      create  config/routes.rb
      create  config/application.rb
      create  config/environment.rb
      create  config/cable.yml
      create  config/puma.rb
      create  config/storage.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/application_controller_renderer.rb
      create  config/initializers/assets.rb
      create  config/initializers/backtrace_silencers.rb
      create  config/initializers/content_security_policy.rb
      create  config/initializers/cookies_serializer.rb
      create  config/initializers/cors.rb
      create  config/initializers/filter_parameter_logging.rb
      create  config/initializers/inflections.rb
      create  config/initializers/mime_types.rb
      create  config/initializers/new_framework_defaults_5_2.rb
      create  config/initializers/wrap_parameters.rb
      create  config/locales
      create  config/locales/en.yml
      create  config/master.key
      append  .gitignore
      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/apple-touch-icon-precomposed.png
      create  public/apple-touch-icon.png
      create  public/favicon.ico
      create  public/robots.txt
      create  tmp
      create  tmp/.keep
      create  tmp/cache
      create  tmp/cache/assets
      create  vendor
      create  vendor/.keep
      create  test/fixtures
      create  test/fixtures/.keep
      create  test/fixtures/files
      create  test/fixtures/files/.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  test/system
      create  test/system/.keep
      create  test/application_system_test_case.rb
      create  storage
      create  storage/.keep
      create  tmp/storage
      create  tmp/storage/.keep
      remove  config/initializers/cors.rb
      remove  config/initializers/new_framework_defaults_5_2.rb
         run  bundle install
Fetching gem metadata from https://rubygems.org/.............
Fetching gem metadata from https://rubygems.org/.
Resolving dependencies...........
Using rake 12.3.2
Using concurrent-ruby 1.1.4
Using i18n 1.5.3
Using minitest 5.11.3
Using thread_safe 0.3.6
Using tzinfo 1.2.5
Using activesupport 5.2.2
Using builder 3.2.3
Using erubi 1.8.0
Using mini_portile2 2.4.0
Using nokogiri 1.10.1 (x64-mingw32)
Using rails-dom-testing 2.0.3
Using crass 1.0.4
Using loofah 2.2.3
Using rails-html-sanitizer 1.0.4
Using actionview 5.2.2
Using rack 2.0.6
Using rack-test 1.1.0
Using actionpack 5.2.2
Using nio4r 2.3.1
Using websocket-extensions 0.1.3
Using websocket-driver 0.7.0
Using actioncable 5.2.2
Using globalid 0.4.2
Using activejob 5.2.2
Using mini_mime 1.0.1
Using mail 2.7.1
Using actionmailer 5.2.2
Using activemodel 5.2.2
Using arel 9.0.0
Using activerecord 5.2.2
Using mimemagic 0.3.3
Using marcel 0.3.3
Using activestorage 5.2.2
Using public_suffix 3.0.3
Using addressable 2.6.0
Using io-like 0.3.0
Using archive-zip 0.11.0
Using bindex 0.5.0
Using msgpack 1.2.6 (x64-mingw32)
Using bootsnap 1.4.0
Using bundler 1.17.2
Using byebug 11.0.0
Using regexp_parser 1.3.0
Using xpath 3.2.0
Using capybara 3.13.2
Using ffi 1.10.0 (x64-mingw32)
Using childprocess 0.9.0
Using chromedriver-helper 2.1.0
Using coffee-script-source 1.12.2
Using execjs 2.7.0
Using coffee-script 2.4.1
Using method_source 0.9.2
Using thor 0.20.3
Using railties 5.2.2
Using coffee-rails 4.2.2
Using duktape 2.0.1.0
Using multi_json 1.13.1
Using jbuilder 2.8.0
Using puma 3.12.0
Using sprockets 3.7.2
Using sprockets-rails 3.2.1
Using rails 5.2.2
Using rb-fsevent 0.10.3
Using rb-inotify 0.10.0
Using rubyzip 1.2.2
Using sass-listen 4.0.0
Using sass 3.7.3
Using tilt 2.0.9
Using sass-rails 5.0.7
Using selenium-webdriver 3.141.0
Using sqlite3 1.4.0
Fetching turbolinks-source 5.2.0
Installing turbolinks-source 5.2.0
Fetching turbolinks 5.2.0
Installing turbolinks 5.2.0
Fetching tzinfo-data 1.2018.9
Installing tzinfo-data 1.2018.9
Fetching uglifier 4.1.20
Installing uglifier 4.1.20
Fetching web-console 3.7.0
Installing web-console 3.7.0
Bundle complete! 16 Gemfile dependencies, 77 gems now installed.
Use `bundle info [gemname]` to see where a bundled gem is installed.

...
Using selenium-webdriver 3.141.0
Using sqlite3 1.4.0
Fetching turbolinks-source 5.2.0
...

Good Luck!
There are a lot of people that prefers using PostgreSQL with Ruby on Rails but some others needs or prefer SQLite 3 because it's a file-based DB, it's faster than others, portable, etc.
For example if you're developing a single-user app, game or cross-platform web app that acts like a native one or just because it's easier to manage during the development period. 
In addition, it is useful because Javascript, Phonegap, Node.js, Xamarin, Android Java between others technologies can also connect with a SQLite DB that will be saved on local. 
Actually, there are a lot of pages that uses it to avoid data loss when the user lost internet connection, an operation cannot be done successfully, etc. 

Alternatively, simply to avoid the not-so-needed data that can be stored on user device,  instead of being transferred to the cloud, sometimes by security matters, performance or kept the speed the application at the top possible or just to avoid the unnecessary use of bandwidth or server disk space.

It has gave a bunch of new possibilities and functionalities, especially after Ruby on Rails strong back-end started to be complimented with Ruby on Rails API that connects native apps for mobile users.