Running Phonix with SQLite for weather-data directly on the Pi (with nerves)

Nerves Forum Nerves Chat
, ,
1

Hi,

I’m new to nerves but already managed to build the Weather Station (and also read the Binary-Clock-Book).

Now I want to modify the Weather-Station to make phoenix actually run directly on the Pi, so that I don’t need another Computer to hold the database. I started with adding hello_phoenix next to my sensor_hub folder. This worked good enough. After flashing the weather station I can see the phoenix default page in my browser. Now I need to add a database. I couldn’t find that much anywhere about that, but finally tried this to run SQLite on the pi. I had dependency problems when trying to add the :sqlite_ecto2 dependency but since the hello_phoenix example already had :ecto_sql and :ecto_sqlite3 in the mix.exs I skipped that step in the article. Then I configured the config.exs as described with

config :ui, Ui.Repo,
  adapter: Sqlite.Ecto2,
  database: "#{Mix.env}.sqlite3"

config :ui, ecto_repos: [Ui.Repo]

Running iex -S mix on my host now creates a database file at the root-level. But that one is not named ‘host.sqlite3’ as I understand it from the article but just ‘ui_dev.db’. It seems ecto is not asking for the database name I specified. Well this is not that much of a problem since I actually want to run it on the pi, right? But since I need to configure (later in the article) the place for the database on the pi by writing

config :ui, Ui.Repo,
  adapter: Sqlite.Ecto2,
  database: "/root/#{Mix.env}.sqlite3"

config :ui, ecto_repos: [Ui.Repo]

into a config/rpi2.exs. Well, that also doesn’t create any file into ‘/root/rpi2.sqlite3’ as far as I can see the filesystem on the pi (I checked with File.ls/1 after ssh-ing to the pi). Ok, Seems I have to fix the root of that problem. Does anyone have an idea what I am doing wrong? Has anybody done that before (running phoenix with a database for the weather-data on the target).

I wonder why I couldn’t find any good project modification for the weather-station anywhere. For me it is a obvious thing to do and I’m really wondering why it is not already included in the book. I don’t see that much of a use for a weather-station on a raspberry pi which needs another computer to run the database…

1 Like
2

I’d recommend trying Ecto SQLite3 instead of SQLite2: GitHub - elixir-sqlite/ecto_sqlite3: An Ecto SQLite3 adapter.

The readme of the project should help you get started with it and there’s not really any nerves-specific setup that you’ll need I think.

To create the database I usually create a release module like:

defmodule Dash.Release do
  @app :dash

  def migrate do
    load_app()

    for repo <- repos() do
      {:ok, _, _} = Ecto.Migrator.with_repo(repo, &Ecto.Migrator.run(&1, :up, all: true))
    end
  end

  def rollback(repo, version) do
    load_app()
    {:ok, _, _} = Ecto.Migrator.with_repo(repo, &Ecto.Migrator.run(&1, :down, to: version))
  end

  defp repos do
    Application.fetch_env!(@app, :ecto_repos)
  end

  defp load_app do
    Application.load(@app)
  end
end

Then connect to your Pi via ssh and run Dash.Release.migrate() and that should create the sqlite file on the Pi.

Good luck!

3 Likes
3

As mentioned in the nerves docs here: Frequently Asked Questions — nerves v1.10.5, you’ll probably want to place your DB somewhere under the /data partition. I believe the root filesystem on nerves is read-only by default.

1 Like
4

/data is actually a symlink to /root so database: "/root/#{Mix.env}.sqlite3" will work. Although using /data directly is generally recommended since that may change in the future.

2 Likes
5

There may not be enough code here to identify why it’s not working on device. My assumption is it will be configuration related since that is handled very specifically in Elixir. Configurations are not inherited from dependencies so those values need to be in the top-level project that is building the firmware.

This is actually a very common use-case for a remote device to only be the interface with sensors and transmitting the data. SD cards and small memory modules have a finite number of writes so to promote the longevity of the device, one might be motivated to avoid writing to disk as much as possible and simply forward data remote to be collected and ingested elsewhere. These devices are typically much smaller processors as well. The more load you put into database management, queries, etc, the more potential you take away from collecting of the sensor data.

This is not to say it is uncommon to have a database on device either, but rather it requires thought and actually sometimes reasonable

1 Like