Bind

Define an API controller like this:

defindex(conn,_params)dousers=conn.query_string|>Bind.query(User)|>Repo.all()render(conn,:index,result: users)end

Now your endpoint supports all these queries out of the box:

GET /users?name[contains]=john&sort=-id&limit=25
GET /users?salary[gte]=50000&location[eq]=berlin
GET /users?joined_at[lt]=2024-01-01&status[neq]=disabled
GET /users?options.prompt[contains]=motorbike

Bind is a flexible and dynamic Ecto query builder, for retrieving data flexibly without writing custom queries for each use case.

Installation

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

defdepsdo[{:bind,"~> 0.6.0"}]end

API

Bind.query(schema,params)

Parameters:

• schema: The Ecto schema module (e.g., MyApp.User).
• params: Either a map of query parameters or a query string.

Returns: An Ecto query.

Usage Example

Create Ecto query:

query=Bind.query(%{"name[eq]"=>"Alice","age[gte]"=>30},MyApp.User)

Alternatively, with a query string:

query=Bind.query("?name[eq]=Alice&age[gte]=30",MyApp.User)

And finally run the query to get results from the database:

results=Repo.all(query)

Here's how it looks in a controller:

defindex(conn,params)doimages=conn.query_string|>Bind.query(MyApp.Media.Image)|>MyApp.Repo.all()render(conn,:index,result: images)end

Error handling

caseBind.query(%{"name[eq]"=>"Alice","age[gte]"=>30},MyApp.User)do{:error,reason}->IO.puts("Error building query: #{reason}")query->results=Repo.all(query)end

Filtering

Examples:

%{"name[eq]"=>"Alice","age[gte]"=>30}

%{"name[starts_with]"=>"A","age[gte]"=>18,"role[in]"=>"superuser,admin,mod","is_active[true]"=>"","last_login[nil]"=>false}

List of comparison operators supported:

• eq: Equal to
• neq: Not equal to
• gt: Greater than
• gte: Greater than or equal to
• lt: Less than
• lte: Less than or equal to
• true: Boolean true
• false: Boolean false
• starts_with: String starts with
• ends_with: String ends with
• in: In a list of values
• contains: String contains
• nil: Is nil (or is not nil)
• empty: JSONB array is empty (or not empty)
• search: Full-text search with tsvector

JSONB Search

For PostgreSQL JSONB columns, use dot notation to search within JSON fields:

%{"options.prompt[contains]"=>"motorbike"}%{"metadata.duration[eq]"=>"5"}%{"config.settings[starts_with]"=>"prod"}

Examples in URLs:

GET /videos?options.prompt[contains]=motorbike
GET /users?preferences.theme[eq]=dark
GET /posts?metadata.tags[contains]=elixir

Supported JSONB operators:

• eq: Exact match
• contains: Case-insensitive substring search
• starts_with: Case-insensitive prefix search
• ends_with: Case-insensitive suffix search
• empty: JSONB array length check (true for empty, false for non-empty)

Empty (JSONB arrays)

Check whether a JSONB array is empty. Works on a column whose value is an array directly, or on a nested key whose value is an array:

# Column value is an array, e.g. tags = [123, 23]%{"tags[empty]"=>"true"}# tags is []%{"tags[empty]"=>"false"}# tags has at least one element# Nested key, e.g. options = %{"foobar" => []}%{"options.foobar[empty]"=>"true"}%{"options.foobar[empty]"=>"false"}

Examples in URLs:

GET /videos?tags[empty]=true
GET /videos?options.foobar[empty]=false

Sorting

Use the sort parameter to specify sorting order:

• Prefix with - for descending order
• No prefix for ascending order

%{"sort"=>"-age"}# Sort by age descending%{"sort"=>"age"}# Sort by age ascending

If nothing specified, sorts by ID field ascending.

Pagination

• limit: Specify the maximum number of results (default: 10)
• start: Specify the starting ID for pagination

Example:

%{"limit"=>20,"start"=>100}

• For ascending order (default): start=100
• For descending order, use leading dash: -start=100

Example for descending pagination:

GET /users?sort=-created_at&-start=-100

Query String Support

In a typical Phoenix controller, you can simply pass conn.query_string and get Ecto query back:

query_string=conn.query_string|>Bind.query(query_string,MyApp.User)|>MyApp.Repo.all()

Transforming Query Parameters

You can transform filter values before query is built:

"user_id[eq]=123&team_id[eq]=456"|>Bind.map(%{user_id: fnid->HashIds.decode(id)end,team_id: fnid->HashIds.decode(id)end})|>Bind.query(MyApp.User)|>Repo.all()

Transform specific fields

Bind.map(params,%{user_id: fnid->HashIds.decode(id)end,name: &String.upcase/1})

Transform multiple fields with regex pattern:

Bind.map(params,%{~r/_id$/i=>fnid->HashIds.decode(id)end})

Note: Value transformation only applies to filter fields (e.g. [eq], [gte]), not to sort/limit/pagination params.

Safe Parameter Transformation

Use Bind.map_safe/2 when transformations might fail. It returns {:ok, mapped_params} on success or {:error, reason} on failure:

params|>Bind.map_safe(%{asset_id: fnhash->HashIds.decode!(hash)end})

map_safe/2 automatically handles Result tuples ({:ok, value} / {:error, reason}) returned by mapper functions:

# Mapper returns {:ok, value} - automatically unwrappedparams|>Bind.map_safe(%{asset_id: fnhash->decode_id(hash)end# returns {:ok, id}})# => {:ok, %{"asset_id[eq]" => id}}# Mapper returns {:error, reason} - propagated as errorparams|>Bind.map_safe(%{asset_id: fnhash->decode_id(hash)end# returns {:error, "invalid"}})# => {:error, {:transformation_failed, "invalid"}}# Mix of return types works seamlesslyBind.map_safe(params,%{user_id: fnid->decode(id)end,# {:ok, val} or {:error, reason}team_id: fnid->decode!(id)end,# val or raisesname: &String.upcase/1# val directly})

Empty value handling:

Empty values (nil or "") are automatically removed from the result if a mapper is defined for that field. Fields without mappers preserve empty values:

params=%{"user_id[eq]"=>"","name[eq]"=>""}Bind.map_safe(params,%{user_id: fnid->decode!(id)end})# => {:ok, %{"name[eq]" => ""}}# user_id[eq] is removed (has mapper + empty value)# name[eq] is kept (no mapper)

Difference between map/2 and map_safe/2:

• Bind.map/2: Raises exceptions if transformation fails (use when you're confident inputs are valid)
• Bind.map_safe/2: Returns error tuples if transformation fails (use when inputs might be invalid)

# map/2 - raises on errorBind.map(params,%{id: &decode!/1})# => raises if decode! fails# map_safe/2 - returns error tupleBind.map_safe(params,%{id: &decode!/1})# => {:ok, mapped} or {:error, {:transformation_failed, reason}}

Access Control with Filters

You can use filters to enforce access control and limit what users can query. Filters compose nicely with the query builder:

defindex(conn,_params)domy_posts=conn.query_string# User can only see their own posts|>Bind.filter(%{"user_id[eq]"=>conn.assigns.current_user.id})# That are active|>Bind.filter(%{"active[true]"=>true})|>Bind.query(Post)|>Repo.all()render(conn,:index,posts: my_posts)end

Join Queries

Query fields on associated tables using colon syntax: association:field[operator]

GET /users?address:city[eq]=berlin
GET /users?address:city[contains]=ber&sort=-id

Fields that you can run join query must be white-listed:

defindex(conn,_params)dousers=conn.query_string|>Bind.query(User,joins: [:address])# allow join queries|>Repo.all()end

Virtual Fields

Expose filters that keep the field[op] notation but aren't columns — bind parses the param, your function writes the SQL. Useful when a filter is more than one join away:

defindex(conn,_params)dochats=conn.query_string|>Bind.query(Chat,virtual: %{asset_id: &Chats.scope_touching_asset/2})|>Repo.all()end# GET /chats?asset_id[eq]=123 calls scope_touching_asset(query, 123)defscope_touching_asset(query,asset_id)dochat_ids=fromminChatMessage,join: rinNodeRun,on: r.chat_message_id==m.id,join: vinAssetVersion,on: v.node_run_id==r.id,where: v.asset_id==^asset_id,select: m.chat_idfromcinquery,where: c.idinsubquery(chat_ids)end

A bare function answers eq only. Declare more constraints as a keyword list; in values arrive as a list (comma-split from the query string):

virtual: %{asset_id: [eq: &Chats.scope_touching_asset/2,in: &Chats.scope_touching_any_asset/2]}

Full-Text Search

For PostgreSQL tsvector columns, use the search operator:

%{"bio[search]"=>"engineer"}

Examples in URLs:

GET /users?keywords[search]=engineer

This generates a query using PostgreSQL's full-text search:

WHERE keywords @@ to_tsquery('simple', 'engineer:*')