README

[fi-kju-ela] · Query files like a database. No database required.

FiQueLa brings SQL querying to structured files. Filter, join, group, aggregate, and export data from XML, CSV, JSON, NDJSON, YAML, NEON, XLSX, ODS, and HTTP access logs — using familiar SQL syntax or a fluent PHP API.

SELECT brand, COUNT(id) AS products, AVG(price) AS avg_price
FROM csv(catalog.csv, delimiter: ";").*
WHERE in_stock = "yes" AND price > 100
GROUP BY brand
HAVING products > 5
ORDER BY avg_price DESC
LIMIT 10

Why FiQueLa?

• No database setup — query files directly, just PHP and Composer
• Familiar SQL — SELECT, WHERE, JOIN, GROUP BY, HAVING, ORDER BY, UNION, WITH (CTE), INTO and more
• Cross-format joins — join a CSV against an XML feed against a JSON file in one query
• Stream-first — large files are processed row by row with low memory overhead
• Expression evaluator — arithmetic, functions, and nested expressions everywhere

Supported Formats

Installation

composer require 1biot/fiquela

Requires PHP 8.2+ with ext-fileinfo, ext-json, ext-mbstring, ext-xmlreader, ext-simplexml, ext-libxml and ext-iconv.

Quick Start

FQL string — write queries just like SQL:

use FQL\Query\Provider;

$results = Provider::fql("
    SELECT name, brand, ROUND(price, 2) AS price
    FROM xml(feed.xml).SHOP.SHOPITEM
    WHERE price > 100 AND in_stock = 'yes'
    ORDER BY price DESC
    LIMIT 20
")->execute()->fetchAll();

Fluent API — chain PHP methods:

use FQL\Enum\Operator;
use FQL\Query\Provider;

$results = Provider::fromFileQuery('xml(feed.xml).SHOP.SHOPITEM')
    ->select('name', 'brand')
    ->round('price', 2)->as('price')
    ->where('price', Operator::GREATER_THAN, 100)
    ->and('in_stock', Operator::EQUAL, 'yes')
    ->orderBy('price')->desc()
    ->limit(20)
    ->execute()
    ->fetchAll();

Core Features

Joins — across files and formats

Join data from different files and formats in a single query. Left, right, inner, full outer, and subquery joins are all supported.

SELECT p.name, p.price, c.name AS category
FROM csv(products.csv).* AS p
LEFT JOIN json(categories.json).categories AS c
    ON p.category_id = c.id
WHERE p.price > 500
ORDER BY p.price DESC

use \FQL\Enum\Operator;
use \FQL\Query\Provider;

$products   = Provider::fromFileQuery('csv(products.csv).*');
$categories = Provider::fromFileQuery('json(categories.json).categories');

$results = $products
    ->select('name', 'price')
    ->select('c.name')->as('category')
    ->leftJoin($categories, 'c')
        ->on('category_id', Operator::EQUAL, 'id')
    ->where('price', Operator::GREATER_THAN, 500)
    ->orderBy('price')->desc()
    ->execute();

UNION — merge results from multiple sources

Combine results from different files, formats, or filter conditions. UNION deduplicates, UNION ALL keeps every row.

SELECT id, name, price, "warehouse_a" AS source
FROM csv(warehouse_a.csv).*
WHERE price < 100
UNION ALL
SELECT id, name, price, "warehouse_b" AS source
FROM xml(warehouse_b.xml).ITEMS.ITEM
WHERE price < 100

WITH — named, reusable sub-queries (CTE)

Declare one or more Common Table Expressions before the main SELECT, then reference them by name in FROM, JOIN, or UNION branches. Each CTE is materialised once and shared between references — perfect for self-joins or eliminating repetition across UNION branches.

WITH expensive AS (
    SELECT id, name FROM json(products.json).data.products
    WHERE price >= 300
),
     overlap AS (
    SELECT id FROM expensive
)
SELECT p.name
FROM json(products.json).data.products AS p
INNER JOIN overlap AS o ON id = o.id

Forward-chaining is allowed (later CTEs may reference earlier ones); WITH RECURSIVE is not yet supported.

Expression Evaluator

FiQueLa 3.0 evaluates arithmetic, function calls, and nested expressions anywhere — in SELECT, WHERE, HAVING, ORDER BY, and ON conditions.

-- arithmetic in SELECT
SELECT name, price * 1.21 AS price_with_vat, price * qty AS total

-- function call on left-hand side of WHERE
SELECT * FROM csv(users.csv).* WHERE LOWER(email) LIKE "%@example.com"

-- arithmetic in WHERE
SELECT * FROM csv(orders.csv).* WHERE price * (1 + vat_rate) > 1000

-- aggregate expression in HAVING
SELECT brand, SUM(price * qty) AS revenue
FROM csv(items.csv).*
GROUP BY brand
HAVING SUM(price * qty) > 50000

Aggregation

Full GROUP BY with aggregate functions, HAVING filtering, and DISTINCT support.

SELECT
    category,
    COUNT(id) AS products,
    SUM(price) AS revenue,
    AVG(price) AS avg_price,
    MIN(price) AS cheapest,
    MAX(price) AS most_expensive,
    GROUP_CONCAT(DISTINCT name, " | ") AS product_list
FROM json(products.json).products
GROUP BY category
HAVING products > 10
ORDER BY revenue DESC

Rich Filtering

-- type checking
WHERE price IS NUMBER AND tags IS ARRAY AND deleted_at IS NULL

-- pattern matching
WHERE name LIKE "%wireless%" AND sku REGEXP "^[A-Z]{2}-\d{4}$"

-- ranges and lists
WHERE price BETWEEN 100 AND 500
AND status IN ("active", "pending")

-- nested condition groups
WHERE price > 100
  AND (stock > 0 OR featured = true)
  AND (category = "electronics" OR discount > 0.2)

Export with INTO

Write query results directly to a file. Directories are created automatically; existing files are never silently overwritten.

-- filter and export to a different format
SELECT name, price, brand
FROM xml(feed.xml).SHOP.SHOPITEM
WHERE price > 500
ORDER BY price DESC
INTO csv(exports/premium.csv)

-- convert between formats
SELECT * FROM csv(data.csv).* INTO json(data.json).root.items
SELECT * FROM json(data.json).root.items INTO xlsx(data.xlsx).Sheet1.A1

Supported output formats: CSV, JSON, NDJSON, XML, XLSX, ODS.

Functions

String: CONCAT, CONCAT_WS, UPPER, LOWER, SUBSTRING, REPLACE, LPAD, RPAD, EXPLODE, IMPLODE, LOCATE, REVERSE, MATCH AGAINST

Math: ROUND, CEIL, FLOOR, MOD

Utility: IF, CASE WHEN, COALESCE, NULLIF, CAST, DATE_FORMAT, NOW, CURDATE, RANDOM_STRING, BASE64_ENCODE, BASE64_DECODE

Aggregate: COUNT, SUM, AVG, MIN, MAX, GROUP_CONCAT — all with optional DISTINCT

EXPLAIN ANALYZE

Profile query execution without leaving PHP. Every pipeline phase reports row counts, wall time, and memory usage.

EXPLAIN ANALYZE
SELECT brand, COUNT(id) AS products, SUM(price) AS revenue
FROM csv(catalog.csv, delimiter: ";").*
GROUP BY brand
ORDER BY revenue DESC

| phase  | rows_in | rows_out | time_ms  | duration_pct | mem_peak_kb |
|--------|---------|----------|----------|--------------|-------------|
| stream | null    | 178 362  | 4 230.1  | 61%          | 14 231.5    |
| where  | 178 362 | 95 110   | 1 840.3  | 27%          | 14 231.5    |
| group  | 95 110  | 42       | 810.5    | 12%          | 18 540.2    |
| sort   | 42      | 42       | 2.1      | <1%          | 18 540.2    |

HTTP Access Log Parsing

Query Nginx and Apache access logs with standard FQL — filter by status code, group by path, aggregate response times.

SELECT path, COUNT(*) AS hits, AVG(timeServeRequest) AS avg_ms
FROM log(access.log, "nginx_combined").*
WHERE status >= 400
GROUP BY path
ORDER BY hits DESC
LIMIT 20

Custom log formats via Apache log_format pattern:

FROM log(access.log, format: "custom", pattern: "%h %t %r %>s %D").*

Two Query Styles

FiQueLa supports both styles interchangeably — pick whichever fits your workflow.

Fetching Results

$results = $query->execute();

// all rows as array
$rows = $results->fetchAll();

// first row only
$row = $results->fetch();

// single scalar value
$value = $results->fetchSingle('price');

// map to DTO
$dtos = $results->fetchAll(ProductDTO::class);

// stream row by row (low memory)
foreach ($results->getIterator() as $row) {
    // process $row
}

Ecosystem

# Install CLI
curl -fsSL https://raw.githubusercontent.com/1biot/fiquela-cli/main/install.sh | bash

# Interactive REPL
fiquela-cli --file=data.csv

# Single query
fiquela-cli "SELECT name, price FROM csv(data.csv).* WHERE price > 100;"

Known Limitations

• JOIN and ORDER BY load data into memory — plan accordingly for very large datasets
• INTO throws FileAlreadyExistsException if the target file already exists

Roadmap

• MessagePack format support
• Parquet format support
• Redis / APCu hashmap cache for JOIN
• LSP server for .fql files (PhpStorm, VS Code)

Documentation

Full documentation at docs.fiquela.io

• Operator BETWEEN: Add operator BETWEEN for filtering data and add support for dates and ranges.
• XLS/XLSX: Add Excel file support.
• Custom cast type: Add support for custom cast type for SELECT clause.
• Add explain method: Add method explain() for explaining query execution from actual query debugger and provide more complex information about query.
• PHPStan 8: Fix all PHPStan 8 errors.
• Tests: Increase test coverage (80%+).
• Optimize GROUP BY: Optimize GROUP BY for more memory efficient data processing.
• DELETE, UPDATE, INSERT: Support for manipulating data in files. - Instead of this, it will comes support for exporting data to files (CSV, NDJson, MessagePack, and more...) by INTO clause.
• Documentation: Create detailed guides and examples for advanced use cases. - docs.fiquela.io
• Tests: Increase test coverage (90%+).
• Next file formats: Add next file formats MessagePack, Parquet, INI and TOML
• Hashmap cache: Add hashmap cache (Redis, Memcache) for more memory efficient data processing.

Contributing

Contributions are welcome! Fork the repo, create a branch, make your changes, and open a pull request. All tests must pass.

composer install
composer test       # PHP CodeSniffer, PHPStan level 8, PHPUnit
composer examples   # run example queries