The KeyValue backend stores translations on a set of two shared tables, one for string-valued translations and one for text-valued translations. These tables are generated when you run the default install generator (rails generate mobility:install) and then migrate your database (rake db:migrate).

A detailed description of the backend is described as "Strategy #3" in this blog post.

The tables generated by the default migration look like this:

create_table "mobility_string_translations", force: :cascade do |t|
  t.string   "locale"
  t.string   "key"
  t.string   "value"
  t.integer  "translatable_id"
  t.string   "translatable_type"
  t.datetime "created_at",        null: false
  t.datetime "updated_at",        null: false
  t.index ["translatable_id", "translatable_type", "key"], name: "index_mobility_string_translations_on_translatable_attribute"
  t.index ["translatable_id", "translatable_type", "locale", "key"], name: "index_mobility_string_translations_on_keys", unique: true
  t.index ["translatable_type", "key", "value", "locale"], name: "index_mobility_string_translations_on_query_keys"
end

create_table "mobility_text_translations", force: :cascade do |t|
  t.string   "locale"
  t.string   "key"
  t.text     "value"
  t.integer  "translatable_id"
  t.string   "translatable_type"
  t.datetime "created_at",        null: false
  t.datetime "updated_at",        null: false
  t.index ["translatable_id", "translatable_type", "key"], name: "index_mobility_text_translations_on_translatable_attribute"
  t.index ["translatable_id", "translatable_type", "locale", "key"], name: "index_mobility_text_translations_on_keys", unique: true
end

Both tables have a translatable_type string column, because these will be used in a polymorphic relationship with translated models.

To understand how this works, suppose we have a model Post:

class Post < ApplicationRecord
  extend Mobility
  translates :title,   backend: :key_value, type: :string
  translates :content, backend: :key_value, type: :text
end

The backend will create associations on the class roughly resulting in:

class Post < ApplicationRecord
  has_many :mobility_string_translations, ->{ where key: [:title] },
    as:         :translatable,
    class_name: Mobility::ActiveRecord::StringTranslation,
    dependent:  :destroy,
    inverse_of: :translatable,
    autosave:   true

  has_many :mobility_text_translations,   ->{ where key: [:content] },
    as:         :translatable,
    class_name: Mobility::ActiveRecord::TextTranslation,
    dependent:  :destroy,
    inverse_of: :translatable,
    autosave:   true
end

So the backend adds associations for each type, scoped to only include translations whose keys are :title and :content, respectively.

The classes Mobility::ActiveRecord::StringTranslation and Mobility::ActiveRecord::TextTranslation both inherit from Mobility::ActiveRecord::Translation, which is an abstract class:

module Mobility
  module ActiveRecord
    class Translation < ::ActiveRecord::Base
      self.abstract_class = true

      belongs_to :translatable, polymorphic: true

      validates :key, presence: true, uniqueness: { scope: [:translatable_id, :translatable_type, :locale] }
      validates :translatable, presence: true
      validates :locale, presence: true
    end
  end
end

Since the class itself is abstract, it has no table; the table is set in the descendants to mobility_string_translations and mobility_text_translations, respectively. The different tables is actually the only difference between the string and text translation classes, and the only difference between the tables is that their value columns are of different types (string/text) (and that the string table has one additional index on value).

When you get a value with post.title, the backend does roughly the following to get the value:

locale = Mobility.locale
translation = mobility_string_translations.find { |t| t.key == "title" && t.locale == locale.to_s }
translation ||= translations.build(locale: locale, key: "title")
translation.value

So it finds the translation from all translations associated with the model which has a key "title" and a locale equal to Mobility.locale. If such a translation does not exist, it builds one. It then returns the value. Setting is much the same, with the value of the translation updated to a new value.

Querying on the KeyValue backend is quite complex since the associations above are polymorphic. Suppose we have a model, Post:

class Post < ApplicationRecord
  extend Mobility
  translates :title, backend: :key_value, type: :text
end

If we now add the Mobility scope (i18n) and query for posts with a title matching the string "foo", like this:

Post.i18n.where(title: "foo")

This is the resulting SQL:

SELECT "posts".* FROM "posts"
  INNER JOIN "mobility_text_translations" "Post_title_en_text_translations"
  ON "Post_title_en_text_translations"."key" = 'title'
  AND "Post_title_en_text_translations"."locale" = 'en'
  AND "Post_title_en_text_translations"."translatable_type" = 'Post'
  AND "Post_title_en_text_translations"."translatable_id" = "posts"."id"
  WHERE "Post_title_en_text_translations"."value" = 'foo'

As you can see, Mobility executes an INNER JOIN on a set of columns on mobility_text_translations (since this attribute is of type "text"), two of which match the Model name ('Post') and id "posts"."id", and the others matching the current locale ("en") and attribute name ("title"), and finally with the where condition matching the attribute value ("title_text_translations"."value" = 'foo').

The key trick here is the alias of "mobility_text_translations" to "Post_title_en_text_translations". This is required because if we query on multiple translated (key-value) attributes, we need to manage these joins and query conditions separately. So if you query on a few different translated attributes, you may end up with a some complex-looking SQL in your logs.

The presence of en and the class name Post in the alias is important for distinguishing between different translated models joining translations in different locales at once. So, for example, you can query for posts whose title is foo in Japanese but bar in English like this:

Post.i18n.where(title: "foo", locale: :ja).where(title: "bar", locale: :en)

and this will generate the SQL:

SELECT "posts".* FROM "posts"
  INNER JOIN "mobility_text_translations" "Post_title_ja_text_translations"
  ON "Post_title_ja_text_translations"."key" = 'title'
  AND "Post_title_ja_text_translations"."locale" = 'ja'
  AND "Post_title_ja_text_translations"."translatable_type" = 'Post'
  AND "Post_title_ja_text_translations"."translatable_id" = "posts"."id"
  INNER JOIN "mobility_text_translations" "Post_title_en_text_translations"
  ON "Post_title_en_text_translations"."key" = 'title'
  AND "Post_title_en_text_translations"."locale" = 'en'
  AND "Post_title_en_text_translations"."translatable_type" = 'Post'
  AND "Post_title_en_text_translations"."translatable_id" = "posts"."id"
  WHERE "Post_title_ja_text_translations"."value" = 'foo'
  AND "Post_title_en_text_translations"."value" = 'bar'

Since we have two joins here, one for each locale, the result will correctly distinguish between records matching each locale separately. (The Table backend uses a similar trick when joining its translation table(s).)

In general, the SQL above should not be an issue. However, one thing to keep in mind is that by default, the results returned by Post.i18n.where(title: "foo") (or any query on Post actually) will not eager load the translations. This has nothing really to do with the query structure, but instead results from the polymorphic associations described above.

If you want to eagerly load results (to avoid "N+1" queries when e.g. listing a set of query results), you can overcome this problem by explicitly eager loading the associations you will need. For example, if you are querying for text-valued key-value attributes, you can tack eager_load(:text_translations) onto your query, like this:

Post.i18n.where(title: "foo").eager_load(:text_translations)

This will result in a query which selects all the columns you need from the polymorphic text_translations association, like this:

SELECT "posts"."id" AS t0_r0,
  "posts"."created_at" AS t0_r1,
  "posts"."updated_at" AS t0_r2,
  "text_translations_posts"."id" AS t1_r0,
  "text_translations_posts"."locale" AS t1_r1,
  "text_translations_posts"."key" AS t1_r2,
  "text_translations_posts"."value" AS t1_r3,
  "text_translations_posts"."translatable_id" AS t1_r4,
  "text_translations_posts"."translatable_type" AS t1_r5,
  "text_translations_posts"."created_at" AS t1_r6,
  "text_translations_posts"."updated_at" AS t1_r7
FROM "posts"
LEFT OUTER JOIN "mobility_text_translations" "text_translations_posts"
  ON "text_translations_posts"."translatable_id" = "posts"."id"
  AND "text_translations_posts"."key" = 'title'
  AND "text_translations_posts"."translatable_type" = $1
INNER JOIN "mobility_text_translations" "Post_title_en_text_translations"
  ON "Post_title_en_text_translations"."key" = 'title'
  AND "Post_title_en_text_translations"."locale" = 'en'
  AND "Post_title_en_text_translations"."translatable_type" = 'Post'
  AND "Post_title_en_text_translations"."translatable_id" = "posts"."id"
WHERE "Post_title_en_text_translations"."value" = $2  [["translatable_type", "Post"], ["value", "foo"]]

You can see here that we now have two joins, one to match only posts whose title is "foo", the other one to join (with an OUTER JOIN) all columns on mobility_text_translations. Once you have eager-loaded results this way, you should not see any extra queries when later fetching post.title (or whatever the translated attribute name is).

If instead you were using a string-valued attribute, this would be the query:

Post.i18n.where(title: "foo").eager_load(:string_translations)

Or, if you have both string and text translations:

Post.i18n.where(title: "foo").eager_load(:string_translations, :text_translations)

(Note that if you try to eager-load one of these associations but you do not actually have any translated attributes of the corresponding type, you will get an error from ActiveRecord.)

An extension of the KeyValue backend can be used to translate Rails Action Text rich text.

If you are using UUIDs that are not integers (e.g. in Postgres they are strings) you likely need to run a migration to change the translatable_id columns to be the correct UUID type. For example:

def change
    change_column :mobility_string_translations, :translatable_id, :string
    change_column :mobility_text_translations, :translatable_id, :string
  end