Dave's page

Elixir and Phoenix and Ecto -- A few clues about database access etc

1   Introduction

The toolset that includes Elixir, Mix, Phoenix, Ecto, etc provides an easy way to generate the working skeleton of a Web CRUD app (where "CRUD" = create, read, update, delete).

However, there are many needs and uses that will require writing code and using tools that run outside of the Web framework.

This article provides some suggestions and clues for how to solve those problems.

2   Ecto.Query

Read can read the documentation on Ecto here -- https://hexdocs.pm/ecto/Ecto.html.

2.1   The Ecto.Query.API

You can find it documented here -- https://hexdocs.pm/ecto/Ecto.Query.html.

You might find the following especially useful when you are constructing queries to be executed against your database: like/2, ilike/2, struct/2, field/2.

Here is an example query:

iex> Ecto.Query.from(sr in Sedb.Searchresults.Searchresult, select: [:title, :url], where: ilike(field(sr, :searchstr), "%python%")) |> Sedb.Repo.all

Notes:

  • The above query returns a list of maps containing the values of specified fields.
    • The select: clause -- Each map contains values for the fields title and url.
    • The where: clause -- Only the records in which the field searchstr contains the substring "python" are returned.
  • For information on the Postgresql like (case sensitive) and ilike (case insensitive) matching functions see -- https://www.postgresql.org/docs/15/functions-matching.html#FUNCTIONS-LIKE

I also wrote an Elixir function to do something similar. Here it is:

@doc """
Filter record using pattern on a specified field.
Print out the requested fields.

## Params

  * `filter_field` -- The field on which to filter items.
  * `pattern` -- The pattern to filter with.
  * `print_fields_atoms` -- A list of fields to be printed.

## Options

  * `json:` -- If true, print out Json, not plain text info.

## Examples

    iex> Test01.test04(:searchstr, "%evolution%", [:searchstr, :title, :url])

"""
@spec test04(atom(), String.t(), [atom()], keyword()) :: :ok | Map.t()
def test04(filter_field, pattern, print_fields_atoms, options \\ []) do
  json = Keyword.get(options, :json, false)
  items = Ecto.Query.from(sr in Searchresult, where: ilike(field(sr, ^filter_field), ^pattern))
          |> Sedb.Repo.all
  if json do
    result = Enum.map(items, fn item ->
      %{
        :searchstr => item.searchstr,
        :title => item.title,
        :abstract => item.abstract,
        :url => item.url
      }
    end)
    case Jason.encode(result) do
      {:ok, json_text} ->
        {:ok, json_text}
      {:error, message} ->
        {:error, message}
      _ ->
        "Error -- Cannot decode JSON"
    end
  else
      items
      |> Enum.each(fn item ->
        IO.puts("----")
        print_fields_atoms
        |> Enum.map(fn atom ->
          "#{String.pad_trailing(Atom.to_string(atom) <> ":", 12)} #{Map.get(item, atom)}"
        end)
      #|> IO.inspect(label: "content")
      |> Enum.each(fn line -> IO.puts(line) end)
      end)
      :ok
  end
end

Notes:

  • We use Keyword.get/3 to retrieve the value of the :json keyword from the options keyword list, or the default value false if the :json keyword is not present.

  • If the :json option is present and true, we use the Jason module to encode the retrieved data structures as a JSON string, and we return that string.

  • The Jason module is available because I've included the following in my list of dependencies in my mix.exs file:

    defp deps do
  [
    o
    o
    o
    {:jason, "~> 1.2"},
  ]
end

  • And, if the :json option is not true, we print out the values of the requested fields.

You can call the above with the following:

iex> {:ok, a} = Test01.test04(:searchstr, "%evolution%", [:url, :title], json: true)
iex> {:ok, b} = Jason.decode(a)
iex> c = hd tl b
iex(37)> IO.puts(~s(title: "#{c["title"]}"  url: "#{c["url"]}"))

Notes:

  • Calling Test01.test04/4 with the :json option produces a JSON string.
  • Jason.decode/1 turns that string into Elixir data structures, in this case a list of maps.
  • The expression hd tl b gets the head-of-the-tail, which is the second item in that list.
  • The call to IO.puts/1 prints out the values of the "title" and "url" keys in that map. Note the use of the string sigil, which enables me to use double-quotes in a string.

3   The API generated by Mix

At least part of the API generated by mix, is in app/lib/<app-name>/<db-table-name>.ex, which, in my case is sedb/lib/sedb/searchresults.ex.

That generated code is Elixir code. So, you can, of course, add to it. Here is a function that I added:

@doc """
Filter records and display records whose specified field contains a pattern.

Possible field names are "id", "searchstr", "title", "abstract".

## Params

  * `field` -- The name of the field to match against.
    Possible field names are "id", "searchstr", "title", "abstract".

  * `pattern` - The pattern that must match.  The string will be
    converted to a regular expression and will be used in a
    regex match.

  * `options` - (optional) A binary/String as accepted by `Regex.compile`.
    Default: "".

## Examples

    $ ./sedb filter searchstr "economic evolution"
    $ ./sedb filter title "Easy|perfection"
"""
def filter_searchresult(field, pattern, options \\ "") do
  {:ok, cpattern} = Regex.compile(pattern, options)
  field_atom = String.to_atom(field)
  searchresults = Sedb.Searchresults.list_searchresults
  item = hd(searchresults)
  if field_atom not in Map.keys(item) do
    IO.puts("\nError. Map does not contain key \"#{field}\".\n")
    Kernel.exit(:error)
  end
  searchresults
  |> Stream.filter(fn searchresult ->
    Map.get(searchresult, field_atom) =~ cpattern end)
  |> Stream.with_index()
  |> Enum.each(fn {searchresult, idx} ->
    IO.puts("#{idx + 1}. search result -- id: #{searchresult.id}:")
    IO.puts("    searchstr: #{searchresult.searchstr}")
    IO.puts("    title: #{searchresult.title}")
    IO.puts("    abstract: #{searchresult.abstract}")
    IO.puts("    url: #{searchresult.url}")
  end)
end

A somewhat better solution, rather that to retrieve all the records and filter them, might be to use the Ecto.Query, as described in section The Ecto.Query.API. Here is an example function that does roughly the same task as the above:

@spec ecto_filter_records(String.t(), String.t(), String.t()) :: {:ok, list()} | :error
def ecto_filter_records(field, pattern, options \\ "") do
  require Ecto.Query

  fieldatom = String.to_atom(field)
  items = if options =~ ~r/i/ do
    Ecto.Query.from(sr in Sedb.Searchresults.Searchresult, where: ilike(field(sr, ^fieldatom), ^pattern))
  else
    Ecto.Query.from(sr in Sedb.Searchresults.Searchresult, where: like(field(sr, ^fieldatom), ^pattern))
  end
  |> Sedb.Repo.all
  items
  |> Enum.with_index()
  |> Enum.each(fn {record, idx} ->
    IO.puts("item #{idx + 1}.")
    IO.puts("    id: #{record.id}")
    IO.puts("    searchstr: #{record.searchstr}")
    IO.puts("    title: #{record.title}")
  end)
  if length(items) == 0 do
    :error
  else
    {:ok, items}
  end
end

4   Writing an escript (revisited)

Also see my previous blog article: http://www.davekuhlman.org/elixir-phoenix-crud-escript.html#writing-building-and-using-an-escript

A limitation/deficiency of Mix is that you cannot generate multiple escript executables in the same app. A possible, though somewhat clumsy work-around could be to make a copy of your mix.exs file, specify the second escript in that copy, then switch back and forth between them (perhaps by copying or renaming. Ugh. That solution is simple, though somewhat inelegant. Another, possibly simpler solution is to copy that line or lines of code, then comment out one or the other before compiling with $ mix escript.build, for example:

def project do
  [
    o
    o
    o
    escript: [main_module: Sedb.CLI01],
    #escript: [main_module: Sedb.CLI02],
  ]
end

Or, as an alternative to the above, and since I have not found a way to pass a parameter into mix.exs, we could use an environment variable. For example, I have the following in my mix.exs:

# mix.exs
def project do
  [
    o
    o
    o
    escript: escript(),
  ]
end

def escript do
  task = System.get_env("ESCRIPT_TASK")
  case task do
    "CLI01" ->
      IO.puts("using ESCRIPT_TASK=CLI01 (default)")
      [main_module: Sedb.CLI01]
    "CLI02" ->
      IO.puts("using ESCRIPT_TASK=CLI02")
      [main_module: Sedb.CLI02]
    _ ->
      IO.puts("use environment variable ESCRIPT_TASK=CLI01|CLI02")
      IO.puts("using ESCRIPT_TASK=CLI01 (default)")
      [main_module: Sedb.CLI01]
  end
end

The above enables me to use an environment variable to control which of two scripts is included in the generated escript. So, for example, I can run the following to generate a escript from module Sedb.CLI01:

$ ESCRIPT_TASK=CLI01 mix escript.build

And here is that escript:

defmodule Sedb.CLI01 do
  @moduledoc """
  ## synopsis:

    Add or list search result records in the project database.
    Read input from stdin.
    The input is JSON.

  ## usage

      $ sedb <command> [ field pattern ]

  where <command> = "add" or "list" or "filter".

  ## examples

      $ cat input_file.json > ./sedb add
      $ ./sedb list
      $ ./sedb filter searchstr "economic evolution"
      $ ./sedb filter title "Easy|perfection"
  """
  def main(args) do
    case args do
      ["add"] ->
        add_records()
      ["list"] ->
        list_records()
      ["filter", field, pattern] ->
        filter_records(field, pattern)
      ["filter", field, pattern, options] ->
        filter_records(field, pattern, options)
      ["ecto", field, pattern] ->
        ecto_filter_records(field, pattern)
      ["ecto", field, pattern, options] ->
        ecto_filter_records(field, pattern, options)
      _ ->
        IO.puts(@moduledoc)
    end
  end

  def add_records do
    jsonstr = IO.read(:stdio, :eof)
    {:ok, jasonstruct} = Jason.decode(jsonstr)
    searchstr = jasonstruct["searchstr"]
    items = jasonstruct["items"]
    items |> Enum.each(fn item ->
      item1 = Map.put(item, "searchstr", searchstr)
      IO.puts(~s(searchstr: #{item1["searchstr"]}))
      IO.puts(~s(title: #{item1["title"]}))
      IO.puts(~s(url: #{item1["url"]}))
      IO.puts(~s(abstract: #{item1["abstract"]}))
      IO.puts("----")
      {:ok, _} = Sedb.Searchresults.create_searchresult(item1)
    end)
  end

  def list_records do
    searchresults = Sedb.Searchresults.list_searchresults
    searchresults
    |> Enum.with_index()
    |> Enum.each(fn {searchresult, idx} ->
      IO.puts("#{idx + 1}. search result -- id: #{searchresult.id}:")
      IO.puts("    searchstr: #{searchresult.searchstr}")
      IO.puts("    title: #{searchresult.title}")
      IO.puts("    abstract: #{searchresult.abstract}")
      IO.puts("    url: #{searchresult.url}")
    end)
  end

  @doc """
  Filter records and display records whose specified field contains a pattern.

  Possible field names are "id", "searchstr", "title", "abstract".

  ## Params

    * `field` -- The name of the field to match against.
      Possible field names are "id", "searchstr", "title", "abstract".

    * `pattern` - The pattern that must match.  The string will be
      converted to a regular expression and will be used in a
      regex match.

    * `options` - (optional) A binary/String as accepted by `Regex.compile`.
      Default: "".

  ## Examples

      $ ./sedb filter searchstr "economic evolution"
      $ ./sedb filter title "Easy|perfection"
  """
  def filter_records(field, pattern, options \\ "") do
    # see -- lib/sedb/searchresults.ex
    Sedb.Searchresults.filter_searchresult(field, pattern, options)
#     {:ok, cpattern} = Regex.compile(pattern, options)
#     field_atom = String.to_atom(field)
#     searchresults = Sedb.Searchresults.list_searchresults
#     item = hd(searchresults)
#     if field_atom not in Map.keys(item) do
#       IO.puts("\nError. Map does not contain key \"#{field}\".\n")
#       Kernel.exit(:error)
#     end
#     searchresults
#     |> Stream.filter(fn searchresult ->
#       Map.get(searchresult, field_atom) =~ cpattern end)
#     |> Stream.with_index()
#     |> Enum.each(fn {searchresult, idx} ->
#       IO.puts("#{idx + 1}. search result -- id: #{searchresult.id}:")
#       IO.puts("    searchstr: #{searchresult.searchstr}")
#       IO.puts("    title: #{searchresult.title}")
#       IO.puts("    abstract: #{searchresult.abstract}")
#       IO.puts("    url: #{searchresult.url}")
#     end)
  end

  @doc """
  Filter records and display records whose specified field contains a pattern.

  Possible field names are "id", "searchstr", "title", "abstract".

  ## Params

    * `field` -- The name of the field to match against.
      Possible field names are "id", "searchstr", "title", "abstract".

    * `pattern` - The pattern that must match.  The string will be
      converted to a regular expression and will be used in a
      regex match.

    * `options` - (optional) A binary/String, "i" means do case
      insensitive match.  Default: "" (case sensitive match).

  ## IEx interactive examples

      iex> Sedb.CLI.ecto_filter_records("title", "%python%", "i")
      iex> Sedb.CLI.ecto_filter_records("title", "%python%")

  ## Command line (escript) examples

      $ ./sedb ecto searchstr "economic evolution"
      $ ./sedb ecto title "Easy|perfection"

  """
  @spec ecto_filter_records(String.t(), String.t(), String.t()) :: {:ok, list()} | :error
  def ecto_filter_records(field, pattern, options \\ "") do
    require Ecto.Query

    fieldatom = String.to_atom(field)
    items = if options =~ ~r/i/ do
      Ecto.Query.from(sr in Sedb.Searchresults.Searchresult, where: ilike(field(sr, ^fieldatom), ^pattern))
    else
      Ecto.Query.from(sr in Sedb.Searchresults.Searchresult, where: like(field(sr, ^fieldatom), ^pattern))
    end
    |> Sedb.Repo.all
    items
    |> Enum.with_index()
    |> Enum.each(fn {record, idx} ->
      IO.puts("item #{idx + 1}.")
      IO.puts("    id: #{record.id}")
      IO.puts("    searchstr: #{record.searchstr}")
      IO.puts("    title: #{record.title}")
    end)
    if length(items) == 0 do
      :error
    else
      {:ok, items}
    end
  end

end

5   Using psql (for Postgresql users)

If you want to use Postgresql itself to view and manipulate your database, you can do that, too.

psql it the interactive prompt (REPL: read, evaluate, print loop) that enables you to evaluate SQL queries and commands provided by psql itself.

For my Phoenix application, I start psql with the following command line:

$ psql -h localhost -U postgres sedb_dev

Notes:

  • My Postgresql user is postgres.
  • The name of my Phoenix project is "sedb". So, the development database is sedb_dev.

At the psql interactive prompt, we can enter and evaluate SQL expressions.

Here is a short sample session:

$ psql -h localhost -U postgres sedb_dev
psql (14.5)
Type "help" for help.

sedb_dev=# select id, searchstr, title, url from searchresults where id >= '40' and id <= '44' order by id;
 id |     searchstr     |                                   title                                   |                               url
----+-------------------+---------------------------------------------------------------------------+------------------------------------------------------------------
 40 | lamb chops        | Lamb Chops - Fresh Cut Lamb Chops at the Best Price! - Farmer's Fresh ... | https://farmersfreshmeat.com/shop/lamb-chops/
 41 | lamb chops        | Lamb chop recipes | BBC Good Food                                         | https://www.bbcgoodfood.com/recipes/collection/lamb-chop-recipes
 42 | python  evolution | Python 3.10.8 documentation                                               | https://docs.python.org/
 43 | python  evolution | The Python Tutorial — Python 3.10.8 documentation                         | https://docs.python.org/3/tutorial/index.html
 44 | python  evolution | Our Documentation | Python.org                                            | https://www.python.org/doc/
(5 rows)

sedb_dev=# \q
$

Notes:

  • We select all the records whose id field is greater than or equal to 40 and less than or equal to 44.
  • We order those selected records by the value of the id field.
  • For each of those selected records, we display fields id, searchstr, title, and url.

For more information on this, see:

At the psql interactive prompt, you can also get help by typing:

  • help -- Display help for psql help.
  • \? -- Display help for ``psql commands.
  • \h -- Display list of SQL commands.
  • \h <sql-command> -- Display help for SQL command. For example, to display help for the SQL select command, use:: \h select.

Published

Category

elixir

Tags

Contact