Rails 3 SQLite3 Boolean false

Question

I'm trying to insert a false boolean value in a SQLite3 table but it always inserts a true value.

Here's my migration:

class CreateUsers < ActiveRecord::Migration
  def self.up
    create_table :users do |t|
      t.column :name, :string
      t.column :active, :boolean, :default => false, :null => false
    end
  end

  def self.down
    drop_table :resources
  end
end

When I try to insert using rails it produces the following SQL:

INSERT INTO "users" ("name", "active") VALUES ('test', 'f')

SQLite treats 'f' as true so it inserts true into my database. The query I want it to generate is:

INSERT INTO "users" ("name", "active") VALUES ('test', false)

What am I doing wrong?

rails: 3.0.7

sqlite3 gem: 1.3.3

Answer 1

SQLite uses 1 for true and 0 for false:

SQLite does not have a separate Boolean storage class. Instead, Boolean values are stored as integers 0 (false) and 1 (true).

But SQLite also has a loose type system and automatically casts things so your 'f' is probably being interpreted as having a truthiness of "true" simply because it isn't zero.

A bit of digging indicates that you have found a bug in the Rails 3.0.7 SQLiteAdapter. In active_record/connection_adapters/abstract/quoting.rb, we find these:

def quoted_true
  "'t'"
end

def quoted_false
  "'f'"
end

So, by default, ActiveRecord assumes that the database understands 't' and 'f' for boolean columns. The MySQL adaptor overrides these to work with its tinyint implementation of boolean columns:

QUOTED_TRUE, QUOTED_FALSE = '1'.freeze, '0'.freeze

#...

def quoted_true
  QUOTED_TRUE
end

def quoted_false
  QUOTED_FALSE
end

But the SQLite adapter does not provide its own implementations of quoted_true or quoted_false so it gets the defaults which don't work with SQLite's booleans.

The 't' and 'f' booleans work in PostgreSQL so maybe everyone is using PostgreSQL with Rails 3 or they're just not noticing that their queries aren't working properly.

I'm a little surprised by this and hopefully someone can point out where I've gone wrong, you can't be the first person to use a boolean column in SQLite with Rails 3.

Try monkey patching def quoted_true;'1';end and def quoted_false;'0';end into ActiveRecord::ConnectionAdapters::SQLiteAdapter (or temporarily hand-edit them into active_record/connection_adapters/sqlite_adapter.rb) and see if you get sensible SQL.

Answer 2

I ran across this as well, here's how to monkey patch:

require 'active_record/connection_adapters/sqlite_adapter'
module ActiveRecord
  module ConnectionAdapters
    class SQLite3Adapter < SQLiteAdapter
      def quoted_true; '1' end
      def quoted_false; '0' end
    end
  end
end

I don't get how I'm still running across this bug??

Answer 3

You may find useful the following code snippet for adding compatibility with SQLite boolean columns actually working on Rails 4 (also posted at https://gist.github.com/ajoman/9391708):

# config/initializers/sqlite3_adapter_patch.rb

module ActiveRecord
  module ConnectionAdapters
    class SQLite3Adapter < AbstractAdapter
      QUOTED_TRUE, QUOTED_FALSE = "'t'", "'f'"

      def quoted_true
        QUOTED_TRUE
      end

      def quoted_false
        QUOTED_FALSE
      end
    end
  end
end

Answer 4

This was fixed on master on 12 Jul 2017. However it is not part of the latest stable release (5.1.4). The most recent release where it's fixed is v5.2.0.rc1.

The behaviour can be set via Rails.application.config.active_record.sqlite3.represent_boolean_as_integer (default is true).

Answer 5

This version works in Rails 4.1.

require 'active_record/connection_adapters/sqlite_adapter'

module ActiveRecord::ConnectionAdapters::SQLite3Adapter
  QUOTED_TRUE, QUOTED_FALSE = 't'.freeze, 'f'.freeze

  def quoted_true; QUOTED_TRUE end
  def quoted_false; QUOTED_FALSE end
end

The constants and .freeze are for performance, so ruby doesn't have to regenerate those strings and garbage collect them on every call.