Load data from staging db to local db

Connect to remote server

$ psql
\c staging_server

Extract data from staging to your local filesystem

\copy (SELECT * from post where id=12) to posts.csv csv header
\copy (SELECT * from comments where post_id=12) to comments.csv csv header;

Connect to local server

\c my_app_dev

Import data

\copy posts from posts.csv DELIMITER ',' CSV header;
\copy comments from comments.csv DELIMITER ',' CSV header;

Done!

Handy when you need to extract only some rows from staging or prod to try something locally

Using Dynamic queries in Ecto

When you have a Phoenix Controller and you need to do a query based on the params, you might end up with something likes this:

defmodule App.PostController do
  def index(conn, params) do
     posts = App.Context.list_posts(params)
     render(conn, "index.html", posts: posts)
  end
end

defmodule App.Context do
   ......
   def  list_posts(params) do
    query = Post 

    query = if user_id = params["owner_id"] do
      query |> where([p], p.user_id == ^user_id)
    else
      query
    end

    Repo.all(query)
  end
  .....
end

There is a better way! Dynamic queries (https://hexdocs.pm/ecto/dynamic-queries.html)

defmodule App.Context do
   ......
   def  list_posts(params) do
    Post 
    |> where(^filter_where(params))
    |> Repo.all()
  end

  defp filter_where(params) do
    Enum.reduce(params, dynamic(true), fn
      {"owner_id", user_id}, dynamic ->
        dynamic([p], ^dynamic and p.user_id == ^user_id)
      {_, _}, dynamic ->
        dynamic
    end)
  end
  .....
end

Now, all your where clauses are in one place :)