Switch Solid Cable, Solid Queue and Solid Cache to Sqlite in Producution

I followed Miles Woodroffe's post to setup solid cable in production with Kamal. This should give us the main setup for splitting up the database into primary (postgres) and queue, cable and cache (sqlite).

In our database.yml, we add the following:

sqlite: &sqlite
  adapter: sqlite3
  pool: <%= ENV.fetch("RAILS_MAX_THREADS") { 5 } %>
  timeout: 5000

Our production database will end up looking like this:

production:
  primary: &primary_production
    <<: *default
    host: '<%= ENV["DB_HOST"] %>'
    port: '<%= ENV.fetch("POSTGRES_PORT", 5432) %>'
    database: '<%= ENV["POSTGRES_DB"] %>'
    username: '<%= ENV["POSTGRES_USER"] %>'
    password: '<%= ENV["POSTGRES_PASSWORD"] %>'
  cable:
    <<: *sqlite
    database: /data/<%= ENV.fetch('SQLITE_DB', 'my_app_production') %>_cable.sqlite3
    migrations_paths: db/cable_migrate
  queue:
    <<: *sqlite
    database: /data/<%= ENV.fetch('SQLITE_DB', 'my_app_production') %>_queue.sqlite3
    migrations_paths: db/queue_migrate
  cache:
    <<: *sqlite
    database: /data/<%= ENV.fetch('SQLITE_DB', 'my_app_production') %>_cache.sqlite3
    migrations_paths: db/cache_migrate

As you can see, we have the migrations_path for each sqlite instance. We need to create these folders inside of our rails app. If you previously had the cable_schem.rb, queue_schem.rb and cache_schema.rb directly under /db, then you have to shift these files into the correspoding, newly created folders. However, it's not enough to just shift them, you have to turn these schemas into a migration. To so, run this:

rails generate migration CreateCableSchema --migrations-path=db/cable_migrate
rails generate migration CreateQueueSchema --migrations-path=db/queue_migrate
rails generate migration CreateCacheSchema --migrations-path=db/cache_migrate

And then move those migration files into their corresponding folder (e.g. db/cache_migrate/20241213144831_create_cache_schema.rb). Copy the contents of the schema into the migration, it should look like this:

class CreateCacheEntries < ActiveRecord::Migration[8.0]
  def change
    create_table 'solid_cache_entries', force: :cascade do |t|
      t.binary 'key', limit: 1024, null: false
      t.binary 'value', limit: 536_870_912, null: false
      t.datetime 'created_at', null: false
      t.integer 'key_hash', limit: 8, null: false
      t.integer 'byte_size', limit: 4, null: false
      t.index ['byte_size'], name: 'index_solid_cache_entries_on_byte_size'
      t.index %w[key_hash byte_size], name: 'index_solid_cache_entries_on_key_hash_and_byte_size'
      t.index ['key_hash'], name: 'index_solid_cache_entries_on_key_hash', unique: true
    end
  end
end

When you deploy next time, the command bin/rails db:prepare should do the migrations for you.

Mounting sqlite under /data

Inside our kamal deploy.yml file, we add a volume:

## config/deploy.yml
volumes:
  - "sqlite_data:/data"

For our setup with Kamal, we need to ensure that the database persists across deployments. To achieve this, it must be mounted on the server’s file system outside the Docker container while remaining accessible from the container every time it’s deployed with Kamal.

Deploy this to your server. I probably won't become healthy but it will create the new volume for you.

Setting Correct Permissions for SQLite Volume in Docker

One issue I faced when adding this after I'd already made my Kamal setup was missing rights, which meant the servers didn't become healthy during deployment.

When using SQLite in a Dockerized Rails application (e.g., for Solid Cable, Solid Queue, or Solid Cache), we have to ensure that the mounted volume has the correct ownership and permissions. We need to verify and fix ownership issues to ensure the rails user inside the container can write to the SQLite database.

Step 1: Verify Current Permissions

ssh into your server instance.

Check the current ownership and permissions of the SQLite volume directory on the host machine:

ls -ld /var/lib/docker/volumes/sqlite_data/_data

Output example:

drwxr-xr-x 2 root root 4096 Dec 13 15:24 /var/lib/docker/volumes/sqlite_data/_data

Here:

• The directory /var/lib/docker/volumes/sqlite_data/_data is owned by root.
• This prevents the rails user (usually UID=1000) inside the container from writing to the directory.

Step 2: Change Ownership

To allow the rails user to access and write to the SQLite database, update the ownership of the volume directory to match the rails user inside the container (UID=1000, GID=1000):

chown -R 1000:1000 /var/lib/docker/volumes/sqlite_data/_data

Step 3: Verify Changes

Re-run the ls command to confirm the updated ownership:

ls -ld /var/lib/docker/volumes/sqlite_data/_data

Expected output:

drwxr-xr-x 2 1000 1000 4096 Dec 13 15:24 /var/lib/docker/volumes/sqlite_data/_data

Now the directory is owned by the rails user (UID=1000), ensuring proper write permissions.

Why This Is Important

SQLite stores its data in files on disk. Without the correct ownership and permissions:

• The rails application inside the container cannot write to the SQLite database.
• This leads to errors such as SQLite3::CantOpenException: unable to open database file.