Rails 5 uuid issue with sqlite

Question

I'm converting my rails 5.1 app to start using UUID instead of incremental ids as the default active_record.

I've changed my migration files to use id: :uuid

class CreateProjects < ActiveRecord::Migration[5.1]
  def change
    create_table :projects, id: :uuid do |t|
      t.string :title
      t.text :description

      t.timestamps
    end
  end
end

I've added a migration file to support 'uuid-ossp' and 'pgcrypto' as I intend to use pg in prod.

class EnablePgcryptoExtension < ActiveRecord::Migration[5.1]
  def change
    enable_extension 'uuid-ossp'
    enable_extension 'pgcrypto'
  end
end

But when I try to create an object, I get an error as if the id was null.

ActiveRecord::NotNullViolation: SQLite3::ConstraintException: NOT NULL constraint failed: projects.id: INSERT INTO "projects" ("created_at", "updated_at") VALUES (?, ?)

the schema.rb offers a hint that 'uuid' is not a known type

# Could not dump table "projects" because of following StandardError
#   Unknown type 'uuid' for column 'id'

I suggest I could start using string types instead of uuid for sqlite3, but I intend on using postgres for production and would like to use uuids.

Should I use pg in dev or is there another way ?

Answer 1

SQLite is not a generic DB and don't fully implement SQL-92. You should only use SQLite for it's designed purpose (mostly enable software, caching,...).

If you take a look here Using UUIDs in SQLite you'll understand that SQLite don't have a real UUID type (a BLOB(16) is the best choice).

As said by Mu Is Too Short, you must use the same environment between your development platform and the production one, or you'll facing a lot of trouble...