Phoenix, Ecto and MySQL Web CRUD app

P. Madhanasekaran

Phoenix is a popular MVC framework in Elixir and we saw the same in earlier articles. Ecto is an Elixir library meant for interaction with the database. MySQL, as you know, is an open source relational database. We will see how to use these technologies together to create and run a database-backed web application.  Phoenix has phoenix_ecto component to use Ecto. A new phoenix app, by-default, has support for Ecto and PostgreSQL database. But to make use of any other database we can pass that as an option when generating a new Phoenix app, if the driver for the database is on the path. A list of databases so supported is given in the next paragraph. Ecto looks like an ORM and provides the functionalities usually provided by an ORM. As there are no classes in Elixir it cannot be an ORM. But like an ORM, it enables you to interact with a database without writing any “SQL”. The github page of Ecto calls it “a domain specific language for writing queries and interacting with databases in Elixir”. To the users it is apparently similar to ActiveRecord in Rails, as it provides scaffolding commands to generate a schema and migrate it to a database and generate and run a full-fledged CRUD web app without any code from us.  Ecto was influenced by LINQ in C#; while the scalability of ActiveRecord and its suitability for Enterprise Applications are questioned, there are no such issues with Phoenix and Ecto, as they are Elixir libraries. As you know, Elixir runs on Erlang VM, which is known for its scalability and fault-tolerance. Phoenix and Ecto combine the productivity of Rails with the performance of Erlang VM. For the steps on “how” to generate a CRUD app with Phoenix, Ecto and PostgreSQL, the talk “Create new blog Post” by Nhu Nguyen available on YouTube is suggested. The intention of this article is to see something more about Phoenix and the Ecto and use MySQL, the database we are used to.


Ecto has four main components:

  • Ecto.Repo . It provides functions to create, update, destroy and query existing entries in the data-store . A repo needs an *adapter and credentials to communicate to the database.
  • Ecto.Model/Schema – The version of Phoenix we use, 1.2, supports only Ecto-1.  Hence our sample uses model component to map tables into Elixir data. In Ecto-2 model has been deprecated and only schemas are used to map any data source into an Elixir struct
  • Ecto.Changeset – The Changeset provides a mechanism to track and validate changes before they are applied to our data. They also provide a way for developers to filter and cast external parameters where necessary
  • Ecto.Query – It is a DSL written in Elixir .The queries are used to retrieve and manipulate data/information from a given repo. Queries in Ecto are secure, avoiding common problems like SQL Injection, while still being composable, allowing developers to build queries piece by piece instead of all at once. From the generated code one may not realize the full potential of Ecto.Query. One is advised to go through the documentation available on

*Adapters are database-specific. Ecto currently has adapters for the following databases:

  • PostgreSQL
  • MySQL
  • SQLite3
  • MongoDB

Newly generated Phoenix applications integrate both Ecto and the PostgreSQL adapter by default. If you want to use another database, say MySQL, with Ecto   you can do so by specifying the same with –database option as under:
mix book - -database mysql
“book”  is the name of our application

Generate a Phoenix resource

To generate a resource, the scaffolding command to be executed is:

If you execute the task it creates Model, Controller and View files under /web and a migration file under /priv/repo/migrations. You can easily migrate this to the database. But before doing that, you will be asked to add a route under browser scope in web\router.ex.  You can put some routes or even a single route under a scope and apply some common plugs to it or them before forwarding the request to a matching controller action. The route to be added to the Browser scope is as under:

resources “/books”, BookController

This is a macro which creates many route-functions for resources /books, as we will see below:

mix ecto.migrate

This command will compile the files created in the earlier step and when you see the message “creating table books “
 migrated in _ seconds” 

you can go to MySQL and under “book_dev” database see “books” table.


As mentioned earlier, the following macro we added to Browser scope generates a number of route functions.
resources “/books”, BookController
You can execute

mix phoenix.routes
and see the full list of routes generated by it for HTTP verbs GET POST etc. Some of them are shown here under:
1)Page_path GET  / Book.PageController :index
2)Page_path GET  / books Book.BookController :index
3)Page_path GET  / books/new  Book.BookController :new
4) Page_path  POST  / books/  Book.BookController :create

Run the application: The application can be run executing
mix phoenix.server
in the browser navigate to localhost:4000/books. You will see figure -1. The route-2 in the above list applies and a GET request for /books  is matched  to index action in BookController The code of the action is given under: 
def index(conn, _params) do                                                        
books = Repo.all(Book)
render(conn, "index.html", books: books)

As a plug, this action takes a connection and returns a modified connection. The whole purpose of this action is to get all of the books from the database and display them in the index.html.eex template. We use the built-in Repo.all/1 query to do that, and we pass in the (aliased) model name. In Future, when Ecto-2 is used, we may have to pass a schema/table -name. For more details on how to fetch data from more than one table you can go through documentation on Query referred earlier.  Notice that we do not use a changeset here. The assumption is that data passes through a changeset when it gets into the database; so data coming out should be valid. Click New Book and you will get Figure-2. In the routes-list, “Page_path GET  / books/new  Book.BookController :new” applies and the “new”  action is invoked and it displays “new.html.eex” and it displays a form. When the form is submitted, the route  Page_path  POST  / books/  Book.BookController :create applies and the “create” action is invoked.

The code for new and create actions are given below:

# this action is used for the URL /get/books. you will see a form to enter details
def new(conn, _params) do
changeset = Book.changeset(%Book{}) # Book Map is passed
render(conn, "new.html", changeset: changeset)
# this action is used for the URL /post/books .The value entered for books is pattern matched and put in a MAP whose key is “ book” and the value is the  variable ’ books_params’
def create(conn, %{"book" => book_params}) do
changeset = Book.changeset(%Book{}, book_params) # book_params passed to changeset

    case Repo.insert(changeset) do                      #Repo.insert() is passed the changeset
{:ok, _book} ->
# There are times when we need to communicate with users during the course of an action. Maybe there was an error updating a model. Maybe we just want to welcome them back to the application. For this, we have flash messages
|> put_flash(:info, "Book created successfully.") 
|> redirect(to: book_path(conn, :index)) #redirected to BookController.index
{:error, changeset} ->
render(conn, "new.html", changeset: changeset)

 Fill up the text boxes and click “submit”. The “create” action is invoked After you add two books the browser may appear as in Figure-3.When you fail to fill up “price” under it you will see the message “can’t be blank” as in Figure-4.How this happens?
See the function
def changeset()
in  /models/book.ex.
you can see the following code which is responsible for this.
|> validate_required([:title, :author, :price])
You can experiment by changing the existing definition of change-set with the following:

def changeset(struct, params \\ %{}) do
   |> cast(params, [:title, :author, :price])
   |> validate_required([:title, :author]) #no validation for “price” field
|> validate_length(:title, min: 5) #The length of “title” should be atleast five characters


See Figure-5, for type of errors you get now:


Like Rails, Phoenix also furnishes necessary tools to create a database-backed web app without any code from us, by simply executing a few commands. But as Phoenix is an Elixir framework which runs on Erlang VM, one need not bother about its scalability or enterprise-worthiness or the performance.  More over as an application created with Phoenix is an Erlang OTP application, it is also fault-tolerant. Though Phoenix performs well in all types of web-applications,  it performs exceedingly well in soft “real time” apps - for which Phoenix was created .Its main feature is “Channel”, its web-socket implementation and we earlier had a separate article on the same. Its creator explained as under:  “I wanted a Web framework that could take on the modern Web’s real-time requirements of many connected devices. I found Elixir and realized it would allow a framework to be both highly productive and have world-class performance. Thus Phoenix was born”

Figure 1

Figure 2