Selecto

Advanced Query Builder for Elixir (Alpha)

⚠️ Alpha Quality Software

selecto is currently alpha quality and under active development. Expect breaking API changes, behavior changes, incomplete features, and potentially severe bugs. Do not treat current releases as production-hardened without your own validation, testing, and risk controls.

Selecto is a query building system that allows you to construct complex SQL queries within configured domains. It supports advanced join patterns, hierarchical relationships, OLAP dimensions, and Common Table Expressions (CTEs).

📘 Livebooks, Tutorials, and Demo

📌 Release Status (0.3.x)

✅ Adapter, Tenant, and Streaming Status (0.3.10)

⚠️ Known Limitations (Advanced Subfilters)

🚀 Key Features

🧭 Field Path Syntax (0.3.2+)

Selecto examples and tests now standardize on dot notation for joined paths:

Use this notation consistently across select, filter, group_by, and order_by field references.

🧩 Extensions (0.3.3+)

Selecto supports package-provided extensions through the :extensions key in your domain config.

Install

def deps do
  [
    {:selecto, "~> 0.3.16"},
    # Optional extension package for spatial/map support
    {:selecto_postgis, "~> 0.1"}
  ]
end

Enable an extension in a domain

domain = %{
  name: "Places",
  source: %{
    source_table: "places",
    primary_key: :id,
    fields: [:id, :name, :location],
    columns: %{location: %{type: :geometry}},
    associations: %{}
  },
  schemas: %{},
  joins: %{},
  extensions: [
    Selecto.Extensions.PostGIS
  ]
}

Optional extension DSL in overlays

defmodule MyApp.Overlays.PlacesOverlay do
  use Selecto.Config.OverlayDSL,
    extensions: [Selecto.Extensions.PostGIS]

  defmap_view do
    geometry_field("location")
    popup_field("name")
    default_zoom(11)
    center({41.2, -87.6})
  end
end

Authoring your own extension

Implement Selecto.Extension and opt into only the callbacks you need:

📋 Quick Start

# Configure your domain
domain = %{
  name: "E-commerce Analytics",
  source: %{
    source_table: "orders",
    primary_key: :id,
    fields: [:id, :total, :customer_id, :created_at],
    columns: %{
      id: %{type: :integer},
      total: %{type: :decimal},
      customer_id: %{type: :integer},
      created_at: %{type: :utc_datetime}
    },
    associations: %{
      customer: %{queryable: :customers, field: :customer, owner_key: :customer_id, related_key: :id},
      items: %{queryable: :order_items, field: :items, owner_key: :id, related_key: :order_id}
    }
  },
  schemas: %{
    customers: %{
      name: "Customer",
      source_table: "customers", 
      fields: [:id, :name, :region_id],
      columns: %{
        id: %{type: :integer},
        name: %{type: :string},
        region_id: %{type: :integer}
      }
    },
    order_items: %{
      name: "Order Item",
      source_table: "order_items",
      fields: [:id, :quantity, :product_id, :order_id],
      columns: %{
        id: %{type: :integer},
        quantity: %{type: :integer}, 
        product_id: %{type: :integer},
        order_id: %{type: :integer}
      }
    }
  },
  joins: %{
    customer: %{type: :star_dimension, display_field: :name},
    items: %{type: :left}
  }
}

# Create and configure Selecto
selecto = Selecto.configure(domain, postgrex_connection)

# Build queries with automatic join resolution
result = selecto
  |> Selecto.select(["id", "total", "customer.name", "items.quantity"])
  |> Selecto.filter([{"total", {:gt, 100}}, {"customer.name", {:like, "John%"}}])
  |> Selecto.order_by(["created_at"])
  |> Selecto.execute()

🏗️ Advanced Join Patterns

OLAP Dimensions (Star Schema)

Perfect for analytics and business intelligence:

joins: %{
  customer: %{type: :star_dimension, display_field: :full_name},
  product: %{type: :star_dimension, display_field: :name},
  time: %{type: :star_dimension, display_field: :date}
}

Snowflake Schema (Normalized Dimensions)

For normalized dimension tables requiring additional joins:

joins: %{
  region: %{
    type: :snowflake_dimension,
    display_field: :name,
    normalization_joins: [%{table: "countries", alias: "co"}]
  }
}

Hierarchical Relationships

Support for tree structures with multiple implementation patterns:

# Adjacency List Pattern
joins: %{
  parent_category: %{
    type: :hierarchical,
    hierarchy_type: :adjacency_list,
    depth_limit: 5
  }
}

# Materialized Path Pattern  
joins: %{
  parent_category: %{
    type: :hierarchical,
    hierarchy_type: :materialized_path,
    path_field: :path,
    path_separator: "/"
  }
}

# Closure Table Pattern
joins: %{
  parent_category: %{
    type: :hierarchical, 
    hierarchy_type: :closure_table,
    closure_table: "category_closure",
    ancestor_field: :ancestor_id,
    descendant_field: :descendant_id
  }
}

Many-to-Many Tagging

Automatic aggregation and faceted filtering:

joins: %{
  tags: %{
    type: :tagging,
    tag_field: :name,
    name: "Post Tags"
  }
}

# Automatically creates:
# - Aggregated tag lists: string_agg(tags.name, ', ')
# - Faceted filters for individual tag selection

🔧 Common Table Expressions (CTEs)

Build complex queries with Selecto's public CTE APIs:

query =
  selecto
  |> Selecto.with_cte("active_users", fn ->
    Selecto.configure(user_domain, conn)
    |> Selecto.select(["id", "name"])
    |> Selecto.filter({"active", true})
  end)
  |> Selecto.with_recursive_cte("hierarchy",
    base_query: fn ->
      Selecto.configure(tree_domain, conn)
      |> Selecto.select(["id", "name", "parent_id", {:literal, 0, as: "level"}])
      |> Selecto.filter({"parent_id", nil})
    end,
    recursive_query: fn cte_ref ->
      Selecto.configure(tree_domain, conn)
      |> Selecto.join(:inner, cte_ref, on: "node.parent_id = hierarchy.id")
      |> Selecto.select(["node.id", "node.name", "node.parent_id", {:literal, 1, as: "level"}])
    end
  )

For low-level SQL assembly, build validated Selecto.Advanced.CTE.Spec entries and render them with Selecto.Builder.CteSql.

📊 Advanced Selection Features

Custom SQL with Field Validation

# Safe custom SQL with automatic field validation
selecto |> Selecto.select([
  {:custom_sql, "COALESCE({{customer_name}}, 'Unknown')", %{
    customer_name: "customer.name"
  }}
])

Complex Aggregations

selecto |> Selecto.select([
  {:func, "count", ["*"]},
  {:func, "avg", ["total"]}, 
  {:array, "product_names", ["items.product_name"]},
  {:case, "status", %{
    "high_value" => [{"total", {:gt, 1000}}],
    "else" => [{:literal, "standard"}]
  }}
])

🔍 Advanced Filtering

Logical Operators

selecto |> Selecto.filter([
  {:and, [
    {"active", true},
    {:or, [
      {"customer.region", "West"},
      {"customer.region", "East"}
    ]}
  ]},
  {"total", {:between, 100, 1000}}
])

Subqueries and Text Search

selecto |> Selecto.filter([
  {"customer_id", {:subquery, :in, "SELECT id FROM vip_customers", []}},
  {"description", {:text_search, "elixir postgresql"}}
])

🎯 Domain Configuration

Complete Domain Structure

domain = %{
  name: "Domain Name",
  source: %{
    source_table: "main_table",
    primary_key: :id,
    fields: [:id, :field1, :field2],
    redact_fields: [:sensitive_field],
    columns: %{
      id: %{type: :integer},
      field1: %{type: :string}
    },
    associations: %{
      related_table: %{
        queryable: :related_schema,
        field: :related,
        owner_key: :foreign_key,
        related_key: :id
      }
    }
  },
  schemas: %{
    related_schema: %{
      name: "Related Schema",
      source_table: "related_table", 
      # ... schema definition
    }
  },
  joins: %{
    related_table: %{type: :left, name: "Related Items"}
  },
  default_selected: ["id", "name"],
  required_filters: [{"active", true}]
}

🧪 Testing and Quality

Cross-DB Baseline Checks

Run adapter baseline execute checks with explicit DB tags:

# PostgreSQL baseline (with service running)
SELECTO_RUN_DB_TESTS=true mix test test/cross_db_baseline_test.exs --only postgres

# MySQL baseline
SELECTO_RUN_DB_TESTS=true mix test test/cross_db_baseline_test.exs --only mysql

# MariaDB baseline
SELECTO_RUN_DB_TESTS=true mix test test/cross_db_baseline_test.exs --only mariadb

# MSSQL baseline
SELECTO_RUN_DB_TESTS=true mix test test/cross_db_baseline_test.exs --only mssql

# SQLite baseline
SELECTO_RUN_DB_TESTS=true mix test test/cross_db_baseline_test.exs --only sqlite

Property Testing

Run property tests (non-DB) for deterministic SQL generation and query-builder invariants:

mix test test/property/property_test.exs

Run the PostgreSQL-backed property suite (tagged with :requires_db):

SELECTO_RUN_DB_TESTS=true mix test test/property/property_test.exs --include requires_db

Optional DB connection overrides for the DB-backed property suite:

SELECTO_POSTGRES_HOST=localhost
SELECTO_POSTGRES_PORT=5432
SELECTO_POSTGRES_USER=postgres
SELECTO_POSTGRES_PASSWORD=password
SELECTO_POSTGRES_DATABASE=selecto_test

📚 Documentation

🚦 System Requirements

🧱 Adapter Support Matrix

Adapter SQL generation Execute Stream
PostgreSQL (Selecto.DB.PostgreSQL) Yes Yes Yes (cursor-backed for direct Postgrex connections)
MySQL (Selecto.DB.MySQL) Yes Yes (with myxql) Adapter-defined (supports?(:stream))
MariaDB (Selecto.DB.MariaDB) Yes Yes (with myxql) Adapter-defined (supports?(:stream))
MSSQL (Selecto.DB.MSSQL) Yes Yes (with tds) Adapter-defined (supports?(:stream))
SQLite (Selecto.DB.SQLite) Yes Yes (with exqlite) Adapter-defined (supports?(:stream))

📦 Installation

def deps do
  [
    {:selecto, "~> 0.3.16"}
  ]
end

For local multi-repo development against vendored ecosystem packages, set:

SELECTO_ECOSYSTEM_USE_LOCAL=true

This is the shared local-development switch used across Selecto ecosystem repos.

🤝 Contributing

Selecto has evolved through multiple development phases:

The codebase uses modern Elixir practices, but remains alpha software and is not presented as production-hardened.

📄 License

MIT

Selecto - From simple queries to complex analytics, Selecto helps model database relationships while the project continues to mature.