EctoQueryParser

A query language parser for Ecto that converts human-readable filter strings into Ecto WHERE clauses. Useful for building user-facing search and filtering interfaces where the filter expression comes from a URL parameter, API request body, or other untrusted text input.

iex> EctoQueryParser.apply(Post, ~s{status == "published" AND author.name contains "alice"})
{:ok, #Ecto.Query<...>}

Installation

Add ecto_query_parser to your list of dependencies in mix.exs:

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

Query Language

Data Types

Operators

Logical Operators

Combine conditions with AND and OR (case-insensitive). Use parentheses for grouping. AND binds tighter than OR.

name == "alice" AND age >= 18
role == "admin" OR role == "moderator"
(role == "admin" OR role == "moderator") AND active == true

Functions

Functions are case-insensitive and can be nested.

String functions:

Math functions:

Date/time functions:

The ROUND_* family includes: ROUND_SECOND, ROUND_MINUTE, ROUND_HOUR, ROUND_DAY, ROUND_WEEK, ROUND_MONTH, ROUND_QUARTER, ROUND_YEAR.

Usage

Basic Usage

Pass an Ecto schema module or an existing Ecto.Query along with a filter string:

# With a schema module
{:ok, query} = EctoQueryParser.apply(MyApp.Post, ~s{status == "published"})
Repo.all(query)

# Composing with an existing query
import Ecto.Query
base = from(p in MyApp.Post, select: p.title)
{:ok, query} = EctoQueryParser.apply(base, ~s{author.name == "alice"})
Repo.all(query)

Association Joins

Dotted identifiers automatically resolve to LEFT JOIN clauses using the schema's associations. Multiple references to the same association produce a single join. Multi-level nesting is supported.

# Single join
{:ok, query} = EctoQueryParser.apply(Post, ~s{author.name == "alice"})

# Multi-level join
{:ok, query} = EctoQueryParser.apply(Post, ~s{author.company.name == "Acme"})

# Deduplication: only one join for author
{:ok, query} = EctoQueryParser.apply(Post, ~s{author.name == "alice" AND author.email contains "example"})

JSONB Column Access

When a dotted identifier refers to a :map field on the schema, it extracts the value via json_extract_path instead of creating a join. Nested paths work too.

# Schema: field :metadata, :map
{:ok, query} = EctoQueryParser.apply(Post, ~s{metadata.category == "tech"})
# Generates: WHERE metadata#>'{category}' = 'tech'

# Nested path
{:ok, query} = EctoQueryParser.apply(Post, ~s{metadata.author.name == "alice"})

For correct type casting on JSON values (required for numeric and boolean comparisons), provide types via the keyword list format of :allowed_fields:

{:ok, query} = EctoQueryParser.apply(Post, ~s{metadata.view_count >= 100},
  allowed_fields: [metadata: :map, "metadata.view_count": :integer]
)

Restricting Fields

Use :allowed_fields to control which fields users can filter on. Two formats are supported:

# Plain list: access control only
EctoQueryParser.apply(Post, query_string,
  allowed_fields: [:name, :age, :"author.name"]
)

# Keyword list: access control + type casting for JSON paths
EctoQueryParser.apply(Post, query_string,
  allowed_fields: [
    name: :string,
    age: :integer,
    metadata: :map,
    "metadata.key": :string,
    "metadata.count": :integer
  ]
)

Fields not in the list return {:error, "field not allowed: ..."}.

Schemaless Queries

When working with a string table name instead of a schema module, you can define associations directly in :allowed_fields:

import Ecto.Query

allowed = [
  name: :string,
  author: {:assoc,
    table: "users",
    owner_key: :author_id,
    related_key: :id,
    fields: [
      name: :string,
      email: :string,
      company: {:assoc,
        table: "companies",
        owner_key: :company_id,
        related_key: :id,
        fields: [name: :string]}
    ]}
]

{:ok, query} = EctoQueryParser.apply(
  from("posts"),
  ~s{author.company.name == "Acme"},
  allowed_fields: allowed
)

Association options:

• :table (required) - target table name as a string
• :owner_key (required) - foreign key on the source table
• :related_key (required) - primary key on the target table
• :fields (optional) - keyword list of permitted fields, supports nesting

Error Handling

All errors are returned as {:error, reason} tuples:

{:error, "field not allowed: secret"}
{:error, "unknown field: nonexistent"}
{:error, "unknown association: nonexistent"}
{:error, "unknown function: bogus"}
{:error, "contains operator requires a string or identifier value, got: ..."}

Development

Running Tests

# Unit tests only (no database required)
mix test

# Start PostgreSQL for integration tests
docker compose up -d

# Run all tests including integration
mix test --include integration
# or
mix test.integration

Integration tests execute every generated SQL query against a real PostgreSQL database to verify correctness beyond what inspect(query) assertions can catch.

License

See LICENSE for details.