iot-storage

Path-based lightweight SQL database with REST API — MQTT-inspired hierarchical records.

Think of it as MQTT topics meets SQL. Every path is a record. Query with SQL. Built for IoT and edge deployments.

Install

npm install -g iot-storage

Quick Start

# Start as a daemon (background)
iot-storage start

# Or run in foreground (debug mode)
iot-storage run

By default, iot-storage listens on http://localhost:9123 and stores data in ~/.iot-storage/data.json.

CLI Commands

| Command | Description | |---------|-------------| | iot-storage start | Start as background daemon | | iot-storage stop | Stop the daemon | | iot-storage restart | Restart the daemon | | iot-storage run | Run in foreground (debug mode) | | iot-storage status | Check if daemon is running |

Options

| Flag | Description | Default | |------|-------------|---------| | -p, --port <number> | Server port | 9123 | | -d, --data <path> | JSON data file path | ~/.iot-storage/data.json | | -b, --backend <name> | Storage backend | json | | -c, --config <path> | Config file | — |

API Reference

All data is sent and received as JSON.

POST /query — Execute SQL

curl -X POST http://localhost:9123/query \
  -H "Content-Type: application/json" \
  -d '{"sql": "INSERT INTO \"/sensors/temp\" (value, unit) VALUES (23.5, \"C\")"}'

Response:

{
  "type": "insert",
  "rows": [
    {
      "_path": "/sensors/temp",
      "value": 23.5,
      "unit": "C",
      "_created": "2026-06-13T10:00:00.000Z",
      "_updated": "2026-06-13T10:00:00.000Z"
    }
  ],
  "affected": 1
}

Path-Based Data Access

For simple operations without SQL:

| Method | Endpoint | Description | |--------|----------|-------------| | GET | /data/:path | Fetch record(s) at path | | PUT | /data/:path | Upsert record at path | | DELETE | /data/:path | Delete record(s) at path |

# Fetch all sensor records
curl http://localhost:9123/data/sensors/+

# Upsert a record
curl -X PUT http://localhost:9123/data/sensors/humidity \
  -H "Content-Type: application/json" \
  -d '{"value": 65, "unit": "%"}'

# Delete a record
curl -X DELETE http://localhost:9123/data/sensors/temp

Field Projection

Append a field name to get just that value instead of the full record:

# Get the full record
curl http://localhost:9123/data/sensors/humidity
# → {"value": 65, "unit": "%", "_path": "/sensors/humidity", ...}

# Get just the 'value' field
curl http://localhost:9123/data/sensors/humidity/value
# → 65

# Get just the 'unit' field
curl http://localhost:9123/data/sensors/humidity/unit
# → "%"

Returns 404 if the field doesn't exist. System fields (_path, _created, _updated) cannot be accessed this way — they require the full record.

Direct PUT (Set a field)

GET /health

curl http://localhost:9123/health

Response:

{
  "status": "ok",
  "uptime": 3600,
  "backend": "json",
  "version": "1.0.1"
}

SQL Reference

INSERT

INSERT INTO "/sensors/temp" (value, unit) VALUES (23.5, 'C')
INSERT INTO "/sensors/temp" (value, unit) VALUES (24.1, 'C'), (22.8, 'C')

SELECT

-- Exact path
SELECT * FROM "/sensors/temp"

-- Single-level wildcard (+)
SELECT * FROM "/sensors/+"

-- Multi-level wildcard (#) — at end of path only
SELECT * FROM "/sensors/#"

-- With WHERE filters
SELECT * FROM "/sensors/+" WHERE value > 20 AND unit = 'C'

-- With sorting
SELECT * FROM "/sensors/+" ORDER BY value DESC

-- With limit
SELECT * FROM "/sensors/+" LIMIT 10

-- Column projection
SELECT value, unit FROM "/sensors/temp"

UPDATE

UPDATE "/sensors/temp" SET unit = 'F', value = 75.2
UPDATE "/sensors/+" SET status = 'inactive' WHERE value < 0

DELETE

DELETE FROM "/sensors/temp"
DELETE FROM "/sensors/+" WHERE value IS NULL

Aggregations

SELECT COUNT(*) FROM "/sensors/+"
SELECT AVG(value) FROM "/sensors/temp"
SELECT SUM(value) FROM "/sensors/+"
SELECT MIN(value), MAX(value) FROM "/sensors/+"
SELECT unit, AVG(value) AS avg_val FROM "/sensors/+" GROUP BY unit

WHERE Operators

| Operator | Description | |----------|-------------| | =, == | Equal | | !=, <> | Not equal | | >, < | Greater / Less than | | >=, <= | Greater / Less or equal | | LIKE | Pattern match (% = any, _ = single char) | | IN | Value in list | | IS NULL | Value is null | | IS NOT NULL | Value is not null |

Path System (MQTT-Inspired)

Paths work like MQTT topics:

| Pattern | Matches | |---------|---------| | /sensors/temp | Exactly /sensors/temp | | /sensors/+ | /sensors/temp, /sensors/humidity (single level) | | /sensors/# | /sensors/temp, /sensors/room/light (all levels) |

Important: When you SELECT a parent path like /sensors, it returns the record at /sensors AND all its descendants. This is analogous to MQTT where subscribing to /sensors gives you all messages under that topic.

Namespaces (@) — Isolated Data Files

Use @namespace as the first segment of a path to store data in a separate JSON file. This keeps unrelated data isolated, improves performance for large datasets, and requires no setup — files are created automatically on first write.

How It Works

| Path | Stored In | |------|-----------| | /sensors/temp | data.json (default) | | /@fleet-a/sensors/temp | fleet-a.json | | /@warehouse/devices/pump | warehouse.json | | /@lab-east/sensors/+ | lab-east.json (wildcard reads) |

All namespace files live in the same directory as data.json (~/.iot-storage/ by default). Names are sanitized: @ is stripped and .json is appended. There is NO limit on the number of namespaces.

Writing to a Namespace

-- This data goes into fleet-a.json
INSERT INTO "/@fleet-a/sensors/temp" (value, unit) VALUES (23.5, 'C')

-- This goes into data.json (no @ prefix)
INSERT INTO "/sensors/temp" (value, unit) VALUES (22.1, 'C')

Or via REST:

curl -X PUT http://localhost:9123/data/@fleet-a/sensors/temp \
  -H "Content-Type: application/json" \
  -d '{"value": 23.5, "unit": "C"}'

Querying Within a Namespace

-- Query only fleet-a's sensors
SELECT * FROM "/@fleet-a/sensors/+"

-- Sort and filter within a namespace
SELECT * FROM "/@fleet-a/sensors/+" WHERE value > 20 ORDER BY value DESC

Cross-Namespace Queries

Use @+ or @# wildcards to query across namespaces:

-- Query sensors from ALL namespaces
SELECT * FROM "/@+/sensors/temp"

-- Query everything across all namespaces
SELECT * FROM "/#"

-- Aggregation across namespaces
SELECT AVG(value) FROM "/@+/sensors/+"

Results from cross-namespace queries include a _namespace field indicating which file each record came from.

Namespace Behavior

• Implicit creation — write to /@anyname/... and anyname.json is created automatically
• Separation — you can't accidentally join data between namespaces without @+ or @#
• Identical schema support — each namespace file is a full JsonBackend, supporting all operations
• No delete namespace — to remove a namespace, delete the .json file from the data directory

Data Directory with Namespaces

~/.iot-storage/
├── data.json          # Default namespace
├── fleet-a.json       # @fleet-a namespace
├── warehouse.json     # @warehouse namespace
├── lab-east.json      # @lab-east namespace
├── config.json
├── iot-storage.pid
└── logs/
    └── iot-storage.log

Configuration

iot-storage reads configuration from (in priority order):

1. CLI flags — iot-storage run --port 8080 --data ./mydata.json
2. Local config — ./iot-storage.config.json (in current directory)
3. Home config — ~/.iot-storage/config.json
4. Environment — IOT_STORAGE_PORT=8080, IOT_STORAGE_DATA_FILE=./mydata.json
5. Defaults — port 9123, data ~/.iot-storage/data.json

Example config file:

{
  "port": 8080,
  "dataFile": "/var/lib/iot-storage/data.json",
  "backend": "json"
}

Backend Plugins

iot-storage supports pluggable backends. Currently json is the only built-in backend. To create a custom backend, extend the Backend base class:

const { backends } = require('iot-storage');

class MyBackend extends backends.Base {
  async connect(config) { /* ... */ }
  async upsert(path, data) { /* ... */ }
  async query(pathPattern, filters, sortBy, sortDir, limit, offset) { /* ... */ }
  async update(pathPattern, updates, filters) { /* ... */ }
  async delete(pathPattern, filters) { /* ... */ }
  async getExact(path) { /* ... */ }
  async close() { /* ... */ }
}

Data Directory

~/.iot-storage/
├── iot-storage.pid        # Daemon PID file
├── config.json      # User configuration
├── data.json        # JSON data storage
└── logs/
    └── iot-storage.log    # Daemon stdout/stderr logs

License

MIT © iot-storage contributors