Talking to databases through Ecto in Phoenix

Ecto is a database persistence framework for Phoenix web applications. It’s a database wrapper that helps interact with different databases, such as PostgreSQL, MySQL, SQLite3, etc. With Ecto, we can create and perform CRUDCreate, Retrieve, Update, and Delete operations operations on a database. In the next few sections, we’ll create a PostgreSQL database, configure Ecto, and use it to perform CRUD operations.

Adding dependencies

To start accessing the Postgres database, we’ll need to add Postgres as a dependency in our Phoenix web application. Additionally to access the Postgres database via Ecto — our database wrapper — we’ll also need to add this as a dependency. For this purpose, we’ll add the following to the mix.exs file of your Phoenix application. Adding Ecto will provide us with a querying API, while postgrex is the PostgreSQL driver that Ecto uses to interact with the database in a way understandable to it.

Configure Ecto and Postgres

Now, we need to configure the Ecto repository module, which connects to the database and handles querying. For that, we need to specify the database name, the PostgreSQL username, and the hostname. We need to add the database details like database name, username, and hostname into config/config.exs file of your Phoenix application. Also, add config :educative_elixir_app, ecto_repos:[Educative_elixir_app.Repo]. This notifies our application of the repo module we’ll use to run common database commands.

Next, we have to run the following command to install these dependencies. This command fetches the dependencies that we specified in the mix.exs file, ensuring that Ecto and Postgrex are available for your application.

Now, let’s set up the Ecto repository responsible for database interactions. Execute the following command to generate the repository module:

This command creates the necessary configuration and module files for the repository named Educative_elixir_app.Repo.

We’ll use the Educative_elixir_app.Repo module present in repo.exs. We are trying to define a database wrapper for querying our database. otp_app states the name of the application that contains the database configurations. It is used to link the repository to your specific Phoenix application and adapter specifies the database system, which is PostgreSQL in our case. The database adapter is set to PostgreSQL. Then, we’ll set Educative_elixir_app.Repo as a supervisor within the application’s tree so that the repository process starts when the application is launched. All of this is added to lib/educative_elixir_app/application.ex.

Creating databases using Ecto

Creating databases using Ecto involves three steps. We’ll go through them one step at a time.

Creating the Postgres database

To create a database, we have to run the command given below. After that, we need to create a migration file in the priv/repo/migrations folder.

Defining a migration file

Next, we generate a migration file to define the structure of our educative_user table. Migrations are necessary to modify our database. In our case, we’ll create the educative_user table in our database. The highlighted text will be added to the migration file. The highlighted section is the schema of the educative_user table. It has three string fields, namely, user_first_name, user_last_name, and user_address, and a single integer field called user_age.

Running the migration

To create the educative_user table, we need to run the command below:

CRUD operations

The general way to execute any database query in Ecto is to create and execute the query.

The educative_user.ex file shows the database schema. We’ll start the IEx shell — Elixir’s interactive shell — with the iex -S mix command to create this at the end of the answer. Once an IEx session has been created, create multiple database users using the command under the insertion tab. As we mentioned above, we first need to formulate the query and then execute it. The command under the Retrieval tab can retrieve all the inserted records, while the command under the Updation tab will update the user_age field of the last inserted record. The deletion tab demonstrates a way to delete a record with id equal to 1.

Copy and paste the following two commands one by one in the terminal given at the end. mix ecto.migrate will ensure that all changes made to the database schema are reflected in the database. While, iex -S mix will launch the IEx shell.

After running the terminal below, paste the insertion, retrieval, update, and deletion commands in the IEx shell.

Happy coding!