Simplifying Ecto Fragments with Defrag: A Better Way to Write Database Queries

Working with complex database queries in Elixir's Ecto can sometimes feel like wrestling with syntax. If you've ever found yourself counting question marks in a fragment to figure out which parameter goes where, the Defrag library might be just what you need.

The Problem with Traditional Fragments

Anyone who's worked with Ecto has likely written code like this:

fragment("date_part('year', ?) BETWEEN ? AND ?", user.birth_date, start_year, end_year)

As your queries grow more complex, tracking which parameter corresponds to which placeholder becomes increasingly error-prone. With 18 variables in a query (yes, I've seen this in production code!), good luck finding where each one belongs without careful counting.

Enter Defrag: String Interpolation for Ecto Fragments

Defrag offers a more intuitive approach by allowing you to use Elixir's familiar string interpolation syntax in your database queries:

defrag("date_part('year', #{user.birth_date}) BETWEEN #{start_year} AND #{end_year}")

The library transforms this readable syntax into proper Ecto fragments at compile time, maintaining all the SQL injection protection of regular fragments while making your code significantly easier to understand.

How It Works

Under the hood, Defrag is a simple macro that:

1. Parses your string with interpolation syntax
2. Replaces each interpolated expression with a question mark
3. Collects all the interpolated values as parameters
4. Builds a standard Ecto fragment with these components

Real-World Usage

Incorporating Defrag into your Ecto queries is straightforward:

import Ecto.Query
import Defrag

def complex_search(query, params) do
  from u in query,
    where: defrag("""
      #{u.status} = 'active' AND
      date_part('year', #{u.registered_at}) >= #{params.min_year} AND
      #{u.points} BETWEEN #{params.min_points} AND #{params.max_points} AND
      lower(#{u.email}) LIKE #{^"%#{params.email_search}%"}
    """)
end

The query above would be nearly impossible to read and maintain with traditional fragments, but with Defrag, the relationship between your SQL and parameters is crystal clear.

Benefits

• Self-documenting queries: See exactly where each parameter fits in the SQL statement
• Reduced errors: No more counting question marks or parameter positions
• Maintainability: Easier to modify queries when you can see the full context
• Familiar syntax: Uses the string interpolation you already know from Elixir

Getting Started

Add Defrag to your dependencies in mix.exs:

def deps do
  [
    {:defrag, "~> 0.1.0"}
  ]
end

Then import it in your query modules:

import Defrag

Final Thoughts

If you've struggled with complex Ecto fragments, Defrag offers a simple yet powerful solution. By bringing familiar string interpolation to your database queries, it makes even the most complex fragments manageable and maintainable.

For those working with data-intensive applications, this small library can make a significant difference in your codebase clarity.