Create Elixir Script that Runs SQL Before App Starts

nelsonic commented 5 years ago

We've made great progress with writing all the code in SQL in #1 |> #2 and #3 |> #4 🚀

The next stage of our quest is to invoke the SQL functions from Elixir. The Elixir script should:

[x] Register all SQL functions idempotently (CREATE OR REPLACE FUNCTION)
[x] Run mirror_tables script before the Phoenix App Starts (thus ensuring that the database tables are synched before running the app)
[x] Create trigger that runs on INSERT/UPDATE

nelsonic commented 5 years ago

@RobStallion we might not need to register the Application, see: https://github.com/ikeikeikeike/sitemap/pull/20 https://www.amberbit.com/blog/2017/9/22/elixir-applications-vs-extra_applications-guide/

nelsonic commented 5 years ago

@RobStallion how did you get on with this after our pairing session on Friday? 💭 ☀️

nelsonic commented 5 years ago

@RobStallion https://stackoverflow.com/questions/34623694/run-code-on-application-startup-phoenix-framework-elixir

RobStallion commented 5 years ago

In order to run our sql scripts we had to separate each function into it's own file and call them like so...

[
  File.read!("/postgres-history-spike/apply_alterations.sql"),
  File.read!("/postgres-history-spike/get_column_names_values.sql"),
  File.read!("/postgres-history-spike/get_column_names.sql"),
  File.read!("/postgres-history-spike/history_trigger.sql"),
  File.read!("/postgres-history-spike/create.sql")
]
|> Enum.each(&Ecto.Adapters.SQL.query!(Scripts.Repo, &1))

Ecto.Adapters.SQL.query!(Scripts.Repo, "SELECT create_history(' db name in single quotes')")

This worked when we ran the file manually (meaning from the terminal we type mix run priv/repo/filename.ex). Next step is to have this run automatically when the application starts.

RobStallion commented 5 years ago

To run the script before the application starts we are going to create a GenServer and add it to the list of children in the app_name/application.ex file.

RobStallion commented 5 years ago

Created a file called create_history.ex to be our GenServer...

defmodule Scripts.CreateHistory do
  def init(arg) do
    create_history(arg)
    {:ok, arg}
  end

  defp create_history(db_name) do
    [
      File.read!("/postgres-history-spike/apply_alterations.sql"),
      File.read!("/postgres-history-spike/get_column_names_values.sql"),
      File.read!("/postgres-history-spike/get_column_names.sql"),
      File.read!("/postgres-history-spike/history_trigger.sql"),
      File.read!("/postgres-history-spike/create.sql")
    ]
    |> Enum.each(&Ecto.Adapters.SQL.query!(Scripts.Repo, &1))

    Ecto.Adapters.SQL.query!(Scripts.Repo, "SELECT create_history(#{db_name})")
  end
end

but when we tried to run the application we got the following error...

[info] Application scripts exited: exited in: Scripts.Application.start(:normal, [])
    ** (EXIT) an exception was raised:
        ** (ArgumentError) The module Scripts.CreateHistory was given as a child to a supervisor
but it does not implement child_spec/1.

If you own the given module, please define a child_spec/1 function
that receives an argument and returns a child specification as a map.
For example:

    def child_spec(opts) do
      %{
        id: __MODULE__,
        start: {__MODULE__, :start_link, [opts]},
        type: :worker,
        restart: :permanent,
        shutdown: 500
      }
    end

Note that "use Agent", "use GenServer" and so on automatically define
this function for you.

Looks like we forgot to put use GenServer in the module 🤦‍♂

RobStallion commented 5 years ago

We have updated the GenServer as follows

defmodule Scripts.CreateHistory do
  use GenServer

  def start_link(_args) do
    GenServer.start_link(__MODULE__, "scripts_dev")
  end

  def init(db_name) do
    create_history(db_name)
    {:ok, db_name}
  end

  defp create_history(db_name) do
    [
      File.read!("postgres-history-spike/apply_alterations.sql"),
      File.read!("postgres-history-spike/get_column_names_values.sql"),
      File.read!("postgres-history-spike/get_column_names.sql"),
      File.read!("postgres-history-spike/history_trigger.sql"),
      File.read!("postgres-history-spike/create.sql")
    ]
    |> Enum.each(&Ecto.Adapters.SQL.query!(Scripts.Repo, &1))

    Ecto.Adapters.SQL.query!(Scripts.Repo, "SELECT create_history('#{db_name}')")
  end
end

and we are now creating the history tables when the application starts.

nelsonic commented 5 years ago

@RobStallion take a look at this: https://github.com/ikeikeikeike/sitemap/blob/master/lib/sitemap.ex

RobStallion commented 5 years ago

I have added ecto as a dependency to the application so that it is able to create / alter table etc. Next step is to create a migration file and run that file to create a database table. If that runs as expected the next step will be to start the application and see if the history table is automatically created.

[x] add ecto
[x] create migration
[x] run migration
[x] run application
[x] check database tables

RobStallion commented 5 years ago

animals table created...

➜  postgres-history-spike git:(delete-trigger) mix ecto.create && mix ecto.migrate
The database for PostgresHistory.Repo has been created

15:33:43.428 [info]  == Running 20190603142009 PostgresHistory.Repo.Migrations.Animals.change/0 forward

15:33:43.429 [info]  create table animals

15:33:43.434 [info]  == Migrated 20190603142009 in 0.0s

Screenshot of the tables that currently exist after running the migration.

Next step is to run the application with iex -S mix

RobStallion commented 5 years ago

Ran iex -S mix but it didn't create a new table. I think I may have left the incorrect db name hardcoded in the script so going to check that now

RobStallion commented 5 years ago

  def start_link(_args) do
    GenServer.start_link(__MODULE__, "scripts_dev")
  end

Yep, 😑

Will put the correct name in now and try again.

RobStallion commented 5 years ago

Worked 🎉

RobStallion commented 5 years ago

Triggers are also working as expected 👍

Now that we have confirmed that we can run the scripts from an elixir application on start up, the final step is to try and require this application into a separate app as a dep and see if we can recreate these results.

RobStallion commented 5 years ago

Steps

[x] Create a new phoenix project locally called require_history.
[x] Temp: for now, manually update the db name in start_link to require_history_dev
[x] Require postgres_history (this app) as a dependency of require_history
[ ] Start require_history and inspect the logs
[ ] Create a migration file to create a db table
[ ] Start require_history again and see if the history table is created.

RobStallion commented 5 years ago

When I tried to start the phoenix application, require_history, I received the following error...

[info] Application postgres_history exited: PostgresHistory.Application.start(:normal, []) returned an error: shutdown: failed to start child: PostgresHistory.CreateHistory
    ** (EXIT) an exception was raised:
        ** (File.Error) could not list directory "sql": no such file or directory
            (elixir) lib/file.ex:1536: File.ls!/1

This error is coming from the following function...

  defp create_history(db_name) do
    "sql"
    |> File.ls!()
    |> Enum.map(&File.read!("sql/#{&1}"))
    |> Enum.each(&Ecto.Adapters.SQL.query!(PostgresHistory.Repo, &1))

    Ecto.Adapters.SQL.query!(PostgresHistory.Repo, "SELECT create_history('#{db_name}')")
  end

and appears to be caused because the parent app (require_history) does not have a folder called sql in its route.

Going to see if the path can be updated to reflect that this is a dependency now

RobStallion commented 5 years ago

Temporarily, I have updated the text "sql" to the full file path to get around this issue. This will not be a long term solution but thought that this was something that we could come back to later.

This resolved the initial error but now I am getting the following...

Application postgres_history exited: PostgresHistory.Application.start(:normal, []) returned an error: shutdown: failed to start child: PostgresHistory.CreateHistory
    ** (EXIT) exited in: DBConnection.Holder.checkout(#PID<0.296.0>, [log: #Function<11.126414906/1 in Ecto.Adapters.SQL.with_log/3>, timeout: 15000, pool_size: 10, pool: DBConnection.ConnectionPool])

I'm not exactly sure what is causing this issue. I think it could be to do with the fact that currently both applications are trying to start ecto.repo.

I am going to remove it from the dependency and see if that resolves the issue.

RobStallion commented 5 years ago

If I remove all the calls to ecto from postgres_history (and put the full file paths for the sql functions) then we are able to start the application (require_history). I can also confirm that it is running the create_history function from postgres_history as expected.

However, as all the calls to ecto have been removed from postgres_history, it is not currently creating the tables.

Need to look into a way of creating the database tables from within the dependency without it trying to start ecto itself.

nelsonic commented 5 years ago

@RobStallion looks like you're making good progress! (thanks for updating the issue as you go...) 👍

RobStallion commented 5 years ago

I read that the module Guardian creates a database table so I am going to look at the source code to see if we can learn anything.

RobStallion commented 5 years ago

After looking at Guardian, I thought it could be useful if the parent project (require_history) had some info in the config file which allowed the dependency (postgres_history) to know which db to access.

I added the following lines to the config.exs file in require_history...

config :postgres_history, PostgresHistory.CreateHistory,
  repo: RequireHistory.Repo,
  database: "require_history_dev"

In postgres_history I have commented out PostgresHistory.Repo in application.ex...

  def start(_type, _args) do
    children = [
      # PostgresHistory.Repo,
      PostgresHistory.CreateHistory
    ]

In postgres_history's create_history.ex, I have added 2 functions which get the repo name and database name from the parent app's config file (the lines added at the top of this comment)...

  def repo do
    :postgres_history
    |> Application.fetch_env!(PostgresHistory.CreateHistory)
    |> Keyword.fetch!(:repo)
  end

  def database do
    :postgres_history
    |> Application.fetch_env!(PostgresHistory.CreateHistory)
    |> Keyword.fetch!(:database)
  end

I have used these functions in the create_history function like so...

  defp create_history(db_name) do
    repo = repo()

    "full_path/sql"
    |> File.ls!()
    |> Enum.map(&File.read!("full_path/sql/#{&1}"))
    |> Enum.each(&Ecto.Adapters.SQL.query!(repo, &1))

    Ecto.Adapters.SQL.query!(PostgresHistory.Repo, "SELECT create_history('#{db_name}')")
  end

When I run this I get the following error...

[info] Application postgres_history exited: PostgresHistory.Application.start(:normal, []) returned an error: shutdown: failed to start child: PostgresHistory.CreateHistory
    ** (EXIT) an exception was raised:
        ** (RuntimeError) could not lookup RequireHistory.Repo because it was not started or it does not exist

The key part of this error is the final line, could not lookup RequireHistory.Repo because it was not started or it does not exist.

This lead me to believe that everything could be okay but it is an issue to do with the order that the module is being loaded.

RobStallion commented 5 years ago

To test and see if this code would work if the require_history's repo had been started I am going to remove the call to create_history in postgres_history and instead call that function myself in the iex shell.

Made a small change to create_history as it no longer needs variables to be passed in...

  def create_history do
    repo = repo()
    db_name = database()

    "/Users/robertfrancis/Code/work/postgres-history-spike/sql"
    |> File.ls!()
    |> Enum.map(&File.read!("/Users/robertfrancis/Code/work/postgres-history-spike/sql/#{&1}"))
    |> Enum.each(&Ecto.Adapters.SQL.query!(repo, &1))

    Ecto.Adapters.SQL.query!(repo, "SELECT create_history('#{db_name}')")
  end

Now that I have removed to call to create_history from postgres_history, I am able to start require_history...

➜  require_history git:(master) ✗ iex -S mix
Erlang/OTP 21 [erts-10.3.1] [source] [64-bit] [smp:8:8] [ds:8:8:10] [async-threads:1] [hipe] [dtrace]

==> postgres_history
Compiling 1 file (.ex)
Interactive Elixir (1.8.1) - press Ctrl+C to exit (type h() ENTER for help)
iex(1)>

Now in the iex shell I am going to call create_history and see if we get the results we were after...

iex(1)> PostgresHistory.CreateHistory.create_history()
... (loads of sql logs in the middle)
SELECT create_history('require_history_dev') []
%Postgrex.Result{
  columns: ["create_history"],
  command: :select,
  connection_id: 30092,
  messages: [],
  num_rows: 1,
  rows: [[true]]
}

This appears to have worked. However I didn't actually create any tables in the parent application so I would need to do that first in order to confirm.

I will create a migration file in require_history to create a table and then repeat these steps.

RobStallion commented 5 years ago

Created migration...

defmodule RequireHistory.Repo.Migrations.CreateUsers do
  use Ecto.Migration

  def change do
    create table(:users) do
      add(:name, :string)
    end
  end
end

Ran the migration and the table has been created...

Started require_history in the shell and ran the create_history function...

iex(1)> PostgresHistory.CreateHistory.create_history()

Users_history table successfully created...

RobStallion commented 5 years ago

Now that we know that the function definitely works the way we would expect the final step for us is to work out how we can get it run after the parent module has loaded started the Repo, e.g. avoid the error mentioned here

RobStallion commented 5 years ago

If I call the function from the child application I am unable to start the parent app...

application.ex in child (postgres_history)...

    children = [
      PostgresHistory.CreateHistory
    ]

create_history.ex in child...

  def init(args) do
    create_history()
    {:ok, args}
  end

Same error...

[info] Application postgres_history exited: PostgresHistory.Application.start(:normal, []) returned an error: shutdown: failed to start child: PostgresHistory.CreateHistory
    ** (EXIT) an exception was raised:
        ** (RuntimeError) could not lookup RequireHistory.Repo because it was not started or it does not exist

Instead of trying to start the CreateHistory GenServer in the child app, I am going to move it into the parent app and see if this solves the issue.

RobStallion commented 5 years ago

In postgres_history the CreateHistory module will remain unchanged. application.ex looks like so...

defmodule PostgresHistory.Application do
  use Application

  def start(_type, _args) do
    children = []

    opts = [strategy: :one_for_one, name: PostgresHistory.Supervisor]
    Supervisor.start_link(children, opts)
  end
end

(It is no longer starting any children so I think that we may be able to remove it completely but I will come back to this afterwards if this works)

In require_history application.ex looks like so...

defmodule RequireHistory.Application do
  use Application

  def start(_type, _args) do
    children = [
      RequireHistory.Repo,
      RequireHistoryWeb.Endpoint,
      PostgresHistory.CreateHistory # <------ Added this line
    ]

    opts = [strategy: :one_for_one, name: RequireHistory.Supervisor]
    Supervisor.start_link(children, opts)
  end

  def config_change(changed, _new, removed) do
    RequireHistoryWeb.Endpoint.config_change(changed, removed)
    :ok
  end
end

I removed all comments to try and keep things concise. The only other change was the only added line.

Next step is to run the application again and see if it works.

RobStallion commented 5 years ago

This works.

@nelsonic let me know your thoughts on this. It is working but the user has to do a little more than you hoped. However, currently I cannot see a way around this.

In the current state, if someone wanted to add this postgres_history to an existing phoenix application as a dependency, they would need to do the following...

`mix.exs`

Get the app

      {:postgres_history, path: "some path"} # <-- as it is not published yet user would need locally

`config.exs`

Set the apps Repo name and database name so postgres_history knows which db to create the tables/triggers.

config :postgres_history, PostgresHistory.CreateHistory,
  repo: RequireHistory.Repo,
  database: "require_history_dev"

`application.ex`

Ensures tables/triggers are created when application starts.

      PostgresHistory.CreateHistory # <--- would need to be added to list of children after AppName.Repo

All of this would only need to be once and would work for new tables added in the future etc.

nelsonic commented 5 years ago

@RobStallion for a Spike this is superb! Once we have shipped a package to Hex.pm we can request improvements from the community. 👍 Do you have time to do a walkthrough of the code with me on Zoom?

dwyl / postgres-history-spike