Querying Databases with Ecto Elixir

What You’ll Learn 📚

• Core concepts of Ecto and its role in Elixir applications
• Key terminology and definitions
• How to perform simple to complex queries
• Troubleshooting common issues

Introduction to Ecto

Ecto is a database wrapper and query generator for Elixir. It allows you to interact with your database in a structured way, making it easier to perform CRUD (Create, Read, Update, Delete) operations. Think of Ecto as a bridge between your Elixir application and the database. 🛤️

Key Terminology

• Repo: A module that acts as the interface to your database.
• Schema: Defines the structure of your data and maps it to a database table.
• Query: A request to retrieve or manipulate data in the database.

Getting Started with Ecto

Setup Instructions

Before we start querying, let’s set up Ecto in an Elixir project.

# Create a new Elixir project
mix new my_ecto_app --sup
cd my_ecto_app

# Add Ecto and Postgrex to your mix.exs dependencies
# Open mix.exs and add the following:
# defp deps do
#   [
#     {:ecto_sql, "~> 3.0"},
#     {:postgrex, "~> 0.15"}
#   ]
# end

# Install the dependencies
mix deps.get

💡 Lightbulb Moment: Ecto requires a database adapter like Postgrex to communicate with a PostgreSQL database.

Creating a Repo and Schema

Let’s create a simple schema for a ‘users’ table.

# In lib/my_ecto_app/repo.ex
defmodule MyEctoApp.Repo do
  use Ecto.Repo,
    otp_app: :my_ecto_app,
    adapter: Ecto.Adapters.Postgres
end

# In lib/my_ecto_app/user.ex
defmodule MyEctoApp.User do
  use Ecto.Schema

  schema "users" do
    field :name, :string
    field :email, :string
  end
end

Querying the Database

Simple Query Example

Let’s start with a simple query to fetch all users.

# In iex -S mix
alias MyEctoApp.{Repo, User}

# Fetch all users
users = Repo.all(User)
IO.inspect(users)

Filtering Queries

Now, let’s filter users by name.

# Fetch users with the name 'Alice'
alice_users = Repo.all(from u in User, where: u.name == "Alice")
IO.inspect(alice_users)

Complex Queries

Let’s perform a more complex query with ordering and limit.

# Fetch the first 5 users, ordered by name
ordered_users = Repo.all(from u in User, order_by: u.name, limit: 5)
IO.inspect(ordered_users)

Common Questions and Answers

1. What is Ecto?

   Ecto is a database wrapper and query generator for Elixir, allowing structured database interactions.

2. How do I set up Ecto in my project?

   Add Ecto and a database adapter like Postgrex to your dependencies and configure your Repo module.

3. How do I perform a simple query?

   Use Repo.all with your schema module to fetch all records.

4. How can I filter query results?

   Use the from macro with where clauses to filter results.

5. What if my query returns no results?

   An empty list is returned, indicating no matching records.

Troubleshooting Common Issues

⚠️ Common Pitfall: Forgetting to start the database server or misconfiguring the database connection can lead to connection errors.

Ensure your database server is running and your configuration in config/config.exs is correct.

🔍 Note: Always check your query syntax and ensure your schema matches the database structure.

Practice Exercises

• Create a new schema for a ‘posts’ table and perform CRUD operations.
• Write a query to find users with a specific domain in their email.
• Experiment with joins to combine data from multiple tables.

For more information, check out the Ecto documentation.

Keep experimenting and happy coding! 🌟