phpsqlarrays - SQL for Arrays, CSV, and JSON

Query PHP arrays, CSV files, and JSON files using SQL via SQLite. Similar to Laravel's Sushi package, but framework-agnostic and works with the DAL class pattern.

Features

• Query arrays with SQL - Use full SQL power on PHP arrays
• CSV file support - Load CSV files as queryable tables
• JSON file support - Load JSON files with nested data path support
• Optional caching - Cache to SQLite files or use pure in-memory tables
• Auto cache invalidation - Automatically rebuilds cache when source files change
• Schema inference - Auto-detects column types or use custom schema definitions
• DAL compatible - Uses the same r() and w() interface as DAL class
• JOIN support - Join across multiple array/CSV/JSON tables

Current Status

Side/hobby project, not intended for serious use.

If you want something professionally maintained for Laravel projects, use Sushi.

Requirements

• PHP 8.x
• PDO extension
• PDO SQLite driver

# Ubuntu/Debian
apt-get install -y php-pdo php-sqlite3

# CentOS/RHEL
yum install php-pdo php-sqlite

# macOS (Homebrew) - SQLite included by default
brew install php

Usage

Basic Array Tables (In-Memory)

<?php
require_once 'phpsqlarrays.php';

// Create in-memory instance (no caching)
$dal = new phpsqlarrays(null, false);

// Define data as array
$states = [
    ['id' => 1, 'abbr' => 'NY', 'name' => 'New York'],
    ['id' => 2, 'abbr' => 'CA', 'name' => 'California'],
    ['id' => 3, 'abbr' => 'TX', 'name' => 'Texas'],
];

// Register as table
$dal->fromArray('states', $states);

// Query with SQL
$result = $dal->r("SELECT * FROM states WHERE abbr = ?", ['CA']);
// Returns: [['id' => 2, 'abbr' => 'CA', 'name' => 'California']]

CSV Files

// Create cached instance
$dal = new phpsqlarrays(__DIR__ . '/cache', true);

// Load CSV file as table
$dal->fromCSV('countries', '/path/to/countries.csv');

// Query the CSV data
$european = $dal->r("SELECT * FROM countries WHERE region = ?", ['Europe']);

JSON Files

// JSON file with nested data:
// {"meta": {...}, "data": {"items": [{...}, {...}]}}

$dal->fromJSON('products', '/path/to/products.json', null, 'data.items');

// Query products
$electronics = $dal->r(
    "SELECT * FROM products WHERE category = ? ORDER BY price DESC",
    ['Electronics']
);

Custom Schema

$roles = [
    ['id' => 'admin', 'label' => 'Admin', 'level' => 100],
    ['id' => 'user', 'label' => 'User', 'level' => 10],
];

$schema = [
    'id' => 'string',
    'label' => 'string',
    'level' => 'integer'
];

$dal->fromArray('roles', $roles, $schema);

JOIN Across Tables

$dal->fromArray('users', [
    ['id' => 1, 'name' => 'Alice', 'role_id' => 'admin'],
    ['id' => 2, 'name' => 'Bob', 'role_id' => 'user'],
]);

$dal->fromArray('roles', [
    ['id' => 'admin', 'label' => 'Administrator'],
    ['id' => 'user', 'label' => 'User'],
]);

$result = $dal->r("
    SELECT u.name, r.label as role
    FROM users u
    JOIN roles r ON u.role_id = r.id
");

Caching Behavior

// With caching enabled (default)
$dal = new phpsqlarrays('/path/to/cache', true);

// Tables are cached to /path/to/cache/array_dal_cache.sqlite
// Cache is automatically invalidated when source files change
$dal->fromCSV('data', '/path/to/data.csv');

// Without caching (pure in-memory)
$dal = new phpsqlarrays(null, false);
// Tables exist only for the lifetime of the script

API Reference

Constructor

new phpsqlarrays(?string $cacheDir = null, bool $useCache = true)

• $cacheDir - Directory for SQLite cache files (null for memory-only)
• $useCache - Enable file-based caching (false = memory tables only)

Data Loading

Method	Description
fromArray($table, $rows, $schema?, $sourceFile?)	Create table from PHP array
fromCSV($table, $path, $schema?, $hasHeader?, $delimiter?)	Create table from CSV file
fromJSON($table, $path, $schema?, $dataPath?)	Create table from JSON file

Query Methods (DAL Compatible)

Method	Description
r($query, $params?, $fetchMode?)	Execute SELECT query
w($query, $params?)	Execute INSERT/UPDATE/DELETE
read(...)	Alias for r()
write(...)	Alias for w()
rows_affected()	Get rows affected by last write

Helper Methods

Method	Description
all($table)	Get all rows from table
find($table, $id, $idColumn?)	Find row by ID
where($table, $column, $value)	Find rows by column value
first($table, $column?, $value?)	Get first matching row
count($table, $column?, $value?)	Count rows

Utility Methods

Method	Description
getDAL()	Get underlying DAL instance
getTables()	List registered table names
tableExists($table)	Check if table exists
getSchema($table)	Get table column info
dropTable($table)	Drop a specific table
clearCache()	Clear all cached tables

Supported Schema Types

Type	SQLite Type
int, integer	INTEGER
float, double, real, decimal	REAL
bool, boolean	INTEGER
string, text	TEXT
blob	BLOB
datetime, date, time, json	TEXT

Running the Demo

php run.php