Ecto: Create a case insensitive unique index

juffel — Fri, 10 Feb 2023 11:15:11 +0000

Today I learned that Ecto.Migration supports this out of the box 🥳

You probably know that you can easily create a unique index like this:

create unique_index("products", [:name], name: :products_lower_name_index)

But did you also know that you can do this also in a case-insensitive way?
According to the docs you can not only provide column names for a new unique index, but also a custom expression.

# Create an index on a custom expression
create unique_index("products", ["(lower(name))"], name: :products_lower_name_index)

Customize sparql ✨ client middleware with tesla ⚡️

juffel — Sun, 01 May 2022 21:41:41 +0000

Tesla is a great elixir library that makes it pretty straightforward to write well-structured, but mighty http clients. One feature that makes tesla especially nice to use, is the plug-based middleware. Similar to Phoenix.Router you can just throw in a single line plug Tesla.Middleware.JSON and your client will automatically take care of encoding & decoding request & response bodies.

Tesla comes with a variety of ready-to-use middlewares, e.g. for adding headers or setting a base url for all requests. While those included middlewares are very handy, chances are that you'll want something more custom-made at some point. Luckily you can simply create a custom middleware and plug it into your existing tesla client.

In my case, I needed a database-backed log to save requests with their query parameters & the response body. While the official tesla docs contain an example for custom middlewares it took me a couple of attempts to get it up and running properly. So here's my take on custom middlewares with tesla 💫

Starting with this simple client that queries the wikidata SPARQL query api.

defmodule Wikidata.Client do
  use Tesla

  plug Tesla.Middleware.BaseUrl, "https://query.wikidata.org"
  plug Tesla.Middleware.Headers, [{"accept", "application/sparql-results+json"}]
  plug Wikidata.SaveClientResponse

  @foods_query File.read!("lib/wikidata/queries/foods.sparql")

  defp get_response() do
    with {:ok, response} <- get("/sparql", query: [query: @foods_query]) do
      {:ok, unpack_response(response.body)}
    else
      {:error, :timeout} ->
        {:error, :wikidata_client_timeout}
      error ->
        {:error, :wikidata_client_error, error}
    end
  end

  defp unpack_response(body) do
    body
    |> Jason.decode!(keys: :atoms)
    |> Map.get(:results)
    |> Map.get(:bindings)
  end
end

Please ignore all the wikidata/sparql-related stuff, if you're not interested in that part - I just included it to give the example some relevance (and check out the post's end for the actual sparql request in case you're interested).

The line plug Wikidata.SaveClientResponse adds a custom module to the tesla middleware pipeline. A custom middleware needs to implement the Tesla.Middleware behaviour which is as simple as a module which implements a call function with this spec:

@spec call(env :: Tesla.Env.t(), next :: Tesla.Env.stack(), options :: any()) :: Tesla.Env.result()

To store the request data, I use a simple ecto schema ClientRequest:

defmodule Wikidata.ClientRequest do
  use Ecto.Schema
  import Ecto.Changeset

  schema "wikidata_client_requests" do
    field :query, :string
    field :response_body, :string

    timestamps()
  end

  def create_changeset(attrs) do
    %__MODULE__{}
    |> cast(attrs, [:query, :response_body])
  end
end

And finally the actual middleware implementation which intercepts requests and stores them as ClientRequests in the database could look like this:

defmodule Wikidata.SaveClientResponse do
  @behaviour Tesla.Middleware

  alias Wikidata.ClientRequest

  @impl Tesla.Middleware
  def call(env, next, _options) do
    with {:ok, env} <- Tesla.run(env, next) do
      save_request_data(env)

      {:ok, env}
    else
      result -> result
    end
  end

  defp save_request_data(%{query: [query: query], body: body}) do
    %{query: query, response_body: body}
    |> ClientRequest.create_changeset()
    |> Repo.insert()
  end
end
  defp save_request_data(_), do: nil

This simple middleware will save a new ClientRequest for each successful request, which includes a query param named query and returns with a body, and will just hand through all other requests of the client (i.e. timed out requests, or any request which does not include a query param). It's just a basic serving suggestion, so please adjust to taste 🍲
I.e. you want to access query params before the request is sent out? Then you'll have to access (or alter) env before you call Tesla.run/2

[1] In case you're interested in my actual use case, here's the wikidata sparql query, which fetches all foods/food ingredients with their descriptions, pictures and related food classes or instances:

SELECT ?item ?itemLabel ?itemDescription ?imageUrl ?instanceOf
WHERE
{
  ?item wdt:P31*/wdt:P279* wd:Q25403900.
  OPTIONAL { ?item wdt:P18 ?imageUrl }
  OPTIONAL { ?item wdt:P31 ?instanceOf }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en". }
}

DEV Community: juffel

Ecto: Create a case insensitive unique index

Customize sparql ✨ client middleware with tesla ⚡️