A Deep Dive Into LiteDB for Ruby on Rails
Tags: ruby, rails
Published: 2023-11-08 11:45:12

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](https://www.sqlite.org/datatype3.html) 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](https://www.sqlite.org/stricttables.html).

## 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:

```sql
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:

```sql
-- 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_.

```sql
-- 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.

```sql
-- 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](https://www.sqlite.org/datatype3.html). 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](https://www.sqlite.org/quirks.html) 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](https://www.sqlite.org/json1.html):

> 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](https://www.sqlite.org/loadext.html) as an [extension](https://sqlite.org/src/file/ext/misc/uuid.c), 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](https://www.sqlite.org/flextypegood.html).

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](https://www.sqlite.org/fasterthanfs.html) 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:

```sh
$ 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:

```diff
  # 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`:

```diff
  # 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:

```diff
  <!-- 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](https://github.com/janko/image_processing) and use [libvips](https://www.libvips.org/) to transform our image.

**Note**: you have to install libvips on your operating system for this to work. Instructions are available [in the README](https://github.com/libvips/libvips#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:

```diff
  # 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](https://github.com/oldmoe/litestack/blob/master/BENCHMARKS.md).

## A Quick Note On Litestream

As a final side-note, I would like to touch on [Litestream](https://litestream.io/how-it-works/), 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](https://litestream.io/getting-started/#restoring-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](https://blog.appsignal.com/ruby-magic)!**