optimadetosql

Translate OPTIMADE filter language queries into SQL with support for custom field mapping, behaviors, JOINs, and multiple SQL dialects.

Installation

pip install optimadetosql

For development:

pip install -e ".[dev]"

Requires Python >= 3.11.

Quick Start

from optimadetosql import DynamicMapper, OptimadeToSQL

mapper = DynamicMapper(
    table_name="structures",
    field_mapping={
        "id": "id",
        "nelements": "n_elements",
        "chemical_formula": "formula",
        "elements": "elements",
    },
)

translator = OptimadeToSQL(mapper=mapper)
sql = translator.translate("nelements > 3")
print(sql)
# SELECT * FROM structures WHERE structures.n_elements > 3

Core Concepts

1. DynamicMapper -- Map OPTIMADE fields to database columns

The DynamicMapper is the bridge between OPTIMADE field names and your actual database schema.

Parameter	Type	Description
table_name	str	Your database table name
field_mapping	dict[str, str]	Maps OPTIMADE field names to database column names
provider_prefix	str	Prefix for provider-specific fields (default: "exa")
field_types	dict[str, str]	Type hints for fields ("string", "integer", "float", "boolean", "datetime")
defaults	dict[str, Any]	Default values for fields
enum_types	dict[str, str]	PostgreSQL enum type names for fields
behaviors	dict[str, BehaviorInput]	Value transformation behaviors for fields
join_map	dict[str, tuple]	Defines JOINs to related tables

Field Mapping Examples

# Simple column mapping
mapper = DynamicMapper(
    table_name="structures",
    field_mapping={
        "id": "id",
        "nelements": "n_elements",
        "chemical_formula": "formula",
    },
)

# JSON / JSONB extraction (PostgreSQL)
mapper = DynamicMapper(
    table_name="entries",
    field_mapping={
        "id": "id",
        "nelements": "value->>'nelements'",     # text extraction
        "elements": "value->'elements'",           # jsonb extraction
        "_mpds_bandgap": "attributes->>'bandgap'",
    },
)

# Literal / constant values (prefix the value with single quotes)
mapper = DynamicMapper(
    table_name="structures",
    field_mapping={
        "type": "'structures'",   # always returns the string 'structures'
    },
)

Field Types

Use field_types to tell the translator how to handle numeric comparisons on JSON fields:

mapper = DynamicMapper(
    table_name="entries",
    field_mapping={
        "_mpds_bandgap": "attributes->>'bandgap'",
    },
    field_types={
        "id": "string",
        "nelements": "integer",
        "_mpds_bandgap": "float",
        "last_modified": "datetime",
    },
)

When a field is typed as "float" or "integer" and maps to a JSON path (contains ->>), the translator automatically casts it in SQL:

# Input: _mpds_bandgap > 2.5
# Output: ... WHERE (attributes->>'bandgap')::float > 2.5

2. Behaviors -- Transform field values

Behaviors let you transform how field values are handled in queries. Each behavior implements two methods:

• get(input, dialect) -- transforms the user-supplied value (e.g., normalizes a chemical formula)
• format(input, dialect) -- wraps the SQL column expression (e.g., applies a SQL function)

Built-in Behaviors

ChemicalFormulaBehavior

Normalizes chemical formula input (e.g., "sio2" -> "SiO2"):

from optimadetosql import DynamicMapper
from optimadetosql.behaviors.ready import ChemicalFormulaBehavior

mapper = DynamicMapper(
    table_name="structures",
    field_mapping={"chemical_formula": "formula"},
    behaviors={"chemical_formula": ChemicalFormulaBehavior()},
)

translator = OptimadeToSQL(mapper=mapper)
result = translator.translate('chemical_formula = "SiO2"')

PeriodicTableBehavior

Expands element group names into element lists for set queries:

from optimadetosql.behaviors.ready import PeriodicTableBehavior

mapper = DynamicMapper(
    table_name="structures",
    field_mapping={"elements": "elements"},
    behaviors={"elements": PeriodicTableBehavior()},
)

# Now you can query:
# elements HAS "transition_metals"
# elements HAS "halogens"
# elements HAS "alkali_metals"

Available groups: alkali_metals, alkaline_earth_metals, transition_metals, post_transition_metals, metalloids, nonmetals, halogens, noble_gases, lanthanides, actinides, rare_earths, refractory_metals, noble_metals, metals. Also: group_N (1-18) and period_N (1-7).

CrystalSystemBehavior

Expands crystal system names into space group number ranges:

from optimadetosql.behaviors.ready import CrystalSystemBehavior

mapper = DynamicMapper(
    table_name="phases",
    field_mapping={"spacegroup": "spg"},
    behaviors={"spacegroup": CrystalSystemBehavior()},
)

# Query: spacegroup IN "cubic" -> space group numbers 195-230
# Query: spacegroup IN "hexagonal" -> space group numbers 168-194

Available systems: triclinic, monoclinic, orthorhombic, tetragonal, trigonal, hexagonal, cubic.

PropertyRangeBehavior

Maps named property ranges to numeric thresholds:

from optimadetosql.behaviors.ready import PropertyRangeBehavior

mapper = DynamicMapper(
    table_name="entries",
    field_mapping={"_mpds_bandgap": "value->>'bandgap'"},
    field_types={"_mpds_bandgap": "float"},
    behaviors={"_mpds_bandgap": PropertyRangeBehavior("band_gap")},
)

# _mpds_bandgap >= "semiconductor"  ->  (value->>'bandgap')::float >= 0.1
# _mpds_bandgap >= "insulator"      ->  (value->>'bandgap')::float >= 4.0

Available properties and ranges:

• band_gap: insulator (>=4.0), semiconductor (>=0.1), semiconductor_wide (>=2.0), semiconductor_narrow (>=0.1), metal (>=0.0), semimetal (0.0)
• density: ultra_light (0-1), light (1-5), medium (5-10), heavy (10-20), ultra_heavy (20+)
• formation_energy: thermodynamically_stable (<0), metastable (0-0.1), unstable (>0.1)
• magnetic_moment: non_magnetic (0), paramagnetic (0-0.1), ferromagnetic (>0.1)
• hardness: soft (0-3), medium (3-7), hard (7-10), superhard (10+)

TemperatureUnitBehavior

Converts temperatures to Kelvin:

from optimadetosql.behaviors.ready import TemperatureUnitBehavior

mapper = DynamicMapper(
    table_name="phases",
    field_mapping={"temperature_min": "tmin", "temperature_max": "tmax"},
    field_types={"temperature_min": "float", "temperature_max": "float"},
    behaviors={
        "temperature_min": TemperatureUnitBehavior(),
        "temperature_max": TemperatureUnitBehavior(),
    },
)

# temperature_min > "300c"   ->  tmin > 573.15
# temperature_max < "500f"   ->  tmax < 533.15

EnergyUnitBehavior

Converts energy units to eV:

from optimadetosql.behaviors.ready import EnergyUnitBehavior

mapper = DynamicMapper(
    table_name="entries",
    field_mapping={"_mpds_formation_energy": "value->>'formation_energy'"},
    field_types={"_mpds_formation_energy": "float"},
    behaviors={"_mpds_formation_energy": EnergyUnitBehavior()},
)

# _mpds_formation_energy < "100meV"  ->  (value->>'formation_energy')::float < 0.1
# _mpds_formation_energy < "0.5ry"   ->  (value->>'formation_energy')::float < 6.803

Supported units: eV, meV, Ry/rydberg, Ha/hartree, kJ/kJ/mol.

StoichiometryBehavior

Maps stoichiometry class names to element counts:

from optimadetosql.behaviors.ready import StoichiometryBehavior

mapper = DynamicMapper(
    table_name="structures",
    field_mapping={"nelements": "n_elements"},
    field_types={"nelements": "integer"},
    behaviors={"nelements": StoichiometryBehavior()},
)

# nelements = "binary"    ->  n_elements = 2
# nelements = "ternary"   ->  n_elements = 3
# nelements >= "ternary"  ->  n_elements >= 3

Available names: unary (1), binary (2), ternary (3), quaternary (4), quinary (5), senary (6).

CompositionFilterBehavior

Maps composition class names to their anion element:

from optimadetosql.behaviors.ready import CompositionFilterBehavior

mapper = DynamicMapper(
    table_name="structures",
    field_mapping={"elements": "elements"},
    behaviors={"elements": CompositionFilterBehavior()},
)

# elements HAS "oxide"    ->  elements HAS "O"
# elements HAS "nitride"   ->  elements HAS "N"

Available: oxide, nitride, sulfide, hydride, carbide, phosphide, chloride, fluoride, bromide, iodide, selenide, telluride, arsenide, silicide, boride.

Creating a custom behavior

from optimadetosql import CustomBehavior
from optimadetosql.behaviors import register_behavior


class UpperBehavior(CustomBehavior):
    def get(self, input, dialect=None):
        return input.upper()

    def format(self, input, dialect=None):
        return f"UPPER({input})"


register_behavior("upper", UpperBehavior)

mapper = DynamicMapper(
    table_name="structures",
    field_mapping={"chemical_formula": "formula"},
    behaviors={"chemical_formula": "upper"},
)

translator = OptimadeToSQL(mapper=mapper)
result = translator.translate('chemical_formula = "sio2"')
# SELECT * FROM structures WHERE UPPER(formula) = 'SIO2'

Behavior input formats

For each field in the behaviors dict, you can pass:

# 1. Instance (most common)
behaviors={"chemical_formula": ChemicalFormulaBehavior()}

# 2. Class reference (auto-instantiated)
behaviors={"chemical_formula": ChemicalFormulaBehavior}

# 3. String name (registered name or full module path)
behaviors={"chemical_formula": "upper"}
behaviors={"chemical_formula": "optimadetosql.behaviors.ready.chemical_formulae.ChemicalFormulaBehavior"}

3. JOINs -- Query across related tables

Use join_map to define relationships between tables. The join map value is a tuple (left_key, right_key, related_mapper).

from optimadetosql import DynamicMapper

phases_mapper = DynamicMapper(
    table_name="phases",
    field_mapping={
        "id": "phid",
        "chemical_formula": "formula_txt",
        "spacegroup": "spg",
    },
)

entries_mapper = DynamicMapper(
    table_name="entries",
    field_mapping={
        "id": "id",
        "phase_id": "phid",
        "nelements": "value->>'nelements'",
        "chemical_formula": "value->>'chemical_formula'",
    },
    join_map={"phases": ("phid", "phid", phases_mapper)},
)

translator = OptimadeToSQL(mapper=entries_mapper)
result = translator.translate("nelements > 3")
# SELECT * FROM entries
# LEFT JOIN phases ON entries.phid = phases.phid
# WHERE (entries.value->>'nelements')::int > 3

Fields from related tables (like spacegroup) are automatically routed through the JOIN, including nested JOINs:

result = translator.translate('spacegroup = "225"')
# SELECT * FROM entries
# LEFT JOIN phases ON entries.phid = phases.phid
# WHERE phases.spg = '225'

4. Multiple Mappers -- Work with different resource types

Option A: Separate instances

translator_phases = OptimadeToSQL(mapper=phases_mapper)
translator_entries = OptimadeToSQL(mapper=entries_mapper)
translator_structures = OptimadeToSQL(mapper=structures_mapper)

Option B: Switch mappers with .using()

translator = OptimadeToSQL(mapper=phases_mapper)
translator.using("entries")  # switch to registered entries mapper
sql = translator.translate("nelements > 3")
translator.using("structures")

Option C: Mapper Registry

from optimadetosql import get_registry

registry = get_registry()
registry.register("phases", phases_mapper, default=True)
registry.register("entries", entries_mapper)

translator = OptimadeToSQL.for_resource("entries")
# or infer from URL path:
translator = OptimadeToSQL.for_path("/structures")
translator = OptimadeToSQL.for_path("/v1/entries?filter=...")

5. Parameterized Queries (SQL Injection Prevention)

For production use, always prefer parameterized queries which separate values from SQL:

translator = OptimadeToSQL(mapper=mapper)

# Returns (sql_with_placeholders, params)
sql, params = translator.translate_params('nelements > 3 AND elements HAS "Si"')
# sql = 'SELECT * FROM structures WHERE "structures"."n_elements" > $1 ...'
# params = [3, 'Si']

# Use with SQLAlchemy
from sqlalchemy import text
stmt = text(sql)
result = await session.execute(stmt, {f"param_{i+1}": v for i, v in enumerate(params)})

6. Error Handling

The library provides specific exception types:

from optimadetosql import OptimadeFilterError, OptimadeTranslationError, MapperError, BehaviorError

try:
    sql = translator.translate("!!!invalid!!!")
except OptimadeFilterError as e:
    print(f"Invalid filter: {e}")
except OptimadeTranslationError as e:
    print(f"Translation failed: {e}")
except MapperError as e:
    print(f"Mapper configuration error: {e}")
except BehaviorError as e:
    print(f"Behavior transformation failed: {e}")

7. OPTIMADE Response Formatting

Wrap database results into OPTIMADE-compliant JSON responses:

from optimadetosql import build_optimade_response

rows = await repository.execute(sql, limit=100, offset=0)
response = build_optimade_response(
    rows,
    query="nelements > 3",
    resource_type="structures",
    provider_name="My Database",
    provider_description="A materials database",
    provider_prefix="mydb",
    more_data_available=len(rows) >= 100,
)

# Returns OPTIMADE-compliant dict with 'data' and 'meta' keys

8. SQL Dialects

The library currently supports PostgreSQL with a dialect system ready for extension.

# PostgreSQL (default)
translator = OptimadeToSQL(mapper=mapper, provider="postgresql")

The dialect handles:

• Identifier quoting ("table"."column")
• Value formatting (strings, numbers, booleans)
• JSON/JSONB extraction (->, ->>)
• LIKE escaping
• Array and JSONB operations (@>, ?, ?&, ?|, ANY())

9. Supported OPTIMADE Filter Features

Feature	Example	SQL Output
Comparisons	nelements > 3	n_elements > 3
String equality	chemical_formula = "SiO2"	formula = 'SiO2'
Fuzzy strings	chemical_formula CONTAINS "Si"	formula LIKE '%Si%'
Logic	nelements > 3 AND nelements < 10	(... AND ...)
Null checks	chemical_formula IS KNOWN	formula IS NOT NULL
Set membership	elements HAS "Si"	'Si' = ANY(elements)
Array length	elements LENGTH > 3	array_length(elements, 1) > 3
JSON extraction	_mpds_bandgap > 2.0	(attributes->>'bandgap')::float > 2.0

MCP Server

The project includes an MCP (Model Context Protocol) server with tools for LLM-based querying:

python mcp_server.py

Tools available:

• translate_filter -- Translate an OPTIMADE filter string to SQL (uses core library)
• query_structures -- Query structures from an OPTIMADE API
• get_structure_by_id -- Retrieve a single structure by ID
• get_info -- Get server info
• element_groups -- List available element group names
• crystal_systems -- List crystal system names and space group ranges
• property_ranges -- List available named property ranges

Configure the target OPTIMADE API via the OPTIMADE_BASE_URL environment variable.

Development

# Install from source with dev dependencies
pip install -e ".[dev]"

# Run tests
pytest tests/ -v

# Run linting
ruff check optimadetosql/

# Run type checking
mypy optimadetosql/

License

© Gumar Arutynian and Evgeny Blokhin

MIT License