In the second post of our series covering LiteStack (an alternative way to build Rails applications entirely based on SQLite), we'll explore the database's concepts of flexible typing and type affinity.

We'll not only discover how SQLite's data handling differs from other SQL databases, but also how we efficiently process and store binary data, like images, directly in a database column.

Note: LiteDB is essentially SQLite, but fine-tuned for usage in Rails. We'll use LiteDB and SQLite interchangeably in this post.

Flexible Typing In LiteDB for Ruby on Rails

The first thing to always keep in mind is that SQLite is flexibly typed. This means that, in contrast to most other SQL databases (where the data type of a value is defined by its container — the column data type), SQLite is very forgiving. Or, as the SQLite docs state more formally:

Datatype of a value is associated with the value itself, not with its container.

This means, for example, that you can store strings in INTEGER columns, or exceed the length defined by VARCHAR(n). In the development process, this is usually not an issue. It boils down to a bit of a vendor lock-in, though: Using an SQLite database in development and a PostgreSQL one in production can become a nightmare.

Note: SQLite version 3.37.0 introduced the option of STRICT tables.

SQLite for Rails: Type Affinity

Type affinity in SQLite is a concept that determines how data is stored and converted. Before explaining what this means, let's take a look at SQLite's storage classes. These are:

• NULL
• INTEGER (7 different data types depending on size)
• REAL (any floating point value)
• TEXT (any string)
• BLOB (raw binary data)

These are equivalent to data types for the purpose of a general discussion, but have a broader scope. When you create a table, you do not declare storage classes for each column, but type affinities.

A certain type affinity determines what storage class(es) will be used to store a certain column. Because this is a rather elusive topic to discuss theoretically, let's look at an example. Say we create the following table:

CREATE TABLE employees (
    id INTEGER PRIMARY KEY,
    name TEXT,
    department TEXT,
    salary NUMERIC
);

INTEGER, TEXT, and NUMERIC are the columns' type affinities. For example, suppose I enter an integer number into the department column. It will be converted into TEXT, because the TEXT affinity only uses the TEXT, NULL, and BLOG storage classes:

-- stored as 1, 'Anna Lee', '123', 85000
INSERT INTO employees (id, name, department, salary) VALUES (1, 'Anna Lee', 123, 85000);

On the other hand, if I enter the string "67000" as salary, the NUMERIC affinity will convert it to INTEGER, because it is a well formed number literal.

-- stored as 2, 'Mike Johnson', 'Customer Support', 67000
INSERT INTO employees (id, name, department, salary) VALUES (2, 'Mike Johnson', 'Customer Support', '67000');

If I were to enter "eighty-five thousand", it would just default to the TEXT storage class.

-- stored as 3, 'Jane Smith', 'Engineering', 'eighty-five thousand'
INSERT INTO employees (id, name, department, salary) VALUES (3, 'Jane Smith', 'Engineering', 'eighty-five thousand');

The rules are rather complex, so check out the SQLite official docs on datatypes. The main takeaway here is that, somewhat counterintuitively, the column type affinities you declare need not reflect the actual data types of the stored values.

SQLite also lacks boolean, datetime, and JSONB datatypes, and UUIDs. Let's take a look at these now.

No Boolean Datatype

Boolean values are commonly represented as 0 and 1 instead of TRUE and FALSE.

No Datetime Datatype

The SQLite docs recommend using the built-in conversion functions and that you store dates and times:

• As a TEXT string in the ISO-8601 format. Example: '2018-04-02 12:13:46'.
• As an INTEGER number of seconds since 1970 (also known as "unix time").
• As a REAL value that is the fractional Julian day number.

No JSONB Datatype

Especially for developers relying on the convenience of the PostgreSQL JSONB datatype, it's important to point out that SQLite doesn't use a binary format to store JSON:

Experiments have been unable to find a binary encoding that is smaller or faster than a plain text encoding.

Most notably, SQLite's JSON implementation doesn't support BLOB storage.

No Universally Unique Identifiers (UUIDs)

Another characteristic that comes as a surprise to most developers is that SQLite lacks the concept of UUIDs. Not completely, as they can be compiled and loaded as an extension, but they are not built into the database per default.

If you are interested in exploring the advantages and tradeoffs of flexible database types further, the SQLite docs have a decent write-up.

Enough with the theoretical deliberations and dwelling on what SQLite doesn't have, though. Let's get back to building! Let's see how we can use SQLite to store image prompts.

Storing Image Prompts Directly in SQLite

The SQLite documentation claims that the database is excellently suited to store small blobs of binary data (e.g., thumbnails). We probably still would use a CDN in production, but it's interesting enough to give it a try.

For starters, let's add a content_type column to our prompts table. It will come in handy later:

$ bin/rails g migration AddContentTypeToPrompts content_type:string
$ bin/rails db:migrate

With this in place, the transcoding into a data URL can be done in the model itself:

  # app/models/prompt.rb
  class Prompt < ApplicationRecord
    include AccountScoped
 
    belongs_to :account
    has_rich_text :description
 
-   validates :title, :prompt_image, presence: true
+   validates :title, :prompt_image, :content_type, presence: true
+
+   def data_url
+     encoded_data = Base64.strict_encode64(prompt_image)
+
+     "data:image/#{content_type};base64,#{encoded_data}"
+   end
  end

We can now start to simplify our controller code a bit. If we assign both prompt_image and content_type to @prompt from the uploaded file, we can scrap the helper methods prompt_image and prompt_image_data_url:

  # app/controllers/prompts_controller.rb
 
  class PromptsController < ApplicationController
    # ....
 
    def create
+     prompt_image = prompt_params.delete(:prompt_image)
+
      @prompt = Prompt.new(prompt_params)
      @prompt.account = Current.account
+     @prompt.prompt_image = prompt_image.read
+     @prompt.content_type = prompt_image.content_type
 
      model = Replicate.client.retrieve_model("stability-ai/stable-diffusion-img2img")
      version = model.latest_version
-     version.predict({prompt: prompt_params[:title],
-       image: prompt_image_data_url}, replicate_rails_url)
-
+     version.predict({prompt: prompt_params[:title],
+       image: @prompt.data_url}, replicate_rails_url)
 
      # ...
    end
 
      # ...
 
    private
 
      # ...
 
-
-     def prompt_image_data_url
-       encoded_data = Base64.strict_encode64(prompt_image.read)
-
-       "data:image/#{prompt_image.content_type};base64,#{encoded_data}"
-     end
-
-     def prompt_image
-       @prompt_image ||= prompt_params[:prompt_image]
-     end
  end

All that's left to do now is to actually display the image in our view. For this, we can use the same data_url helper method from the model:

  <!-- app/views/prompts/_prompt.html.erb -->
  <div id="<%= dom_id prompt %>">
    <p>
      <strong>Title:</strong>
      <%= prompt.title %>
    </p>
 
    <p>
      <strong>Description:</strong>
      <%= prompt.description %>
    </p>
 
    <p>
      <strong>Prompt image:</strong>
-     <%= image_tag prompt.prompt_image %>
+     <%= image_tag prompt.data_url %>
    </p>
  </div>

There is a tiny blemish here, though. We are potentially storing and transferring way too much image data. StableDiffusion needs only a 768 pixel wide (or long) image to work, so we will scale it down beforehand. To accomplish this, we'll install the ImageProcessing library and use libvips to transform our image.

Note: you have to install libvips on your operating system for this to work. Instructions are available in the README.

The image_processing gem is already installed in an off-the-shelf Rails 7 app, so we can start writing our transformation logic. We'll do this in a before_save callback in our Prompt model:

  # app/models/prompt.rb
+ require "vips"
 
  class Prompt < ApplicationRecord
    include AccountScoped
 
    # ...
 
+   before_save :scale_prompt_image
 
    # ...
 
+   private
+
+   def scale_prompt_image
+     image = Vips::Image.new_from_buffer(prompt_image, "")
+     pipeline = ImageProcessing::Vips.source(image)
+
+     self.prompt_image = pipeline.resize_to_fit(768, 768).call.read
+   end
  end

The resize_to_fit method will scale an image to fit the specified dimensions while preserving the aspect ratio. For example, if we upload an image with 1024x768 pixels, it will be scaled down to 768x576.

This completes the preprocessing logic of our image generation pipeline.

Overall Notes on SQLite for Rails and Its Performance

Although some traditional data types like boolean and datetime are absent, SQLite excels in handling binary data, making it an excellent choice for certain applications. If you are interested in how SQLite compares to other Relational Database Management Systems (RDBMS's) in terms of performance, have a look at the benchmarks.

A Quick Note On Litestream

As a final side-note, I would like to touch on Litestream, a performant and safe way to replicate your SQLite databases to S3-compatible object storage in a streaming manner. Not only does it provide a very cost-effective backup solution, but it is also a very simple way to restore your database.

Up Next: Asynchronously Handling Image Predictions with LiteJob

In this article, we've delved into SQLite's flexible typing and type affinity, harnessing its power to process and store binary data. We have looked into optimizing image storage and leveraged SQLite's unique data-handling features.

Next, we'll consider how to handle the actual generation and persistence of prediction images. We have only implemented a webhook stub so far, and created the predictions in a peculiar way in our PromptsController. We will look into performing this step asynchronously with LiteJob.

Until then, happy coding!

P.S. If you'd like to read Ruby Magic posts as soon as they get off the press, subscribe to our Ruby Magic newsletter and never miss a single post!