Announcing: AshSqlite! (beta)

If you encounter and issues, feel free to open tickets.

Exciting!

As far as the Ash ecosystem goes, were there any features of Ash Postgres which Ash Sqlite wasn’t able to support? Or are they full one to one replacements for each other?

There are various features we could not support. The big ones are aggregates and certain expressions do not have a parallel (or one that we cared to build in the first release) in SQLite3. For example, concat_ws([list, of, things]) is not supported.

Aggregates are a pretty big feature and not being able to use them may or may not affect a given use case. You can always use calculations to extract similar data, but it likely won’t be usable in filters and sorts unless you write it as a fragment that does a subquery.

We will most likely be able to support all aggregate types except for list at some point, but we couldn’t use the implementation from ash_postgres because it uses lateral joins which are not available in SQLite3. Something new will need to be devised for AshSqlite.

Another issue w/ the current implementation is that the migration generator currently is essentially fully copied from AshPostgres but AshPostgres uses alter column and that is not supported by SQLite3, meaning that those migrations need to be adjusted by hand. We should fix the migration generator to have it do something like warn to the user and drop some comments in the migration file, or something like that. Still TBD.

While I’m using AshSqlite3 In https://ash-hq.org I will likely not need any advanced feature support at any point in the near future, so hopefully those that do use it can champion pushing it forward in the above regards.

Hey people I’m new to ash. any ideas how i could do this would be really nice.
I have a table like this.

I want to create a read action something like:

Posts.read_translated(:some_locale)

This action would return the title/content as string from the json column, extra credit if it could default to some defined locale if the current locale is empty. here’s the resource code:

defmodule Octafest.Blog.Post do
  use Ash.Resource,
    data_layer: AshSqlite.DataLayer

  sqlite do
    table "posts"

    repo Octafest.Repo
  end

  code_interface do
    define_for Octafest.Blog
    define :create, action: :create
    define :read_all, action: :read
    define :update, action: :update
    define :destroy, action: :destroy
    define :get_by_id, args: [:id], action: :by_id
  end

  actions do
    defaults ~w(create read update destroy)a

    read :by_id do
      argument :id, :uuid, allow_nil?: false
      get? true

      filter expr(id == ^arg(:id))
    end

    read :translated do
      argument :locale, :atom do
        allow_nil? false
      end

      pagination offset?: true, countable: :by_default
    end
  end

  attributes do
    uuid_primary_key :id

    attribute :title, Octafest.Shared.Translated
    attribute :content, Octafest.Shared.Translated
  end
end

Here’s the fix:

defmodule Octafest.Api.Post do
  use Ash.Resource,
    data_layer: AshSqlite.DataLayer

  sqlite do
    table "posts"

    repo Octafest.Repo
  end

  attributes do
    uuid_primary_key :id

    attribute :title, Octafest.Shared.Translated
    attribute :content, Octafest.Shared.Translated
  end

  actions do
    defaults ~w(create read update destroy)a

    read :by_id do
      argument :id, :uuid, allow_nil?: false
      get? true

      filter expr(id == ^arg(:id))
    end

    read :translated do
      argument :locale, :atom, allow_nil?: false

      prepare build(
                load: [
                  {:t_title, locale: arg(:locale)},
                  {:t_content, locale: arg(:locale)}
                ]
              )
    end
  end

  calculations do
    calculate :t_title, :string, {TranslateAttribute, attribute: :title} do
      argument :locale, :atom do
        default :en
      end
    end

    calculate :t_content, :string, {TranslateAttribute, attribute: :content} do
      argument :locale, :atom do
        default :en
      end
    end
  end

  code_interface do
    define_for Octafest.Api
    define :create, action: :create
    define :read_all, action: :read
    define :update, action: :update
    define :destroy, action: :destroy

    define :get_by_id, args: [:id], action: :by_id
    define :translated, args: [:locale], action: :translated
  end
end

defmodule TranslateAttribute do
  use Ash.Calculation

  @impl true
  def select(_, opts, _), do: [opts[:attribute]]

  @impl true
  @spec calculate(any(), any(), %{:locale => atom(), optional(any()) => any()}) :: list()
  def calculate(records, opts, %{locale: locale}) do
    records
    |> Enum.map(fn record ->
      record |> Map.get(opts[:attribute]) |> Map.get(locale)
    end)
  end
end

And this is how you call it:

defmodule OctafestWeb.AdminLive do
  use OctafestWeb, :admin_live_view
  alias Octafest.Api.Post

  def mount(_params, _session, socket) do
    posts = Post.translated!(:en)

    locales = ~w(en lt)a

    create_form = get_post_create_form()

    socket =
      assign(socket,
        posts: posts,
        locales: locales,
        locale: :en
      )

    {:ok, socket}
  end
end

But i’ve decided against using this, as this is quite a bit of code and while i could most likely add an extension it feels somewhat magical.