An embedded, federated query engine for Java

One query across your database, your files and your APIs

Relix is a library that runs inside your application. Give it your existing DataSource, a CSV file and a JSON endpoint, and query them together as if they were tables in one database.

It sends the work your database can do to your database, and does the rest itself.

Try it in five minutes How federation works Use it from the shell

Open source, Apache 2.0
Java 21+
One jar, no server
Source on GitHub

Revenue by region Postgres table + CSV file

DataSource shop = ...;   // the database you already have

Relix relix = Relix.builder().jdbc("shop", shop).build();

relix.define("""
    source Regions from csv("regions.csv") {
        header: true,
        schema: { customer_id: NUMBER, region: STRING }
    };
    """);

List<Tuple> rows = relix.relation("""
    GROUP region, SUM(amount) -> revenue (
        SELECT status = 'OPEN' (shop.orders) JOIN Regions
    )
    """).toList();

region  revenue
North       175

The status = 'OPEN' filter ran in the database. The join with the CSV ran in your process.

A library, not a server

One jar in your application. Nothing to install, deploy or keep running.

Not a database

It stores nothing. It reads from the systems you already have.

Not an ORM

No entities or mappings. You ask questions and get rows, or records if you prefer.

Read-only

A query can't change your data. Relix reads; it never writes.

Federated queries

Ask one question across every system you run

Real questions cross boundaries. Customers are in one database, invoices in another, support tickets behind an API, and account managers in a spreadsheet. The usual answer is a pipeline that copies everything into a warehouse first. Relix queries each system where it lives, when you ask.

“Which customers have an overdue invoice and an urgent support ticket, and who manages them?”

shopPostgreSQL

sent as SQL

SELECT "customer_id", "name"
FROM "customers"

billingMySQL

sent as SQL, filter included

SELECT `invoice_id`, `customer_id`,
       `status`, `amount`
FROM `invoices`
WHERE (CONVERT(`status` USING utf8mb4)
       COLLATE utf8mb4_0900_bin
       = 'OVERDUE')

TicketsHTTP / JSON API

fetched, then filtered in Relix

GET https://support.example.com/api/tickets

ManagersCSV file

read from disk

managers.csv

Relix, in your process

joins the database rows and the CSV
keeps customers with an urgent ticket
totals the overdue amount

One result

name	manager	overdue
Acme Ltd	Priya	1500

Declare each source once

Relix relix = Relix.builder()
        .jdbc("shop", postgres)      // DataSources you already have
        .jdbc("billing", mysql)
        .build();

relix.define("""
    source Tickets from http {
        url: "https://support.example.com/api/tickets",
        extract: json("$.tickets"),
        schema: { customer_id: NUMBER at "$.customer.id",
                  priority: STRING }
    };
    source Managers from csv("managers.csv") {
        header: true,
        schema: { customer_id: NUMBER, manager: STRING }
    };
    """);

Then query them as one

GROUP name, manager, SUM(amount) -> overdue (
    (shop.customers
        JOIN SELECT status = 'OVERDUE' (billing.invoices)
        JOIN Managers)
    SEMI customer_id = Tickets.customer_id
        SELECT priority = 'urgent' (Tickets)
)

γ name, manager, SUM(amount) → overdue (
    (shop.customers
        ⋈ σ status = 'OVERDUE' (billing.invoices)
        ⋈ Managers)
    ⋉ customer_id = Tickets.customer_id
        σ priority = 'urgent' (Tickets)
)

Names from a database connection are written connection.table. Everything else is a name you declared. The query neither knows nor cares which system is which.

What actually runs

Aggregate
└─ Join SEMI/NESTED_LOOP build=RIGHT
   ├─ Join NATURAL/HASH build=RIGHT
   │  ├─ Join NATURAL/HASH build=RIGHT
   │  │  ├─ PushedScan [jdbc/shop]
   │  │  │    SELECT "customer_id", "name" FROM "customers"
   │  │  └─ PushedScan [jdbc/billing]
   │  │       SELECT `invoice_id`, `customer_id`, `status`, `amount`
   │  │       FROM `invoices`
   │  │       WHERE (CONVERT(`status` USING utf8mb4)
   │  │              COLLATE utf8mb4_0900_bin = 'OVERDUE')
   │  └─ Scan Managers
   └─ Select
      └─ Scan Tickets

Each PushedScan is a database computing its own share; everything above them runs in this process. The API and the file have no query engine to fold into, and a join across two connections has no single system to run in — so what crosses the network here is whatever those scans return.

No copies, no pipeline

Nothing is loaded into a warehouse first, so an answer is as current as the systems it came from.

Each system does its share

A database gets the filters, grouping and sorting it can run as SQL, written for its dialect, and Relix computes the rest. How much crosses the network is a property of the query, which is why the plan is on this page rather than a promise about it.

Everything composes

A federated source is just a table, so the routes, budgets and rankings below work across systems too.

The fold has edges

One operator a backend can’t express ends the fold there, and a join across two connections is always computed here. explain() says where the line fell, before a row is read.

Reads from PostgreSQL · MySQL · any JDBC database · CSV · JSON files · log files · GEDCOM family trees · HTTP / JSON APIs · MongoDB (plugin) · rows your program already holds

Beyond SQL

Questions that are one operator here

Every Relix operation takes tables and returns a table, so each of these works on federated data as readily as on a single table.

The cheapest route, and the route itself

In SQLA recursive CTE, with the path glued into a string you parse back apart.

TRACE origin, dest VIA cost MINIMIZE
  AS route (Flights)

The row for LHR → JFK

cost	route
350	[LHR, DUB, JFK]

Deciding what to fund under a budget

In SQLNo query for it. Export the data, run a solver somewhere else, paste the answer back.

OPTIMIZE MAXIMIZE SUM(value)
  SUBJECT TO SUM(cost) <= 50000
  PER region (Backlog)

OPTIMIZE MAXIMIZE SUM(value)
  SUBJECT TO SUM(cost) ≤ 50000
  PER region (Backlog)

Result: the repairs to fund

repair	value	cost
road	60	20000
school	50	25000

The biggest order for each customer

In SQLA window function in a subquery, filtered outside it.

TOP 1 amount DESC
  PER customer (Orders)

Result: whole rows

customer	order_id	amount
Alice	3	200
Bob	5	75

Sessions, as-of joins, lineage, graph clusters and 19 more →

Made for Java

Write queries as text, or build them in code

Both produce the same query. Building one in Java means a value from outside goes in as a value rather than as text spliced into a query, so there is no string for it to break out of.

As text

Relation open = relix.relation("""
    PROJECT order_id, amount (
        SELECT status = 'OPEN' (shop.orders)
    )
    """);

In code

import static com.darkcollective.relix.ast.Expr.*;

String status = request.getParameter("status");

Relation open = relix.relation("shop.orders")
        .select(eq(attr("status"), str(status)))
        .project("order_id", "amount");

str(status) binds a value, so the request may put anything in it. The names around it are still names: a column or relation chosen at run time is an identifier, and belongs checked against the schema first — as a sort column would in any API.

No black box

See exactly what reached your database

Before a row is read, a query can show its plan: what was sent to each system as SQL and what Relix does itself.

Ask

System.out.println(relix.relation("""
    GROUP region, SUM(amount) -> revenue (
        SELECT status = 'OPEN' (shop.orders) JOIN Regions
    )
    """).explain());

The plan

Aggregate
└─ Join NATURAL/HASH build=RIGHT
   ├─ PushedScan [jdbc/shop]
   │    SELECT order_id, customer_id, status, amount
   │    FROM orders WHERE (status = 'OPEN')
   └─ Scan Regions

Everything under PushedScan ran in the database. When a query stays inside one database, its grouping, sorting and conditional joins are sent there too.

On the command line

The same queries, as one stage of a pipeline

The relix command runs Relix from a shell. Data comes from files, standard input, or the tables a project declares in its .relix/ directory. Rows go out as a table on a terminal, or as TSV, CSV, NDJSON, JSON or Markdown for the next program, so relix sits between jq, awk and sort like any other filter. It brings its own Java runtime, so there is nothing else to install.

jq, then relix, then jq

jq -c '{order_id, reason, refund: .refund.amount}' data/returns.ndjson \
  | relix -i Returns=ndjson:- -e 'PROJECT name, order_id, refund (Customers JOIN Orders JOIN Returns)' -o ndjson \
  | jq -r '"\(.name) was refunded \(.refund) on order \(.order_id)"'

Alice was refunded 80 on order 2
Carol was refunded 20 on order 4

Customers and Orders are tables the project declares, and Returns arrives on standard input, flattened by jq. Standard output carries rows and nothing else: messages go to standard error, and the exit status says what happened.

A data check in CI

relix --fail-rows -e 'SELECT amount <= 0 (Orders)' -o tsv > /dev/null && echo "every order has a positive amount"

every order has a positive amount

--fail-rows exits 1 when the query returns a row, and --fail-empty when it returns none.

Lint every script

find scripts -name '*.relix' | sort | xargs relix check 2>&1 || echo "a script has a problem"

scripts/broken.relix:1:30: error: Undefined relation: 'Ordrs'
a script has a problem

relix check analyses a script without running it, and reports each problem as FILE:LINE:COLUMN.

Install it

With Homebrew on macOS and Linux, Scoop on Windows, or an archive from the releases page.

Install the command →

A first query

Where a script comes from, binding files and standard input, and the output formats.

Start here →

Pipelines

Recipes with jq, awk, find, xargs, shell loops, another relix, and a GitHub Actions workflow.

See the recipes →

Every command

run, check, explain, optimize, trace, fmt, catalog and the rest, a page each.

Command reference →

Before you add a dependency

Questions you're probably asking

What can it read from?

Any database with a JDBC driver, with SQL written for PostgreSQL, MySQL, DuckDB, SQLite, SQL Server and Db2. Also CSV and JSON files, log files, GEDCOM family trees, HTTP endpoints that return JSON, rows your program already holds, and MongoDB through a plugin. One query can use any mix of them.

Does it load everything into memory?

No. Rows stream through the query. Filters, projections, grouping, sorting and joins on a condition within one database are sent to that database as SQL, so only what's left crosses the wire. Operations that must hold rows, such as a sort in Relix itself, can be capped (maxMaterializedRows) so a runaway query fails with a clear error instead of an OutOfMemoryError.

Do I have to type Greek letters?

No. Every operator has a plain keyword (SELECT, PROJECT, JOIN, GROUP) and that's what this page uses. The symbols from relational algebra (σ, π, ⋈, γ) are an optional shorthand that means exactly the same thing, and every example in the manuals can be switched between the two.

Is it a replacement for SQL or for jOOQ?

Neither, really. Keep SQL for what lives inside one database. Relix is for the questions that cross systems, or that SQL turns into a project: routes through a graph, choices under constraints, lineage, sessions and time-aligned joins. If you think in SQL, Coming from SQL shows the everyday statements side by side with their Relix.

How stable is it?

The Java API keeps backward compatibility within a major version, and every example in the manuals is compiled and run against the release, so the documentation matches what you install. Relix is developed by one person; About has the background.

Why Java 21?

Java 21 is the current long-term-support release, so it is the version most projects can actually adopt. The jar needs Java 21 or newer at runtime — a newer JDK is fine, and nothing in it is a preview feature.

Get started

Add one dependency, then query a file

1Add Relix to your build. The engine, functions, and the file, HTTP and JDBC readers are all in the one jar. For a database, add its JDBC driver too; for OPTIMIZE or COVER EXACT, add relix-solver-ojalgo at the same version as a runtime dependency.

Add it to your build Maven Central · 1.0.0-rc13

dependencies {
    implementation("com.darkcollective.relix:relix:1.0.0-rc13")
}

dependencies {
    implementation 'com.darkcollective.relix:relix:1.0.0-rc13'
}

<dependency>
  <groupId>com.darkcollective.relix</groupId>
  <artifactId>relix</artifactId>
  <version>1.0.0-rc13</version>
</dependency>

Java 21+
Apache 2.0
Bring your own JDBC driver

2Point it at a CSV file and ask a question. No database needed:

try (Relix relix = Relix.open()) {
    relix.define("""
        source Orders from csv("orders.csv") {
            header: true,
            schema: { order_id: NUMBER, customer: STRING, amount: NUMBER }
        };
        """);

    for (Tuple t : relix.relation("TOP 1 amount DESC PER customer (Orders)").toList()) {
        System.out.println(t.string("customer") + " " + t.decimal("amount"));
    }
}

3Connect your own database with Relix.builder().jdbc("name", dataSource), and name its tables as name.table. Querying a database walks through it.

Going further

Where to next

The programming guide

The Java API step by step: sessions, getting data in, building and running queries, and reading the plan. Every example is compiled and run.

Read the guide →

The command line

The relix command runs the same scripts from a shell, as one stage of a pipeline. Install it with Homebrew on macOS and Linux, or Scoop on Windows.

Use the command →

The language reference

A page for every operator and function, with a worked example, its real output, and what the engine did with it. The grammar has the whole language on one page.

SELECTσPROJECTπJOIN⋈GROUPγ

Find an operator →

Problem solving

What kind of problem is this, and which parts of the language solve it — a recipe per class of question, each built around a worked example with its real output.

Find a recipe →

Coming from SQL

The statements you already write, each beside its Relix. Every pair is run on a real database and returns the same rows.

See them side by side →

Beyond SQL

Twenty-three questions SQL makes hard or impossible, each shown with its Relix version.

See all 23 →

Relix for fun

A Game of Life spaceship, a Turing machine, a royal family tree and a Pokémon battle, each in a handful of lines.

Play →